copter-train/src/api/userExcel.go

package api

import (
	"copter-train/db/model"
	"fmt"
	"strings"

	"github.com/xuri/excelize/v2"
)

type UserExcel struct {
	Offset           int
	Row              int
	Title            string //标题
	Excel            *excelize.File
	SheetName        string
	AlignCenterStyle int
	Content          []*model.User
	RowMap           map[string]int
	RowWidthArray    []float64
	RowsHeightArray  []map[int]float64
}

// 批量设置行高
func (b *UserExcel) setRowsHeight() {
	for _, rowHeight := range b.RowsHeightArray {
		for row, height := range rowHeight {
			b.Excel.SetRowHeight(b.SheetName, row, height)
		}
	}

}

// 获取范围内单元格的宽度 A:F
func (b *UserExcel) getRangeWidth(r string) float64 {
	rg := strings.Split(r, ":")

	if len(rg) == 1 {
		start := b.RowMap[rg[0]]
		return b.RowWidthArray[start]
	} else if len(rg) == 2 {
		start := b.RowMap[rg[0]]
		end := b.RowMap[rg[1]]
		rowr := b.RowWidthArray[start : end+1]
		width := 0.0
		for _, v := range rowr {
			width += v
		}
		return width
	}
	return 0.0
}

func (b *UserExcel) drawTitle() error {
	b.Row++
	startCell := fmt.Sprintf("A%d", b.Row)
	endCell := fmt.Sprintf("D%d", b.Row)

	b.RowMap = map[string]int{"A": 0, "B": 1, "C": 2, "D": 3}

	b.RowWidthArray = []float64{12, 12, 12, 36}
	b.Excel.SetColWidth(b.SheetName, "A", "A", 12)
	b.Excel.SetColWidth(b.SheetName, "B", "B", 12)
	b.Excel.SetColWidth(b.SheetName, "C", "C", 12)
	b.Excel.SetColWidth(b.SheetName, "D", "D", 36)

	err := b.Excel.MergeCell(b.SheetName, startCell, endCell)
	if err != nil {
		return err
	}

	style, err := b.Excel.NewStyle(&excelize.Style{
		Alignment: &excelize.Alignment{Horizontal: "center", Vertical: "center"},
		Font:      &excelize.Font{Bold: true, Size: 18}})
	if err != nil {
		return err
	}
	err = b.Excel.SetCellStyle(b.SheetName, startCell, startCell, style)
	if err != nil {
		return err
	}
	b.Excel.SetRowHeight(b.SheetName, b.Row, 26)

	b.Excel.SetCellValue(b.SheetName, startCell, b.Title)
	return nil
}

func (b *UserExcel) drawTableTitle() error {
	b.Row++
	var drawCol = func(prefix string, value string) error {
		cell := fmt.Sprintf("%s%d", prefix, b.Row)
		err := b.Excel.SetCellStyle(b.SheetName, cell, cell, b.AlignCenterStyle)
		if err != nil {
			return err
		}

		return b.Excel.SetCellValue(b.SheetName, cell, value)
	}

	drawCol("A", "用户名")
	drawCol("B", "编号")
	drawCol("C", "角色")
	drawCol("D", "密码")
	b.Excel.SetRowHeight(b.SheetName, b.Row, 22)

	return nil
}

func (b *UserExcel) drawTableContent() error {
	b.Row++
	var DrawRow = func(rowIndex int, values ...string) float64 {
		charas := []string{"A", "B", "C", "D"}
		// 获取该行最大行高
		max := getRowHeight(values[0], b.getRangeWidth(charas[0]))
		for i, c := range charas {
			v := ""
			if i < len(values) {
				v = values[i]
			}
			b.Excel.SetCellValue(b.SheetName, fmt.Sprintf("%s%d", c, rowIndex), v)
			val2Cel := fmt.Sprintf("%s%d", c, rowIndex)
			b.Excel.SetCellStyle(b.SheetName, val2Cel, val2Cel, b.AlignCenterStyle)

			if getRowHeight(v, b.getRangeWidth(c)) > max {
				max = getRowHeight(v, b.getRangeWidth(c))
			}
		}
		return max
	}

	users := b.Content
	if len(users) > 0 {
		for _, user := range users {
			rowMaxHeight := DrawRow(b.Row, user.Name, user.Nid, user.Roles[0], user.Password)
			b.RowsHeightArray = append(b.RowsHeightArray, map[int]float64{b.Row: rowMaxHeight})
			b.Row++
		}
	}

	return nil
}

func (b *UserExcel) Draws() {
	b.drawTitle()

	b.drawTableTitle()
	b.drawTableContent()
	// 设置行高
	b.setRowsHeight()
}

func NewUserExcel(f *excelize.File) *UserExcel {

	border := []excelize.Border{
		{Type: "top", Style: 1, Color: "000000"},
		{Type: "left", Style: 1, Color: "000000"},
		{Type: "right", Style: 1, Color: "000000"},
		{Type: "bottom", Style: 1, Color: "000000"},
	}

	styleLeft, _ := f.NewStyle(&excelize.Style{
		Alignment: &excelize.Alignment{Horizontal: "center", Vertical: "center", WrapText: true},
		Border:    border,
	})

	b := &UserExcel{
		Title:            "用户信息",
		SheetName:        "Sheet1",
		Excel:            f,
		Offset:           0,
		AlignCenterStyle: styleLeft,
		RowMap:           map[string]int{"A": 0, "B": 1, "C": 2, "D": 3},
		RowWidthArray:    []float64{12, 12, 12, 20},
		RowsHeightArray:  make([]map[int]float64, 0),
	}

	// f.SetPageMargins(b.SheetName, excelize.PageMarginTop(1), excelize.PageMarginLeft(0), excelize.PageMarginRight(0))

	return b
}

func (b *UserExcel) FormatToEmpty(str *string) {
	if *str == "0" || *str == "0.000" {
		*str = "-"
	}

}