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. 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. 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. 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. 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.

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:

err := f.GetRowVisible("Sheet1", 2)

Get sheet index

func (f *File) GetSheetIndex(name string) int

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 the worksheet 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
}
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(oldName, newName string)

SetSheetName provides a function to set the worksheet name by given the old and new 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.

Set sheet properties

func (f *File) SetSheetPrOptions(name string, opts ...SheetPrOption) error

SetSheetPrOptions provides a function to sets worksheet properties.

Available options:

Optional Attribute Type
CodeName string
EnableFormatConditionsCalculation bool
Published bool
FitToPage bool
TabColor string
AutoPageBreaks bool
OutlineSummaryBelow bool

For example:

f := excelize.NewFile()
const sheet = "Sheet1"

if err := f.SetSheetPrOptions(sheet,
    excelize.CodeName("code"),
    excelize.EnableFormatConditionsCalculation(false),
    excelize.Published(false),
    excelize.FitToPage(true),
    excelize.TabColor("#FFFF00"),
    excelize.AutoPageBreaks(true),
    excelize.OutlineSummaryBelow(false),
); err != nil {
    fmt.Println(err)
}

Get sheet properties

func (f *File) GetSheetPrOptions(name string, opts ...SheetPrOptionPtr) error

GetSheetPrOptions provides a function to gets worksheet properties.

Optional Attribute Type
CodeName string
EnableFormatConditionsCalculation bool
Published bool
FitToPage bool
TabColor string
AutoPageBreaks bool
OutlineSummaryBelow bool

For example:

f := excelize.NewFile()
const sheet = "Sheet1"

var (
    codeName                          excelize.CodeName
    enableFormatConditionsCalculation excelize.EnableFormatConditionsCalculation
    published                         excelize.Published
    fitToPage                         excelize.FitToPage
    tabColor                          excelize.TabColor
    autoPageBreaks                    excelize.AutoPageBreaks
    outlineSummaryBelow               excelize.OutlineSummaryBelow
)

if err := f.GetSheetPrOptions(sheet,
    &codeName,
    &enableFormatConditionsCalculation,
    &published,
    &fitToPage,
    &tabColor,
    &autoPageBreaks,
    &outlineSummaryBelow,
); err != nil {
    fmt.Println(err)
}
fmt.Println("Defaults:")
fmt.Printf("- codeName: %q\n", codeName)
fmt.Println("- enableFormatConditionsCalculation:", enableFormatConditionsCalculation)
fmt.Println("- published:", published)
fmt.Println("- fitToPage:", fitToPage)
fmt.Printf("- tabColor: %q\n", tabColor)
fmt.Println("- autoPageBreaks:", autoPageBreaks)
fmt.Println("- outlineSummaryBelow:", outlineSummaryBelow)

Output:

Defaults:
- codeName: ""
- enableFormatConditionsCalculation: true
- published: true
- fitToPage: false
- tabColor: ""
- autoPageBreaks: false
- outlineSummaryBelow: true

Insert column

func (f *File) InsertCol(sheet, column string) error

InsertCol provides a function to insert a new column before given column index. For example, create a new column before column C in Sheet1:

err := f.InsertCol("Sheet1", "C")

Insert row

func (f *File) InsertRow(sheet string, row int) error

InsertRow provides a function to insert a new row after given Excel row number starting from 1. For example, create a new row before row 3 in Sheet1:

err := f.InsertRow("Sheet1", 3)

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:

Create row outline

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:

Create column outline

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. 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. 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.

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 - 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 coordinates 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 coordinates of the upper left corner of the merged area.

For example, search the coordinates of the value of 100 on Sheet1:

result, err := f.SearchSheet("Sheet1", "100")

For example, search the coordinates 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, settings *FormatSheetProtection) 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:

Protect Sheet

err := f.ProtectSheet("Sheet1", &excelize.FormatSheetProtection{
    AlgorithmName: "SHA-512",
    Password:      "password",
    EditScenarios: false,
})

FormatSheetProtection directly maps the settings of worksheet protection.

type FormatSheetProtection 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 row values

func (f *File) SetSheetRow(sheet, axis string, slice interface{}) error

SetSheetRow writes an array to row by given worksheet name, starting coordinate and a pointer to array type slice. 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 axis, 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 axis.

results matching ""

    No results matching ""