package api import ( "box-cost/db/model" "fmt" "regexp" _ "image/gif" _ "image/jpeg" _ "image/png" "github.com/xuri/excelize/v2" ) type ReportProductExcel struct { Offset int Row int Title string //标题 Excel *excelize.File SheetName string AlignCenterStyle int Content *model.ProductBill BudgetAmount float64 RealAmount float64 } func (b *ReportProductExcel) drawTitle() error { marginLeft := excelize.PageMarginLeft(0.3) b.Excel.SetPageMargins(b.SheetName, marginLeft) b.Row++ startCell := fmt.Sprintf("A%d", b.Row) err := b.Excel.MergeCell(b.SheetName, startCell, fmt.Sprintf("J%d", b.Row)) 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, 23) b.Excel.SetCellValue(b.SheetName, startCell, b.Title) return nil } func (b *ReportProductExcel) 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 } styleRight, err := b.Excel.NewStyle(&excelize.Style{ Alignment: &excelize.Alignment{Horizontal: "right", Vertical: "center"}, Font: &excelize.Font{Size: 11}}) if err != nil { return err } var drawLeft = func(rowIndex int, value string) error { //左边1 left1Cell := fmt.Sprintf("A%d", rowIndex) err = b.Excel.MergeCell(b.SheetName, left1Cell, fmt.Sprintf("E%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 } var drawRight = func(rowIndex int, value string) error { right1Cell := fmt.Sprintf("F%d", rowIndex) err = b.Excel.MergeCell(b.SheetName, right1Cell, fmt.Sprintf("J%d", rowIndex)) if err != nil { return err } err = b.Excel.SetCellStyle(b.SheetName, right1Cell, right1Cell, styleRight) if err != nil { return err } b.Excel.SetCellValue(b.SheetName, right1Cell, value) return nil } //第一行 drawLeft(b.Row, "类别:"+b.Content.Type) drawRight(b.Row, "单号:"+b.Content.SerialNumber) b.Excel.SetRowHeight(b.SheetName, b.Row, 21) b.Row++ //第二行 drawLeft(b.Row, "供应商名称:"+b.Content.Supplier) timeformat := b.Content.CreateTime.Local().Format("2006年01月02号 15:04:05") drawRight(b.Row, "下单时间:"+timeformat) b.Excel.SetRowHeight(b.SheetName, b.Row, 21) b.Row++ //第三行 drawLeft(b.Row, "产品名称:"+b.Content.ProductName) status := "" if b.Content.Status == "complete" { status = fmt.Sprintf("已完成(%d)", b.Content.ConfirmCount) } if b.Content.Status == "created" { status = "进行中" } drawRight(b.Row, "状态:"+status) b.Excel.SetRowHeight(b.SheetName, b.Row, 21) return nil } func (b *ReportProductExcel) 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) } // a采购项目 b规格 c下单数量 d单位 e完成数量 f单价 g预算金额 h实际金额 i交货时间 j备注 drawCol("A", "采购项目") drawCol("B", "规格") drawCol("C", "下单数量") drawCol("D", "单位") drawCol("E", "完成数量") drawCol("F", "单价") drawCol("G", "预算金额") drawCol("H", "实际金额") drawCol("I", "交货时间") drawCol("J", "备注") return nil } func (b *ReportProductExcel) drawTableContent() error { b.Row += 2 var DrawRow = func(rowIndex int, values ...string) { charas := []string{"A", "B", "C", "D", "E", "F", "G", "H", "I", "J"} 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, 21) } } products := b.Content.Products if len(products) > 0 { for _, product := range products { deliveryTime := product.DeliveryTime.Local().Format("2006-01-02") realCount := "" realPrice := "" // 预算金额 orderPrice := fmt.Sprintf("%.3f", float64(product.OrderCount)*product.Price) b.FormatToEmpty(&orderPrice) // 实际完成数 realCount = fmt.Sprintf("%d", product.ConfirmCount) b.FormatToEmpty(&realCount) // 实际金额 realPrice = fmt.Sprintf("%.3f", float64(product.ConfirmCount)*product.Price) b.FormatToEmpty(&realPrice) // a采购项目 b规格 c下单数量 d单位 e完成数量 f单价 g预算金额 h实际金额 i交货时间 j备注 orderCount := fmt.Sprintf("%d", product.OrderCount) price := fmt.Sprintf("%.3f", product.Price) b.FormatToEmpty(&price) DrawRow(b.Row, product.Name, product.Norm, orderCount, product.Unit, realCount, price, orderPrice, realPrice, deliveryTime, product.Remark) b.Row++ b.BudgetAmount += float64(product.OrderCount) * product.Price b.RealAmount += float64(product.ConfirmCount) * product.Price } } return nil } func (b *ReportProductExcel) drawRemark() error { // 填备注 b.Row++ remarkTitleCell := fmt.Sprintf("A%d", b.Row) b.Excel.SetCellValue(b.SheetName, remarkTitleCell, "备注:") b.Row++ remarkContentScell := fmt.Sprintf("A%d", b.Row) // 根据换行符确定多少行单元格 reg := regexp.MustCompile(`\n`) remarkRowNum := len(reg.FindAllString(b.Content.Remark, -1)) + 1 b.Row += remarkRowNum remarkContentEcell := fmt.Sprintf("J%d", b.Row) b.Excel.MergeCell(b.SheetName, remarkContentScell, remarkContentEcell) b.Excel.SetCellValue(b.SheetName, remarkContentScell, b.Content.Remark) return nil } func (b *ReportProductExcel) Draws() { b.drawTitle() b.drawSubTitles() b.drawTableTitle() b.drawTableContent() b.drawRemark() } func NewReportProductBill(f *excelize.File) *ReportProductExcel { 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"}, Border: border, Font: &excelize.Font{Size: 10}, }) b := &ReportProductExcel{ Title: "原材料采购单", SheetName: "Sheet1", Excel: f, Offset: 0, AlignCenterStyle: styleLeft, } f.SetColWidth(b.SheetName, "A", "J", 11.5) f.SetPageMargins(b.SheetName, excelize.PageMarginTop(0), excelize.PageMarginLeft(0), excelize.PageMarginRight(0)) return b } func (b *ReportProductExcel) FormatToEmpty(str *string) { if *str == "0" || *str == "0.000" { *str = "" } }