基于Beego架構的導入導出Excel

1.概述

導入導出功能是最常見的開發功能之一，這裡用beego架構實作，這裡采用 github.com/360EntSecGroup-Skylar/excelize 方式實作。

2.功能實作

2.1 安裝

安裝 github.com/360EntSecGroup-Skylar/excelize

go get -u github.com/360EntSecGroup-Skylar/excelize

2.2 實作代碼

package controllers import ( "bytes" "encoding/base64" "encoding/json" "errors" "github.com/360EntSecGroup-Skylar/excelize" "github.com/astaxie/beego/logs" "net/http" "net/url" "path" "screen/components" "screen/models" "strconv" "strings" "time" "github.com/astaxie/beego" ) // TestController operations for Topo type TestController struct { beego.Controller } // URLMapping ... func (c *TestController) URLMapping() { c.Mapping("ExportExcel", c.ExportExcel) c.Mapping("ImportExcel", c.ImportExcel) } // ExportExcel ... // @Title Get One // @Description get Topo by id // @Param id path string true "The key for staticblock" // @Success 200 {object} // @Failure 403 :id is empty // @router /export/ [get] func (c *TestController) ExportExcel() { excelize.NewFile() file := excelize.NewFile() //設定表格頭 title := getTitle() file.SetSheetRow("Sheet1", "A1", title) list, _ := models.GetTopoByIds(ids) //寫入資料 for i := 0; i < 10; i++ { file.SetSheetRow("Sheet1", "A"+lint, &[]interface{}{ i, "name"+i, }) } //構造檔案名稱 fileName := "導出檔案" + time.Now().Format("20060102150405") + ".xlsx"

// 解決檔案名中文亂碼問題 fileName = path.Base(fileName) fileName = url.QueryEscape(fileName) c.Ctx.Output.Header("Content-type", "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet") c.Ctx.Output.Header("Content-Disposition", "attachment;filename="+fileName) c.Ctx.Output.Header("Pragma", "No-cache") c.Ctx.Output.Header("Cache-Control", "No-cache") c.Ctx.Output.Header("Expires", "0") var buffer bytes.Buffer if err := file.Write(&buffer); err != nil { logs.Error(err) } r := bytes.NewReader(buffer.Bytes()) http.ServeContent(c.Ctx.ResponseWriter, c.Ctx.Request, fileName, time.Now(), r) } func getTitle() *[]interface{} { var titles = []interface{}{ "序号", "名稱", } return &titles } // ImportExcel ... // @Title ImportExcel // @Description import Topo // @Param body body true // @Success 201 {int} // @Failure 403 body is empty // @router /import/ [post] func (c *TestController ) ImportExcel() { file, h, _ := c.GetFile("file") //擷取上傳的檔案 ext := path.Ext(h.Filename) //驗證字尾名是否符合要求 AllowExtMap := map[string]bool{ ".xlsx": true, } if _, ok := AllowExtMap[ext]; !ok { c.Data["json"] = "字尾名不符合上傳要求" c.ServeJSON() return } xlsx, err := excelize.OpenReader(file) if err != nil { logs.Error(err) c.Data["json"] = "讀取檔案異常" c.ServeJSON() return } rows := xlsx.GetRows("Sheet1") if rows == nil || len(rows) == 0 { c.Data["json"] = "Sheet1不存在或無資料" c.ServeJSON() return } // 驗證表頭是否一直 titleRow := rows[0] title := getTitle() for i, v := range *title { value := titleRow[i] if v != value { c.Data["json"] = "标題不符合要求" c.ServeJSON() return } } count := 0 for i, row := range rows { if i > 0 {

fmt.Println(row[0], row[1]) count++ } } if count == 0 { c.Data["json"] = "上傳失敗" } else { c.Data["json"] = "上傳成功" + strconv.Itoa(count) + "條" } c.ServeJSON() }

3.解決檔案名中文亂碼

如下代碼，解決問題

// 解決檔案名中文亂碼問題 fileName = path.Base(fileName) fileName = url.QueryEscape(fileName)