base-golang/pkg/excel/export.go

112 lines
2.7 KiB
Go
Raw Permalink Normal View History

2024-07-23 10:23:43 +08:00
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, err := f.NewSheet(sheetName)
if err != nil {
return err
}
_ = 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
}