golang使用Excelize导出表格直接下载到本地

import (

"github.com/gogf/gf/v2/frame/g"
"github.com/gogf/gf/v2/net/ghttp"
"github.com/gogf/gf/v2/os/gcache"
"github.com/gogf/gf/v2/os/gtime"
"github.com/gogf/gf/v2/util/gconv"
"github.com/xuri/excelize/v2"

）

//导出excel
func (c *communicationStatisticBlockService) ExportStatistic(ctx context.Context, req *v1.ExportStatisticReq) (res v1.ExportStatisticListApiRes, err error) {
   //根据时间区间查询数据
   var beginYm = ""
   var endYm = ""
   //如果未传递时间区间 默认本月
   if req.BeginAt == "" || req.EndAt == "" {
      beginYm = gtime.Now().Format("ym")
      endYm = gtime.Now().Format("ym")
   } else {
      beginYm = gtime.NewFromStr(req.BeginAt).Format("ym")
      endYm = gtime.NewFromStr(req.EndAt).Format("ym")
   }
   var beginTableName = statusDeviceStatisticBaseTableName + "_" + beginYm
   var endTableName = statusDeviceStatisticBaseTableName + "_" + endYm
   f := excelize.NewFile()
   // 创建一个工作表
   index := f.NewSheet("Sheet1")
   //合并单元格
   err = f.MergeCell("Sheet1", "A1", "E1")
   if err != nil {
      return nil, err
   }
   err = f.MergeCell("Sheet1", "A2", "E2")
   if err != nil {
      return nil, err
   }
   //设置列宽
   err = f.SetColWidth("Sheet1", "A", "E", 25)
   if err != nil {
      return nil, err
   }
   //设置表头内容
   err = f.SetCellValue("Sheet1", "A1", "电表数据明细")
   if err != nil {
      return nil, err
   }
   err = f.SetCellValue("Sheet1", "A2", ""+req.BeginAt+"至"+req.EndAt+"")
   if err != nil {
      return nil, err
   }
   err = f.SetCellValue("Sheet1", "A3", "电表编号")
   if err != nil {
      return nil, err
   }
   err = f.SetCellValue("Sheet1", "B3", "起始读数")
   if err != nil {
      return nil, err
   }
   err = f.SetCellValue("Sheet1", "C3", "抄表读数")
   if err != nil {
      return nil, err
   }
   err = f.SetCellValue("Sheet1", "D3", "实际用电量")
   if err != nil {
      return nil, err
   }
   err = f.SetCellValue("Sheet1", "E3", "所属区域")
   if err != nil {
      return nil, err
   }
   //获取设备列表
   lists := ([]*entity.Device)(nil)
   devicelist := dao.Device.Ctx(ctx)
   err = devicelist.Scan(&lists)
   if err != nil {
      return nil, err
   }
   //遍历设备
   for i, v := range lists {
      curIndex := i + 4
      strIndex := strconv.Itoa(curIndex)
      //设置表内内容
      err = f.SetCellValue("Sheet1", "A"+strIndex, v.Name)
      if err != nil {
         return nil, err
      }
      result, _ := g.DB().GetAll(ctx, "select total_active_energy from "+beginTableName+" where device_sn = "+v.DeviceSn+" and created_at >= "+req.BeginAt+" order by total_active_energy  ASC")
      result2, _ := g.DB().GetAll(ctx, "select total_active_energy from "+endTableName+" where device_sn = "+v.DeviceSn+" and created_at >= "+req.EndAt+" order by total_active_energy  DESC")
      if len(result) > 0 && len(result2) > 0 {
         first := result[0]
         //设置起始读数
         err = f.SetCellValue("Sheet1", "B"+strIndex, first["total_active_energy"])
         if err != nil {
            return nil, err
         }
         last := result2[0]
         //设置抄表读数
         err = f.SetCellValue("Sheet1", "C"+strIndex, last["total_active_energy"])
         if err != nil {
            return nil, err
         }
         //
         //cha := last["total_active_energy"].Float64() - first["total_active_energy"].Float64()
         //f.SetCellValue("Sheet1", "D"+strIndex, cha)
      }
      //设置设备所属范围
      result3, _ := g.DB().GetOne(ctx, "select name from meter_group where group_id = ?", v.GroupId)
      re := result3["name"]
      err = f.SetCellValue("Sheet1", "E"+strIndex, re)
      if err != nil {
         return nil, err
      }
      //设置表格样式，垂直水平居中
      styleOne, err := f.NewStyle(
         `{"alignment":{"horizontal":"center","vertical":"center"}}`,
      )
      if err != nil {
         fmt.Println(err)
      }
      // 设置样式范围
      err = f.SetCellStyle("Sheet1", "A1", "E"+strIndex, styleOne)
      if err != nil {
         return nil, err
      }
      //设置表格样式，垂直水平居中，字体加粗
      styleTwo, err := f.NewStyle(
         `{"font":{"bold":true},"alignment":{"horizontal":"center","vertical":"center"}}`,
      )
      if err != nil {
         fmt.Println(err)
      }
      // 设置样式范围
      err = f.SetCellStyle("Sheet1", "A1", "E3", styleTwo)
      if err != nil {
         return nil, err
      }
   }
   r := ghttp.RequestFromCtx(ctx).Response
   // 设置工作簿的默认工作表
   f.SetActiveSheet(index)
   //设置表格名
   filename := "work.xlsx"
   // 返回数据到客户端
   r = ghttp.RequestFromCtx(ctx).Response
   r.Header().Add("Content-Type", "application/octet-stream")
   r.Header().Add("Content-Disposition", "attachment; filename="+filename)
   r.Header().Add("Content-Transfer-Encoding", "binary")
   //下载表格
   _ = f.Write(r.Writer)
   return

}

导出表格如图