GO 数据库内容导出到Excel表格

1.导出列表

func exportTaskList(c *gin.Context){u :=user.GetCookie(c)Data, err :=handleData(c)iferr!=nil{c.JSON(http.StatusInternalServerError, err.Error())return}warehouseId, _ :=Data["warehouse_id"].(string)ifok, err :=order.GetWareHouseEmpty(warehouseId);ok{c.JSON(http.StatusBadRequest, err)return}allList, err :=queryAllTaskFromDB(warehouseId, u)iferr!=nil{c.JSON(http.StatusInternalServerError, gin.H{"code":500,"msg":"查询失败"})return}//1. 创建文件 f :=excelize.NewFile()sheetName :="任务列表"//2. v1 用法：NewSheet 只返回一个 int 索引，没有 error index :=f.NewSheet(sheetName)// 这里只接收一个值，不再报错 //3. 设置活动工作表 f.SetActiveSheet(index)f.DeleteSheet("Sheet1")//4. 设置表头 headers :=[]string{"订单编号","状态","类型","容器码","起点位置","目标位置","下发时间","完成时间","用时","执行结果","组盘人","创建人","分拣人","所属仓库"}fori, h :=range headers{cell :=fmt.Sprintf("%c1",'A'+i)f.SetCellValue(sheetName, cell, h)}//5. v1 用法：NewStyle 返回(int, error)，用两个变量接收 style, err :=f.NewStyle(`{"font":{"bold":true}}`)// v1 样式用 JSON 字符串iferr==nil{f.SetCellStyle(sheetName,"A1", fmt.Sprintf("%c1",'A'+len(headers)-1), style)}users, _ :=getUserAll(u)inventorys, _ :=getInventoryAll(u)//6. 填充数据forrowIdx, row :=range allList{rowNum :=rowIdx +2wcs_sn, _ :=row["wcs_sn"].(string)status, _ :=row["status"].(string)ss :=""switch status{case"status_wait":ss="待执行"breakcase"status_progress":ss="进行中"breakcase"status_success":ss="已完成"breakcase"status_cancel":ss="已取消"breakcase"status_fail":ss="失败"breakcase"status_delete":ss="已删除"breakcase"status_suspend":ss="已暂停"break}types, _ :=row["types"].(string)tt :=""switch types{case"in":tt="入库"breakcase"out":tt="出库"breakcase"return":tt="回库"breakcase"move":tt="移库"breakcase"outEmpty":tt="空托出库"breakcase"inEmpty":tt="空托入库"breakcase"outMaterial":tt="空筐出库"breakcase"inreturn":tt="盘点回库"breakcase"nin":tt="移车"break}container_code, _ :=row["container_code"].(string)port_addr, _ :=row["port_addr"].(mo.M)src :=""ifport_addr!=nil{src=fmt.Sprintf("%d-%d-%d", port_addr["f"], port_addr["c"], port_addr["r"])}dst :=""addr, _ :=row["addr"].(mo.M)ifaddr!=nil{dst=fmt.Sprintf("%d-%d-%d", addr["f"], addr["c"], addr["r"])}send_time, _ :=row["send_time"].(mo.DateTime)p :=send_time.Time()send_time_str :=p.Format("2006-01-02")ifsend_time_str=="1970-01-01"{send_time_str=""}complete_time, _ :=row["complete_time"].(mo.DateTime)cc :=complete_time.Time()complete_time_str :=cc.Format("2006-01-02")ifcomplete_time_str=="1970-01-01"{complete_time_str=""}minsecdiff :=""ifcomplete_time_str==""{minsecdiff=""}ifsrc==dst{minsecdiff=""}ifsend_time_str!=""&&complete_time_str!=""{duration :=p.Sub(cc)ifduration<0{duration=-duration}totalSeconds :=int(duration.Seconds())diffMinutes :=totalSeconds /60diffSeconds :=totalSeconds %60minsecdiff=fmt.Sprintf("%d分%d秒", diffMinutes, diffSeconds)}remark, _ :=row["remark"].(string)creatorStr :=""sort_creatorStr :=""groupStr :=""iflen(users)>0{creator, _ :=row["creator"].(mo.ObjectID)sort_creator, _ :=row["sort_creator"].(mo.ObjectID)creatorStr=users[creator]sort_creatorStr=users[sort_creator]iflen(inventorys)>0{group :=inventorys[wcs_sn]groupStr=users[group]}}f.SetCellValue(sheetName, fmt.Sprintf("A%d", rowNum), wcs_sn)f.SetCellValue(sheetName, fmt.Sprintf("B%d", rowNum), ss)f.SetCellValue(sheetName, fmt.Sprintf("C%d", rowNum), tt)f.SetCellValue(sheetName, fmt.Sprintf("D%d", rowNum), container_code)f.SetCellValue(sheetName, fmt.Sprintf("E%d", rowNum), src)f.SetCellValue(sheetName, fmt.Sprintf("F%d", rowNum), dst)f.SetCellValue(sheetName, fmt.Sprintf("G%d", rowNum), p)f.SetCellValue(sheetName, fmt.Sprintf("H%d", rowNum), cc)f.SetCellValue(sheetName, fmt.Sprintf("I%d", rowNum), minsecdiff)f.SetCellValue(sheetName, fmt.Sprintf("J%d", rowNum), remark)f.SetCellValue(sheetName, fmt.Sprintf("K%d", rowNum), groupStr)f.SetCellValue(sheetName, fmt.Sprintf("L%d", rowNum), creatorStr)f.SetCellValue(sheetName, fmt.Sprintf("M%d", rowNum), sort_creatorStr)f.SetCellValue(sheetName, fmt.Sprintf("N%d", rowNum), warehouseId)}//7. 设置列宽等样式 f.SetColWidth(sheetName,"A","A",10)f.SetColWidth(sheetName,"B","B",10)f.SetColWidth(sheetName,"C","C",10)f.SetColWidth(sheetName,"D","D",10)f.SetColWidth(sheetName,"E","E",10)f.SetColWidth(sheetName,"F","F",10)f.SetColWidth(sheetName,"G","G",10)f.SetColWidth(sheetName,"H","H",10)f.SetColWidth(sheetName,"I","I",10)f.SetColWidth(sheetName,"J","J",10)f.SetColWidth(sheetName,"K","K",10)f.SetColWidth(sheetName,"L","L",10)f.SetColWidth(sheetName,"M","M",10)f.SetColWidth(sheetName,"N","N",10)//8. 输出文件 filename :=fmt.Sprintf("%s.xlsx", time.Now().Format("20060102_150405"))c.Header("Content-Type","application/vnd.openxmlformats-officedocument.spreadsheetml.sheet")c.Header("Content-Disposition", fmt.Sprintf("attachment; filename*=UTF-8''%s", filename))c.Header("Content-Disposition", fmt.Sprintf("attachment; filename=\"%s\"", filename))iferr :=f.Write(c.Writer);err!=nil{c.JSON(http.StatusInternalServerError, gin.H{"code":500,"msg":"生成文件失败"})}}

2. 获取数据库数据

func queryAllTaskFromDB(wId string, u ii.User)([]mo.M, error){// 获取用户 fil :=mo.Matcher{}fil.Eq("warehouse_id", wId)fil.In("status", mo.A{"status_success","status_delete","status_cancel"})list, err :=svc.Svc(u).Find(stocks.WmsTaskHistory, fil.Done())iferr!=nil{returnnil, err}returnlist, nil}

3.获取用户列表

func getUserAll(u ii.User)(map[mo.ObjectID]string, error){users:=make(map[mo.ObjectID]string)list, err :=svc.Svc(u).Find(stocks.WmsUser, mo.D{})iferr!=nil{returnnil, err}for_, row :=range list{_id, _ :=row[mo.ID.Key()].(mo.ObjectID)name, _ :=row["name"].(string)users[_id]=name}returnusers, nil}

4.获取入库单数据

func getInventoryAll(u ii.User)(map[string]mo.ObjectID, error){data :=make(map[string]mo.ObjectID)list, err :=svc.Svc(u).Find(stocks.WmsGroupInventory, mo.D{})iferr!=nil{returnnil, err}for_, row :=range list{wcsSn, _ :=row["wcs_sn"].(string)creator, _ :=row["creator"].(mo.ObjectID)data[wcsSn]=creator}returndata, nil}