golang导出execl，数据没有写入execl

Question

func (f *Finance) BusinessSalaryExport(w http.ResponseWriter,req *iapi.BusinessSalaryListReq,authorization string){
    funs_log_tag := "财务管理-业务员待发工资导出"

    var err error
    if err = f.get_token_detail(&authorization);err != nil{
        http.Error(w, "token校验失败", http.StatusInternalServerError)
        return
    }

    (*req).PageSize = 1000
    //list,_,err := dao.BusinessSalaryList(f.TokenDetail.GuildId,req)
    list,_,err := dao.BusinessSalaryList(1,req)
    if err != nil{
        logx.Error(fmt.Sprintf("tags:%+v,%+v,数据库异常,req:%+v,err:%+v", f.LogTag,req,funs_log_tag,err))
        http.Error(w, "数据查询失败", http.StatusInternalServerError)
        return
    }

    fs := excelize.NewFile()
    defer fs.Close()

    // 创建工作表并设置表头
    index, err := fs.NewSheet("业务员待发放工资")
    if err != nil {
        fmt.Println(err)
        http.Error(w, "创建Excel失败", http.StatusInternalServerError)
        return
    }
    fs.SetActiveSheet(index)


    // 设置表头
    headers := []string{"月份", "业务员信息", "账号", "待发放金额", "状态", "时间"}
    for i, header := range headers {
        cell, _ := excelize.CoordinatesToCellName(i+1, 1)
        fs.SetCellValue("业务员待发放工资", cell, header)
    }

    count := len(list)
    var business_ids = make([]int64,0)
    for i:=0;i < count;i++{
        business_ids = append(business_ids,list[i].BusinessId)
    }

    //去除零值、去重
    business_ids = tools.RemoveNonZero(business_ids)
    business_ids = business_ids[0:tools.RemoveDuplicatesInt64(business_ids)]

    if len(business_ids) < 1{
        http.Error(w, "没有数据", http.StatusInternalServerError)
        return
    }

    //+++++++++++++++++获取主播所属业务员+++++++++++++++++
    business_list,err := dao.GuildAccountByIdsBatch(&business_ids,types.Guild_Business_Manager)
    if err != nil{
        logx.Error(fmt.Sprintf("tags:%+v,%+v,获取主播所属的业务员,数据库异常,req:%+v,err:%+v", f.LogTag,funs_log_tag,req,err))
        return
    }

    type business_detail struct{
        account string
        name    string
    }

    var business_map = make(map[int64]business_detail,0)
    for i,count := 0, len(business_list); i < count; i++{
        detail := business_list[i]

        business_map[detail.Id] = business_detail{name:detail.GuildAccountName,account:detail.GuildAccount}
    }

    for i:=0;i < count;i++{
        detail := list[i]

        var business_name,business_account string
        if val,ok := business_map[detail.BusinessId];ok{
            business_name = val.name
            business_account = val.account
        }

        var status = "-"
        if detail.Status == 1{
            status = "待发放" 
        }else if(detail.Status == 2){
            status = "发放成功" 
        }else if (detail.Status == 3){
            status = "发放失败" 
        }

        row := i + 2 // 从第2行开始，第1行是表头
        fs.SetCellValue("月份", fmt.Sprintf("A%d", row), detail.Month)
        fs.SetCellValue("业务员信息", fmt.Sprintf("B%d", row), business_name)
        fs.SetCellValue("账号", fmt.Sprintf("B%d", row), business_account)
        fs.SetCellValue("待发放金额", fmt.Sprintf("B%d", row), detail.Amount)
        fs.SetCellValue("状态", fmt.Sprintf("B%d", row), status)
        fs.SetCellValue("时间", fmt.Sprintf("B%d", row), tools.FormatDates(detail.CreateAt))

        /*data[i] = iapi.BusinessSalaryList{
            Id:detail.Id,
            BusinessId:detail.BusinessId,
            BusinessName:business_name,
            BusinessAccount:business_account,
            Amount:detail.Amount,
            Month:detail.Month,
            Status:detail.Status,
            CreateAt:tools.FormatDates(detail.CreateAt),
        }*/
    }

    w.Header().Set("Content-Type", "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet")
    w.Header().Set("Content-Disposition", "attachment; filename=business_salary.xlsx")
    w.Header().Set("Content-Transfer-Encoding", "binary")
    w.Header().Set("Cache-Control", "no-cache")

    // 将Excel文件写入HTTP响应
    if err := fs.Write(w); err != nil {
        fmt.Printf("写入Excel到响应失败: %+v\n", err)
        http.Error(w, "导出失败", http.StatusInternalServerError)
        return
    }
  }

我定义了生成execl的函数，并从浏览器下载，execl的表头写入到execl，但数据没有写入，数据库确认有数据

Answer 1

让【宝码香车】来帮您解答，本回答参考DeepSeek编写，并整理提供。

如果答案让您满意，请采纳、点赞、关注，非常感谢！
.
每日一句正能量，让它带给您好心情：困难像一面墙，翻过去，就能看到不一样的风景。
.

看了您的代码，问题主要出现在数据写入时的单元格定位上。在循环中写入数据时，您错误地使用了工作表名称而不是单元格坐标。

主要问题分析

1. 单元格定位错误：在 fs.SetCellValue 中，第一个参数应该是工作表名称，但您传入了表头名称
2. 列坐标错误：所有数据都写在了B列，应该分别写入不同的列

修正后的代码

func (f *Finance) BusinessSalaryExport(w http.ResponseWriter, req *iapi.BusinessSalaryListReq, authorization string) {
    funs_log_tag := "财务管理-业务员待发工资导出"


    var err error
    if err = f.get_token_detail(&authorization); err != nil {
        http.Error(w, "token校验失败", http.StatusInternalServerError)
        return
    }


    (*req).PageSize = 1000
    list, _, err := dao.BusinessSalaryList(1, req)
    if err != nil {
        logx.Error(fmt.Sprintf("tags:%+v,%+v,数据库异常,req:%+v,err:%+v", f.LogTag, req, funs_log_tag, err))
        http.Error(w, "数据查询失败", http.StatusInternalServerError)
        return
    }


    fs := excelize.NewFile()
    defer fs.Close()


    // 创建工作表并设置表头
    index, err := fs.NewSheet("业务员待发放工资")
    if err != nil {
        fmt.Println(err)
        http.Error(w, "创建Excel失败", http.StatusInternalServerError)
        return
    }
    fs.SetActiveSheet(index)


    // 设置表头
    headers := []string{"月份", "业务员信息", "账号", "待发放金额", "状态", "时间"}
    for i, header := range headers {
        cell, _ := excelize.CoordinatesToCellName(i+1, 1)
        fs.SetCellValue("业务员待发放工资", cell, header)
    }


    count := len(list)
    var business_ids = make([]int64, 0)
    for i := 0; i < count; i++ {
        business_ids = append(business_ids, list[i].BusinessId)
    }


    // 去除零值、去重
    business_ids = tools.RemoveNonZero(business_ids)
    business_ids = business_ids[0:tools.RemoveDuplicatesInt64(business_ids)]


    if len(business_ids) < 1 {
        http.Error(w, "没有数据", http.StatusInternalServerError)
        return
    }


    // 获取主播所属业务员
    business_list, err := dao.GuildAccountByIdsBatch(&business_ids, types.Guild_Business_Manager)
    if err != nil {
        logx.Error(fmt.Sprintf("tags:%+v,%+v,获取主播所属的业务员,数据库异常,req:%+v,err:%+v", f.LogTag, funs_log_tag, req, err))
        return
    }


    type business_detail struct {
        account string
        name    string
    }


    var business_map = make(map[int64]business_detail, 0)
    for i, count := 0, len(business_list); i < count; i++ {
        detail := business_list[i]
        business_map[detail.Id] = business_detail{name: detail.GuildAccountName, account: detail.GuildAccount}
    }


    // 修正数据写入部分
    for i := 0; i < count; i++ {
        detail := list[i]


        var business_name, business_account string
        if val, ok := business_map[detail.BusinessId]; ok {
            business_name = val.name
            business_account = val.account
        }


        var status = "-"
        if detail.Status == 1 {
            status = "待发放"
        } else if detail.Status == 2 {
            status = "发放成功"
        } else if detail.Status == 3 {
            status = "发放失败"
        }


        row := i + 2 // 从第2行开始，第1行是表头
        
        // 修正：使用正确的工作表名称和列坐标
        fs.SetCellValue("业务员待发放工资", fmt.Sprintf("A%d", row), detail.Month)
        fs.SetCellValue("业务员待发放工资", fmt.Sprintf("B%d", row), business_name)
        fs.SetCellValue("业务员待发放工资", fmt.Sprintf("C%d", row), business_account)
        fs.SetCellValue("业务员待发放工资", fmt.Sprintf("D%d", row), detail.Amount)
        fs.SetCellValue("业务员待发放工资", fmt.Sprintf("E%d", row), status)
        fs.SetCellValue("业务员待发放工资", fmt.Sprintf("F%d", row), tools.FormatDates(detail.CreateAt))
    }


    w.Header().Set("Content-Type", "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet")
    w.Header().Set("Content-Disposition", "attachment; filename=business_salary.xlsx")
    w.Header().Set("Content-Transfer-Encoding", "binary")
    w.Header().Set("Cache-Control", "no-cache")


    // 将Excel文件写入HTTP响应
    if err := fs.Write(w); err != nil {
        fmt.Printf("写入Excel到响应失败: %+v\n", err)
        http.Error(w, "导出失败", http.StatusInternalServerError)
        return
    }
}

主要修改点

1. 修正单元格写入：将 fs.SetCellValue("月份", ...) 改为 fs.SetCellValue("业务员待发放工资", ...)
2. 修正列坐标：

• A列：月份
• B列：业务员信息
• C列：账号
• D列：待发放金额
• E列：状态
• F列：时间

调试建议

如果问题仍然存在，建议添加调试代码：

// 在写入数据后添加验证
for i := 0; i < count; i++ {
    // ... 数据写入代码
}


// 验证数据是否写入成功
for i := 1; i <= count+1; i++ {
    month, _ := fs.GetCellValue("业务员待发放工资", fmt.Sprintf("A%d", i))
    fmt.Printf("第%d行A列数据: %s\n", i, month)
}

这样修改后，数据应该能正确写入Excel文件了。