go将mysql表内容转换为excel特定的格式并以reader返回

package rearinstallothercostdata

import (
	"io"
	"sort"

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

var datePoint int

// ShowExcelTable4直接显示表4
func ShowExcelTable4(response *roimodel.RearInstallOtherCostResonse) (buff io.Reader, err error) {
	file := excelize.NewFile()

	productSlice := []string{"ss", "ff", "gg"}

	for _, sheetName := range productSlice {
		index, _ := file.NewSheet(sheetName)
		file.SetActiveSheet(index)
		yearMonthIndex := writeTable4Date(file, response, sheetName) //将日期写入到表4并返回年月对应的位置索引

		writeTable4Title(file, sheetName) //将表4标题写入到表4

		writeTable4Rows(file, response, sheetName, yearMonthIndex) //将数据写入到表4

		//从第3行第1列开始冻结
		_ = file.SetPanes(sheetName, &excelize.Panes{
			Freeze:      true,
			Split:       false,
			XSplit:      1,
			YSplit:      3,
			TopLeftCell: "B4",
		})

	}

	//先删除默认的sheet1
	err = file.DeleteSheet("Sheet1")
	if err != nil {
		return
	}
	buff, _ = file.WriteToBuffer()

	return
}

// writeTable4Rows 将数据写入到表4
func writeTable4Rows(file *excelize.File, response *roimodel.RearInstallOtherCostResonse, sheetName string,
	yearMonthIndex map[string]int) {

	styleFmt4, _ := file.NewStyle(&excelize.Style{
		Alignment: &excelize.Alignment{
			Horizontal: "right",
			Vertical:   "center",
		},
		//加粗
		Font: &excelize.Font{Bold: true, Color: "#000000"},
	})
	for _, data := range response.Data {
		if data.Product != sheetName {
			continue
		}
		row := []interface{}{data.ExchangeRate, data.Rev, data.Profit, data.Channel, data.Cost, data.Dau, data.RMBCost, data.USDCost}

		for i, v := range row {
			cell, _ := excelize.CoordinatesToCellName(yearMonthIndex[data.Date], 4+i)
			_ = file.SetCellValue(sheetName, cell, v)

			_ = file.SetCellStyle(sheetName, cell, cell, styleFmt4)
		}
	}

	style2, _ := file.NewStyle(&excelize.Style{
		Alignment: &excelize.Alignment{
			Horizontal: "right",
			Vertical:   "center",
		},
		//加粗
		Font: &excelize.Font{Bold: true},
		//黄色背景
		Fill: excelize.Fill{Type: "pattern", Color: []string{"#FAD94A"}, Pattern: 1},
	})
	cell, _ := excelize.CoordinatesToCellName(1, 11)
	cell2, _ := excelize.CoordinatesToCellName(datePoint+1, 11)
	_ = file.SetCellStyle(sheetName, cell, cell2, style2)
}

// writeTable4Title 写入表4表头
func writeTable4Title(file *excelize.File, sheetName string) {

	style, _ := file.NewStyle(&excelize.Style{
		Alignment: &excelize.Alignment{
			Horizontal: "right",
			Vertical:   "center",
		},
		//加粗
		Font: &excelize.Font{Bold: true},
	})

	moveRowsCountry := 4
	fields := []string{"汇率", "收入", "利润", "渠道", "其他成本", "DAU", "RMB单户其他成本", "USD单户其他成本"}

	for ci, field := range fields {
		cell, _ := excelize.CoordinatesToCellName(1, moveRowsCountry+ci)
		_ = file.SetCellValue(sheetName, cell, field)
		//设置字体居中
		_ = file.SetCellStyle(sheetName, cell, cell, style)
	}
	cell, _ := excelize.ColumnNumberToName(datePoint + 1)
	_ = file.SetColWidth(sheetName, "A", cell, 25)
}

// writeTable4Date 将日期写入到表4
func writeTable4Date(file *excelize.File, response *roimodel.RearInstallOtherCostResonse, sheetName string) (yearMonthIndex map[string]int) {
	yearMonthIndex = make(map[string]int)
	var cell, cell2 string
	var dateBaseMove = 2 //日期列偏移量
	cell, _ = excelize.CoordinatesToCellName(1, 1)
	_ = file.SetCellValue(sheetName, cell, "天数")
	cell, _ = excelize.CoordinatesToCellName(1, 2)
	_ = file.SetCellValue(sheetName, cell, "年份")
	cell, _ = excelize.CoordinatesToCellName(1, 3)
	_ = file.SetCellValue(sheetName, cell, "月份")

	style, _ := file.NewStyle(&excelize.Style{
		Alignment: &excelize.Alignment{
			Horizontal: "center",
		},
		//加粗
		Font: &excelize.Font{Bold: true, Color: "#000000"},
		//背景色
		Fill: excelize.Fill{Type: "pattern", Color: []string{"#f2f2f2"}, Pattern: 1},
	})

	yearMonths, yearIndex := getAllYearMonths(response, sheetName)
	datePoint = 0 //每个sheet的日期列开始位置
	for _, year := range yearIndex {
		//合并单元格
		cell, _ = excelize.CoordinatesToCellName(datePoint+dateBaseMove, 2)
		cell2, _ = excelize.CoordinatesToCellName(datePoint+len(yearMonths[year])+dateBaseMove-1, 2)
		_ = file.MergeCell(sheetName, cell, cell2)
		_ = file.SetCellValue(sheetName, cell, year)

		for _, month := range yearMonths[year] {
			monthDays, _ := utils.GetMonthDays(month, "200601")
			cell, _ = excelize.CoordinatesToCellName(datePoint+dateBaseMove, 1)
			_ = file.SetCellValue(sheetName, cell, monthDays)
			cell, _ = excelize.CoordinatesToCellName(datePoint+dateBaseMove, 3)
			yearMonthIndex[month] = datePoint + dateBaseMove
			datePoint++
			_ = file.SetCellValue(sheetName, cell, month[2:])
		}
	}
	cell, _ = excelize.CoordinatesToCellName(1, 2)
	cell2, _ = excelize.CoordinatesToCellName(datePoint+dateBaseMove, 3)
	//设置字体居中
	_ = file.SetCellStyle(sheetName, cell, cell2, style)
	return yearMonthIndex
}

// getAllYearMonths 获取所有年份月份
func getAllYearMonths(response *roimodel.RearInstallOtherCostResonse, sheetName string) (yearMonths map[string][]string, yearIndex []string) {
	// 将[]string{"202201","202202","202203","202301","202302","202303"}变为map[string][]string{"2022":
	// ["202201","202202","202203"],"2023":["202301","202302","202303"]}
	yearMonths = make(map[string][]string)
	for _, v := range response.Data {
		if v.Product != sheetName {
			continue
		}
		if v.Date == "汇总" {
			continue
		}
		y := v.Date[:4] + "年"
		if _, ok := yearMonths[y]; !ok {
			yearMonths[y] = []string{v.Date}
			yearIndex = append(yearIndex, y)
		} else {
			yearMonths[y] = utils.SliceAppendIfNotExist(yearMonths[y], v.Date)
		}
	}
	for _, v := range yearMonths {
		sort.Slice(v, func(i, j int) bool {
			return v[i] < v[j]
		})
	}
	// yearIndex排序
	sort.Slice(yearIndex, func(i, j int) bool {
		return yearIndex[i] < yearIndex[j]
	})
	return
}