文章目录

  • 1 Excelize介绍
  • 2 相关需求与实现
    • 2.1 数据的excel文件导出
    • 2.2 带数据校验的excel文件导出

1 Excelize介绍

  • Excelize 是 Go 语言编写的用于操作 Office Excel 文档基础库。官方文档：https://xuri.me/excelize/zh-hans/

  • 引入方法

go get "github.com/xuri/excelize/v2"

2 相关需求与实现

2.1 数据的excel文件导出

  • 需求：入参是一个map，其键是每个sheet页的名字，其值是一个二维切片。在每个sheet页中依次写入切片中的数据。返回文件名和错误信息。
  • 代码：
// 写入数据到export/*.xlsx// datas: map[sheet]每个sheet页的数据// return: 文件名, errorfunc WriteExcel(datas map[string][][]string) (string, error) {file := excelize.NewFile()for sheetName, data := range datas {index, _ := file.NewSheet(sheetName)for i, row := range data {for j, val := range row {// 列行数字索引转excel坐标索引cellName, _ := excelize.CoordinatesToCellName(j+1, i+1)//fmt.Println("cellName:", cellName)// 设置，写入file.SetCellValue(sheetName, cellName, val)}}// 创建表格file.SetActiveSheet(index)}filename := "aaa" + ".xlsx"_, err := os.ReadDir("aaa/")if err != nil {// 不存在就创建err = os.MkdirAll("aaa/", fs.ModePerm)if err != nil {fmt.Println(err)}}// 删除默认工作表file.DeleteSheet("Sheet1")err = file.SaveAs("aaa/" + filename)if err != nil {return "", err}return filename, nil}func main() {datas := make(map[string][][]string)datas["xxx"] = [][]string{{"A1", "B1", "C1", "d1", "E1", "G1", "H1", "I1"}, {"A2", "B2", "C2"}, {"A3", "B3", "C3"}}datas["yyy"] = [][]string{{"X1", "Y1", "Z1"}, {"X2", "Y2", "Z2"}, {"X3", "Y3", "Z3"}}fileName, err := WriteExcel(datas)if err != nil {fmt.Println("Write excel error: ", err)return}fmt.Println("Write excel success, file name is: ", fileName)
  • 输出文件：

2.2 带数据校验的excel文件导出

  • 需求：入参是一个map，其键是每个sheet页的名字，其值是一个二维切片。在每个sheet页中依次写入切片中的数据。返回文件名和错误信息。
    数据校验：A列只能输入a,aa,aaa,aaaa；B列只能输入b,bb；C列只能输入c,cc,ccc。
  • 代码：
func WriteExcelValidation(datas map[string][][]string, validations map[string][]string) (string, error) {file := excelize.NewFile()for sheetName, data := range datas {// 创建sheetindex, _ := file.NewSheet(sheetName)// 有效性校验for col, validation := range validations {dv := excelize.NewDataValidation(true)dv.SetSqref(col + "2:" + col + "1048576")err := dv.SetDropList(validation)if err != nil {return "", err}file.AddDataValidation(sheetName, dv)}for i, row := range data {for j, val := range row {// 列行数字索引转excel坐标索引cellName, _ := excelize.CoordinatesToCellName(j+1, i+1)// 设置，写入file.SetCellValue(sheetName, cellName, val)}}// 创建表格file.SetActiveSheet(index)}filename := "bbb" + ".xlsx"// 创建目录_, err := os.ReadDir("aaa/")if err != nil {// 不存在就创建err = os.MkdirAll("aaa/", fs.ModePerm)if err != nil {return "", err}}file.DeleteSheet("Sheet1")err = file.SaveAs("aaa/" + filename)if err != nil {return "", err}return filename, nil}func main() {validations := make(map[string][]string)validations["A"] = []string{"a", "aa", "aaa", "aaaa"}validations["B"] = []string{"b", "bb"}validations["E"] = []string{"c", "cc", "ccc"}datas := make(map[string][][]string)datas["xxx"] = [][]string{{"A1", "B1", "C1", "d1", "E1", "G1", "H1", "I1"}, {"A2", "B2", "C2"}, {"A3", "B3", "C3"}}datas["yyy"] = [][]string{{"X1", "Y1", "Z1"}, {"X2", "Y2", "Z2"}, {"X3", "Y3", "Z3"}}fileName, err := WriteExcelValidation(datas, validations)if err != nil {fmt.Println("Write excel error: ", err)return}fmt.Println("Write excel success, file name is: ", fileName)}
  • 输出文件：