go语言使用tealeg/xlsx导出excel

向右看齐 2023-10-10 20:12 51阅读 0赞

### 前端传参： ###

{
      "fileName": "用户列表.xlsx",
      "infoList": [
        {
          "id": 25,
          "from": "12345",
          "to": "123456",
          "tripTime": 1684319731,
          "tripType": 1,
          "passengerAmount": 0,
          "freeSeats": 3,
          "reason": "12",
          "username": "李望",
          "uid": "13902804",
          "deptId": "202754",
          "deptName": "开发部",
          "remark": "",
          "applications": [],
          "logs": null,
          "userTel": "17634371725",
          "phone": "",
          "vehicleNo": "",
          "vehicleRemark": "",
          "tripStatus": "0",
          "imgName": "1684319760",
          "imgData": null,
          "address": "123"
        },
        {
          "id": 19,
          "from": "1",
          "to": "2",
          "tripTime": 1684312025,
          "tripType": 1,
          "passengerAmount": 0,
          "freeSeats": 3,
          "reason": "支援",
          "uid": "13902961",
          "username": "陈先生",
          "deptId": "202754",
          "deptName": "开发部",
          "remark": "",
          "applications": [],
          "logs": null,
          "userTel": "13838613158",
          "phone": "",
          "vehicleNo": "",
          "vehicleRemark": "",
          "tripStatus": "0",
          "imgName": "1684311747",
          "imgData": null,
          "address": ""
        },
        {
          "id": 12,
          "from": "新厂",
          "to": "老厂",
          "tripTime": 1684312009,
          "tripType": 1,
          "passengerAmount": 0,
          "freeSeats": 3,
          "reason": "支援",
          "uid": "13902961",
          "username": "陈先生",
          "deptId": "202754",
          "deptName": "开发部",
          "remark": "1",
          "applications": [],
          "logs": null,
          "userTel": "13838613158",
          "phone": "12",
          "vehicleNo": "12",
          "vehicleRemark": "23",
          "tripStatus": "0",
          "imgName": "1684300884",
          "imgData": null,
          "address": "55"
        }
      ]
    }

## model层 ##

package model
    
    // 导出excel
    type ExportData struct {
        FileName string  `json:"fileName"` //文件名
        InfoList []*Trip `json:"infoList"` //导出数据
    }
    
    type ExportTrip struct {
        Uid             string `json:"uid"`             //工号
        DeptId          string `json:"deptId"`          //部门id
        DeptName        string `json:"deptName"`        //部门名称
        Username        string `json:"username"`        //发布者姓名
        TripTime        string `json:"tripTime"`        //出发时间
        FromAddress     string `json:"from"`            //出发地
        ToAddress       string `json:"to"`              //目的地
        Address         string `json:"address"`         //详细地址
        TripType        string `json:"tripType"`        //行程类型0 私车公用 1 公司派车
        PassengerAmount int32  `json:"passengerAmount"` //乘车人数
        FreeSeats       int32  `json:"freeSeats"`       //空余座位
        Reason          string `json:"reason"`          //出行原因
        Remark          string `json:"remark"`          //备注
        UserTel         string `json:"userTel"`         //发布者电话
        Phone           string `json:"phone"`           //司机电话
        VehicleNo       string `json:"vehicleNo"`       //车牌号 高铁车次
        VehicleRemark   string `json:"vehicleRemark"`   //车辆备注
        TripStatus      string `json:"tripStatus"`      //行程状态 0 未确认 1 已确认 2 已完成
    }
    
    func (t ExportTrip) FromExportProto(req *Trip) *ExportTrip {
        var tripType string
        if req.TripType == 0 {
            tripType = "私车公用"
        } else {
            tripType = "公司派车"
        }
        var tripStatus string
        if req.TripStatus == 0 {
            tripStatus = "未确认"
        }
        if req.TripStatus == 1 {
            tripStatus = "已确认"
        }
        if req.TripStatus == 2 {
            tripStatus = "已完成"
        }
        return &ExportTrip{
            FromAddress:     req.FromAddress,
            ToAddress:       req.ToAddress,
            TripTime:        req.TripTime.Time().Format("2006/01/02 15:04:05"),
            TripType:        tripType,
            PassengerAmount: req.PassengerAmount,
            FreeSeats:       req.FreeSeats,
            Reason:          req.Reason,
            Uid:             req.Uid,
            Username:        req.Username,
            DeptId:          req.DeptId,
            DeptName:        req.DeptName,
            Remark:          req.Remark,
            UserTel:         req.UserTel,
            Phone:           req.Phone,
            VehicleNo:       req.VehicleNo,
            VehicleRemark:   req.VehicleRemark,
            TripStatus:      tripStatus,
            Address:         req.Address,
        }
    }

## controller层 ##

func exportExcelHandler(c *gin.Context) {
        var exportData *model.ExportData
    
        if err := c.BindJSON(&exportData); err != nil {
            c.JSON(http.StatusBadRequest, gin.H{"error": err.Error()})
            return
        }
    
        var res []interface{}
        for _, trip := range exportData.InfoList {
            exportTrip := model.ExportTrip{}.FromExportProto(trip)
            res = append(res, exportTrip)
        }
        content := ToExcel([]string{"工号", "部门ID", "部门名称", "发布者姓名", "出发时间", "出发地", "目的地", "详细地址", "行程类型", "乘车人数", "空余座位", "出行原因", "备注", "发布者电话", "司机电话", "车牌号", "车辆备注", "行程状态"}, res)
        ResponseXls(c, content, exportData.FileName)
    }

## utils ##

// 生成io.ReadSeeker  参数 titleList 为Excel表头，dataList 为数据
    func ToExcel(titleList []string, dataList []interface{}) (content io.ReadSeeker) {
        // 生成一个新的文件
        file := xlsx.NewFile()
        // 添加sheet页
        sheet, _ := file.AddSheet("Sheet1")
        // 插入表头
        titleRow := sheet.AddRow()
        for _, v := range titleList {
            cell := titleRow.AddCell()
            cell.Value = v
        }
        // 插入内容
        for _, v := range dataList {
            row := sheet.AddRow()
            row.WriteStruct(v, -1)
        }
    
        var buffer bytes.Buffer
        _ = file.Write(&buffer)
        content = bytes.NewReader(buffer.Bytes())
        return
    }
    
    // 向前端返回Excel文件
    // 参数 content 为上面生成的io.ReadSeeker， fileTag 为返回前端的文件名
    func ResponseXls(c *gin.Context, content io.ReadSeeker, fileTag string) {
        //time.Now().Format("20060102")
        fileName := fmt.Sprintf("%s%s%s.xlsx", xtime.Now().Time().Format("20060102"), `-`, fileTag)
        c.Writer.Header().Add("Content-Disposition", fmt.Sprintf(`attachment; filename="%s"`, fileName))
        c.Writer.Header().Add("Content-Type", "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet")
        http.ServeContent(c.Writer, c.Request, fileName, xtime.Now().Time(), content)
    }