bill-product-excel.go 11 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394
  1. package api
  2. import (
  3. "box-cost/db/model"
  4. "fmt"
  5. _ "image/gif"
  6. _ "image/jpeg"
  7. _ "image/png"
  8. "github.com/xuri/excelize/v2"
  9. )
  10. type ProductBillExcel struct {
  11. Offset int
  12. Row int
  13. Title string //标题
  14. Excel *excelize.File
  15. SheetName string
  16. AlignCenterStyle int
  17. Content *model.ProductBill
  18. Signatures []*model.Signature
  19. IsPdf string
  20. }
  21. func (b *ProductBillExcel) drawTitle() error {
  22. b.Row++
  23. startCell := fmt.Sprintf("A%d", b.Row)
  24. endCell := fmt.Sprintf("H%d", b.Row)
  25. marginLeft := excelize.PageMarginLeft(0.35)
  26. b.Excel.SetColWidth(b.SheetName, "A", "A", 18.5)
  27. b.Excel.SetColWidth(b.SheetName, "B", "B", 16)
  28. b.Excel.SetColWidth(b.SheetName, "C", "G", 12)
  29. b.Excel.SetColWidth(b.SheetName, "H", "H", 18.5)
  30. b.Excel.SetPageMargins(b.SheetName, marginLeft)
  31. err := b.Excel.MergeCell(b.SheetName, startCell, endCell)
  32. if err != nil {
  33. return err
  34. }
  35. style, err := b.Excel.NewStyle(&excelize.Style{
  36. Alignment: &excelize.Alignment{Horizontal: "center", Vertical: "center"},
  37. Font: &excelize.Font{Bold: true, Size: 18}})
  38. if err != nil {
  39. return err
  40. }
  41. err = b.Excel.SetCellStyle(b.SheetName, startCell, startCell, style)
  42. if err != nil {
  43. return err
  44. }
  45. b.Excel.SetRowHeight(b.SheetName, b.Row, 23)
  46. b.Excel.SetCellValue(b.SheetName, startCell, b.Title)
  47. return nil
  48. }
  49. func (b *ProductBillExcel) drawSubTitles() error {
  50. b.Row++
  51. styleLeft, err := b.Excel.NewStyle(&excelize.Style{
  52. Alignment: &excelize.Alignment{Horizontal: "left", Vertical: "center"},
  53. Font: &excelize.Font{Size: 11}})
  54. if err != nil {
  55. return err
  56. }
  57. styleRight, err := b.Excel.NewStyle(&excelize.Style{
  58. Alignment: &excelize.Alignment{Horizontal: "right", Vertical: "center"},
  59. Font: &excelize.Font{Size: 11}})
  60. if err != nil {
  61. return err
  62. }
  63. drawLeft := func(rowIndex int, value string) error {
  64. //左边1
  65. left1Cell := fmt.Sprintf("A%d", rowIndex)
  66. err = b.Excel.MergeCell(b.SheetName, left1Cell, fmt.Sprintf("F%d", rowIndex))
  67. if err != nil {
  68. return err
  69. }
  70. err = b.Excel.SetCellStyle(b.SheetName, left1Cell, left1Cell, styleLeft)
  71. if err != nil {
  72. return err
  73. }
  74. b.Excel.SetCellValue(b.SheetName, left1Cell, value)
  75. return nil
  76. }
  77. drawRight := func(rowIndex int, value string) error {
  78. right1Cell := fmt.Sprintf("G%d", rowIndex)
  79. err = b.Excel.MergeCell(b.SheetName, right1Cell, fmt.Sprintf("H%d", rowIndex))
  80. if err != nil {
  81. return err
  82. }
  83. err = b.Excel.SetCellStyle(b.SheetName, right1Cell, right1Cell, styleRight)
  84. if err != nil {
  85. return err
  86. }
  87. b.Excel.SetCellValue(b.SheetName, right1Cell, value)
  88. return nil
  89. }
  90. //第一行
  91. drawLeft(b.Row, "类别:"+b.Content.Type)
  92. drawRight(b.Row, "单号:"+b.Content.SerialNumber)
  93. b.Excel.SetRowHeight(b.SheetName, b.Row, 21)
  94. //第二行
  95. drawLeft(b.Row+1, "供应商名称:"+b.Content.Supplier)
  96. timeformat := b.Content.CreateTime.Local().Format("2006年01月02号 15:04:05")
  97. drawRight(b.Row+1, "下单时间:"+timeformat)
  98. b.Excel.SetRowHeight(b.SheetName, b.Row+1, 21)
  99. //第三行
  100. drawLeft(b.Row+2, "产品名称:"+b.Content.ProductName)
  101. status := ""
  102. if b.Content.Status == "complete" {
  103. status = "已完成"
  104. }
  105. if b.Content.Status == "created" {
  106. status = "进行中"
  107. }
  108. drawRight(b.Row+2, "状态:"+status)
  109. b.Excel.SetRowHeight(b.SheetName, b.Row+2, 21)
  110. // 第四行
  111. drawLeft(b.Row+3, "包含工序:"+b.Content.CompProduceName)
  112. return nil
  113. }
  114. func (b *ProductBillExcel) drawTableTitle() error {
  115. b.Row += 4
  116. // 品名 规格 数量 单位 单价 金额
  117. var drawCol = func(prefix string, value string) error {
  118. left1Cell := fmt.Sprintf("%s%d", prefix, b.Row)
  119. left2Cell := fmt.Sprintf("%s%d", prefix, b.Row+1)
  120. err := b.Excel.MergeCell(b.SheetName, left1Cell, left2Cell)
  121. if err != nil {
  122. return err
  123. }
  124. err = b.Excel.SetCellStyle(b.SheetName, left1Cell, left2Cell, b.AlignCenterStyle)
  125. if err != nil {
  126. return err
  127. }
  128. return b.Excel.SetCellValue(b.SheetName, left1Cell, value)
  129. }
  130. drawCol("A", "采购项目")
  131. drawCol("B", "规格")
  132. drawCol("C", "下单数量")
  133. drawCol("D", "单位")
  134. drawCol("E", "单价")
  135. drawCol("F", "预算金额")
  136. drawCol("G", "交货时间")
  137. drawCol("H", "备注")
  138. return nil
  139. }
  140. func (b *ProductBillExcel) drawTableContent() error {
  141. b.Row += 2
  142. var DrawRow = func(rowIndex int, values ...string) {
  143. charas := []string{"A", "B", "C", "D", "E", "F", "G", "H"}
  144. for i, c := range charas {
  145. v := ""
  146. if i < len(values) {
  147. v = values[i]
  148. }
  149. b.Excel.SetCellValue(b.SheetName, fmt.Sprintf("%s%d", c, rowIndex), v)
  150. val2Cel := fmt.Sprintf("%s%d", c, rowIndex)
  151. b.Excel.SetCellStyle(b.SheetName, val2Cel, val2Cel, b.AlignCenterStyle)
  152. // var magN float64 = 1
  153. // lenv := len([]rune(v))
  154. // magN = math.Ceil(float64(lenv) / 10)
  155. // if lenv%10 > 5 {
  156. // magN = math.Ceil(float64(lenv) / 10)
  157. // } else {
  158. // n := math.Floor(float64(lenv) / 10)
  159. // if n > 0 {
  160. // magN = n
  161. // }
  162. // }
  163. // b.Excel.SetRowHeight(b.SheetName, rowIndex, 21*magN)
  164. b.Excel.SetRowHeight(b.SheetName, rowIndex, 46)
  165. }
  166. }
  167. products := b.Content.Products
  168. if len(products) > 0 {
  169. for _, product := range products {
  170. deliveryTime := product.DeliveryTime.Local().Format("2006-01-02")
  171. realCount := "-"
  172. realPrice := "-"
  173. // 预算金额
  174. budgetAmount := fmt.Sprintf("%.3f", float64(product.OrderCount)*product.OrderPrice)
  175. b.FormatToEmpty(&budgetAmount)
  176. // 实际完成数
  177. realCount = fmt.Sprintf("%d", product.ConfirmCount)
  178. b.FormatToEmpty(&realCount)
  179. // 实际金额
  180. realPrice = fmt.Sprintf("%.3f", float64(product.ConfirmCount)*product.OrderPrice)
  181. b.FormatToEmpty(&realPrice)
  182. // a采购项目 b规格 c下单数量 d单位 e完成数量 f单价 g预算金额 h实际金额 i交货时间 j备注
  183. orderCount := fmt.Sprintf("%d", product.OrderCount)
  184. price := fmt.Sprintf("%.3f", product.OrderPrice)
  185. b.FormatToEmpty(&price)
  186. DrawRow(b.Row, product.Name, product.Norm, orderCount, product.Unit, price, budgetAmount, deliveryTime, product.Remark)
  187. b.Row++
  188. }
  189. }
  190. return nil
  191. }
  192. func (b *ProductBillExcel) drawTableFooter() error {
  193. // left1Cell := fmt.Sprintf("A%d", b.Row)
  194. border := []excelize.Border{
  195. {Type: "top", Style: 1, Color: "000000"},
  196. {Type: "left", Style: 1, Color: "000000"},
  197. {Type: "right", Style: 1, Color: "000000"},
  198. {Type: "bottom", Style: 1, Color: "000000"},
  199. }
  200. styleLeft, _ := b.Excel.NewStyle(&excelize.Style{
  201. Alignment: &excelize.Alignment{Horizontal: "left", Vertical: "center"},
  202. Border: border,
  203. })
  204. sendToStartCell := fmt.Sprintf("A%d", b.Row)
  205. // sendToEndCell := fmt.Sprintf("G%d", b.Row)
  206. // supplierStartCell := fmt.Sprintf("H%d", b.Row)
  207. // supplierEndCell := fmt.Sprintf("J%d", b.Row)
  208. // !!isPdf
  209. // if b.IsPdf == "true" {
  210. sendToEndCell := fmt.Sprintf("E%d", b.Row)
  211. supplierStartCell := fmt.Sprintf("F%d", b.Row)
  212. supplierEndCell := fmt.Sprintf("H%d", b.Row)
  213. // }
  214. b.Excel.MergeCell(b.SheetName, sendToStartCell, sendToEndCell)
  215. b.Excel.SetCellStyle(b.SheetName, sendToStartCell, sendToEndCell, styleLeft)
  216. b.Excel.SetCellValue(b.SheetName, sendToStartCell, "送货地址:"+b.Content.SendTo)
  217. b.Excel.MergeCell(b.SheetName, supplierStartCell, supplierEndCell)
  218. b.Excel.SetCellStyle(b.SheetName, supplierStartCell, supplierEndCell, styleLeft)
  219. b.Excel.SetCellValue(b.SheetName, supplierStartCell, " 供应商签字:")
  220. b.Excel.SetRowHeight(b.SheetName, b.Row, 21)
  221. return nil
  222. }
  223. func (b *ProductBillExcel) drawTableSignature() error {
  224. b.Row += 2
  225. // row := b.Row
  226. style1, _ := b.Excel.NewStyle(&excelize.Style{
  227. Alignment: &excelize.Alignment{Horizontal: "center", Vertical: "center"},
  228. })
  229. // 制单人
  230. billUserCellS := fmt.Sprintf("A%d", b.Row+1)
  231. billUserCellE := fmt.Sprintf("B%d", b.Row+1)
  232. b.Excel.MergeCell(b.SheetName, billUserCellS, billUserCellE)
  233. b.Excel.SetCellValue(b.SheetName, billUserCellS, "制单人:"+b.Content.UserName)
  234. fontNum := "E"
  235. image1s := "F"
  236. image2s := "H"
  237. image1e := "G"
  238. image2e := "H"
  239. fontCell := fmt.Sprintf("%s%d", fontNum, b.Row)
  240. fontEndCell := fmt.Sprintf("%s%d", fontNum, b.Row+2)
  241. imageCell1 := fmt.Sprintf("%s%d", image1s, b.Row)
  242. imageEndCell1 := fmt.Sprintf("%s%d", image1e, b.Row+2)
  243. imageCell2 := fmt.Sprintf("%s%d", image2s, b.Row)
  244. imageEndCell2 := fmt.Sprintf("%s%d", image2e, b.Row+2)
  245. b.Excel.MergeCell(b.SheetName, fontCell, fontEndCell)
  246. b.Excel.SetCellStyle(b.SheetName, fontCell, fontCell, style1)
  247. b.Excel.SetCellValue(b.SheetName, fontCell, "审核签字:")
  248. // 签字图片1 I10-J12
  249. b.Excel.MergeCell(b.SheetName, imageCell1, imageEndCell1)
  250. b.Excel.SetCellStyle(b.SheetName, imageCell1, imageCell1, style1)
  251. b.Excel.SetCellValue(b.SheetName, imageCell1, "")
  252. // 签字图片2 K10-L12
  253. b.Excel.MergeCell(b.SheetName, imageCell2, imageEndCell2)
  254. b.Excel.SetCellStyle(b.SheetName, imageCell2, imageCell1, style1)
  255. b.Excel.SetCellValue(b.SheetName, imageCell2, "")
  256. if b.Content.Reviewed == 1 {
  257. imageCells := []string{imageCell1, imageCell2}
  258. if len(b.Signatures) > 0 {
  259. for k, sign := range b.Signatures {
  260. b.Excel.AddPicture(b.SheetName, imageCells[k], sign.Path, sign.Format)
  261. }
  262. }
  263. }
  264. b.Excel.SetRowHeight(b.SheetName, b.Row, 21)
  265. return nil
  266. }
  267. func (b *ProductBillExcel) Draws() {
  268. b.drawTitle()
  269. b.drawSubTitles()
  270. b.drawTableTitle()
  271. b.drawTableContent()
  272. b.drawTableFooter()
  273. b.drawTableSignature()
  274. }
  275. func NewProductBill(f *excelize.File) *ProductBillExcel {
  276. border := []excelize.Border{
  277. {Type: "top", Style: 1, Color: "000000"},
  278. {Type: "left", Style: 1, Color: "000000"},
  279. {Type: "right", Style: 1, Color: "000000"},
  280. {Type: "bottom", Style: 1, Color: "000000"},
  281. }
  282. styleLeft, _ := f.NewStyle(&excelize.Style{
  283. Alignment: &excelize.Alignment{Horizontal: "center", Vertical: "center", WrapText: true},
  284. Border: border,
  285. Font: &excelize.Font{Size: 10},
  286. })
  287. b := &ProductBillExcel{
  288. Title: "原材料采购单",
  289. SheetName: "Sheet1",
  290. Excel: f,
  291. Offset: 0,
  292. AlignCenterStyle: styleLeft,
  293. Signatures: make([]*model.Signature, 0),
  294. }
  295. // f.SetColWidth(b.SheetName, "A", "A", 17)
  296. // f.SetColWidth(b.SheetName, "B", "B", 12)
  297. // f.SetColWidth(b.SheetName, "C", "H", 10.5)
  298. // f.SetColWidth(b.SheetName, "I", "J", 12)
  299. f.SetPageMargins(b.SheetName, excelize.PageMarginTop(1), excelize.PageMarginLeft(0.25), excelize.PageMarginRight(0))
  300. return b
  301. }
  302. func (b *ProductBillExcel) FormatToEmpty(str *string) {
  303. if *str == "0" || *str == "0.000" {
  304. *str = ""
  305. }
  306. }
  307. func (b *ProductBillExcel) SetContent(content interface{}) {
  308. b.Content = content.(*model.ProductBill)
  309. }
  310. func (b *ProductBillExcel) SetTitle(title string) {
  311. b.Title = title
  312. }
  313. func (b *ProductBillExcel) SetSheetName(name string) {
  314. b.SheetName = name
  315. }
  316. func (b *ProductBillExcel) SetRow(row int) {
  317. b.Row = row
  318. }
  319. func (b *ProductBillExcel) SetIsPdf(isPdf string) {
  320. b.IsPdf = isPdf
  321. }
  322. func (b *ProductBillExcel) GetRow() int {
  323. return b.Row
  324. }
  325. func (b *ProductBillExcel) SetSignatures(sign interface{}) {
  326. b.Signatures = sign.([]*model.Signature)
  327. }