This article brings you relevant knowledge about Golang. It mainly introduces how to use Golang to generate Excel documents. Friends who are interested can take a look below. I hope it will be helpful to everyone.
Generating Excel documents based on data is a very common requirement. This article will introduce how to use Go’s Excelize library to generate Excel documents. As well as code implementation in some specific scenarios.
Excelize
is a basic library written in Go language for operating Office Excel documents, based on ECMA-376, ISO/IEC 29500 international standard. You can use it to read and write spreadsheet documents created with Microsoft Excel™ 2007 and above. It supports multiple document formats such as XLSX / XLSM / XLTM / Data workbook. It can be applied to various reporting platforms, cloud computing, edge computing and other systems. Using this library requires Go language version 1.15 or higher.
The following figure shows the performance comparison of some major open source Excel libraries when generating a 12800*50 plain text matrix (OS: macOS Mojave version 10.14.4, CPU: 3.4 GHz Intel Core i5, RAM: 16 GB 2400 MHz DDR4, HDD: 1 TB), including Go, Python, Java, PHP and NodeJS.
The latest version is v2.4.0
:
go get github.com/360EntSecGroup-Skylar/excelize/v2
In the following case, we created an Excel document and used the NewSheet
method to create a new Sheet2
worksheet. Sheet1
is the worksheet created by default. , and then we use the SetCellValue
method to set the A2
cells of the Sheet2
worksheet and the B2
of the Sheet1
table respectively. Set the value of the cell, set the Sheet2
worksheet as the default worksheet by using the SetActiveSheet
method, and finally call the SaveAs
method to write the data into the Excel document:
package main import ( "fmt" "github.com/360EntSecGroup-Skylar/excelize/v2" ) func main() { f := excelize.NewFile() // 创建一个工作表 index := f.NewSheet("Sheet2") // 设置单元格的值 f.SetCellValue("Sheet2", "A2", "Hello world.") f.SetCellValue("Sheet1", "B2", 100) // 设置工作簿的默认工作表 f.SetActiveSheet(index) // 根据指定路径保存文件 if err := f.SaveAs("Book1.xlsx"); err != nil { fmt.Println(err) } }
The worksheet name is case-sensitive:
index := f.NewSheet("Sheet2")
The Excel document created by default contains a worksheet named Sheet1
. We may not need this default worksheet. At this time, we can delete this worksheet:
f.DeleteSheet("Sheet1")
MergeSheet1
Cells in the F1:I2
range on the worksheet:
excel.MergeCell("Sheet1", "F1", "I2")
Setting styles for cells is often encountered, such as setting the background color of cells. Excelize
The library provides the following two methods to set cell styles (NewStyle
and SetCellStyle
):
// 通过给定的样式格式 JSON 或结构体的指针创建样式并返回样式索引。 // 请注意,颜色需要使用 RGB 色域代码表示。 style, err := f.NewStyle(`{ "border": [ { "type": "left", "color": "0000FF", "style": 3 }, { "type": "top", "color": "00FF00", "style": 4 }, { "type": "bottom", "color": "FFFF00", "style": 5 }, { "type": "right", "color": "FF0000", "style": 6 }, { "type": "diagonalDown", "color": "A020F0", "style": 7 }, { "type": "diagonalUp", "color": "A020F0", "style": 8 }] }`) if err != nil { fmt.Println(err) } err = f.SetCellStyle("Sheet1", "D7", "D7", style)
Horizontal centering of text requires the use of Alignment
Style structure:
type Alignment struct { Horizontal string `json:"horizontal"` Indent int `json:"indent"` JustifyLastLine bool `json:"justify_last_line"` ReadingOrder uint64 `json:"reading_order"` RelativeIndent int `json:"relative_indent"` ShrinkToFit bool `json:"shrink_to_fit"` TextRotation int `json:"text_rotation"` Vertical string `json:"vertical"` WrapText bool `json:"wrap_text"` }
Horizontal centering only needs to be set The value of Horizontal
is center
:
style, err := f.NewStyle(`{"alignment":{"horizontal":"center"}}`) if err != nil { fmt.Println(err) } err = excel.SetCellStyle("Sheet1", "B1", "B1", style)
will be used to fill the cell with color Fill
Style structure:
type Fill struct { Type string `json:"type"` Pattern int `json:"pattern"` Color []string `json:"color"` Shading int `json:"shading"` }
From the code to set the style above, we can find that border
is an array, and alignment
is a structure, which is determined by the Style
structure:
type Style struct { Border []Border `json:"border"` Fill Fill `json:"fill"` Font *Font `json:"font"` Alignment *Alignment `json:"alignment"` Protection *Protection `json:"protection"` NumFmt int `json:"number_format"` DecimalPlaces int `json:"decimal_places"` CustomNumFmt *string `json:"custom_number_format"` Lang string `json:"lang"` NegRed bool `json:"negred"` }
The above is the detailed content of An article explaining in detail how Golang generates Excel documents. For more information, please follow other related articles on the PHP Chinese website!