导出Excel，从PHP下xlswriter到golang的进化是2分缩减到5秒
先看图
数据条数：9000+
第1,2行，golang+excelize方式导出，耗时：5s
第3行，PHP+xlswriter方式导出，耗时：2min
一、介绍
xlswriter是一个高效处理excel文件的PHP扩展，底层以C语言实现；处理速度是PHPExcel几十倍甚至几百倍的效率。
官方链接：https://gitee.com/viest/php-ext-xlswriter
缺点：更深入的功能（例如读取excel图片）不健全；导出excel样式不够丰富
导出excel文件，xlswriter绝对是效率最高的处理方式，配合php后台任务导出百万千万级别数据也没有问题
通用导出类：
class ExportOperateAnaly { public $export_limit = 13000; /** * 统一导出 * @Author zhibin3 * @DateTime 2022-12-24 * @param array $pageData [description] * @return [type] [description] */ public function layout(array $params, $is_export=true) { ini_set('memory_limit', '512M'); $list = []; //todo $header_list = ['商品ID', '商品标题', '商品图片']; $width_list = ['商品图片'=>55,'商品标题'=>55]; return $this->export($header_list, $list, '商品运营分析', $is_export, $width_list); } /** * 通用导出方法 * @Author zhibin3 * @DateTime 2023-02-14 * @param array $header_list [description] * @param array $list [description] * @return [type] [description] */ public function export(array $header_list, array $datas, $filename='选款导出列表', $is_export=true, array $width_list=[]) { $config = [ 'path' => ROOT_PATH . 'public' . DS . 'dl' // xlsx文件保存路径 ]; $Excel = new \Vtiful\Kernel\Excel($config); $filename .= datetime(time(), 'Y-m-d_H_i_s').'.xlsx'; $fileObject = $Excel->fileName($filename, 'sheet1')->header($header_list); $fileHandle = $fileObject->getHandle(); $format = new \Vtiful\Kernel\Format($fileHandle); $boldStyle = $format->bold()->toResource(); $alignStyle = $format->align(\Vtiful\Kernel\Format::FORMAT_ALIGN_CENTER, \Vtiful\Kernel\Format::FORMAT_ALIGN_VERTICAL_CENTER)->toResource(); // $backgroundStyle = $format->background(0xD9D9D9)->toResource(); foreach ($datas as $index => $val) { $column = 0; foreach ($val as $key => $value) { $row = $index+1; /*if ($key=='img') { $return = ImageImport::export_image_save($value); $img_path = $return['image'] ? $return['path'] : $value; // pre($value, $return['image'], $row, $column, $img_path); $fileObject->insertImage($row, $column, $img_path, 0.1, 0.1); }*/ if ($key=='lowest_price') { $fileObject->insertFormula($row, $column, $value); } else if (is_string($value)) { $fileObject->insertText($row, $column, $value); } else { $fileObject->insertText($row, $column, $value); } $column++; } } // pre($datas);die; foreach ($header_list as $key => $value) { // $letter = $header_map[$key]; $letter = \Vtiful\Kernel\Excel::stringFromColumnIndex($key); $width_value = $width_list[$value] ?? 20; $fileObject->setRow($letter.($key+1), 35, $boldStyle); $fileObject->setColumn($letter.':'.$letter, $width_value, $alignStyle); } $format2 = new \Vtiful\Kernel\Format($fileHandle); $alignStyle = $format2->align(\Vtiful\Kernel\Format::FORMAT_ALIGN_CENTER, \Vtiful\Kernel\Format::FORMAT_ALIGN_VERTICAL_CENTER)->toResource(); $wrapStyle = $format2->wrap()->toResource(); foreach ($datas as $index => $value) { $row = $index+2; $fileObject->setRow('A'.$row, 30, $alignStyle); } // pre($fileObject);die; $filePath = $fileObject->output(); if ($is_export) { header("Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"); header('Content-Disposition: attachment;filename="' . $filename . '"'); header('Content-Length: ' . filesize($filePath)); header('Content-Transfer-Encoding: binary'); header('Cache-Control: must-revalidate'); header('Cache-Control: max-age=0'); header('Pragma: public'); if (ob_get_contents()) ob_clean(); flush(); if (@copy($filePath, 'php://output') === false) throw new \Exception($filePath. '地址出问题了'); @unlink($filePath); return ['code'=>1, 'msg'=>'导出成功', 'file_path'=>'']; } else { return ['code'=>1, 'msg'=>'导出成功', 'file_path'=>'dl/'.$filename]; } } }
二、golang
虽然xlswriter在PHP中是比较高效的excel文件处理扩展，但是跟golang相比还是比较大的距离；这就是解释语言与编译语言差距。
golang中使用excelize处理excel文件
具体：github.com/xuri/excelize/v2
数据：gorm.io/driver/mysql + gorm.io/gorm
通用导出处理：
1 package services 2 3 import ( 4 "fmt" 5 "gin_async/models" 6 "gin_async/utils" 7 "gin_async/config" 8 "github.com/xuri/excelize/v2" 9 "reflect" 10 "strings" 11 "time" 12 "strconv" 13 ) 14 15 var OperateProductSaleService = new(OperateProductSale) 16 17 type OperateProductSale struct { 18 } 19 20 func (s *OperateProductSale) Startup(data utils.Dict, logId int, isExport bool) { 21 //todo 22 //fmt.Println(list) return 23 var headerList = []string{"商品ID", "商品标题", "商品图片", "商品链接", "店铺名称", "款号", "运营", "一级分类", "二级分类", "销量", "销售额", "发货金额", "退款金额", "退货金额", "净销售额", "14天退款率", "推广费用", "履约费用", "公摊费用", "商品成本", "退货商品收入", "佣金及服务费", "分账及扣款", "其他服务费", "预估毛利润", "预估净利润", "订单量", "发货前退货", "库存", "曝光", "点击", "点击率", "点击成交率", "收藏"} 24 var widthMap = map[string]float64{ 25 "商品ID": 16, 26 "商品标题": 30, 27 "商品图片": 30, 28 "商品链接": 30, 29 } 30 31 filename, err := s.Export(headerList, rows, widthMap) 32 var code int 33 var msg string 34 if err != nil { 35 code = 0 36 msg = "导出文件错误" 37 } else { 38 code = 1 39 msg = "导出成功" 40 } 41 models.AsyncLogModel.UpdateLog(logId, map[string]interface{}{"code": code, "msg": msg, "file_path": filename, "remark":"from gin export"}, filename) 42 fmt.Println("异步任务[export_operate_analy]结束，时间：", time.Now().Format("2006-01-02 15:04:05")) 43 } 44 45 var colIndex int //定义全局列属性 46 var rowIndex int //定义全局行属性 47 var f *excelize.File //定义全局文件属性 48 var Sheet1 string 49 50 func (s *OperateProductSale) Export(headerList []string, list []models.GetRelatProductStruct, widthMap map[string]float64) (string, error) { 51 savePath := config.GlobalConfig.Common.SavePath //读取配置文件 52 if savePath=="" { 53 savePath = "./" 54 } 55 56 //fmt.Println(widthMap) 57 f = excelize.NewFile() 58 defer func() { 59 if err := f.Close(); err != nil { 60 fmt.Println(err) 61 } 62 }() 63 64 Sheet1 = "Sheet1" 65 // 创建一个工作表 66 index, err := f.NewSheet(Sheet1) 67 if err != nil { 68 fmt.Println(err) 69 return "", err 70 } 71 72 // 创建一个样式，设置字体大小为14 73 style, err := f.NewStyle(&excelize.Style{ 74 Font: &excelize.Font{ 75 Size: 13, 76 Bold: true, 77 Color: "#000000", 78 }, 79 Alignment: &excelize.Alignment{ 80 Horizontal: "center", 81 Vertical: "center", 82 }, 83 }) 84 if err != nil { 85 fmt.Println(err) 86 return "", err 87 } 88 89 // 设置单元格的值 90 //var lastCol string 91 //var headerListLen = len(headerList) 92 for index, header := range headerList { 93 cellAddress, _ := excelize.CoordinatesToCellName(index+1, 1) 94 /*if index == headerListLen-1 { 95 lastCol = cellAddress 96 }*/ 97 98 f.SetCellValue(Sheet1, cellAddress, header) 99 f.SetCellStyle("Sheet1", cellAddress, cellAddress, style) 100 101 widthValue, ok := widthMap[header] 102 if !ok { 103 widthValue = 14 104 } 105 err = f.SetColWidth(Sheet1, strings.Trim(cellAddress, "1"), strings.Trim(cellAddress, "1"), widthValue) //设置宽度 106 } 107 //err = f.SetColWidth(Sheet1, "A", strings.Trim(lastCol, "1"), 15) //设置宽度 108 err = f.SetRowHeight(Sheet1, 1, 25) //设置高度 109 110 //插入数据行 111 for rIndex, value := range list { 112 cost := fmt.Sprintf("%.2f", value.Cost) 113 refundCost := fmt.Sprintf("%.2f", value.RefundCost) 114 //方式一 115 /*values := getStructFieldValues(row) 116 for colIndex, value := range values { 117 if colIndex > headerListLen-1 { 118 continue 119 } 120 cellAddress, _ := excelize.CoordinatesToCellName(colIndex+1, rowIndex+2) 121 f.SetCellValue("Sheet1", cellAddress, value) 122 }*/ 123 124 //方式二 125 colIndex = 1 126 rowIndex = rIndex + 2 127 SetValue(value.ShopStyleCode) //商品ID 128 SetValue(value.ItemName) //商品标题 129 SetValue(value.ItemMainImage) //商品图片 130 SetValue(value.ShopLink) //商品链接 131 SetValue(value.ShopName) //店铺名称 132 SetValue(value.LocalStyleCode) 133 SetValue(value.User) 134 SetValue(value.FClassifyName) 135 SetValue(value.SClassifyName) 136 SetValue(value.SaleQty) 137 SetValue(value.SaleAmount) 138 SetValue(value.SendAmount) 139 SetValue(value.RefundAmount) 140 SetValue(value.ReturnAmount) 141 SetValue(value.NetSaleAmount) 142 SetValue(value.RefundRate) 143 SetValue(value.PromoteCost) 144 SetValue(value.PerformCost) 145 SetValue(value.PublicCost) 146 SetValue(cost) 147 SetValue(refundCost) 148 SetValue(value.PayCommissionService) 149 SetValue(value.PayShareDebit) 150 SetValue(value.PayOtherService) 151 SetValue(value.PredictGrossProfit) 152 SetValue(value.PredictProfit) 153 SetValue(value.OrderQty) 154 SetValue(value.UnsendRefundQty) 155 SetValue(value.Stock) 156 SetValue(value.Exposure) 157 SetValue(value.Hits) 158 SetValue(value.HitsRate) 159 SetValue(value.HitsDealRate) 160 SetValue(value.Collect) 161 } 162 163 // 设置工作簿的默认工作表 164 f.SetActiveSheet(index) 165 166 datetime := time.Now().Format("2006-01-02_15_04_05") 167 filename := fmt.Sprintf("dl/商品运营分析_%s.xlsx", datetime) 168 savefile := fmt.Sprintf("%s%s", savePath, filename) 169 fmt.Println("保存地址:"+savefile) 170 // 根据指定路径保存文件 171 if err := f.SaveAs(savefile); err != nil { 172 fmt.Println(err) 173 } 174 175 fmt.Println("Excel file exported successfully.") 176 return filename, nil 177 } 178 179 func SetValue(value interface{}) { 180 cellAddress, _ := excelize.CoordinatesToCellName(colIndex, rowIndex) 181 f.SetCellValue(Sheet1, cellAddress, value) 182 183 colIndex++ 184 }
