当前位置：首页 > java >正文

goFrame框架中如何实现文件的excel导出

java 2025/5/13 22:31:35

需求场景

公司业务有一个excel导出的需求，数据量10w+，框架是goFrame。对一些需要处理的数据根据字典进行转化输出为string类型的值，然后转成excel表格。但是批量导出的数据又是很多条，我的实现采用了：协程、缓存、bytes、锁等进行实现的。

代码实现

logic的文件下的某个文件:logic/test.go

type sTest struct{}var (exportCache  = make(map[string]*mid.ExportCacheItem)cacheLock    sync.RWMutexcols = dao.Test.Columns()
)// 导出函数处理 req：前端传递主键id []int
func (*sAccountEvent) ExportData(ctx context.Context, req *v2.EventExportReq) (err error) {longCtx, cancel := context.WithTimeout(context.Background(), 1*time.Hour)defer cancel()// 缓存 Key：基于 req.Ids 排序后的哈希生成sort.Slice(req.Ids, func(i, j int) bool { return req.Ids[i] < req.Ids[j] })cacheKey := fmt.Sprintf("export:%x", md5.Sum([]byte(fmt.Sprintf("%v", req.Ids))))// 尝试读取缓存var allDataCache []*mid.TestEventResponsevar found boolif allDataCache, found = GetExportCache(cacheKey); !found {// 缓存未命中，分批次查询并构建缓存const batchSize = 1000allDataCache = make([]*mid.TestEventResponse, 0)for i := 0; i < len(req.Ids); i += batchSize {start := iend := i + batchSizeif end > len(req.Ids) {end = len(req.Ids)}// 构建子 ID 切片subIds := req.Ids[start:end]// 查询当前批次数据var batchData []*mid.TestEventResponseerr = dao.AccountEvent.Ctx(longCtx).WhereIn(cols.Id, subIds).Scan(&batchData)if err != nil {return gerror.Wrapf(err, "分批查询失败（%d-%d）", start, end)}allDataCache = append(allDataCache, batchData...)}SetExportCache(cacheKey, allDataCache)}// 构建 Excel 数据excelData := make([]*mid.TestEventExcel, 0, len(allDataCache))for _, event := range allDataCache {// 根据公司业务的字典翻译处理event.testExtraData = translateExtraDataHandler(event.OtherData)excelData = append(excelData, convertToExcelStruct(event))}// 构建 Excel 文件fileBytes, err := buildExcelBytes(excelData)if err != nil {return gerror.Wrap(err, "生成Excel失败")}// 设置下载响应头response := g.RequestFromCtx(ctx).Responseresponse.Header().Set("Content-Type", "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=UTF-8")response.Header().Set("Content-Disposition", fmt.Sprintf("attachment; filename=%s","data.xlsx",))// 直接返回字节流response.Write(fileBytes)return
}// 构建bytes返回给前端处理
func buildExcelBytes(data []*mid.TestEventExcel) ([]byte, error) {f := excelize.NewFile()sheet := "Sheet1"index, _ := f.NewSheet(sheet)headers := []string{"主键", "Key值", "Value值",}for rowNum, item := range data {row := rowNum + 2_ = f.SetCellValue(sheet, fmt.Sprintf("A%d", row), item.Id)_ = f.SetCellValue(sheet, fmt.Sprintf("B%d", row), item.Key)_ = f.SetCellValue(sheet, fmt.Sprintf("C%d", row), item.Value)// 其他需要设置的字段}f.SetActiveSheet(index)// 返回字节流buff, err := f.WriteToBuffer()if err != nil {return nil, err}return buff.Bytes(), nil
}// convertToExcelStruct 转换为Excel结构
func convertToExcelStruct(event *mid.AccountEventResponse) *mid.TestEventExcel{return &mid.TestEventExcel{Id:                   event.Id,Key:            event.Key,Value:         event.Value,// ..........}
}// SetExportCache 缓存原始数据
func SetExportCache(key string, data []*mid.TestEventResponse) {cacheLock.Lock()defer cacheLock.Unlock()exportCache[key] = &mid.ExportCacheItem{Data:      data,ExpiresAt: time.Now().Add(10 * time.Minute),}
}// GetExportCache 获取缓存数据
func GetExportCache(key string) ([]*mid.TestEventResponse, bool) {cacheLock.RLock()defer cacheLock.RUnlock()item, ok := exportCache[key]if !ok || item.ExpiresAt.Before(time.Now()) {return nil, false}return item.Data, true
}