Workbook

Options defines the options for reading and writing spreadsheets.

type Options struct {
    MaxCalcIterations uint
    Password          string
    RawCellValue      bool
    UnzipSizeLimit    int64
    UnzipXMLSizeLimit int64
    ShortDatePattern  string
    LongDatePattern   string
    LongTimePattern   string
    CultureInfo       CultureName
}

MaxCalcIterations specifies the maximum iterations for iterative calculation, the default value is 0.

Password specifies the password of the spreadsheet in plain text.

RawCellValue specifies if apply the number format for the cell value or get the raw value.

UnzipSizeLimit specifies the unzip size limit in bytes on open the spreadsheet, this value should be greater than or equal to UnzipXMLSizeLimit, the default size limit is 16GB.

UnzipXMLSizeLimit specifies the memory limit on unzipping worksheet and shared string table in bytes, worksheet XML will be extracted to system temporary directory when the file size is over this value, this value should be less than or equal to UnzipSizeLimit, the default value is 16MB.

ShortDatePattern specifies the short date number format code. In the spreadsheet applications, date formats display date and time serial numbers as date values. Date formats that begin with an asterisk (*) respond to changes in regional date and time settings that are specified for the operating system. Formats without an asterisk are not affected by operating system settings. The ShortDatePattern used for specifies apply date formats that begin with an asterisk.

LongDatePattern specifies the long date number format code.

LongTimePattern specifies the long time number format code.

CultureInfo specifies the country code for applying built-in language number format code these effect by the system's local language settings.

Create Excel document

func NewFile(opts ...Options) *File

NewFile provides a function to create new file by default template. The newly created workbook will by default contain a worksheet named Sheet1. For example:

Open

func OpenFile(filename string, opts ...Options) (*File, error)

OpenFile takes the name of a spreadsheet file and returns a populated spreadsheet file struct for it. For example, open a spreadsheet with password protection:

f, err := excelize.OpenFile("Book1.xlsx", excelize.Options{Password: "password"})
if err != nil {
    return
}

Close the file by Close() after opening the spreadsheet.

Open data stream

func OpenReader(r io.Reader, opts ...Options) (*File, error)

OpenReader read data stream from io.Reader and return a populated spreadsheet file.

For example, create HTTP server to handle upload template, then response download file with new worksheet added:

package main

import (
    "fmt"
    "net/http"

    "github.com/xuri/excelize/v2"
)

func process(w http.ResponseWriter, req *http.Request) {
    file, _, err := req.FormFile("file")
    if err != nil {
        fmt.Fprint(w, err.Error())
        return
    }
    defer file.Close()
    f, err := excelize.OpenReader(file)
    if err != nil {
        fmt.Fprint(w, err.Error())
        return
    }
    f.Path = "Book1.xlsx"
    f.NewSheet("NewSheet")
    w.Header().Set("Content-Disposition", fmt.Sprintf("attachment; filename=%s", f.Path))
    w.Header().Set("Content-Type", req.Header.Get("Content-Type"))
    if err := f.Write(w); err != nil {
        fmt.Fprint(w, err.Error())
    }
}

func main() {
    http.HandleFunc("/process", process)
    http.ListenAndServe(":8090", nil)
}

Test with cURL:

curl --location --request GET 'http://127.0.0.1:8090/process' \
--form 'file=@/tmp/template.xltx' -O -J

Save

func (f *File) Save(opts ...Options) error

Save provides a function to override the spreadsheet file with the origin path.

Save as

func (f *File) SaveAs(name string, opts ...Options) error

SaveAs provides a function to create or update the spreadsheet file at the provided path.

Close workbook

func (f *File) Close() error

Close closes and cleanup the open temporary file for the spreadsheet.

Create worksheet

func (f *File) NewSheet(sheet string) (int, error)

NewSheet provides the function to create a new sheet by given a worksheet name and returns the index of the sheets in the workbook (spreadsheet) after it appended. Note that when creating a new spreadsheet file, the default worksheet named Sheet1 will be created.

Delete worksheet

func (f *File) DeleteSheet(sheet string) error

DeleteSheet provides a function to delete worksheet in a workbook by given worksheet name. 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 deleted worksheet, it will cause a file error when you open it. This function will be invalid when only one worksheet is left.

Copy worksheet

func (f *File) CopySheet(from, to int) error

CopySheet provides a function to duplicate a worksheet by gave source and target worksheet index. Note that currently doesn't support duplicate workbooks that contain tables, charts or pictures. For example:

// Sheet1 already exists...
index, err := f.NewSheet("Sheet2")
if err != nil {
    fmt.Println(err)
    return
}
err := f.CopySheet(1, index)

Group worksheets

func (f *File) GroupSheets(sheets []string) error

GroupSheets provides a function to group worksheets by given worksheets names. Group worksheets must contain an active worksheet.

Ungroup worksheets

func (f *File) UngroupSheets() error

UngroupSheets provides a function to ungroup worksheets.

Set worksheet background

func (f *File) SetSheetBackground(sheet, picture string) error

SetSheetBackground provides a function to set background picture by given worksheet name and file path. Supported image types: BMP, EMF, EMZ, GIF, JPEG, JPG, PNG, SVG, TIF, TIFF, WMF, and WMZ.

func (f *File) SetSheetBackgroundFromBytes(sheet, extension string, picture []byte) error

SetSheetBackgroundFromBytes provides a function to set background picture by given worksheet name, extension name and image data. Supported image types: BMP, EMF, EMZ, GIF, JPEG, JPG, PNG, SVG, TIF, TIFF, WMF, and WMZ.

Set default worksheet

func (f *File) SetActiveSheet(index int)

SetActiveSheet provides a function to set the default active sheet of the workbook by a given index. Note that the active index is different from the ID returned by function GetSheetMap. It should be greater or equal to 0 and less than the total worksheet numbers.

Get active sheet index

func (f *File) GetActiveSheetIndex() int

GetActiveSheetIndex provides a function to get an active worksheet of the workbook. If not found the active sheet will return integer 0.

Set worksheet visible

func (f *File) SetSheetVisible(sheet string, visible bool, veryHidden ...bool) error

SetSheetVisible provides a function to set worksheet visible by given worksheet name. A workbook must contain at least one visible worksheet. If the given worksheet has been activated, this setting will be invalidated. The third optional veryHidden parameter only works when visible was false.

For example, hide Sheet1:

err := f.SetSheetVisible("Sheet1", false)

Get worksheet visible

func (f *File) GetSheetVisible(sheet string) (bool, error)

GetSheetVisible provides a function to get worksheet visible by given worksheet name. For example, get the visible state of Sheet1:

visible, err := f.GetSheetVisible("Sheet1")

Set worksheet properties

func (f *File) SetSheetProps(sheet string, opts *SheetPropsOptions) error

SetSheetProps provides a function to set worksheet properties. The properties that can be set are:

For example, make worksheet rows default as hidden:

f, enable := excelize.NewFile(), true
if err := f.SetSheetProps("Sheet1", &excelize.SheetPropsOptions{
    ZeroHeight: &enable,
}); err != nil {
    fmt.Println(err)
}
if err := f.SetRowVisible("Sheet1", 10, true); err != nil {
    fmt.Println(err)
}
f.SaveAs("Book1.xlsx")

Get worksheet properties

func (f *File) GetSheetProps(sheet string) (SheetPropsOptions, error)

GetSheetProps provides a function to get worksheet properties.

Set worksheet view properties

func (f *File) SetSheetView(sheet string, viewIndex int, opts *ViewOptions) error

SetSheetView sets sheet view properties. The viewIndex may be negative and if so is counted backward (-1 is the last view). The properties that can be set are:

Get worksheet view properties

func (f *File) GetSheetView(sheet string, viewIndex int) (ViewOptions, error)

GetSheetView gets the value of sheet view properties. The viewIndex may be negative and if so is counted backward (-1 is the last view).

Set worksheet page layout

func (f *File) SetPageLayout(sheet string, opts *PageLayoutOptions) error

SetPageLayout provides a function to sets worksheet page layout. Available options:

Size specified the worksheet paper size, the default paper size of worksheet is "Letter paper (8.5 in. by 11 in.)". The following shows the paper size sorted by Excelize index number:

Orientation specified worksheet orientation, the default orientation is portrait. The possible values for this field is portrait and landscape.

FirstPageNumber specified the first printed page number. If no value is specified, then "automatic" is assumed.

AdjustTo specified the print scaling. This attribute is restricted to values ranging from 10 (10%) to 400 (400%). This setting is overridden when FitToWidth and/or FitToHeight are in use.

FitToHeight specified the number of vertical pages to fit on.

FitToWidth specified the number of horizontal pages to fit on.

BlackAndWhite specified print black and white.

For example, set page layout for Sheet1 with print black and white, first printed page number from 2, landscape A4 small paper (210 mm by 297 mm), 2 vertical pages to fit on, and 2 horizontal pages to fit:

f := excelize.NewFile()
var (
    size                 = 10
    orientation          = "landscape"
    firstPageNumber uint = 2
    adjustTo        uint = 100
    fitToHeight          = 2
    fitToWidth           = 2
    blackAndWhite        = true
)
if err := f.SetPageLayout("Sheet1", &excelize.PageLayoutOptions{
    Size:            &size,
    Orientation:     &orientation,
    FirstPageNumber: &firstPageNumber,
    AdjustTo:        &adjustTo,
    FitToHeight:     &fitToHeight,
    FitToWidth:      &fitToWidth,
    BlackAndWhite:   &blackAndWhite,
}); err != nil {
    fmt.Println(err)
}

Get worksheet page layout

func (f *File) GetPageLayout(sheet string) (PageLayoutOptions, error)

GetPageLayout provides a function to gets worksheet page layout.

Set worksheet page margins

func (f *File) SetPageMargins(sheet string, opts *PageLayoutMarginsOptions) error

SetPageMargins provides a function to set worksheet page margins. Available options:

Get worksheet page margins

func (f *File) GetPageMargins(sheet string) (PageLayoutMarginsOptions, error)

GetPageMargins provides a function to get worksheet page margins.

Set workbook properties

func (f *File) SetWorkbookProps(opts *WorkbookPropsOptions) error

SetWorkbookProps provides a function to sets workbook properties. Available options:

Get workbook properties

func (f *File) GetWorkbookProps() (WorkbookPropsOptions, error)

GetWorkbookProps provides a function to gets workbook properties.

Set header and footer

func (f *File) SetHeaderFooter(sheet string, opts *HeaderFooterOptions) error

SetHeaderFooter provides a function to set headers and footers by given worksheet name and the control characters.

Headers and footers are specified using the following settings fields:

The following formatting codes can be used in 6 string type fields: OddHeader, OddFooter, EvenHeader, EvenFooter, FirstFooter, FirstHeader

For example:

err := f.SetHeaderFooter("Sheet1", &excelize.HeaderFooterOptions{
    DifferentFirst:   true,
    DifferentOddEven: true,
    OddHeader:        "&R&P",
    OddFooter:        "&C&F",
    EvenHeader:       "&L&P",
    EvenFooter:       "&L&D&R&T",
    FirstHeader:      `&CCenter &"-,Bold"Bold&"-,Regular"HeaderU+000A&D`,
})

This example shows:

• The first page has its own header and footer
• Odd and even-numbered pages have different headers and footers
• Current page number in the right section of odd-page headers
• Current workbook's file name in the center section of odd-page footers
• Current page number in the left section of even-page headers
• Current date in the left section and the current time in the right section of even-page footers
• The text "Center Bold Header" on the first line of the center section of the first page, and the date on the second line of the center section of that same page
• No footer on the first page

Set defined name

func (f *File) SetDefinedName(definedName *DefinedName) error

SetDefinedName provides a function to set the defined names of the workbook or worksheet. If not specified scope, the default scope is the workbook. For example:

err := f.SetDefinedName(&excelize.DefinedName{
    Name:     "Amount",
    RefersTo: "Sheet1!$A$2:$D$5",
    Comment:  "defined name comment",
    Scope:    "Sheet2",
})

Print area and print titles settings for the worksheet:

if err := f.SetDefinedName(&excelize.DefinedName{
    Name:     "_xlnm.Print_Area",
    RefersTo: "Sheet1!$A$1:$Z$100",
    Scope:    "Sheet1",
}); err != nil {
    fmt.Println(err)
}
if err := f.SetDefinedName(&excelize.DefinedName{
    Name:     "_xlnm.Print_Titles",
    RefersTo: "Sheet1!$A:$A,Sheet1!$1:$1",
    Scope:    "Sheet1",
}); err != nil {
    fmt.Println(err)
}

If you fill the RefersTo property with only one columns range without a comma, it will work as "Columns to repeat at left" only. For example:

if err := f.SetDefinedName(&excelize.DefinedName{
    Name:     "_xlnm.Print_Titles",
    RefersTo: "Sheet1!$A:$A",
    Scope:    "Sheet1",
}); err != nil {
    fmt.Println(err)
}

If you fill the RefersTo property with only one rows range without a comma, it will work as "Rows to repeat at top" only. For example:

if err := f.SetDefinedName(&excelize.DefinedName{
    Name:     "_xlnm.Print_Titles",
    RefersTo: "Sheet1!$1:$1",
    Scope:    "Sheet1",
}); err != nil {
    fmt.Println(err)
}

Get defined name

func (f *File) GetDefinedName() []DefinedName

GetDefinedName provides a function to get the defined names of the workbook or worksheet.

Delete defined name

func (f *File) DeleteDefinedName(definedName *DefinedName) error

DeleteDefinedName provides a function to delete the defined names of the workbook or worksheet. If not specified scope, the default scope is workbook. For example:

err := f.DeleteDefinedName(&excelize.DefinedName{
    Name:     "Amount",
    Scope:    "Sheet2",
})

Set application properties

func (f *File) SetAppProps(appProperties *AppProperties) error

SetAppProps provides a function to set document application properties. The properties that can be set are:

For example:

err := f.SetAppProps(&excelize.AppProperties{
    Application:       "Microsoft Excel",
    ScaleCrop:         true,
    DocSecurity:       3,
    Company:           "Company Name",
    LinksUpToDate:     true,
    HyperlinksChanged: true,
    AppVersion:        "16.0000",
})

Get application properties

func (f *File) GetAppProps() (*AppProperties, error)

GetAppProps provides a function to get document application properties.

Set document properties

func (f *File) SetDocProps(docProperties *DocProperties) error

SetDocProps provides a function to set document core properties. The properties that can be set are:

For example:

err := f.SetDocProps(&excelize.DocProperties{
    Category:       "category",
    ContentStatus:  "Draft",
    Created:        "2019-06-04T22:00:10Z",
    Creator:        "Go Excelize",
    Description:    "This file created by Go Excelize",
    Identifier:     "xlsx",
    Keywords:       "Spreadsheet",
    LastModifiedBy: "Go Author",
    Modified:       "2019-06-04T22:00:10Z",
    Revision:       "0",
    Subject:        "Test Subject",
    Title:          "Test Title",
    Language:       "en-US",
    Version:        "1.0.0",
})

Get document properties

func (f *File) GetDocProps() (*DocProperties, error)

GetDocProps provides a function to get document core properties.

Protect workbook

func (f *File) ProtectWorkbook(opts *WorkbookProtectionOptions) error

ProtectWorkbook provides a function to prevent other users from accidentally or deliberately changing, moving, or deleting data in a workbook. The optional field AlgorithmName specified hash algorithm, support XOR, MD4, MD5, SHA-1, SHA2-56, SHA-384, and SHA-512 currently, if no hash algorithm specified, will be using the XOR algorithm as default. For example, protect workbook with protection settings:

err := f.ProtectWorkbook(&excelize.WorkbookProtectionOptions{
    Password:      "password",
    LockStructure: true,
})

WorkbookProtectionOptions directly maps the settings of workbook protection.

type WorkbookProtectionOptions struct {
    AlgorithmName string
    Password      string
    LockStructure bool
    LockWindows   bool
}

Unprotect workbook

func (f *File) UnprotectWorkbook(password ...string) error

UnprotectWorkbook provides a function to remove protection for workbook, specified the optional password parameter to remove workbook protection with password verification.