文章目录
文件处理-os库
用io.Copy()给前端返回字节流
io.Copy(dst Writer, src Reader)字节直接写到文件流
func (m *FileBiz) DownloadFileV2(ctx *ctrl.Context, fileLink, fileName string) (err error) {
// 记录下载日志
record.BusinessLog(record.Debug, "RecordDownloadFileV2", fmt.Sprintf("filePath:%s,wsId:%s,email:%s", fileLink, ctx.GetString("ws_id"), ctx.GetString("email")), "")
// 获取地址异常
if fileLink == "" {
err = errInfo.ErrFilesNull
return
}
// 初始化
request, err := http.NewRequest("GET", fileLink, nil)
if err != nil {
record.BusinessLog(record.Error, "NewRequest", fmt.Sprintf("filePath:%s", fileLink), err.Error())
err = errInfo.ErrHttpInit
return
}
// 执行请求
clt := http.Client{}
resp, err := clt.Do(request)
if err != nil {
record.BusinessLog(record.Error, "HttpDp", fmt.Sprintf("filePath:%s", fileLink), err.Error())
err = errInfo.ErrHttpDo
return
}
defer func(Body io.ReadCloser) {
errClose := Body.Close()
if errClose != nil {
record.BusinessLog(record.Error, "FileClose", fmt.Sprintf("filePath:%s", fileLink), errClose.Error())
}
}(resp.Body)
// 响应头
ctx.Header("Content-Length", resp.Header.Get("Content-Length"))
ctx.Header("Content-Disposition", fmt.Sprintf("attachment; filename=\"%s\"", fileName))
ctx.Header("Content-Type", "application/octet-stream;charset=UTF-8")
ctx.Header("Set-Cookie", "download=success; Domain=.media.io; Path=/;")
// 响应流
written, err := io.Copy(ctx.ResponseWriter(), resp.Body)
if err != nil {
record.BusinessLog(record.Error, "IoCopy", fmt.Sprintf("filePath:%s, written:%d", fileLink, written), err.Error())
err = errInfo.ErrResponseWritten
}
return
}
用os来压缩打包文件夹
// ZipFolder 压缩文件夹并保持目录结构
func ZipFolder(src, dest string) error {
// 创建目标ZIP文件
zipFile, err := os.Create(dest)
if err != nil {
return err
}
defer zipFile.Close()
// 创建一个ZIP writer
zipWriter := zip.NewWriter(zipFile)
defer zipWriter.Close()
// 遍历源文件夹及其子目录并添加文件到ZIP
err = filepath.Walk(src, func(path string, info os.FileInfo, err error) error {
if err != nil {
return err
}
// 获取文件的相对路径
relPath, err := filepath.Rel(src, path)
if err != nil {
return err
}
// 创建一个新的ZIP文件头
header, err := zip.FileInfoHeader(info)
if err != nil {
return err
}
header.Name = filepath.ToSlash(relPath)
// 如果是目录，需要在名称后添加"/"以便在ZIP中表示为目录
if info.IsDir() {
header.Name += "/"
}
// 将文件头添加到ZIP中
writer, err := zipWriter.CreateHeader(header)
if err != nil {
return err
}
// 如果是目录，不需要写入内容
if info.IsDir() {
return nil
}
// 打开源文件并将其内容复制到ZIP中
file, err := os.Open(path)
if err != nil {
return err
}
defer file.Close()
_, err = io.Copy(writer, file)
return err
})
return err
}
压缩后通过context.SendFile来发送
func (m *FileBiz) PixpicBatchDownloadFile(ctx *ctrl.Context, taskId string) (err error) {
task, err := m.pixpicTaskModel.GetOne("task_id", taskId)
if err != nil {
record.BusinessLog(record.Error, "GetOne", fmt.Sprintf("taskId:%s", taskId), err.Error())
err = errInfo.ErrDBGetTask
return
}
// 获取资源信息
resourcesInput, err := m.pixpicResourceModel.GetInputs(task.TaskId)
if err != nil {
record.BusinessLog(record.Error, "GetOneInput", fmt.Sprintf("taskId:%s", task.ID), err.Error())
err = errInfo.ErrDBGetResource
return
}
// 创建文件夹，规则：taskid_template,以及子目录input,output
destName := fmt.Sprintf("%s_%s", taskId, task.Template) // url.QueryEscape
destPath := fmt.Sprintf("%s/%s", "download", destName)
subPathList := []string{
fmt.Sprintf("%s/%s", destPath, "input"),
}
if task.Status == TaskStatusSuccess {
subPathList = append(subPathList, fmt.Sprintf("%s/%s", destPath, "output"))
}
_, errPath := os.Stat(destPath)
if errPath != nil {
err = os.Mkdir(destPath, os.ModePerm)
if err != nil {
record.BusinessLog(record.Error, "OsMkdir", fmt.Sprintf("destPath:%s", destPath), err.Error())
err = errInfo.ErrMkDir
return
}
for _, val := range subPathList {
_, errsubPath := os.Stat(val)
if errsubPath != nil {
err = os.Mkdir(val, os.ModePerm)
if err != nil {
record.BusinessLog(record.Error, "OsMkdir", fmt.Sprintf("destSubPath:%s", val), err.Error())
err = errInfo.ErrMkDir
return
}
}
}
}
// 删除临时文件夹及文件
defer func(path string) {
errR := os.RemoveAll(path)
if errR != nil {
record.BusinessLog(record.Error, "OsRemoveAll", fmt.Sprintf("destPath:%s", path), errR.Error())
return
}
record.BusinessLog(record.Info, "OsRemoveAll", fmt.Sprintf("destPath:%s", path), "ok")
}(destPath)
// 写入资源
srv := service.GetStorageService()
for _, val := range resourcesInput {
tmpList := strings.Split(val.Url, "/")
suffix := "_false"
if val.Status == 1 {
suffix = "_true"
}
fileName := tmpList[len(tmpList)-1]
index := strings.LastIndex(fileName, ".")
if index > 0 {
fileName = fileName[:index] + suffix + fileName[index:]
}
filePath := fmt.Sprintf("%s/%s", subPathList[0], fileName)
signUrl := srv.GetSignUrl(context.TODO(), val.Url, 0)
errDF := oss.DownloadFile2(signUrl, filePath)
if errDF != nil {
record.BusinessLog(record.Error, "DownloadFile2", fmt.Sprintf("download err,file: %s, taskId:%s", signUrl, taskId), err.Error())
continue
}
}
if task.Status == TaskStatusSuccess {
resourcesOutput, err2 := m.pixpicResourceModel.GetOutputs(task.TaskId, 100)
if err2 != nil {
record.BusinessLog(record.Error, "GetOneOutput", fmt.Sprintf("taskId:%s", task.ID), err.Error())
err2 = errInfo.ErrDBGetResource
return err2
}
for _, val := range resourcesOutput {
tmpList := strings.Split(val.Url, "/")
fileName := tmpList[len(tmpList)-1]
filePath := fmt.Sprintf("%s/%s", subPathList[1], fileName)
signUrl := srv.GetSignUrl(context.TODO(), val.Url, 0)
errDF := oss.DownloadFile2(signUrl, filePath)
if errDF != nil {
record.BusinessLog(record.Error, "DownloadFile2", fmt.Sprintf("download err,file: %s, taskId:%s", signUrl, taskId), err.Error())
continue
}
}
filePath := fmt.Sprintf("%s/%s.safetensors", destPath, taskId)
signUrl := srv.GetSignUrl(context.TODO(), fmt.Sprintf("pixpic/models/%s/%s.safetensors", taskId, taskId), 0)
errDF := oss.DownloadFile2(signUrl, filePath)
if errDF != nil {
record.BusinessLog(record.Error, "DownloadFile2", fmt.Sprintf("download err,file: %s, taskId:%s", signUrl, taskId), err.Error())
}
}
// 打包文件
downloadFileName := fmt.Sprintf("%s.zip", destName)
downloadFilePath := fmt.Sprintf("%s", downloadFileName)
err = zip.ZipFolder(destPath, downloadFilePath)
if err != nil {
record.BusinessLog(record.Error, "ZipZip", fmt.Sprintf("downloadFilePath:%s", downloadFilePath), err.Error())
err = errInfo.ErrZip
return
}
// 下载
downloadFileName = strings.ReplaceAll(downloadFileName, ",", "+")
err = ctx.SendFile(downloadFilePath, downloadFileName)
if err != nil {
record.BusinessLog(record.Error, "SendFile", fmt.Sprintf("downloadFilePath:%s downloadFileName:%s", downloadFilePath, downloadFileName), err.Error())
err = errInfo.ErrSendFile
return
}
return
}
读写excel-excelize库
windows下最高支持 github.com/xuri/excelize/v2@v2.6.0版本
例1-基础的读和写
func pushEmail(excel_input string, excel_result string, survey_name string) {
f, err := excelize.OpenFile(excel_input)
if err != nil {
fmt.Println(err)
return
}
defer func() {
if err := f.Close(); err != nil {
fmt.Println(err)
}
}()
emails := make([]string, 0)
rows, err := f.Rows("Sheet1")
if err != nil {
fmt.Println(err)
return
}
for rows.Next() {
row, err := rows.Columns()
if err != nil {
fmt.Println(err)
}
if len(row) > 0 {
emails = append(emails, row[0])
}
}
if err = rows.Close(); err != nil {
fmt.Println(err)
}
//fmt.Println(emails)
fWrite := excelize.NewFile()
defer func() {
if err := fWrite.Close(); err != nil {
fmt.Println(err)
}
}()
streamWriter, err := fWrite.NewStreamWriter("Sheet1")
if err != nil {
fmt.Println(err)
return
}
streamWriter.SetRow("A1", []interface{}{"email", "flag"})
for i := 1; i < len(emails); i++ {
//fmt.Println(emails[i])
email, flag := PushUserEmail2(emails[i], survey_name, "FamiSafe", nil)
if flag == string("0") {
fmt.Println(email)
fmt.Println(flag)
break
}
axis, _ := excelize.CoordinatesToCellName(1, i+1)
streamWriter.SetRow(axis, []interface{}{email, flag})
}
if err = streamWriter.Flush(); err != nil {
fmt.Println(err)
return
}
fWrite.SaveAs(excel_result)
}
例2-写多个Sheet数据到excel
package user
import (
"bufio"
"bytes"
"context"
"fmt"
"github.com/gogf/gf/errors/gerror"
"github.com/gogf/gf/frame/g"
"github.com/xuri/excelize/v2"
s "pxgit.300624.cn/gaea-server/x/core/storage"
"pxgit.300624.cn/pixso-service/pixso-service-web/app/dao"
"pxgit.300624.cn/pixso-service/pixso-service-web/app/define"
"pxgit.300624.cn/pixso-service/pixso-service-web/app/service/msg"
"pxgit.300624.cn/pixso-service/pixso-service-web/common/intranet/statistics"
"pxgit.300624.cn/pixso-service/pixso-service-web/common/intranet/user"
"pxgit.300624.cn/pixso-service/pixso-service-web/common/tool/logger"
"pxgit.300624.cn/pixso-service/pixso-service-web/common/tool/storage"
"pxgit.300624.cn/pixso-service/pixso-service-web/common/tool/utils"
"sort"
"strconv"
)
func ExportExportEntActive(ctx context.Context, req *define.UserExportActiveStatReq) error {
subInfo := &define.ExportStatInfo{
UserId: req.UserId,
SpaceId: req.SpaceId,
Id: req.Id,
FileNameWithSuffix: fmt.Sprintf("%s.xlsx", req.FileName),
}
f, err := ExportEntActiveToExcel(ctx, req)
if err != nil {
logger.BaseLog().Errorf("ExportExportEntActive.ExportEntActiveToExcel, err:%+v", err)
err2 := dao.EntExportQueue.UpdateInterface(ctx, req.Id, g.Map{dao.EntExportQueue.Columns.ExportStatus: statistics.ExportFail})
if err2 != nil {
return err2
}
subInfo.ExportStatus = statistics.ExportFail
msg.UserExportStatMsg(ctx, subInfo)
return err
}
var b bytes.Buffer
writer := bufio.NewWriter(&b)
err = f.Write(writer)
if err != nil {
return err
}
var EXLHeader = map[string]interface{}{"Content-FileType": storage.TypeFile, "content-type": storage.TypeFile,
s.HeaderContentDisposition: fmt.Sprintf("attachment;filename=%s.xlsx", req.FileName)} // 存储时header头带上"Content-Disposition"，存二进制文件的后缀格式
objectId, err := storage.Public().NewObject(ctx, "xlsx", int64(b.Len()), EXLHeader, &b)
if err != nil {
logger.BaseLog().Errorf("ExportExportEntActive.storage.NewObject, err:%+v", err)
return gerror.Wrap(err, "ExportExportEntActive.storage.NewObject")
}
err = dao.EntExportQueue.UpdateInterface(ctx, req.Id, g.Map{dao.EntExportQueue.Columns.ExportStatus: statistics.ExportSuccess,
dao.EntExportQueue.Columns.ObjectKey: objectId})
if err != nil {
return err
}
subInfo.ExportStatus = statistics.ExportSuccess
fileUrl, err := storage.Public().SignUrl(ctx, objectId, 60*60*24*7)
if err != nil {
logger.BaseLog().Errorf("ExportExportEntActive.storage.SignUrl, err:%+v", err)
return gerror.Wrap(err, "ExportExportEntActive.storage.SignUrl")
}
subInfo.FileUrl = fileUrl
msg.UserExportStatMsg(ctx, subInfo)
return nil
}
func ExportEntActiveToExcel(ctx context.Context, req *define.UserExportActiveStatReq) (*excelize.File, error) {
userActive := &statistics.InnerEntUserActiveArgs{
Product: statistics.ProductPixso,
IntervalType: statistics.InterValDay,
StartTime: req.StartTime,
EndTime: req.EndTime,
SpaceId: req.SpaceId,
}
userEdit := &statistics.InnerEntUserEditArgs{
Product: statistics.ProductPixso,
IntervalType: statistics.InterValDay,
StartTime: req.StartTime,
EndTime: req.EndTime,
SpaceId: req.SpaceId,
ObjType: statistics.PixsoDesignFile,
}
f := excelize.NewFile()
defer func() {
if err := f.Close(); err != nil {
fmt.Println(err)
}
}()
// 1 概况sheet
// 活跃天数
entUserActiveInfoResp, err := statistics.NewStatCenter().EntUserActiveInfo(userActive)
if err != nil {
return nil, err
}
userIdList := make([]uint64, 0) // userlist排序下
for k := range entUserActiveInfoResp.EntUserActiveInfo {
userIdList = append(userIdList, k)
}
sort.Sort(utils.Uint64Slice(userIdList))
// 设计文件编辑活跃天数、设计文件总编辑时长
entUserEditInfoResp, err := statistics.NewStatCenter().EntUserEditInfo(userEdit)
if err != nil {
return nil, err
}
// 账号、用户名、席位信息
uMap, err := user.MapById(ctx, userIdList)
if err != nil {
return nil, err
}
flMap, err := dao.UserTag.FileLevelMap(ctx, req.SpaceId, userIdList)
if err != nil {
return nil, err
}
sheetName := req.SheetNames[0]
f.NewSheet(sheetName)
streamWriter, err := f.NewStreamWriter(sheetName)
if err != nil {
return nil, gerror.Wrap(err, "WriteToSheet.NewStreamWriter")
}
columnNamesInterface := make([]interface{}, 0)
for _, c := range req.SheetColumnMap[sheetName] {
columnNamesInterface = append(columnNamesInterface, c)
}
streamWriter.SetRow("A1", columnNamesInterface)
for i, userId := range userIdList {
account := ""
if len(uMap[userId].UniqueId) > 0 {
account = uMap[userId].UniqueId
} else if len(uMap[userId].Mobile) > 0 {
account = uMap[userId].Mobile
} else if len(uMap[userId].Email) > 0 {
account = uMap[userId].Email
}
fileLevelString := strconv.FormatUint(uint64(flMap[userId]), 10)
if v, ok := req.FileLevelMap[flMap[userId]]; ok {
fileLevelString = v
}
row := []interface{}{account, uMap[userId].NickName, fileLevelString, entUserActiveInfoResp.EntUserActiveInfo[userId],
entUserEditInfoResp.EntUserEditInfo[userId].ActiveDay, entUserEditInfoResp.EntUserEditInfo[userId].Duration}
axis, _ := excelize.CoordinatesToCellName(1, i+2)
streamWriter.SetRow(axis, row)
}
if err = streamWriter.Flush(); err != nil {
return nil, gerror.Wrap(err, "WriteToSheet.Flush ")
}
// 2 活跃用户数sheet
entUserActiveTrendResp, err := statistics.NewStatCenter().EntUserActiveTrend(userActive)
if err != nil {
return nil, err
}
dateList := make([]string, 0, len(entUserActiveTrendResp.EntUserActiveTrend))
for k := range entUserActiveTrendResp.EntUserActiveTrend {
dateList = append(dateList, k)
}
sort.Sort(utils.StringSlice(dateList))
sheetName = req.SheetNames[1]
f.NewSheet(sheetName)
streamWriter, err = f.NewStreamWriter(sheetName)
if err != nil {
return nil, gerror.Wrap(err, "WriteToSheet.NewStreamWriter")
}
columnNamesInterface1 := make([]interface{}, 0)
for _, c := range req.SheetColumnMap[sheetName] {
columnNamesInterface1 = append(columnNamesInterface1, c)
}
streamWriter.SetRow("A1", columnNamesInterface1)
for i, date := range dateList {
row := []interface{}{date, entUserActiveTrendResp.EntUserActiveTrend[date]}
axis, _ := excelize.CoordinatesToCellName(1, i+2)
streamWriter.SetRow(axis, row)
}
if err = streamWriter.Flush(); err != nil {
return nil, gerror.Wrap(err, "WriteToSheet.Flush ")
}
// 3 设计文件编辑人数sheet
entUserEditTrendResp, err := statistics.NewStatCenter().EntUserEditTrend(userEdit)
if err != nil {
return nil, err
}
dateList = make([]string, 0, len(entUserEditTrendResp.EntUserEditUserTrend))
for k := range entUserEditTrendResp.EntUserEditUserTrend {
dateList = append(dateList, k)
}
sort.Sort(utils.StringSlice(dateList))
sheetName = req.SheetNames[2]
f.NewSheet(sheetName)
streamWriter, err = f.NewStreamWriter(sheetName)
if err != nil {
return nil, gerror.Wrap(err, "WriteToSheet.NewStreamWriter")
}
columnNamesInterface2 := make([]interface{}, 0)
for _, c := range req.SheetColumnMap[sheetName] {
columnNamesInterface2 = append(columnNamesInterface2, c)
}
streamWriter.SetRow("A1", columnNamesInterface2)
for i, date := range dateList {
row := []interface{}{date, entUserEditTrendResp.EntUserEditUserTrend[date]}
axis, _ := excelize.CoordinatesToCellName(1, i+2)
streamWriter.SetRow(axis, row)
}
if err = streamWriter.Flush(); err != nil {
return nil, gerror.Wrap(err, "WriteToSheet.Flush ")
}
// 4 设计文件人均编辑时长sheet
dateList = make([]string, 0, len(entUserEditTrendResp.EntUserEditTrend))
for k := range entUserEditTrendResp.EntUserEditTrend {
dateList = append(dateList, k)
}
sort.Sort(utils.StringSlice(dateList))
sheetName = req.SheetNames[3]
f.NewSheet(sheetName)
streamWriter, err = f.NewStreamWriter(sheetName)
if err != nil {
return nil, gerror.Wrap(err, "WriteToSheet.NewStreamWriter")
}
columnNamesInterface3 := make([]interface{}, 0)
for _, c := range req.SheetColumnMap[sheetName] {
columnNamesInterface3 = append(columnNamesInterface3, c)
}
streamWriter.SetRow("A1", columnNamesInterface3)
for i, date := range dateList {
row := []interface{}{date, entUserEditTrendResp.EntUserEditTrend[date]}
axis, _ := excelize.CoordinatesToCellName(1, i+2)
streamWriter.SetRow(axis, row)
}
if err = streamWriter.Flush(); err != nil {
return nil, gerror.Wrap(err, "WriteToSheet.Flush ")
}
// 删除默认工作表
f.DeleteSheet("Sheet1")
// 设置工作簿的默认工作表
f.SetActiveSheet(0)
return f, nil
}
func WriteToFile(fileName string, sheetNames []string, m map[string]*define.SheetInfo) (*excelize.File, error) {
f := excelize.NewFile()
defer func() {
if err := f.Close(); err != nil {
fmt.Println(err)
}
}()
for _, sheetName := range sheetNames {
if v, ok := m[sheetName]; ok {
err := WriteToSheet(f, sheetName, v.ColumnNames, v.SheetRows)
if err != nil {
return nil, err
}
}
}
// 删除默认工作表
f.DeleteSheet("Sheet1")
// 设置工作簿的默认工作表
f.SetActiveSheet(0)
var b bytes.Buffer
writer := bufio.NewWriter(&b)
err := f.Write(writer)
if err != nil {
return nil, err
}
var EXLHeader = map[string]interface{}{"Content-FileType": storage.TypeFile, "content-type": storage.TypeFile,
s.HeaderContentDisposition: fmt.Sprintf("attachment;filename=%s", fileName)}
objectId, err := storage.Public().NewObject(context.TODO(), "xlsx", int64(b.Len()), EXLHeader, &b)
fmt.Println(storage.Public().SignUrl(context.TODO(), objectId, 60*60*24*7))
if err != nil {
fmt.Println("storage err!!!")
return nil, gerror.Wrap(err, "storage err!!!")
}
return f, nil
}
func WriteToSheet(f *excelize.File, sheetName string, columnNames []string, rows [][]interface{}) error {
f.NewSheet(sheetName)
streamWriter, err := f.NewStreamWriter(sheetName)
if err != nil {
return gerror.Wrap(err, "WriteToSheet.NewStreamWriter")
}
columnNamesInterface := make([]interface{}, 0)
for _, c := range columnNames {
columnNamesInterface = append(columnNamesInterface, c)
}
streamWriter.SetRow("A1", columnNamesInterface)
for i, row := range rows {
axis, _ := excelize.CoordinatesToCellName(1, i+2)
streamWriter.SetRow(axis, row)
}
if err = streamWriter.Flush(); err != nil {
return gerror.Wrap(err, "WriteToSheet.Flush ")
}
return nil
}
![NL2SQL进阶系列(3)：Data-Copilot、Chat2DB、Vanna Text2SQL优化框架开源应用实践详解[Text2SQL]](https://img-blog.csdnimg.cn/img_convert/fe9ea57cc08b74aa976f64d4c6b0a68f.jpeg)
