Cold_Logistic/internal/pkg/utils/excelutil/create.go

package excelutil

import (
	"bytes"
	"context"
	"fmt"
	"github.com/xuri/excelize/v2"
	"io"
	"time"

	"gogs.baozhida.cn/Cold_Logistic_libs/pkg/contrib/errors"
)

func ExportExcel(ctx context.Context, titles []interface{}, values [][]interface{}, cellFunc ExcelCellOptionFunc, opts ...ExcelOptionFunc) (io.Reader, error) {
	if len(titles) == 0 {
		return nil, errors.New("表头为空")
	}

	file := excelize.NewFile()
	defer func() {
		if err := file.Close(); err != nil {
			fmt.Println(err)
		}
	}()

	streamWriter, err := file.NewStreamWriter("Sheet1")
	if err != nil {
		return nil, errors.WithStack(err)
	}
	for _, opt := range opts {
		if err = opt(file, streamWriter); err != nil {
			return nil, err
		}
	}

	// 写入标题
	titleStyleID, err := getDefaultTitleStyleId(file)
	if err != nil {
		return nil, errors.WithStackOnce(err)
	}
	for i, v := range titles {
		cell := excelize.Cell{}
		if val, ok := v.(excelize.Cell); ok {
			cell = val
		} else {
			cell = excelize.Cell{StyleID: titleStyleID, Value: v}
		}
		if cellFunc != nil {
			if err = cellFunc(0, i, &cell); err != nil {
				return nil, errors.WithStack(err)
			}
		}
		titles[i] = cell
	}
	if err = streamWriter.SetRow("A1", titles, excelize.RowOpts{Height: 20}); err != nil {
		return nil, errors.WithStack(err)
	}

	// 写入内容
	valueStyleID, err := GetDefaultValueStyleId(file)
	if err != nil {
		return nil, errors.WithStackOnce(err)
	}
	timeStyleID, err := getDefaultTimeStyleId(file)
	if err != nil {
		return nil, errors.WithStackOnce(err)
	}

	for rowID := 0; rowID < len(values); rowID++ {
		row := values[rowID]

		for i, v := range row {
			cell := excelize.Cell{}
			if val, ok := v.(excelize.Cell); ok {
				cell = val
			} else if _, ok := v.(time.Time); ok {
				cell = excelize.Cell{StyleID: timeStyleID, Value: v}
			} else {
				cell = excelize.Cell{StyleID: valueStyleID, Value: v}
			}
			if cellFunc != nil {
				if err = cellFunc(rowID+1, i, &cell); err != nil {
					return nil, errors.WithStack(err)
				}
			}
			row[i] = cell
		}

		cell, _ := excelize.CoordinatesToCellName(1, rowID+2)
		if err := streamWriter.SetRow(cell, row); err != nil {
			return nil, errors.WithStack(err)
		}
	}

	if err := streamWriter.Flush(); err != nil {
		return nil, errors.WithStack(err)
	}

	buf := bytes.NewBuffer([]byte{})
	if err = file.Write(buf); err != nil {
		return nil, errors.WithStack(err)
	}

	return buf, nil
}

func getDefaultTitleStyleId(file *excelize.File) (int, error) {
	styleID, err := file.NewStyle(&excelize.Style{
		// 标题加粗
		Font: &excelize.Font{
			Bold:  true,
			Color: "FFFFFF",
		},
		// 设置边框
		Border: []excelize.Border{
			{Type: "left", Color: "000000", Style: 1},
			{Type: "top", Color: "000000", Style: 1},
			{Type: "bottom", Color: "000000", Style: 1},
			{Type: "right", Color: "000000", Style: 1},
		},
		// 图案填充-绿色
		Fill: excelize.Fill{
			Pattern: 1,
			Type:    "pattern",
			Color:   []string{"2EA121"},
		},
		// 居中
		Alignment: &excelize.Alignment{
			// 水平对齐：
			Horizontal: "center",
			// 垂直对齐
			Vertical: "center",
		},
	})
	return styleID, err
}

func GetYellowStyleId(file *excelize.File) (int, error) {
	styleID, err := file.NewStyle(&excelize.Style{
		// 标题加粗
		Font: &excelize.Font{
			Bold:  true,
			Color: "FFFFFF",
		},
		// 设置边框
		Border: []excelize.Border{
			{Type: "left", Color: "000000", Style: 1},
			{Type: "top", Color: "000000", Style: 1},
			{Type: "bottom", Color: "000000", Style: 1},
			{Type: "right", Color: "000000", Style: 1},
		},
		// 图案填充-黄色
		Fill: excelize.Fill{
			Pattern: 1,
			Type:    "pattern",
			Color:   []string{"FFC60A"},
		},
		// 居中
		Alignment: &excelize.Alignment{
			// 水平对齐：
			Horizontal: "center",
			// 垂直对齐
			Vertical: "center",
		},
	})
	return styleID, err
}

func GetDefaultValueStyleId(file *excelize.File) (int, error) {
	styleID, err := file.NewStyle(&excelize.Style{
		// 设置边框
		//Border: []excelize.Border{
		//	{Type: "left", Color: "000000", Style: 1},
		//	{Type: "top", Color: "000000", Style: 1},
		//	{Type: "bottom", Color: "000000", Style: 1},
		//	{Type: "right", Color: "000000", Style: 1},
		//},
		// 居中
		Alignment: &excelize.Alignment{
			// 水平对齐：
			Horizontal: "center",
			// 垂直对齐
			Vertical: "center",
		},
	})
	return styleID, err
}

func getDefaultTimeStyleId(file *excelize.File) (int, error) {
	styleID, err := file.NewStyle(&excelize.Style{
		// 设置边框
		Border: []excelize.Border{
			{Type: "left", Color: "000000", Style: 1},
			{Type: "top", Color: "000000", Style: 1},
			{Type: "bottom", Color: "000000", Style: 1},
			{Type: "right", Color: "000000", Style: 1},
		},
		// 居中
		Alignment: &excelize.Alignment{
			// 水平对齐：
			Horizontal: "center",
			// 垂直对齐
			Vertical: "center",
		},
		NumFmt: 22,
	})
	return styleID, err
}

type ExcelOptionFunc func(*excelize.File, *excelize.StreamWriter) error

type ExcelCellOptionFunc func(rowIndex, cellIndex int, cell *excelize.Cell) error