Worksheet
Set column visibility
func (f *File) SetColVisible(sheet, col string, visible bool) error
SetColVisible provides a function to set visible of a single column by given worksheet name and column name. This function is concurrency safe. For example, hide column D in Sheet1:
err := f.SetColVisible("Sheet1", "D", false)
Hide the columns from D to F (included):
err := f.SetColVisible("Sheet1", "D:F", false)
Set column width
func (f *File) SetColWidth(sheet, startCol, endCol string, width float64) error
SetColWidth provides a function to set the width of a single column or multiple columns. This function is concurrency safe. For example:
f := excelize.NewFile()
err := f.SetColWidth("Sheet1", "A", "H", 20)
Set row height
func (f *File) SetRowHeight(sheet string, row int, height float64) error
SetRowHeight provides a function to set the height of a single row. If the value of height is 0, will hide the specified row, if the value of height is -1, will unset the custom row height. For example, set the height of the first row in Sheet1:
err := f.SetRowHeight("Sheet1", 1, 50)
Set row visibility
func (f *File) SetRowVisible(sheet string, row int, visible bool) error
SetRowVisible provides a function to set visible of a single row by given worksheet name and row number. For example, hide row 2 in Sheet1:
err := f.SetRowVisible("Sheet1", 2, false)
Get sheet name
func (f *File) GetSheetName(index int) string
GetSheetName provides a function to get the sheet name of the workbook by the given sheet index. If the given sheet index is invalid, it will return an empty string.
Get column visibility
func (f *File) GetColVisible(sheet, column string) (bool, error)
GetColVisible provides a function to get visible of a single column by given worksheet name and column name. This function is concurrency safe. For example, get the visible state of column D in Sheet1:
visible, err := f.GetColVisible("Sheet1", "D")
Get column width
func (f *File) GetColWidth(sheet, col string) (float64, error)
GetColWidth provides a function to get the column width by given the worksheet name and column name. This function is concurrency safe.
Get row height
func (f *File) GetRowHeight(sheet string, row int) (float64, error)
GetRowHeight provides a function to get row height by given worksheet name and row number. For example, get the height of the first row in Sheet1:
height, err := f.GetRowHeight("Sheet1", 1)
Get row visibility
func (f *File) GetRowVisible(sheet string, row int) (bool, error)
GetRowVisible provides a function to get visible of a single row by given worksheet name and row number. For example, get visible state of row 2 in Sheet1:
visible, err := f.GetRowVisible("Sheet1", 2)
Get sheet index
func (f *File) GetSheetIndex(sheet string) (int, error)
GetSheetIndex provides a function to get a sheet index of the workbook by the given sheet name. If the given sheet name is invalid or sheet doesn't exist, it will return an integer type value -1.
The obtained index can be used as a parameter to call the SetActiveSheet() function when setting the workbook default worksheet.
Get sheet map
func (f *File) GetSheetMap() map[int]string
GetSheetMap provides a function to get worksheets, chart sheets, dialog sheets ID, and name maps of the workbook. For example:
f, err := excelize.OpenFile("Book1.xlsx")
if err != nil {
return
}
defer func() {
if err := f.Close(); err != nil {
fmt.Println(err)
}
}()
for index, name := range f.GetSheetMap() {
fmt.Println(index, name)
}
Get sheet list
func (f *File) GetSheetList() []string
GetSheetList provides a function to get worksheets, chart sheets, and dialog sheets name list of the workbook.
Set sheet name
func (f *File) SetSheetName(source, target string) error
SetSheetName provides a function to set the worksheet name by given the source and target worksheet names. Maximum 31 characters are allowed in sheet title and this function only changes the name of the sheet and will not update the sheet name in the formula or reference associated with the cell. So there may be a problem formula error or reference missing.
Insert columns
func (f *File) InsertCols(sheet, col string, n int) error
InsertCols provides a function to insert new columns before the given column name and number of columns. For example, create two columns before column C in Sheet1:
err := f.InsertCols("Sheet1", "C", 2)
Insert rows
func (f *File) InsertRows(sheet string, row, n int) error
InsertRows provides a function to insert new rows after the given Excel row number starting from 1 and number of rows. For example, create two rows before row 3 in Sheet1:
err := f.InsertRows("Sheet1", 3, 2)
Append duplicate row
func (f *File) DuplicateRow(sheet string, row int) error
DuplicateRow inserts a copy of specific row below specified, for example:
err := f.DuplicateRow("Sheet1", 2)
Use this method with caution, which will affect changes in references such as formulas, charts, and so on. If there is any referenced value of the worksheet, it will cause a file error when you open it. The excelize only partially updates these references currently.
Duplicate row
func (f *File) DuplicateRowTo(sheet string, row, row2 int) error
DuplicateRowTo inserts a copy of specified row by it Excel number to specified row position moving down exists rows after target position, for example:
err := f.DuplicateRowTo("Sheet1", 2, 7)
Use this method with caution, which will affect changes in references such as formulas, charts, and so on. If there is any referenced value of the worksheet, it will cause a file error when you open it. The excelize only partially updates these references currently.
Create row outline
func (f *File) SetRowOutlineLevel(sheet string, row int, level uint8) error
SetRowOutlineLevel provides a function to set outline level number of a single row by given worksheet name and Excel row number. For example, outline row 2 in Sheet1 to level 1:
err := f.SetRowOutlineLevel("Sheet1", 2, 1)
Create column outline
func (f *File) SetColOutlineLevel(sheet, col string, level uint8) error
SetColOutlineLevel provides a function to set outline level of a single column by given worksheet name and column name. For example, set outline level of column D in Sheet1 to 2:
err := f.SetColOutlineLevel("Sheet1", "D", 2)
Get row outline
func (f *File) GetRowOutlineLevel(sheet string, row int) (uint8, error)
GetRowOutlineLevel provides a function to get the outline level number of a single row by given worksheet name and Excel row number. For example, get the outline number of row 2 in Sheet1:
err := f.GetRowOutlineLevel("Sheet1", 2)
Get column outline
func (f *File) GetColOutlineLevel(sheet, col string) (uint8, error)
GetColOutlineLevel provides a function to get the outline level of a single column by given worksheet name and column name. For example, get outline level of column D in Sheet1:
level, err := f.GetColOutlineLevel("Sheet1", "D")
Column iterator
func (f *File) Cols(sheet string) (*Cols, error)
Cols returns a column iterator, used for streaming reading data for a worksheet with a large data. This function is concurrency safe. For example:
cols, err := f.Cols("Sheet1")
if err != nil {
fmt.Println(err)
return
}
for cols.Next() {
col, err := cols.Rows()
if err != nil {
fmt.Println(err)
}
for _, rowCell := range col {
fmt.Print(rowCell, "\t")
}
fmt.Println()
}
Column iterator - Columns
func (cols *Cols) Rows(opts ...Options) ([]string, error)
Rows return the current column's row values.
Column iterator - Traversing
func (cols *Cols) Next() bool
Next will return true if the next column is found.
Column iterator - Error handling
func (cols *Cols) Error() error
Error will return the error when the error occurs.
Row iterator
func (f *File) Rows(sheet string) (*Rows, error)
Rows returns a rows iterator, used for streaming reading data for a worksheet with a large data. This function is concurrency safe. For example:
rows, err := f.Rows("Sheet1")
if err != nil {
fmt.Println(err)
return
}
for rows.Next() {
row, err := rows.Columns()
if err != nil {
fmt.Println(err)
}
for _, colCell := range row {
fmt.Print(colCell, "\t")
}
fmt.Println()
}
if err = rows.Close(); err != nil {
fmt.Println(err)
}
Row iterator - Columns
func (rows *Rows) Columns(opts ...Options) ([]string, error)
Columns return the current row's column values. This fetches the worksheet data as a stream, returns each cell in a row as is, and will not skip empty rows in the tail of the worksheet.
Row iterator - Traversing
func (rows *Rows) Next() bool
Next will return true if find the next row element.
Row iterator - Error handling
func (rows *Rows) Error() error
Error will return the error when the error occurs.
Row iterator - Get row options
func (rows *Rows) GetRowOpts() RowOpts
GetRowOpts will return the RowOpts of the current row.
Row iterator - Close
func (rows *Rows) Close() error
Close closes the open worksheet XML file in the system temporary directory.
Search Sheet
func (f *File) SearchSheet(sheet, value string, reg ...bool) ([]string, error)
SearchSheet provides a function to get cell reference by given worksheet name, cell value, and regular expression. The function doesn't support searching on the calculated result, formatted numbers and conditional lookup currently. If it is a merged cell, it will return the cell reference of the upper left cell of the merged range reference.
For example, search the cell reference of the value of 100 on Sheet1:
result, err := f.SearchSheet("Sheet1", "100")
For example, search the cell reference where the numerical value in the range of 0-9 of Sheet1 is described:
result, err := f.SearchSheet("Sheet1", "[0-9]", true)
Protect Sheet
func (f *File) ProtectSheet(sheet string, opts *SheetProtectionOptions) error
ProtectSheet provides a function to prevent other users from accidentally or deliberately changing, moving, or deleting data in a worksheet. The optional field AlgorithmName specified hash algorithm, support XOR, MD4, MD5, SHA-1, SHA-256, SHA-384, and SHA-512 currently, if no hash algorithm specified, will be using the XOR algorithm as default. For example, protect Sheet1 with protection settings:
err := f.ProtectSheet("Sheet1", &excelize.SheetProtectionOptions{
AlgorithmName: "SHA-512",
Password: "password",
SelectLockedCells: true,
SelectUnlockedCells: true,
EditScenarios: true,
})
SheetProtectionOptions directly maps the settings of worksheet protection.
type SheetProtectionOptions struct {
AlgorithmName string
AutoFilter bool
DeleteColumns bool
DeleteRows bool
EditObjects bool
EditScenarios bool
FormatCells bool
FormatColumns bool
FormatRows bool
InsertColumns bool
InsertHyperlinks bool
InsertRows bool
Password string
PivotTables bool
SelectLockedCells bool
SelectUnlockedCells bool
Sort bool
}
Unprotect Sheet
func (f *File) UnprotectSheet(sheet string, password ...string) error
UnprotectSheet provides a function to remove protection for a sheet, specified the second optional password parameter to remove sheet protection with password verification.
Remove column
func (f *File) RemoveCol(sheet, col string) error
RemoveCol provides a function to remove a single column by given worksheet name and column index. For example, remove column C in Sheet1:
err := f.RemoveCol("Sheet1", "C")
Use this method with caution, which will affect changes in references such as formulas, charts, and so on. If there is any referenced value of the worksheet, it will cause a file error when you open it. The excelize only partially updates these references currently.
Remove row
func (f *File) RemoveRow(sheet string, row int) error
RemoveRow provides a function to remove a single row by given worksheet name and Excel row number. For example, remove row 3 in Sheet1:
err := f.RemoveRow("Sheet1", 3)
Use this method with caution, which will affect changes in references such as formulas, charts, and so on. If there is any referenced value of the worksheet, it will cause a file error when you open it. The excelize only partially updates these references currently.
Set column values
func (f *File) SetSheetCol(sheet, cell string, slice interface{}) error
SetSheetCol writes an array to column by given worksheet name, starting cell reference and a pointer to array type slice. For example, writes an array to column B start with the cell B6 on Sheet1:
err := f.SetSheetCol("Sheet1", "B6", &[]interface{}{"1", nil, 2})
Set row values
func (f *File) SetSheetRow(sheet, cell string, slice interface{}) error
SetSheetRow writes an array to row by given worksheet name, starting cell reference and a pointer to array type slice. This function is concurrency safe. For example, writes an array to row 6 start with the cell B6 on Sheet1:
err := f.SetSheetRow("Sheet1", "B6", &[]interface{}{"1", nil, 2})
Insert page break
func (f *File) InsertPageBreak(sheet, cell string) error
InsertPageBreak create a page break to determine where the printed page ends and where begins the next one by given worksheet name and cell reference, so the content before the page break will be printed on one page and after the page break on another.
Remove page break
func (f *File) RemovePageBreak(sheet, cell string) error
RemovePageBreak remove a page break by given worksheet name and cell reference.
Set sheet dimension
func (f *File) SetSheetDimension(sheet string, rangeRef string) error
SetSheetDimension provides the method to set or remove the used range of the worksheet by a given range reference. It specifies the row and column bounds of used cells in the worksheet. The range reference is set using the A1 reference style(e.g., A1:D5). Passing an empty range reference will remove the used range of the worksheet.
Get sheet dimension
func (f *File) GetSheetDimension(sheet string) (string, error)
GetSheetDimension provides the method to get the used range of the worksheet.