基于Golang實(shí)現(xiàn)Excel表格的導(dǎo)入導(dǎo)出功能
最近項(xiàng)目開發(fā)中有涉及到Excel的導(dǎo)入與導(dǎo)出功能,特別是導(dǎo)出表格時(shí)需要特定的格式(單元格合并等),廢話不多說,直接上代碼了。
首先用到一個(gè)第三方庫,實(shí)測還是很強(qiáng)大很好用的,就是這個(gè)https://github.com/qax-os/excelize
引用庫
go get github.com/xuri/excelize/v2
導(dǎo)入表格
package main import ( "encoding/json" "github.com/xuri/excelize/v2" "net/http" "strings" ) func fileImport(w http.ResponseWriter, r *http.Request) { // 獲取請求中的文件名 formFile, _, err := r.FormFile("filename") if err != nil { w.Write([]byte("獲取文件失敗, " + err.Error())) return } // 關(guān)閉 defer formFile.Close() // reader, err := excelize.OpenReader(formFile) if err != nil { w.Write([]byte("讀取文件失敗, " + err.Error())) return } // 關(guān)閉 defer reader.Close() rows, err := reader.GetRows("Sheet1") if err != nil { w.Write([]byte("獲取工作表失敗, " + err.Error())) return } ret := make([]string, 0, 8) for i, row := range rows { // 每一行數(shù)據(jù)的列, 都是從0開始的, 一般0行都是表頭 if i == 0 { continue } value1 := row[0] // 第一列 value2 := row[1] // 第二列 // 去除空格 value1 = strings.Trim(strings.TrimSpace(value1), "\n") value2 = strings.Trim(strings.TrimSpace(value2), "\n") // ret = append(ret, value1+","+value2) } bytes, _ := json.Marshal(ret) w.Write(bytes) return } func main() { // HTTP服務(wù) http.HandleFunc("/fileImport", fileImport) err := http.ListenAndServe(":8192", nil) if err != nil { panic(err) } }
表格格式
執(zhí)行后效果
導(dǎo)出表格
package main import ( "encoding/json" "fmt" "github.com/xuri/excelize/v2" "net/http" "strings" ) func fileExport(w http.ResponseWriter, r *http.Request) { file := excelize.NewFile() defer file.Close() // 設(shè)置頁 sheetName := "Sheet1" // 創(chuàng)建 sheet, err := file.NewSheet(sheetName) if err != nil { w.Write([]byte("創(chuàng)建失敗, " + err.Error())) return } // 設(shè)置單元格格式 style := &excelize.Style{ Border: nil, Fill: excelize.Fill{}, Font: nil, Alignment: &excelize.Alignment{ Horizontal: "center", Indent: 0, JustifyLastLine: false, ReadingOrder: 0, RelativeIndent: 0, ShrinkToFit: false, TextRotation: 0, Vertical: "center", WrapText: false, }, Protection: nil, NumFmt: 0, DecimalPlaces: nil, CustomNumFmt: nil, NegRed: false, } styleID, _ := file.NewStyle(style) // 設(shè)置表頭 _ = file.SetCellValue(sheetName, "A1", "款") _ = file.SetCellStyle(sheetName, "A1", "A1", styleID) _ = file.SetCellValue(sheetName, "B1", "尺碼") _ = file.SetCellStyle(sheetName, "B1", "B1", styleID) // 設(shè)置值 for i := 0; i < 5; i++ { lineStr := fmt.Sprintf("%d", i+2) // _ = file.SetCellValue(sheetName, "A"+lineStr, "基礎(chǔ)款") _ = file.SetCellStyle(sheetName, "A"+lineStr, "A"+lineStr, styleID) // _ = file.SetCellValue(sheetName, "B"+lineStr, "1:2:3:4:5:6") _ = file.SetCellStyle(sheetName, "B"+lineStr, "B"+lineStr, styleID) } // file.SetActiveSheet(sheet) // buffer, err := file.WriteToBuffer() if err != nil { w.Write([]byte("導(dǎo)出失敗, " + err.Error())) return } w.Header().Set("Content-Disposition", fmt.Sprintf("attachment; filename=%s", "導(dǎo)出文件.xlsx")) w.Header().Set("Content-Type", "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet") w.Write(buffer.Bytes()) } func main() { // HTTP服務(wù) http.HandleFunc("/fileImport", fileImport) http.HandleFunc("/fileExport", fileExport) err := http.ListenAndServe(":8192", nil) if err != nil { panic(err) } }
頁面請求 http://127.0.0.1:8192/fileExport 后會(huì)直接生成xlsx文件并下載
合并單元格
package main import ( "encoding/json" "fmt" "github.com/xuri/excelize/v2" "net/http" "strings" ) func fileExport(w http.ResponseWriter, r *http.Request) { file := excelize.NewFile() defer file.Close() // 設(shè)置頁 sheetName := "Sheet1" // 創(chuàng)建 sheet, err := file.NewSheet(sheetName) if err != nil { w.Write([]byte("創(chuàng)建失敗, " + err.Error())) return } // 設(shè)置單元格格式 style := &excelize.Style{ Border: nil, // 邊框樣式 Fill: excelize.Fill{}, Font: nil, // 字體樣式 Alignment: &excelize.Alignment{ // 位置樣式 Horizontal: "center", Indent: 0, JustifyLastLine: false, ReadingOrder: 0, RelativeIndent: 0, ShrinkToFit: false, TextRotation: 0, Vertical: "center", WrapText: false, }, Protection: nil, NumFmt: 0, DecimalPlaces: nil, CustomNumFmt: nil, NegRed: false, } styleID, _ := file.NewStyle(style) // 設(shè)置表頭 _ = file.MergeCell(sheetName, "A1", "A2") // 合并單元格 _ = file.SetCellValue(sheetName, "A1", "款") _ = file.SetCellStyle(sheetName, "A1", "A2", styleID) _ = file.MergeCell(sheetName, "B1", "G1") // 合并單元格 _ = file.SetCellValue(sheetName, "B1", "尺碼") _ = file.SetCellStyle(sheetName, "B1", "G1", styleID) _ = file.SetCellValue(sheetName, "B2", "XS") _ = file.SetCellStyle(sheetName, "B2", "B2", styleID) _ = file.SetCellValue(sheetName, "C2", "S") _ = file.SetCellStyle(sheetName, "C2", "C2", styleID) _ = file.SetCellValue(sheetName, "D2", "M") _ = file.SetCellStyle(sheetName, "D2", "D2", styleID) _ = file.SetCellValue(sheetName, "E2", "L") _ = file.SetCellStyle(sheetName, "E2", "E2", styleID) _ = file.SetCellValue(sheetName, "F2", "XL") _ = file.SetCellStyle(sheetName, "F2", "F2", styleID) _ = file.SetCellValue(sheetName, "G2", "XLL") _ = file.SetCellStyle(sheetName, "G2", "G2", styleID) // 設(shè)置值 for i := 0; i < 5; i++ { lineStr := fmt.Sprintf("%d", i+3) // _ = file.SetCellValue(sheetName, "A"+lineStr, "基礎(chǔ)款") _ = file.SetCellStyle(sheetName, "A"+lineStr, "A"+lineStr, styleID) // split := strings.Split("1:2:3:4:5:6", ":") _ = file.SetCellValue(sheetName, "B"+lineStr, split[0]) _ = file.SetCellStyle(sheetName, "B"+lineStr, "B"+lineStr, styleID) _ = file.SetCellValue(sheetName, "C"+lineStr, split[1]) _ = file.SetCellStyle(sheetName, "C"+lineStr, "C"+lineStr, styleID) _ = file.SetCellValue(sheetName, "D"+lineStr, split[2]) _ = file.SetCellStyle(sheetName, "D"+lineStr, "D"+lineStr, styleID) _ = file.SetCellValue(sheetName, "E"+lineStr, split[3]) _ = file.SetCellStyle(sheetName, "E"+lineStr, "E"+lineStr, styleID) _ = file.SetCellValue(sheetName, "F"+lineStr, split[4]) _ = file.SetCellStyle(sheetName, "F"+lineStr, "F"+lineStr, styleID) _ = file.SetCellValue(sheetName, "G"+lineStr, split[5]) _ = file.SetCellStyle(sheetName, "G"+lineStr, "G"+lineStr, styleID) } // file.SetActiveSheet(sheet) // buffer, err := file.WriteToBuffer() if err != nil { w.Write([]byte("導(dǎo)出失敗, " + err.Error())) return } // 設(shè)置文件名 w.Header().Set("Content-Disposition", fmt.Sprintf("attachment; filename=%s", "導(dǎo)出文件.xlsx")) // 導(dǎo)出的文件格式 xlsx 或者 xsl // xlsx application/vnd.openxmlformats-officedocument.spreadsheetml.sheet // xls application/vnd.ms-excel w.Header().Set("Content-Type", "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet") w.Write(buffer.Bytes()) } func main() { // HTTP服務(wù) http.HandleFunc("/fileImport", fileImport) http.HandleFunc("/fileExport", fileExport) err := http.ListenAndServe(":8192", nil) if err != nil { panic(err) } }
頁面請求 http://127.0.0.1:8192/fileExport 后會(huì)直接生成xlsx文件并下載
以上就是基于Golang實(shí)現(xiàn)Excel表格的導(dǎo)入導(dǎo)出功能的詳細(xì)內(nèi)容,更多關(guān)于Golang導(dǎo)入導(dǎo)出Excel表格的資料請關(guān)注腳本之家其它相關(guān)文章!
相關(guān)文章
如何用go-zero 實(shí)現(xiàn)中臺系統(tǒng)
這篇文章主要介紹了如何用go-zero 實(shí)現(xiàn)中臺系統(tǒng),本文給大家介紹的非常詳細(xì),對大家的學(xué)習(xí)或工作具有一定的參考借鑒價(jià)值,需要的朋友可以參考下2020-12-12重學(xué)Go語言之基礎(chǔ)數(shù)據(jù)類型詳解
Go語言有非常強(qiáng)大的數(shù)據(jù)類型系統(tǒng),其支持的數(shù)據(jù)類型大體上可分為四類:基礎(chǔ)數(shù)據(jù)類型、引用數(shù)據(jù)類型、接口類型、復(fù)合類型。本文就來講講它們各自的用法吧2023-02-02