box-cost/boxcost/api/report-excel.go

package api

import (
	"box-cost/db/model"
	"box-cost/log"
	"encoding/json"
	"fmt"

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

type ReportExcel struct {
	Row              int
	Title            string //标题
	SupplierName     string //供应商名
	TimeRange        []string
	Excel            *excelize.File
	SheetName        string
	AlignCenterStyle int
	Content          []map[string]interface{}
}

func (b *ReportExcel) drawTitle() error {
	b.Row++
	// 设置外边距
	startCell := fmt.Sprintf("A%d", b.Row)
	endCell := fmt.Sprintf("I%d", b.Row)

	b.Excel.SetColWidth(b.SheetName, "A", "A", 10)
	b.Excel.SetColWidth(b.SheetName, "B", "B", 16)
	b.Excel.SetColWidth(b.SheetName, "C", "D", 20)
	b.Excel.SetColWidth(b.SheetName, "E", "H", 18)
	b.Excel.SetColWidth(b.SheetName, "I", "I", 35)

	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, 35)
	b.Excel.SetCellValue(b.SheetName, startCell, b.Title)
	return nil
}

func (b *ReportExcel) drawSubTitles() error {
	b.Row++

	styleLeft, err := b.Excel.NewStyle(&excelize.Style{
		Alignment: &excelize.Alignment{Horizontal: "left", Vertical: "center"},
		Font:      &excelize.Font{Size: 11}})
	if err != nil {
		return err
	}
	var drawLeft = func(rowIndex int, value string) error {
		left1Cell := fmt.Sprintf("A%d", rowIndex)
		err = b.Excel.MergeCell(b.SheetName, left1Cell, fmt.Sprintf("I%d", rowIndex))
		if err != nil {
			return err
		}
		err = b.Excel.SetCellStyle(b.SheetName, left1Cell, left1Cell, styleLeft)
		if err != nil {
			return err
		}
		b.Excel.SetCellValue(b.SheetName, left1Cell, value)
		return nil
	}
	timeString := ""
	if len(b.TimeRange) == 2 {
		timeString = fmt.Sprintf(" ( %s~%s ) ", b.TimeRange[0], b.TimeRange[1])
	}

	drawLeft(b.Row, "单位名称："+b.SupplierName+timeString)
	b.Excel.SetRowHeight(b.SheetName, b.Row, 35)
	return nil
}

func (b *ReportExcel) drawTableTitle() error {
	b.Row++
	var drawCol = func(prefix string, value string) error {
		left1Cell := fmt.Sprintf("%s%d", prefix, b.Row)
		left2Cell := fmt.Sprintf("%s%d", prefix, b.Row+1)

		err := b.Excel.MergeCell(b.SheetName, left1Cell, left2Cell)
		if err != nil {
			return err
		}
		err = b.Excel.SetCellStyle(b.SheetName, left1Cell, left2Cell, b.AlignCenterStyle)
		if err != nil {
			return err
		}

		return b.Excel.SetCellValue(b.SheetName, left1Cell, value)
	}
	drawCol("A", "序号")
	drawCol("B", "日期")
	drawCol("C", "采购或加工项目")
	drawCol("D", "规格")
	drawCol("E", "尺寸")
	drawCol("F", "数量")
	drawCol("G", "单价")
	drawCol("H", "金额")
	drawCol("I", "产品名称")
	// b.Excel.SetRowHeight(b.SheetName, b.Row, 35)

	return nil
}

func (b *ReportExcel) drawTableContent() error {
	b.Row += 2
	var DrawRow = func(rowIndex int, values ...string) {
		charas := []string{"A", "B", "C", "D", "E", "F", "G", "H", "I"}

		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)
			b.Excel.SetRowHeight(b.SheetName, rowIndex, 46)
		}
	}

	lists := b.Content

	if len(lists) > 0 {
		index := 0
		var totalPrice float64 = 0.00
		var totalCount int = 0
		for _, list := range lists {
			data, err := json.Marshal(list)
			if err != nil {
				log.Error(err)
				continue
			}
			if list["billType"] == "purchase" {
				purchase := model.PurchaseBill{}
				err = json.Unmarshal(data, &purchase)
				if err != nil {
					log.Error(err)
					continue
				}
				for _, paper := range purchase.Paper {
					index++
					completeTime := purchase.CompleteTime.Local().Format("2006-01-02")
					// 实际完成数
					realCount := fmt.Sprintf("%d", paper.ConfirmCount)
					b.FormatToEmpty(&realCount)

					// 实际金额
					realPrice := fmt.Sprintf("%.3f", paper.OrderPrice*float64(paper.ConfirmCount))
					b.FormatToEmpty(&realPrice)
					DrawRow(b.Row, fmt.Sprintf("%d", index), completeTime, paper.Name, paper.Norm, fmt.Sprintf("%s*%s", paper.Height, paper.Width), fmt.Sprintf("%d", paper.ConfirmCount), fmt.Sprintf("%.3f", paper.OrderPrice), realPrice, purchase.ProductName)
					totalPrice += paper.OrderPrice * float64(paper.ConfirmCount)
					totalCount += paper.ConfirmCount
					b.Row++
				}
			}
			if list["billType"] == "produce" {
				produce := model.ProduceBill{}
				err = json.Unmarshal(data, &produce)
				if err != nil {
					log.Error(err)
					continue
				}
				for _, pproduce := range produce.Produces {
					index++
					completeTime := produce.CompleteTime.Local().Format("2006-01-02")
					// 实际完成数
					realCount := fmt.Sprintf("%d", pproduce.ConfirmCount)
					b.FormatToEmpty(&realCount)

					// 实际金额
					realPrice := fmt.Sprintf("%.3f", pproduce.OrderPrice*float64(pproduce.ConfirmCount))
					b.FormatToEmpty(&realPrice)
					DrawRow(b.Row, fmt.Sprintf("%d", index), completeTime, pproduce.Name, pproduce.Norm, pproduce.PrintSize, fmt.Sprintf("%d", pproduce.ConfirmCount), fmt.Sprintf("%.3f", pproduce.OrderPrice), realPrice, produce.ProductName)
					totalPrice += pproduce.OrderPrice * float64(pproduce.ConfirmCount)
					totalCount += pproduce.ConfirmCount
					b.Row++
				}
			}
			if list["billType"] == "product" {
				product := model.ProductBill{}
				err = json.Unmarshal(data, &product)
				if err != nil {
					log.Error(err)
					continue
				}
				for _, pproduct := range product.Products {
					index++
					completeTime := product.CompleteTime.Local().Format("2006-01-02")
					// 实际完成数
					realCount := fmt.Sprintf("%d", pproduct.ConfirmCount)
					b.FormatToEmpty(&realCount)

					// 实际金额
					realPrice := fmt.Sprintf("%.3f", pproduct.OrderPrice*float64(pproduct.ConfirmCount))
					b.FormatToEmpty(&realPrice)
					DrawRow(b.Row, fmt.Sprintf("%d", index), completeTime, pproduct.Name, pproduct.Norm, "-", fmt.Sprintf("%d", pproduct.ConfirmCount), fmt.Sprintf("%.3f", pproduct.OrderPrice), realPrice, product.ProductName)
					totalPrice += pproduct.OrderPrice * float64(pproduct.ConfirmCount)
					totalCount += pproduct.ConfirmCount
					b.Row++
				}
			}

		}
		DrawRow(b.Row, "", "", "", "", "", "", "", "", "")
		startCell := fmt.Sprintf("%s%d", "A", b.Row)
		endCell := fmt.Sprintf("%s%d", "B", b.Row)
		FCell := fmt.Sprintf("%s%d", "F", b.Row)
		HCell := fmt.Sprintf("%s%d", "H", b.Row)
		b.Excel.MergeCell(b.SheetName, startCell, endCell)
		b.Excel.SetCellValue(b.SheetName, startCell, "合计")
		// 数量汇总
		b.Excel.SetCellValue(b.SheetName, FCell, totalCount)
		// 金额汇总
		b.Excel.SetCellValue(b.SheetName, HCell, fmt.Sprintf("%.3f", totalPrice))
	}

	return nil
}

func (b *ReportExcel) Draws() {
	b.drawTitle()
	b.drawSubTitles()
	b.drawTableTitle()
	b.drawTableContent()
}

func NewReportExcel(f *excelize.File) *ReportExcel {

	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 := &ReportExcel{
		Title:            "供应商统计报表",
		SupplierName:     "",
		SheetName:        "Sheet1",
		Excel:            f,
		AlignCenterStyle: styleLeft,
	}
	f.SetPageMargins(b.SheetName, excelize.PageMarginTop(0), excelize.PageMarginLeft(0), excelize.PageMarginRight(0))

	return b
}

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