Line Chart from CSV

Charting a file instead of a literal table changes one thing, and it is the thing that breaks charts: you no longer know how many rows there will be. A series reference is a string like 'Sheet 1'!B2:B6, written into the chart XML and never checked, so a range hardcoded for a five-row fixture silently plots five points of a fifty-row file.

The fix is to record the row numbers while writing the data and format the reference afterwards.

Building the range as you go

row := sheet.AddRow()
row.AddCell().SetString("Date")
row.AddCell().SetString("Length")

startRowNumber := row.RowNumber() + 1
endRowNumber := startRowNumber
for r := 1; r < len(dateSlice); r++ {
    km, err := strconv.Atoi(kmSlice[r])
    if err != nil {
        log.Fatalf("unable to convert data into integer: %v", err)
    }
    row := sheet.AddRow()
    row.AddCell().SetString(dateSlice[r])
    row.AddCell().SetNumber(float64(km))
    endRowNumber = row.RowNumber()
}

kmSeries.CategoryAxis().SetLabelReference(fmt.Sprintf(`'Sheet 1'!A%d:A%d`, startRowNumber, endRowNumber))
kmSeries.Values().SetReference(fmt.Sprintf(`'Sheet 1'!B%d:B%d`, startRowNumber, endRowNumber))

RowNumber returns the 1-based row number, or zero if the row has no explicit number, so the header row’s number plus one is where the data starts. Tracking the last row inside the loop covers the case where some input rows are skipped and the count no longer matches the file.

The sheet name in the reference has to match the real name. AddSheet produces Sheet 1, Sheet 2 and so on, and the space is why the name is quoted. Rename the sheet with SetName and every reference string has to change with it.

The shape of the input

This example’s CSV is transposed. Each line is a whole series rather than a record: one line of kilometer readings, one line of dates, semicolon separated inside a single comma field. readCsv returns it as [][]string with one field per line, and the example splits on ; itself.

Check that before copying the loop. The ordinary layout of one record per line needs the opposite treatment: read down the rows and append a cell per column. Only the range arithmetic carries over unchanged.

Limitations

Nothing validates the reference. A range that points past the data plots blank points, and one that points at text plots nothing at all; both save without error, since Workbook.Validate checks sheets and cells but never charts.

Data has to reach the sheet before the chart can use it. There is no way to hand a series a Go slice and have Excel treat it as live data, though Line Chart with No Data covers embedding literal values when a table is not wanted.

SetNumber takes a float64, so integer input needs converting on the way in. Writing a parsed number back with SetString stores it as text, and a value reference expects numeric cells.

Run the example

readCsv loads the fixture, the main loop writes it to the sheet, and the chart plots kilometers against dates. The example reads example-data.csv from the working directory, so run it from inside the example folder.

git clone https://github.com/unidoc/unioffice-examples.git
cd unioffice-examples/spreadsheet/line-chart-from-csv
go run main.go

If this is your first time using UniOffice, follow the getting started guide to create an API key and set up your development environment.