excelize

package module
v2.7.2 Latest Latest
Warning

This package is not in the latest version of its module.

Go to latest
Published: Aug 25, 2023 License: BSD-3-Clause Imports: 48 Imported by: 0

README

Excelize logo

Build Status Code Coverage Go Report Card go.dev Licenses Donate

Excelize

Introduction

Excelize is a library written in pure Go providing a set of functions that allow you to write to and read from XLAM / XLSM / XLSX / XLTM / XLTX files. Supports reading and writing spreadsheet documents generated by Microsoft Excel™ 2007 and later. Supports complex components by high compatibility, and provided streaming API for generating or reading data from a worksheet with huge amounts of data. This library needs Go version 1.16 or later. The full docs can be seen using go's built-in documentation tool, or online at go.dev and docs reference.

Basic Usage

Installation
go get github.com/xuri/excelize
  • If your packages are managed using Go Modules, please install with following command.
go get github.com/xuri/excelize/v2
Create spreadsheet

Here is a minimal example usage that will create spreadsheet file.

package main

import (
    "fmt"

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

func main() {
    f := excelize.NewFile()
    defer func() {
        if err := f.Close(); err != nil {
            fmt.Println(err)
        }
    }()
    // Create a new sheet.
    index, err := f.NewSheet("Sheet2")
    if err != nil {
        fmt.Println(err)
        return
    }
    // Set value of a cell.
    f.SetCellValue("Sheet2", "A2", "Hello world.")
    f.SetCellValue("Sheet1", "B2", 100)
    // Set active sheet of the workbook.
    f.SetActiveSheet(index)
    // Save spreadsheet by the given path.
    if err := f.SaveAs("Book1.xlsx"); err != nil {
        fmt.Println(err)
    }
}
Reading spreadsheet

The following constitutes the bare to read a spreadsheet document.

package main

import (
    "fmt"

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

func main() {
    f, err := excelize.OpenFile("Book1.xlsx")
    if err != nil {
        fmt.Println(err)
        return
    }
    defer func() {
        // Close the spreadsheet.
        if err := f.Close(); err != nil {
            fmt.Println(err)
        }
    }()
    // Get value from cell by given worksheet name and cell reference.
    cell, err := f.GetCellValue("Sheet1", "B2")
    if err != nil {
        fmt.Println(err)
        return
    }
    fmt.Println(cell)
    // Get all the rows in the Sheet1.
    rows, err := f.GetRows("Sheet1")
    if err != nil {
        fmt.Println(err)
        return
    }
    for _, row := range rows {
        for _, colCell := range row {
            fmt.Print(colCell, "\t")
        }
        fmt.Println()
    }
}
Add chart to spreadsheet file

With Excelize chart generation and management is as easy as a few lines of code. You can build charts based on data in your worksheet or generate charts without any data in your worksheet at all.

Excelize

package main

import (
    "fmt"

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

func main() {
    f := excelize.NewFile()
    defer func() {
        if err := f.Close(); err != nil {
            fmt.Println(err)
        }
    }()
    for idx, row := range [][]interface{}{
        {nil, "Apple", "Orange", "Pear"}, {"Small", 2, 3, 3},
        {"Normal", 5, 2, 4}, {"Large", 6, 7, 8},
    } {
        cell, err := excelize.CoordinatesToCellName(1, idx+1)
        if err != nil {
            fmt.Println(err)
            return
        }
        f.SetSheetRow("Sheet1", cell, &row)
    }
    if err := f.AddChart("Sheet1", "E1", &excelize.Chart{
        Type: excelize.Col3DClustered,
        Series: []excelize.ChartSeries{
            {
                Name:       "Sheet1!$A$2",
                Categories: "Sheet1!$B$1:$D$1",
                Values:     "Sheet1!$B$2:$D$2",
            },
            {
                Name:       "Sheet1!$A$3",
                Categories: "Sheet1!$B$1:$D$1",
                Values:     "Sheet1!$B$3:$D$3",
            },
            {
                Name:       "Sheet1!$A$4",
                Categories: "Sheet1!$B$1:$D$1",
                Values:     "Sheet1!$B$4:$D$4",
            }},
        Title: []excelize.RichTextRun{
            {
                Text: "Fruit 3D Clustered Column Chart",
            },
        },
    }); err != nil {
        fmt.Println(err)
        return
    }
    // Save spreadsheet by the given path.
    if err := f.SaveAs("Book1.xlsx"); err != nil {
        fmt.Println(err)
    }
}
Add picture to spreadsheet file
package main

import (
    "fmt"
    _ "image/gif"
    _ "image/jpeg"
    _ "image/png"

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

func main() {
    f, err := excelize.OpenFile("Book1.xlsx")
    if err != nil {
        fmt.Println(err)
        return
    }
    defer func() {
        // Close the spreadsheet.
        if err := f.Close(); err != nil {
            fmt.Println(err)
        }
    }()
    // Insert a picture.
    if err := f.AddPicture("Sheet1", "A2", "image.png", nil); err != nil {
        fmt.Println(err)
    }
    // Insert a picture to worksheet with scaling.
    if err := f.AddPicture("Sheet1", "D2", "image.jpg",
        &excelize.GraphicOptions{ScaleX: 0.5, ScaleY: 0.5}); err != nil {
        fmt.Println(err)
    }
    // Insert a picture offset in the cell with printing support.
    enable, disable := true, false
    if err := f.AddPicture("Sheet1", "H2", "image.gif",
        &excelize.GraphicOptions{
            PrintObject:     &enable,
            LockAspectRatio: false,
            OffsetX:         15,
            OffsetY:         10,
            Locked:          &disable,
        }); err != nil {
        fmt.Println(err)
    }
    // Save the spreadsheet with the origin path.
    if err = f.Save(); err != nil {
        fmt.Println(err)
    }
}

Contributing

Contributions are welcome! Open a pull request to fix a bug, or open an issue to discuss a new feature or change. XML is compliant with part 1 of the 5th edition of the ECMA-376 Standard for Office Open XML.

Licenses

This program is under the terms of the BSD 3-Clause License. See https://opensource.org/licenses/BSD-3-Clause.

The Excel logo is a trademark of Microsoft Corporation. This artwork is an adaptation.

gopher.{ai,svg,png} was created by Takuya Ueda. Licensed under the Creative Commons 3.0 Attributions license.

Documentation

Overview

Package excelize providing a set of functions that allow you to write to and read from XLAM / XLSM / XLSX / XLTM / XLTX files. Supports reading and writing spreadsheet documents generated by Microsoft Excel™ 2007 and later. Supports complex components by high compatibility, and provided streaming API for generating or reading data from a worksheet with huge amounts of data. This library needs Go version 1.16 or later.

See https://xuri.me/excelize for more information about this package.

Index

Examples

Constants

View Source
const (
	// STCellFormulaTypeArray defined the formula is an array formula.
	STCellFormulaTypeArray = "array"
	// STCellFormulaTypeDataTable defined the formula is a data table formula.
	STCellFormulaTypeDataTable = "dataTable"
	// STCellFormulaTypeNormal defined the formula is a regular cell formula.
	STCellFormulaTypeNormal = "normal"
	// STCellFormulaTypeShared defined the formula is part of a shared formula.
	STCellFormulaTypeShared = "shared"
)
View Source
const (
	DataValidationTypeCustom
	DataValidationTypeDate
	DataValidationTypeDecimal

	DataValidationTypeTextLength
	DataValidationTypeTime
	// DataValidationTypeWhole Integer
	DataValidationTypeWhole
)

Data validation types.

View Source
const (
	DataValidationOperatorBetween
	DataValidationOperatorEqual
	DataValidationOperatorGreaterThan
	DataValidationOperatorGreaterThanOrEqual
	DataValidationOperatorLessThan
	DataValidationOperatorLessThanOrEqual
	DataValidationOperatorNotBetween
	DataValidationOperatorNotEqual
)

Data validation operators.

View Source
const (
	ContentTypeAddinMacro                         = "application/vnd.ms-excel.addin.macroEnabled.main+xml"
	ContentTypeDrawing                            = "application/vnd.openxmlformats-officedocument.drawing+xml"
	ContentTypeDrawingML                          = "application/vnd.openxmlformats-officedocument.drawingml.chart+xml"
	ContentTypeMacro                              = "application/vnd.ms-excel.sheet.macroEnabled.main+xml"
	ContentTypeSheetML                            = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet.main+xml"
	ContentTypeSpreadSheetMLChartsheet            = "application/vnd.openxmlformats-officedocument.spreadsheetml.chartsheet+xml"
	ContentTypeSpreadSheetMLComments              = "application/vnd.openxmlformats-officedocument.spreadsheetml.comments+xml"
	ContentTypeSpreadSheetMLPivotCacheDefinition  = "application/vnd.openxmlformats-officedocument.spreadsheetml.pivotCacheDefinition+xml"
	ContentTypeSpreadSheetMLPivotTable            = "application/vnd.openxmlformats-officedocument.spreadsheetml.pivotTable+xml"
	ContentTypeSpreadSheetMLSharedStrings         = "application/vnd.openxmlformats-officedocument.spreadsheetml.sharedStrings+xml"
	ContentTypeSpreadSheetMLTable                 = "application/vnd.openxmlformats-officedocument.spreadsheetml.table+xml"
	ContentTypeSpreadSheetMLWorksheet             = "application/vnd.openxmlformats-officedocument.spreadsheetml.worksheet+xml"
	ContentTypeTemplate                           = "application/vnd.openxmlformats-officedocument.spreadsheetml.template.main+xml"
	ContentTypeTemplateMacro                      = "application/vnd.ms-excel.template.macroEnabled.main+xml"
	ContentTypeVBA                                = "application/vnd.ms-office.vbaProject"
	ContentTypeVML                                = "application/vnd.openxmlformats-officedocument.vmlDrawing"
	NameSpaceDrawingMLMain                        = "http://schemas.openxmlformats.org/drawingml/2006/main"
	NameSpaceDublinCore                           = "http://purl.org/dc/elements/1.1/"
	NameSpaceDublinCoreMetadataInitiative         = "http://purl.org/dc/dcmitype/"
	NameSpaceDublinCoreTerms                      = "http://purl.org/dc/terms/"
	NameSpaceExtendedProperties                   = "http://schemas.openxmlformats.org/officeDocument/2006/extended-properties"
	NameSpaceXML                                  = "http://www.w3.org/XML/1998/namespace"
	NameSpaceXMLSchemaInstance                    = "http://www.w3.org/2001/XMLSchema-instance"
	SourceRelationshipChart                       = "http://schemas.openxmlformats.org/officeDocument/2006/relationships/chart"
	SourceRelationshipChartsheet                  = "http://schemas.openxmlformats.org/officeDocument/2006/relationships/chartsheet"
	SourceRelationshipComments                    = "http://schemas.openxmlformats.org/officeDocument/2006/relationships/comments"
	SourceRelationshipDialogsheet                 = "http://schemas.openxmlformats.org/officeDocument/2006/relationships/dialogsheet"
	SourceRelationshipDrawingML                   = "http://schemas.openxmlformats.org/officeDocument/2006/relationships/drawing"
	SourceRelationshipDrawingVML                  = "http://schemas.openxmlformats.org/officeDocument/2006/relationships/vmlDrawing"
	SourceRelationshipExtendProperties            = "http://schemas.openxmlformats.org/officeDocument/2006/relationships/extended-properties"
	SourceRelationshipHyperLink                   = "http://schemas.openxmlformats.org/officeDocument/2006/relationships/hyperlink"
	SourceRelationshipImage                       = "http://schemas.openxmlformats.org/officeDocument/2006/relationships/image"
	SourceRelationshipOfficeDocument              = "http://schemas.openxmlformats.org/officeDocument/2006/relationships/officeDocument"
	SourceRelationshipPivotCache                  = "http://schemas.openxmlformats.org/officeDocument/2006/relationships/pivotCacheDefinition"
	SourceRelationshipPivotTable                  = "http://schemas.openxmlformats.org/officeDocument/2006/relationships/pivotTable"
	SourceRelationshipSharedStrings               = "http://schemas.openxmlformats.org/officeDocument/2006/relationships/sharedStrings"
	SourceRelationshipTable                       = "http://schemas.openxmlformats.org/officeDocument/2006/relationships/table"
	SourceRelationshipVBAProject                  = "http://schemas.microsoft.com/office/2006/relationships/vbaProject"
	SourceRelationshipWorkSheet                   = "http://schemas.openxmlformats.org/officeDocument/2006/relationships/worksheet"
	StrictNameSpaceDocumentPropertiesVariantTypes = "http://purl.oclc.org/ooxml/officeDocument/docPropsVTypes"
	StrictNameSpaceDrawingMLMain                  = "http://purl.oclc.org/ooxml/drawingml/main"
	StrictNameSpaceExtendedProperties             = "http://purl.oclc.org/ooxml/officeDocument/extendedProperties"
	StrictNameSpaceSpreadSheet                    = "http://purl.oclc.org/ooxml/spreadsheetml/main"
	StrictSourceRelationship                      = "http://purl.oclc.org/ooxml/officeDocument/relationships"
	StrictSourceRelationshipChart                 = "http://purl.oclc.org/ooxml/officeDocument/relationships/chart"
	StrictSourceRelationshipComments              = "http://purl.oclc.org/ooxml/officeDocument/relationships/comments"
	StrictSourceRelationshipExtendProperties      = "http://purl.oclc.org/ooxml/officeDocument/relationships/extendedProperties"
	StrictSourceRelationshipImage                 = "http://purl.oclc.org/ooxml/officeDocument/relationships/image"
	StrictSourceRelationshipOfficeDocument        = "http://purl.oclc.org/ooxml/officeDocument/relationships/officeDocument"
	// ExtURIConditionalFormattings is the extLst child element
	// ([ISO/IEC29500-1:2016] section 18.2.10) of the worksheet element
	// ([ISO/IEC29500-1:2016] section 18.3.1.99) is extended by the addition of
	// new child ext elements ([ISO/IEC29500-1:2016] section 18.2.7)
	ExtURIConditionalFormattingRuleID = "{B025F937-C7B1-47D3-B67F-A62EFF666E3E}"
	ExtURIConditionalFormattings      = "{78C0D931-6437-407d-A8EE-F0AAD7539E65}"
	ExtURIDataValidations             = "{CCE6A557-97BC-4B89-ADB6-D9C93CAAB3DF}"
	ExtURIDrawingBlip                 = "{28A0092B-C50C-407E-A947-70E740481C1C}"
	ExtURIIgnoredErrors               = "{01252117-D84E-4E92-8308-4BE1C098FCBB}"
	ExtURIMacExcelMX                  = "{64002731-A6B0-56B0-2670-7721B7C09600}"
	ExtURIProtectedRanges             = "{FC87AEE6-9EDD-4A0A-B7FB-166176984837}"
	ExtURISlicerCachesListX14         = "{BBE1A952-AA13-448e-AADC-164F8A28A991}"
	ExtURISlicerListX14               = "{A8765BA9-456A-4DAB-B4F3-ACF838C121DE}"
	ExtURISlicerListX15               = "{3A4CF648-6AED-40f4-86FF-DC5316D8AED3}"
	ExtURISparklineGroups             = "{05C60535-1F16-4fd2-B633-F4F36F0B64E0}"
	ExtURISVG                         = "{96DAC541-7B7A-43D3-8B79-37D633B846F1}"
	ExtURITimelineRefs                = "{7E03D99C-DC04-49d9-9315-930204A7B6E9}"
	ExtURIWebExtensions               = "{F7C9EE02-42E1-4005-9D12-6889AFFD525C}"
)

Source relationship and namespace.

View Source
const (
	MaxCellStyles        = 65430
	MaxColumns           = 16384
	MaxColumnWidth       = 255
	MaxFieldLength       = 255
	MaxFilePathLength    = 2047
	MaxFormControlValue  = 30000
	MaxFontFamilyLength  = 31
	MaxFontSize          = 409
	MaxRowHeight         = 409
	MaxSheetNameLength   = 31
	MinColumns           = 1
	MinFontSize          = 1
	StreamChunkSize      = 1 << 24
	TotalCellChars       = 32767
	TotalRows            = 1048576
	TotalSheetHyperlinks = 65529
	UnzipSizeLimit       = 1000 << 24
)

Excel specifications and limits

View Source
const (
	ColorMappingTypeLight1 ColorMappingType = iota
	ColorMappingTypeDark1
	ColorMappingTypeLight2
	ColorMappingTypeDark2
	ColorMappingTypeAccent1
	ColorMappingTypeAccent2
	ColorMappingTypeAccent3
	ColorMappingTypeAccent4
	ColorMappingTypeAccent5
	ColorMappingTypeAccent6
	ColorMappingTypeHyperlink
	ColorMappingTypeFollowedHyperlink
	ColorMappingTypeUnset int = -1
)

Color transformation types enumeration.

View Source
const (
	EMU int = 9525
)

Define the default cell size and EMU unit of measurement.

Variables

View Source
var (
	// ErrStreamSetColWidth defined the error message on set column width in
	// stream writing mode.
	ErrStreamSetColWidth = errors.New("must call the SetColWidth function before the SetRow function")
	// ErrStreamSetPanes defined the error message on set panes in stream
	// writing mode.
	ErrStreamSetPanes = errors.New("must call the SetPanes function before the SetRow function")
	// ErrColumnNumber defined the error message on receive an invalid column
	// number.
	ErrColumnNumber = fmt.Errorf(`the column number must be greater than or equal to %d and less than or equal to %d`, MinColumns, MaxColumns)
	// ErrColumnWidth defined the error message on receive an invalid column
	// width.
	ErrColumnWidth = fmt.Errorf("the width of the column must be less than or equal to %d characters", MaxColumnWidth)
	// ErrOutlineLevel defined the error message on receive an invalid outline
	// level number.
	ErrOutlineLevel = errors.New("invalid outline level")
	// ErrCoordinates defined the error message on invalid coordinates tuples
	// length.
	ErrCoordinates = errors.New("coordinates length must be 4")
	// ErrExistsSheet defined the error message on given sheet already exists.
	ErrExistsSheet = errors.New("the same name sheet already exists")
	// ErrTotalSheetHyperlinks defined the error message on hyperlinks count
	// overflow.
	ErrTotalSheetHyperlinks = errors.New("over maximum limit hyperlinks in a worksheet")
	// ErrInvalidFormula defined the error message on receive an invalid
	// formula.
	ErrInvalidFormula = errors.New("formula not valid")
	// ErrAddVBAProject defined the error message on add the VBA project in
	// the workbook.
	ErrAddVBAProject = errors.New("unsupported VBA project")
	// ErrMaxRows defined the error message on receive a row number exceeds maximum limit.
	ErrMaxRows = errors.New("row number exceeds maximum limit")
	// ErrMaxRowHeight defined the error message on receive an invalid row
	// height.
	ErrMaxRowHeight = fmt.Errorf("the height of the row must be less than or equal to %d points", MaxRowHeight)
	// ErrImgExt defined the error message on receive an unsupported image
	// extension.
	ErrImgExt = errors.New("unsupported image extension")
	// ErrWorkbookFileFormat defined the error message on receive an
	// unsupported workbook file format.
	ErrWorkbookFileFormat = errors.New("unsupported workbook file format")
	// ErrMaxFilePathLength defined the error message on receive the file path
	// length overflow.
	ErrMaxFilePathLength = fmt.Errorf("file path length exceeds maximum limit %d characters", MaxFilePathLength)
	// ErrUnknownEncryptMechanism defined the error message on unsupported
	// encryption mechanism.
	ErrUnknownEncryptMechanism = errors.New("unknown encryption mechanism")
	// ErrUnsupportedEncryptMechanism defined the error message on unsupported
	// encryption mechanism.
	ErrUnsupportedEncryptMechanism = errors.New("unsupported encryption mechanism")
	// ErrUnsupportedHashAlgorithm defined the error message on unsupported
	// hash algorithm.
	ErrUnsupportedHashAlgorithm = errors.New("unsupported hash algorithm")
	// ErrUnsupportedNumberFormat defined the error message on unsupported number format
	// expression.
	ErrUnsupportedNumberFormat = errors.New("unsupported number format token")
	// ErrPasswordLengthInvalid defined the error message on invalid password
	// length.
	ErrPasswordLengthInvalid = errors.New("password length invalid")
	// ErrParameterRequired defined the error message on receive the empty
	// parameter.
	ErrParameterRequired = errors.New("parameter is required")
	// ErrParameterInvalid defined the error message on receive the invalid
	// parameter.
	ErrParameterInvalid = errors.New("parameter is invalid")
	// ErrDefinedNameScope defined the error message on not found defined name
	// in the given scope.
	ErrDefinedNameScope = errors.New("no defined name on the scope")
	// ErrDefinedNameDuplicate defined the error message on the same name
	// already exists on the scope.
	ErrDefinedNameDuplicate = errors.New("the same name already exists on the scope")
	// ErrCustomNumFmt defined the error message on receive the empty custom number format.
	ErrCustomNumFmt = errors.New("custom number format can not be empty")
	// ErrFontLength defined the error message on the length of the font
	// family name overflow.
	ErrFontLength = fmt.Errorf("the length of the font family name must be less than or equal to %d", MaxFontFamilyLength)
	// ErrFontSize defined the error message on the size of the font is invalid.
	ErrFontSize = fmt.Errorf("font size must be between %d and %d points", MinFontSize, MaxFontSize)
	// ErrSheetIdx defined the error message on receive the invalid worksheet
	// index.
	ErrSheetIdx = errors.New("invalid worksheet index")
	// ErrUnprotectSheet defined the error message on worksheet has set no
	// protection.
	ErrUnprotectSheet = errors.New("worksheet has set no protect")
	// ErrUnprotectSheetPassword defined the error message on remove sheet
	// protection with password verification failed.
	ErrUnprotectSheetPassword = errors.New("worksheet protect password not match")
	// ErrGroupSheets defined the error message on group sheets.
	ErrGroupSheets = errors.New("group worksheet must contain an active worksheet")
	// ErrDataValidationFormulaLength defined the error message for receiving a
	// data validation formula length that exceeds the limit.
	ErrDataValidationFormulaLength = fmt.Errorf("data validation must be 0-%d characters", MaxFieldLength)
	// ErrDataValidationRange defined the error message on set decimal range
	// exceeds limit.
	ErrDataValidationRange = errors.New("data validation range exceeds limit")
	// ErrCellCharsLength defined the error message for receiving a cell
	// characters length that exceeds the limit.
	ErrCellCharsLength = fmt.Errorf("cell value must be 0-%d characters", TotalCellChars)
	// ErrOptionsUnzipSizeLimit defined the error message for receiving
	// invalid UnzipSizeLimit and UnzipXMLSizeLimit.
	ErrOptionsUnzipSizeLimit = errors.New("the value of UnzipSizeLimit should be greater than or equal to UnzipXMLSizeLimit")
	// ErrSave defined the error message for saving file.
	ErrSave = errors.New("no path defined for file, consider File.WriteTo or File.Write")
	// ErrAttrValBool defined the error message on marshal and unmarshal
	// boolean type XML attribute.
	ErrAttrValBool = errors.New("unexpected child of attrValBool")
	// ErrSparklineType defined the error message on receive the invalid
	// sparkline Type parameters.
	ErrSparklineType = errors.New("parameter 'Type' must be 'line', 'column' or 'win_loss'")
	// ErrSparklineLocation defined the error message on missing Location
	// parameters
	ErrSparklineLocation = errors.New("parameter 'Location' is required")
	// ErrSparklineRange defined the error message on missing sparkline Range
	// parameters
	ErrSparklineRange = errors.New("parameter 'Range' is required")
	// ErrSparkline defined the error message on receive the invalid sparkline
	// parameters.
	ErrSparkline = errors.New("must have the same number of 'Location' and 'Range' parameters")
	// ErrSparklineStyle defined the error message on receive the invalid
	// sparkline Style parameters.
	ErrSparklineStyle = errors.New("parameter 'Style' must between 0-35")
	// ErrWorkbookPassword defined the error message on receiving the incorrect
	// workbook password.
	ErrWorkbookPassword = errors.New("the supplied open workbook password is not correct")
	// ErrSheetNameInvalid defined the error message on receive the sheet name
	// contains invalid characters.
	ErrSheetNameInvalid = errors.New("the sheet can not contain any of the characters :\\/?*[or]")
	// ErrSheetNameSingleQuote defined the error message on the first or last
	// character of the sheet name was a single quote.
	ErrSheetNameSingleQuote = errors.New("the first or last character of the sheet name can not be a single quote")
	// ErrSheetNameBlank defined the error message on receive the blank sheet
	// name.
	ErrSheetNameBlank = errors.New("the sheet name can not be blank")
	// ErrSheetNameLength defined the error message on receiving the sheet
	// name length exceeds the limit.
	ErrSheetNameLength = fmt.Errorf("the sheet name length exceeds the %d characters limit", MaxSheetNameLength)
	// ErrNameLength defined the error message on receiving the defined name or
	// table name length exceeds the limit.
	ErrNameLength = fmt.Errorf("the name length exceeds the %d characters limit", MaxFieldLength)
	// ErrExistsTableName defined the error message on given table already exists.
	ErrExistsTableName = errors.New("the same name table already exists")
	// ErrCellStyles defined the error message on cell styles exceeds the limit.
	ErrCellStyles = fmt.Errorf("the cell styles exceeds the %d limit", MaxCellStyles)
	// ErrUnprotectWorkbook defined the error message on workbook has set no
	// protection.
	ErrUnprotectWorkbook = errors.New("workbook has set no protect")
	// ErrUnprotectWorkbookPassword defined the error message on remove workbook
	// protection with password verification failed.
	ErrUnprotectWorkbookPassword = errors.New("workbook protect password not match")
	// ErrorFormControlValue defined the error message for receiving a scroll
	// value exceeds limit.
	ErrorFormControlValue = fmt.Errorf("scroll value must be between 0 and %d", MaxFormControlValue)
)
View Source
var (
	NameSpaceDocumentPropertiesVariantTypes = xml.Attr{Name: xml.Name{Local: "vt", Space: "xmlns"}, Value: "http://schemas.openxmlformats.org/officeDocument/2006/docPropsVTypes"}
	NameSpaceDrawing2016SVG                 = xml.Attr{Name: xml.Name{Local: "asvg", Space: "xmlns"}, Value: "http://schemas.microsoft.com/office/drawing/2016/SVG/main"}
	NameSpaceDrawingML                      = xml.Attr{Name: xml.Name{Local: "a", Space: "xmlns"}, Value: "http://schemas.openxmlformats.org/drawingml/2006/main"}
	NameSpaceDrawingMLChart                 = xml.Attr{Name: xml.Name{Local: "c", Space: "xmlns"}, Value: "http://schemas.openxmlformats.org/drawingml/2006/chart"}
	NameSpaceDrawingMLSpreadSheet           = xml.Attr{Name: xml.Name{Local: "xdr", Space: "xmlns"}, Value: "http://schemas.openxmlformats.org/drawingml/2006/spreadsheetDrawing"}
	NameSpaceMacExcel2008Main               = xml.Attr{Name: xml.Name{Local: "mx", Space: "xmlns"}, Value: "http://schemas.microsoft.com/office/mac/excel/2008/main"}
	NameSpaceSpreadSheet                    = xml.Attr{Name: xml.Name{Local: "xmlns"}, Value: "http://schemas.openxmlformats.org/spreadsheetml/2006/main"}
	NameSpaceSpreadSheetExcel2006Main       = xml.Attr{Name: xml.Name{Local: "xne", Space: "xmlns"}, Value: "http://schemas.microsoft.com/office/excel/2006/main"}
	NameSpaceSpreadSheetX14                 = xml.Attr{Name: xml.Name{Local: "x14", Space: "xmlns"}, Value: "http://schemas.microsoft.com/office/spreadsheetml/2009/9/main"}
	NameSpaceSpreadSheetX15                 = xml.Attr{Name: xml.Name{Local: "x15", Space: "xmlns"}, Value: "http://schemas.microsoft.com/office/spreadsheetml/2010/11/main"}
	SourceRelationship                      = xml.Attr{Name: xml.Name{Local: "r", Space: "xmlns"}, Value: "http://schemas.openxmlformats.org/officeDocument/2006/relationships"}
	SourceRelationshipChart20070802         = xml.Attr{Name: xml.Name{Local: "c14", Space: "xmlns"}, Value: "http://schemas.microsoft.com/office/drawing/2007/8/2/chart"}
	SourceRelationshipChart2014             = xml.Attr{Name: xml.Name{Local: "c16", Space: "xmlns"}, Value: "http://schemas.microsoft.com/office/drawing/2014/chart"}
	SourceRelationshipChart201506           = xml.Attr{Name: xml.Name{Local: "c16r2", Space: "xmlns"}, Value: "http://schemas.microsoft.com/office/drawing/2015/06/chart"}
	SourceRelationshipCompatibility         = xml.Attr{Name: xml.Name{Local: "mc", Space: "xmlns"}, Value: "http://schemas.openxmlformats.org/markup-compatibility/2006"}
)

Source relationship and namespace list, associated prefixes and schema in which it was introduced.

View Source
var HSLModel = color.ModelFunc(hslModel)

HSLModel converts any color.Color to a HSL color.

View Source
var IndexedColorMapping = []string{
	"000000", "FFFFFF", "FF0000", "00FF00", "0000FF", "FFFF00", "FF00FF", "00FFFF",
	"000000", "FFFFFF", "FF0000", "00FF00", "0000FF", "FFFF00", "FF00FF", "00FFFF",
	"800000", "008000", "000080", "808000", "800080", "008080", "C0C0C0", "808080",
	"9999FF", "993366", "FFFFCC", "CCFFFF", "660066", "FF8080", "0066CC", "CCCCFF",
	"000080", "FF00FF", "FFFF00", "00FFFF", "800080", "800000", "008080", "0000FF",
	"00CCFF", "CCFFFF", "CCFFCC", "FFFF99", "99CCFF", "FF99CC", "CC99FF", "FFCC99",
	"3366FF", "33CCCC", "99CC00", "FFCC00", "FF9900", "FF6600", "666699", "969696",
	"003366", "339966", "003300", "333300", "993300", "993366", "333399", "333333",
	"000000", "FFFFFF",
}

IndexedColorMapping is the table of default mappings from indexed color value to RGB value. Note that 0-7 are redundant of 8-15 to preserve backwards compatibility. A legacy indexing scheme for colors that is still required for some records, and for backwards compatibility with legacy formats. This element contains a sequence of RGB color values that correspond to color indexes (zero-based). When using the default indexed color palette, the values are not written out, but instead are implied. When the color palette has been modified from default, then the entire color palette is written out.

Functions

func CellNameToCoordinates

func CellNameToCoordinates(cell string) (int, int, error)

CellNameToCoordinates converts alphanumeric cell name to [X, Y] coordinates or returns an error.

Example:

excelize.CellNameToCoordinates("A1") // returns 1, 1, nil
excelize.CellNameToCoordinates("Z3") // returns 26, 3, nil

func ColumnNameToNumber

func ColumnNameToNumber(name string) (int, error)

ColumnNameToNumber provides a function to convert Excel sheet column name (case-insensitive) to int. The function returns an error if column name incorrect.

Example:

excelize.ColumnNameToNumber("AK") // returns 37, nil

func ColumnNumberToName

func ColumnNumberToName(num int) (string, error)

ColumnNumberToName provides a function to convert the integer to Excel sheet column title.

Example:

excelize.ColumnNumberToName(37) // returns "AK", nil

func CoordinatesToCellName

func CoordinatesToCellName(col, row int, abs ...bool) (string, error)

CoordinatesToCellName converts [X, Y] coordinates to alpha-numeric cell name or returns an error.

Example:

excelize.CoordinatesToCellName(1, 1) // returns "A1", nil
excelize.CoordinatesToCellName(1, 1, true) // returns "$A$1", nil

func Decrypt

func Decrypt(raw []byte, opts *Options) (packageBuf []byte, err error)

Decrypt API decrypts the CFB file format with ECMA-376 agile encryption and standard encryption. Support cryptographic algorithm: MD4, MD5, RIPEMD-160, SHA1, SHA256, SHA384 and SHA512 currently.

func Encrypt

func Encrypt(raw []byte, opts *Options) ([]byte, error)

Encrypt API encrypt data with the password.

func ExcelDateToTime

func ExcelDateToTime(excelDate float64, use1904Format bool) (time.Time, error)

ExcelDateToTime converts a float-based Excel date representation to a time.Time.

func HSLToRGB

func HSLToRGB(h, s, l float64) (r, g, b uint8)

HSLToRGB converts an HSL triple to an RGB triple.

func JoinCellName

func JoinCellName(col string, row int) (string, error)

JoinCellName joins cell name from column name and row number.

func RGBToHSL

func RGBToHSL(r, g, b uint8) (h, s, l float64)

RGBToHSL converts an RGB triple to an HSL triple.

func SplitCellName

func SplitCellName(cell string) (string, int, error)

SplitCellName splits cell name to column name and row number.

Example:

excelize.SplitCellName("AK74") // return "AK", 74, nil

func ThemeColor

func ThemeColor(baseColor string, tint float64) string

ThemeColor applied the color with tint value.

Types

type Alignment

type Alignment struct {
	Horizontal      string
	Indent          int
	JustifyLastLine bool
	ReadingOrder    uint64
	RelativeIndent  int
	ShrinkToFit     bool
	TextRotation    int
	Vertical        string
	WrapText        bool
}

Alignment directly maps the alignment settings of the cells.

type AppProperties

type AppProperties struct {
	Application       string
	ScaleCrop         bool
	DocSecurity       int
	Company           string
	LinksUpToDate     bool
	HyperlinksChanged bool
	AppVersion        string
}

AppProperties directly maps the document application properties.

type ArgType

type ArgType byte

ArgType is the type of formula argument type.

const (
	ArgUnknown ArgType = iota
	ArgNumber
	ArgString
	ArgList
	ArgMatrix
	ArgError
	ArgEmpty
)

Formula argument types enumeration.

type AutoFilterOptions

type AutoFilterOptions struct {
	Column     string
	Expression string
}

AutoFilterOptions directly maps the auto filter settings.

type Border

type Border struct {
	Type  string
	Color string
	Style int
}

Border directly maps the border settings of the cells.

type Cell

type Cell struct {
	StyleID int
	Formula string
	Value   interface{}
}

Cell can be used directly in StreamWriter.SetRow to specify a style and a value.

type CellType

type CellType byte

CellType is the type of cell value type.

const (
	CellTypeUnset CellType = iota
	CellTypeBool
	CellTypeDate
	CellTypeError
	CellTypeFormula
	CellTypeInlineString
	CellTypeNumber
	CellTypeSharedString
)

Cell value types enumeration.

type Chart

type Chart struct {
	Type         ChartType
	Series       []ChartSeries
	Format       GraphicOptions
	Dimension    ChartDimension
	Legend       ChartLegend
	Title        []RichTextRun
	VaryColors   *bool
	XAxis        ChartAxis
	YAxis        ChartAxis
	PlotArea     ChartPlotArea
	ShowBlanksAs string
	HoleSize     int
	// contains filtered or unexported fields
}

Chart directly maps the format settings of the chart.

type ChartAxis

type ChartAxis struct {
	None           bool
	MajorGridLines bool
	MinorGridLines bool
	MajorUnit      float64
	TickLabelSkip  int
	ReverseOrder   bool
	Secondary      bool
	Maximum        *float64
	Minimum        *float64
	Font           Font
	LogBase        float64
	NumFmt         ChartNumFmt
	Title          []RichTextRun
	// contains filtered or unexported fields
}

ChartAxis directly maps the format settings of the chart axis.

type ChartDimension

type ChartDimension struct {
	Width  uint
	Height uint
}

ChartDimension directly maps the dimension of the chart.

type ChartLegend

type ChartLegend struct {
	Position      string
	ShowLegendKey bool
}

ChartLegend directly maps the format settings of the chart legend.

type ChartLine

type ChartLine struct {
	Smooth bool
	Width  float64
}

ChartLine directly maps the format settings of the chart line.

type ChartMarker

type ChartMarker struct {
	Symbol string
	Size   int
}

ChartMarker directly maps the format settings of the chart marker.

type ChartNumFmt

type ChartNumFmt struct {
	CustomNumFmt string
	SourceLinked bool
}

ChartNumFmt directly maps the number format settings of the chart.

type ChartPlotArea

type ChartPlotArea struct {
	SecondPlotValues int
	ShowBubbleSize   bool
	ShowCatName      bool
	ShowLeaderLines  bool
	ShowPercent      bool
	ShowSerName      bool
	ShowVal          bool
	NumFmt           ChartNumFmt
}

ChartPlotArea directly maps the format settings of the plot area.

type ChartSeries

type ChartSeries struct {
	Name       string
	Categories string
	Sizes      string
	Values     string
	Fill       Fill
	Line       ChartLine
	Marker     ChartMarker
}

ChartSeries directly maps the format settings of the chart series.

type ChartType

type ChartType byte

ChartType is the type of supported chart types.

const (
	Area ChartType = iota
	AreaStacked
	AreaPercentStacked
	Area3D
	Area3DStacked
	Area3DPercentStacked
	Bar
	BarStacked
	BarPercentStacked
	Bar3DClustered
	Bar3DStacked
	Bar3DPercentStacked
	Bar3DConeClustered
	Bar3DConeStacked
	Bar3DConePercentStacked
	Bar3DPyramidClustered
	Bar3DPyramidStacked
	Bar3DPyramidPercentStacked
	Bar3DCylinderClustered
	Bar3DCylinderStacked
	Bar3DCylinderPercentStacked
	Col
	ColStacked
	ColPercentStacked
	Col3D
	Col3DClustered
	Col3DStacked
	Col3DPercentStacked
	Col3DCone
	Col3DConeClustered
	Col3DConeStacked
	Col3DConePercentStacked
	Col3DPyramid
	Col3DPyramidClustered
	Col3DPyramidStacked
	Col3DPyramidPercentStacked
	Col3DCylinder
	Col3DCylinderClustered
	Col3DCylinderStacked
	Col3DCylinderPercentStacked
	Doughnut
	Line
	Line3D
	Pie
	Pie3D
	PieOfPie
	BarOfPie
	Radar
	Scatter
	Surface3D
	WireframeSurface3D
	Contour
	WireframeContour
	Bubble
	Bubble3D
)

This section defines the currently supported chart types enumeration.

type ColorMappingType

type ColorMappingType byte

ColorMappingType is the type of color transformation.

type Cols

type Cols struct {
	// contains filtered or unexported fields
}

Cols defines an iterator to a sheet

func (*Cols) Error

func (cols *Cols) Error() error

Error will return an error when the error occurs.

func (*Cols) Next

func (cols *Cols) Next() bool

Next will return true if the next column is found.

func (*Cols) Rows

func (cols *Cols) Rows(opts ...Options) ([]string, error)

Rows return the current column's row values.

type Comment

type Comment struct {
	Author    string
	AuthorID  int
	Cell      string
	Text      string
	Paragraph []RichTextRun
}

Comment directly maps the comment information.

type ConditionalFormatOptions

type ConditionalFormatOptions struct {
	Type           string
	AboveAverage   bool
	Percent        bool
	Format         int
	Criteria       string
	Value          string
	MinType        string
	MidType        string
	MaxType        string
	MinValue       string
	MidValue       string
	MaxValue       string
	MinColor       string
	MidColor       string
	MaxColor       string
	BarColor       string
	BarBorderColor string
	BarDirection   string
	BarOnly        bool
	BarSolid       bool
	IconStyle      string
	ReverseIcons   bool
	IconsOnly      bool
	StopIfTrue     bool
}

ConditionalFormatOptions directly maps the conditional format settings of the cells.

type CultureName

type CultureName byte

CultureName is the type of supported language country codes types for apply number format.

const (
	CultureNameUnknown CultureName = iota
	CultureNameEnUS
	CultureNameZhCN
)

This section defines the currently supported country code types enumeration for apply number format.

type DataIntegrity

type DataIntegrity struct {
	EncryptedHmacKey   string `xml:"encryptedHmacKey,attr"`
	EncryptedHmacValue string `xml:"encryptedHmacValue,attr"`
}

DataIntegrity specifies the encrypted copies of the salt and hash values used to help ensure that the integrity of the encrypted data has not been compromised.

type DataValidation

type DataValidation struct {
	AllowBlank       bool    `xml:"allowBlank,attr"`
	Error            *string `xml:"error,attr"`
	ErrorStyle       *string `xml:"errorStyle,attr"`
	ErrorTitle       *string `xml:"errorTitle,attr"`
	Operator         string  `xml:"operator,attr,omitempty"`
	Prompt           *string `xml:"prompt,attr"`
	PromptTitle      *string `xml:"promptTitle,attr"`
	ShowDropDown     bool    `xml:"showDropDown,attr,omitempty"`
	ShowErrorMessage bool    `xml:"showErrorMessage,attr,omitempty"`
	ShowInputMessage bool    `xml:"showInputMessage,attr,omitempty"`
	Sqref            string  `xml:"sqref,attr"`
	Type             string  `xml:"type,attr,omitempty"`
	Formula1         string  `xml:",innerxml"`
	Formula2         string  `xml:",innerxml"`
}

DataValidation directly maps the single item of data validation defined on a range of the worksheet.

func NewDataValidation

func NewDataValidation(allowBlank bool) *DataValidation

NewDataValidation return data validation struct.

func (*DataValidation) SetDropList

func (dv *DataValidation) SetDropList(keys []string) error

SetDropList data validation list.

func (*DataValidation) SetError

func (dv *DataValidation) SetError(style DataValidationErrorStyle, title, msg string)

SetError set error notice.

func (*DataValidation) SetInput

func (dv *DataValidation) SetInput(title, msg string)

SetInput set prompt notice.

func (*DataValidation) SetRange

func (dv *DataValidation) SetRange(f1, f2 interface{}, t DataValidationType, o DataValidationOperator) error

SetRange provides function to set data validation range in drop list, only accepts int, float64, or string data type formula argument.

func (*DataValidation) SetSqref

func (dv *DataValidation) SetSqref(sqref string)

SetSqref provides function to set data validation range in drop list.

func (*DataValidation) SetSqrefDropList

func (dv *DataValidation) SetSqrefDropList(sqref string)

SetSqrefDropList provides set data validation on a range with source reference range of the worksheet by given data validation object and worksheet name. The data validation object can be created by NewDataValidation function. For example, set data validation on Sheet1!A7:B8 with validation criteria source Sheet1!E1:E3 settings, create in-cell dropdown by allowing list source:

dv := excelize.NewDataValidation(true)
dv.Sqref = "A7:B8"
dv.SetSqrefDropList("$E$1:$E$3")
err := f.AddDataValidation("Sheet1", dv)

type DataValidationErrorStyle

type DataValidationErrorStyle int

DataValidationErrorStyle defined the style of data validation error alert.

const (
	DataValidationErrorStyleStop DataValidationErrorStyle
	DataValidationErrorStyleWarning
	DataValidationErrorStyleInformation
)

Data validation error styles.

type DataValidationOperator

type DataValidationOperator int

DataValidationOperator operator enum.

type DataValidationType

type DataValidationType int

DataValidationType defined the type of data validation.

type DefinedName

type DefinedName struct {
	Name     string
	Comment  string
	RefersTo string
	Scope    string
}

DefinedName directly maps the name for a cell or cell range on a worksheet.

type DocProperties

type DocProperties struct {
	Category       string
	ContentStatus  string
	Created        string
	Creator        string
	Description    string
	Identifier     string
	Keywords       string
	LastModifiedBy string
	Modified       string
	Revision       string
	Subject        string
	Title          string
	Language       string
	Version        string
}

DocProperties directly maps the document core properties.

type EncryptedKey

type EncryptedKey struct {
	XMLName                    xml.Name `xml:"http://schemas.microsoft.com/office/2006/keyEncryptor/password encryptedKey"`
	SpinCount                  int      `xml:"spinCount,attr"`
	EncryptedVerifierHashInput string   `xml:"encryptedVerifierHashInput,attr"`
	EncryptedVerifierHashValue string   `xml:"encryptedVerifierHashValue,attr"`
	EncryptedKeyValue          string   `xml:"encryptedKeyValue,attr"`
	KeyData
}

EncryptedKey used to generate the encrypting key.

type Encryption

type Encryption struct {
	XMLName       xml.Name      `xml:"encryption"`
	KeyData       KeyData       `xml:"keyData"`
	DataIntegrity DataIntegrity `xml:"dataIntegrity"`
	KeyEncryptors KeyEncryptors `xml:"keyEncryptors"`
}

Encryption specifies the encryption structure, streams, and storages are required when encrypting ECMA-376 documents.

type ErrSheetNotExist

type ErrSheetNotExist struct {
	SheetName string
}

ErrSheetNotExist defines an error of sheet that does not exist

func (ErrSheetNotExist) Error

func (err ErrSheetNotExist) Error() string

type File

type File struct {
	CalcChain        *xlsxCalcChain
	Comments         map[string]*xlsxComments
	ContentTypes     *xlsxTypes
	Drawings         sync.Map
	Path             string
	SharedStrings    *xlsxSST
	Sheet            sync.Map
	SheetCount       int
	Styles           *xlsxStyleSheet
	Theme            *xlsxTheme
	DecodeVMLDrawing map[string]*decodeVmlDrawing
	VMLDrawing       map[string]*vmlDrawing
	WorkBook         *xlsxWorkbook
	Relationships    sync.Map
	Pkg              sync.Map
	CharsetReader    charsetTranscoderFn
	// contains filtered or unexported fields
}

File define a populated spreadsheet file struct.

func NewFile

func NewFile(opts ...Options) *File

NewFile provides a function to create new file by default template. For example:

f := NewFile()

func OpenFile

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

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

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

Close the file by Close function after opening the spreadsheet.

func OpenReader

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

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

func (*File) AddChart

func (f *File) AddChart(sheet, cell string, chart *Chart, combo ...*Chart) error

AddChart provides the method to add chart in a sheet by given chart format set (such as offset, scale, aspect ratio setting and print settings) and properties set. For example, create 3D clustered column chart with data Sheet1!$E$1:$L$15:

package main

import (
    "fmt"

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

func main() {
    f := excelize.NewFile()
    defer func() {
        if err := f.Close(); err != nil {
            fmt.Println(err)
        }
    }()
    for idx, row := range [][]interface{}{
        {nil, "Apple", "Orange", "Pear"}, {"Small", 2, 3, 3},
        {"Normal", 5, 2, 4}, {"Large", 6, 7, 8},
    } {
        cell, err := excelize.CoordinatesToCellName(1, idx+1)
        if err != nil {
            fmt.Println(err)
            return
        }
        f.SetSheetRow("Sheet1", cell, &row)
    }
    if err := f.AddChart("Sheet1", "E1", &excelize.Chart{
        Type: excelize.Col3DClustered,
        Series: []excelize.ChartSeries{
            {
                Name:       "Sheet1!$A$2",
                Categories: "Sheet1!$B$1:$D$1",
                Values:     "Sheet1!$B$2:$D$2",
            },
            {
                Name:       "Sheet1!$A$3",
                Categories: "Sheet1!$B$1:$D$1",
                Values:     "Sheet1!$B$3:$D$3",
            },
            {
                Name:       "Sheet1!$A$4",
                Categories: "Sheet1!$B$1:$D$1",
                Values:     "Sheet1!$B$4:$D$4",
            },
        },
        Title: []excelize.RichTextRun{
            {
                Text: "Fruit 3D Clustered Column Chart",
            },
        },
        Legend: excelize.ChartLegend{
            ShowLegendKey: false,
        },
        PlotArea: excelize.ChartPlotArea{
            ShowBubbleSize:  true,
            ShowCatName:     false,
            ShowLeaderLines: false,
            ShowPercent:     true,
            ShowSerName:     true,
            ShowVal:         true,
        },
    }); err != nil {
        fmt.Println(err)
        return
    }
    // Save spreadsheet by the given path.
    if err := f.SaveAs("Book1.xlsx"); err != nil {
        fmt.Println(err)
    }
}

The following shows the type of chart supported by excelize:

 ID | Enumeration                 | Chart
----+-----------------------------+------------------------------
 0  | Area                        | 2D area chart
 1  | AreaStacked                 | 2D stacked area chart
 2  | AreaPercentStacked          | 2D 100% stacked area chart
 3  | Area3D                      | 3D area chart
 4  | Area3DStacked               | 3D stacked area chart
 5  | Area3DPercentStacked        | 3D 100% stacked area chart
 6  | Bar                         | 2D clustered bar chart
 7  | BarStacked                  | 2D stacked bar chart
 8  | BarPercentStacked           | 2D 100% stacked bar chart
 9  | Bar3DClustered              | 3D clustered bar chart
 10 | Bar3DStacked                | 3D stacked bar chart
 11 | Bar3DPercentStacked         | 3D 100% stacked bar chart
 12 | Bar3DConeClustered          | 3D cone clustered bar chart
 13 | Bar3DConeStacked            | 3D cone stacked bar chart
 14 | Bar3DConePercentStacked     | 3D cone percent bar chart
 15 | Bar3DPyramidClustered       | 3D pyramid clustered bar chart
 16 | Bar3DPyramidStacked         | 3D pyramid stacked bar chart
 17 | Bar3DPyramidPercentStacked  | 3D pyramid percent stacked bar chart
 18 | Bar3DCylinderClustered      | 3D cylinder clustered bar chart
 19 | Bar3DCylinderStacked        | 3D cylinder stacked bar chart
 20 | Bar3DCylinderPercentStacked | 3D cylinder percent stacked bar chart
 21 | Col                         | 2D clustered column chart
 22 | ColStacked                  | 2D stacked column chart
 23 | ColPercentStacked           | 2D 100% stacked column chart
 24 | Col3DClustered              | 3D clustered column chart
 25 | Col3D                       | 3D column chart
 26 | Col3DStacked                | 3D stacked column chart
 27 | Col3DPercentStacked         | 3D 100% stacked column chart
 28 | Col3DCone                   | 3D cone column chart
 29 | Col3DConeClustered          | 3D cone clustered column chart
 30 | Col3DConeStacked            | 3D cone stacked column chart
 31 | Col3DConePercentStacked     | 3D cone percent stacked column chart
 32 | Col3DPyramid                | 3D pyramid column chart
 33 | Col3DPyramidClustered       | 3D pyramid clustered column chart
 34 | Col3DPyramidStacked         | 3D pyramid stacked column chart
 35 | Col3DPyramidPercentStacked  | 3D pyramid percent stacked column chart
 36 | Col3DCylinder               | 3D cylinder column chart
 37 | Col3DCylinderClustered      | 3D cylinder clustered column chart
 38 | Col3DCylinderStacked        | 3D cylinder stacked column chart
 39 | Col3DCylinderPercentStacked | 3D cylinder percent stacked column chart
 40 | Doughnut                    | doughnut chart
 41 | Line                        | line chart
 42 | Line3D                      | 3D line chart
 43 | Pie                         | pie chart
 44 | Pie3D                       | 3D pie chart
 45 | PieOfPie                    | pie of pie chart
 46 | BarOfPie                    | bar of pie chart
 47 | Radar                       | radar chart
 48 | Scatter                     | scatter chart
 49 | Surface3D                   | 3D surface chart
 50 | WireframeSurface3D          | 3D wireframe surface chart
 51 | Contour                     | contour chart
 52 | WireframeContour            | wireframe contour chart
 53 | Bubble                      | bubble chart
 54 | Bubble3D                    | 3D bubble chart

In Excel a chart series is a collection of information that defines which data is plotted such as values, axis labels and formatting.

The series options that can be set are:

Name
Categories
Sizes
Values
Fill
Line
Marker

Name: Set the name for the series. The name is displayed in the chart legend and in the formula bar. The 'Name' property is optional and if it isn't supplied it will default to Series 1..n. The name can also be a formula such as Sheet1!$A$1

Categories: This sets the chart category labels. The category is more or less the same as the X axis. In most chart types the 'Categories' property is optional and the chart will just assume a sequential series from 1..n.

Sizes: This sets the bubble size in a data series.

Values: This is the most important property of a series and is the only mandatory option for every chart object. This option links the chart with the worksheet data that it displays.

Fill: This set the format for the data series fill.

Line: This sets the line format of the line chart. The 'Line' property is optional and if it isn't supplied it will default style. The options that can be set are width and color. The range of width is 0.25pt - 999pt. If the value of width is outside the range, the default width of the line is 2pt.

Marker: This sets the marker of the line chart and scatter chart. The range of optional field 'Size' is 2-72 (default value is 5). The enumeration value of optional field 'Symbol' are (default value is 'auto'):

circle
dash
diamond
dot
none
picture
plus
square
star
triangle
x
auto

Set properties of the chart legend. The options that can be set are:

Position
ShowLegendKey

Position: Set the position of the chart legend. The default legend position is bottom. The available positions are:

none
top
bottom
left
right
top_right

ShowLegendKey: Set the legend keys shall be shown in data labels. The default value is false.

Set properties of the chart title. The properties that can be set are:

Title

Title: Set the name (title) for the chart. The name is displayed above the chart. The name can also be a formula such as Sheet1!$A$1 or a list with a sheet name. The name property is optional. The default is to have no chart title.

Specifies how blank cells are plotted on the chart by 'ShowBlanksAs'. The default value is gap. The options that can be set are:

gap
span
zero

gap: Specifies that blank values shall be left as a gap.

span: Specifies that blank values shall be spanned with a line.

zero: Specifies that blank values shall be treated as zero.

Specifies that each data marker in the series has a different color by 'VaryColors'. The default value is true.

Set chart offset, scale, aspect ratio setting and print settings by format, same as function 'AddPicture'.

Set the position of the chart plot area by PlotArea. The properties that can be set are:

SecondPlotValues
ShowBubbleSize
ShowCatName
ShowLeaderLines
ShowPercent
ShowSerName
ShowVal

SecondPlotValues: Specifies the values in second plot for the 'pieOfPie' and 'barOfPie' chart.

ShowBubbleSize: Specifies the bubble size shall be shown in a data label. The 'ShowBubbleSize' property is optional. The default value is false.

ShowCatName: Specifies that the category name shall be shown in the data label. The 'ShowCatName' property is optional. The default value is true.

ShowLeaderLines: Specifies leader lines shall be shown for data labels. The 'ShowLeaderLines' property is optional. The default value is false.

ShowPercent: Specifies that the percentage shall be shown in a data label. The 'ShowPercent' property is optional. The default value is false.

ShowSerName: Specifies that the series name shall be shown in a data label. The 'ShowSerName' property is optional. The default value is false.

ShowVal: Specifies that the value shall be shown in a data label. The 'ShowVal' property is optional. The default value is false.

Set the primary horizontal and vertical axis options by 'XAxis' and 'YAxis'. The properties of 'XAxis' that can be set are:

None
MajorGridLines
MinorGridLines
TickLabelSkip
ReverseOrder
Maximum
Minimum
Font
NumFmt
Title

The properties of 'YAxis' that can be set are:

None
MajorGridLines
MinorGridLines
MajorUnit
Secondary
ReverseOrder
Maximum
Minimum
Font
LogBase
NumFmt
Title

None: Disable axes.

MajorGridLines: Specifies major grid lines.

MinorGridLines: Specifies minor grid lines.

MajorUnit: Specifies the distance between major ticks. Shall contain a positive floating-point number. The 'MajorUnit' property is optional. The default value is auto.

Secondary: Specifies the current series vertical axis as the secondary axis, this only works for the second and later chart in the combo chart. The default value is false.

TickLabelSkip: Specifies how many tick labels to skip between label that is drawn. The 'TickLabelSkip' property is optional. The default value is auto.

ReverseOrder: Specifies that the categories or values on reverse order (orientation of the chart). The 'ReverseOrder' property is optional. The default value is false.

Maximum: Specifies that the fixed maximum, 0 is auto. The 'Maximum' property is optional. The default value is auto.

Minimum: Specifies that the fixed minimum, 0 is auto. The 'Minimum' property is optional. The default value is auto.

Font: Specifies that the font of the horizontal and vertical axis. The properties of font that can be set are:

Bold
Italic
Underline
Family
Size
Strike
Color
VertAlign

LogBase: Specifies logarithmic scale base number of the vertical axis.

NumFmt: Specifies that if linked to source and set custom number format code for axis. The 'NumFmt' property is optional. The default format code is 'General'.

Title: Specifies that the primary horizontal or vertical axis title and resize chart. The 'Title' property is optional.

Set chart size by 'Dimension' property. The 'Dimension' property is optional. The default width is 480, and height is 260.

combo: Specifies the create a chart that combines two or more chart types in a single chart. For example, create a clustered column - line chart with data Sheet1!$E$1:$L$15:

package main

import (
    "fmt"

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

func main() {
    f := excelize.NewFile()
    defer func() {
        if err := f.Close(); err != nil {
            fmt.Println(err)
        }
    }()
    for idx, row := range [][]interface{}{
        {nil, "Apple", "Orange", "Pear"}, {"Small", 2, 3, 3},
        {"Normal", 5, 2, 4}, {"Large", 6, 7, 8},
    } {
        cell, err := excelize.CoordinatesToCellName(1, idx+1)
        if err != nil {
            fmt.Println(err)
            return
        }
        f.SetSheetRow("Sheet1", cell, &row)
    }
    enable, disable := true, false
    if err := f.AddChart("Sheet1", "E1", &excelize.Chart{
        Type: "col",
        Series: []excelize.ChartSeries{
            {
                Name:       "Sheet1!$A$2",
                Categories: "Sheet1!$B$1:$D$1",
                Values:     "Sheet1!$B$2:$D$2",
            },
        },
        Format: excelize.GraphicOptions{
            ScaleX:          1,
            ScaleY:          1,
            OffsetX:         15,
            OffsetY:         10,
            PrintObject:     &enable,
            LockAspectRatio: false,
            Locked:          &disable,
        },
        Title: []excelize.RichTextRun{
            {
                Text: "Clustered Column - Line Chart",
            },
        },
        Legend: excelize.ChartLegend{
            Position:      "left",
            ShowLegendKey: false,
        },
        PlotArea: excelize.ChartPlotArea{
            ShowCatName:     false,
            ShowLeaderLines: false,
            ShowPercent:     true,
            ShowSerName:     true,
            ShowVal:         true,
        },
    }, &excelize.Chart{
        Type: "line",
        Series: []excelize.ChartSeries{
            {
                Name:       "Sheet1!$A$4",
                Categories: "Sheet1!$B$1:$D$1",
                Values:     "Sheet1!$B$4:$D$4",
                Marker: excelize.ChartMarker{
                    Symbol: "none", Size: 10,
                },
            },
        },
        Format: excelize.GraphicOptions{
            ScaleX:          1,
            ScaleY:          1,
            OffsetX:         15,
            OffsetY:         10,
            PrintObject:     &enable,
            LockAspectRatio: false,
            Locked:          &disable,
        },
        Legend: excelize.ChartLegend{
            Position:      "right",
            ShowLegendKey: false,
        },
        PlotArea: excelize.ChartPlotArea{
            ShowCatName:     false,
            ShowLeaderLines: false,
            ShowPercent:     true,
            ShowSerName:     true,
            ShowVal:         true,
        },
    }); err != nil {
        fmt.Println(err)
        return
    }
    // Save spreadsheet by the given path.
    if err := f.SaveAs("Book1.xlsx"); err != nil {
        fmt.Println(err)
    }
}

func (*File) AddChartSheet

func (f *File) AddChartSheet(sheet string, chart *Chart, combo ...*Chart) error

AddChartSheet provides the method to create a chartsheet by given chart format set (such as offset, scale, aspect ratio setting and print settings) and properties set. In Excel a chartsheet is a worksheet that only contains a chart.

func (*File) AddComment

func (f *File) AddComment(sheet string, opts Comment) error

AddComment provides the method to add comment in a sheet by given worksheet name, cell reference and format set (such as author and text). Note that the max author length is 255 and the max text length is 32512. For example, add a comment in Sheet1!$A$30:

err := f.AddComment("Sheet1", excelize.Comment{
    Cell:   "A12",
    Author: "Excelize",
    Paragraph: []excelize.RichTextRun{
        {Text: "Excelize: ", Font: &excelize.Font{Bold: true}},
        {Text: "This is a comment."},
    },
})

func (*File) AddDataValidation

func (f *File) AddDataValidation(sheet string, dv *DataValidation) error

AddDataValidation provides set data validation on a range of the worksheet by given data validation object and worksheet name. The data validation object can be created by NewDataValidation function.

Example 1, set data validation on Sheet1!A1:B2 with validation criteria settings, show error alert after invalid data is entered with "Stop" style and custom title "error body":

dv := excelize.NewDataValidation(true)
dv.Sqref = "A1:B2"
dv.SetRange(10, 20, excelize.DataValidationTypeWhole, excelize.DataValidationOperatorBetween)
dv.SetError(excelize.DataValidationErrorStyleStop, "error title", "error body")
err := f.AddDataValidation("Sheet1", dv)

Example 2, set data validation on Sheet1!A3:B4 with validation criteria settings, and show input message when cell is selected:

dv = excelize.NewDataValidation(true)
dv.Sqref = "A3:B4"
dv.SetRange(10, 20, excelize.DataValidationTypeWhole, excelize.DataValidationOperatorGreaterThan)
dv.SetInput("input title", "input body")
err = f.AddDataValidation("Sheet1", dv)

Example 3, set data validation on Sheet1!A5:B6 with validation criteria settings, create in-cell dropdown by allowing list source:

dv = excelize.NewDataValidation(true)
dv.Sqref = "A5:B6"
dv.SetDropList([]string{"1", "2", "3"})
err = f.AddDataValidation("Sheet1", dv)

func (*File) AddFormControl

func (f *File) AddFormControl(sheet string, opts FormControl) error

AddFormControl provides the method to add form control button in a worksheet by given worksheet name and form control options. Supported form control type: button, check box, group box, label, option button, scroll bar and spinner. If set macro for the form control, the workbook extension should be XLSM or XLTM. Scroll value must be between 0 and 30000.

Example 1, add button form control with macro, rich-text, custom button size, print property on Sheet1!A2, and let the button do not move or size with cells:

enable := true
err := f.AddFormControl("Sheet1", excelize.FormControl{
    Cell:   "A2",
    Type:   excelize.FormControlButton,
    Macro:  "Button1_Click",
    Width:  140,
    Height: 60,
    Text:   "Button 1\r\n",
    Paragraph: []excelize.RichTextRun{
        {
            Font: &excelize.Font{
                Bold:      true,
                Italic:    true,
                Underline: "single",
                Family:    "Times New Roman",
                Size:      14,
                Color:     "777777",
            },
            Text: "C1=A1+B1",
        },
    },
    Format: excelize.GraphicOptions{
        PrintObject: &enable,
        Positioning: "absolute",
    },
})

Example 2, add option button form control with checked status and text on Sheet1!A1:

err := f.AddFormControl("Sheet1", excelize.FormControl{
    Cell:    "A1",
    Type:    excelize.FormControlOptionButton,
    Text:    "Option Button 1",
    Checked: true,
})

Example 3, add spin button form control on Sheet1!B1 to increase or decrease the value of Sheet1!A1:

err := f.AddFormControl("Sheet1", excelize.FormControl{
    Cell:       "B1",
    Type:       excelize.FormControlSpinButton,
    Width:      15,
    Height:     40,
    CurrentVal: 7,
    MinVal:     5,
    MaxVal:     10,
    IncChange:  1,
    CellLink:   "A1",
})

Example 4, add horizontally scroll bar form control on Sheet1!A2 to change the value of Sheet1!A1 by click the scroll arrows or drag the scroll box:

err := f.AddFormControl("Sheet1", excelize.FormControl{
    Cell:         "A2",
    Type:         excelize.FormControlScrollBar,
    Width:        140,
    Height:       20,
    CurrentVal:   50,
    MinVal:       10,
    MaxVal:       100,
    IncChange:    1,
    PageChange:   1,
    CellLink:     "A1",
    Horizontally: true,
})

func (*File) AddPicture

func (f *File) AddPicture(sheet, cell, name string, opts *GraphicOptions) error

AddPicture provides the method to add picture in a sheet by given picture format set (such as offset, scale, aspect ratio setting and print settings) and file path, supported image types: BMP, EMF, EMZ, GIF, JPEG, JPG, PNG, SVG, TIF, TIFF, WMF, and WMZ. This function is concurrency safe. For example:

package main

import (
    "fmt"
    _ "image/gif"
    _ "image/jpeg"
    _ "image/png"

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

func main() {
    f := excelize.NewFile()
    defer func() {
        if err := f.Close(); err != nil {
            fmt.Println(err)
        }
    }()
    // Insert a picture.
    if err := f.AddPicture("Sheet1", "A2", "image.jpg", nil); err != nil {
        fmt.Println(err)
        return
    }
    // Insert a picture scaling in the cell with location hyperlink.
    enable := true
    if err := f.AddPicture("Sheet1", "D2", "image.png",
        &excelize.GraphicOptions{
            ScaleX:        0.5,
            ScaleY:        0.5,
            Hyperlink:     "#Sheet2!D8",
            HyperlinkType: "Location",
        },
    ); err != nil {
        fmt.Println(err)
        return
    }
    // Insert a picture offset in the cell with external hyperlink, printing and positioning support.
    if err := f.AddPicture("Sheet1", "H2", "image.gif",
        &excelize.GraphicOptions{
            PrintObject:     &enable,
            LockAspectRatio: false,
            OffsetX:         15,
            OffsetY:         10,
            Hyperlink:       "https://github.com/xuri/excelize",
            HyperlinkType:   "External",
            Positioning:     "oneCell",
        },
    ); err != nil {
        fmt.Println(err)
        return
    }
    if err := f.SaveAs("Book1.xlsx"); err != nil {
        fmt.Println(err)
    }
}

The optional parameter "AltText" is used to add alternative text to a graph object.

The optional parameter "PrintObject" indicates whether the graph object is printed when the worksheet is printed, the default value of that is 'true'.

The optional parameter "Locked" indicates whether lock the graph object. Locking an object has no effect unless the sheet is protected.

The optional parameter "LockAspectRatio" indicates whether lock aspect ratio for the graph object, the default value of that is 'false'.

The optional parameter "AutoFit" specifies if you make graph object size auto-fits the cell, the default value of that is 'false'.

The optional parameter "OffsetX" specifies the horizontal offset of the graph object with the cell, the default value of that is 0.

The optional parameter "OffsetY" specifies the vertical offset of the graph object with the cell, the default value of that is 0.

The optional parameter "ScaleX" specifies the horizontal scale of graph object, the default value of that is 1.0 which presents 100%.

The optional parameter "ScaleY" specifies the vertical scale of graph object, the default value of that is 1.0 which presents 100%.

The optional parameter "Hyperlink" specifies the hyperlink of the graph object.

The optional parameter "HyperlinkType" defines two types of hyperlink "External" for website or "Location" for moving to one of the cells in this workbook. When the "HyperlinkType" is "Location", coordinates need to start with "#".

The optional parameter "Positioning" defines 3 types of the position of a graph object in a spreadsheet: "oneCell" (Move but don't size with cells), "twoCell" (Move and size with cells), and "absolute" (Don't move or size with cells). If you don't set this parameter, the default positioning is to move and size with cells.

func (*File) AddPictureFromBytes

func (f *File) AddPictureFromBytes(sheet, cell string, pic *Picture) error

AddPictureFromBytes provides the method to add picture in a sheet by given picture format set (such as offset, scale, aspect ratio setting and print settings), file base name, extension name and file bytes, supported image types: EMF, EMZ, GIF, JPEG, JPG, PNG, SVG, TIF, TIFF, WMF, and WMZ. For example:

package main

import (
    "fmt"
    _ "image/jpeg"
    "os"

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

func main() {
    f := excelize.NewFile()
    defer func() {
        if err := f.Close(); err != nil {
            fmt.Println(err)
        }
    }()
    file, err := os.ReadFile("image.jpg")
    if err != nil {
        fmt.Println(err)
        return
    }
    if err := f.AddPictureFromBytes("Sheet1", "A2", &excelize.Picture{
        Extension: ".jpg",
        File:      file,
        Format:    &excelize.GraphicOptions{AltText: "Excel Logo"},
    }); err != nil {
        fmt.Println(err)
        return
    }
    if err := f.SaveAs("Book1.xlsx"); err != nil {
        fmt.Println(err)
    }
}

func (*File) AddPivotTable

func (f *File) AddPivotTable(opts *PivotTableOptions) error

AddPivotTable provides the method to add pivot table by given pivot table options. Note that the same fields can not in Columns, Rows and Filter fields at the same time.

For example, create a pivot table on the range reference Sheet1!$G$2:$M$34 with the range reference Sheet1!$A$1:$E$31 as the data source, summarize by sum for sales:

package main

import (
    "fmt"
    "math/rand"

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

func main() {
    f := excelize.NewFile()
    defer func() {
        if err := f.Close(); err != nil {
            fmt.Println(err)
        }
    }()
    // Create some data in a sheet
    month := []string{"Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec"}
    year := []int{2017, 2018, 2019}
    types := []string{"Meat", "Dairy", "Beverages", "Produce"}
    region := []string{"East", "West", "North", "South"}
    f.SetSheetRow("Sheet1", "A1", &[]string{"Month", "Year", "Type", "Sales", "Region"})
    for row := 2; row < 32; row++ {
        f.SetCellValue("Sheet1", fmt.Sprintf("A%d", row), month[rand.Intn(12)])
        f.SetCellValue("Sheet1", fmt.Sprintf("B%d", row), year[rand.Intn(3)])
        f.SetCellValue("Sheet1", fmt.Sprintf("C%d", row), types[rand.Intn(4)])
        f.SetCellValue("Sheet1", fmt.Sprintf("D%d", row), rand.Intn(5000))
        f.SetCellValue("Sheet1", fmt.Sprintf("E%d", row), region[rand.Intn(4)])
    }
    if err := f.AddPivotTable(&excelize.PivotTableOptions{
        DataRange:       "Sheet1!$A$1:$E$31",
        PivotTableRange: "Sheet1!$G$2:$M$34",
        Rows:            []excelize.PivotTableField{{Data: "Month", DefaultSubtotal: true}, {Data: "Year"}},
        Filter:          []excelize.PivotTableField{{Data: "Region"}},
        Columns:         []excelize.PivotTableField{{Data: "Type", DefaultSubtotal: true}},
        Data:            []excelize.PivotTableField{{Data: "Sales", Name: "Summarize", Subtotal: "Sum"}},
        RowGrandTotals:  true,
        ColGrandTotals:  true,
        ShowDrill:       true,
        ShowRowHeaders:  true,
        ShowColHeaders:  true,
        ShowLastColumn:  true,
    }); err != nil {
        fmt.Println(err)
    }
    if err := f.SaveAs("Book1.xlsx"); err != nil {
        fmt.Println(err)
    }
}

func (*File) AddShape

func (f *File) AddShape(sheet string, opts *Shape) error

AddShape provides the method to add shape in a sheet by given worksheet name and shape format set (such as offset, scale, aspect ratio setting and print settings). For example, add text box (rect shape) in Sheet1:

lineWidth := 1.2
err := f.AddShape("Sheet1",
    &excelize.Shape{
        Cell: "G6",
        Type: "rect",
        Line: excelize.ShapeLine{Color: "4286F4", Width: &lineWidth},
        Fill: excelize.Fill{Color: []string{"8EB9FF"}, Pattern: 1},
        Paragraph: []excelize.RichTextRun{
            {
                Text: "Rectangle Shape",
                Font: &excelize.Font{
                    Bold:      true,
                    Italic:    true,
                    Family:    "Times New Roman",
                    Size:      18,
                    Color:     "777777",
                    Underline: "sng",
                },
            },
        },
        Width:  180,
        Height: 40,
    },
)

The following shows the type of shape supported by excelize:

accentBorderCallout1 (Callout 1 with Border and Accent Shape)
accentBorderCallout2 (Callout 2 with Border and Accent Shape)
accentBorderCallout3 (Callout 3 with Border and Accent Shape)
accentCallout1 (Callout 1 Shape)
accentCallout2 (Callout 2 Shape)
accentCallout3 (Callout 3 Shape)
actionButtonBackPrevious (Back or Previous Button Shape)
actionButtonBeginning (Beginning Button Shape)
actionButtonBlank (Blank Button Shape)
actionButtonDocument (Document Button Shape)
actionButtonEnd (End Button Shape)
actionButtonForwardNext (Forward or Next Button Shape)
actionButtonHelp (Help Button Shape)
actionButtonHome (Home Button Shape)
actionButtonInformation (Information Button Shape)
actionButtonMovie (Movie Button Shape)
actionButtonReturn (Return Button Shape)
actionButtonSound (Sound Button Shape)
arc (Curved Arc Shape)
bentArrow (Bent Arrow Shape)
bentConnector2 (Bent Connector 2 Shape)
bentConnector3 (Bent Connector 3 Shape)
bentConnector4 (Bent Connector 4 Shape)
bentConnector5 (Bent Connector 5 Shape)
bentUpArrow (Bent Up Arrow Shape)
bevel (Bevel Shape)
blockArc (Block Arc Shape)
borderCallout1 (Callout 1 with Border Shape)
borderCallout2 (Callout 2 with Border Shape)
borderCallout3 (Callout 3 with Border Shape)
bracePair (Brace Pair Shape)
bracketPair (Bracket Pair Shape)
callout1 (Callout 1 Shape)
callout2 (Callout 2 Shape)
callout3 (Callout 3 Shape)
can (Can Shape)
chartPlus (Chart Plus Shape)
chartStar (Chart Star Shape)
chartX (Chart X Shape)
chevron (Chevron Shape)
chord (Chord Shape)
circularArrow (Circular Arrow Shape)
cloud (Cloud Shape)
cloudCallout (Callout Cloud Shape)
corner (Corner Shape)
cornerTabs (Corner Tabs Shape)
cube (Cube Shape)
curvedConnector2 (Curved Connector 2 Shape)
curvedConnector3 (Curved Connector 3 Shape)
curvedConnector4 (Curved Connector 4 Shape)
curvedConnector5 (Curved Connector 5 Shape)
curvedDownArrow (Curved Down Arrow Shape)
curvedLeftArrow (Curved Left Arrow Shape)
curvedRightArrow (Curved Right Arrow Shape)
curvedUpArrow (Curved Up Arrow Shape)
decagon (Decagon Shape)
diagStripe (Diagonal Stripe Shape)
diamond (Diamond Shape)
dodecagon (Dodecagon Shape)
donut (Donut Shape)
doubleWave (Double Wave Shape)
downArrow (Down Arrow Shape)
downArrowCallout (Callout Down Arrow Shape)
ellipse (Ellipse Shape)
ellipseRibbon (Ellipse Ribbon Shape)
ellipseRibbon2 (Ellipse Ribbon 2 Shape)
flowChartAlternateProcess (Alternate Process Flow Shape)
flowChartCollate (Collate Flow Shape)
flowChartConnector (Connector Flow Shape)
flowChartDecision (Decision Flow Shape)
flowChartDelay (Delay Flow Shape)
flowChartDisplay (Display Flow Shape)
flowChartDocument (Document Flow Shape)
flowChartExtract (Extract Flow Shape)
flowChartInputOutput (Input Output Flow Shape)
flowChartInternalStorage (Internal Storage Flow Shape)
flowChartMagneticDisk (Magnetic Disk Flow Shape)
flowChartMagneticDrum (Magnetic Drum Flow Shape)
flowChartMagneticTape (Magnetic Tape Flow Shape)
flowChartManualInput (Manual Input Flow Shape)
flowChartManualOperation (Manual Operation Flow Shape)
flowChartMerge (Merge Flow Shape)
flowChartMultidocument (Multi-Document Flow Shape)
flowChartOfflineStorage (Offline Storage Flow Shape)
flowChartOffpageConnector (Off-Page Connector Flow Shape)
flowChartOnlineStorage (Online Storage Flow Shape)
flowChartOr (Or Flow Shape)
flowChartPredefinedProcess (Predefined Process Flow Shape)
flowChartPreparation (Preparation Flow Shape)
flowChartProcess (Process Flow Shape)
flowChartPunchedCard (Punched Card Flow Shape)
flowChartPunchedTape (Punched Tape Flow Shape)
flowChartSort (Sort Flow Shape)
flowChartSummingJunction (Summing Junction Flow Shape)
flowChartTerminator (Terminator Flow Shape)
foldedCorner (Folded Corner Shape)
frame (Frame Shape)
funnel (Funnel Shape)
gear6 (Gear 6 Shape)
gear9 (Gear 9 Shape)
halfFrame (Half Frame Shape)
heart (Heart Shape)
heptagon (Heptagon Shape)
hexagon (Hexagon Shape)
homePlate (Home Plate Shape)
horizontalScroll (Horizontal Scroll Shape)
irregularSeal1 (Irregular Seal 1 Shape)
irregularSeal2 (Irregular Seal 2 Shape)
leftArrow (Left Arrow Shape)
leftArrowCallout (Callout Left Arrow Shape)
leftBrace (Left Brace Shape)
leftBracket (Left Bracket Shape)
leftCircularArrow (Left Circular Arrow Shape)
leftRightArrow (Left Right Arrow Shape)
leftRightArrowCallout (Callout Left Right Arrow Shape)
leftRightCircularArrow (Left Right Circular Arrow Shape)
leftRightRibbon (Left Right Ribbon Shape)
leftRightUpArrow (Left Right Up Arrow Shape)
leftUpArrow (Left Up Arrow Shape)
lightningBolt (Lightning Bolt Shape)
line (Line Shape)
lineInv (Line Inverse Shape)
mathDivide (Divide Math Shape)
mathEqual (Equal Math Shape)
mathMinus (Minus Math Shape)
mathMultiply (Multiply Math Shape)
mathNotEqual (Not Equal Math Shape)
mathPlus (Plus Math Shape)
moon (Moon Shape)
nonIsoscelesTrapezoid (Non-Isosceles Trapezoid Shape)
noSmoking (No Smoking Shape)
notchedRightArrow (Notched Right Arrow Shape)
octagon (Octagon Shape)
parallelogram (Parallelogram Shape)
pentagon (Pentagon Shape)
pie (Pie Shape)
pieWedge (Pie Wedge Shape)
plaque (Plaque Shape)
plaqueTabs (Plaque Tabs Shape)
plus (Plus Shape)
quadArrow (Quad-Arrow Shape)
quadArrowCallout (Callout Quad-Arrow Shape)
rect (Rectangle Shape)
ribbon (Ribbon Shape)
ribbon2 (Ribbon 2 Shape)
rightArrow (Right Arrow Shape)
rightArrowCallout (Callout Right Arrow Shape)
rightBrace (Right Brace Shape)
rightBracket (Right Bracket Shape)
round1Rect (One Round Corner Rectangle Shape)
round2DiagRect (Two Diagonal Round Corner Rectangle Shape)
round2SameRect (Two Same-side Round Corner Rectangle Shape)
roundRect (Round Corner Rectangle Shape)
rtTriangle (Right Triangle Shape)
smileyFace (Smiley Face Shape)
snip1Rect (One Snip Corner Rectangle Shape)
snip2DiagRect (Two Diagonal Snip Corner Rectangle Shape)
snip2SameRect (Two Same-side Snip Corner Rectangle Shape)
snipRoundRect (One Snip One Round Corner Rectangle Shape)
squareTabs (Square Tabs Shape)
star10 (Ten Pointed Star Shape)
star12 (Twelve Pointed Star Shape)
star16 (Sixteen Pointed Star Shape)
star24 (Twenty Four Pointed Star Shape)
star32 (Thirty Two Pointed Star Shape)
star4 (Four Pointed Star Shape)
star5 (Five Pointed Star Shape)
star6 (Six Pointed Star Shape)
star7 (Seven Pointed Star Shape)
star8 (Eight Pointed Star Shape)
straightConnector1 (Straight Connector 1 Shape)
stripedRightArrow (Striped Right Arrow Shape)
sun (Sun Shape)
swooshArrow (Swoosh Arrow Shape)
teardrop (Teardrop Shape)
trapezoid (Trapezoid Shape)
triangle (Triangle Shape)
upArrow (Up Arrow Shape)
upArrowCallout (Callout Up Arrow Shape)
upDownArrow (Up Down Arrow Shape)
upDownArrowCallout (Callout Up Down Arrow Shape)
uturnArrow (U-Turn Arrow Shape)
verticalScroll (Vertical Scroll Shape)
wave (Wave Shape)
wedgeEllipseCallout (Callout Wedge Ellipse Shape)
wedgeRectCallout (Callout Wedge Rectangle Shape)
wedgeRoundRectCallout (Callout Wedge Round Rectangle Shape)

The following shows the type of text underline supported by excelize:

none
words
sng
dbl
heavy
dotted
dottedHeavy
dash
dashHeavy
dashLong
dashLongHeavy
dotDash
dotDashHeavy
dotDotDash
dotDotDashHeavy
wavy
wavyHeavy
wavyDbl

func (*File) AddSparkline

func (f *File) AddSparkline(sheet string, opts *SparklineOptions) error

AddSparkline provides a function to add sparklines to the worksheet by given formatting options. Sparklines are small charts that fit in a single cell and are used to show trends in data. Sparklines are a feature of Excel 2010 and later only. You can write them to an XLSX file that can be read by Excel 2007, but they won't be displayed. For example, add a grouped sparkline. Changes are applied to all three:

err := f.AddSparkline("Sheet1", &excelize.SparklineOptions{
    Location: []string{"A1", "A2", "A3"},
    Range:    []string{"Sheet2!A1:J1", "Sheet2!A2:J2", "Sheet2!A3:J3"},
    Markers:  true,
})

The following shows the formatting options of sparkline supported by excelize:

 Parameter   | Description
-------------+--------------------------------------------
 Location    | Required, must have the same number with 'Range' parameter
 Range       | Required, must have the same number with 'Location' parameter
 Type        | Enumeration value: line, column, win_loss
 Style       | Value range: 0 - 35
 Hight       | Toggle sparkline high points
 Low         | Toggle sparkline low points
 First       | Toggle sparkline first points
 Last        | Toggle sparkline last points
 Negative    | Toggle sparkline negative points
 Markers     | Toggle sparkline markers
 Axis        | Used to specify if show horizontal axis
 Reverse     | Used to specify if enable plot data right-to-left
 SeriesColor | An RGB Color is specified as RRGGBB

func (*File) AddTable

func (f *File) AddTable(sheet string, table *Table) error

AddTable provides the method to add table in a worksheet by given worksheet name, range reference and format set. For example, create a table of A1:D5 on Sheet1:

err := f.AddTable("Sheet1", &excelize.Table{Range: "A1:D5"})

Create a table of F2:H6 on Sheet2 with format set:

disable := false
err := f.AddTable("Sheet2", &excelize.Table{
    Range:             "F2:H6",
    Name:              "table",
    StyleName:         "TableStyleMedium2",
    ShowFirstColumn:   true,
    ShowLastColumn:    true,
    ShowRowStripes:    &disable,
    ShowColumnStripes: true,
})

Note that the table must be at least two lines including the header. The header cells must contain strings and must be unique, and must set the header row data of the table before calling the AddTable function. Multiple tables range reference that can't have an intersection.

Name: The name of the table, in the same worksheet name of the table should be unique, starts with a letter or underscore (_), doesn't include a space or character, and should be no more than 255 characters

StyleName: The built-in table style names

TableStyleLight1 - TableStyleLight21
TableStyleMedium1 - TableStyleMedium28
TableStyleDark1 - TableStyleDark11

func (*File) AddVBAProject

func (f *File) AddVBAProject(file []byte) error

AddVBAProject provides the method to add vbaProject.bin file which contains functions and/or macros. The file extension should be XLSM or XLTM. For example:

codeName := "Sheet1"
if err := f.SetSheetProps("Sheet1", &excelize.SheetPropsOptions{
    CodeName: &codeName,
}); err != nil {
    fmt.Println(err)
    return
}
file, err := os.ReadFile("vbaProject.bin")
if err != nil {
    fmt.Println(err)
    return
}
if err := f.AddVBAProject(file); err != nil {
    fmt.Println(err)
    return
}
if err := f.SaveAs("macros.xlsm"); err != nil {
    fmt.Println(err)
    return
}

func (*File) AutoFilter

func (f *File) AutoFilter(sheet, rangeRef string, opts []AutoFilterOptions) error

AutoFilter provides the method to add auto filter in a worksheet by given worksheet name, range reference and settings. An auto filter in Excel is a way of filtering a 2D range of data based on some simple criteria. For example applying an auto filter to a cell range A1:D4 in the Sheet1:

err := f.AutoFilter("Sheet1", "A1:D4", []excelize.AutoFilterOptions{})

Filter data in an auto filter:

err := f.AutoFilter("Sheet1", "A1:D4", []excelize.AutoFilterOptions{
    {Column: "B", Expression: "x != blanks"},
})

Column defines the filter columns in an auto filter range based on simple criteria

It isn't sufficient to just specify the filter condition. You must also hide any rows that don't match the filter condition. Rows are hidden using the SetRowVisible function. Excelize can't filter rows automatically since this isn't part of the file format.

Setting a filter criteria for a column:

Expression defines the conditions, the following operators are available for setting the filter criteria:

==
!=
>
<
>=
<=
and
or

An expression can comprise a single statement or two statements separated by the 'and' and 'or' operators. For example:

x <  2000
x >  2000
x == 2000
x >  2000 and x <  5000
x == 2000 or  x == 5000

Filtering of blank or non-blank data can be achieved by using a value of Blanks or NonBlanks in the expression:

x == Blanks
x == NonBlanks

Excel also allows some simple string matching operations:

x == b*      // begins with b
x != b*      // doesn't begin with b
x == *b      // ends with b
x != *b      // doesn't end with b
x == *b*     // contains b
x != *b*     // doesn't contain b

You can also use '*' to match any character or number and '?' to match any single character or number. No other regular expression quantifier is supported by Excel's filters. Excel's regular expression characters can be escaped using '~'.

The placeholder variable x in the above examples can be replaced by any simple string. The actual placeholder name is ignored internally so the following are all equivalent:

x     < 2000
col   < 2000
Price < 2000

func (*File) CalcCellValue

func (f *File) CalcCellValue(sheet, cell string, opts ...Options) (result string, err error)

CalcCellValue provides a function to get calculated cell value. This feature is currently in working processing. Iterative calculation, implicit intersection, explicit intersection, array formula, table formula and some other formulas are not supported currently.

Supported formula functions:

ABS
ACCRINT
ACCRINTM
ACOS
ACOSH
ACOT
ACOTH
ADDRESS
AGGREGATE
AMORDEGRC
AMORLINC
AND
ARABIC
ASIN
ASINH
ATAN
ATAN2
ATANH
AVEDEV
AVERAGE
AVERAGEA
AVERAGEIF
AVERAGEIFS
BASE
BESSELI
BESSELJ
BESSELK
BESSELY
BETA.DIST
BETA.INV
BETADIST
BETAINV
BIN2DEC
BIN2HEX
BIN2OCT
BINOM.DIST
BINOM.DIST.RANGE
BINOM.INV
BINOMDIST
BITAND
BITLSHIFT
BITOR
BITRSHIFT
BITXOR
CEILING
CEILING.MATH
CEILING.PRECISE
CHAR
CHIDIST
CHIINV
CHISQ.DIST
CHISQ.DIST.RT
CHISQ.INV
CHISQ.INV.RT
CHISQ.TEST
CHITEST
CHOOSE
CLEAN
CODE
COLUMN
COLUMNS
COMBIN
COMBINA
COMPLEX
CONCAT
CONCATENATE
CONFIDENCE
CONFIDENCE.NORM
CONFIDENCE.T
CONVERT
CORREL
COS
COSH
COT
COTH
COUNT
COUNTA
COUNTBLANK
COUNTIF
COUNTIFS
COUPDAYBS
COUPDAYS
COUPDAYSNC
COUPNCD
COUPNUM
COUPPCD
COVAR
COVARIANCE.P
COVARIANCE.S
CRITBINOM
CSC
CSCH
CUMIPMT
CUMPRINC
DATE
DATEDIF
DATEVALUE
DAVERAGE
DAY
DAYS
DAYS360
DB
DCOUNT
DCOUNTA
DDB
DEC2BIN
DEC2HEX
DEC2OCT
DECIMAL
DEGREES
DELTA
DEVSQ
DGET
DISC
DMAX
DMIN
DOLLARDE
DOLLARFR
DPRODUCT
DSTDEV
DSTDEVP
DSUM
DURATION
DVAR
DVARP
EDATE
EFFECT
ENCODEURL
EOMONTH
ERF
ERF.PRECISE
ERFC
ERFC.PRECISE
ERROR.TYPE
EUROCONVERT
EVEN
EXACT
EXP
EXPON.DIST
EXPONDIST
F.DIST
F.DIST.RT
F.INV
F.INV.RT
F.TEST
FACT
FACTDOUBLE
FALSE
FDIST
FIND
FINDB
FINV
FISHER
FISHERINV
FIXED
FLOOR
FLOOR.MATH
FLOOR.PRECISE
FORMULATEXT
FTEST
FV
FVSCHEDULE
GAMMA
GAMMA.DIST
GAMMA.INV
GAMMADIST
GAMMAINV
GAMMALN
GAMMALN.PRECISE
GAUSS
GCD
GEOMEAN
GESTEP
GROWTH
HARMEAN
HEX2BIN
HEX2DEC
HEX2OCT
HLOOKUP
HOUR
HYPERLINK
HYPGEOM.DIST
HYPGEOMDIST
IF
IFERROR
IFNA
IFS
IMABS
IMAGINARY
IMARGUMENT
IMCONJUGATE
IMCOS
IMCOSH
IMCOT
IMCSC
IMCSCH
IMDIV
IMEXP
IMLN
IMLOG10
IMLOG2
IMPOWER
IMPRODUCT
IMREAL
IMSEC
IMSECH
IMSIN
IMSINH
IMSQRT
IMSUB
IMSUM
IMTAN
INDEX
INDIRECT
INT
INTRATE
IPMT
IRR
ISBLANK
ISERR
ISERROR
ISEVEN
ISFORMULA
ISLOGICAL
ISNA
ISNONTEXT
ISNUMBER
ISO.CEILING
ISODD
ISOWEEKNUM
ISPMT
ISREF
ISTEXT
KURT
LARGE
LCM
LEFT
LEFTB
LEN
LENB
LN
LOG
LOG10
LOGINV
LOGNORM.DIST
LOGNORM.INV
LOGNORMDIST
LOOKUP
LOWER
MATCH
MAX
MAXA
MAXIFS
MDETERM
MDURATION
MEDIAN
MID
MIDB
MIN
MINA
MINIFS
MINUTE
MINVERSE
MIRR
MMULT
MOD
MODE
MODE.MULT
MODE.SNGL
MONTH
MROUND
MULTINOMIAL
MUNIT
N
NA
NEGBINOM.DIST
NEGBINOMDIST
NETWORKDAYS
NETWORKDAYS.INTL
NOMINAL
NORM.DIST
NORM.INV
NORM.S.DIST
NORM.S.INV
NORMDIST
NORMINV
NORMSDIST
NORMSINV
NOT
NOW
NPER
NPV
OCT2BIN
OCT2DEC
OCT2HEX
ODD
ODDFPRICE
OR
PDURATION
PEARSON
PERCENTILE
PERCENTILE.EXC
PERCENTILE.INC
PERCENTRANK
PERCENTRANK.EXC
PERCENTRANK.INC
PERMUT
PERMUTATIONA
PHI
PI
PMT
POISSON
POISSON.DIST
POWER
PPMT
PRICE
PRICEDISC
PRICEMAT
PRODUCT
PROPER
PV
QUARTILE
QUARTILE.EXC
QUARTILE.INC
QUOTIENT
RADIANS
RAND
RANDBETWEEN
RANK
RANK.EQ
RATE
RECEIVED
REPLACE
REPLACEB
REPT
RIGHT
RIGHTB
ROMAN
ROUND
ROUNDDOWN
ROUNDUP
ROW
ROWS
RRI
RSQ
SEC
SECH
SECOND
SERIESSUM
SHEET
SHEETS
SIGN
SIN
SINH
SKEW
SKEW.P
SLN
SLOPE
SMALL
SQRT
SQRTPI
STANDARDIZE
STDEV
STDEV.P
STDEV.S
STDEVA
STDEVP
STDEVPA
STEYX
SUBSTITUTE
SUBTOTAL
SUM
SUMIF
SUMIFS
SUMPRODUCT
SUMSQ
SUMX2MY2
SUMX2PY2
SUMXMY2
SWITCH
SYD
T
T.DIST
T.DIST.2T
T.DIST.RT
T.INV
T.INV.2T
T.TEST
TAN
TANH
TBILLEQ
TBILLPRICE
TBILLYIELD
TDIST
TEXTJOIN
TIME
TIMEVALUE
TINV
TODAY
TRANSPOSE
TREND
TRIM
TRIMMEAN
TRUE
TRUNC
TTEST
TYPE
UNICHAR
UNICODE
UPPER
VALUE
VAR
VAR.P
VAR.S
VARA
VARP
VARPA
VDB
VLOOKUP
WEEKDAY
WEEKNUM
WEIBULL
WEIBULL.DIST
WORKDAY
WORKDAY.INTL
XIRR
XLOOKUP
XNPV
XOR
YEAR
YEARFRAC
YIELD
YIELDDISC
YIELDMAT
Z.TEST
ZTEST

func (*File) CharsetTranscoder

func (f *File) CharsetTranscoder(fn charsetTranscoderFn) *File

CharsetTranscoder Set user defined codepage transcoder function for open XLSX from non UTF-8 encoding.

func (*File) Close

func (f *File) Close() error

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

func (*File) Cols

func (f *File) Cols(sheet string) (*Cols, error)

Cols returns a columns 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()
}

func (*File) CopySheet

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)

func (*File) DeleteChart

func (f *File) DeleteChart(sheet, cell string) error

DeleteChart provides a function to delete chart in spreadsheet by given worksheet name and cell reference.

func (*File) DeleteComment

func (f *File) DeleteComment(sheet, cell string) error

DeleteComment provides the method to delete comment in a worksheet by given worksheet name and cell reference. For example, delete the comment in Sheet1!$A$30:

err := f.DeleteComment("Sheet1", "A30")

func (*File) DeleteDataValidation

func (f *File) DeleteDataValidation(sheet string, sqref ...string) error

DeleteDataValidation delete data validation by given worksheet name and reference sequence. All data validations in the worksheet will be deleted if not specify reference sequence parameter.

func (*File) DeleteDefinedName

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

func (*File) DeleteFormControl

func (f *File) DeleteFormControl(sheet, cell string) error

DeleteFormControl provides the method to delete form control in a worksheet by given worksheet name and cell reference. For example, delete the form control in Sheet1!$A$1:

err := f.DeleteFormControl("Sheet1", "A1")

func (*File) DeletePicture

func (f *File) DeletePicture(sheet, cell string) error

DeletePicture provides a function to delete all pictures in a cell by given worksheet name and cell reference. Note that the image file won't be deleted from the document currently.

func (*File) DeleteSheet

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.

func (*File) DeleteTable

func (f *File) DeleteTable(name string) error

DeleteTable provides the method to delete table by given table name.

func (*File) DuplicateRow

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

DuplicateRow inserts a copy of specified row (by its Excel row number) below

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.

func (*File) DuplicateRowTo

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

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.

func (*File) GetActiveSheetIndex

func (f *File) GetActiveSheetIndex() (index int)

GetActiveSheetIndex provides a function to get active sheet index of the spreadsheet. If not found the active sheet will be return integer 0.

func (*File) GetAppProps

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

GetAppProps provides a function to get document application properties.

func (*File) GetCellFormula

func (f *File) GetCellFormula(sheet, cell string) (string, error)

GetCellFormula provides a function to get formula from cell by given worksheet name and cell reference in spreadsheet.

func (f *File) GetCellHyperLink(sheet, cell string) (bool, string, error)

GetCellHyperLink gets a cell hyperlink based on the given worksheet name and cell reference. If the cell has a hyperlink, it will return 'true' and the link address, otherwise it will return 'false' and an empty link address.

For example, get a hyperlink to a 'H6' cell on a worksheet named 'Sheet1':

link, target, err := f.GetCellHyperLink("Sheet1", "H6")

func (*File) GetCellRichText

func (f *File) GetCellRichText(sheet, cell string) (runs []RichTextRun, err error)

GetCellRichText provides a function to get rich text of cell by given worksheet.

func (*File) GetCellStyle

func (f *File) GetCellStyle(sheet, cell string) (int, error)

GetCellStyle provides a function to get cell style index by given worksheet name and cell reference.

func (*File) GetCellType

func (f *File) GetCellType(sheet, cell string) (CellType, error)

GetCellType provides a function to get the cell's data type by given worksheet name and cell reference in spreadsheet file.

func (*File) GetCellValue

func (f *File) GetCellValue(sheet, cell string, opts ...Options) (string, error)

GetCellValue provides a function to get formatted value from cell by given worksheet name and cell reference in spreadsheet. The return value is converted to the 'string' data type. This function is concurrency safe. If the cell format can be applied to the value of a cell, the applied value will be returned, otherwise the original value will be returned. All cells' values will be the same in a merged range.

func (*File) GetColOutlineLevel

func (f *File) GetColOutlineLevel(sheet, col string) (uint8, error)

GetColOutlineLevel provides a function to get 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")

func (*File) GetColStyle

func (f *File) GetColStyle(sheet, col string) (int, error)

GetColStyle provides a function to get column style ID by given worksheet name and column name. This function is concurrency safe.

func (*File) GetColVisible

func (f *File) GetColVisible(sheet, col 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 visible state of column D in Sheet1:

visible, err := f.GetColVisible("Sheet1", "D")

func (*File) GetColWidth

func (f *File) GetColWidth(sheet, col string) (float64, error)

GetColWidth provides a function to get column width by given worksheet name and column name. This function is concurrency safe.

func (*File) GetCols

func (f *File) GetCols(sheet string, opts ...Options) ([][]string, error)

GetCols gets the value of all cells by columns on the worksheet based on the given worksheet name, returned as a two-dimensional array, where the value of the cell is converted to the `string` type. If the cell format can be applied to the value of the cell, the applied value will be used, otherwise the original value will be used.

For example, get and traverse the value of all cells by columns on a worksheet named 'Sheet1':

cols, err := f.GetCols("Sheet1")
if err != nil {
    fmt.Println(err)
    return
}
for _, col := range cols {
    for _, rowCell := range col {
        fmt.Print(rowCell, "\t")
    }
    fmt.Println()
}

func (*File) GetComments

func (f *File) GetComments(sheet string) ([]Comment, error)

GetComments retrieves all comments in a worksheet by given worksheet name.

func (*File) GetConditionalFormats

func (f *File) GetConditionalFormats(sheet string) (map[string][]ConditionalFormatOptions, error)

GetConditionalFormats returns conditional format settings by given worksheet name.

func (*File) GetDataValidations

func (f *File) GetDataValidations(sheet string) ([]*DataValidation, error)

GetDataValidations returns data validations list by given worksheet name.

func (*File) GetDefaultFont

func (f *File) GetDefaultFont() (string, error)

GetDefaultFont provides the default font name currently set in the workbook. The spreadsheet generated by excelize default font is Calibri.

func (*File) GetDefinedName

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

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

func (*File) GetDocProps

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

GetDocProps provides a function to get document core properties.

func (*File) GetFormControls

func (f *File) GetFormControls(sheet string) ([]FormControl, error)

GetFormControls retrieves all form controls in a worksheet by a given worksheet name. Note that, this function does not support getting the width and height of the form controls currently.

func (*File) GetMergeCells

func (f *File) GetMergeCells(sheet string) ([]MergeCell, error)

GetMergeCells provides a function to get all merged cells from a worksheet currently.

func (*File) GetPageLayout

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

GetPageLayout provides a function to gets worksheet page layout.

func (*File) GetPageMargins

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

GetPageMargins provides a function to get worksheet page margins.

func (*File) GetPanes

func (f *File) GetPanes(sheet string) (Panes, error)

GetPanes provides a function to get freeze panes, split panes, and worksheet views by given worksheet name.

func (*File) GetPictures

func (f *File) GetPictures(sheet, cell string) ([]Picture, error)

GetPictures provides a function to get picture meta info and raw content embed in spreadsheet by given worksheet and cell name. This function returns the image contents as []byte data types. This function is concurrency safe. For example:

f, err := excelize.OpenFile("Book1.xlsx")
if err != nil {
    fmt.Println(err)
    return
}
defer func() {
    if err := f.Close(); err != nil {
        fmt.Println(err)
    }
}()
pics, err := f.GetPictures("Sheet1", "A2")
if err != nil {
	fmt.Println(err)
}
for idx, pic := range pics {
    name := fmt.Sprintf("image%d%s", idx+1, pic.Extension)
    if err := os.WriteFile(name, pic.File, 0644); err != nil {
        fmt.Println(err)
    }
}

func (*File) GetRowHeight

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)

func (*File) GetRowOutlineLevel

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

GetRowOutlineLevel provides a function to get outline level number of a single row by given worksheet name and Excel row number. For example, get outline number of row 2 in Sheet1:

level, err := f.GetRowOutlineLevel("Sheet1", 2)

func (*File) GetRowVisible

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 Excel row number. For example, get visible state of row 2 in Sheet1:

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

func (*File) GetRows

func (f *File) GetRows(sheet string, opts ...Options) ([][]string, error)

GetRows return all the rows in a sheet by given worksheet name, returned as a two-dimensional array, where the value of the cell is converted to the string type. If the cell format can be applied to the value of the cell, the applied value will be used, otherwise the original value will be used. GetRows fetched the rows with value or formula cells, the continually blank cells in the tail of each row will be skipped, so the length of each row may be inconsistent.

For example, get and traverse the value of all cells by rows on a worksheet named 'Sheet1':

rows, err := f.GetRows("Sheet1")
if err != nil {
    fmt.Println(err)
    return
}
for _, row := range rows {
    for _, colCell := range row {
        fmt.Print(colCell, "\t")
    }
    fmt.Println()
}

func (*File) GetSheetDimension

func (f *File) GetSheetDimension(sheet string) (string, error)

GetSheetDimension provides the method to get the used range of the worksheet.

func (*File) GetSheetIndex

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.

func (*File) GetSheetList

func (f *File) GetSheetList() (list []string)

GetSheetList provides a function to get worksheets, chart sheets, and dialog sheets name list of the workbook.

func (*File) GetSheetMap

func (f *File) GetSheetMap() map[int]string

GetSheetMap provides a function to get worksheets, chart sheets, dialog sheets ID and name map 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)
}

func (*File) GetSheetName

func (f *File) GetSheetName(index int) (name 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.

func (*File) GetSheetProps

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

GetSheetProps provides a function to get worksheet properties.

func (*File) GetSheetView

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

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

func (*File) GetSheetVisible

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

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

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

func (*File) GetStyle

func (f *File) GetStyle(idx int) (*Style, error)

GetStyle get style details by given style index.

func (*File) GetTables

func (f *File) GetTables(sheet string) ([]Table, error)

GetTables provides the method to get all tables in a worksheet by given worksheet name.

func (*File) GetWorkbookProps

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

GetWorkbookProps provides a function to gets workbook properties.

func (*File) GroupSheets

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

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

func (*File) InsertCols

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)

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.

func (*File) InsertPageBreak

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.

func (*File) InsertRows

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)

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.

func (*File) MergeCell

func (f *File) MergeCell(sheet, hCell, vCell string) error

MergeCell provides a function to merge cells by given range reference and sheet name. Merging cells only keeps the upper-left cell value, and discards the other values. For example create a merged cell of D3:E9 on Sheet1:

err := f.MergeCell("Sheet1", "D3", "E9")

If you create a merged cell that overlaps with another existing merged cell, those merged cells that already exist will be removed. The cell references tuple after merging in the following range will be: A1(x3,y1) D1(x2,y1) A8(x3,y4) D8(x2,y4)

             B1(x1,y1)      D1(x2,y1)
           +------------------------+
           |                        |
A4(x3,y3)  |    C4(x4,y3)           |
+------------------------+          |
|          |             |          |
|          |B5(x1,y2)    | D5(x2,y2)|
|          +------------------------+
|                        |
|A8(x3,y4)      C8(x4,y4)|
+------------------------+

func (*File) NewConditionalStyle

func (f *File) NewConditionalStyle(style *Style) (int, error)

NewConditionalStyle provides a function to create style for conditional format by given style format. The parameters are the same with the NewStyle function.

func (*File) NewSheet

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 after it appended. Note that when creating a new workbook, the default worksheet named `Sheet1` will be created.

func (*File) NewStreamWriter

func (f *File) NewStreamWriter(sheet string) (*StreamWriter, error)

NewStreamWriter returns stream writer struct by given worksheet name used for writing data on a new existing empty worksheet with large amounts of data. Note that after writing data with the stream writer for the worksheet, you must call the 'Flush' method to end the streaming writing process, ensure that the order of row numbers is ascending when set rows, and the normal mode functions and stream mode functions can not be work mixed to writing data on the worksheets. The stream writer will try to use temporary files on disk to reduce the memory usage when in-memory chunks data over 16MB, and you can't get cell value at this time. For example, set data for worksheet of size 102400 rows x 50 columns with numbers and style:

f := excelize.NewFile()
defer func() {
    if err := f.Close(); err != nil {
        fmt.Println(err)
    }
}()
sw, err := f.NewStreamWriter("Sheet1")
if err != nil {
    fmt.Println(err)
    return
}
styleID, err := f.NewStyle(&excelize.Style{Font: &excelize.Font{Color: "777777"}})
if err != nil {
    fmt.Println(err)
    return
}
if err := sw.SetRow("A1",
    []interface{}{
        excelize.Cell{StyleID: styleID, Value: "Data"},
        []excelize.RichTextRun{
            {Text: "Rich ", Font: &excelize.Font{Color: "2354e8"}},
            {Text: "Text", Font: &excelize.Font{Color: "e83723"}},
        },
    },
    excelize.RowOpts{Height: 45, Hidden: false}); err != nil {
    fmt.Println(err)
    return
}
for rowID := 2; rowID <= 102400; rowID++ {
    row := make([]interface{}, 50)
    for colID := 0; colID < 50; colID++ {
        row[colID] = rand.Intn(640000)
    }
    cell, err := excelize.CoordinatesToCellName(1, rowID)
    if err != nil {
        fmt.Println(err)
        break
    }
    if err := sw.SetRow(cell, row); err != nil {
        fmt.Println(err)
        break
    }
}
if err := sw.Flush(); err != nil {
    fmt.Println(err)
    return
}
if err := f.SaveAs("Book1.xlsx"); err != nil {
    fmt.Println(err)
}

Set cell value and cell formula for a worksheet with stream writer:

err := sw.SetRow("A1", []interface{}{
    excelize.Cell{Value: 1},
    excelize.Cell{Value: 2},
    excelize.Cell{Formula: "SUM(A1,B1)"}});

Set cell value and rows style for a worksheet with stream writer:

err := sw.SetRow("A1", []interface{}{
    excelize.Cell{Value: 1}},
    excelize.RowOpts{StyleID: styleID, Height: 20, Hidden: false});

func (*File) NewStyle

func (f *File) NewStyle(style *Style) (int, error)

NewStyle provides a function to create the style for cells by a given style options, and returns style index. The same style index can not be used across different workbook. This function is concurrency safe. Note that the 'Font.Color' field uses an RGB color represented in 'RRGGBB' hexadecimal notation.

The following table shows the border types used in 'Border.Type' supported by excelize:

 Type         | Description
--------------+------------------
 left         | Left border
 top          | Top border
 right        | Right border
 bottom       | Bottom border
 diagonalDown | Diagonal down border
 diagonalUp   | Diagonal up border

The following table shows the border styles used in 'Border.Style' supported by excelize index number:

 Index | Name          | Weight | Style
-------+---------------+--------+-------------
 0     | None          | 0      |
 1     | Continuous    | 1      | -----------
 2     | Continuous    | 2      | -----------
 3     | Dash          | 1      | - - - - - -
 4     | Dot           | 1      | . . . . . .
 5     | Continuous    | 3      | -----------
 6     | Double        | 3      | ===========
 7     | Continuous    | 0      | -----------
 8     | Dash          | 2      | - - - - - -
 9     | Dash Dot      | 1      | - . - . - .
 10    | Dash Dot      | 2      | - . - . - .
 11    | Dash Dot Dot  | 1      | - . . - . .
 12    | Dash Dot Dot  | 2      | - . . - . .
 13    | SlantDash Dot | 2      | / - . / - .

The following table shows the border styles used in 'Border.Style' in the order shown in the Excel dialog:

 Index | Style       | Index | Style
-------+-------------+-------+-------------
 0     | None        | 12    | - . . - . .
 7     | ----------- | 13    | / - . / - .
 4     | . . . . . . | 10    | - . - . - .
 11    | - . . - . . | 8     | - - - - - -
 9     | - . - . - . | 2     | -----------
 3     | - - - - - - | 5     | -----------
 1     | ----------- | 6     | ===========

The following table shows the shading styles used in 'Fill.Shading' supported by excelize index number:

 Index | Style           | Index | Style
-------+-----------------+-------+-----------------
 0-2   | Horizontal      | 9-11  | Diagonal down
 3-5   | Vertical        | 12-15 | From corner
 6-8   | Diagonal Up     | 16    | From center

The following table shows the pattern styles used in 'Fill.Pattern' supported by excelize index number:

 Index | Style           | Index | Style
-------+-----------------+-------+-----------------
 0     | None            | 10    | darkTrellis
 1     | solid           | 11    | lightHorizontal
 2     | mediumGray      | 12    | lightVertical
 3     | darkGray        | 13    | lightDown
 4     | lightGray       | 14    | lightUp
 5     | darkHorizontal  | 15    | lightGrid
 6     | darkVertical    | 16    | lightTrellis
 7     | darkDown        | 17    | gray125
 8     | darkUp          | 18    | gray0625
 9     | darkGrid        |       |

The 'Alignment.Indent' is an integer value, where an increment of 1 represents 3 spaces. Indicates the number of spaces (of the normal style font) of indentation for text in a cell. The number of spaces to indent is calculated as following:

Number of spaces to indent = indent value * 3

For example, an indent value of 1 means that the text begins 3 space widths (of the normal style font) from the edge of the cell. Note: The width of one space character is defined by the font. Only left, right, and distributed horizontal alignments are supported.

The following table shows the type of cells' horizontal alignment used in 'Alignment.Horizontal':

 Style
------------------
 left
 center
 right
 fill
 justify
 centerContinuous
 distributed

The following table shows the type of cells' vertical alignment used in 'Alignment.Vertical':

 Style
------------------
 top
 center
 justify
 distributed

The 'Alignment.ReadingOrder' is an uint64 value indicating whether the reading order of the cell is left-to-right, right-to-left, or context dependent. the valid value of this field was:

 Value | Description
-------+----------------------------------------------------
 0     | Context Dependent - reading order is determined by scanning the
       | text for the first non-whitespace character: if it is a strong
       | right-to-left character, the reading order is right-to-left;
       | otherwise, the reading order left-to-right.
 1     | Left-to-Right: reading order is left-to-right in the cell, as in
       | English.
 2     | Right-to-Left: reading order is right-to-left in the cell, as in
       | Hebrew.

The 'Alignment.RelativeIndent' is an integer value to indicate the additional number of spaces of indentation to adjust for text in a cell.

The following table shows the type of font underline style used in 'Font.Underline':

 Style
------------------
 none
 single
 double

Excel's built-in all languages formats are shown in the following table:

 Index | Format String
-------+----------------------------------------------------
 0     | General
 1     | 0
 2     | 0.00
 3     | #,##0
 4     | #,##0.00
 5     | ($#,##0_);($#,##0)
 6     | ($#,##0_);[Red]($#,##0)
 7     | ($#,##0.00_);($#,##0.00)
 8     | ($#,##0.00_);[Red]($#,##0.00)
 9     | 0%
 10    | 0.00%
 11    | 0.00E+00
 12    | # ?/?
 13    | # ??/??
 14    | m/d/yy
 15    | d-mmm-yy
 16    | d-mmm
 17    | mmm-yy
 18    | h:mm AM/PM
 19    | h:mm:ss AM/PM
 20    | h:mm
 21    | h:mm:ss
 22    | m/d/yy h:mm
 ...   | ...
 37    | (#,##0_);(#,##0)
 38    | (#,##0_);[Red](#,##0)
 39    | (#,##0.00_);(#,##0.00)
 40    | (#,##0.00_);[Red](#,##0.00)
 41    | _(* #,##0_);_(* (#,##0);_(* "-"_);_(@_)
 42    | _($* #,##0_);_($* (#,##0);_($* "-"_);_(@_)
 43    | _(* #,##0.00_);_(* (#,##0.00);_(* "-"??_);_(@_)
 44    | _($* #,##0.00_);_($* (#,##0.00);_($* "-"??_);_(@_)
 45    | mm:ss
 46    | [h]:mm:ss
 47    | mm:ss.0
 48    | ##0.0E+0
 49    | @

Number format code in zh-tw language:

 Index | Symbol
-------+-------------------------------------------
 27    | [$-404]e/m/d
 28    | [$-404]e"年"m"月"d"日"
 29    | [$-404]e"年"m"月"d"日"
 30    | m/d/yy
 31    | yyyy"年"m"月"d"日"
 32    | hh"時"mm"分"
 33    | hh"時"mm"分"ss"秒"
 34    | 上午/下午 hh"時"mm"分"
 35    | 上午/下午 hh"時"mm"分"ss"秒"
 36    | [$-404]e/m/d
 50    | [$-404]e/m/d
 51    | [$-404]e"年"m"月"d"日"
 52    | 上午/下午 hh"時"mm"分"
 53    | 上午/下午 hh"時"mm"分"ss"秒"
 54    | [$-404]e"年"m"月"d"日"
 55    | 上午/下午 hh"時"mm"分"
 56    | 上午/下午 hh"時"mm"分"ss"秒"
 57    | [$-404]e/m/d
 58    | [$-404]e"年"m"月"d"日"

Number format code in zh-cn language:

 Index | Symbol
-------+-------------------------------------------
 27    | yyyy"年"m"月"
 28    | m"月"d"日"
 29    | m"月"d"日"
 30    | m-d-yy
 31    | yyyy"年"m"月"d"日"
 32    | h"时"mm"分"
 33    | h"时"mm"分"ss"秒"
 34    | 上午/下午 h"时"mm"分"
 35    | 上午/下午 h"时"mm"分"ss"秒
 36    | yyyy"年"m"月
 50    | yyyy"年"m"月
 51    | m"月"d"日
 52    | yyyy"年"m"月
 53    | m"月"d"日
 54    | m"月"d"日
 55    | 上午/下午 h"时"mm"分
 56    | 上午/下午 h"时"mm"分"ss"秒
 57    | yyyy"年"m"月
 58    | m"月"d"日"

Number format code in ja-jp language:

 Index | Symbol
-------+-------------------------------------------
 27    | [$-411]ge.m.d
 28    | [$-411]ggge"年"m"月"d"日
 29    | [$-411]ggge"年"m"月"d"日
 30    | m/d/y
 31    | yyyy"年"m"月"d"日
 32    | h"時"mm"分
 33    | h"時"mm"分"ss"秒
 34    | yyyy"年"m"月
 35    | m"月"d"日
 36    | [$-411]ge.m.d
 50    | [$-411]ge.m.d
 51    | [$-411]ggge"年"m"月"d"日
 52    | yyyy"年"m"月
 53    | m"月"d"日
 54    | [$-411]ggge"年"m"月"d"日
 55    | yyyy"年"m"月
 56    | m"月"d"日
 57    | [$-411]ge.m.d
 58    | [$-411]ggge"年"m"月"d"日"

Number format code in ko-kr language:

 Index | Symbol
-------+-------------------------------------------
 27    | yyyy"年" mm"月" dd"日
 28    | mm-d
 29    | mm-d
 30    | mm-dd-y
 31    | yyyy"년" mm"월" dd"일
 32    | h"시" mm"분
 33    | h"시" mm"분" ss"초
 34    | yyyy-mm-d
 35    | yyyy-mm-d
 36    | yyyy"年" mm"月" dd"日
 50    | yyyy"年" mm"月" dd"日
 51    | mm-d
 52    | yyyy-mm-d
 53    | yyyy-mm-d
 54    | mm-d
 55    | yyyy-mm-d
 56    | yyyy-mm-d
 57    | yyyy"年" mm"月" dd"日
 58    | mm-dd

Number format code in th-th language:

 Index | Symbol
-------+-------------------------------------------
 59    | t
 60    | t0.0
 61    | t#,##
 62    | t#,##0.0
 67    | t0
 68    | t0.00
 69    | t# ?/
 70    | t# ??/?
 71    | ว/ด/ปปป
 72    | ว-ดดด-ป
 73    | ว-ดด
 74    | ดดด-ป
 75    | ช:น
 76    | ช:นน:ท
 77    | ว/ด/ปปปป ช:น
 78    | นน:ท
 79    | [ช]:นน:ท
 80    | นน:ทท.
 81    | d/m/bb

Excelize built-in currency formats are shown in the following table, only support these types in the following table (Index number is used only for markup and is not used inside an Excel file and you can't get formatted value by the function GetCellValue) currently:

 Index | Symbol
-------+---------------------------------------------------------------
 164   | ¥
 165   | $ English (United States)
 166   | $ Cherokee (United States)
 167   | $ Chinese (Singapore)
 168   | $ Chinese (Taiwan)
 169   | $ English (Australia)
 170   | $ English (Belize)
 171   | $ English (Canada)
 172   | $ English (Jamaica)
 173   | $ English (New Zealand)
 174   | $ English (Singapore)
 175   | $ English (Trinidad & Tobago)
 176   | $ English (U.S. Virgin Islands)
 177   | $ English (United States)
 178   | $ French (Canada)
 179   | $ Hawaiian (United States)
 180   | $ Malay (Brunei)
 181   | $ Quechua (Ecuador)
 182   | $ Spanish (Chile)
 183   | $ Spanish (Colombia)
 184   | $ Spanish (Ecuador)
 185   | $ Spanish (El Salvador)
 186   | $ Spanish (Mexico)
 187   | $ Spanish (Puerto Rico)
 188   | $ Spanish (United States)
 189   | $ Spanish (Uruguay)
 190   | £ English (United Kingdom)
 191   | £ Scottish Gaelic (United Kingdom)
 192   | £ Welsh (United Kindom)
 193   | ¥ Chinese (China)
 194   | ¥ Japanese (Japan)
 195   | ¥ Sichuan Yi (China)
 196   | ¥ Tibetan (China)
 197   | ¥ Uyghur (China)
 198   | ֏ Armenian (Armenia)
 199   | ؋ Pashto (Afghanistan)
 200   | ؋ Persian (Afghanistan)
 201   | ৳ Bengali (Bangladesh)
 202   | ៛ Khmer (Cambodia)
 203   | ₡ Spanish (Costa Rica)
 204   | ₦ Hausa (Nigeria)
 205   | ₦ Igbo (Nigeria)
 206   | ₩ Korean (South Korea)
 207   | ₪ Hebrew (Israel)
 208   | ₫ Vietnamese (Vietnam)
 209   | € Basque (Spain)
 210   | € Breton (France)
 211   | € Catalan (Spain)
 212   | € Corsican (France)
 213   | € Dutch (Belgium)
 214   | € Dutch (Netherlands)
 215   | € English (Ireland)
 216   | € Estonian (Estonia)
 217   | € Euro (€ 123)
 218   | € Euro (123 €)
 219   | € Finnish (Finland)
 220   | € French (Belgium)
 221   | € French (France)
 222   | € French (Luxembourg)
 223   | € French (Monaco)
 224   | € French (Réunion)
 225   | € Galician (Spain)
 226   | € German (Austria)
 227   | € German (German)
 228   | € German (Luxembourg)
 229   | € Greek (Greece)
 230   | € Inari Sami (Finland)
 231   | € Irish (Ireland)
 232   | € Italian (Italy)
 233   | € Latin (Italy)
 234   | € Latin, Serbian (Montenegro)
 235   | € Larvian (Latvia)
 236   | € Lithuanian (Lithuania)
 237   | € Lower Sorbian (Germany)
 238   | € Luxembourgish (Luxembourg)
 239   | € Maltese (Malta)
 240   | € Northern Sami (Finland)
 241   | € Occitan (France)
 242   | € Portuguese (Portugal)
 243   | € Serbian (Montenegro)
 244   | € Skolt Sami (Finland)
 245   | € Slovak (Slovakia)
 246   | € Slovenian (Slovenia)
 247   | € Spanish (Spain)
 248   | € Swedish (Finland)
 249   | € Swiss German (France)
 250   | € Upper Sorbian (Germany)
 251   | € Western Frisian (Netherlands)
 252   | ₭ Lao (Laos)
 253   | ₮ Mongolian (Mongolia)
 254   | ₮ Mongolian, Mongolian (Mongolia)
 255   | ₱ English (Philippines)
 256   | ₱ Filipino (Philippines)
 257   | ₴ Ukrainian (Ukraine)
 258   | ₸ Kazakh (Kazakhstan)
 259   | ₹ Arabic, Kashmiri (India)
 260   | ₹ English (India)
 261   | ₹ Gujarati (India)
 262   | ₹ Hindi (India)
 263   | ₹ Kannada (India)
 264   | ₹ Kashmiri (India)
 265   | ₹ Konkani (India)
 266   | ₹ Manipuri (India)
 267   | ₹ Marathi (India)
 268   | ₹ Nepali (India)
 269   | ₹ Oriya (India)
 270   | ₹ Punjabi (India)
 271   | ₹ Sanskrit (India)
 272   | ₹ Sindhi (India)
 273   | ₹ Tamil (India)
 274   | ₹ Urdu (India)
 275   | ₺ Turkish (Turkey)
 276   | ₼ Azerbaijani (Azerbaijan)
 277   | ₼ Cyrillic, Azerbaijani (Azerbaijan)
 278   | ₽ Russian (Russia)
 279   | ₽ Sakha (Russia)
 280   | ₾ Georgian (Georgia)
 281   | B/. Spanish (Panama)
 282   | Br Oromo (Ethiopia)
 283   | Br Somali (Ethiopia)
 284   | Br Tigrinya (Ethiopia)
 285   | Bs Quechua (Bolivia)
 286   | Bs Spanish (Bolivia)
 287   | BS. Spanish (Venezuela)
 288   | BWP Tswana (Botswana)
 289   | C$ Spanish (Nicaragua)
 290   | CA$ Latin, Inuktitut (Canada)
 291   | CA$ Mohawk (Canada)
 292   | CA$ Unified Canadian Aboriginal Syllabics, Inuktitut (Canada)
 293   | CFA French (Mali)
 294   | CFA French (Senegal)
 295   | CFA Fulah (Senegal)
 296   | CFA Wolof (Senegal)
 297   | CHF French (Switzerland)
 298   | CHF German (Liechtenstein)
 299   | CHF German (Switzerland)
 300   | CHF Italian (Switzerland)
 301   | CHF Romansh (Switzerland)
 302   | CLP Mapuche (Chile)
 303   | CN¥ Mongolian, Mongolian (China)
 304   | DZD Central Atlas Tamazight (Algeria)
 305   | FCFA French (Cameroon)
 306   | Ft Hungarian (Hungary)
 307   | G French (Haiti)
 308   | Gs. Spanish (Paraguay)
 309   | GTQ K'iche' (Guatemala)
 310   | HK$ Chinese (Hong Kong (China))
 311   | HK$ English (Hong Kong (China))
 312   | HRK Croatian (Croatia)
 313   | IDR English (Indonesia)
 314   | IQD Arbic, Central Kurdish (Iraq)
 315   | ISK Icelandic (Iceland)
 316   | K Burmese (Myanmar (Burma))
 317   | Kč Czech (Czech Republic)
 318   | KM Bosnian (Bosnia & Herzegovina)
 319   | KM Croatian (Bosnia & Herzegovina)
 320   | KM Latin, Serbian (Bosnia & Herzegovina)
 321   | kr Faroese (Faroe Islands)
 322   | kr Northern Sami (Norway)
 323   | kr Northern Sami (Sweden)
 324   | kr Norwegian Bokmål (Norway)
 325   | kr Norwegian Nynorsk (Norway)
 326   | kr Swedish (Sweden)
 327   | kr. Danish (Denmark)
 328   | kr. Kalaallisut (Greenland)
 329   | Ksh Swahili (kenya)
 330   | L Romanian (Moldova)
 331   | L Russian (Moldova)
 332   | L Spanish (Honduras)
 333   | Lekë Albanian (Albania)
 334   | MAD Arabic, Central Atlas Tamazight (Morocco)
 335   | MAD French (Morocco)
 336   | MAD Tifinagh, Central Atlas Tamazight (Morocco)
 337   | MOP$ Chinese (Macau (China))
 338   | MVR Divehi (Maldives)
 339   | Nfk Tigrinya (Eritrea)
 340   | NGN Bini (Nigeria)
 341   | NGN Fulah (Nigeria)
 342   | NGN Ibibio (Nigeria)
 343   | NGN Kanuri (Nigeria)
 344   | NOK Lule Sami (Norway)
 345   | NOK Southern Sami (Norway)
 346   | NZ$ Maori (New Zealand)
 347   | PKR Sindhi (Pakistan)
 348   | PYG Guarani (Paraguay)
 349   | Q Spanish (Guatemala)
 350   | R Afrikaans (South Africa)
 351   | R English (South Africa)
 352   | R Zulu (South Africa)
 353   | R$ Portuguese (Brazil)
 354   | RD$ Spanish (Dominican Republic)
 355   | RF Kinyarwanda (Rwanda)
 356   | RM English (Malaysia)
 357   | RM Malay (Malaysia)
 358   | RON Romanian (Romania)
 359   | Rp Indonesoan (Indonesia)
 360   | Rs Urdu (Pakistan)
 361   | Rs. Tamil (Sri Lanka)
 362   | RSD Latin, Serbian (Serbia)
 363   | RSD Serbian (Serbia)
 364   | RUB Bashkir (Russia)
 365   | RUB Tatar (Russia)
 366   | S/. Quechua (Peru)
 367   | S/. Spanish (Peru)
 368   | SEK Lule Sami (Sweden)
 369   | SEK Southern Sami (Sweden)
 370   | soʻm Latin, Uzbek (Uzbekistan)
 371   | soʻm Uzbek (Uzbekistan)
 372   | SYP Syriac (Syria)
 373   | THB Thai (Thailand)
 374   | TMT Turkmen (Turkmenistan)
 375   | US$ English (Zimbabwe)
 376   | ZAR Northern Sotho (South Africa)
 377   | ZAR Southern Sotho (South Africa)
 378   | ZAR Tsonga (South Africa)
 379   | ZAR Tswana (south Africa)
 380   | ZAR Venda (South Africa)
 381   | ZAR Xhosa (South Africa)
 382   | zł Polish (Poland)
 383   | ден Macedonian (Macedonia)
 384   | KM Cyrillic, Bosnian (Bosnia & Herzegovina)
 385   | KM Serbian (Bosnia & Herzegovina)
 386   | лв. Bulgarian (Bulgaria)
 387   | p. Belarusian (Belarus)
 388   | сом Kyrgyz (Kyrgyzstan)
 389   | сом Tajik (Tajikistan)
 390   | ج.م. Arabic (Egypt)
 391   | د.أ. Arabic (Jordan)
 392   | د.أ. Arabic (United Arab Emirates)
 393   | د.ب. Arabic (Bahrain)
 394   | د.ت. Arabic (Tunisia)
 395   | د.ج. Arabic (Algeria)
 396   | د.ع. Arabic (Iraq)
 397   | د.ك. Arabic (Kuwait)
 398   | د.ل. Arabic (Libya)
 399   | د.م. Arabic (Morocco)
 400   | ر Punjabi (Pakistan)
 401   | ر.س. Arabic (Saudi Arabia)
 402   | ر.ع. Arabic (Oman)
 403   | ر.ق. Arabic (Qatar)
 404   | ر.ي. Arabic (Yemen)
 405   | ریال Persian (Iran)
 406   | ل.س. Arabic (Syria)
 407   | ل.ل. Arabic (Lebanon)
 408   | ብር Amharic (Ethiopia)
 409   | रू Nepaol (Nepal)
 410   | රු. Sinhala (Sri Lanka)
 411   | ADP
 412   | AED
 413   | AFA
 414   | AFN
 415   | ALL
 416   | AMD
 417   | ANG
 418   | AOA
 419   | ARS
 420   | ATS
 421   | AUD
 422   | AWG
 423   | AZM
 424   | AZN
 425   | BAM
 426   | BBD
 427   | BDT
 428   | BEF
 429   | BGL
 430   | BGN
 431   | BHD
 432   | BIF
 433   | BMD
 434   | BND
 435   | BOB
 436   | BOV
 437   | BRL
 438   | BSD
 439   | BTN
 440   | BWP
 441   | BYR
 442   | BZD
 443   | CAD
 444   | CDF
 445   | CHE
 446   | CHF
 447   | CHW
 448   | CLF
 449   | CLP
 450   | CNY
 451   | COP
 452   | COU
 453   | CRC
 454   | CSD
 455   | CUC
 456   | CVE
 457   | CYP
 458   | CZK
 459   | DEM
 460   | DJF
 461   | DKK
 462   | DOP
 463   | DZD
 464   | ECS
 465   | ECV
 466   | EEK
 467   | EGP
 468   | ERN
 469   | ESP
 470   | ETB
 471   | EUR
 472   | FIM
 473   | FJD
 474   | FKP
 475   | FRF
 476   | GBP
 477   | GEL
 478   | GHC
 479   | GHS
 480   | GIP
 481   | GMD
 482   | GNF
 483   | GRD
 484   | GTQ
 485   | GYD
 486   | HKD
 487   | HNL
 488   | HRK
 489   | HTG
 490   | HUF
 491   | IDR
 492   | IEP
 493   | ILS
 494   | INR
 495   | IQD
 496   | IRR
 497   | ISK
 498   | ITL
 499   | JMD
 500   | JOD
 501   | JPY
 502   | KAF
 503   | KES
 504   | KGS
 505   | KHR
 506   | KMF
 507   | KPW
 508   | KRW
 509   | KWD
 510   | KYD
 511   | KZT
 512   | LAK
 513   | LBP
 514   | LKR
 515   | LRD
 516   | LSL
 517   | LTL
 518   | LUF
 519   | LVL
 520   | LYD
 521   | MAD
 522   | MDL
 523   | MGA
 524   | MGF
 525   | MKD
 526   | MMK
 527   | MNT
 528   | MOP
 529   | MRO
 530   | MTL
 531   | MUR
 532   | MVR
 533   | MWK
 534   | MXN
 535   | MXV
 536   | MYR
 537   | MZM
 538   | MZN
 539   | NAD
 540   | NGN
 541   | NIO
 542   | NLG
 543   | NOK
 544   | NPR
 545   | NTD
 546   | NZD
 547   | OMR
 548   | PAB
 549   | PEN
 550   | PGK
 551   | PHP
 552   | PKR
 553   | PLN
 554   | PTE
 555   | PYG
 556   | QAR
 557   | ROL
 558   | RON
 559   | RSD
 560   | RUB
 561   | RUR
 562   | RWF
 563   | SAR
 564   | SBD
 565   | SCR
 566   | SDD
 567   | SDG
 568   | SDP
 569   | SEK
 570   | SGD
 571   | SHP
 572   | SIT
 573   | SKK
 574   | SLL
 575   | SOS
 576   | SPL
 577   | SRD
 578   | SRG
 579   | STD
 580   | SVC
 581   | SYP
 582   | SZL
 583   | THB
 584   | TJR
 585   | TJS
 586   | TMM
 587   | TMT
 588   | TND
 589   | TOP
 590   | TRL
 591   | TRY
 592   | TTD
 593   | TWD
 594   | TZS
 595   | UAH
 596   | UGX
 597   | USD
 598   | USN
 599   | USS
 600   | UYI
 601   | UYU
 602   | UZS
 603   | VEB
 604   | VEF
 605   | VND
 606   | VUV
 607   | WST
 608   | XAF
 609   | XAG
 610   | XAU
 611   | XB5
 612   | XBA
 613   | XBB
 614   | XBC
 615   | XBD
 616   | XCD
 617   | XDR
 618   | XFO
 619   | XFU
 620   | XOF
 621   | XPD
 622   | XPF
 623   | XPT
 624   | XTS
 625   | XXX
 626   | YER
 627   | YUM
 628   | ZAR
 629   | ZMK
 630   | ZMW
 631   | ZWD
 632   | ZWL
 633   | ZWN
 634   | ZWR

Excelize support set custom number format for cell. For example, set number as date type in Uruguay (Spanish) format for Sheet1!A6:

f := excelize.NewFile()
defer func() {
    if err := f.Close(); err != nil {
        fmt.Println(err)
    }
}()
if err := f.SetCellValue("Sheet1", "A6", 42920.5); err != nil {
    fmt.Println(err)
    return
}
exp := "[$-380A]dddd\\,\\ dd\" de \"mmmm\" de \"yyyy;@"
style, err := f.NewStyle(&excelize.Style{CustomNumFmt: &exp})
if err != nil {
    fmt.Println(err)
    return
}
err = f.SetCellStyle("Sheet1", "A6", "A6", style)

Cell Sheet1!A6 in the Excel Application: martes, 04 de Julio de 2017

func (*File) ProtectSheet

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, SHA2-56, 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,
})

func (*File) ProtectWorkbook

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

ProtectWorkbook provides a function to prevent other users from viewing hidden worksheets, adding, moving, deleting, or hiding worksheets, and renaming worksheets 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. The generated workbook only works on Microsoft Office 2007 and later. For example, protect workbook with protection settings:

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

func (*File) ReadZipReader

func (f *File) ReadZipReader(r *zip.Reader) (map[string][]byte, int, error)

ReadZipReader extract spreadsheet with given options.

func (*File) RemoveCol

func (f *File) RemoveCol(sheet, col string) error

RemoveCol provides a function to remove 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.

func (*File) RemovePageBreak

func (f *File) RemovePageBreak(sheet, cell string) error

RemovePageBreak remove a page break by given worksheet name and cell reference.

func (*File) RemoveRow

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

RemoveRow provides a function to remove 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.

func (*File) Rows

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

func (*File) Save

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

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

func (*File) SaveAs

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

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

func (*File) SearchSheet

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.

An example of search the cell reference of the value of "100" on Sheet1:

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

An example of 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)

func (*File) SetActiveSheet

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 than or equal to 0 and less than the total worksheet numbers.

func (*File) SetAppProps

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

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

 Property          | Description
-------------------+--------------------------------------------------------------------------
 Application       | The name of the application that created this document.
                   |
 ScaleCrop         | Indicates the display mode of the document thumbnail. Set this element
                   | to 'true' to enable scaling of the document thumbnail to the display. Set
                   | this element to 'false' to enable cropping of the document thumbnail to
                   | show only sections that will fit the display.
                   |
 DocSecurity       | Security level of a document as a numeric value. Document security is
                   | defined as:
                   | 1 - Document is password protected.
                   | 2 - Document is recommended to be opened as read-only.
                   | 3 - Document is enforced to be opened as read-only.
                   | 4 - Document is locked for annotation.
                   |
 Company           | The name of a company associated with the document.
                   |
 LinksUpToDate     | Indicates whether hyperlinks in a document are up-to-date. Set this
                   | element to 'true' to indicate that hyperlinks are updated. Set this
                   | element to 'false' to indicate that hyperlinks are outdated.
                   |
 HyperlinksChanged | Specifies that one or more hyperlinks in this part were updated
                   | exclusively in this part by a producer. The next producer to open this
                   | document shall update the hyperlink relationships with the new
                   | hyperlinks specified in this part.
                   |
 AppVersion        | Specifies the version of the application which produced this document.
                   | The content of this element shall be of the form XX.YYYY where X and Y
                   | represent numerical values, or the document shall be considered
                   | non-conformant.

For example:

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

func (*File) SetCellBool

func (f *File) SetCellBool(sheet, cell string, value bool) error

SetCellBool provides a function to set bool type value of a cell by given worksheet name, cell reference and cell value.

func (*File) SetCellDefault

func (f *File) SetCellDefault(sheet, cell, value string) error

SetCellDefault provides a function to set string type value of a cell as default format without escaping the cell.

func (*File) SetCellFloat

func (f *File) SetCellFloat(sheet, cell string, value float64, precision, bitSize int) error

SetCellFloat sets a floating point value into a cell. The precision parameter specifies how many places after the decimal will be shown while -1 is a special value that will use as many decimal places as necessary to represent the number. bitSize is 32 or 64 depending on if a float32 or float64 was originally used for the value. For Example:

var x float32 = 1.325
f.SetCellFloat("Sheet1", "A1", float64(x), 2, 32)
Example
f := NewFile()
defer func() {
	if err := f.Close(); err != nil {
		fmt.Println(err)
	}
}()
x := 3.14159265
if err := f.SetCellFloat("Sheet1", "A1", x, 2, 64); err != nil {
	fmt.Println(err)
}
val, err := f.GetCellValue("Sheet1", "A1")
if err != nil {
	fmt.Println(err)
	return
}
fmt.Println(val)
Output:

3.14

func (*File) SetCellFormula

func (f *File) SetCellFormula(sheet, cell, formula string, opts ...FormulaOpts) error

SetCellFormula provides a function to set formula on the cell is taken according to the given worksheet name and cell formula settings. The result of the formula cell can be calculated when the worksheet is opened by the Office Excel application or can be using the "CalcCellValue" function also can get the calculated cell value. If the Excel application doesn't calculate the formula automatically when the workbook has been opened, please call "UpdateLinkedValue" after setting the cell formula functions.

Example 1, set normal formula "=SUM(A1,B1)" for the cell "A3" on "Sheet1":

err := f.SetCellFormula("Sheet1", "A3", "=SUM(A1,B1)")

Example 2, set one-dimensional vertical constant array (column array) formula "1,2,3" for the cell "A3" on "Sheet1":

err := f.SetCellFormula("Sheet1", "A3", "={1;2;3}")

Example 3, set one-dimensional horizontal constant array (row array) formula '"a","b","c"' for the cell "A3" on "Sheet1":

err := f.SetCellFormula("Sheet1", "A3", "={\"a\",\"b\",\"c\"}")

Example 4, set two-dimensional constant array formula '{1,2,"a","b"}' for the cell "A3" on "Sheet1":

formulaType, ref := excelize.STCellFormulaTypeArray, "A3:A3"
err := f.SetCellFormula("Sheet1", "A3", "={1,2;\"a\",\"b\"}",
    excelize.FormulaOpts{Ref: &ref, Type: &formulaType})

Example 5, set range array formula "A1:A2" for the cell "A3" on "Sheet1":

formulaType, ref := excelize.STCellFormulaTypeArray, "A3:A3"
err := f.SetCellFormula("Sheet1", "A3", "=A1:A2",
       excelize.FormulaOpts{Ref: &ref, Type: &formulaType})

Example 6, set shared formula "=A1+B1" for the cell "C1:C5" on "Sheet1", "C1" is the master cell:

formulaType, ref := excelize.STCellFormulaTypeShared, "C1:C5"
err := f.SetCellFormula("Sheet1", "C1", "=A1+B1",
    excelize.FormulaOpts{Ref: &ref, Type: &formulaType})

Example 7, set table formula "=SUM(Table1[[A]:[B]])" for the cell "C2" on "Sheet1":

package main

import (
    "fmt"

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

func main() {
    f := excelize.NewFile()
    defer func() {
        if err := f.Close(); err != nil {
            fmt.Println(err)
        }
    }()
    for idx, row := range [][]interface{}{{"A", "B", "C"}, {1, 2}} {
        if err := f.SetSheetRow("Sheet1", fmt.Sprintf("A%d", idx+1), &row); err != nil {
            fmt.Println(err)
            return
        }
    }
    if err := f.AddTable("Sheet1", &excelize.Table{
        Range: "A1:C2", Name: "Table1", StyleName: "TableStyleMedium2",
    }); err != nil {
        fmt.Println(err)
        return
    }
    formulaType := excelize.STCellFormulaTypeDataTable
    if err := f.SetCellFormula("Sheet1", "C2", "=SUM(Table1[[A]:[B]])",
        excelize.FormulaOpts{Type: &formulaType}); err != nil {
        fmt.Println(err)
        return
    }
    if err := f.SaveAs("Book1.xlsx"); err != nil {
        fmt.Println(err)
    }
}
func (f *File) SetCellHyperLink(sheet, cell, link, linkType string, opts ...HyperlinkOpts) error

SetCellHyperLink provides a function to set cell hyperlink by given worksheet name and link URL address. LinkType defines two types of hyperlink "External" for website or "Location" for moving to one of cell in this workbook. Maximum limit hyperlinks in a worksheet is 65530. This function is only used to set the hyperlink of the cell and doesn't affect the value of the cell. If you need to set the value of the cell, please use the other functions such as `SetCellStyle` or `SetSheetRow`. The below is example for external link.

display, tooltip := "https://github.com/xuri/excelize", "Excelize on GitHub"
if err := f.SetCellHyperLink("Sheet1", "A3",
    display, "External", excelize.HyperlinkOpts{
        Display: &display,
        Tooltip: &tooltip,
    }); err != nil {
    fmt.Println(err)
}
// Set underline and font color style for the cell.
style, err := f.NewStyle(&excelize.Style{
    Font: &excelize.Font{Color: "1265BE", Underline: "single"},
})
if err != nil {
    fmt.Println(err)
}
err = f.SetCellStyle("Sheet1", "A3", "A3", style)

This is another example for "Location":

err := f.SetCellHyperLink("Sheet1", "A3", "Sheet1!A40", "Location")

func (*File) SetCellInt

func (f *File) SetCellInt(sheet, cell string, value int) error

SetCellInt provides a function to set int type value of a cell by given worksheet name, cell reference and cell value.

func (*File) SetCellRichText

func (f *File) SetCellRichText(sheet, cell string, runs []RichTextRun) error

SetCellRichText provides a function to set cell with rich text by given worksheet. For example, set rich text on the A1 cell of the worksheet named Sheet1:

package main

import (
    "fmt"

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

func main() {
    f := excelize.NewFile()
    defer func() {
        if err := f.Close(); err != nil {
            fmt.Println(err)
        }
    }()
    if err := f.SetRowHeight("Sheet1", 1, 35); err != nil {
        fmt.Println(err)
        return
    }
    if err := f.SetColWidth("Sheet1", "A", "A", 44); err != nil {
        fmt.Println(err)
        return
    }
    if err := f.SetCellRichText("Sheet1", "A1", []excelize.RichTextRun{
        {
            Text: "bold",
            Font: &excelize.Font{
                Bold:   true,
                Color:  "2354e8",
                Family: "Times New Roman",
            },
        },
        {
            Text: " and ",
            Font: &excelize.Font{
                Family: "Times New Roman",
            },
        },
        {
            Text: "italic ",
            Font: &excelize.Font{
                Bold:   true,
                Color:  "e83723",
                Italic: true,
                Family: "Times New Roman",
            },
        },
        {
            Text: "text with color and font-family,",
            Font: &excelize.Font{
                Bold:   true,
                Color:  "2354e8",
                Family: "Times New Roman",
            },
        },
        {
            Text: "\r\nlarge text with ",
            Font: &excelize.Font{
                Size:  14,
                Color: "ad23e8",
            },
        },
        {
            Text: "strike",
            Font: &excelize.Font{
                Color:  "e89923",
                Strike: true,
            },
        },
        {
            Text: " superscript",
            Font: &excelize.Font{
                Color:     "dbc21f",
                VertAlign: "superscript",
            },
        },
        {
            Text: " and ",
            Font: &excelize.Font{
                Size:      14,
                Color:     "ad23e8",
                VertAlign: "baseline",
            },
        },
        {
            Text: "underline",
            Font: &excelize.Font{
                Color:     "23e833",
                Underline: "single",
            },
        },
        {
            Text: " subscript.",
            Font: &excelize.Font{
                Color:     "017505",
                VertAlign: "subscript",
            },
        },
    }); err != nil {
        fmt.Println(err)
        return
    }
    style, err := f.NewStyle(&excelize.Style{
        Alignment: &excelize.Alignment{
            WrapText: true,
        },
    })
    if err != nil {
        fmt.Println(err)
        return
    }
    if err := f.SetCellStyle("Sheet1", "A1", "A1", style); err != nil {
        fmt.Println(err)
        return
    }
    if err := f.SaveAs("Book1.xlsx"); err != nil {
        fmt.Println(err)
    }
}

func (*File) SetCellStr

func (f *File) SetCellStr(sheet, cell, value string) error

SetCellStr provides a function to set string type value of a cell. Total number of characters that a cell can contain 32767 characters.

func (*File) SetCellStyle

func (f *File) SetCellStyle(sheet, hCell, vCell string, styleID int) error

SetCellStyle provides a function to add style attribute for cells by given worksheet name, range reference and style ID. This function is concurrency safe. Note that diagonalDown and diagonalUp type border should be use same color in the same range. SetCellStyle will overwrite the existing styles for the cell, it won't append or merge style with existing styles.

For example create a borders of cell H9 on Sheet1:

style, err := f.NewStyle(&excelize.Style{
    Border: []excelize.Border{
        {Type: "left", Color: "0000FF", Style: 3},
        {Type: "top", Color: "00FF00", Style: 4},
        {Type: "bottom", Color: "FFFF00", Style: 5},
        {Type: "right", Color: "FF0000", Style: 6},
        {Type: "diagonalDown", Color: "A020F0", Style: 7},
        {Type: "diagonalUp", Color: "A020F0", Style: 8},
    },
})
if err != nil {
    fmt.Println(err)
}
err = f.SetCellStyle("Sheet1", "H9", "H9", style)

Set gradient fill with vertical variants shading styles for cell H9 on Sheet1:

style, err := f.NewStyle(&excelize.Style{
    Fill: excelize.Fill{Type: "gradient", Color: []string{"FFFFFF", "E0EBF5"}, Shading: 1},
})
if err != nil {
    fmt.Println(err)
}
err = f.SetCellStyle("Sheet1", "H9", "H9", style)

Set solid style pattern fill for cell H9 on Sheet1:

style, err := f.NewStyle(&excelize.Style{
    Fill: excelize.Fill{Type: "pattern", Color: []string{"E0EBF5"}, Pattern: 1},
})
if err != nil {
    fmt.Println(err)
}
err = f.SetCellStyle("Sheet1", "H9", "H9", style)

Set alignment style for cell H9 on Sheet1:

style, err := f.NewStyle(&excelize.Style{
    Alignment: &excelize.Alignment{
        Horizontal:      "center",
        Indent:          1,
        JustifyLastLine: true,
        ReadingOrder:    0,
        RelativeIndent:  1,
        ShrinkToFit:     true,
        TextRotation:    45,
        Vertical:        "",
        WrapText:        true,
    },
})
if err != nil {
    fmt.Println(err)
}
err = f.SetCellStyle("Sheet1", "H9", "H9", style)

Dates and times in Excel are represented by real numbers, for example "Apr 7 2017 12:00 PM" is represented by the number 42920.5. Set date and time format for cell H9 on Sheet1:

f.SetCellValue("Sheet1", "H9", 42920.5)
style, err := f.NewStyle(&excelize.Style{NumFmt: 22})
if err != nil {
    fmt.Println(err)
}
err = f.SetCellStyle("Sheet1", "H9", "H9", style)

Set font style for cell H9 on Sheet1:

style, err := f.NewStyle(&excelize.Style{
    Font: &excelize.Font{
        Bold:   true,
        Italic: true,
        Family: "Times New Roman",
        Size:   36,
        Color:  "777777",
    },
})
if err != nil {
    fmt.Println(err)
}
err = f.SetCellStyle("Sheet1", "H9", "H9", style)

Hide and lock for cell H9 on Sheet1:

style, err := f.NewStyle(&excelize.Style{
    Protection: &excelize.Protection{
        Hidden: true,
        Locked: true,
    },
})
if err != nil {
    fmt.Println(err)
}
err = f.SetCellStyle("Sheet1", "H9", "H9", style)

func (*File) SetCellValue

func (f *File) SetCellValue(sheet, cell string, value interface{}) error

SetCellValue provides a function to set the value of a cell. This function is concurrency safe. The specified coordinates should not be in the first row of the table, a complex number can be set with string text. The following shows the supported data types:

int
int8
int16
int32
int64
uint
uint8
uint16
uint32
uint64
float32
float64
string
[]byte
time.Duration
time.Time
bool
nil

Note that default date format is m/d/yy h:mm of time.Time type value. You can set numbers format by the SetCellStyle function. If you need to set the specialized date in Excel like January 0, 1900 or February 29, 1900, these times can not representation in Go language time.Time data type. Please set the cell value as number 0 or 60, then create and bind the date-time number format style for the cell.

func (*File) SetColOutlineLevel

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. The value of parameter 'level' is 1-7. For example, set outline level of column D in Sheet1 to 2:

err := f.SetColOutlineLevel("Sheet1", "D", 2)

func (*File) SetColStyle

func (f *File) SetColStyle(sheet, columns string, styleID int) error

SetColStyle provides a function to set style of columns by given worksheet name, columns range and style ID. This function is concurrency safe. Note that this will overwrite the existing styles for the columns, it won't append or merge style with existing styles.

For example set style of column H on Sheet1:

err = f.SetColStyle("Sheet1", "H", style)

Set style of columns C:F on Sheet1:

err = f.SetColStyle("Sheet1", "C:F", style)

func (*File) SetColVisible

func (f *File) SetColVisible(sheet, columns string, visible bool) error

SetColVisible provides a function to set visible columns by given worksheet name, columns range and visibility. This function is concurrency safe.

For example hide column D on Sheet1:

err := f.SetColVisible("Sheet1", "D", false)

Hide the columns from D to F (included):

err := f.SetColVisible("Sheet1", "D:F", false)

func (*File) SetColWidth

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:

err := f.SetColWidth("Sheet1", "A", "H", 20)

func (*File) SetConditionalFormat

func (f *File) SetConditionalFormat(sheet, rangeRef string, opts []ConditionalFormatOptions) error

SetConditionalFormat provides a function to create conditional formatting rule for cell value. Conditional formatting is a feature of Excel which allows you to apply a format to a cell or a range of cells based on certain criteria.

The type option is a required parameter and it has no default value. Allowable type values and their associated parameters are:

 Type          | Parameters
---------------+------------------------------------
 cell          | Criteria
               | Value
               | MinValue
               | MaxValue
 date          | Criteria
               | Value
               | MinValue
               | MaxValue
 time_period   | Criteria
 text          | Criteria
               | Value
 average       | Criteria
 duplicate     | (none)
 unique        | (none)
 top           | Criteria
               | Value
 bottom        | Criteria
               | Value
 blanks        | (none)
 no_blanks     | (none)
 errors        | (none)
 no_errors     | (none)
 2_color_scale | MinType
               | MaxType
               | MinValue
               | MaxValue
               | MinColor
               | MaxColor
 3_color_scale | MinType
               | MidType
               | MaxType
               | MinValue
               | MidValue
               | MaxValue
               | MinColor
               | MidColor
               | MaxColor
 data_bar      | MinType
               | MaxType
               | MinValue
               | MaxValue
               | BarBorderColor
               | BarColor
               | BarDirection
               | BarOnly
               | BarSolid
 icon_set      | IconStyle
               | ReverseIcons
               | IconsOnly
 formula       | Criteria

The 'Criteria' parameter is used to set the criteria by which the cell data will be evaluated. It has no default value. The most common criteria as applied to {"type":"cell"} are:

between                  |
not between              |
equal to                 | ==
not equal to             | !=
greater than             | >
less than                | <
greater than or equal to | >=
less than or equal to    | <=

You can either use Excel's textual description strings, in the first column above, or the more common symbolic alternatives.

Additional criteria which are specific to other conditional format types are shown in the relevant sections below.

value: The value is generally used along with the criteria parameter to set the rule by which the cell data will be evaluated:

err := f.SetConditionalFormat("Sheet1", "D1:D10",
    []excelize.ConditionalFormatOptions{
        {
            Type:     "cell",
            Criteria: ">",
            Format:   format,
            Value:    "6",
        },
    },
)

The value property can also be an cell reference:

err := f.SetConditionalFormat("Sheet1", "D1:D10",
    []excelize.ConditionalFormatOptions{
        {
            Type:     "cell",
            Criteria: ">",
            Format:   format,
            Value:    "$C$1",
        },
    },
)

type: format - The format parameter is used to specify the format that will be applied to the cell when the conditional formatting criterion is met. The format is created using the NewConditionalStyle function in the same way as cell formats:

format, err := f.NewConditionalStyle(
    &excelize.Style{
        Font: &excelize.Font{Color: "9A0511"},
        Fill: excelize.Fill{
            Type: "pattern", Color: []string{"FEC7CE"}, Pattern: 1,
        },
    },
)
if err != nil {
    fmt.Println(err)
}
err = f.SetConditionalFormat("Sheet1", "D1:D10",
    []excelize.ConditionalFormatOptions{
        {Type: "cell", Criteria: ">", Format: format, Value: "6"},
    },
)

Note: In Excel, a conditional format is superimposed over the existing cell format and not all cell format properties can be modified. Properties that cannot be modified in a conditional format are font name, font size, superscript and subscript, diagonal borders, all alignment properties and all protection properties.

Excel specifies some default formats to be used with conditional formatting. These can be replicated using the following excelize formats:

// Rose format for bad conditional.
format1, err := f.NewConditionalStyle(
    &excelize.Style{
        Font: &excelize.Font{Color: "9A0511"},
        Fill: excelize.Fill{
            Type: "pattern", Color: []string{"#FEC7CE"}, Pattern: 1,
        },
    },
)

// Light yellow format for neutral conditional.
format2, err := f.NewConditionalStyle(
    &excelize.Style{
        Font: &excelize.Font{Color: "9B5713"},
        Fill: excelize.Fill{
            Type: "pattern", Color: []string{"FEEAA0"}, Pattern: 1,
        },
    },
)

// Light green format for good conditional.
format3, err := f.NewConditionalStyle(
    &excelize.Style{
        Font: &excelize.Font{Color: "09600B"},
        Fill: excelize.Fill{
            Type: "pattern", Color: []string{"C7EECF"}, Pattern: 1,
        },
    },
)

type: MinValue - The 'MinValue' parameter is used to set the lower limiting value when the criteria is either "between" or "not between".

// Highlight cells rules: between...
err := f.SetConditionalFormat("Sheet1", "A1:A10",
    []excelize.ConditionalFormatOptions{
        {
            Type:     "cell",
            Criteria: "between",
            Format:   format,
            MinValue: 6",
            MaxValue: 8",
        },
    },
)

type: MaxValue - The 'MaxValue' parameter is used to set the upper limiting value when the criteria is either "between" or "not between". See the previous example.

type: average - The average type is used to specify Excel's "Average" style conditional format:

// Top/Bottom rules: Above Average...
err := f.SetConditionalFormat("Sheet1", "A1:A10",
    []excelize.ConditionalFormatOptions{
        {
            Type:         "average",
            Criteria:     "=",
            Format:       format1,
            AboveAverage: true,
        },
    },
)

// Top/Bottom rules: Below Average...
err := f.SetConditionalFormat("Sheet1", "B1:B10",
    []excelize.ConditionalFormatOptions{
        {
            Type:         "average",
            Criteria:     "=",
            Format:       format2,
            AboveAverage: false,
        },
    },
)

type: duplicate - The duplicate type is used to highlight duplicate cells in a range:

// Highlight cells rules: Duplicate Values...
err := f.SetConditionalFormat("Sheet1", "A1:A10",
    []excelize.ConditionalFormatOptions{
        {Type: "duplicate", Criteria: "=", Format: format},
    },
)

type: unique - The unique type is used to highlight unique cells in a range:

// Highlight cells rules: Not Equal To...
err := f.SetConditionalFormat("Sheet1", "A1:A10",
    []excelize.ConditionalFormatOptions{
        {Type: "unique", Criteria: "=", Format: format},
    },
)

type: top - The top type is used to specify the top n values by number or percentage in a range:

// Top/Bottom rules: Top 10.
err := f.SetConditionalFormat("Sheet1", "H1:H10",
    []excelize.ConditionalFormatOptions{
        {
            Type:     "top",
            Criteria: "=",
            Format:   format,
            Value:    "6",
        },
    },
)

The criteria can be used to indicate that a percentage condition is required:

err := f.SetConditionalFormat("Sheet1", "A1:A10",
    []excelize.ConditionalFormatOptions{
        {
            Type:     "top",
            Criteria: "=",
            Format:   format,
            Value:    "6",
            Percent:  true,
        },
    },
)

type: 2_color_scale - The 2_color_scale type is used to specify Excel's "2 Color Scale" style conditional format:

// Color scales: 2 color.
err := f.SetConditionalFormat("Sheet1", "A1:A10",
    []excelize.ConditionalFormatOptions{
        {
            Type:     "2_color_scale",
            Criteria: "=",
            MinType:  "min",
            MaxType:  "max",
            MinColor: "#F8696B",
            MaxColor: "#63BE7B",
        },
    },
)

This conditional type can be modified with MinType, MaxType, MinValue, MaxValue, MinColor and MaxColor, see below.

type: 3_color_scale - The 3_color_scale type is used to specify Excel's "3 Color Scale" style conditional format:

// Color scales: 3 color.
err := f.SetConditionalFormat("Sheet1", "A1:A10",
    []excelize.ConditionalFormatOptions{
        {
            Type:     "3_color_scale",
            Criteria: "=",
            MinType:  "min",
            MidType:  "percentile",
            MaxType:  "max",
            MinColor: "#F8696B",
            MidColor: "#FFEB84",
            MaxColor: "#63BE7B",
        },
    },
)

This conditional type can be modified with MinType, MidType, MaxType, MinValue, MidValue, MaxValue, MinColor, MidColor and MaxColor, see below.

type: data_bar - The data_bar type is used to specify Excel's "Data Bar" style conditional format.

MinType - The MinType and MaxType properties are available when the conditional formatting type is 2_color_scale, 3_color_scale or data_bar. The MidType is available for 3_color_scale. The properties are used as follows:

// Data Bars: Gradient Fill.
err := f.SetConditionalFormat("Sheet1", "K1:K10",
    []excelize.ConditionalFormatOptions{
        {
            Type:     "data_bar",
            Criteria: "=",
            MinType:  "min",
            MaxType:  "max",
            BarColor: "#638EC6",
        },
    },
)

The available min/mid/max types are:

min        (for MinType only)
num
percent
percentile
formula
max        (for MaxType only)

MidType - Used for 3_color_scale. Same as MinType, see above.

MaxType - Same as MinType, see above.

MinValue - The MinValue and MaxValue properties are available when the conditional formatting type is 2_color_scale, 3_color_scale or data_bar.

MidValue - The MidValue is available for 3_color_scale. Same as MinValue, see above.

MaxValue - Same as MinValue, see above.

MinColor - The MinColor and MaxColor properties are available when the conditional formatting type is 2_color_scale, 3_color_scale or data_bar.

MidColor - The MidColor is available for 3_color_scale. The properties are used as follows:

// Color scales: 3 color.
err := f.SetConditionalFormat("Sheet1", "B1:B10",
    []excelize.ConditionalFormatOptions{
        {
            Type:     "3_color_scale",
            Criteria: "=",
            MinType:  "min",
            MidType:  "percentile",
            MaxType:  "max",
            MinColor: "#F8696B",
            MidColor: "#FFEB84",
            MaxColor: "#63BE7B",
        },
    },
)

MaxColor - Same as MinColor, see above.

BarColor - Used for data_bar. Same as MinColor, see above.

BarBorderColor - Used for sets the color for the border line of a data bar, this is only visible in Excel 2010 and later.

BarDirection - sets the direction for data bars. The available options are:

context - Data bar direction is set by spreadsheet application based on the context of the data displayed.
leftToRight - Data bar direction is from right to left.
rightToLeft - Data bar direction is from left to right.

BarOnly - Used for set displays a bar data but not the data in the cells.

BarSolid - Used for turns on a solid (non-gradient) fill for data bars, this is only visible in Excel 2010 and later.

IconStyle - The available options are:

3Arrows
3ArrowsGray
3Flags
3Signs
3Symbols
3Symbols2
3TrafficLights1
3TrafficLights2
4Arrows
4ArrowsGray
4Rating
4RedToBlack
4TrafficLights
5Arrows
5ArrowsGray
5Quarters
5Rating

ReverseIcons - Used for set reversed icons sets.

IconsOnly - Used for set displayed without the cell value.

StopIfTrue - used to set the "stop if true" feature of a conditional formatting rule when more than one rule is applied to a cell or a range of cells. When this parameter is set then subsequent rules are not evaluated if the current rule is true.

func (*File) SetDefaultFont

func (f *File) SetDefaultFont(fontName string) error

SetDefaultFont changes the default font in the workbook.

func (*File) SetDefinedName

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 workbook. For example:

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

func (*File) SetDocProps

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

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

 Property       | Description
----------------+-----------------------------------------------------------
 Title          | The name given to the resource.
                |
 Subject        | The topic of the content of the resource.
                |
 Creator        | An entity primarily responsible for making the content of
                | the resource.
                |
 Keywords       | A delimited set of keywords to support searching and
                | indexing. This is typically a list of terms that are not
                | available elsewhere in the properties.
                |
 Description    | An explanation of the content of the resource.
                |
 LastModifiedBy | The user who performed the last modification. The
                | identification is environment-specific.
                |
 Language       | The language of the intellectual content of the resource.
                |
 Identifier     | An unambiguous reference to the resource within a given
                | context.
                |
 Revision       | The topic of the content of the resource.
                |
 ContentStatus  | The status of the content. For example: Values might
                | include "Draft", "Reviewed" and "Final"
                |
 Category       | A categorization of the content of this package.
                |
 Version        | The version number. This value is set by the user or by
                | the application.
                |
 Created        | The created time of the content of the resource which
                | represent in ISO 8601 UTC format, for example
                | "2019-06-04T22:00:10Z".
                |
 Modified       | The modified time of the content of the resource which
                | represent in ISO 8601 UTC format, for example
                | "2019-06-04T22:00:10Z".
                |

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

func (*File) SetHeaderFooter

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:

 Fields           | Description
------------------+-----------------------------------------------------------
 AlignWithMargins | Align header footer margins with page margins
 DifferentFirst   | Different first-page header and footer indicator
 DifferentOddEven | Different odd and even page headers and footers indicator
 ScaleWithDoc     | Scale header and footer with document scaling
 OddFooter        | Odd Page Footer
 OddHeader        | Odd Header
 EvenFooter       | Even Page Footer
 EvenHeader       | Even Page Header
 FirstFooter      | First Page Footer
 FirstHeader      | First Page Header

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

 Formatting Code        | Description
------------------------+-------------------------------------------------------------------------
 &&                     | The character "&"
                        |
 &font-size             | Size of the text font, where font-size is a decimal font size in points
                        |
 &"font name,font type" | A text font-name string, font name, and a text font-type string,
                        | font type
                        |
 &"-,Regular"           | Regular text format. Toggles bold and italic modes to off
                        |
 &A                     | Current worksheet's tab name
                        |
 &B or &"-,Bold"        | Bold text format, from off to on, or vice versa. The default mode is off
                        |
 &D                     | Current date
                        |
 &C                     | Center section
                        |
 &E                     | Double-underline text format
                        |
 &F                     | Current workbook's file name
                        |
 &G                     | Drawing object as background (Not support currently)
                        |
 &H                     | Shadow text format
                        |
 &I or &"-,Italic"      | Italic text format
                        |
 &K                     | Text font color
                        |
                        | An RGB Color is specified as RRGGBB
                        |
                        | A Theme Color is specified as TTSNNN where TT is the theme color Id,
                        | S is either "+" or "-" of the tint/shade value, and NNN is the
                        | tint/shade value
                        |
 &L                     | Left section
                        |
 &N                     | Total number of pages
                        |
 &O                     | Outline text format
                        |
 &P[[+|-]n]             | Without the optional suffix, the current page number in decimal
                        |
 &R                     | Right section
                        |
 &S                     | Strike through text format
                        |
 &T                     | Current time
                        |
 &U                     | Single-underline text format. If double-underline mode is on, the next
                        | occurrence in a section specifier toggles double-underline mode to off;
                        | otherwise, it toggles single-underline mode, from off to on, or vice
                        | versa. The default mode is off
                        |
 &X                     | Superscript text format
                        |
 &Y                     | Subscript text format
                        |
 &Z                     | Current workbook's file path

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

func (*File) SetPageLayout

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

SetPageLayout provides a function to sets worksheet page layout.

The following shows the paper size sorted by excelize index number:

 Index | Paper Size
-------+-----------------------------------------------
   1   | Letter paper (8.5 in. by 11 in.)
   2   | Letter small paper (8.5 in. by 11 in.)
   3   | Tabloid paper (11 in. by 17 in.)
   4   | Ledger paper (17 in. by 11 in.)
   5   | Legal paper (8.5 in. by 14 in.)
   6   | Statement paper (5.5 in. by 8.5 in.)
   7   | Executive paper (7.25 in. by 10.5 in.)
   8   | A3 paper (297 mm by 420 mm)
   9   | A4 paper (210 mm by 297 mm)
   10  | A4 small paper (210 mm by 297 mm)
   11  | A5 paper (148 mm by 210 mm)
   12  | B4 paper (250 mm by 353 mm)
   13  | B5 paper (176 mm by 250 mm)
   14  | Folio paper (8.5 in. by 13 in.)
   15  | Quarto paper (215 mm by 275 mm)
   16  | Standard paper (10 in. by 14 in.)
   17  | Standard paper (11 in. by 17 in.)
   18  | Note paper (8.5 in. by 11 in.)
   19  | #9 envelope (3.875 in. by 8.875 in.)
   20  | #10 envelope (4.125 in. by 9.5 in.)
   21  | #11 envelope (4.5 in. by 10.375 in.)
   22  | #12 envelope (4.75 in. by 11 in.)
   23  | #14 envelope (5 in. by 11.5 in.)
   24  | C paper (17 in. by 22 in.)
   25  | D paper (22 in. by 34 in.)
   26  | E paper (34 in. by 44 in.)
   27  | DL envelope (110 mm by 220 mm)
   28  | C5 envelope (162 mm by 229 mm)
   29  | C3 envelope (324 mm by 458 mm)
   30  | C4 envelope (229 mm by 324 mm)
   31  | C6 envelope (114 mm by 162 mm)
   32  | C65 envelope (114 mm by 229 mm)
   33  | B4 envelope (250 mm by 353 mm)
   34  | B5 envelope (176 mm by 250 mm)
   35  | B6 envelope (176 mm by 125 mm)
   36  | Italy envelope (110 mm by 230 mm)
   37  | Monarch envelope (3.875 in. by 7.5 in.).
   38  | 6 3/4 envelope (3.625 in. by 6.5 in.)
   39  | US standard fanfold (14.875 in. by 11 in.)
   40  | German standard fanfold (8.5 in. by 12 in.)
   41  | German legal fanfold (8.5 in. by 13 in.)
   42  | ISO B4 (250 mm by 353 mm)
   43  | Japanese postcard (100 mm by 148 mm)
   44  | Standard paper (9 in. by 11 in.)
   45  | Standard paper (10 in. by 11 in.)
   46  | Standard paper (15 in. by 11 in.)
   47  | Invite envelope (220 mm by 220 mm)
   50  | Letter extra paper (9.275 in. by 12 in.)
   51  | Legal extra paper (9.275 in. by 15 in.)
   52  | Tabloid extra paper (11.69 in. by 18 in.)
   53  | A4 extra paper (236 mm by 322 mm)
   54  | Letter transverse paper (8.275 in. by 11 in.)
   55  | A4 transverse paper (210 mm by 297 mm)
   56  | Letter extra transverse paper (9.275 in. by 12 in.)
   57  | SuperA/SuperA/A4 paper (227 mm by 356 mm)
   58  | SuperB/SuperB/A3 paper (305 mm by 487 mm)
   59  | Letter plus paper (8.5 in. by 12.69 in.)
   60  | A4 plus paper (210 mm by 330 mm)
   61  | A5 transverse paper (148 mm by 210 mm)
   62  | JIS B5 transverse paper (182 mm by 257 mm)
   63  | A3 extra paper (322 mm by 445 mm)
   64  | A5 extra paper (174 mm by 235 mm)
   65  | ISO B5 extra paper (201 mm by 276 mm)
   66  | A2 paper (420 mm by 594 mm)
   67  | A3 transverse paper (297 mm by 420 mm)
   68  | A3 extra transverse paper (322 mm by 445 mm)
   69  | Japanese Double Postcard (200 mm x 148 mm)
   70  | A6 (105 mm x 148 mm)
   71  | Japanese Envelope Kaku #2
   72  | Japanese Envelope Kaku #3
   73  | Japanese Envelope Chou #3
   74  | Japanese Envelope Chou #4
   75  | Letter Rotated (11in x 8 1/2 11 in)
   76  | A3 Rotated (420 mm x 297 mm)
   77  | A4 Rotated (297 mm x 210 mm)
   78  | A5 Rotated (210 mm x 148 mm)
   79  | B4 (JIS) Rotated (364 mm x 257 mm)
   80  | B5 (JIS) Rotated (257 mm x 182 mm)
   81  | Japanese Postcard Rotated (148 mm x 100 mm)
   82  | Double Japanese Postcard Rotated (148 mm x 200 mm)
   83  | A6 Rotated (148 mm x 105 mm)
   84  | Japanese Envelope Kaku #2 Rotated
   85  | Japanese Envelope Kaku #3 Rotated
   86  | Japanese Envelope Chou #3 Rotated
   87  | Japanese Envelope Chou #4 Rotated
   88  | B6 (JIS) (128 mm x 182 mm)
   89  | B6 (JIS) Rotated (182 mm x 128 mm)
   90  | (12 in x 11 in)
   91  | Japanese Envelope You #4
   92  | Japanese Envelope You #4 Rotated
   93  | PRC 16K (146 mm x 215 mm)
   94  | PRC 32K (97 mm x 151 mm)
   95  | PRC 32K(Big) (97 mm x 151 mm)
   96  | PRC Envelope #1 (102 mm x 165 mm)
   97  | PRC Envelope #2 (102 mm x 176 mm)
   98  | PRC Envelope #3 (125 mm x 176 mm)
   99  | PRC Envelope #4 (110 mm x 208 mm)
   100 | PRC Envelope #5 (110 mm x 220 mm)
   101 | PRC Envelope #6 (120 mm x 230 mm)
   102 | PRC Envelope #7 (160 mm x 230 mm)
   103 | PRC Envelope #8 (120 mm x 309 mm)
   104 | PRC Envelope #9 (229 mm x 324 mm)
   105 | PRC Envelope #10 (324 mm x 458 mm)
   106 | PRC 16K Rotated
   107 | PRC 32K Rotated
   108 | PRC 32K(Big) Rotated
   109 | PRC Envelope #1 Rotated (165 mm x 102 mm)
   110 | PRC Envelope #2 Rotated (176 mm x 102 mm)
   111 | PRC Envelope #3 Rotated (176 mm x 125 mm)
   112 | PRC Envelope #4 Rotated (208 mm x 110 mm)
   113 | PRC Envelope #5 Rotated (220 mm x 110 mm)
   114 | PRC Envelope #6 Rotated (230 mm x 120 mm)
   115 | PRC Envelope #7 Rotated (230 mm x 160 mm)
   116 | PRC Envelope #8 Rotated (309 mm x 120 mm)
   117 | PRC Envelope #9 Rotated (324 mm x 229 mm)
   118 | PRC Envelope #10 Rotated (458 mm x 324 mm)

func (*File) SetPageMargins

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

SetPageMargins provides a function to set worksheet page margins.

func (*File) SetPanes

func (f *File) SetPanes(sheet string, panes *Panes) error

SetPanes provides a function to create and remove freeze panes and split panes by given worksheet name and panes options.

ActivePane defines the pane that is active. The possible values for this attribute are defined in the following table:

 Enumeration Value               | Description
---------------------------------+-------------------------------------------------------------
 bottomLeft (Bottom Left Pane)   | Bottom left pane, when both vertical and horizontal
                                 | splits are applied.
                                 |
                                 | This value is also used when only a horizontal split has
                                 | been applied, dividing the pane into upper and lower
                                 | regions. In that case, this value specifies the bottom
                                 | pane.
                                 |
 bottomRight (Bottom Right Pane) | Bottom right pane, when both vertical and horizontal
                                 | splits are applied.
                                 |
 topLeft (Top Left Pane)         | Top left pane, when both vertical and horizontal splits
                                 | are applied.
                                 |
                                 | This value is also used when only a horizontal split has
                                 | been applied, dividing the pane into upper and lower
                                 | regions. In that case, this value specifies the top pane.
                                 |
                                 | This value is also used when only a vertical split has
                                 | been applied, dividing the pane into right and left
                                 | regions. In that case, this value specifies the left pane
                                 |
 topRight (Top Right Pane)       | Top right pane, when both vertical and horizontal
                                 | splits are applied.
                                 |
                                 | This value is also used when only a vertical split has
                                 | been applied, dividing the pane into right and left
                                 | regions. In that case, this value specifies the right
                                 | pane.

Pane state type is restricted to the values supported currently listed in the following table:

 Enumeration Value               | Description
---------------------------------+-------------------------------------------------------------
 frozen (Frozen)                 | Panes are frozen, but were not split being frozen. In
                                 | this state, when the panes are unfrozen again, a single
                                 | pane results, with no split.
                                 |
                                 | In this state, the split bars are not adjustable.
                                 |
 split (Split)                   | Panes are split, but not frozen. In this state, the split
                                 | bars are adjustable by the user.

XSplit (Horizontal Split Position): Horizontal position of the split, in 1/20th of a point; 0 (zero) if none. If the pane is frozen, this value indicates the number of columns visible in the top pane.

YSplit (Vertical Split Position): Vertical position of the split, in 1/20th of a point; 0 (zero) if none. If the pane is frozen, this value indicates the number of rows visible in the left pane. The possible values for this attribute are defined by the W3C XML Schema double datatype.

TopLeftCell: Location of the top left visible cell in the bottom right pane (when in Left-To-Right mode).

SQRef (Sequence of References): Range of the selection. Can be non-contiguous set of ranges.

An example of how to freeze column A in the Sheet1 and set the active cell on Sheet1!K16:

err := f.SetPanes("Sheet1", &excelize.Panes{
    Freeze:      true,
    Split:       false,
    XSplit:      1,
    YSplit:      0,
    TopLeftCell: "B1",
    ActivePane:  "topRight",
    Selection: []excelize.Selection{
        {SQRef: "K16", ActiveCell: "K16", Pane: "topRight"},
    },
})

An example of how to freeze rows 1 to 9 in the Sheet1 and set the active cell ranges on Sheet1!A11:XFD11:

err := f.SetPanes("Sheet1", &excelize.Panes{
    Freeze:      true,
    Split:       false,
    XSplit:      0,
    YSplit:      9,
    TopLeftCell: "A34",
    ActivePane:  "bottomLeft",
    Selection: []excelize.Selection{
        {SQRef: "A11:XFD11", ActiveCell: "A11", Pane: "bottomLeft"},
    },
})

An example of how to create split panes in the Sheet1 and set the active cell on Sheet1!J60:

err := f.SetPanes("Sheet1", &excelize.Panes{
    Freeze:      false,
    Split:       true,
    XSplit:      3270,
    YSplit:      1800,
    TopLeftCell: "N57",
    ActivePane:  "bottomLeft",
    Selection: []excelize.Selection{
        {SQRef: "I36", ActiveCell: "I36"},
        {SQRef: "G33", ActiveCell: "G33", Pane: "topRight"},
        {SQRef: "J60", ActiveCell: "J60", Pane: "bottomLeft"},
        {SQRef: "O60", ActiveCell: "O60", Pane: "bottomRight"},
    },
})

An example of how to unfreeze and remove all panes on Sheet1:

err := f.SetPanes("Sheet1", &excelize.Panes{Freeze: false, Split: false})

func (*File) SetRowHeight

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)

func (*File) SetRowOutlineLevel

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. The value of parameter 'level' is 1-7. For example, outline row 2 in Sheet1 to level 1:

err := f.SetRowOutlineLevel("Sheet1", 2, 1)

func (*File) SetRowStyle

func (f *File) SetRowStyle(sheet string, start, end, styleID int) error

SetRowStyle provides a function to set the style of rows by given worksheet name, row range, and style ID. Note that this will overwrite the existing styles for the rows, it won't append or merge style with existing styles.

For example set style of row 1 on Sheet1:

err := f.SetRowStyle("Sheet1", 1, 1, styleID)

Set style of rows 1 to 10 on Sheet1:

err := f.SetRowStyle("Sheet1", 1, 10, styleID)

func (*File) SetRowVisible

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 Excel row number. For example, hide row 2 in Sheet1:

err := f.SetRowVisible("Sheet1", 2, false)

func (*File) SetSheetBackground

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 (*File) SetSheetBackgroundFromBytes

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.

func (*File) SetSheetCol

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

func (*File) SetSheetDimension

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.

func (*File) SetSheetName

func (f *File) SetSheetName(source, target string) error

SetSheetName provides a function to set the worksheet name by given 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 problem formula error or reference missing.

func (*File) SetSheetProps

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

SetSheetProps provides a function to set worksheet properties.

func (*File) SetSheetRow

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

func (*File) SetSheetView

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

SetSheetView sets sheet view options. The viewIndex may be negative and if so is counted backward (-1 is the last view).

func (*File) SetSheetVisible

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)

func (*File) SetWorkbookProps

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

SetWorkbookProps provides a function to sets workbook properties.

func (*File) UngroupSheets

func (f *File) UngroupSheets() error

UngroupSheets provides a function to ungroup worksheets.

func (*File) UnmergeCell

func (f *File) UnmergeCell(sheet, hCell, vCell string) error

UnmergeCell provides a function to unmerge a given range reference. For example unmerge range reference D3:E9 on Sheet1:

err := f.UnmergeCell("Sheet1", "D3", "E9")

Attention: overlapped range will also be unmerged.

func (*File) UnprotectSheet

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.

func (*File) UnprotectWorkbook

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.

func (*File) UnsetConditionalFormat

func (f *File) UnsetConditionalFormat(sheet, rangeRef string) error

UnsetConditionalFormat provides a function to unset the conditional format by given worksheet name and range reference.

func (*File) UpdateLinkedValue

func (f *File) UpdateLinkedValue() error

UpdateLinkedValue fix linked values within a spreadsheet are not updating in Office Excel application. This function will be remove value tag when met a cell have a linked value. Reference https://social.technet.microsoft.com/Forums/office/en-US/e16bae1f-6a2c-4325-8013-e989a3479066/excel-2010-linked-cells-not-updating

Notice: after opening generated workbook, Excel will update the linked value and generate a new value and will prompt to save the file or not.

For example:

<row r="19" spans="2:2">
    <c r="B19">
        <f>SUM(Sheet2!D2,Sheet2!D11)</f>
        <v>100</v>
     </c>
</row>

to

<row r="19" spans="2:2">
    <c r="B19">
        <f>SUM(Sheet2!D2,Sheet2!D11)</f>
    </c>
</row>

func (*File) Write

func (f *File) Write(w io.Writer, opts ...Options) error

Write provides a function to write to an io.Writer.

func (*File) WriteTo

func (f *File) WriteTo(w io.Writer, opts ...Options) (int64, error)

WriteTo implements io.WriterTo to write the file.

func (*File) WriteToBuffer

func (f *File) WriteToBuffer() (*bytes.Buffer, error)

WriteToBuffer provides a function to get bytes.Buffer from the saved file, and it allocates space in memory. Be careful when the file size is large.

type Fill

type Fill struct {
	Type    string
	Pattern int
	Color   []string
	Shading int
}

Fill directly maps the fill settings of the cells.

type Font

type Font struct {
	Bold         bool
	Italic       bool
	Underline    string
	Family       string
	Size         float64
	Strike       bool
	Color        string
	ColorIndexed int
	ColorTheme   *int
	ColorTint    float64
	VertAlign    string
}

Font directly maps the font settings of the fonts.

type FormControl

type FormControl struct {
	Cell         string
	Macro        string
	Width        uint
	Height       uint
	Checked      bool
	CurrentVal   uint
	MinVal       uint
	MaxVal       uint
	IncChange    uint
	PageChange   uint
	Horizontally bool
	CellLink     string
	Text         string
	Paragraph    []RichTextRun
	Type         FormControlType
	Format       GraphicOptions
}

FormControl directly maps the form controls information.

type FormControlType

type FormControlType byte

FormControlType is the type of supported form controls.

const (
	FormControlNote FormControlType = iota
	FormControlButton
	FormControlOptionButton
	FormControlSpinButton
	FormControlCheckBox
	FormControlGroupBox
	FormControlLabel
	FormControlScrollBar
)

This section defines the currently supported form control types enumeration.

type FormulaOpts

type FormulaOpts struct {
	Type *string // Formula type
	Ref  *string // Shared formula ref
}

FormulaOpts can be passed to SetCellFormula to use other formula types.

type GraphicOptions

type GraphicOptions struct {
	AltText         string
	PrintObject     *bool
	Locked          *bool
	LockAspectRatio bool
	AutoFit         bool
	OffsetX         int
	OffsetY         int
	ScaleX          float64
	ScaleY          float64
	Hyperlink       string
	HyperlinkType   string
	Positioning     string
}

GraphicOptions directly maps the format settings of the picture.

type HSL

type HSL struct {
	H, S, L float64
}

HSL represents a cylindrical coordinate of points in an RGB color model.

Values are in the range 0 to 1.

func (HSL) RGBA

func (c HSL) RGBA() (uint32, uint32, uint32, uint32)

RGBA returns the alpha-premultiplied red, green, blue and alpha values for the HSL.

type HeaderFooterOptions

type HeaderFooterOptions struct {
	AlignWithMargins bool
	DifferentFirst   bool
	DifferentOddEven bool
	ScaleWithDoc     bool
	OddHeader        string
	OddFooter        string
	EvenHeader       string
	EvenFooter       string
	FirstHeader      string
	FirstFooter      string
}

HeaderFooterOptions directly maps the settings of header and footer.

type HyperlinkOpts

type HyperlinkOpts struct {
	Display *string
	Tooltip *string
}

HyperlinkOpts can be passed to SetCellHyperlink to set optional hyperlink attributes (e.g. display value)

type KeyData

type KeyData struct {
	SaltSize        int    `xml:"saltSize,attr"`
	BlockSize       int    `xml:"blockSize,attr"`
	KeyBits         int    `xml:"keyBits,attr"`
	HashSize        int    `xml:"hashSize,attr"`
	CipherAlgorithm string `xml:"cipherAlgorithm,attr"`
	CipherChaining  string `xml:"cipherChaining,attr"`
	HashAlgorithm   string `xml:"hashAlgorithm,attr"`
	SaltValue       string `xml:"saltValue,attr"`
}

KeyData specifies the cryptographic attributes used to encrypt the data.

type KeyEncryptor

type KeyEncryptor struct {
	XMLName      xml.Name     `xml:"keyEncryptor"`
	URI          string       `xml:"uri,attr"`
	EncryptedKey EncryptedKey `xml:"encryptedKey"`
}

KeyEncryptor specifies that the schema used by this encryptor is the schema specified for password-based encryptors.

type KeyEncryptors

type KeyEncryptors struct {
	KeyEncryptor []KeyEncryptor `xml:"keyEncryptor"`
}

KeyEncryptors specifies the key encryptors used to encrypt the data.

type MergeCell

type MergeCell []string

MergeCell define a merged cell data. It consists of the following structure. example: []string{"D4:E10", "cell value"}

func (*MergeCell) GetCellValue

func (m *MergeCell) GetCellValue() string

GetCellValue returns merged cell value.

func (*MergeCell) GetEndAxis

func (m *MergeCell) GetEndAxis() string

GetEndAxis returns the bottom right cell reference of merged range, for example: "D4".

func (*MergeCell) GetStartAxis

func (m *MergeCell) GetStartAxis() string

GetStartAxis returns the top left cell reference of merged range, for example: "C2".

type Options

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

Options define the options for opening and reading the spreadsheet.

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

type PageLayoutMarginsOptions

type PageLayoutMarginsOptions struct {
	Bottom       *float64
	Footer       *float64
	Header       *float64
	Left         *float64
	Right        *float64
	Top          *float64
	Horizontally *bool
	Vertically   *bool
}

PageLayoutMarginsOptions directly maps the settings of page layout margins.

type PageLayoutOptions

type PageLayoutOptions struct {
	// Size defines the paper size of the worksheet.
	Size *int
	// Orientation defines the orientation of page layout for a worksheet.
	Orientation *string
	// FirstPageNumber specified the first printed page number. If no value is
	// specified, then 'automatic' is assumed.
	FirstPageNumber *uint
	// AdjustTo defines the print scaling. This attribute is restricted to
	// value ranging from 10 (10%) to 400 (400%). This setting is overridden
	// when fitToWidth and/or fitToHeight are in use.
	AdjustTo *uint
	// FitToHeight specified the number of vertical pages to fit on.
	FitToHeight *int
	// FitToWidth specified the number of horizontal pages to fit on.
	FitToWidth *int
	// BlackAndWhite specified print black and white.
	BlackAndWhite *bool
}

PageLayoutOptions directly maps the settings of page layout.

type Panes

type Panes struct {
	Freeze      bool
	Split       bool
	XSplit      int
	YSplit      int
	TopLeftCell string
	ActivePane  string
	Selection   []Selection
}

Panes directly maps the settings of the panes.

type Picture

type Picture struct {
	Extension string
	File      []byte
	Format    *GraphicOptions
}

Picture maps the format settings of the picture.

type PivotTableField

type PivotTableField struct {
	Compact         bool
	Data            string
	Name            string
	Outline         bool
	Subtotal        string
	DefaultSubtotal bool
}

PivotTableField directly maps the field settings of the pivot table. Subtotal specifies the aggregation function that applies to this data field. The default value is sum. The possible values for this attribute are:

Average
Count
CountNums
Max
Min
Product
StdDev
StdDevp
Sum
Var
Varp

Name specifies the name of the data field. Maximum 255 characters are allowed in data field name, excess characters will be truncated.

type PivotTableOptions

type PivotTableOptions struct {
	DataRange           string
	PivotTableRange     string
	Rows                []PivotTableField
	Columns             []PivotTableField
	Data                []PivotTableField
	Filter              []PivotTableField
	RowGrandTotals      bool
	ColGrandTotals      bool
	ShowDrill           bool
	UseAutoFormatting   bool
	PageOverThenDown    bool
	MergeItem           bool
	CompactData         bool
	ShowError           bool
	ShowRowHeaders      bool
	ShowColHeaders      bool
	ShowRowStripes      bool
	ShowColStripes      bool
	ShowLastColumn      bool
	PivotTableStyleName string
	// contains filtered or unexported fields
}

PivotTableOptions directly maps the format settings of the pivot table.

PivotTableStyleName: The built-in pivot table style names

PivotStyleLight1 - PivotStyleLight28
PivotStyleMedium1 - PivotStyleMedium28
PivotStyleDark1 - PivotStyleDark28

type Protection

type Protection struct {
	Hidden bool
	Locked bool
}

Protection directly maps the protection settings of the cells.

type RichTextRun

type RichTextRun struct {
	Font *Font
	Text string
}

RichTextRun directly maps the settings of the rich text run.

type RowOpts

type RowOpts struct {
	Height       float64
	Hidden       bool
	StyleID      int
	OutlineLevel int
}

RowOpts define the options for the set row, it can be used directly in StreamWriter.SetRow to specify the style and properties of the row.

type Rows

type Rows struct {
	// contains filtered or unexported fields
}

Rows defines an iterator to a sheet.

func (*Rows) Close

func (rows *Rows) Close() error

Close closes the open worksheet XML file in the system temporary directory.

func (*Rows) 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.

func (*Rows) Error

func (rows *Rows) Error() error

Error will return the error when the error occurs.

func (*Rows) GetRowOpts

func (rows *Rows) GetRowOpts() RowOpts

GetRowOpts will return the RowOpts of the current row.

func (*Rows) Next

func (rows *Rows) Next() bool

Next will return true if it finds the next row element.

type Selection

type Selection struct {
	SQRef      string
	ActiveCell string
	Pane       string
}

Selection directly maps the settings of the worksheet selection.

type Shape

type Shape struct {
	Cell      string
	Type      string
	Macro     string
	Width     uint
	Height    uint
	Format    GraphicOptions
	Fill      Fill
	Line      ShapeLine
	Paragraph []RichTextRun
}

Shape directly maps the format settings of the shape.

type ShapeColor

type ShapeColor struct {
	Line   string
	Fill   string
	Effect string
}

ShapeColor directly maps the color settings of the shape.

type ShapeLine

type ShapeLine struct {
	Color string
	Width *float64
}

ShapeLine directly maps the line settings of the shape.

type SheetPropsOptions

type SheetPropsOptions struct {
	// Specifies a stable name of the sheet, which should not change over time,
	// and does not change from user input. This name should be used by code
	// to reference a particular sheet.
	CodeName *string
	// EnableFormatConditionsCalculation indicating whether the conditional
	// formatting calculations shall be evaluated. If set to false, then the
	// min/max values of color scales or data bars or threshold values in Top N
	// rules shall not be updated. Essentially the conditional
	// formatting "calc" is off.
	EnableFormatConditionsCalculation *bool
	// Published indicating whether the worksheet is published.
	Published *bool
	// AutoPageBreaks indicating whether the sheet displays Automatic Page
	// Breaks.
	AutoPageBreaks *bool
	// FitToPage indicating whether the Fit to Page print option is enabled.
	FitToPage *bool
	// TabColorIndexed represents the indexed color value.
	TabColorIndexed *int
	// TabColorRGB represents the standard Alpha Red Green Blue color value.
	TabColorRGB *string
	// TabColorTheme represents the zero-based index into the collection,
	// referencing a particular value expressed in the Theme part.
	TabColorTheme *int
	// TabColorTint specifies the tint value applied to the color.
	TabColorTint *float64
	// OutlineSummaryBelow indicating whether summary rows appear below detail
	// in an outline, when applying an outline.
	OutlineSummaryBelow *bool
	// OutlineSummaryRight indicating whether summary columns appear to the
	// right of detail in an outline, when applying an outline.
	OutlineSummaryRight *bool
	// BaseColWidth specifies the number of characters of the maximum digit
	// width of the normal style's font. This value does not include margin
	// padding or extra padding for grid lines. It is only the number of
	// characters.
	BaseColWidth *uint8
	// DefaultColWidth specifies the default column width measured as the
	// number of characters of the maximum digit width of the normal style's
	// font.
	DefaultColWidth *float64
	// DefaultRowHeight specifies the default row height measured in point
	// size. Optimization so we don't have to write the height on all rows.
	// This can be written out if most rows have custom height, to achieve the
	// optimization.
	DefaultRowHeight *float64
	// CustomHeight specifies the custom height.
	CustomHeight *bool
	// ZeroHeight specifies if rows are hidden.
	ZeroHeight *bool
	// ThickTop specifies if rows have a thick top border by default.
	ThickTop *bool
	// ThickBottom specifies if rows have a thick bottom border by default.
	ThickBottom *bool
}

SheetPropsOptions directly maps the settings of sheet view.

type SheetProtectionOptions

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
}

SheetProtectionOptions directly maps the settings of worksheet protection.

type SparklineOptions

type SparklineOptions struct {
	Location      []string
	Range         []string
	Max           int
	CustMax       int
	Min           int
	CustMin       int
	Type          string
	Weight        float64
	DateAxis      bool
	Markers       bool
	High          bool
	Low           bool
	First         bool
	Last          bool
	Negative      bool
	Axis          bool
	Hidden        bool
	Reverse       bool
	Style         int
	SeriesColor   string
	NegativeColor string
	MarkersColor  string
	FirstColor    string
	LastColor     string
	HightColor    string
	LowColor      string
	EmptyCells    string
}

SparklineOptions directly maps the settings of the sparkline.

type Stack

type Stack struct {
	// contains filtered or unexported fields
}

Stack defined an abstract data type that serves as a collection of elements.

func NewStack

func NewStack() *Stack

NewStack create a new stack.

func (*Stack) Empty

func (stack *Stack) Empty() bool

Empty the stack.

func (*Stack) Len

func (stack *Stack) Len() int

Len return the number of items in the stack.

func (*Stack) Peek

func (stack *Stack) Peek() interface{}

Peek view the top item on the stack.

func (*Stack) Pop

func (stack *Stack) Pop() interface{}

Pop the top item of the stack and return it.

func (*Stack) Push

func (stack *Stack) Push(value interface{})

Push a value onto the top of the stack.

type StandardEncryptionHeader

type StandardEncryptionHeader struct {
	Flags        uint32
	SizeExtra    uint32
	AlgID        uint32
	AlgIDHash    uint32
	KeySize      uint32
	ProviderType uint32
	Reserved1    uint32
	Reserved2    uint32
	CspName      string
}

StandardEncryptionHeader structure is used by ECMA-376 document encryption [ECMA-376] and Office binary document RC4 CryptoAPI encryption, to specify encryption properties for an encrypted stream.

type StandardEncryptionVerifier

type StandardEncryptionVerifier struct {
	SaltSize              uint32
	Salt                  []byte
	EncryptedVerifier     []byte
	VerifierHashSize      uint32
	EncryptedVerifierHash []byte
}

StandardEncryptionVerifier structure is used by Office Binary Document RC4 CryptoAPI Encryption and ECMA-376 Document Encryption. Every usage of this structure MUST specify the hashing algorithm and encryption algorithm used in the EncryptionVerifier structure.

type StreamWriter

type StreamWriter struct {
	Sheet   string
	SheetID int
	// contains filtered or unexported fields
}

StreamWriter defined the type of stream writer.

func (*StreamWriter) AddTable

func (sw *StreamWriter) AddTable(table *Table) error

AddTable creates an Excel table for the StreamWriter using the given cell range and format set. For example, create a table of A1:D5:

err := sw.AddTable(&excelize.Table{Range: "A1:D5"})

Create a table of F2:H6 with format set:

disable := false
err := sw.AddTable(&excelize.Table{
    Range:             "F2:H6",
    Name:              "table",
    StyleName:         "TableStyleMedium2",
    ShowFirstColumn:   true,
    ShowLastColumn:    true,
    ShowRowStripes:    &disable,
    ShowColumnStripes: true,
})

Note that the table must be at least two lines including the header. The header cells must contain strings and must be unique.

Currently, only one table is allowed for a StreamWriter. AddTable must be called after the rows are written but before Flush.

See File.AddTable for details on the table format.

func (*StreamWriter) Flush

func (sw *StreamWriter) Flush() error

Flush ending the streaming writing process.

func (*StreamWriter) InsertPageBreak

func (sw *StreamWriter) InsertPageBreak(cell string) error

InsertPageBreak creates a page break to determine where the printed page ends and where begins the next one by a given cell reference, the content before the page break will be printed on one page and after the page break on another.

func (*StreamWriter) MergeCell

func (sw *StreamWriter) MergeCell(hCell, vCell string) error

MergeCell provides a function to merge cells by a given range reference for the StreamWriter. Don't create a merged cell that overlaps with another existing merged cell.

func (*StreamWriter) SetColWidth

func (sw *StreamWriter) SetColWidth(min, max int, width float64) error

SetColWidth provides a function to set the width of a single column or multiple columns for the StreamWriter. Note that you must call the 'SetColWidth' function before the 'SetRow' function. For example set the width column B:C as 20:

err := sw.SetColWidth(2, 3, 20)

func (*StreamWriter) SetPanes

func (sw *StreamWriter) SetPanes(panes *Panes) error

SetPanes provides a function to create and remove freeze panes and split panes by giving panes options for the StreamWriter. Note that you must call the 'SetPanes' function before the 'SetRow' function.

func (*StreamWriter) SetRow

func (sw *StreamWriter) SetRow(cell string, values []interface{}, opts ...RowOpts) error

SetRow writes an array to stream rows by giving starting cell reference and a pointer to an array of values. Note that you must call the 'Flush' function to end the streaming writing process.

As a special case, if Cell is used as a value, then the Cell.StyleID will be applied to that cell.

type Style

type Style struct {
	Border        []Border
	Fill          Fill
	Font          *Font
	Alignment     *Alignment
	Protection    *Protection
	NumFmt        int
	DecimalPlaces *int
	CustomNumFmt  *string
	NegRed        bool
}

Style directly maps the style settings of the cells.

type Table

type Table struct {
	Range             string
	Name              string
	StyleName         string
	ShowColumnStripes bool
	ShowFirstColumn   bool
	ShowHeaderRow     *bool
	ShowLastColumn    bool
	ShowRowStripes    *bool
	// contains filtered or unexported fields
}

Table directly maps the format settings of the table.

type ViewOptions

type ViewOptions struct {
	// DefaultGridColor indicating that the consuming application should use
	// the default grid lines color(system dependent). Overrides any color
	// specified in colorId.
	DefaultGridColor *bool
	// RightToLeft indicating whether the sheet is in 'right to left' display
	// mode. When in this mode, Column A is on the far right, Column B; is one
	// column left of Column A, and so on. Also, information in cells is
	// displayed in the Right to Left format.
	RightToLeft *bool
	// ShowFormulas indicating whether this sheet should display formulas.
	ShowFormulas *bool
	// ShowGridLines indicating whether this sheet should display grid lines.
	ShowGridLines *bool
	// ShowRowColHeaders indicating whether the sheet should display row and
	// column headings.
	ShowRowColHeaders *bool
	// ShowRuler indicating this sheet should display ruler.
	ShowRuler *bool
	// ShowZeros indicating whether to "show a zero in cells that have zero
	// value". When using a formula to reference another cell which is empty,
	// the referenced value becomes 0 when the flag is true. (Default setting
	// is true.)
	ShowZeros *bool
	// TopLeftCell specifies a location of the top left visible cell Location
	// of the top left visible cell in the bottom right pane (when in
	// Left-to-Right mode).
	TopLeftCell *string
	// View indicating how sheet is displayed, by default it uses empty string
	// available options: normal, pageLayout, pageBreakPreview
	View *string
	// ZoomScale specifies a window zoom magnification for current view
	// representing percent values. This attribute is restricted to values
	// ranging from 10 to 400. Horizontal & Vertical scale together.
	ZoomScale *float64
}

ViewOptions directly maps the settings of sheet view.

type WorkbookPropsOptions

type WorkbookPropsOptions struct {
	Date1904      *bool
	FilterPrivacy *bool
	CodeName      *string
}

WorkbookPropsOptions directly maps the settings of workbook proprieties.

type WorkbookProtectionOptions

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

WorkbookProtectionOptions directly maps the settings of workbook protection.

Jump to

Keyboard shortcuts

? : This menu
/ : Search site
f or F : Jump to
y or Y : Canonical URL