base-golang/pkg/excel/export.go

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
}