| | |
| | | } |
| | | } |
| | | data.AvailableNumber = data.Amount.Sub(available) |
| | | |
| | | if *material.MoreUnit { |
| | | data.AmountMoreUnits = CreateMoreUnit(data.Amount, material.MoreUnitList) |
| | | data.AvailableNumberMoreUnits = CreateMoreUnit(data.AvailableNumber, material.MoreUnitList) |
| | | data.InMoreUnits = CreateMoreUnit(data.In, material.MoreUnitList) |
| | | data.OutMoreUnits = CreateMoreUnit(data.Out, material.MoreUnitList) |
| | | } |
| | | |
| | | result = append(result, data) |
| | | } |
| | | return |
| | |
| | | //查询产品 |
| | | search = models.NewMaterialSearch() |
| | | search.Orm = search.Orm.Model(&models.Material{}). |
| | | Select(`material.id, material.name, material.cost, material.amount, material.unit, wms_product_category.name as category_name `). |
| | | Select(`material.id, material.name, material.cost, material.amount, material.unit, material.more_unit, material.more_unit_value, wms_product_category.name as category_name `). |
| | | Joins("left join wms_product_category on material.category_id = wms_product_category.id") |
| | | if len(params.CategoryIds) > 0 { |
| | | search.Orm.Where("material.category_id in (?)", params.CategoryIds) |
| | |
| | | f := excelize.NewFile() |
| | | |
| | | // 自定义表头 |
| | | headers := []string{"产品", "产品类别", "单位成本", "总价值", "在库", "可用库存", "入库", "出库", "单位"} |
| | | |
| | | headers := []string{"产品", "产品类别", "单位成本", "总价值", "在库", "可用库存", "入库", "出库"} |
| | | // 设置表头 |
| | | for i, header := range headers { |
| | | cell := getColumnAlphabet(i+1) + "1" |
| | | cell := getColumnAlphabet(i) + "1" |
| | | f.SetCellValue("Sheet1", cell, header) |
| | | } |
| | | |
| | | f.SetCellValue("Sheet1", getColumnAlphabet(0)+"1", "产品") |
| | | f.SetCellValue("Sheet1", getColumnAlphabet(1)+"1", "产品类别") |
| | | f.SetCellValue("Sheet1", getColumnAlphabet(2)+"1", "单位成本") |
| | | f.SetCellValue("Sheet1", getColumnAlphabet(3)+"1", "总价值") |
| | | f.SetCellValue("Sheet1", getColumnAlphabet(4)+"1", "在库") |
| | | f.MergeCell("Sheet1", getColumnAlphabet(4)+"1", getColumnAlphabet(7)+"1") // 合并单元格 |
| | | f.SetCellValue("Sheet1", getColumnAlphabet(8)+"1", "可用库存") |
| | | f.MergeCell("Sheet1", getColumnAlphabet(8)+"1", getColumnAlphabet(11)+"1") // 合并单元格 |
| | | f.SetCellValue("Sheet1", getColumnAlphabet(12)+"1", "入库") |
| | | f.MergeCell("Sheet1", getColumnAlphabet(12)+"1", getColumnAlphabet(15)+"1") // 合并单元格 |
| | | f.SetCellValue("Sheet1", getColumnAlphabet(16)+"1", "出库") |
| | | f.MergeCell("Sheet1", getColumnAlphabet(16)+"1", getColumnAlphabet(19)+"1") // 合并单元格 |
| | | |
| | | unitData := []string{"件", "匹", "米", "重量"} |
| | | for i, header := range unitData { |
| | | f.SetCellValue("Sheet1", getColumnAlphabet(i+4)+"2", header) |
| | | f.SetCellValue("Sheet1", getColumnAlphabet(i+8)+"2", header) |
| | | f.SetCellValue("Sheet1", getColumnAlphabet(i+12)+"2", header) |
| | | f.SetCellValue("Sheet1", getColumnAlphabet(i+16)+"2", header) |
| | | } |
| | | |
| | | // 设置表头样式 |
| | | style := &excelize.Style{ |
| | | Border: nil, |
| | | Fill: excelize.Fill{ |
| | | Type: "pattern", |
| | | Pattern: 1, |
| | | Shading: 0, |
| | | }, |
| | | Font: &excelize.Font{ |
| | | Bold: true, |
| | | }, |
| | | Alignment: &excelize.Alignment{ |
| | | Horizontal: "center", |
| | | }, |
| | | } |
| | | titleStyle, err := f.NewStyle(style) |
| | | if err != nil { |
| | | return |
| | | } |
| | | |
| | | lastColumn := getColumnAlphabet(19) |
| | | f.SetCellStyle("Sheet1", "A1", lastColumn+"2", titleStyle) |
| | | // 设置列宽 |
| | | f.SetColWidth("Sheet1", "A", "D", 30) |
| | | f.SetColWidth("Sheet1", "E", lastColumn, 15) |
| | | |
| | | for i, v := range dataList { |
| | | column := strconv.Itoa(i + 2) |
| | | f.SetCellValue("Sheet1", "A"+column, v.ProductName) |
| | | f.SetCellValue("Sheet1", "B"+column, v.ProductType) |
| | | f.SetCellValue("Sheet1", "C"+column, v.Cost) |
| | | f.SetCellValue("Sheet1", "D"+column, v.Value) |
| | | f.SetCellValue("Sheet1", "E"+column, v.Amount) |
| | | f.SetCellValue("Sheet1", "F"+column, v.AvailableNumber) |
| | | f.SetCellValue("Sheet1", "G"+column, v.In) |
| | | f.SetCellValue("Sheet1", "H"+column, v.Out) |
| | | f.SetCellValue("Sheet1", "I"+column, v.Unit) |
| | | column := strconv.Itoa(i + 3) |
| | | f.SetCellValue("Sheet1", getColumnAlphabet(0)+column, v.ProductName) |
| | | f.SetCellValue("Sheet1", getColumnAlphabet(1)+column, v.ProductType) |
| | | f.SetCellValue("Sheet1", getColumnAlphabet(2)+column, v.Cost) |
| | | f.SetCellValue("Sheet1", getColumnAlphabet(3)+column, v.Value) |
| | | |
| | | f.SetCellValue("Sheet1", getColumnAlphabet(4)+column, v.Amount) |
| | | FillMoreUnitToExcel(v.Amount, v.AmountMoreUnits, 5, i+3, f) |
| | | |
| | | f.SetCellValue("Sheet1", getColumnAlphabet(8)+column, v.AvailableNumber) |
| | | FillMoreUnitToExcel(v.AvailableNumber, v.AvailableNumberMoreUnits, 9, i+3, f) |
| | | |
| | | f.SetCellValue("Sheet1", getColumnAlphabet(12)+column, v.In) |
| | | FillMoreUnitToExcel(v.In, v.AmountMoreUnits, 13, i+3, f) |
| | | |
| | | f.SetCellValue("Sheet1", getColumnAlphabet(16)+column, v.Out) |
| | | FillMoreUnitToExcel(v.Out, v.AmountMoreUnits, 17, i+3, f) |
| | | } |
| | | |
| | | fileName = fmt.Sprintf("库存报表%s.xlsx", time.Now().Format("2006-01-02-1504")) |
| | |
| | | func getColumnAlphabet(index int) string { |
| | | const alphabet = "ABCDEFGHIJKLMNOPQRSTUVWXYZ" |
| | | if index <= 26 { |
| | | return string(alphabet[index-1]) |
| | | return string(alphabet[index]) |
| | | } |
| | | return getColumnAlphabet((index-1)/26) + getColumnAlphabet((index-1)%26+1) |
| | | return getColumnAlphabet((index)/26) + getColumnAlphabet((index)%26+1) |
| | | } |