背景:本人PHPer,业务中用到了PHP导出Excel,但是当数据量过大时,导出缓慢。最近在学习go,所以想试验一下go中相同数据量的excel导出耗时与PHP相差多少。根据现在的业务情况,写出代码如下:
```php
<?php
ini_set('memory_limit','1024M');
ini_set('post_max_size','1024M');
$ch = curl_init();
curl_setopt($ch, CURLOPT_URL, "http://127.0.0.1:12345/excel_export");
curl_setopt($ch, CURLOPT_RETURNTRANSFER, 1);
curl_setopt($ch, CURLOPT_SSL_VERIFYPEER, FALSE);
// POST数据
curl_setopt($ch, CURLOPT_POST, 1);
$str = "ABCDEFGHIJKLMNOPQRSTUVWXYZ";
for ($i=1;$i<=1000000;$i++){
$json[] = array(
"age" => $i,
"name" => substr($str, rand(0,26), rand(1,26)),
"sex" => $i + 1*rand(1,10)
);
}
$params = array(
"data" => json_encode($json),
"file_path" => "http://localhost/test.xlsx", //模板文件地址
"export_filename" => "测试导出Excel.xlsx" //导出的Excel文件名
);
curl_setopt($ch, CURLOPT_POSTFIELDS, $params);
$output = curl_exec($ch);
var_dump($output);
var_dump(curl_error($ch));
curl_close($ch);
?>
```
```go
package main
import (
"bufio"
"encoding/json"
"fmt"
"github.com/360EntSecGroup-Skylar/excelize"
"io"
"log"
"net/http"
"os"
"path"
"time"
)
type stringSlice []string
func main() {
//1,监听请求
http.HandleFunc("/excel_export", excelExport)
err := http.ListenAndServe("127.0.0.1:12345", nil)
if err != nil {
fmt.Println(err)
return
}
}
func excelExport(w http.ResponseWriter, req *http.Request) {
start := time.Now()
//方法1
//req.ParseForm()
//
//postData,ok := req.Form["data"]
//fmt.Println(ok)
//
//filepath,ok := req.Form["file_path"]
//fmt.Println(ok)
//
//exportFilename,ok := req.Form["export_filename"]
//fmt.Println(ok)
//方法2
//buf := new(bytes.Buffer)
//io.Copy(buf, req.Body)
//body := buf.String()
//
//fmt.Println(body)
//方法3
postData := req.FormValue("data")
filepath := req.FormValue("file_path")
exportFilename := req.FormValue("export_filename")
if postData == "" || filepath == "" {
io.WriteString(w, "error")
return
}
fileName := path.Base(filepath)
//1,创建文件,如果文件创建失败,则返回
createTemplateFile(filepath)
//2,根据传来的模板文件,获取到文件中的表头
title := getTitle(fileName)
if len(title) < 1 {
io.WriteString(w, "error1")
return
}
//3,获取并解析待写入的数据
exportData := getDatas(req, postData)
if len(exportData) < 1 {
io.WriteString(w, "error2")
return
}
//开始创建文件并写入数据
flag := createFile(title, exportData, exportFilename)
if !flag {
io.WriteString(w, "error3")
return
}
end := time.Now()
log.Println(end.Sub(start))
io.WriteString(w, "success")
}
//通过传来的文件地址,读取并在本地创建一个相同的模板文件
func createTemplateFile(filepath string) bool {
res,err := http.Get(filepath)
if err != nil {
log.Println(err)
return false
}
filename := path.Base(filepath)
reader := bufio.NewReader(res.Body)
file,err := os.Create(filename)
if err != nil {
log.Println(err)
return false
}
defer file.Close()
writer := bufio.NewWriter(file)
_,err = io.Copy(writer, reader)
if err != nil {
log.Println(err)
return false
}
return true
}
func createFile(title []stringSlice, exportData []map[string]interface{}, exportFilename string) bool {
file := excelize.NewFile()
streamWriter, err := file.NewStreamWriter("Sheet1")
if err != nil {
log.Println(err)
return false;
}
//fmt.Println(title)
var titleString []string
//写 title
for rowID := 0; rowID < len(title); rowID++ {
row := make([]interface{}, len(title[rowID]))
for colID := 0; colID < len(title[rowID]); colID++ {
row[colID] = title[rowID][colID]
if rowID == 1 {
titleString = append(titleString, title[rowID][colID])
}
}
cell, _ := excelize.CoordinatesToCellName(1, rowID+1)
if err := streamWriter.SetRow(cell, row); err != nil {
return false
}
}
//fmt.Println(titleString)
titleLen := len(title) + 1
//写数据
for _,v := range exportData {
row := make([]interface{}, 0)
for i := 0; i < len(titleString); i++ {
vv, ok := v[titleString[i]]
if !ok {
return false
}
row = append(row, vv)
}
//fmt.Println(row)
cell, _ := excelize.CoordinatesToCellName(1, titleLen)
if err := streamWriter.SetRow(cell, row); err != nil {
return false
}
titleLen++
}
if err := streamWriter.Flush(); err != nil {
log.Println(err)
return false
}
if err := file.SaveAs(exportFilename); err != nil {
log.Println(err)
return false
}
return true
}
//获取表格模板的表头
func getTitle(fileName string) []stringSlice {
title := make([]stringSlice, 0)
//2, excel 打开文件
xls, err := excelize.OpenFile(fileName)
if err != nil {
return title
}
rows, err := xls.GetRows("Sheet1")
for _, row := range rows {
slice := make([]string, 0)
for _, colCell := range row {
slice = append(slice, colCell)
}
title = append(title, slice)
}
return title
}
//解析待写入的数据
func getDatas(req *http.Request, postDatas string) []map[string]interface{} {
var str []map[string]interface{}
err := json.Unmarshal([]byte(postDatas), &str)
if err != nil {
return str
}
return str
}
```
在go中的 excelExport 方法里,方法1接收不到任何数据,方法2可以接收到全部数据,但是我不清除如何解析;方法3在PHP中生成数据再95万以内传过去时,可以正常接收到数据,但是超出95万就收不到了。请问各位大佬我这里是哪儿出了问题呢?如果使用方法2的话我该如何解析各部分数据呢?
有疑问加站长微信联系(非本文作者)