Exploring Excelize with Go

Formulas, pivot tables, and plugins all provide ways of transforming or manipulating your data once it’s in a CSV or XLSX format. But, what if you are interested in continuously loading data into a shared report that other team members can more easily leverage in Excel or Google Sheets?

Excelize is a Go library for generating Excel-compatible spreadsheets with support for embedding formulas, styles, charts, and more. I’ve recently been working on an automation project where I have leveraged Go and Excelize to catalog a directory structure and turn that information into an auto-updating spreadsheet.

It’s been an exciting experience leveraging Go for this type of lightweight “ETL-ish” type of workload, and I wanted to share some of the insights I picked up while working with Excelize.

Style Definition

Unsurprisingly, Excel has a style system embedded into a spreadsheet when you change a cell’s background, text, or border attributes. It took me a bit longer than I would like to admit to understand how Excelize handles styles. Specifically, a lot of the documentation examples provide a style object as a JSON string.

This isn’t how you would want to structure your styles as you lose all type checking and have to handle escape characters and JSON formatting manually. It turns out that the NewStyle function supports an interface{} parameter, and when you pull back the covers, this function simply unmarshals your string into a Style object.

If we take this one step further with the example above, you can define the Style struct inline. Now you don’t have to worry about writing JSON by hand or messing up the types on individual attributes.

A final note on styles, some attributes such as the Border are typed to accept a slice of strings. If you’re like me, this makes it seem like you can have any number of border style definitions which doesn’t make sense.

It turns out, that similar to CSS, the expectation is to define four Border structs for the four sides of a cell (top, left, right, bottom). In the example on the left, you’ll notice that you designate each side by setting a Type attribute.

It seems like an interesting choice to leverage a slice over a map for this data structure but might have some hidden benefits under the hood. If you have any thoughts on this, I’d love to hear about it in the comments.

Style Indexing

Excelize builds an index of styles that get embedded in the spreadsheet; unfortunately, it only exposes that index via an incremental id. I’m assuming this is how Excel handles tracking this information internally, but I found this a bit error-prone as I added more styles. I opted to build a centralized index of these styles by registering them upfront and then leveraged a map[string]int to attach a name to a style’s internal ID.

This lets me inject the style index into various functions and retrieve a style by its name. I also wrote wrapper methods on the StyleIndex struct to provide better autocomplete and abstract away the underlying key if I need to change it in the future.

Cell Configuration

Excelize provides functions for setting a cell’s attribute individually but lacks higher-level functions that group together standard configurations. This was probably the right call for the library as a whole but led to a lot of duplicate code to perform the same set of actions each time. I wrote a few helper functions like the example below in which I grouped merging multiple cells and setting styles and values.

You should also notice the StyleIndex struct that is being injected into the function. I went back and forth on whether to pass around the index or pass in a style ID from one layer up. I’m not sure which option I prefer more, as each has its own tradeoffs regarding reusability.

• Having each call to a function specify its own style ID makes it more generic and reusable.
• Defining a separate function for each style means the end-user doesn’t need to have as much knowledge of the underlying styles used.

Do you have some thoughts on this tradeoff? Maybe, you’ve run into this problem in another styling system.

Generating Cell and Row Offsets as you iterate through data

At some point, you’re going to need to programmatically determine which row and column you need to write a piece of data in. Below is an example of how I accomplished this by leveraging the ColumnNumberToName function and an offset value. This allowed me to calculate the column position for multiple pieces of data without doing any ASCII calculations to calculate what comes after column AX1.

Conclusion

I’ve enjoyed leveraging Excelize to automate the generation of spreadsheets with rich content embedded. It very much feels like a lower-level API on top of the Excel file format, and that means you’ll have to spend some time creating higher-level functions for yourself. Have you leveraged Excelize before or perhaps another excel library in a different language?

Drop a comment below or reach out to me on Twitter; I would love to continue the conversation.