Last week I create a Go language spreadsheet document library named Excelize. Sometimes we want to generate a spreadsheet document (XLSX / XLSM / XLTM) without Excel application, .Net or Java applications, according to assessment of some popular open-source library, I can't find out one library that supports save charts after writing an exists Excel document. Sometimes these libraries will lose some colors or style of Excel, so I decided to solve this problem and create library support to read and write a spreadsheet with charts.
Before spreadsheet document operations, we need to understand the document structure and Open XML and OOXML (Office Open XML) standard. Let's take a look of spreadsheet document structure. For example Workbook1.xlsx:
Unzip Workbook1.xlsx file we can got directory tree like this:
Workbook1
├── [Content_Types].xml
├── _rels
├── docProps
│ ├── app.xml
│ └── core.xml
└── xl
├── _rels
│ └── workbook.xml.rels
├── charts
│ ├── _rels
│ │ ├── chart1.xml.rels
│ │ └── chart2.xml.rels
│ ├── chart1.xml
│ ├── chart2.xml
│ ├── colors1.xml
│ ├── colors2.xml
│ ├── style1.xml
│ └── style2.xml
├── drawings
│ ├── _rels
│ │ └── drawing1.xml.rels
│ └── drawing1.xml
├── sharedStrings.xml
├── styles.xml
├── theme
│ └── theme1.xml
├── workbook.xml
└── worksheets
├── _rels
│ └── sheet1.xml.rels
├── sheet1.xml
└── sheet2.xml
- Content Types
Every package must have a [Content_Types].xml, found at the root of the package. This file contains a list of all of the content types of the parts in the package. Every part and its type must be listed in [Content_Types].xml. The following is a content type for the main content part:
<Override PartName="/xl/workbook.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet.main+xml"/> </Override>
It's important to keep this in mind when adding new parts to the package.
- Relationships
Every package contains a relationships part that defines the relationships between the other parts and to resources outside of the package. This separates the relationships from content and makes it easy to change relationships without changing the sources that reference targets.
For an OOXML package, there is always a relationships part .rels within the _rels folder that identifies the starting parts of the package, or the package relationships. For example, the following defines the identity of the start part for the content:
<Relationship Id="rId1" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/officeDocument" Target="xl/workbook.xml"/>
- Document Properties
There are also typically relationships within .rels for app.xml and core.xml.
In addition to the relationships part of the package, each part that is the source of one or more relationships will have its own relationships part. Each such relationship part is found within a _rels sub-folder of the part and is named by appending .rels to the name of the part.
Typically the main content part workbook.xml has its own relationships part workbook.xml.rels. It will contain relationships to the other parts of the content, such as sheet1.xml, sharedStrings.xml, styles.xml, theme1,xml, as well as the URIs for external links.
- Chartsheet
Contains a chart that is stored in its owne sheet. A package can contain multiple such parts, referenced from the workbook part.
- Drawings
Contains the presentation and layout information for one or more drawing elements that are present in the worksheet. There should be drawings part for each worksheet that has a drawing.
- Shared String Table
Contains one occurrence of each unique string that occurs in any worksheet within the workbook. There is one such part of a package.
- Styles
Contains all the characteristics for all cells in the workbook, including numeric and text formatting, alignment, font, color, and border. A package contains no more than one such part.
- Workbook
Contains data and references to all of the worksheets. There must be one and only one workbook part.
- Worksheet
Contains all the data, formulas, and characteristics of a given worksheet. There is one such part of each worksheet in the package.
Open XML is an open ECMA-376 standard and is also approved as the ISO/IEC 29500 standard that defines a set of XML schemas for representing spreadsheets, charts, presentations, and word processing documents. Microsoft Office Word 2007, Excel 2007, PowerPoint 2007, and the later versions all use Open XML as the default file format.
The Open XML file formats are useful for developers because they use an open standard and are based on well-known technologies: ZIP and XML.
We can create a library to read and write a spreadsheet document by OOXML standard, Excelize is a Go implementation of that. Excelize can read and write files created by Office Excel 2007™ and later.
Basic Usage
- Installation
$ go get github.com/xuri/excelize/v2
- Create spreadsheet
Here is a minimal example usage that will create spreadsheet file.
package main
import (
"fmt"
"github.com/xuri/excelize/v2"
)
func main() {
f := excelize.NewFile()
// Create a new sheet.
index := f.NewSheet("Sheet2")
// Set the value of a cell.
f.SetCellValue("Sheet2", "A2", "Hello world.")
f.SetCellValue("Sheet1", "B2", 100)
// Set active sheet of the workbook.
f.SetActiveSheet(index)
// Save spreadsheet by the given path.
if err := f.SaveAs("Book1.xlsx"); err != nil {
fmt.Println(err)
}
}
- Reading spreadsheet
The following constitutes the bare to read a spreadsheet document.
package main
import (
"fmt"
"github.com/xuri/excelize/v2"
)
func main() {
f, err := excelize.OpenFile("Book1.xlsx")
if err != nil {
fmt.Println(err)
return
}
// Close the spreadsheet.
defer func() {
if err := f.Close(); err != nil {
fmt.Println(err)
}
}()
// Get value from cell by given worksheet name and axis.
cell, err := f.GetCellValue("Sheet1", "B2")
if err != nil {
fmt.Println(err)
return
}
fmt.Println(cell)
// Get all the rows in the Sheet1.
rows, err := f.GetRows("Sheet1")
if err != nil {
fmt.Println(err)
return
}
for _, row := range rows {
for _, colCell := range row {
fmt.Print(colCell, "\t")
}
fmt.Println()
}
}
- Add chart to spreadsheet file
With Excelize chart generation and management is as easy as a few lines of code. You can build charts based on data in your worksheet or generate charts without any data in your worksheet at all.
package main
import (
"fmt"
"github.com/xuri/excelize/v2"
)
func main() {
categories := map[string]string{
"A2": "Small", "A3": "Normal", "A4": "Large",
"B1": "Apple", "C1": "Orange", "D1": "Pear"}
values := map[string]int{
"B2": 2, "C2": 3, "D2": 3, "B3": 5,
"C3": 2, "D3": 4, "B4": 6, "C4": 7, "D4": 8}
f := excelize.NewFile()
for k, v := range categories {
f.SetCellValue("Sheet1", k, v)
}
for k, v := range values {
f.SetCellValue("Sheet1", k, v)
}
if err := f.AddChart("Sheet1", "E1", `{
"type": "col3DClustered",
"series": [
{
"name": "Sheet1!$A$2",
"categories": "Sheet1!$B$1:$D$1",
"values": "Sheet1!$B$2:$D$2"
},
{
"name": "Sheet1!$A$3",
"categories": "Sheet1!$B$1:$D$1",
"values": "Sheet1!$B$3:$D$3"
},
{
"name": "Sheet1!$A$4",
"categories": "Sheet1!$B$1:$D$1",
"values": "Sheet1!$B$4:$D$4"
}],
"title":
{
"name": "Fruit 3D Clustered Column Chart"
}
}`); err != nil {
fmt.Println(err)
return
}
// Save spreadsheet by the given path.
if err := f.SaveAs("Book1.xlsx"); err != nil {
fmt.Println(err)
}
}
- Add picture to spreadsheet file
package main
import (
"fmt"
_ "image/gif"
_ "image/jpeg"
_ "image/png"
"github.com/xuri/excelize/v2"
)
func main() {
f, err := excelize.OpenFile("Book1.xlsx")
if err != nil {
fmt.Println(err)
return
}
// Close the spreadsheet.
defer func() {
if err := f.Close(); err != nil {
fmt.Println(err)
}
}()
// Insert a picture.
if err := f.AddPicture("Sheet1", "A2", "image.png", ""); err != nil {
fmt.Println(err)
}
// Insert a picture to worksheet with scaling.
if err := f.AddPicture("Sheet1", "D2", "image.jpg",
`{"x_scale": 0.5, "y_scale": 0.5}`); err != nil {
fmt.Println(err)
}
// Insert a picture offset in the cell with printing support.
if err := f.AddPicture("Sheet1", "H2", "image.gif", `{
"x_offset": 15,
"y_offset": 10,
"print_obj": true,
"lock_aspect_ratio": false,
"locked": false
}`); err != nil {
fmt.Println(err)
}
// Save the spreadsheet with the origin path.
if err = f.Save(); err != nil {
fmt.Println(err)
}
}
I will continue to update the program and improve the performance of this package, welcome contributions, open a pull request to fix a bug, or open an issue to discuss a new feature or change.
Performance Figures
Performance comparison of similar libs
The following graph shows performance comparison of generation 102400*50 plain text matrix by the major open source Excel libs under personal computer (2.6 GHz 6-Core Intel Core i7, 16 GB 2667 MHz DDR4, 500GB SSD, macOS Monterey 12.3.1), including Go, Python, Java, PHP and NodeJS.
Reference
Standard ECMA-376 Office Open XML File Formats
Office developer documentation
Structure of a SpreadsheetML document (Open XML SDK)
Open XML SDK 2.5 for Microsoft Office (only works on the Windows)
Google Chrome Extensions: OOXML Tools