package excel import ( "encoding/json" "errors" "eta_gn/eta_api/models/data_manage/excel" "eta_gn/eta_api/models/data_manage/excel/request" "eta_gn/eta_api/models/system" "eta_gn/eta_api/utils" "strconv" "time" ) var cellSplitNum = 10000 // 基础分割单元格数 // AddCustomAnalysisTable 添加自定义分析表格 func AddCustomAnalysisTable(excelName, content, excelImage string, excelClassifyId int, sysUser *system.Admin) (excelInfo *excel.ExcelInfo, err error, errMsg string, isSendEmail bool) { isSendEmail = true contentByte := []byte(content) var luckySheetList []LuckySheet err = json.Unmarshal(contentByte, &luckySheetList) if err != nil { return } // sheet内容为空 if len(luckySheetList) <= 0 { errMsg = "sheet内容为空" err = errors.New(errMsg) isSendEmail = false return } excelClassify, err := excel.GetExcelClassifyById(excelClassifyId) if err != nil { if utils.IsErrNoRow(err) { errMsg = "分类不存在" err = errors.New(errMsg) isSendEmail = false return } errMsg = "获取分类信息失败" err = errors.New("获取分类信息失败,Err:" + err.Error()) return } if excelClassify == nil { errMsg = "分类不存在" err = errors.New(errMsg) isSendEmail = false return } if excelClassify.Source != utils.CUSTOM_ANALYSIS_TABLE { errMsg = "当前分类不是自定义分析分类" err = errors.New("当前分类不是自定义分析分类") isSendEmail = false return } // 校验是否同名文件 { var condition string var pars []interface{} condition += " AND excel_classify_id=? " pars = append(pars, excelClassifyId) condition += " AND excel_name=? " pars = append(pars, excelName) // 获取分类下是否存在该表格名称 count, tmpErr := excel.GetExcelInfoCountByCondition(condition, pars) if tmpErr != nil { errMsg = "判断表格名称是否存在失败" err = errors.New("判断表格名称是否存在失败,Err:" + tmpErr.Error()) return } if count > 0 { errMsg = "表格名称已存在,请重新填写表格名称" err = errors.New(errMsg) isSendEmail = false return } } timestamp := strconv.FormatInt(time.Now().UnixNano(), 10) // 表格 excelInfo = &excel.ExcelInfo{ //ExcelInfoId: 0, ExcelName: excelName, Source: utils.CUSTOM_ANALYSIS_TABLE, //ExcelType: req.ExcelType, UniqueCode: utils.MD5(utils.EXCEL_DATA_PREFIX + "_" + timestamp), ExcelClassifyId: excelClassifyId, SysUserId: sysUser.AdminId, SysUserRealName: sysUser.RealName, Content: ``, ExcelImage: excelImage, Sort: 0, IsDelete: 0, ModifyTime: time.Now(), CreateTime: time.Now(), } addSheetList := make([]excel.AddExcelSheetParams, 0) // sheet处理 sheetNameMap := make(map[string]string) for k, sheetInfo := range luckySheetList { sheetName := utils.TrimLRStr(sheetInfo.Name) _, ok := sheetNameMap[sheetName] if ok { errMsg = "excel表中存在同名sheet" err = errors.New(errMsg) isSendEmail = false return } sheetConf, tmpErr := json.Marshal(sheetInfo.Config) if tmpErr != nil { err = tmpErr return } // 计算公式 sheetCalcChain, tmpErr := json.Marshal(sheetInfo.CalcChain) if tmpErr != nil { err = tmpErr return } addSheetItem := excel.AddExcelSheetParams{ ExcelSheetId: 0, ExcelInfoId: 0, SheetName: sheetName, Index: sheetInfo.Index, Sort: k, Config: string(sheetConf), CalcChain: string(sheetCalcChain), } lenCellData := len(sheetInfo.CellData) splitLen := lenCellData / cellSplitNum residue := lenCellData % cellSplitNum if residue > 0 { splitLen += 1 } sheetDataList := make([]*excel.ExcelSheetData, 0) for i := 0; i < splitLen; i++ { startRow := i * cellSplitNum endRow := (i + 1) * cellSplitNum if i == splitLen-1 && residue > 0 { endRow = lenCellData } tmpData := sheetInfo.CellData[startRow:endRow] tmpDataByte, tmpErr := json.Marshal(tmpData) if tmpErr != nil { errMsg = "保存失败" err = errors.New("保存失败:" + tmpErr.Error()) return } sheetDataList = append(sheetDataList, &excel.ExcelSheetData{ ExcelDataId: 0, ExcelInfoId: 0, ExcelSheetId: 0, Sort: i + 1, Data: string(tmpDataByte), ModifyTime: time.Now(), CreateTime: time.Now(), }) } addSheetItem.DataList = sheetDataList addSheetList = append(addSheetList, addSheetItem) } err = excel.AddExcelInfoAndSheet(excelInfo, addSheetList) return } // SaveCustomAnalysisTable 编辑自定义分析表格 func SaveCustomAnalysisTable(excelInfo *excel.ExcelInfo, excelName, content, excelImage string, excelClassifyId int, sheetOpList []request.SheetOp) (err error, errMsg string, isSendEmail bool) { isSendEmail = true contentByte := []byte(content) var luckySheetList []LuckySheet err = json.Unmarshal(contentByte, &luckySheetList) if err != nil { return } // sheet内容为空 if len(luckySheetList) <= 0 { errMsg = "sheet内容为空" err = errors.New(errMsg) isSendEmail = false return } // sheet内容为空 //if len(sheetOpList) <= 0 { // errMsg = "sheet操作为空" // err = errors.New(errMsg) // isSendEmail = false // return //} excelClassify, err := excel.GetExcelClassifyById(excelClassifyId) if err != nil { if utils.IsErrNoRow(err) { errMsg = "分类不存在" err = errors.New(errMsg) isSendEmail = false return } errMsg = "获取分类信息失败" err = errors.New("获取分类信息失败,Err:" + err.Error()) return } if excelClassify == nil { errMsg = "分类不存在" err = errors.New(errMsg) isSendEmail = false return } if excelClassify.Source != utils.CUSTOM_ANALYSIS_TABLE { errMsg = "当前分类不是自定义分析分类" err = errors.New("当前分类不是自定义分析分类") isSendEmail = false return } // 校验是否同名文件 { var condition string var pars []interface{} condition += " AND excel_classify_id=? AND excel_info_id !=? " pars = append(pars, excelClassifyId, excelInfo.ExcelInfoId) condition += " AND excel_name=? " pars = append(pars, excelName) // 获取分类下是否存在该表格名称 count, tmpErr := excel.GetExcelInfoCountByCondition(condition, pars) if tmpErr != nil { errMsg = "判断表格名称是否存在失败" err = errors.New("判断表格名称是否存在失败,Err:" + tmpErr.Error()) return } if count > 0 { errMsg = "表格名称已存在,请重新填写表格名称" err = errors.New(errMsg) isSendEmail = false return } } // 表格 excelInfo.ExcelName = excelName // 如果分类不传入的话,那么分类不变更 if excelClassifyId <= 0 { excelInfo.ExcelClassifyId = excelClassifyId } // 如果缩略图不传入的话,那么缩略图不变更 if excelImage != `` { excelInfo.ExcelImage = excelImage } excelInfo.ModifyTime = time.Now() updateExcelInfoParam := []string{"ExcelName", "ExcelClassifyId", "ExcelImage", "ModifyTime"} addSheetList := make([]excel.AddExcelSheetParams, 0) // sheet处理 sheetNameMap := make(map[string]string) for k, sheetInfo := range luckySheetList { sheetName := utils.TrimLRStr(sheetInfo.Name) _, ok := sheetNameMap[sheetName] if ok { errMsg = "excel表中存在同名sheet" err = errors.New(errMsg) isSendEmail = false return } sheetConf, tmpErr := json.Marshal(sheetInfo.Config) if tmpErr != nil { err = tmpErr return } // 计算公式 sheetCalcChain, tmpErr := json.Marshal(sheetInfo.CalcChain) if tmpErr != nil { err = tmpErr return } addSheetItem := excel.AddExcelSheetParams{ ExcelSheetId: 0, ExcelInfoId: excelInfo.ExcelInfoId, SheetName: sheetName, Index: sheetInfo.Index, Sort: k, Config: string(sheetConf), CalcChain: string(sheetCalcChain), } lenCellData := len(sheetInfo.CellData) splitLen := lenCellData / cellSplitNum residue := lenCellData % cellSplitNum if residue > 0 { splitLen += 1 } sheetDataList := make([]*excel.ExcelSheetData, 0) for i := 0; i < splitLen; i++ { startRow := i * cellSplitNum endRow := (i + 1) * cellSplitNum if i == splitLen-1 && residue > 0 { endRow = lenCellData } tmpData := sheetInfo.CellData[startRow:endRow] tmpDataByte, tmpErr := json.Marshal(tmpData) if tmpErr != nil { errMsg = "保存失败" err = errors.New("保存失败:" + tmpErr.Error()) return } sheetDataList = append(sheetDataList, &excel.ExcelSheetData{ ExcelDataId: 0, ExcelInfoId: excelInfo.ExcelInfoId, ExcelSheetId: 0, Sort: i + 1, Data: string(tmpDataByte), ModifyTime: time.Now(), CreateTime: time.Now(), }) } addSheetItem.DataList = sheetDataList addSheetList = append(addSheetList, addSheetItem) } err = excel.SaveExcelInfoAndSheet(excelInfo, updateExcelInfoParam, addSheetList) return } type LuckySheet struct { Name string `json:"name"` //Config struct { // Columnlen struct { // Num15 int `json:"15"` // Num16 int `json:"16"` // Num20 int `json:"20"` // Num34 int `json:"34"` // Num35 int `json:"35"` // } `json:"columnlen"` //} `json:"config"` Config interface{} Index string `json:"index"` Order int `json:"order"` ZoomRatio float64 `json:"zoomRatio"` ShowGridLines string `json:"showGridLines"` DefaultColWidth int `json:"defaultColWidth"` DefaultRowHeight int `json:"defaultRowHeight"` CellData []LuckySheetCellData `json:"celldata"` CalcChain []interface{} `json:"calcChain"` //DataVerification struct { //} `json:"dataVerification"` //Hyperlink struct { //} `json:"hyperlink"` //Hide int `json:"hide"` } // LuckySheetCellData 单元格数据 type LuckySheetCellData struct { R int `json:"r"` C int `json:"c"` V interface{} `json:"v,omitempty"` }