utils/excelutils/excelutils.go

package excelutils

import (
	"bytes"
	"fmt"
	"git.sxidc.com/go-tools/utils/strutils"
	"github.com/xuri/excelize/v2"
	"log"
	"reflect"
	"strconv"
)

func ExportExcel(titleList []string, data []interface{}, sheetName string) ([]byte, error) {
	f := excelize.NewFile()
	defer func() {
		err := f.Close()
		if err != nil {
			log.Println("ExportExcel Close File:", err.Error())
		}
	}()

	err := f.SetSheetName("Sheet1", sheetName)
	if err != nil {
		return nil, err
	}

	header := make([]string, 0)
	for _, v := range titleList {
		header = append(header, v)
	}

	rowStyleID, _ := f.NewStyle(&excelize.Style{
		Font: &excelize.Font{
			Family: "arial",
			Size:   13,
			Color:  "#666666",
		},
		Alignment: &excelize.Alignment{
			Horizontal: "center",
			Vertical:   "center",
		},
	})

	err = f.SetSheetRow(sheetName, "A1", &header)
	if err != nil {
		return nil, err
	}

	err = f.SetRowHeight(sheetName, 1, 30)
	if err != nil {
		return nil, err
	}

	length := len(titleList)
	headStyle := strutils.GenerateUpperLetters(length)
	var lastRow string
	var widthRow string
	for k, v := range headStyle {
		if k == length-1 {
			lastRow = fmt.Sprintf("%s1", v)
			widthRow = v
		}
	}

	if err := f.SetColWidth(sheetName, "A", widthRow, 20); err != nil {
		return nil, err
	}

	rowNum := 1
	_ = f.SetCellStyle(sheetName, fmt.Sprintf("A%d", 1), fmt.Sprintf("%s", lastRow), rowStyleID)
	for _, v := range data {
		t := reflect.TypeOf(v)
		value := reflect.ValueOf(v)
		row := make([]interface{}, 0)

		for l := 0; l < t.NumField(); l++ {
			val := value.Field(l).Interface()
			row = append(row, val)
		}

		rowNum++

		err := f.SetSheetRow(sheetName, "A"+strconv.Itoa(rowNum), &row)
		if err != nil {
			return nil, err
		}
	}

	buffer := &bytes.Buffer{}
	err = f.Write(buffer)
	if err != nil {
		return nil, err
	}

	return buffer.Bytes(), nil
}