Multiple Charts from CSV

This is the report case: one dataset, several views of it. A CSV of sales rows becomes a sheet, and three charts sit beside it - a line chart of daily sales over time, a bar chart of totals per salesperson, and a pie chart of each person’s share of the total.

What makes it worth reading is that the three charts get their data two different ways. The line chart references cells, because the daily figures are in the sheet anyway. The bar and pie charts are fed from Go values, because their numbers are aggregates that exist only in a map.

Neither approach is better. Referenced data stays editable in Excel; embedded values keep the sheet clean of intermediate columns.

Three charts, one drawing

drawing := ss.AddDrawing()

chart, anc := drawing.AddChart(spreadsheet.AnchorTypeTwoCell)
anc.SetWidthCells(10)
anc.MoveTo(7, 1)

chart2, anc2 := drawing.AddChart(spreadsheet.AnchorTypeTwoCell)
anc2.SetWidthCells(8)
anc2.MoveTo(18, 1)

chart3, anc3 := drawing.AddChart(spreadsheet.AnchorTypeTwoCell)
anc3.SetWidthCells(10)
anc3.MoveTo(9, 23)

sheet.SetDrawing(drawing)

A sheet references a single drawing, so all three charts are added to the same one and kept apart by their anchors. SetWidthCells then MoveTo is the right order: MoveTo preserves whatever size the anchor already has, and moves both corners together. See Multiple Charts for the drawing rule in more detail.

Dates in the sheet

The date column needs a number format, or every date reads as a five-digit serial number:

cell := row.AddCell()
cell.SetDate(dataRow.DateOfSale)
cellStyle := ss.StyleSheet.AddCellStyle()
cellStyle.SetNumberFormatStandard(spreadsheet.StandardFormat15)
cell.SetStyle(cellStyle)

StandardFormat15 is d-mmm-yy. SetDateWithStyle does the same job in one call, reusing an existing date style or creating one, where the example builds a fresh cell style for every row.

Limitations

SetDate silently ignores dates before the workbook epoch. Excel does not support negative serial dates, so a pre-1900 date leaves the cell empty with no error returned.

Cell ranges in this example are hardcoded to the fixture’s 31 rows ('Sheet 1'!C2:C32). Point it at a longer CSV and the line chart still plots 31 points. Line Chart from CSV shows how to compute the range from the rows actually written.

Aggregates come out of a Go map, and map iteration order is not stable, so the salesperson categories on the bar and pie charts appear in a different order each run. Sorting the keys before building the slices fixes it.

The example does not call ss.Validate() before saving, and it ignores the error from SaveToFile. Both are worth adding in anything you ship.

One thing to correct if you adapt loadCsv: it parses the quantity field from the sale price column, so the quantity column in the output workbook is a copy of the price column. No chart uses that column, which is why it goes unnoticed.

Run the example

loadCsv parses ./data/test-data.csv into a slice of structs, the main function writes the rows and the two cumulative columns, then builds the three charts. Run it from inside the example folder so the relative data path resolves.

git clone https://github.com/unidoc/unioffice-examples.git
cd unioffice-examples/spreadsheet/multiple-charts-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.