package excel import ( "github.com/xuri/excelize/v2" "strconv" ) const maxCharCount = 26 var DefaultColumnWidth float64 = 20 type ColumnOption struct { Field string Comment string Width float64 } func Export(sheetName, filepath string, columns []ColumnOption, rows []map[string]any) error { f := excelize.NewFile() sheetIndex := f.NewSheet(sheetName) f.DeleteSheet("Sheet1") _ = f.SetColWidth(sheetName, "A", string(byte('A'+len(columns)-1)), DefaultColumnWidth) contentStyle, _ := f.NewStyle(&excelize.Style{ Alignment: &excelize.Alignment{ Horizontal: "center", Vertical: "center", WrapText: true, }, }) titleStyle, _ := f.NewStyle(&excelize.Style{ Alignment: &excelize.Alignment{ Horizontal: "center", Vertical: "center", WrapText: true, }, Font: &excelize.Font{ Bold: true, Size: 14, }, }) maxColumnRowNameLen := 1 + len(strconv.Itoa(len(rows))) columnCount := len(columns) if columnCount > maxCharCount { maxColumnRowNameLen++ } else if columnCount > maxCharCount*maxCharCount { maxColumnRowNameLen += 2 } //标题 type columnItem struct { RowName []byte FieldName string } columnNames := make([]columnItem, 0) for index, column := range columns { columnName := getColumnName(index, maxColumnRowNameLen) if column.Width > 0 { _ = f.SetColWidth(sheetName, string(columnName), string(columnName), column.Width) } columnNames = append(columnNames, columnItem{FieldName: column.Field, RowName: columnName}) rowName := getColumnRowName(columnName, 1) err := f.SetCellValue(sheetName, rowName, column.Comment) if err != nil { return err } _ = f.SetCellStyle(sheetName, rowName, rowName, titleStyle) } //正文 for rowIndex, row := range rows { for _, item := range columnNames { rowName := getColumnRowName(item.RowName, rowIndex+2) err := f.SetCellValue(sheetName, rowName, row[item.FieldName]) if err != nil { return err } _ = f.SetCellStyle(sheetName, rowName, rowName, contentStyle) } } f.SetActiveSheet(sheetIndex) err := f.SaveAs(filepath) if err != nil { return err } return nil } func getColumnName(column, maxColumnRowNameLen int) []byte { const A = 'A' if column < maxCharCount { slice := make([]byte, 0, maxColumnRowNameLen) return append(slice, byte(A+column)) } else { return append(getColumnName(column/maxCharCount-1, maxColumnRowNameLen), byte(A+column%maxCharCount)) } } func getColumnRowName(columnName []byte, rowIndex int) (columnRowName string) { l := len(columnName) columnName = strconv.AppendInt(columnName, int64(rowIndex), 10) columnRowName = string(columnName) columnName = columnName[:l] return }