cc/server/service/example/exa_excel_parse.go

package example

import (
	"bytes"
	"errors"
	"fmt"
	"github.com/flipped-aurora/gin-vue-admin/server/global"
	"github.com/flipped-aurora/gin-vue-admin/server/model/autocode"
	"github.com/flipped-aurora/gin-vue-admin/server/model/system"
	"github.com/xuri/excelize/v2"
	"strconv"
	"strings"
)

type ExcelService struct {
}

func (exa *ExcelService) ParseInfoList2Excel(infoList []system.SysBaseMenu, filePath string) error {
	excel := excelize.NewFile()
	excel.SetSheetRow("Sheet1", "A1", &[]string{"ID", "路由Name", "路由Path", "是否隐藏", "父节点", "排序", "文件名称"})
	for i, menu := range infoList {
		axis := fmt.Sprintf("A%d", i+2)
		excel.SetSheetRow("Sheet1", axis, &[]interface{}{
			menu.ID,
			menu.Name,
			menu.Path,
			menu.Hidden,
			menu.ParentId,
			menu.Sort,
			menu.Component,
		})
	}
	err := excel.SaveAs(filePath)
	return err
}

func (exa *ExcelService) ProblemInfoList2Excel(infoList []autocode.ProblemInfo, filePath string, siteType system.SysDictionary, list []autocode.ProblemType) error {
	excel := excelize.NewFile()
	excel.SetSheetRow("Sheet1", "A1", &[]string{"ID", "问题发布时间", "问题处理时间", "问题视频地址", "问题图片", "站点名称", "站点位置", "问题", "站点类型", "一类单位", "评分", "责任部门", "已查阅次数", "处理状态", "处理图片", "处理内容", "处理人"})
	//站点类型
	siteMap := make(map[int]string)
	for _, siteT := range siteType.SysDictionaryDetails {
		siteMap[siteT.Value] = siteT.Label
	}
	//问题类型
	siteProMap := make(map[int]map[int]string)
	siteId := list[0].SiteType
	proMap := make(map[int]string)
	for _, pro := range list {
		if *siteId != *pro.SiteType {
			siteProMap[*siteId] = proMap
			siteId = pro.SiteType
			proMap = make(map[int]string)
		}
		proMap[int(pro.ID)] = pro.Problem
	}

	for i, problemInfo := range infoList {
		axis := fmt.Sprintf("A%d", i+2)
		var matterStr bytes.Buffer
		var status string
		if problemInfo.Status != "Untreated" {
			status = "未处理"
		} else {
			status = "已处理"
		}
		if problemInfo.Matter != "" {
			for index, id := range strings.Split(problemInfo.Matter, "|") {
				id, _ := strconv.Atoi(id)
				matterStr.WriteString(strconv.Itoa(index+1) + "." + siteProMap[*problemInfo.SiteType][id] + " \n")
			}
		} else {
			matterStr.WriteString(problemInfo.Remark)
		}

		excel.SetSheetRow("Sheet1", axis, &[]interface{}{
			problemInfo.ID,
			problemInfo.CreatedAt,
			problemInfo.UpdatedAt,
			problemInfo.Video,
			problemInfo.Imgs,
			problemInfo.SiteName,
			"https://apis.map.qq.com/uri/v1/marker?marker=coord:" + problemInfo.Position + ";title:" + problemInfo.SiteName + ";addr:" + siteMap[*problemInfo.SiteType],
			matterStr.String(),
			siteMap[*problemInfo.SiteType],
			problemInfo.UnitName,
			float64(*problemInfo.Integral) / 10,
			problemInfo.Department,
			*problemInfo.Count,
			status,
			problemInfo.HandImgs,
			problemInfo.HandText,
			&problemInfo.Handler,
		})
	}
	err := excel.SaveAs(filePath)
	return err
}

func (exa *ExcelService) ParseExcel2InfoList() ([]system.SysBaseMenu, error) {
	skipHeader := true
	fixedHeader := []string{"ID", "路由Name", "路由Path", "是否隐藏", "父节点", "排序", "文件名称"}
	file, err := excelize.OpenFile(global.GVA_CONFIG.Excel.Dir + "ExcelImport.xlsx")
	if err != nil {
		return nil, err
	}
	menus := make([]system.SysBaseMenu, 0)
	rows, err := file.Rows("Sheet1")
	if err != nil {
		return nil, err
	}
	for rows.Next() {
		row, err := rows.Columns()
		if err != nil {
			return nil, err
		}
		if skipHeader {
			if exa.compareStrSlice(row, fixedHeader) {
				skipHeader = false
				continue
			} else {
				return nil, errors.New("Excel格式错误")
			}
		}
		if len(row) != len(fixedHeader) {
			continue
		}
		id, _ := strconv.Atoi(row[0])
		hidden, _ := strconv.ParseBool(row[3])
		sort, _ := strconv.Atoi(row[5])
		menu := system.SysBaseMenu{
			GVA_MODEL: global.GVA_MODEL{
				ID: uint(id),
			},
			Name:      row[1],
			Path:      row[2],
			Hidden:    hidden,
			ParentId:  row[4],
			Sort:      sort,
			Component: row[6],
		}
		menus = append(menus, menu)
	}
	return menus, nil
}

func (exa *ExcelService) compareStrSlice(a, b []string) bool {
	if len(a) != len(b) {
		return false
	}
	if (b == nil) != (a == nil) {
		return false
	}
	for key, value := range a {
		if value != b[key] {
			return false
		}
	}
	return true
}