utils/excelutils/excelutils.go

package excelutils

import (
	"bytes"
	"fmt"
	"github.com/pkg/errors"
	"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, err := generateExcelColumnIndexes(length)
	if err != nil {
		return nil, err
	}

	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
}

func generateExcelColumnIndexes(columnNum int) ([]string, error) {
	if columnNum > 26*27 {
		return nil, errors.New("columnNum is too large")
	}

	indexes := make([]string, 0)

	for currentColumn := 1; currentColumn <= columnNum; currentColumn++ {
		var index string

		if currentColumn%26 == 0 {
			index = "Z"
		} else {
			index = string(rune('A' + currentColumn%26 - 1))
		}

		if currentColumn <= 26 {
			indexes = append(indexes, index)
			continue
		}

		upperLetterIndex := currentColumn / 26
		if upperLetterIndex%26 == 0 && currentColumn != 26*26 {
			index = "Z" + index
		} else {
			if currentColumn%26 == 0 {
				index = string(rune('A'+upperLetterIndex-2)) + "Z"
			} else {
				index = string(rune('A'+upperLetterIndex%26-1)) + index
			}
		}

		indexes = append(indexes, index)
	}

	return indexes, nil
}