Go Excelize Workbook

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 is used to specify the maximum number of iterations when calculating formulas, with a default value of 0.

Password is used to specify the password for opening and saving the workbook in plain text, with a default value of empty.

RawCellValue is used to specify whether to retrieve the raw value when reading cell values, with a default value of false (applying number format).

UnzipSizeLimit is used to specify the size limit (in bytes) for decompression when opening a spreadsheet document, which should be greater than or equal to UnzipXMLSizeLimit, with a default limit of 16GB.

UnzipXMLSizeLimit is used to specify the memory limit (in bytes) when decompressing each worksheet and shared string table, and when the size exceeds this value, the worksheet XML file will be decompressed to the system temporary directory. This value should be less than or equal to UnzipSizeLimit, with a default limit of 16MB.

ShortDatePattern is used to specify the short date numeric format code.

LongDatePattern is used to specify the long date numeric format code.

LongTimePattern is used to specify the long time numeric format code.

CultureInfo is used to specify the regional format, which will be used when reading numeric formats influenced by region-specific date and time settings.

Create

func NewFile(opts ...Options) *File

Use NewFile to create a new Excel workbook, which will defaultly contain a worksheet named Sheet1.

Open

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

Use OpenFile to open an existing Excel document. For example, open a spreadsheet document with password protection:

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

Use Close() to close the opened workbook.

Open Data Stream

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

OpenReader reads data from io.Reader.

In the following example, we create a simple HTTP server to receive uploaded spreadsheet documents, add a new worksheet to the received document, and return a download response:

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)
}

Testing using 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

Use Save to save edits to an Excel document.

Save As

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

Use SaveAs to save the Excel document as a specified file.

Close Workbook

func (f *File) Close() error

Close the workbook and clean up any system disk cache that may have been created when opening the document.

New Worksheet

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

Create a new worksheet based on the given worksheet name and return the index of the worksheet in the workbook. Please note that when creating a new workbook, it will contain a default worksheet named Sheet1.

Delete Worksheet

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

Delete the specified worksheet based on the given worksheet name. Use this method with caution, as it will affect formulas, references, charts, and other elements associated with the deleted worksheet. If other components reference values on the deleted worksheet, an error will be prompted, and it may even cause the workbook to fail to open. Calling this method is invalid when the workbook contains only one worksheet.

Copy Worksheet

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

Copy the worksheet based on the given source worksheet and target worksheet index. The developer needs to confirm whether the target worksheet index already exists. Currently, only copying of worksheets containing cell values and formulas is supported, while copying of worksheets containing tables, images, charts, and pivot tables is not supported.

// The worksheet named 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

Group the worksheets based on the given worksheet names, and the default worksheet must be included in the given worksheets.

Ungroup Worksheets

func (f *File) UngroupSheets() error

Ungroup the worksheets.

Set Worksheet Background Image

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

Set a tiled background image for the specified worksheet based on the given worksheet name and image file path. Supported image file formats include: BMP, EMF, EMZ, GIF, JPEG, JPG, PNG, SVG, TIF, TIFF, WMF, and WMZ.

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

Set a tiled background image for the specified worksheet based on the given worksheet name, image format extension, and image format data. Supported image file formats include: BMP, EMF, EMZ, GIF, JPEG, JPG, PNG, SVG, TIF, TIFF, WMF, and WMZ.

Set Default Worksheet

func (f *File) SetActiveSheet(index int)

Set the default worksheet based on the given index value, where the value should be greater than or equal to 0 and less than the cumulative total number of worksheets in the workbook.

Get Default Worksheet Index

func (f *File) GetActiveSheetIndex() int

Get the index of the default worksheet, returning 0 if no default worksheet is found.

Set Worksheet Visibility

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

Set the visibility of the worksheet based on the given worksheet name and visibility parameter. A workbook must contain at least one visible worksheet. If the given worksheet is the default worksheet, the visibility setting is invalid. The third optional parameter veryHidden is only valid when the visible parameter value is false.

For example, to hide the worksheet named Sheet1:

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

Get Worksheet Visibility

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

Get the visibility setting of the worksheet based on the given worksheet name. For example, to retrieve the visibility setting of the worksheet named Sheet1:

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

Set Sheet Properties

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

Set the properties of a specified worksheet based on the given worksheet name and property options. The supported worksheet property options are as follows:

For example, to set the default row in the worksheet named Sheet1 to be 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 Sheet Properties

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

Get the properties of a specified worksheet based on the given worksheet name.

Set Sheet View Properties

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

Set the sheet view properties based on the given sheet name, view index, and view options. viewIndex can be a negative number, in which case counting will be done backwards (-1 represents the last view). The supported options for setting sheet view properties are as follows:

Get Sheet View Properties

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

Retrieve the sheet view properties based on the given sheet name and view index. viewIndex can be a negative number, in which case counting will be done backwards (-1 represents the last view).

Set Worksheet Page Layout

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

Set the page layout attributes of the worksheet based on the given worksheet name and page layout parameters. Currently supported page layout attributes include:

The Size attribute is used to specify the page paper size, with the default page layout size being “Letter 8½ × 11 inches”. The following table shows the relationship between the page layout sizes and the index Size parameter in Excelize:

| 41 | German legal paper fanfold 8.5 × 13 inches |
| 42 | ISO B4 250 × 353 mm |
| 43 | Japanese postcard 100 × 148 mm |
| 44 | Standard paper 9 × 11 inches |
| 45 | Standard paper 10 × 11 inches |
| 46 | Standard paper 15 × 11 inches |
| 47 | Invitation letter 220 × 220 mm |
| 50 | Letterhead enlarged 9.275 × 12 inches |
| 51 | Extra large legal paper 9.275 × 15 inches |
| 52 | Tabloid extra paper 11.69 × 18 inches |
| 53 | A4 extra large 236 × 322 mm |
| 54 | Letterhead landscape 8.275 × 11 inches |
| 55 | A4 landscape 210 × 297 mm |
| 56 | Letterhead extra large landscape 9.275 × 12 inches |
| 57 | SuperA/SuperA/A4 paper 227 × 356 mm |
| 58 | SuperB/SuperB/A3 paper 305 × 487 mm |
| 59 | Letterhead enlarged 8.5 × 12.69 inches |
| 60 | A4 enlarged 210 × 330 mm |
| 61 | A5 landscape 148 × 210 mm |
| 62 | JIS B5 landscape 182 × 257 mm |
| 63 | A3 extra large 322 × 445 mm |
| 64 | A5 extra large 174 × 235 mm |
| 65 | ISO B5 extra large 201 × 276 mm |
| 66 | A2 420 × 594 mm |
| 67 | A3 landscape 297 × 420 mm |
| 68 | A3 extra large landscape 322 × 445 mm |
| 69 | Double Japanese postcard 200 × 148 mm |
| 70 | A6 105 × 148 mm |
| 71 | Japanese envelope Kaku #2 |
| 72 | Japanese envelope Kaku #3 |
| 73 | Japanese envelope Chou #3 |
| 74 | Japanese envelope Chou #4 |
| 75 | Letterhead landscape 11 × 8½ inches |
| 76 | A3 landscape 420 × 297 mm |
| 77 | A4 landscape 297 × 210 mm |
| 78 | A5 landscape 210 × 148 mm |
| 79 | B4 (JIS) landscape 364 × 257 mm |
| 80 | B5 (JIS) landscape 257 × 182 mm |
| 81 | Japanese postcard landscape 148 × 100 mm |
| 82 | Double Japanese postcard landscape 148 × 200 mm |
| 83 | A6 landscape 148 × 105 mm |
| 84 | Japanese envelope Kaku #2 landscape |
| 85 | Japanese envelope Kaku #3 landscape |
| 86 | Japanese envelope Chou #3 landscape |

| 87 | Japanese Envelope Chou #4 Landscape |
| 88 | B6 (JIS) 128 × 182 mm |
| 89 | B6 (JIS) Landscape 182 × 128 mm |
| 90 | 12 × 11 inches |
| 91 | Japanese Envelope You #4 |
| 92 | Japanese Envelope You #4 Landscape |
| 93 | Chinese 16K 146 × 215 mm |
| 94 | Chinese 32K 97 × 151 mm |
| 95 | Chinese Large 32K 97 × 151 mm |
| 96 | Chinese Envelope #1 102 × 165 mm |
| 97 | Chinese Envelope #2 102 × 176 mm |
| 98 | Chinese Envelope #3 125 × 176 mm |
| 99 | Chinese Envelope #4 110 × 208 mm |
| 100 | Chinese Envelope #5 110 × 220 mm |
| 101 | Chinese Envelope #6 120 × 230 mm |
| 102 | Chinese Envelope #7 160 × 230 mm |
| 103 | Chinese Envelope #8 120 × 309 mm |
| 104 | Chinese Envelope #9 229 × 324 mm |
| 105 | Chinese Envelope #10 324 × 458 mm |
| 106 | Chinese 16K Landscape |
| 107 | Chinese 32K Landscape |
| 108 | Chinese Large 32K Landscape |
| 109 | Chinese Envelope #1 Landscape 165 × 102 mm |
| 110 | Chinese Envelope #2 Landscape 176 × 102 mm |
| 111 | Chinese Envelope #3 Landscape 176 × 125 mm |
| 112 | Chinese Envelope #4 Landscape 208 × 110 mm |
| 113 | Chinese Envelope #5 Landscape 220 × 110 mm |
| 114 | Chinese Envelope #6 Landscape 230 × 120 mm |
| 115 | Chinese Envelope #7 Landscape 230 × 160 mm |
| 116 | Chinese Envelope #8 Landscape 309 × 120 mm |
| 117 | Chinese Envelope #9 Landscape 324 × 229 mm |
| 118 | Chinese Envelope #10 Landscape 458 × 324 mm |

The `Orientation` property is used to specify the page layout direction, with the default page layout direction being "portrait", and the optional values are `portrait` and `landscape`.

The `FirstPageNumber` property is used to specify the starting page number, with the default being automatic.

The `AdjustTo` property is used to specify the page scaling ratio, with a range of 10 to 400, i.e., scaling from 10% to 400%, and the default value is `100` for normal size. Setting `FitToHeight` or `FitToWidth` will override this property.

The `FitToHeight` property is used to specify the page scaling to adjust the page width, with a default value of `1`.

The `FitToWidth` property is used to specify the page scaling to adjust the page height, with a default value of `1`.

The `BlackAndWhite` property is used to specify monochrome printing, with the default being off.

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)

Obtain the page layout properties of the worksheet based on the given worksheet name and page layout parameters.

Set Worksheet Page Margins

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

Set the page margins of the worksheet based on the given worksheet name and margin parameters. The supported margin options are:

Get Worksheet Page Margins

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

Obtain the page margins of the worksheet based on the given worksheet name and margin parameters.

Set Workbook Properties

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

SetWorkbookProps is used to set the properties of the workbook. The supported workbook properties are:

Get Workbook Properties

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

GetWorkbookProps is used to obtain the properties of the workbook.

Set Header and Footer

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

Set the header and footer of the worksheet based on the given worksheet name and control characters.

The header and footer include the following fields:

The format codes in the table below can be used for the 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`,
})

The example above contains the following format:

• The first page has its own header and footer
• Odd and even pages have different headers and footers
• The right part of the odd page header is the current page number
• The center part of the odd page footer is the file name of the current workbook
• The left part is the current page number in the even page header
• The left part is the current date, and the right part of the even page footer is the current time
• The text on the first row in the center part of the first page is “Center Bold Header”, and the second row is the date
• There is no footer on the first page

  Set Name

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

Set the name and reference range based on the given name, with the default scope being the workbook. For example:

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

Setting the print area and print titles of 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)
}

Get Name

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

Get the list of names of the workbook and worksheet within the scope.

Delete Name

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

Delete the defined name based on the given name and name scope, with the default scope being the workbook. For example:

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

Set Workbook Application Properties

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

Set the application properties of the workbook. The properties that can be set include:

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 Workbook Application Properties

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

Get the application properties of the workbook.

Set document properties

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

Set the core properties of the workbook. The properties that can be set include:

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)

Get the core properties of the workbook.

Protect the workbook

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

Protect the structure of the workbook with a password to prevent other users from viewing hidden worksheets, adding, moving, or hiding worksheets, and renaming worksheets. The AlgorithmName field supports specifying hash algorithms XOR, MD4, MD5, SHA-1, SHA-256, SHA-384, or SHA-512. If no hash algorithm is specified, the XOR algorithm is used by default. For example, protect the structure of the workbook with a password:

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

WorkbookProtectionOptions defines the options for protecting the workbook.

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

Unprotect the workbook

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

Unprotect the workbook, specifying an optional password parameter to verify and remove the protection of the workbook.