Excelize - Golang Library for Reading and Writing XLSX Files

Excelize - Golang Library for Reading and Writing XLSX Files

Last week I create a Golang XLSX file library named Excelize. Sometimes we want to generate a XLSX file without Excel, .Net or Java applications, accroding to assessment some popular open-source library, I can't find out one library that support save charts after write an exists XLSX. Some times these library will lost some colors or style of Excel, so I decided to solve this problem and create a library support read and write XLSX with charts.

Before XLSX file operations, we need to understand the file structure and Open XML and OOXML (Office Open XML) standard. Let's take a look of XLSX file structure. For example Workbook1.xlsx:

Excelize - Golang Library for Reading and Writing XLSX Files

Excelize - Golang Library for Reading and Writing XLSX Files

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 for 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 for 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 for 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 library to read and write XLSX by OOXML standard, Excelize is a Golang implementation of that. Excelize can read and write file created by Office Excel 2007 and later.

Basic Usage

  • Installation
$ go get github.com/xuri/excelize
  • Create XLSX File

Here is a minimal example usage that will create XLSX file.

package main

import (
    "fmt"
    "os"

    "github.com/xuri/excelize"
)

func main() {
    xlsx := excelize.NewFile()
    // Create a new sheet.
    xlsx.NewSheet(2, "Sheet2")
    // Set value of a cell.
    xlsx.SetCellValue("Sheet2", "A2", "Hello world.")
    xlsx.SetCellValue("Sheet1", "B2", 100)
    // Set active sheet of the workbook.
    xlsx.SetActiveSheet(2)
    // Save xlsx file by the given path.
    err := xlsx.SaveAs("./Workbook.xlsx")
    if err != nil {
        fmt.Println(err)
        os.Exit(1)
    }
}
  • Reading XLSX File

The following constitutes the bare to read a XLSX document.

package main

import (
    "fmt"
    "os"
    "strconv"

    "github.com/xuri/excelize"
)

func main() {
    xlsx, err := excelize.OpenFile("./Workbook.xlsx")
    if err != nil {
        fmt.Println(err)
        os.Exit(1)
    }
    // Get value from cell by given sheet index and axis.
    cell := xlsx.GetCellValue("Sheet1", "B2")
    fmt.Println(cell)
    // Get sheet index.
    index := xlsx.GetSheetIndex("Sheet2")
    // Get all the rows in a sheet.
    rows := xlsx.GetRows("sheet" + strconv.Itoa(index))
    for _, row := range rows {
        for _, colCell := range row {
            fmt.Print(colCell, "\t")
        }
        fmt.Println()
    }
}
  • Add picture to XLSX file
package main

import (
    "fmt"
    "os"
    _ "image/gif"
    _ "image/jpeg"
    _ "image/png"

    "github.com/xuri/excelize"
)

func main() {
    xlsx, err := excelize.OpenFile("./Workbook.xlsx")
    if err != nil {
        fmt.Println(err)
        os.Exit(1)
    }
    // Insert a picture.
    err = xlsx.AddPicture("Sheet1", "A2", "./image1.png", "")
    if err != nil {
        fmt.Println(err)
    }
    // Insert a picture to sheet with scaling.
    err = xlsx.AddPicture("Sheet1", "D2", "./image2.jpg", `{"x_scale": 0.5, "y_scale": 0.5}`)
    if err != nil {
        fmt.Println(err)
    }
    // Insert a picture offset in the cell with printing support.
    err = xlsx.AddPicture("Sheet1", "H2", "./image3.gif", `{"x_offset": 15, "y_offset": 10, "print_obj": true, "lock_aspect_ratio": false, "locked": false}`)
    if err != nil {
        fmt.Println(err)
    }
    // Save the xlsx file with the origin path.
    err = xlsx.Save()
    if err != nil {
        fmt.Println(err)
        os.Exit(1)
    }
}

I will continue to update the program and improve 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.

Reference

Standard ECMA-376 Office Open XML File Formats
Office developer documentation
Structure of a SpreadsheetML document (Open XML SDK)
2007 Office Document: Open XML Markup Explained

Excelize - Golang Library for Reading and Writing XLSX Files
8 votes, 4.88 avg. rating (97% score)
  • Lisa

    Great work that i'm benefiting from so thanks very much. 👍🏻