go excel导出分sheet

流式导出 + 分 sheet 避免导出大文件无法打开

使用库

github.com/xuri/excelize/v2

file := excelize.NewFile()
sheetName := fmt.Sprintf("Sheet%d", 1)
//设置表名
//创建流式写入
writer, err := file.NewStreamWriter(sheetName)
//修改列宽
_ = writer.SetColWidth(1, 15, 12)
//设置表头
titles := []interface{}{"月份", "订单号", "金额(美元)", "订单时间"}
err = writer.SetRow("A1", titles)
if err != nil {
   return "", err
}
//初始化生成excel完毕
var (
   page       = 1
   limit      = 50000
   sheetPage  = 1 //工作簿页数
   sheetLimit = 500000
)
//开始生成excel
rowNum := 1
for {
   //var dataList [][]interface{}
   data, err := strategy.GetBillHandle(date, channel, orderId, project, page, limit)
   if err != nil {
      return "", err
   }
   count := data.(map[string]interface{})["count"].(int64)
   dataList := data.(map[string]interface{})["list"].([]*userPayList)
   for _, s := range dataList {
      //索引转单元格坐标
      cell, _ := excelize.CoordinatesToCellName(1, rowNum+1)
      //添加的数据
      _ = writer.SetRow(cell, []interface{}{
         fmt.Sprintf("%s", s.Date),
         s.OrderId,
         fmt.Sprintf("%f", s.Spend),
         s.Time,
      })
      rowNum++
   }
   if count >= int64(sheetLimit) && page*limit > sheetPage*sheetLimit {
      sheetPage++
      sheetName = fmt.Sprintf("Sheet%d", sheetPage)
      rowNum = 1 //初始化
      //结束流式写入
      _ = writer.Flush()
      _, _ = file.NewSheet(sheetName)
      writer, _ = file.NewStreamWriter(sheetName)
      //修改列宽
      _ = writer.SetColWidth(1, 15, 12)
      //设置表头
      _ = writer.SetRow("A1", titles)
   }
   fmt.Println(fmt.Sprintf("正在处理项目组账单导出列表第%d页，第%d-%d列数据，sheetname：%s", page, (page-1)*limit, page*limit, sheetName))
   if len(dataList) < limit {
      break
   }
   page = page + 1
}
// 保存excel
if err := file.SaveAs(filePath); err != nil {
   fmt.Println(err)
}