back/internal/api/manage/excel/create.go

package excel

import (
	"fmt"
	"github.com/360EntSecGroup-Skylar/excelize/v2"
	"strconv"
	"strings"
)

func CreateExcelFile(rows []*ExpressRow, newCompanList []string) *excelize.File {
	f := newExcelFile()
	pointLine := 4
	for _, r := range rows {
		store := ""
		switch r.Storehouse {
		case 1:
			store = "国内仓"
		case 2:
			store = "海外直邮"
		case 3:
			store = "保税仓"
		}
		_ = f.SetCellStyle(SheetName, axis("A", pointLine), axis(excelLetters[r.Length()], pointLine), commonStyle(f))
		_ = f.SetRowHeight(SheetName, pointLine, 21) // 设置行高度

		_ = f.SetCellValue(SheetName, axis("A", pointLine), fmt.Sprintf("%d", r.ID))
		_ = f.SetCellValue(SheetName, axis("B", pointLine), fmt.Sprintf("%d", r.OrderID))
		_ = f.SetCellValue(SheetName, axis("C", pointLine), r.BrandName)
		_ = f.SetCellValue(SheetName, axis("D", pointLine), r.VendorName)
		_ = f.SetCellValue(SheetName, axis("E", pointLine), r.PayTime.Time.Format("2006-01-02 15:04:05"))
		_ = f.SetCellValue(SheetName, axis("F", pointLine), r.ReceiverName)
		_ = f.SetCellValue(SheetName, axis("G", pointLine), r.Mobile)
		_ = f.SetCellValue(SheetName, axis("H", pointLine), r.Province)
		_ = f.SetCellValue(SheetName, axis("I", pointLine), r.City)
		_ = f.SetCellValue(SheetName, axis("J", pointLine), r.District)
		_ = f.SetCellValue(SheetName, axis("K", pointLine), r.Address)
		_ = f.SetCellValue(SheetName, axis("L", pointLine), r.BuyerMessage)
		_ = f.SetCellValue(SheetName, axis("M", pointLine), r.GoodsName)
		_ = f.SetCellValue(SheetName, axis("N", pointLine), r.SkuName)
		_ = f.SetCellValue(SheetName, axis("O", pointLine), r.SkuCode)
		_ = f.SetCellValue(SheetName, axis("P", pointLine), fmt.Sprintf("%d", r.Quantity))
		_ = f.SetCellValue(SheetName, axis("Q", pointLine), store)
		_ = f.SetCellValue(SheetName, axis("R", pointLine), "")
		_ = f.SetCellValue(SheetName, axis("S", pointLine), "")

		pointLine++
	}

	dvRange := excelize.NewDataValidation(true)
	dvRange.Sqref = "Q4"
	_ = dvRange.SetDropList(newCompanList)

	_ = f.AddDataValidation(SheetName, dvRange)

	index := f.NewSheet("物流公司列表")
	f.SetActiveSheet(index)
	pointLine = 1
	for _, info := range newCompanList {
		_ = f.SetCellValue("物流公司列表", axis("A", pointLine), info)
		pointLine++
	}
	return f
}

func axis(letter string, line int) string {
	return fmt.Sprintf("%v%d", letter, line)
}

func newExcelFile() *excelize.File {
	f := excelize.NewFile()
	{
		index := f.NewSheet(SheetName)
		// 设置工作簿的默认工作表
		f.SetActiveSheet(index)
		f.DeleteSheet("Sheet1")

		_ = f.SetRowHeight(SheetName, 1, 40) // 设置行高度
		_ = f.MergeCell(SheetName, "A1", "Z1")
		_ = f.SetCellStyle(SheetName, "A1", "R1", tableHeaderStyle(f))
		_ = f.SetCellValue(SheetName, "A1", "瑞库客-未发货订单汇总")

		_ = f.SetRowHeight(SheetName, 2, 90)
		_ = f.MergeCell(SheetName, "A2", "Z2")
		_ = f.SetCellStyle(SheetName, "A2", "Z2", tableTipsLineStyle(f))
		_ = f.SetCellValue(SheetName, "A2", excelDesc)

		_ = f.SetRowHeight(SheetName, 3, 25)
		_ = f.SetCellStyle(SheetName, "A3", "Z3", tableTitleLineStyle(f))
		for i, v := range excelColArray {
			array := strings.Split(v, "/")
			t := array[0]
			w, _ := strconv.Atoi(array[1])
			_ = f.SetCellValue(SheetName, fmt.Sprintf("%v3", excelLetters[i]), t)
			_ = f.SetColWidth(SheetName, excelLetters[i], excelLetters[i], float64(w))
		}
		_ = f.SetCellStyle(SheetName, "R3", "S3", specialTitleStyle(f))
	}
	return f
}

func specialTitleStyle(f *excelize.File) int {
	s := `{
			"fill":{
					"type":"pattern",
					"pattern":2,
					"color":["#00BFFF"]
			}, 
			"font":{
					"bold":true,
					"italic":false,
					"family":"宋体",
					"size":12,
					"color":"#ea0b1e"
			},
			"alignment":{
					"horizontal":"left",
					"vertical":"center",
					"wrap_text":true
			}
		}`

	style, _ := f.NewStyle(s)
	return style
}

func tableHeaderStyle(f *excelize.File) int {
	s := `{
			"fill":{
					"type":"pattern",
					"pattern":17,
					"color":["#3CB371"]
			}, 
			"font":{
					"bold":true,
					"italic":false,
					"family":"宋体",
					"size":20,
					"color":"#000000"
			},
			"alignment":{
					"horizontal":"left",
					"vertical":"center",
					"wrap_text":true
			}
		}`

	style, _ := f.NewStyle(s)
	return style
}

func tableTipsLineStyle(f *excelize.File) int {
	s := `{
			"fill":{
					"type":"pattern",
					"pattern":2,
					"color":["#FFE4B5"]
			}, 
			"font":{
					"bold":true,
					"italic":false,
					"family":"宋体",
					"size":10,
					"color":"#ea0b1e"
			},
			"alignment":{
					"horizontal":"left",
					"vertical":"center",
					"wrap_text":true
			}
		}`

	style, _ := f.NewStyle(s)
	return style
}

func tableTitleLineStyle(f *excelize.File) int {
	s := `{
			"fill":{
					"type":"pattern",
					"pattern":2,
					"color":["#00BFFF"]
			}, 
			"font":{
					"bold":true,
					"italic":false,
					"family":"宋体",
					"size":12,
					"color":"#000000"
			},
			"alignment":{
					"horizontal":"left",
					"vertical":"center",
					"wrap_text":true
			}
		}`

	style, _ := f.NewStyle(s)
	return style
}

func commonStyle(f *excelize.File) int {
	s := `{
			"font":{
					"bold":true,
					"italic":false,
					"family":"宋体",
					"size":12,
					"color":"#000000"
			},
			"alignment":{
					"horizontal":"left",
					"vertical":"center",
					"wrap_text":true
			}
		}`
	style, _ := f.NewStyle(s)
	return style
}

func dataStyle(f *excelize.File, idx int) int {
	d := idx % 2
	if d == 0 {
		s := `{
			"fill":{
					"type":"pattern",
					"pattern":2,
					"color":["#2E8B57"]
			}, 
			"font":{
					"bold":true,
					"italic":false,
					"family":"宋体",
					"size":12,
					"color":"#000000"
			},
			"alignment":{
					"horizontal":"left",
					"vertical":"center",
					"wrap_text":true
			}
		}`

		style, _ := f.NewStyle(s)
		return style
	} else {
		s := `{
			"fill":{
					"type":"pattern",
					"pattern":2,
					"color":["#FF3030"]
			}, 
			"font":{
					"bold":true,
					"italic":false,
					"family":"宋体",
					"size":12,
					"color":"#000000"
			},
			"alignment":{
					"horizontal":"left",
					"vertical":"center",
					"wrap_text":true
			}
		}`

		style, _ := f.NewStyle(s)
		return style
	}
}