This is the 7th day of my participation in Gwen Challenge.More article challenges

Generating Excel documents based on data is a common requirement, and this article will show you how to use Go’s Excelize library to generate Excel documents, as well as some code implementation in specific scenarios.

About Excelize library

Excelize is a basic Office Excel document library written in Go language. It is 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. Supports multiple document formats such as XLSX/XLSM/XLTM/XLTX, is highly compatible with documents with styles, images (tables), Pivottables, slicers and other complex components, and provides streaming read and write APIS for working with workbooks containing large amounts of data. It can be applied to various report platforms, cloud computing, edge computing and other systems. The Go language version 1.15 or higher is required to use this library.

The performance comparison

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

The installation

The latest version is V2.4.0:

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

Creating Excel Documents

In the following example, we created an Excel document and created a Sheet2 worksheet using the NewSheet method. Sheet1 is the default worksheet created. We then used the SetCellValue method to set values in the A2 cell of the Sheet2 worksheet and the B2 cell of the Sheet1 and set the Sheet2 worksheet to the default worksheet by using the SetActiveSheet method. Finally, the SaveAs method is called to write the data to an Excel document:

package main

import (
    "fmt"

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

func main(a) {
    f := excelize.NewFile()
    // Create a worksheet
    index := f.NewSheet("Sheet2")
    // Set the cell value
    f.SetCellValue("Sheet2"."A2"."Hello world.")
    f.SetCellValue("Sheet1"."B2".100)
    // Sets the default worksheet for the workbook
    f.SetActiveSheet(index)
    // Save the file in the specified path
    if err := f.SaveAs("Book1.xlsx"); err ! =nil {
        fmt.Println(err)
    }
}
Copy the code

The actual scenario is displayed

Creating a worksheet

Worksheet names are case sensitive:

index := f.NewSheet("Sheet2")
Copy the code

Delete the worksheet created by default

The default created Excel document contains a worksheet called Sheet1. We may not need the default worksheet, at which point we can delete it:

f.DeleteSheet("Sheet1")
Copy the code

Merged cell

Merge cells within the F1:I2 area on the Sheet1 worksheet:

excel.MergeCell("Sheet1"."F1"."I2")
Copy the code

Cell style

The Excelize library provides the following two methods for styling cells (NewStyle and SetCellStyle) :

// Create a style using a pointer to the given style format JSON or structure and return the style index.
// Please note that colors need to be represented using RGB gamut codes.
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 }] }`)
iferr ! =nil {
    fmt.Println(err)
}
err = f.SetCellStyle("Sheet1"."D7"."D7", style)
Copy the code

Text level center

Horizontal center text requires an 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"`
}
Copy the code

Set Horizontal to center:

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

Fill the cell with a solid color

Filling the cell with color uses the Fill style structure:

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

Style structure

From the styling code above, we can see that a border is an array and an alignment is a structure, as 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"`
}
Copy the code

Reference documentation

  • Excelize docs reference
  • Talks at Beijing Gopher Meetup