An article explaining in detail how Golang generates Excel documents

Generating Excel documents based on data is a very common requirement. This article will introduce how to use Go’s Excelize library to generate Excel documents. As well as code implementation in some specific scenarios.

About Excelize library

Excelize is a basic library written in Go language for operating Office Excel documents, based on ECMA-376, ISO/IEC 29500 international standard. You can use it to read and write spreadsheet documents created with Microsoft Excel™ 2007 and above. It supports multiple document formats such as XLSX / XLSM / XLTM / Data workbook. It can be applied to various reporting platforms, cloud computing, edge computing and other systems. Using this library requires Go language version 1.15 or higher.

Performance comparison

The following figure shows the performance comparison of some major open source Excel libraries when generating a 12800*50 plain text matrix (OS: macOS Mojave version 10.14.4, CPU: 3.4 GHz Intel Core i5, RAM: 16 GB 2400 MHz DDR4, HDD: 1 TB), including Go, Python, Java, PHP and NodeJS.

Installation

The latest version is v2.4.0:

go get github.com/360EntSecGroup-Skylar/excelize/v2

Create Excel document

In the following case, we created an Excel document and used the NewSheet method to create a new Sheet2 worksheet. Sheet1 is the worksheet created by default. , and then we use the SetCellValue method to set the A2 cells of the Sheet2 worksheet and the B2 of the Sheet1 table respectively. Set the value of the cell, set the Sheet2 worksheet as the default worksheet by using the SetActiveSheet method, and finally call the SaveAs method to write the data into the Excel document:

package main

import (
    "fmt"

    "github.com/360EntSecGroup-Skylar/excelize/v2"
)

func main() {
    f := excelize.NewFile()
    // 创建一个工作表
    index := f.NewSheet("Sheet2")
    // 设置单元格的值
    f.SetCellValue("Sheet2", "A2", "Hello world.")
    f.SetCellValue("Sheet1", "B2", 100)
    // 设置工作簿的默认工作表
    f.SetActiveSheet(index)
    // 根据指定路径保存文件
    if err := f.SaveAs("Book1.xlsx"); err != nil {
        fmt.Println(err)
    }
}

Reproduction of the actual scenario

Create a worksheet

The worksheet name is case-sensitive:

index := f.NewSheet("Sheet2")

Delete the worksheet created by default

The Excel document created by default contains a worksheet named Sheet1. We may not need this default worksheet. At this time, we can delete this worksheet:

f.DeleteSheet("Sheet1")

Merge cells

MergeSheet1 Cells in the F1:I2 range on the worksheet:

excel.MergeCell("Sheet1", "F1", "I2")

Cell style

Setting styles for cells is often encountered, such as setting the background color of cells. Excelize The library provides the following two methods to set cell styles (NewStyle and SetCellStyle):

// 通过给定的样式格式 JSON 或结构体的指针创建样式并返回样式索引。
// 请注意，颜色需要使用 RGB 色域代码表示。
style, err := f.NewStyle(`{
    "border": [
    {
        "type": "left",
        "color": "0000FF",
        "style": 3
    },
    {
        "type": "top",
        "color": "00FF00",
        "style": 4
    },
    {
        "type": "bottom",
        "color": "FFFF00",
        "style": 5
    },
    {
        "type": "right",
        "color": "FF0000",
        "style": 6
    },
    {
        "type": "diagonalDown",
        "color": "A020F0",
        "style": 7
    },
    {
        "type": "diagonalUp",
        "color": "A020F0",
        "style": 8
    }]
}`)
if err != nil {
    fmt.Println(err)
}
err = f.SetCellStyle("Sheet1", "D7", "D7", style)

Horizontal centering of text

Horizontal centering of text requires the use of Alignment Style structure:

type Alignment struct {
    Horizontal      string `json:"horizontal"`
    Indent          int    `json:"indent"`
    JustifyLastLine bool   `json:"justify_last_line"`
    ReadingOrder    uint64 `json:"reading_order"`
    RelativeIndent  int    `json:"relative_indent"`
    ShrinkToFit     bool   `json:"shrink_to_fit"`
    TextRotation    int    `json:"text_rotation"`
    Vertical        string `json:"vertical"`
    WrapText        bool   `json:"wrap_text"`
}

Horizontal centering only needs to be set The value of Horizontal is center:

style, err := f.NewStyle(`{"alignment":{"horizontal":"center"}}`)
if err != nil {
    fmt.Println(err)
}
err = excel.SetCellStyle("Sheet1", "B1", "B1", style)

Set a solid color fill for the cell

will be used to fill the cell with color Fill Style structure:

type Fill struct {
    Type    string   `json:"type"`
    Pattern int      `json:"pattern"`
    Color   []string `json:"color"`
    Shading int      `json:"shading"`
}

Style structure

From the code to set the style above, we can find that border is an array, and alignment is a structure, which is determined by the Style structure:

type Style struct {
    Border        []Border    `json:"border"`
    Fill          Fill        `json:"fill"`
    Font          *Font       `json:"font"`
    Alignment     *Alignment  `json:"alignment"`
    Protection    *Protection `json:"protection"`
    NumFmt        int         `json:"number_format"`
    DecimalPlaces int         `json:"decimal_places"`
    CustomNumFmt  *string     `json:"custom_number_format"`
    Lang          string      `json:"lang"`
    NegRed        bool        `json:"negred"`
}