天津企业网站建站,图书网页设计网站,做瑞士网站,营口市组织部两学一做网站最近项目开发中有涉及到Excel的导入与导出功能#xff0c;特别是导出表格时需要特定的格式#xff08;单元格合并等#xff09;#xff0c;废话不多说#xff0c;直接上代码了。
首先用到一个第三方库#xff0c;实测还是很强大很好用的#xff0c;就是这个https://git…最近项目开发中有涉及到Excel的导入与导出功能特别是导出表格时需要特定的格式单元格合并等废话不多说直接上代码了。
首先用到一个第三方库实测还是很强大很好用的就是这个https://github.com/qax-os/excelize
引用库
go get github.com/xuri/excelize/v2导入表格
package mainimport (encoding/jsongithub.com/xuri/excelize/v2net/httpstrings
)func fileImport(w http.ResponseWriter, r *http.Request) {// 获取请求中的文件名formFile, _, err : r.FormFile(filename)if err ! nil {w.Write([]byte(获取文件失败, err.Error()))return}// 关闭defer formFile.Close()//reader, err : excelize.OpenReader(formFile)if err ! nil {w.Write([]byte(读取文件失败, err.Error()))return}// 关闭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 {// 每一行数据的列, 都是从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服务http.HandleFunc(/fileImport, fileImport)err : http.ListenAndServe(:8192, nil)if err ! nil {panic(err)}
}表格格式 执行后效果
导出表格
package mainimport (encoding/jsonfmtgithub.com/xuri/excelize/v2net/httpstrings
)func fileExport(w http.ResponseWriter, r *http.Request) {file : excelize.NewFile()defer file.Close()// 设置页sheetName : Sheet1// 创建sheet, err : file.NewSheet(sheetName)if err ! nil {w.Write([]byte(创建失败, err.Error()))return}// 设置单元格格式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)// 设置表头_ file.SetCellValue(sheetName, A1, 款)_ file.SetCellStyle(sheetName, A1, A1, styleID)_ file.SetCellValue(sheetName, B1, 尺码)_ file.SetCellStyle(sheetName, B1, B1, styleID)// 设置值for i : 0; i 5; i {lineStr : fmt.Sprintf(%d, i2)//_ file.SetCellValue(sheetName, AlineStr, 基础款)_ file.SetCellStyle(sheetName, AlineStr, AlineStr, styleID)//_ file.SetCellValue(sheetName, BlineStr, 1:2:3:4:5:6)_ file.SetCellStyle(sheetName, BlineStr, BlineStr, styleID)}//file.SetActiveSheet(sheet)//buffer, err : file.WriteToBuffer()if err ! nil {w.Write([]byte(导出失败, err.Error()))return}w.Header().Set(Content-Disposition, fmt.Sprintf(attachment; filename%s, 导出文件.xlsx))w.Header().Set(Content-Type, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)w.Write(buffer.Bytes())
}func main() {// HTTP服务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 后会直接生成xlsx文件并下载
合并单元格 package mainimport (encoding/jsonfmtgithub.com/xuri/excelize/v2net/httpstrings
)func fileExport(w http.ResponseWriter, r *http.Request) {file : excelize.NewFile()defer file.Close()// 设置页sheetName : Sheet1// 创建sheet, err : file.NewSheet(sheetName)if err ! nil {w.Write([]byte(创建失败, err.Error()))return}// 设置单元格格式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)// 设置表头_ 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)// 设置值for i : 0; i 5; i {lineStr : fmt.Sprintf(%d, i3)//_ file.SetCellValue(sheetName, AlineStr, 基础款)_ file.SetCellStyle(sheetName, AlineStr, AlineStr, styleID)//split : strings.Split(1:2:3:4:5:6, :)_ file.SetCellValue(sheetName, BlineStr, split[0])_ file.SetCellStyle(sheetName, BlineStr, BlineStr, styleID)_ file.SetCellValue(sheetName, ClineStr, split[1])_ file.SetCellStyle(sheetName, ClineStr, ClineStr, styleID)_ file.SetCellValue(sheetName, DlineStr, split[2])_ file.SetCellStyle(sheetName, DlineStr, DlineStr, styleID)_ file.SetCellValue(sheetName, ElineStr, split[3])_ file.SetCellStyle(sheetName, ElineStr, ElineStr, styleID)_ file.SetCellValue(sheetName, FlineStr, split[4])_ file.SetCellStyle(sheetName, FlineStr, FlineStr, styleID)_ file.SetCellValue(sheetName, GlineStr, split[5])_ file.SetCellStyle(sheetName, GlineStr, GlineStr, styleID)}//file.SetActiveSheet(sheet)//buffer, err : file.WriteToBuffer()if err ! nil {w.Write([]byte(导出失败, err.Error()))return}// 设置文件名w.Header().Set(Content-Disposition, fmt.Sprintf(attachment; filename%s, 导出文件.xlsx))// 导出的文件格式 xlsx 或者 xsl// xlsx application/vnd.openxmlformats-officedocument.spreadsheetml.sheet// xls application/vnd.ms-excelw.Header().Set(Content-Type, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)w.Write(buffer.Bytes())
}func main() {// HTTP服务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 后会直接生成xlsx文件并下载
