exa_excel_parse.go 3.7 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134
  1. package example
  2. import (
  3. "bytes"
  4. "errors"
  5. "fmt"
  6. "github.com/flipped-aurora/gin-vue-admin/server/global"
  7. "github.com/flipped-aurora/gin-vue-admin/server/model/autocode"
  8. "github.com/flipped-aurora/gin-vue-admin/server/model/system"
  9. "github.com/xuri/excelize/v2"
  10. "strconv"
  11. "strings"
  12. )
  13. type ExcelService struct {
  14. }
  15. func (exa *ExcelService) ParseInfoList2Excel(infoList []system.SysBaseMenu, filePath string) error {
  16. excel := excelize.NewFile()
  17. excel.SetSheetRow("Sheet1", "A1", &[]string{"ID", "路由Name", "路由Path", "是否隐藏", "父节点", "排序", "文件名称"})
  18. for i, menu := range infoList {
  19. axis := fmt.Sprintf("A%d", i+2)
  20. excel.SetSheetRow("Sheet1", axis, &[]interface{}{
  21. menu.ID,
  22. menu.Name,
  23. menu.Path,
  24. menu.Hidden,
  25. menu.ParentId,
  26. menu.Sort,
  27. menu.Component,
  28. })
  29. }
  30. err := excel.SaveAs(filePath)
  31. return err
  32. }
  33. func (exa *ExcelService) ProblemInfoList2Excel(infoList []autocode.ProblemInfo, filePath string, dictList []system.SysDictionary) error {
  34. excel := excelize.NewFile()
  35. excel.SetSheetRow("Sheet1", "A1", &[]string{"ID", "问题发布时间", "问题处理时间", "问题视频地址", "问题图片", "站点名称", "站点位置", "问题", "站点类型", "责任部门", "处理状态", "处理图片", "处理内容", "处理人"})
  36. for i, problemInfo := range infoList {
  37. axis := fmt.Sprintf("A%d", i+2)
  38. var matterStr bytes.Buffer
  39. for _, dictionary := range dictList {
  40. if dictionary.Name == problemInfo.SiteType {
  41. for index, id := range strings.Split(problemInfo.Matter, "|") {
  42. tempid, _ := strconv.Atoi(id)
  43. if len(dictionary.SysDictionaryDetails) >= tempid+1 {
  44. matterStr.WriteString(strconv.Itoa(index+1) + "." + dictionary.SysDictionaryDetails[tempid].Label + " \n")
  45. }
  46. }
  47. }
  48. }
  49. excel.SetSheetRow("Sheet1", axis, &[]interface{}{
  50. problemInfo.ID,
  51. problemInfo.CreatedAt,
  52. problemInfo.UpdatedAt,
  53. problemInfo.Video,
  54. problemInfo.Imgs,
  55. problemInfo.SiteName,
  56. "https://apis.map.qq.com/uri/v1/marker?marker=coord:" + problemInfo.Position + ";title:" + problemInfo.SiteName + ";addr:" + problemInfo.SiteType,
  57. matterStr.String(),
  58. problemInfo.SiteType,
  59. problemInfo.Department,
  60. problemInfo.Status,
  61. problemInfo.HandImgs,
  62. problemInfo.HandText,
  63. problemInfo.Handler,
  64. })
  65. }
  66. err := excel.SaveAs(filePath)
  67. return err
  68. }
  69. func (exa *ExcelService) ParseExcel2InfoList() ([]system.SysBaseMenu, error) {
  70. skipHeader := true
  71. fixedHeader := []string{"ID", "路由Name", "路由Path", "是否隐藏", "父节点", "排序", "文件名称"}
  72. file, err := excelize.OpenFile(global.GVA_CONFIG.Excel.Dir + "ExcelImport.xlsx")
  73. if err != nil {
  74. return nil, err
  75. }
  76. menus := make([]system.SysBaseMenu, 0)
  77. rows, err := file.Rows("Sheet1")
  78. if err != nil {
  79. return nil, err
  80. }
  81. for rows.Next() {
  82. row, err := rows.Columns()
  83. if err != nil {
  84. return nil, err
  85. }
  86. if skipHeader {
  87. if exa.compareStrSlice(row, fixedHeader) {
  88. skipHeader = false
  89. continue
  90. } else {
  91. return nil, errors.New("Excel格式错误")
  92. }
  93. }
  94. if len(row) != len(fixedHeader) {
  95. continue
  96. }
  97. id, _ := strconv.Atoi(row[0])
  98. hidden, _ := strconv.ParseBool(row[3])
  99. sort, _ := strconv.Atoi(row[5])
  100. menu := system.SysBaseMenu{
  101. GVA_MODEL: global.GVA_MODEL{
  102. ID: uint(id),
  103. },
  104. Name: row[1],
  105. Path: row[2],
  106. Hidden: hidden,
  107. ParentId: row[4],
  108. Sort: sort,
  109. Component: row[6],
  110. }
  111. menus = append(menus, menu)
  112. }
  113. return menus, nil
  114. }
  115. func (exa *ExcelService) compareStrSlice(a, b []string) bool {
  116. if len(a) != len(b) {
  117. return false
  118. }
  119. if (b == nil) != (a == nil) {
  120. return false
  121. }
  122. for key, value := range a {
  123. if value != b[key] {
  124. return false
  125. }
  126. }
  127. return true
  128. }