Golang创建读取Excel
飞书技术 人气:0摘要
本文提出一种使用 Golang 进行 Excel 文件创建和读取的方案。首先对问题进行分析，引出方案的基本架构；然后分章节描述了 Excelize 基础库的基本用法，以及 Excel 数据在 Golang 中的表示和解析方式，并进一步提出了应对大规模数据写入场景的优化方法；最后，指出了一些可能遇到的问题和对策。
引言
飞书是业界领先的下一代企业协作与管理平台，集合了很多细分领域的优秀 ToB 产品。作者所在的部门，负责员工持股计划（Employee Stock Ownership Plan，ESOP）相关系统的研发，主要的后端开发语言为 Golang 。系统管理员，需要定期对公司 ESOP 的参与人信息，以及股权激励的授予、归属、取消等信息，以Excel表格的形式进行汇总，为相关决策提供参考和依据；必要时，也需要通过修改 Excel 数据表，上传系统，实现参与人、授予等信息的批量修改。
总而言之，随着 Golang 的广泛应用，如何使用 Golang 进行 Excel 数据表的读取和创建，是一个值得探讨的问题。本文将描述一套完整的 Excel 文件创建和读取的方案，方案力图做到灵活通用，和具体的业务数据类型无关，同时能够兼顾大规模数据导出的效率。最后，分享了一些研发过程中遇到的问题，希望能够避免读者再次踩坑。
正文
架构
在开始具体深入地描述我们的方案之前，不妨先思考一下，实现这样一个方案，需要解决的问题都有哪些？数据的处理大致应该是一个什么样的流程？下图是ESOP系统中，涉及到Excel文件创建和读取的部分业务场景。
文件对象
很自然地，我们需要引入一个 Excel 文件对象，此对象应该包含一个 Excel 工作簿的所有信息：有哪些工作表，每个工作表都有哪些列，每一行数据是什么，单元格和文本的格式是什么样的，某一列是否包含枚举值，等等。我们对文件的任何操作，无论是数据的增删改，还是格式样式的变更，亦或是文件的打开和保存，都应该将这个文件对象作为切入点。
数据的表示
一个 Excel 工作表，可以很自然地和 Golang 结构体联系起来，工作表的每一列对应结构体中的一个字段。然而，只定义一个基本的结构体还不够，至少还应该想办法保存每个字段对应的 Excel 列名、数据验证等等。
数据的解析
用 Golang 结构体表示了一个 Excel 工作表，自然还需要一种方法来解释结构体中记录的各种 Excel 数据信息，这样，程序代码才知道如何将结构体数据正确地写入文件对象，以及反过来，如何读取文件对象中的数据，来还原 Golang 结构体。数据表示和解析的整体思路，如下图所示。
实际架构
幸运的是，上面几个问题，我们都可以找到成熟且有力的工具来解决。方案基本的架构如下图所示。
文件对象的创建和各种操作，我们通过 Excelize 基础库来实现，后面会对该基础库进行简要介绍。
Excel 数据的表示，我们使用包含 tag 的 Golang 结构体实现，数据值以外的其他信息，用某种格式记录在 tag 中。
Excel 数据的解析，我们利用 Golang 的反射机制实现。通过反射，我们可以解析出结构体每个字段的值以及 tag 中保存的其他有用信息。
Excelize 基础库
Excelize 是一个使用 Golang 编写的，用于操作 Office Excel 文档的基础库，支持 Golang 1.15 及以上版本。下面对其基本用法进行介绍，涉及到的各 API 的具体用法，可查看文章末尾给出的Excelize Doc链接。
文件
文件对象是本文大部分 Excel 文件操作的入口。使用 NewFile 函数，可以创建一个空白的文件对象。如果需要用已有的 Excel 文件数据创建文件对象，可以使用 OpenReader 或者 OpenFile 函数。结束文件操作之后，通常需要将文件保存在本地，或者将文件输出为字节数组，返回给前端供用户下载，使用文件对象的 SaveAs 和 WriteToBuffer 方法，可以达到目的。
坐标
在使用更多功能之前，必须搞清楚如何定位一个单元格或一个区域。
Excel 中使用形如“A1”这样的坐标来定位单元格。相应的，在 Excelize 基础库中，可以通过 CoordinatesToCellName 函数，将行号和列号这个二元组，转换为一个形如“A1”的坐标。一些操作，需要通过两个坐标来确定生效区域，此时，两个相同的坐标表示对一个单元格生效，两个不同的坐标表示对一个矩形区域生效，分别指向区域左上角和右下角的单元格。
样式
样式包含字体、文字大小、粗细、位置、颜色等属性。Excelize 中，样式可以通过 NewStyle 方法创建，返回一个整数索引，唯一标识这个样式。通过文件对象的 SetCellStyle 方法，指定坐标和样式索引，可以为一个区域设置统一的样式。
单元格操作
单元格的常用操作有设置高度和宽度、合并单元格、设置单元格数据等。
我们针对一行设置高度，针对一列设置宽度，分别使用文件对象中的 SetRowHeight 和 SetColWidth 方法进行。
在“坐标”部分，我们讲解了如何确定一个区域，合并单元格的操作，就是一个例子，我们可以使用文件对象中的 MergeCell 方法来完成。
一般情况下，数据的写入操作，在单元格层面进行。使用文件对象中的 SetCellValue 方法，指定一个坐标，可以将 Golang 中常用的数据类型（包括无符号整数、有符号整数、浮点数、字节切片、字符串、时间、布尔类型等等）的值写入对应的单元格。
数据验证
数据验证功能，可以为某一列数据定义枚举值，用户可以使用下拉列表，为该列中某一行选择要填入的值。
使用 NewDataValidation 函数，可以创建一个数据验证对象，不妨命名为 vd 。为了指定 vd 的生效范围，需要为 vd 设置一个 Sqref 属性，格式形如“A1:A10”，表示此 vd 对第 1 列中第 1 行到第 10 行的数据生效。然后，使用 vd 的 SetDropList 方法，设置下拉列表的内容。最后，使用文件对象中的 AddDataValidation 方法，将此 vd 写入文件。
数据的表示和解析
表示
根据“架构”部分的设想，我们可以定义一个带有 tag 的结构体，来表示一个 Excel 工作表。
Golang 结构体的 tag ，是以键值对的形式表示的。为了和其他用途的 tag 进行区分，我们将本方案的 tag 信息，用一个名为 ex 的键来表示，而 ex 的值，仍然沿用键值对的形式，如下列代码所示：
type PeopleInfo struct {
PeopleNo string `ex:"head:工号;type:string;required;color:#0070C0"`
PeopleName string `ex:"head:姓名;type:string;required"`
BirthDate string `ex:"head:出生日期;type:date;omitempty"`
EmploymentStatus string `ex:"head:在职状态;type:string;select:在职,离职"`
}
我们可以为ex设计下列属性：
- head，指定了此结构体字段对应的 Excel 列名。
- type，表示在使用反射进行数据解析时，会将此结构体字段的值作为指定的类型处理。
- select，表示此字段所在的列，包含一个下拉列表，列表中的枚举值由 select 后面的值指定。
- required，表示此字段必须包含非零值，否则在写入 Excel 时会报错。
- omitempty，表示此字段如果是零值，则对应的单元格留空。
- color，指定了列名所在单元格的颜色，通过这个字段，可以为不同的列名设置不同的底色，赋予一些含义，例如，可以将必填的列和选填的列，设置不同的底色。可以通过 Excel 的 RGB 颜色设置窗口，查看不同颜色对应的色号，作为 color 属性的值。
此外，我们还要定义一个结构体，保存 ex 的解析结果，结构体不妨命名为 Setting ：
type Setting struct {
Head string
Type string
Select []string
Required bool
OmitEmpty bool
Color string
}
解析
使用 Golang 的反射机制，对类似于 PeopleInfo 这样的结构体，我们可以抽取每个字段的ex值，进行字符串处理后，组装成Setting对象。示例代码如下：
import reflect
// 解析第idx个字段的ex
func ParseEx(idx int, data interface{}) *Setting {
tp := reflect.ValueOf(data).Type().Elem().Elem() // 获得结构体的反射Type
field := tp.Field(idx)
exStr := field.Tag.Get("ex") // 获得tag中ex的值
setting := &Setting{}
// 下面可通过对exStr字符串进行切分，来组装Setting对象，较为简单，省略
...
return setting
}
func main() {
ParseEx(0, []*PeopleInfo{{}})
}
由于反射机制较为抽象，这里不再赘述，对反射不熟悉的读者，可以查看文章末尾给出的 Golang reflect 链接。
组装了 Setting 之后，我们可以继续通过反射，来获取结构体中各字段的值，然后使用前面介绍过的一些 API ，将这些信息写入 Excel 文件。
下面给出创建 Excel 文件的示例代码，代码对 omitempty 和 type 属性进行了处理，并将部分数据写入文件对象。其他 ex 属性的处理，因篇幅有限，不再演示，读者有兴趣可以自己尝试实现。
import reflect
import "github.com/xuri/excelize/v2"
// 写入第1行数据的第idx个字段
func WriteFirstRow(ef *excelize.File, idx int, data interface{}) error {
firstRow := reflect.ValueOf(data).Index(0).Elem() // 第1个数据的反射Value
v := firstRow.Field(idx) // 第idx个字段的反射Value
setting := ParseEx(idx, data) // 第idx个字段解析出来的ex信息
// 处理omitempty
if setting.OmitEmpty && v.IsZero() {
return nil
}
var val interface{}
// 处理type
switch setting.Type {
case "string":
val = v.String()
case ...
}
// Excel列号从1开始，所以列号是idx+1；行号从2开始，因为第1行要显示列名
axis, err := excelize.CoordinatesToCellName(idx+1, 2)
if err != nil {
return err
}
// 将数据写入默认工作表Sheet1中axis坐标处的单元格
return ef.SetCellValue("Sheet1", axis, val)
}
func main() {
ef := excelize.NewFile()
WriteFirstRow(ef, 0, []*PeopleInfo{{PeopleNo: "test"}})
ef.SaveAs("people_info.xlsx")
}
上面给出的是创建 Excel 文件的示例。读取 Excel 文件的过程是类似的，首先从二进制数据创建出文件对象，然后根据文件对象中的每一列数据，生成对应的结构体对象。示例代码如下。
import reflect
import "github.com/xuri/excelize/v2"
// 读取第1行数据的第idx列，假定idx从0开始，只有一个默认工作表Sheet1，数据从第2行开始
func ReadFirstRow(ef *excelize.File, idx int, holder interface{}) error {
rows, err := ef.GetRows("Sheet1") // 所有行
if err != nil {
return err
}
row := rows[1]
tp := reflect.TypeOf(holder).Elem().Elem().Elem() // 结构体的类型
val := reflect.New(tp) // 创建一个新的结构体对象
field := val.Elem().Field(idx) // 第idx个字段的反射Value
cellValue := row[idx] // 第idx个字段对应的Excel数据
field.SetString(cellValue) // 将Excel数据保存到结构体对象的对应字段中
listV := reflect.ValueOf(holder)
listV.Elem().Set(reflect.Append(listV.Elem(), val)) // 将结构体对象添加到holder中
return nil
}
func main() {
ef, _ := excelize.OpenFile("people_info.xlsx")
holder := make([]*PeopleInfo, 0, 10)
ReadFirstRow(ef, 0, &holder)
}
本节描述了如何使用 Golang 来表示和解析 Excel 数据，以及在此基础上如何创建和读取 Excel 文件。示例代码中对 Excel 文件的写入和读取操作函数，使用 interface 类型的参数作为数据提供方或接收方，和具体的业务数据类型无关，因此该方案具备通用性。
大规模数据的写入
之前演示的 Excel 文件写入方式，是在单元格层面进行的，在大规模数据写入的场景下，耗时长，体验差。Excelize 提供了一套流式写入 API，以行为单位写入 Excel 数据，能够显著提高大规模数据的写入效率。
使用流式 API 写入 Excel 数据，首先需要使用文件对象的 NewStreamWriter 方法，创建一个流式写入器。写入一行数据时，需要构造一个切片，表示这一行数据，切片中每个元素表示一个单元格信息，包含单元格的值和样式。单元格元素，可以使用 Excelize 中提供的 Cell 数据类型来表示。之后，就可以通过流式写入器的 SetRow 方法，将行数据写入 Excel 文件。行的高度，可以在写入时指定。示例代码如下：
import reflect
import "github.com/xuri/excelize/v2"
// 写入第1行数据
func StreamWriteFirstRow(sw *excelize.StreamWriter, data interface{}) error {
firstRow := reflect.ValueOf(data).Index(0).Elem() // 第1个数据的反射Value
v := firstRow.Field(0) // 首个字段的反射Value
style := &excelize.Style{
Alignment: &excelize.Alignment{
Horizontal: "left",
Vertical: "center",
},
}
styleID, err := sw.File.NewStyle(style) // 创建样式
if err != nil {
return err
}
length := firstRow.NumField() // 结构体字段数量
row := make([]interface{}, length) // 创建一个切片，表示一行数据
row[0] = excelize.Cell{
Value: v.String(),
StyleID: styleID,
} // 这里只写入首个字段
// 每一行都是从列号1开始；行号从2开始，因为假定第1行要显示列名
axis, err := excelize.CoordinatesToCellName(1, 2)
if err != nil {
return err
}
// 流式写入行，并指定高度
return sw.SetRow(axis, row, excelize.RowOpts{Height: 16})
}
func main() {
ef := excelize.NewFile()
sw, _ := ef.NewStreamWriter("Sheet1")
StreamWriteFirstRow(sw, []*PeopleInfo{{PeopleNo: "test"}})
sw.Flush()
ef.SaveAs("stream_people_info.xlsx")
}
需要关注的问题
大量枚举值的设置
在“数据验证”部分，我们提到使用 SetDropList 方法可以设置下拉列表。然而，这样设置的下拉列表是有局限性的：每个枚举值使用逗号拼接后得到的字符串，其总长度不得超过 255 个字符。
如果超过了这个限制，我们需要创建一个工作表（假设名称为 enum ），将枚举值存储在 enum 工作表的某一列中（假设存储在 A 列，第 2 行到第 10 行），然后通过 vd 的 SetSqrefDropList 方法设置下拉列表，此方法通过一个形如“enum!A2:A10”的字符串来指定枚举值的存储位置，即 enum 工作表 A 列的第 2 行到第 10 行。
大工作表的读取
读取 Excel 文件时，我们会基于已有的物理文件来创建文件对象，如果其中有一个很大的工作表，那么当我们将这个文件对象另存为一个新的物理文件时，可能会发现文件变小了，且无法正常打开。
Excelize 库通过一些参数，来限制打开和读取工作薄时的内存使用。其中，WorksheetUnzipMemLimit 参数限制了 unzip 一个工作表时允许使用的最大内存，默认为 16 MB 。当一个工作表大小超过这一默认值时，此工作表的数据会 unzip 到操作系统的临时文件中。然而，当我们进行另存为操作时，这些临时文件的数据却被 Excelize 的相关函数忽略了。
这可能是 Excelize 库的一个 bug ，但是我们可以通过增大 WorksheetUnzipMemLimit 参数来规避。这一参数的值，可根据具体业务场景来设置，最大可以设置为和 UnzipSizeLimit 参数相同，后者是打开整个工作簿时总的内存使用限制，默认为 16 GB 。
流式写入的注意事项
流式操作有自己的一套 API ，用于数据写入、合并单元格、设置列宽等操作。流式 API 不能和普通的非流式 API 混用，否则可能无法正确写入数据或设置格式。使用流式 API 设置列宽，需要在写入数据之前进行。流式写入完成之后，需要调用流式写入器的 Flush 方法来结束写入，否则保存文件时可能会丢失数据。
结语
本文对 Golang 中创建和读取 Excel 文件所涉及的各方面问题，进行了总结归纳，并提出了一套完整的方案。此方案使用 Golang 结构体的 tag ，以及 Golang 反射机制，对 Excel 数据进行定义和解释，实现了 Golang 结构体和 Excel 数据的双向映射，同时使用成熟强大的 Excelize 基础库，对 Excel 文件进行创建、写入、读取等操作。
希望读者能有所收获，为解决实际的问题提供思路，也欢迎大家对方案中的不足之处提出改进意见。
参考资料
- Excelize Doc：xuri.me/excelize/zh…
- Golang reflect：pkg.go.dev/reflect
总结
加载全部内容