Golang operation Excel module Excel – sets the value of the cell
-
Create a new excel file
excelize.NewFile() -
Gets the index of the currently active sheet page
func (f *File) GetActiveSheetIndex() (index int) -
Specify cell settings
func (f *File) SetCellValue(sheet, axis string, value interface{}) error -
Specify the cell setting value, and generally do not transcode
func (f *File) SetCellDefault(sheet, axis, value string) error -
Sets the value string of the cell
func (f *File) SetCellStr(sheet, axis, value string) error -
Sets the Boolean value of the cell
func (f *File) SetCellBool(sheet, axis string, value bool) error -
Sets the floating point value of the cell
The third parameter is a value, which must be of float64 type. The fourth is the number of decimal places. The fifth is 32 or 64. The original data is float32, which is 32, and float64, which is 64
func (f *File) SetCellFloat(sheet, axis string, value float64, prec, bitSize int) error
-
Sets the integer value of the cell
func (f *File) SetCellInt(sheet, axis string, value int) error -
Set cell value formula
func (f *File) SetCellFormula(sheet, axis, formula string, opts ...FormulaOpts) error -
Set link linktype - > location local file - > external external external link, and jump to the page
It only sets the link, does not set the displayed value, and does not set the style
func (f *File) SetCellHyperLink(sheet, axis, link, linkType string, opts ...HyperlinkOpts) error
-
Insert row insert column the index of the inserted row / column is the index of the current row, starting from 1/A
func (f *File) InsertRow(sheet string, row int) error
func (f *File) InsertCol(sheet, col string) error -
To append a copied row is to insert the current row below
func (f *File) DuplicateRow(sheet string, row int) error -
Copy specified row to specified row copy row to row2
func (f *File) DuplicateRowTo(sheet string, row, row2 int) error -
Delete row / column
func (f *File) RemoveRow(sheet string, row int) error
func (f *File) RemoveCol(sheet, col string) error -
Query the regular cells in the specified sheet page
The searched cells meet character matching, that is, cells that meet unicode encoding
func (f *File) SearchSheet(sheet, value string, reg ...bool) ([]string, error) -
Set the name of the worksheet (sheet page)
func (f *File) SetSheetName(oldName, newName string) -
The third parameter is the pointer of the slice
func (f *File) SetSheetRow(sheet, axis string, slice interface{}) error -
Conversion between row column index and cell coordinate
Func excel. Joincellname (col, string, row, int) (string, error) row column index to cell coordinate
Func excel.splitcellname (cell string) (string, int, error) cell column row index -
merge cell
Func (* excel. File). Mergecell (sheet string, hcell string, vcell string) error the last two parameters are the coordinates of the start cell and the end cellThe values of all the basic cells of the merged cells are the same. I don't know if it's the excel version. It's to be confirmed
Code: main.go
package main
import (
"fmt"
"github.com/xuri/excelize/v2"
)
func main() {
f := excelize.NewFile()
f.Path = "../excel_files/TMP_02.xlsx"
// Gets the index of the currently active sheet page
sheet_idx := f.GetActiveSheetIndex()
sheetName := f.GetSheetName(sheet_idx)
// Sets the value of the cell
// The values set by SetCellValue in excel are in normal mode
f.SetCellValue(sheetName, "A1", "character string")
f.SetCellValue(sheetName, "A2", 200)
f.SetCellValue(sheetName, "A3", true)
f.SetCellStr(sheetName, "B1", "This is SetCellStr")
f.SetCellBool(sheetName, "B3", true)
f.SetCellFloat(sheetName, "B2", 100.00200, 3, 64) // The third parameter is a value, which must be of float64 type. The fourth is the number of decimal places. The fifth is 32 or 64. The original data is float32, which is 32, and float64, which is 64
f.SetCellInt(sheetName, "B4", 100000) // integer
f.SetCellDefault(sheetName, "C1", "This is SetCellDefault")
// Sets the formula for a single cell
f.SetCellFormula(sheetName, "C2", "B2+A2")
// Links are only links, and do not assign values to cells. You need to set values and styles separately
f.SetCellHyperLink(sheetName, "D1", "https://www.baidu.com", "External")
f.SetCellValue(sheetName, "D1", "Link 1 External")
f.SetCellHyperLink(sheetName, "D2", "Sheet1!A1", "Location")
f.SetCellValue(sheetName, "D2", "Link 2 Location")
// Insert row
f.InsertRow(sheetName, 2)
f.InsertCol(sheetName, "D")
// Copy line
f.DuplicateRow(sheetName, 3)
f.DuplicateRowTo(sheetName, 1, 7)
// Delete row / column
f.RemoveCol(sheetName, "F")
f.RemoveRow(sheetName, 7)
//lookup
fmt.Println(f.SearchSheet(sheetName, "200", true))
fmt.Println(f.SearchSheet(sheetName, "^[0-9]{1,3}$", true))
// Modify the name of the sheet page
f.SetSheetName(sheetName, "New name")
// go data type is added to excel. The basic data types, string, int, float and bool, can be directly realized by setting the value of the specified cell
// The following demonstrates how to write data in array, slice and map format into excel
var arr = []interface{}{
"full name", "Gender", "Age", "work",
}
idx := f.NewSheet("data")
// Data must be of type [] interface {}
err := f.SetSheetRow("data", "A1", &arr)
if err != nil {
fmt.Println(err)
}
f.SetActiveSheet(idx)
// Convert row column index to cell index
fmt.Println(excelize.JoinCellName("A", 10))
// Cell index split line, column index
fmt.Println(excelize.SplitCellName("AA10"))
// Setting formulas for multiple cells is equivalent to copying formulas by dragging in excel
data := [][]float64{{10.5, 20.5}, {18, 28}, {100, 900}}
for i, d := range data {
startCell, _ := excelize.JoinCellName("A", i+2)
f.SetSheetRow("data", startCell, &d)
}
// Define all cell ranges for which formulas need to be set
f_type, ref := excelize.STCellFormulaTypeShared, "C2:C4"
// This is equivalent to setting a calculation formula template
if err := f.SetCellFormula("data", "C2", "=SUM(A2:B2)", excelize.FormulaOpts{Type: &f_type, Ref: &ref}); err != nil {
fmt.Println(err)
}
// merge cell
if err := f.MergeCell("data", "D1", "G6"); err != nil {
fmt.Println(err)
}
mergedCells, err := f.GetMergeCells("data")
if err != nil {
fmt.Println(err)
}
// Gets the value of the merged cell and the start and end position of the merged cell
for _, cell := range mergedCells {
fmt.Println(cell.GetStartAxis())
fmt.Println(cell.GetEndAxis())
fmt.Println(cell.GetCellValue())
}
v, err := f.GetCellValue("data", "F3")
if err != nil {
fmt.Println(err)
}
fmt.Println("The data values in the merged cells are:", v)
// preservation
f.Save()
// f.SaveAs("../excel_files/TMP_02.xlsx")
}
For more API s, please move the author's document: https://xuri.me/excelize/zh-hans/
Author's video: https://www.bilibili.com/video/BV1hU4y1F7wQ