golang mapping between xlsx and struct instances.
本库提供高层golang的struct切片和excel文件的映射，避免直接处理底层sheet/row/cells等细节。
本库底层使用unioffice，其提供了比360EntSecGroup-Skylar/excelize更加友好的API。
还有另外一个比较活跃的底层实现tealeg/xlsx尚未认证。
package main import"github.com/bingoohuang/xlsx"typememberStatstruct{Totalint`title:"会员总数" sheet:"会员"`// sheet可选，不声明则选择首个sheet页读写Newint`title:"其中：新增"`Effectiveint`title:"其中：有效"` } funcmain(){x, _:=xlsx.New() deferx.Close() x.Write([]memberStat{{Total: 100, New: 50, Effective: 50},{Total: 200, New: 60, Effective: 140}, }) x.SaveToFile("testdata/test1.xlsx") }you will get the result excel file as the following:
x, _:=xlsx.New(xlsx.WithTemplate("testdata/template.xlsx")) deferx.Close() x.Write([]memberStat{{Total: 100, New: 50, Effective: 50},{Total: 200, New: 60, Effective: 140}, }) x.SaveToFile("testdata/test1.xlsx")you will get the result excel file as the following:
varmemberStats []memberStatx, _:=xlsx.New(xlsx.WithExcel("testdata/test1.xlsx")) deferx.Close() iferr:=x.Read(&memberStats); err!=nil{panic(err) } assert.Equal(t, []memberStat{{Total: 100, New: 50, Effective: 50},{Total: 200, New: 60, Effective: 140}, }, memberStats)- Method 1: use template sheet to list the validation datas like:
then declare the data validation tag dataValidation like:
typeMemberstruct{Areastring`title:"区域" dataValidation:"Validation!A1:A3"`Totalint`title:"会员总数"`Newint`title:"其中：新增"`Effectiveint`title:"其中：有效"` }- Method 2: directly give the list in tag
dataValidationwith comma-separated like:
typeMemberstruct{Areastring`title:"区域" dataValidation:"A,B,C"`Totalint`title:"会员总数"`Newint`title:"其中：新增"`Effectiveint`title:"其中：有效"` }- Method 3: programmatically declares and specified the key name in the tag
dataValidationlike:
typeMemberstruct{Areastring`title:"区域" dataValidation:"areas"`Totalint`title:"会员总数"`Newint`title:"其中：新增"`Effectiveint`title:"其中：有效"` } funcdemo(){x, _:=xlsx.New(xlsx.WithValidations(map[string][]string{"areas":{"A23", "B23", "C23"}, })) deferx.Close() _=x.Write([]memberStat23{{Area: "A23", Total: 100, New: 50, Effective: 50},{Area: "B23", Total: 200, New: 60, Effective: 140},{Area: "C23", Total: 300, New: 70, Effective: 240}, }) _=x.SaveToFile("result.xlsx") }
typeRegisterTablestruct{ContactNamestring`asPlaceholder:"true"`// 联系人Mobilestring// 手机Landlinestring// 座机RegisterDate time.Time// 登记日期DeviceTypestring`placeholderCell:"C8"`// 类型Manufacturerstring// 生产厂家DeviceModernstring// 型号 } funcdemo(){x, _:=xlsx.New(xlsx.WithTemplate("testdata/placeholder.xlsx")) deferx.Close() _=x.Write(RegisterTable{ContactName: "隔壁老王", Mobile: "1234567890", Landline: "010-1234567890", RegisterDate: time.Now(), DeviceType: "A1", Manufacturer: "来弄你", DeviceModern: "X786", }) _=x.SaveToFile("testdata/out_placeholder.xlsx") }
占位符模板读取，需要两个文件：
- “占位符模板”excel文件
- “待读取数据”excel文件
然后从“占位符模板”里获取占位符的位置，用这个位置信息，去实际“待读取数据”的excel文件中提取数据。
typeRegisterTablestruct{ContactNamestring`asPlaceholder:"true"`// 联系人Mobilestring// 手机Landlinestring// 座机RegisterDate time.Time// 登记日期DeviceTypestring`placeholderCell:"C8"`// 类型，直接从C8单元格读取Manufacturerstring// 生产厂家DeviceModernstring// 型号 } funcdemo() error{x, err:=xlsx.New( xlsx.WithTemplate("testdata/placeholder.xlsx"), // 1. “占位符模板”excel文件xlsx.WithExcel("testdata/out_placeholder.xlsx")) // 2. “待读取数据”excel文件iferr!=nil{returnerr } deferx2.Close() varvRegisterTableerr=x2.Read(&v) iferr!=nil{returnerr } }asPlaceholder:"true"使用“占位符模板”excel文件，从“占位符模板”里获取占位符的位置，用这个位置信息，去实际“待读取数据”的excel文件中提取数据。ignoreEmptyRows:"false"读取excel时，是否忽略全空行(包括空白)，默认true
Libraries for working with Microsoft Excel.
- qax-os/excelize at 2022-04-12
- excelize - Golang library for reading and writing Microsoft Excel™ (XLSX) files.
- go-excel - A simple and light reader to read a relate-db-like excel as a table.
- goxlsxwriter - Golang bindings for libxlsxwriter for writing XLSX (Microsoft Excel) files.
- xlsx - Library to simplify reading the XML format used by recent version of Microsoft Excel in Go programs.
- xlsx - Fast and safe way to read/update your existing Microsoft Excel files in Go programs.