使用golang生成xlsx文件

在处理数据的时候经常会用到这个功能，主要代码如下，包含了常见的格式设置。

package main

import (
    "fmt"
    "log"

    "github.com/xuri/excelize/v2"
)

func main() {
    // 创建一个新的 Excel 文件
    f := excelize.NewFile()

    // 设置标题的字体样式
    titleStyle := &excelize.Style{
        Font: &excelize.Font{
            Bold:  true,
            Size:  14,
            Color: "FFFFFF",
        },
        Fill: excelize.Fill{
            Type:    "pattern",
            Color:   []string{"4BACC6"},
            Pattern: 1,
        },
        Alignment: &excelize.Alignment{
            Horizontal: "center",
            Vertical:   "center",
        },
        Border: []excelize.Border{
            {Type: "left", Color: "000000", Style: 1},
            {Type: "top", Color: "000000", Style: 1},
            {Type: "right", Color: "000000", Style: 1},
            {Type: "bottom", Color: "000000", Style: 1},
        },
    }

    // 设置单元格样式
    cellStyle := &excelize.Style{
        Font: &excelize.Font{
            Size:  11,
            Color: "000000",
        },
        Alignment: &excelize.Alignment{
            Horizontal: "center",
            Vertical:   "center",
        },
        Border: []excelize.Border{
            {Type: "left", Color: "000000", Style: 1},
            {Type: "top", Color: "000000", Style: 1},
            {Type: "right", Color: "000000", Style: 1},
            {Type: "bottom", Color: "000000", Style: 1},
        },
    }

    // 创建样式ID
    titleStyleID, err := f.NewStyle(titleStyle)
    if err != nil {
        log.Fatalf("Failed to create title style: %s", err)
    }

    cellStyleID, err := f.NewStyle(cellStyle)
    if err != nil {
        log.Fatalf("Failed to create cell style: %s", err)
    }

    // 添加标题
    title := []string{"ID", "Name", "Age", "Email"}
    for i, v := range title {
        cell := fmt.Sprintf("ABCDEFGHIJKLMNOPQRSTUVWXYZ"[:len(title)][i:i+1] + "1")
        f.SetCellValue("Sheet1", cell, v)
        f.SetCellStyle("Sheet1", cell, cell, titleStyleID)
    }

    // 添加示例数据
    data := [][]string{
        {"1", "John Doe", "28", "john.doe@example.com"},
        {"2", "Jane Smith", "32", "jane.smith@example.com"},
        {"3", "Alice Johnson", "25", "alice.johnson@example.com"},
    }

    for i, row := range data {
        for j, v := range row {
            cell := fmt.Sprintf("ABCDEFGHIJKLMNOPQRSTUVWXYZ"[:len(title)][j:j+1]+"%d", i+2)
            f.SetCellValue("Sheet1", cell, v)
            f.SetCellStyle("Sheet1", cell, cell, cellStyleID)
        }
    }

    // 设置行高和列宽
    // 设置每一列的宽度为30
    for i := range title {
        col := string("ABCDEFGHIJKLMNOPQRSTUVWXYZ"[i])
        f.SetColWidth("Sheet1", col, col, 30)
    }
    for i := 0; i < len(data)+1; i++ {
        row := i + 1
        f.SetRowHeight("Sheet1", row, 20)
    }

    // 保存文件
    if err := f.SaveAs("example.xlsx"); err != nil {
        log.Fatalf("Failed to save file: %s", err)
    }

    fmt.Println("Excel file created successfully!")
}

 如果使用的是go 1.20版本(支持Win7的最后一个版本是1.20.14)，注意相关依赖库的版本如下：

module writexlsxdemo

go 1.20

require (
    github.com/mohae/deepcopy v0.0.0-20170929034955-c48cc78d4826 // indirect
    github.com/richardlehane/mscfb v1.0.4 // indirect
    github.com/richardlehane/msoleps v1.0.3 // indirect
    github.com/xuri/efp v0.0.0-20220603152613-6918739fd470 // indirect
    github.com/xuri/excelize/v2 v2.6.1
    github.com/xuri/nfp v0.0.0-20220409054826-5e722a1d9e22 // indirect
    golang.org/x/crypto v0.0.0-20220817201139-bc19a97f63c8 // indirect
    golang.org/x/net v0.0.0-20220812174116-3211cb980234 // indirect
    golang.org/x/text v0.3.7 // indirect
)