excelize

package module
v2.3.1-0...-e13f400 Latest Latest
Warning

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

 Go to latest Published: Aug 11, 2020 License: BSD-3-Clause Imports: 28 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 XLSX / XLSM / XLTM 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.10 or later. The full API 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/360EntSecGroup-Skylar/excelize
  • If your package management with Go Modules, please install with following command.
go get github.com/360EntSecGroup-Skylar/excelize/v2
Create spreadsheet

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

package main

import (
    "fmt"

    "github.com/360EntSecGroup-Skylar/excelize"
)

func main() {
    f := excelize.NewFile()
    // Create a new sheet.
    index := f.NewSheet("Sheet2")
    // 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 xlsx file 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/360EntSecGroup-Skylar/excelize"
)

func main() {
    f, err := excelize.OpenFile("Book1.xlsx")
    if err != nil {
        fmt.Println(err)
        return
    }
    // Get value from cell by given worksheet name and axis.
    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")
    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 off data in your worksheet or generate charts without any data in your worksheet at all.

Excelize

package main

import (
    "fmt"

    "github.com/360EntSecGroup-Skylar/excelize"
)

func main() {
    categories := map[string]string{"A2": "Small", "A3": "Normal", "A4": "Large", "B1": "Apple", "C1": "Orange", "D1": "Pear"}
    values := map[string]int{"B2": 2, "C2": 3, "D2": 3, "B3": 5, "C3": 2, "D3": 4, "B4": 6, "C4": 7, "D4": 8}
    f := excelize.NewFile()
    for k, v := range categories {
        f.SetCellValue("Sheet1", k, v)
    }
    for k, v := range values {
        f.SetCellValue("Sheet1", k, v)
    }
    if err := f.AddChart("Sheet1", "E1", `{"type":"col3DClustered","series":[{"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":{"name":"Fruit 3D Clustered Column Chart"}}`); err != nil {
        fmt.Println(err)
        return
    }
    // Save xlsx file 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/360EntSecGroup-Skylar/excelize"
)

func main() {
    f, err := excelize.OpenFile("Book1.xlsx")
    if err != nil {
        fmt.Println(err)
        return
    }
    // Insert a picture.
    if err := f.AddPicture("Sheet1", "A2", "image.png", ""); err != nil {
        fmt.Println(err)
    }
    // Insert a picture to worksheet with scaling.
    if err := f.AddPicture("Sheet1", "D2", "image.jpg", `{"x_scale": 0.5, "y_scale": 0.5}`); err != nil {
        fmt.Println(err)
    }
    // Insert a picture offset in the cell with printing support.
    if err := f.AddPicture("Sheet1", "H2", "image.gif", `{"x_offset": 15, "y_offset": 10, "print_obj": true, "lock_aspect_ratio": false, "locked": false}`); err != nil {
        fmt.Println(err)
    }
    // Save the xlsx file 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 XLSX / XLSM / XLTM files. Supports reading and writing spreadsheet documents generated by Microsoft Exce™ 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.10 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 (
	Area                        = "area"
	AreaStacked                 = "areaStacked"
	AreaPercentStacked          = "areaPercentStacked"
	Area3D                      = "area3D"
	Area3DStacked               = "area3DStacked"
	Area3DPercentStacked        = "area3DPercentStacked"
	Bar                         = "bar"
	BarStacked                  = "barStacked"
	BarPercentStacked           = "barPercentStacked"
	Bar3DClustered              = "bar3DClustered"
	Bar3DStacked                = "bar3DStacked"
	Bar3DPercentStacked         = "bar3DPercentStacked"
	Bar3DConeClustered          = "bar3DConeClustered"
	Bar3DConeStacked            = "bar3DConeStacked"
	Bar3DConePercentStacked     = "bar3DConePercentStacked"
	Bar3DPyramidClustered       = "bar3DPyramidClustered"
	Bar3DPyramidStacked         = "bar3DPyramidStacked"
	Bar3DPyramidPercentStacked  = "bar3DPyramidPercentStacked"
	Bar3DCylinderClustered      = "bar3DCylinderClustered"
	Bar3DCylinderStacked        = "bar3DCylinderStacked"
	Bar3DCylinderPercentStacked = "bar3DCylinderPercentStacked"
	Col                         = "col"
	ColStacked                  = "colStacked"
	ColPercentStacked           = "colPercentStacked"
	Col3D                       = "col3D"
	Col3DClustered              = "col3DClustered"
	Col3DStacked                = "col3DStacked"
	Col3DPercentStacked         = "col3DPercentStacked"
	Col3DCone                   = "col3DCone"
	Col3DConeClustered          = "col3DConeClustered"
	Col3DConeStacked            = "col3DConeStacked"
	Col3DConePercentStacked     = "col3DConePercentStacked"
	Col3DPyramid                = "col3DPyramid"
	Col3DPyramidClustered       = "col3DPyramidClustered"
	Col3DPyramidStacked         = "col3DPyramidStacked"
	Col3DPyramidPercentStacked  = "col3DPyramidPercentStacked"
	Col3DCylinder               = "col3DCylinder"
	Col3DCylinderClustered      = "col3DCylinderClustered"
	Col3DCylinderStacked        = "col3DCylinderStacked"
	Col3DCylinderPercentStacked = "col3DCylinderPercentStacked"
	Doughnut                    = "doughnut"
	Line                        = "line"
	Pie                         = "pie"
	Pie3D                       = "pie3D"
	PieOfPieChart               = "pieOfPie"
	BarOfPieChart               = "barOfPie"
	Radar                       = "radar"
	Scatter                     = "scatter"
	Surface3D                   = "surface3D"
	WireframeSurface3D          = "wireframeSurface3D"
	Contour                     = "contour"
	WireframeContour            = "wireframeContour"
	Bubble                      = "bubble"
	Bubble3D                    = "bubble3D"
)

This section defines the currently supported chart types.

View Source 
const (
	DataValidationTypeCustom
	DataValidationTypeDate
	DataValidationTypeDecimal

	DataValidationTypeTextLeng
	DataValidationTypeTime
	// DataValidationTypeWhole Integer
	DataValidationTypeWhole
)

Data validation types.

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

Data validation operators.

View Source 
const (
	// OrientationPortrait indicates page layout orientation id portrait.
	OrientationPortrait = "portrait"
	// OrientationLandscape indicates page layout orientation id landscape.
	OrientationLandscape = "landscape"
)
View Source 
const (
	SourceRelationshipChart                      = "http://schemas.openxmlformats.org/officeDocument/2006/relationships/chart"
	SourceRelationshipComments                   = "http://schemas.openxmlformats.org/officeDocument/2006/relationships/comments"
	SourceRelationshipImage                      = "http://schemas.openxmlformats.org/officeDocument/2006/relationships/image"
	SourceRelationshipTable                      = "http://schemas.openxmlformats.org/officeDocument/2006/relationships/table"
	SourceRelationshipDrawingML                  = "http://schemas.openxmlformats.org/officeDocument/2006/relationships/drawing"
	SourceRelationshipDrawingVML                 = "http://schemas.openxmlformats.org/officeDocument/2006/relationships/vmlDrawing"
	SourceRelationshipHyperLink                  = "http://schemas.openxmlformats.org/officeDocument/2006/relationships/hyperlink"
	SourceRelationshipWorkSheet                  = "http://schemas.openxmlformats.org/officeDocument/2006/relationships/worksheet"
	SourceRelationshipChartsheet                 = "http://schemas.openxmlformats.org/officeDocument/2006/relationships/chartsheet"
	SourceRelationshipDialogsheet                = "http://schemas.openxmlformats.org/officeDocument/2006/relationships/dialogsheet"
	SourceRelationshipPivotTable                 = "http://schemas.openxmlformats.org/officeDocument/2006/relationships/pivotTable"
	SourceRelationshipPivotCache                 = "http://schemas.openxmlformats.org/officeDocument/2006/relationships/pivotCacheDefinition"
	SourceRelationshipSharedStrings              = "http://schemas.openxmlformats.org/officeDocument/2006/relationships/sharedStrings"
	SourceRelationshipVBAProject                 = "http://schemas.microsoft.com/office/2006/relationships/vbaProject"
	SourceRelationshipChart201506                = "http://schemas.microsoft.com/office/drawing/2015/06/chart"
	SourceRelationshipChart20070802              = "http://schemas.microsoft.com/office/drawing/2007/8/2/chart"
	SourceRelationshipChart2014                  = "http://schemas.microsoft.com/office/drawing/2014/chart"
	NameSpaceDrawingML                           = "http://schemas.openxmlformats.org/drawingml/2006/main"
	NameSpaceDrawingMLChart                      = "http://schemas.openxmlformats.org/drawingml/2006/chart"
	NameSpaceDrawingMLSpreadSheet                = "http://schemas.openxmlformats.org/drawingml/2006/spreadsheetDrawing"
	NameSpaceSpreadSheetX15                      = "http://schemas.microsoft.com/office/spreadsheetml/2010/11/main"
	NameSpaceSpreadSheetExcel2006Main            = "http://schemas.microsoft.com/office/excel/2006/main"
	NameSpaceMacExcel2008Main                    = "http://schemas.microsoft.com/office/mac/excel/2008/main"
	NameSpaceXML                                 = "http://www.w3.org/XML/1998/namespace"
	NameSpaceXMLSchemaInstance                   = "http://www.w3.org/2001/XMLSchema-instance"
	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"
	StrictSourceRelationshipImage                = "http://purl.oclc.org/ooxml/officeDocument/relationships/image"
	StrictNameSpaceSpreadSheet                   = "http://purl.oclc.org/ooxml/spreadsheetml/main"
	NameSpaceDublinCore                          = "http://purl.org/dc/elements/1.1/"
	NameSpaceDublinCoreTerms                     = "http://purl.org/dc/terms/"
	NameSpaceDublinCoreMetadataIntiative         = "http://purl.org/dc/dcmitype/"
	ContentTypeDrawing                           = "application/vnd.openxmlformats-officedocument.drawing+xml"
	ContentTypeDrawingML                         = "application/vnd.openxmlformats-officedocument.drawingml.chart+xml"
	ContentTypeMacro                             = "application/vnd.ms-excel.sheet.macroEnabled.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"
	ContentTypeVBA                               = "application/vnd.ms-office.vbaProject"
	ContentTypeVML                               = "application/vnd.openxmlformats-officedocument.vmlDrawing"
	// 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)
	ExtURIConditionalFormattings = "{78C0D931-6437-407D-A8EE-F0AAD7539E65}"
	ExtURIDataValidations        = "{CCE6A557-97BC-4B89-ADB6-D9C93CAAB3DF}"
	ExtURISparklineGroups        = "{05C60535-1F16-4fd2-B633-F4F36F0B64E0}"
	ExtURISlicerListX14          = "{A8765BA9-456A-4DAB-B4F3-ACF838C121DE}"
	ExtURISlicerCachesListX14    = "{BBE1A952-AA13-448e-AADC-164F8A28A991}"
	ExtURISlicerListX15          = "{3A4CF648-6AED-40f4-86FF-DC5316D8AED3}"
	ExtURIProtectedRanges        = "{FC87AEE6-9EDD-4A0A-B7FB-166176984837}"
	ExtURIIgnoredErrors          = "{01252117-D84E-4E92-8308-4BE1C098FCBB}"
	ExtURIWebExtensions          = "{F7C9EE02-42E1-4005-9D12-6889AFFD525C}"
	ExtURITimelineRefs           = "{7E03D99C-DC04-49d9-9315-930204A7B6E9}"
	ExtURIDrawingBlip            = "{28A0092B-C50C-407E-A947-70E740481C1C}"
	ExtURIMacExcelMX             = "{64002731-A6B0-56B0-2670-7721B7C09600}"
)

Source relationship and namespace.

View Source 
const (
	FileNameLength       = 207
	TotalRows            = 1048576
	TotalColumns         = 16384
	TotalSheetHyperlinks = 65529
	TotalCellChars       = 32767
)

Excel specifications and limits

View Source 
const (
	EMU int = 9525
)

Define the default cell size and EMU unit of measurement.

View Source 
const XMLHeader = "<?xml version=\"1.0\" encoding=\"UTF-8\" standalone=\"yes\"?>\n"

XMLHeader define an XML declaration can also contain a standalone declaration.

Variables

View Source 
var (
	SourceRelationship              = xml.Attr{Name: xml.Name{Local: "r", Space: "xmlns"}, Value: "http://schemas.openxmlformats.org/officeDocument/2006/relationships"}
	SourceRelationshipCompatibility = xml.Attr{Name: xml.Name{Local: "mc", Space: "xmlns"}, Value: "http://schemas.openxmlformats.org/markup-compatibility/2006"}
	NameSpaceSpreadSheet            = xml.Attr{Name: xml.Name{Local: "xmlns"}, Value: "http://schemas.openxmlformats.org/spreadsheetml/2006/main"}
	NameSpaceSpreadSheetX14         = xml.Attr{Name: xml.Name{Local: "x14", Space: "xmlns"}, Value: "http://schemas.microsoft.com/office/spreadsheetml/2009/9/main"}
)

Source relationship and namespace.

View Source 
var HSLModel = color.ModelFunc(hslModel)

HSLModel converts any color.Color to a HSL color.

View Source 
var (
	// XMLHeaderByte define an XML declaration can also contain a standalone
	// declaration.
	XMLHeaderByte = []byte(XMLHeader)
)

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 to int. Column name case insensitive. 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) (string, error)

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

Example: 

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

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 a 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 a HSL triple.

func ReadZipReader 

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

ReadZipReader can be used to read the spreadsheet in memory without touching the filesystem.

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 `json:"horizontal"`
	Indent          int    `json:"indent"`
	JustifyLastLine bool   `json:"justify_last_line"`
	ReadingOrder    uint64 `json:"reading_order"`
	RelativeIndent  int    `json:"relative_indent"`
	ShrinkToFit     bool   `json:"shrink_to_fit"`
	TextRotation    int    `json:"text_rotation"`
	Vertical        string `json:"vertical"`
	WrapText        bool   `json:"wrap_text"`
}

Alignment directly maps the alignment settings of the cells.

type ArgType 

type ArgType byte

ArgType is the type if formula argument type. 

const (
	ArgUnknown ArgType = iota
	ArgString
	ArgMatrix
)

Formula argument types enumeration.

type AutoPageBreaks 

type AutoPageBreaks bool

AutoPageBreaks is a SheetPrOption

type BaseColWidth 

type BaseColWidth uint8

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 gridlines. It is only the number of characters.

type Border 

type Border struct {
	Type  string `json:"type"`
	Color string `json:"color"`
	Style int    `json:"style"`
}

Border directly maps the border settings of the cells.

type Cell 

type Cell struct {
	StyleID int
	Value   interface{}
}

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

type CodeName 

type CodeName string

CodeName is a SheetPrOption

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() ([]string, error)

Rows return the current column's row values.

type Comment 

type Comment struct {
	Author   string `json:"author"`
	AuthorID int    `json:"author_id"`
	Ref      string `json:"ref"`
	Text     string `json:"text"`
}

Comment directly maps the comment information.

type CustomHeight 

type CustomHeight bool

CustomHeight specifies the custom height.

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 a 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 (dd *DataValidation) SetDropList(keys []string) error

SetDropList data validation list.

func (*DataValidation) SetError 

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

SetError set error notice.

func (*DataValidation) SetInput 

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

SetInput set prompt notice.

func (*DataValidation) SetRange 

func (dd *DataValidation) SetRange(f1, f2 int, t DataValidationType, o DataValidationOperator) error

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

func (*DataValidation) SetSqref 

func (dd *DataValidation) SetSqref(sqref string)

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

func (*DataValidation) SetSqrefDropList 

func (dd *DataValidation) SetSqrefDropList(sqref string, isCurrentSheet bool) error

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: 

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

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 DefaultColWidth 

type DefaultColWidth float64

DefaultColWidth specifies the default column width measured as the number of characters of the maximum digit width of the normal style's font.

type DefaultGridColor 

type DefaultGridColor bool

DefaultGridColor is a SheetViewOption. It specifies a flag indicating that the consuming application should use the default grid lines color (system dependent). Overrides any color specified in colorId.

type DefaultRowHeight 

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

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 EnableFormatConditionsCalculation 

type EnableFormatConditionsCalculation bool

EnableFormatConditionsCalculation is a SheetPrOption

type ErrSheetNotExist 

type ErrSheetNotExist struct {
	SheetName string
}

ErrSheetNotExist defines an error of sheet is not exist

func (ErrSheetNotExist) Error 

func (err ErrSheetNotExist) Error() string

type File 

type File struct {
	CalcChain     *xlsxCalcChain
	Comments      map[string]*xlsxComments
	ContentTypes  *xlsxTypes
	Drawings      map[string]*xlsxWsDr
	Path          string
	SharedStrings *xlsxSST

	Sheet            map[string]*xlsxWorksheet
	SheetCount       int
	Styles           *xlsxStyleSheet
	Theme            *xlsxTheme
	DecodeVMLDrawing map[string]*decodeVmlDrawing
	VMLDrawing       map[string]*vmlDrawing
	WorkBook         *xlsxWorkbook
	Relationships    map[string]*xlsxRelationships
	XLSX             map[string][]byte
	CharsetReader    charsetTranscoderFn
	// contains filtered or unexported fields
}

File define a populated spreadsheet file struct.

func NewFile 

func NewFile() *File

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

xlsx := NewFile()

func OpenFile 

func OpenFile(filename string) (*File, error)

OpenFile take the name of an spreadsheet file and returns a populated spreadsheet file struct for it.

func OpenReader 

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

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

func (*File) AddChart 

func (f *File) AddChart(sheet, cell, format string, combo ...string) 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/360EntSecGroup-Skylar/excelize"
)

func main() {
    categories := map[string]string{"A2": "Small", "A3": "Normal", "A4": "Large", "B1": "Apple", "C1": "Orange", "D1": "Pear"}
    values := map[string]int{"B2": 2, "C2": 3, "D2": 3, "B3": 5, "C3": 2, "D3": 4, "B4": 6, "C4": 7, "D4": 8}
    f := excelize.NewFile()
    for k, v := range categories {
        f.SetCellValue("Sheet1", k, v)
    }
    for k, v := range values {
        f.SetCellValue("Sheet1", k, v)
    }
    if err := f.AddChart("Sheet1", "E1", `{"type":"col3DClustered","series":[{"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":{"name":"Fruit 3D Clustered Column Chart"},"plotarea":{"show_bubble_size":true,"show_cat_name":false,"show_leader_lines":false,"show_percent":true,"show_series_name":true,"show_val":true},"show_blanks_as":"zero","x_axis":{"reverse_order":true},"y_axis":{"maximum":7.5,"minimum":0.5}}`); err != nil {
        fmt.Println(err)
        return
    }
    // Save xlsx file 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: 

 Type                        | Chart
-----------------------------+------------------------------
 area                        | 2D area chart
 areaStacked                 | 2D stacked area chart
 areaPercentStacked          | 2D 100% stacked area chart
 area3D                      | 3D area chart
 area3DStacked               | 3D stacked area chart
 area3DPercentStacked        | 3D 100% stacked area chart
 bar                         | 2D clustered bar chart
 barStacked                  | 2D stacked bar chart
 barPercentStacked           | 2D 100% stacked bar chart
 bar3DClustered              | 3D clustered bar chart
 bar3DStacked                | 3D stacked bar chart
 bar3DPercentStacked         | 3D 100% stacked bar chart
 bar3DConeClustered          | 3D cone clustered bar chart
 bar3DConeStacked            | 3D cone stacked bar chart
 bar3DConePercentStacked     | 3D cone percent bar chart
 bar3DPyramidClustered       | 3D pyramid clustered bar chart
 bar3DPyramidStacked         | 3D pyramid stacked bar chart
 bar3DPyramidPercentStacked  | 3D pyramid percent stacked bar chart
 bar3DCylinderClustered      | 3D cylinder clustered bar chart
 bar3DCylinderStacked        | 3D cylinder stacked bar chart
 bar3DCylinderPercentStacked | 3D cylinder percent stacked bar chart
 col                         | 2D clustered column chart
 colStacked                  | 2D stacked column chart
 colPercentStacked           | 2D 100% stacked column chart
 col3DClustered              | 3D clustered column chart
 col3D                       | 3D column chart
 col3DStacked                | 3D stacked column chart
 col3DPercentStacked         | 3D 100% stacked column chart
 col3DCone                   | 3D cone column chart
 col3DConeClustered          | 3D cone clustered column chart
 col3DConeStacked            | 3D cone stacked column chart
 col3DConePercentStacked     | 3D cone percent stacked column chart
 col3DPyramid                | 3D pyramid column chart
 col3DPyramidClustered       | 3D pyramid clustered column chart
 col3DPyramidStacked         | 3D pyramid stacked column chart
 col3DPyramidPercentStacked  | 3D pyramid percent stacked column chart
 col3DCylinder               | 3D cylinder column chart
 col3DCylinderClustered      | 3D cylinder clustered column chart
 col3DCylinderStacked        | 3D cylinder stacked column chart
 col3DCylinderPercentStacked | 3D cylinder percent stacked column chart
 doughnut                    | doughnut chart
 line                        | line chart
 pie                         | pie chart
 pie3D                       | 3D pie chart
 pieOfPie                    | pie of pie chart
 barOfPie                    | bar of pie chart
 radar                       | radar chart
 scatter                     | scatter chart
 surface3D                   | 3D surface chart
 wireframeSurface3D          | 3D wireframe surface chart
 contour                     | contour chart
 wireframeContour            | wireframe contour chart
 bubble                      | bubble chart
 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
values
line

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.

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.

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

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

position
show_legend_key

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

top
bottom
left
right
top_right

show_legend_key: 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

name: 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 sheetname. The name property is optional. The default is to have no chart title.

Specifies how blank cells are plotted on the chart by show_blanks_as. 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.

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

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

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: 

show_bubble_size
show_cat_name
show_leader_lines
show_percent
show_series_name
show_val

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

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

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

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

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

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

Set the primary horizontal and vertical axis options by x_axis and y_axis. The properties of x_axis that can be set are: 

major_grid_lines
minor_grid_lines
tick_label_skip
reverse_order
maximum
minimum

The properties of y_axis that can be set are: 

major_grid_lines
minor_grid_lines
major_unit
reverse_order
maximum
minimum

major_grid_lines: Specifies major gridlines.

minor_grid_lines: Specifies minor gridlines.

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

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

reverse_order: Specifies that the categories or values on reverse order (orientation of the chart). The reverse_order 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.

Set chart size by dimension property. The dimension property is optional. The default width is 480, and height is 290.

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/360EntSecGroup-Skylar/excelize"
)

func main() {
    categories := map[string]string{"A2": "Small", "A3": "Normal", "A4": "Large", "B1": "Apple", "C1": "Orange", "D1": "Pear"}
    values := map[string]int{"B2": 2, "C2": 3, "D2": 3, "B3": 5, "C3": 2, "D3": 4, "B4": 6, "C4": 7, "D4": 8}
    f := excelize.NewFile()
    for k, v := range categories {
        f.SetCellValue("Sheet1", k, v)
    }
    for k, v := range values {
        f.SetCellValue("Sheet1", k, v)
    }
    if err := f.AddChart("Sheet1", "E1", `{"type":"col","series":[{"name":"Sheet1!$A$2","categories":"","values":"Sheet1!$B$2:$D$2"},{"name":"Sheet1!$A$3","categories":"Sheet1!$B$1:$D$1","values":"Sheet1!$B$3:$D$3"}],"format":{"x_scale":1.0,"y_scale":1.0,"x_offset":15,"y_offset":10,"print_obj":true,"lock_aspect_ratio":false,"locked":false},"legend":{"position":"left","show_legend_key":false},"title":{"name":"Clustered Column - Line Chart"},"plotarea":{"show_bubble_size":true,"show_cat_name":false,"show_leader_lines":false,"show_percent":true,"show_series_name":true,"show_val":true}}`, `{"type":"line","series":[{"name":"Sheet1!$A$4","categories":"Sheet1!$B$1:$D$1","values":"Sheet1!$B$4:$D$4"}],"format":{"x_scale":1.0,"y_scale":1.0,"x_offset":15,"y_offset":10,"print_obj":true,"lock_aspect_ratio":false,"locked":false},"legend":{"position":"left","show_legend_key":false},"plotarea":{"show_bubble_size":true,"show_cat_name":false,"show_leader_lines":false,"show_percent":true,"show_series_name":true,"show_val":true}}`); err != nil {
        fmt.Println(err)
        return
    }
    // Save xlsx file by the given path.
    if err := f.SaveAs("Book1.xlsx"); err != nil {
        fmt.Println(err)
    }
}

func (*File) AddChartSheet 

func (f *File) AddChartSheet(sheet, format string, combo ...string) 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, cell, format string) error

AddComment provides the method to add comment in a sheet by given worksheet index, cell 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", "A30", `{"author":"Excelize: ","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": 

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

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

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

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

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

func (*File) AddPicture 

func (f *File) AddPicture(sheet, cell, picture, format string) 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. For example: 

package main

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

    "github.com/360EntSecGroup-Skylar/excelize"
)

func main() {
    f := excelize.NewFile()
    // Insert a picture.
    if err := f.AddPicture("Sheet1", "A2", "image.jpg", ""); err != nil {
        fmt.Println(err)
    }
    // Insert a picture scaling in the cell with location hyperlink.
    if err := f.AddPicture("Sheet1", "D2", "image.png", `{"x_scale": 0.5, "y_scale": 0.5, "hyperlink": "#Sheet2!D8", "hyperlink_type": "Location"}`); err != nil {
        fmt.Println(err)
    }
    // Insert a picture offset in the cell with external hyperlink, printing and positioning support.
    if err := f.AddPicture("Sheet1", "H2", "image.gif", `{"x_offset": 15, "y_offset": 10, "hyperlink": "https://github.com/360EntSecGroup-Skylar/excelize", "hyperlink_type": "External", "print_obj": true, "lock_aspect_ratio": false, "locked": false, "positioning": "oneCell"}`); err != nil {
        fmt.Println(err)
    }
    if err := f.SaveAs("Book1.xlsx"); err != nil {
        fmt.Println(err)
    }
}

LinkType defines two types of hyperlink "External" for web site or "Location" for moving to one of cell in this workbook. When the "hyperlink_type" is "Location", coordinates need to start with "#".

Positioning defines two types of the position of a picture in an Excel spreadsheet, "oneCell" (Move but don't size with cells) or "absolute" (Don't move or size with cells). If you don't set this parameter, default positioning is move and size with cells.

func (*File) AddPictureFromBytes 

func (f *File) AddPictureFromBytes(sheet, cell, format, name, extension string, file []byte) 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. For example: 

package main

import (
    "fmt"
    _ "image/jpeg"
    "io/ioutil"

    "github.com/360EntSecGroup-Skylar/excelize"
)

func main() {
    f := excelize.NewFile()

    file, err := ioutil.ReadFile("image.jpg")
    if err != nil {
        fmt.Println(err)
    }
    if err := f.AddPictureFromBytes("Sheet1", "A2", "", "Excel Logo", ".jpg", file); err != nil {
        fmt.Println(err)
    }
    if err := f.SaveAs("Book1.xlsx"); err != nil {
        fmt.Println(err)
    }
}

func (*File) AddPivotTable 

func (f *File) AddPivotTable(opt *PivotTableOption) error

AddPivotTable provides the method to add pivot table by given pivot table options.

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

package main

import (
    "fmt"
    "math/rand"

    "github.com/360EntSecGroup-Skylar/excelize"
)

func main() {
    f := excelize.NewFile()
    // 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 i := 0; i < 30; i++ {
        f.SetCellValue("Sheet1", fmt.Sprintf("A%d", i+2), month[rand.Intn(12)])
        f.SetCellValue("Sheet1", fmt.Sprintf("B%d", i+2), year[rand.Intn(3)])
        f.SetCellValue("Sheet1", fmt.Sprintf("C%d", i+2), types[rand.Intn(4)])
        f.SetCellValue("Sheet1", fmt.Sprintf("D%d", i+2), rand.Intn(5000))
        f.SetCellValue("Sheet1", fmt.Sprintf("E%d", i+2), region[rand.Intn(4)])
    }
    if err := f.AddPivotTable(&excelize.PivotTableOption{
        DataRange:       "Sheet1!$A$1:$E$31",
        PivotTableRange: "Sheet1!$G$2:$M$34",
        Rows:            []excelize.PivotTableField{{Data: "Month"}, {Data: "Year"}},
        Filter:          []excelize.PivotTableField{{Data: "Region"}},
        Columns:         []excelize.PivotTableField{{Data: "Type"}},
        Data:            []excelize.PivotTableField{{Data: "Sales", Name: "Summarize", Subtotal: "Sum"}},
    }); err != nil {
        fmt.Println(err)
    }
    if err := f.SaveAs("Book1.xlsx"); err != nil {
        fmt.Println(err)
    }
}

func (*File) AddShape 

func (f *File) AddShape(sheet, cell, format string) error

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

err := f.AddShape("Sheet1", "G6", `{"type":"rect","color":{"line":"#4286F4","fill":"#8eb9ff"},"paragraph":[{"text":"Rectangle Shape","font":{"bold":true,"italic":true,"family":"Times New Roman","size":36,"color":"#777777","underline":"sng"}}],"width":180,"height": 90}`)

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, opt *SparklineOption) (err 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.SparklineOption{
    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
 ColorAxis | An RGB Color is specified as RRGGBB
 Axis      | Show sparkline axis

func (*File) AddTable 

func (f *File) AddTable(sheet, hcell, vcell, format string) error

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

err := f.AddTable("Sheet1", "A1", "D5", ``)

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

err := f.AddTable("Sheet2", "F2", "H6", `{"table_name":"table","table_style":"TableStyleMedium2", "show_first_column":true,"show_last_column":true,"show_row_stripes":false,"show_column_stripes":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 coordinate areas that can't have an intersection.

table_name: The name of the table, in the same worksheet name of the table should be unique

table_style: The built-in table style names 

TableStyleLight1 - TableStyleLight21
TableStyleMedium1 - TableStyleMedium28
TableStyleDark1 - TableStyleDark11

func (*File) AddVBAProject 

func (f *File) AddVBAProject(bin string) error

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

if err := f.SetSheetPrOptions("Sheet1", excelize.CodeName("Sheet1")); err != nil {
    fmt.Println(err)
}
if err := f.AddVBAProject("vbaProject.bin"); err != nil {
    fmt.Println(err)
}
if err := f.SaveAs("macros.xlsm"); err != nil {
    fmt.Println(err)
}

func (*File) AutoFilter 

func (f *File) AutoFilter(sheet, hcell, vcell, format string) error

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

err := f.AutoFilter("Sheet1", "A1", "D4", "")

Filter data in an autofilter: 

err := f.AutoFilter("Sheet1", "A1", "D4", `{"column":"B","expression":"x != blanks"}`)

column defines the filter columns in a autofilter 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() method. 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*      // doesnt begin with b
x == *b      // ends with b
x != *b      // doesnt end with b
x == *b*     // contains b
x != *b*     // doesn't contains 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) (result string, err error)

CalcCellValue provides a function to get calculated cell value. This feature is currently in working processing. Array formula, table formula and some other formulas are not supported currently.

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) 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. 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 := f.NewSheet("Sheet2")
err := f.CopySheet(1, index)
return err

func (*File) DeleteChart 

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

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

func (*File) DeleteDataValidation 

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

DeleteDataValidation delete data validation by given worksheet name and reference sequence.

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: 

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

func (*File) DeletePicture 

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

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

func (*File) DeleteSheet 

func (f *File) DeleteSheet(name string)

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 the one worksheet is left.

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

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

GetCellFormula provides a function to get formula from cell by given worksheet name and axis in XLSX file. 

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

GetCellHyperLink provides a function to get cell hyperlink by given worksheet name and axis. Boolean type value link will be ture if the cell has a hyperlink and the target is the address of the hyperlink. Otherwise, the value of link will be false and the value of the target will be a blank string. For example get hyperlink of Sheet1!H6: 

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

func (*File) GetCellStyle 

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

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

func (*File) GetCellValue 

func (f *File) GetCellValue(sheet, axis string) (string, error)

GetCellValue provides a function to get formatted value from cell by given worksheet name and axis in XLSX file. If it is possible to apply a format to the cell value, it will do so, if not then an error will be returned, along with the raw value of the cell.

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

func (*File) GetCols 

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

GetCols return all the columns in a sheet by given worksheet name (case sensitive). For example: 

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() (comments map[string][]Comment)

GetComments retrieves all comments and returns a map of worksheet name to the worksheet comments.

func (*File) GetDefaultFont 

func (f *File) GetDefaultFont() string

GetDefaultFont provides the default font name currently set in the workbook Documents generated by excelize start with 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) 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, opts ...PageLayoutOptionPtr) error

GetPageLayout provides a function to gets worksheet page layout.

Available options: 

PageLayoutOrientation(string)
PageLayoutPaperSize(int)
FitToHeight(int)
FitToWidth(int)
Example 
f := excelize.NewFile()
var (
	orientation excelize.PageLayoutOrientation
	paperSize   excelize.PageLayoutPaperSize
	fitToHeight excelize.FitToHeight
	fitToWidth  excelize.FitToWidth
)
if err := f.GetPageLayout("Sheet1", &orientation); err != nil {
	fmt.Println(err)
}
if err := f.GetPageLayout("Sheet1", &paperSize); err != nil {
	fmt.Println(err)
}
if err := f.GetPageLayout("Sheet1", &fitToHeight); err != nil {
	fmt.Println(err)
}

if err := f.GetPageLayout("Sheet1", &fitToWidth); err != nil {
	fmt.Println(err)
}
fmt.Println("Defaults:")
fmt.Printf("- orientation: %q\n", orientation)
fmt.Printf("- paper size: %d\n", paperSize)
fmt.Printf("- fit to height: %d\n", fitToHeight)
fmt.Printf("- fit to width: %d\n", fitToWidth)

Output:

Defaults:
- orientation: "portrait"
- paper size: 1
- fit to height: 1
- fit to width: 1

func (*File) GetPageMargins 

func (f *File) GetPageMargins(sheet string, opts ...PageMarginsOptionsPtr) error

GetPageMargins provides a function to get worksheet page margins.

Available options: 

PageMarginBottom(float64)
PageMarginFooter(float64)
PageMarginHeader(float64)
PageMarginLeft(float64)
PageMarginRight(float64)
PageMarginTop(float64)
Example 
f := excelize.NewFile()
const sheet = "Sheet1"

var (
	marginBottom excelize.PageMarginBottom
	marginFooter excelize.PageMarginFooter
	marginHeader excelize.PageMarginHeader
	marginLeft   excelize.PageMarginLeft
	marginRight  excelize.PageMarginRight
	marginTop    excelize.PageMarginTop
)

if err := f.GetPageMargins(sheet,
	&marginBottom,
	&marginFooter,
	&marginHeader,
	&marginLeft,
	&marginRight,
	&marginTop,
); err != nil {
	fmt.Println(err)
}
fmt.Println("Defaults:")
fmt.Println("- marginBottom:", marginBottom)
fmt.Println("- marginFooter:", marginFooter)
fmt.Println("- marginHeader:", marginHeader)
fmt.Println("- marginLeft:", marginLeft)
fmt.Println("- marginRight:", marginRight)
fmt.Println("- marginTop:", marginTop)

Output:

Defaults:
- marginBottom: 0.75
- marginFooter: 0.3
- marginHeader: 0.3
- marginLeft: 0.7
- marginRight: 0.7
- marginTop: 0.75

func (*File) GetPicture 

func (f *File) GetPicture(sheet, cell string) (string, []byte, error)

GetPicture provides a function to get picture base name and raw content embed in XLSX by given worksheet and cell name. This function returns the file name in XLSX and file contents as []byte data types. For example: 

f, err := excelize.OpenFile("Book1.xlsx")
if err != nil {
    fmt.Println(err)
    return
}
file, raw, err := f.GetPicture("Sheet1", "A2")
if err != nil {
    fmt.Println(err)
    return
}
if err := ioutil.WriteFile(file, raw, 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 index. 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) ([][]string, error)

GetRows return all the rows in a sheet by given worksheet name (case sensitive). For example: 

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

func (f *File) GetSheetFormatPr(sheet string, opts ...SheetFormatPrOptionsPtr) error

GetSheetFormatPr provides a function to get worksheet formatting properties.

Available options: 

BaseColWidth(uint8)
DefaultColWidth(float64)
DefaultRowHeight(float64)
CustomHeight(bool)
ZeroHeight(bool)
ThickTop(bool)
ThickBottom(bool)
Example 
f := excelize.NewFile()
const sheet = "Sheet1"

var (
	baseColWidth     excelize.BaseColWidth
	defaultColWidth  excelize.DefaultColWidth
	defaultRowHeight excelize.DefaultRowHeight
	customHeight     excelize.CustomHeight
	zeroHeight       excelize.ZeroHeight
	thickTop         excelize.ThickTop
	thickBottom      excelize.ThickBottom
)

if err := f.GetSheetFormatPr(sheet,
	&baseColWidth,
	&defaultColWidth,
	&defaultRowHeight,
	&customHeight,
	&zeroHeight,
	&thickTop,
	&thickBottom,
); err != nil {
	fmt.Println(err)
}
fmt.Println("Defaults:")
fmt.Println("- baseColWidth:", baseColWidth)
fmt.Println("- defaultColWidth:", defaultColWidth)
fmt.Println("- defaultRowHeight:", defaultRowHeight)
fmt.Println("- customHeight:", customHeight)
fmt.Println("- zeroHeight:", zeroHeight)
fmt.Println("- thickTop:", thickTop)
fmt.Println("- thickBottom:", thickBottom)

Output:

Defaults:
- baseColWidth: 0
- defaultColWidth: 0
- defaultRowHeight: 15
- customHeight: false
- zeroHeight: false
- thickTop: false
- thickBottom: false

func (*File) GetSheetIndex 

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

GetSheetIndex provides a function to get a sheet index of the workbook by the given sheet name. If the given sheet name is invalid, it will return an integer type value 0.

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

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

GetSheetPrOptions provides a function to gets worksheet properties.

Available options: 

CodeName(string)
EnableFormatConditionsCalculation(bool)
Published(bool)
FitToPage(bool)
AutoPageBreaks(bool)
OutlineSummaryBelow(bool)
Example 
f := excelize.NewFile()
const sheet = "Sheet1"

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

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

Output:

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

func (*File) GetSheetViewOptions 

func (f *File) GetSheetViewOptions(name string, viewIndex int, opts ...SheetViewOptionPtr) error

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

Available options: 

DefaultGridColor(bool)
RightToLeft(bool)
ShowFormulas(bool)
ShowGridLines(bool)
ShowRowColHeaders(bool)
ZoomScale(float64)
TopLeftCell(string)

Example: 

var showGridLines excelize.ShowGridLines
err = f.GetSheetViewOptions("Sheet1", -1, &showGridLines)
Example 
f := excelize.NewFile()
const sheet = "Sheet1"

var (
	defaultGridColor  excelize.DefaultGridColor
	rightToLeft       excelize.RightToLeft
	showFormulas      excelize.ShowFormulas
	showGridLines     excelize.ShowGridLines
	showZeros         excelize.ShowZeros
	showRowColHeaders excelize.ShowRowColHeaders
	zoomScale         excelize.ZoomScale
	topLeftCell       excelize.TopLeftCell
)

if err := f.GetSheetViewOptions(sheet, 0,
	&defaultGridColor,
	&rightToLeft,
	&showFormulas,
	&showGridLines,
	&showZeros,
	&showRowColHeaders,
	&zoomScale,
	&topLeftCell,
); err != nil {
	fmt.Println(err)
}

fmt.Println("Default:")
fmt.Println("- defaultGridColor:", defaultGridColor)
fmt.Println("- rightToLeft:", rightToLeft)
fmt.Println("- showFormulas:", showFormulas)
fmt.Println("- showGridLines:", showGridLines)
fmt.Println("- showZeros:", showZeros)
fmt.Println("- showRowColHeaders:", showRowColHeaders)
fmt.Println("- zoomScale:", zoomScale)
fmt.Println("- topLeftCell:", `"`+topLeftCell+`"`)

if err := f.SetSheetViewOptions(sheet, 0, excelize.TopLeftCell("B2")); err != nil {
	fmt.Println(err)
}

if err := f.GetSheetViewOptions(sheet, 0, &topLeftCell); err != nil {
	fmt.Println(err)
}

if err := f.SetSheetViewOptions(sheet, 0, excelize.ShowGridLines(false)); err != nil {
	fmt.Println(err)
}

if err := f.GetSheetViewOptions(sheet, 0, &showGridLines); err != nil {
	fmt.Println(err)
}

if err := f.SetSheetViewOptions(sheet, 0, excelize.ShowZeros(false)); err != nil {
	fmt.Println(err)
}

if err := f.GetSheetViewOptions(sheet, 0, &showZeros); err != nil {
	fmt.Println(err)
}

fmt.Println("After change:")
fmt.Println("- showGridLines:", showGridLines)
fmt.Println("- showZeros:", showZeros)
fmt.Println("- topLeftCell:", topLeftCell)

Output:

Default:
- defaultGridColor: true
- rightToLeft: false
- showFormulas: false
- showGridLines: true
- showZeros: true
- showRowColHeaders: true
- zoomScale: 0
- topLeftCell: ""
After change:
- showGridLines: false
- showZeros: false
- topLeftCell: B2

func (*File) GetSheetVisible 

func (f *File) GetSheetVisible(name string) bool

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

f.GetSheetVisible("Sheet1")

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

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

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

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

func (*File) InsertPageBreak 

func (f *File) InsertPageBreak(sheet, cell string) (err error)

InsertPageBreak create a page break to determine where the printed page ends and where begins the next one by given worksheet name and axis, so the content before the page break will be printed on one page and after the page break on another.

func (*File) InsertRow 

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

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

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

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 coordinate area and sheet name. 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. 

             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 string) (int, error)

NewConditionalStyle provides a function to create style for conditional format by given style format. The parameters are the same as function NewStyle(). Note that the color field uses RGB color code and only support to set font, fills, alignment and borders currently.

func (*File) NewSheet 

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

NewSheet provides function to create a new sheet by given worksheet name. When creating a new spreadsheet file, the default worksheet will be created. Returns the number of sheets in the workbook (file) after appending the new sheet.

func (*File) NewStreamWriter 

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

NewStreamWriter return stream writer struct by given worksheet name for generate new worksheet with large amounts of data. Note that after set rows, you must call the 'Flush' method to end the streaming writing process and ensure that the order of line numbers is ascending. For example, set data for worksheet of size 102400 rows x 50 columns with numbers and style: 

file := excelize.NewFile()
streamWriter, err := file.NewStreamWriter("Sheet1")
if err != nil {
    fmt.Println(err)
}
styleID, err := file.NewStyle(`{"font":{"color":"#777777"}}`)
if err != nil {
    fmt.Println(err)
}
if err := streamWriter.SetRow("A1", []interface{}{excelize.Cell{StyleID: styleID, Value: "Data"}}); err != nil {
    fmt.Println(err)
}
for rowID := 2; rowID <= 102400; rowID++ {
    row := make([]interface{}, 50)
    for colID := 0; colID < 50; colID++ {
        row[colID] = rand.Intn(640000)
    }
    cell, _ := excelize.CoordinatesToCellName(1, rowID)
    if err := streamWriter.SetRow(cell, row); err != nil {
        fmt.Println(err)
    }
}
if err := streamWriter.Flush(); err != nil {
    fmt.Println(err)
}
if err := file.SaveAs("Book1.xlsx"); err != nil {
    fmt.Println(err)
}

func (*File) NewStyle 

func (f *File) NewStyle(style interface{}) (int, error)

NewStyle provides a function to create the style for cells by given JSON or structure pointer. Note that the color field uses RGB color code.

The following shows the border styles sorted 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 shows the borders 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 shows the shading styles sorted by excelize index number: 

 Index | Style           | Index | Style
-------+-----------------+-------+-----------------
 0     | Horizontal      | 3     | Diagonal down
 1     | Vertical        | 4     | From corner
 2     | Diagonal Up     | 5     | From center

The following shows the patterns styles sorted 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 following the type of horizontal alignment in cells: 

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

The following the type of vertical alignment in cells: 

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

The following the type of font underline style: 

 Style
------------------
 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 with unicode values provided for language glyphs where they occur in zh-tw language: 

 Index | Symbol
-------+-------------------------------------------
 27    | [$-404]e/m/
 28    | [$-404]e"5E74"m"6708"d"65E5
 29    | [$-404]e"5E74"m"6708"d"65E5
 30    | m/d/y
 31    | yyyy"5E74"m"6708"d"65E5
 32    | hh"6642"mm"5206
 33    | hh"6642"mm"5206"ss"79D2
 34    | 4E0A5348/4E0B5348hh"6642"mm"5206
 35    | 4E0A5348/4E0B5348hh"6642"mm"5206"ss"79D2
 36    | [$-404]e/m/
 50    | [$-404]e/m/
 51    | [$-404]e"5E74"m"6708"d"65E5
 52    | 4E0A5348/4E0B5348hh"6642"mm"5206
 53    | 4E0A5348/4E0B5348hh"6642"mm"5206"ss"79D2
 54    | [$-404]e"5E74"m"6708"d"65E5
 55    | 4E0A5348/4E0B5348hh"6642"mm"5206
 56    | 4E0A5348/4E0B5348hh"6642"mm"5206"ss"79D2
 57    | [$-404]e/m/
 58    | [$-404]e"5E74"m"6708"d"65E5"

Number format code with unicode values provided for language glyphs where they occur in zh-cn language: 

 Index | Symbol
-------+-------------------------------------------
 27    | yyyy"5E74"m"6708
 28    | m"6708"d"65E5
 29    | m"6708"d"65E5
 30    | m-d-y
 31    | yyyy"5E74"m"6708"d"65E5
 32    | h"65F6"mm"5206
 33    | h"65F6"mm"5206"ss"79D2
 34    | 4E0A5348/4E0B5348h"65F6"mm"5206
 35    | 4E0A5348/4E0B5348h"65F6"mm"5206"ss"79D2
 36    | yyyy"5E74"m"6708
 50    | yyyy"5E74"m"6708
 51    | m"6708"d"65E5
 52    | yyyy"5E74"m"6708
 53    | m"6708"d"65E5
 54    | m"6708"d"65E5
 55    | 4E0A5348/4E0B5348h"65F6"mm"5206
 56    | 4E0A5348/4E0B5348h"65F6"mm"5206"ss"79D2
 57    | yyyy"5E74"m"6708
 58    | m"6708"d"65E5"

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 with unicode values provided for language glyphs where they occur in ja-jp language: 

 Index | Symbol
-------+-------------------------------------------
 27    | [$-411]ge.m.d
 28    | [$-411]ggge"5E74"m"6708"d"65E5
 29    | [$-411]ggge"5E74"m"6708"d"65E5
 30    | m/d/y
 31    | yyyy"5E74"m"6708"d"65E5
 32    | h"6642"mm"5206
 33    | h"6642"mm"5206"ss"79D2
 34    | yyyy"5E74"m"6708
 35    | m"6708"d"65E5
 36    | [$-411]ge.m.d
 50    | [$-411]ge.m.d
 51    | [$-411]ggge"5E74"m"6708"d"65E5
 52    | yyyy"5E74"m"6708
 53    | m"6708"d"65E5
 54    | [$-411]ggge"5E74"m"6708"d"65E5
 55    | yyyy"5E74"m"6708
 56    | m"6708"d"65E5
 57    | [$-411]ge.m.d
 58    | [$-411]ggge"5E74"m"6708"d"65E5"

Number format code with unicode values provided for language glyphs where they occur in ko-kr language: 

 Index | Symbol
-------+-------------------------------------------
 27    | yyyy"5E74" mm"6708" dd"65E5
 28    | mm-d
 29    | mm-d
 30    | mm-dd-y
 31    | yyyy"B144" mm"C6D4" dd"C77C
 32    | h"C2DC" mm"BD84
 33    | h"C2DC" mm"BD84" ss"CD08
 34    | yyyy-mm-d
 35    | yyyy-mm-d
 36    | yyyy"5E74" mm"6708" dd"65E5
 50    | yyyy"5E74" mm"6708" dd"65E5
 51    | mm-d
 52    | yyyy-mm-d
 53    | yyyy-mm-d
 54    | mm-d
 55    | yyyy-mm-d
 56    | yyyy-mm-d
 57    | yyyy"5E74" mm"6708" dd"65E5
 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

Number format code with unicode values provided for language glyphs where they occur 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    | 0E27/0E14/0E1B0E1B0E1B0E1
 72    | 0E27-0E140E140E14-0E1B0E1
 73    | 0E27-0E140E140E1
 74    | 0E140E140E14-0E1B0E1
 75    | 0E0A:0E190E1
 76    | 0E0A:0E190E19:0E170E1
 77    | 0E27/0E14/0E1B0E1B0E1B0E1B 0E0A:0E190E1
 78    | 0E190E19:0E170E1
 79    | [0E0A]:0E190E19:0E170E1
 80    | 0E190E19:0E170E17.
 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   | CN¥
 165   | $ English (China)
 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   | ₦ Yoruba (Nigeria)
 207   | ₩ Korean (South Korea)
 208   | ₪ Hebrew (Israel)
 209   | ₫ Vietnamese (Vietnam)
 210   | € Basque (Spain)
 211   | € Breton (France)
 212   | € Catalan (Spain)
 213   | € Corsican (France)
 214   | € Dutch (Belgium)
 215   | € Dutch (Netherlands)
 216   | € English (Ireland)
 217   | € Estonian (Estonia)
 218   | € Euro (€ 123)
 219   | € Euro (123 €)
 220   | € Finnish (Finland)
 221   | € French (Belgium)
 222   | € French (France)
 223   | € French (Luxembourg)
 224   | € French (Monaco)
 225   | € French (Réunion)
 226   | € Galician (Spain)
 227   | € German (Austria)
 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()
f.SetCellValue("Sheet1", "A6", 42920.5)
exp := "[$-380A]dddd\\,\\ dd\" de \"mmmm\" de \"yyyy;@"
style, err := f.NewStyle(&excelize.Style{CustomNumFmt: &exp})
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, settings *FormatSheetProtection) error

ProtectSheet provides a function to prevent other users from accidentally or deliberately changing, moving, or deleting data in a worksheet. For example, protect Sheet1 with protection settings: 

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

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) (err error)

RemovePageBreak remove a page break by given worksheet name and axis.

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

func (*File) Save 

func (f *File) Save() error

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

func (*File) SaveAs 

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

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

func (*File) SearchSheet 

func (f *File) SearchSheet(sheet, value string, reg ...bool) ([]string, error)

SearchSheet provides a function to get coordinates by given worksheet name, cell value, and regular expression. The function doesn't support searching on the calculated result, formatted numbers and conditional lookup currently. If it is a merged cell, it will return the coordinates of the upper left corner of the merged area.

An example of search the coordinates of the value of "100" on Sheet1: 

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

An example of search the coordinates where the numerical value in the range of "0-9" of Sheet1 is described: 

result, err := f.SearchSheet("Sheet1", "[0-9]", true)

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

func (*File) SetCellBool 

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

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

func (*File) SetCellDefault 

func (f *File) SetCellDefault(sheet, axis, 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, axis string, value float64, prec, bitSize int) error

SetCellFloat sets a floating point value into a cell. The prec 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()
var x = 3.14159265
if err := f.SetCellFloat("Sheet1", "A1", x, 2, 64); err != nil {
	fmt.Println(err)
}
val, _ := f.GetCellValue("Sheet1", "A1")
fmt.Println(val)

Output:

3.14

func (*File) SetCellFormula 

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

SetCellFormula provides a function to set cell formula by given string and worksheet name. 

func (f *File) SetCellHyperLink(sheet, axis, link, linkType string) 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 web site or "Location" for moving to one of cell in this workbook. Maximum limit hyperlinks in a worksheet is 65530. The below is example for external link. 

err := f.SetCellHyperLink("Sheet1", "A3", "https://github.com/360EntSecGroup-Skylar/excelize", "External")
// Set underline and font color style for the cell.
style, err := f.NewStyle(`{"font":{"color":"#1265BE","underline":"single"}}`)
err = f.SetCellStyle("Sheet1", "A3", "A3", style)

A this is another example for "Location": 

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

func (*File) SetCellInt 

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

SetCellInt provides a function to set int type value of a cell by given worksheet name, cell coordinates 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/360EntSecGroup-Skylar/excelize"
)

func main() {
    f := excelize.NewFile()
    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: "blod",
            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: " and ",
            Font: &excelize.Font{
                Size:  14,
                Color: "ad23e8",
            },
        },
        {
            Text: "underline.",
            Font: &excelize.Font{
                Color:     "23e833",
                Underline: "single",
            },
        },
    }); 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, axis, 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, coordinate area and style ID. Note that diagonalDown and diagonalUp type border should be use same color in the same coordinate area.

For example create a borders of cell H9 on Sheet1: 

style, err := f.NewStyle(`{"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(`{"fill":{"type":"gradient","color":["#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(`{"fill":{"type":"pattern","color":["#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(`{"alignment":{"horizontal":"center","ident":1,"justify_last_line":true,"reading_order":0,"relative_indent":1,"shrink_to_fit":true,"text_rotation":45,"vertical":"","wrap_text":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(`{"number_format": 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(`{"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(`{"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, axis string, value interface{}) error

SetCellValue provides a function to set value of a cell. The specified coordinates should not be in the first row of the table. 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 SetCellStyle() method.

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.

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.

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

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

func (*File) SetConditionalFormat 

func (f *File) SetConditionalFormat(sheet, area, formatSet string) 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
               | minimum
               | maximum
 date          | criteria
               | value
               | minimum
               | maximum
 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 | min_type
               | max_type
               | min_value
               | max_value
               | min_color
               | max_color
 3_color_scale | min_type
               | mid_type
               | max_type
               | min_value
               | mid_value
               | max_value
               | min_color
               | mid_color
               | max_color
 data_bar      | min_type
               | max_type
               | min_value
               | max_value
               | bar_color
 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: 

f.SetConditionalFormat("Sheet1", "D1:D10", fmt.Sprintf(`[{"type":"cell","criteria":">","format":%d,"value":"6"}]`, format))

The value property can also be an cell reference: 

f.SetConditionalFormat("Sheet1", "D1:D10", fmt.Sprintf(`[{"type":"cell","criteria":">","format":%d,"value":"$C$1"}]`, format))

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() method in the same way as cell formats: 

format, err = f.NewConditionalStyle(`{"font":{"color":"#9A0511"},"fill":{"type":"pattern","color":["#FEC7CE"],"pattern":1}}`)
if err != nil {
    fmt.Println(err)
}
f.SetConditionalFormat("Sheet1", "A1:A10", fmt.Sprintf(`[{"type":"cell","criteria":">","format":%d,"value":"6"}]`, format))

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(`{"font":{"color":"#9A0511"},"fill":{"type":"pattern","color":["#FEC7CE"],"pattern":1}}`)

// Light yellow format for neutral conditional.
format2, err = f.NewConditionalStyle(`{"font":{"color":"#9B5713"},"fill":{"type":"pattern","color":["#FEEAA0"],"pattern":1}}`)

// Light green format for good conditional.
format3, err = f.NewConditionalStyle(`{"font":{"color":"#09600B"},"fill":{"type":"pattern","color":["#C7EECF"],"pattern":1}}`)

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

// Hightlight cells rules: between...
f.SetConditionalFormat("Sheet1", "A1:A10", fmt.Sprintf(`[{"type":"cell","criteria":"between","format":%d,"minimum":"6","maximum":"8"}]`, format))

type: maximum - The maximum 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...
f.SetConditionalFormat("Sheet1", "A1:A10", fmt.Sprintf(`[{"type":"average","criteria":"=","format":%d, "above_average": true}]`, format1))

// Top/Bottom rules: Below Average...
f.SetConditionalFormat("Sheet1", "B1:B10", fmt.Sprintf(`[{"type":"average","criteria":"=","format":%d, "above_average": false}]`, format2))

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

// Hightlight cells rules: Duplicate Values...
f.SetConditionalFormat("Sheet1", "A1:A10", fmt.Sprintf(`[{"type":"duplicate","criteria":"=","format":%d}]`, format))

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

// Hightlight cells rules: Not Equal To...
f.SetConditionalFormat("Sheet1", "A1:A10", fmt.Sprintf(`[{"type":"unique","criteria":"=","format":%d}]`, 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.
f.SetConditionalFormat("Sheet1", "H1:H10", fmt.Sprintf(`[{"type":"top","criteria":"=","format":%d,"value":"6"}]`, format))

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

f.SetConditionalFormat("Sheet1", "A1:A10", fmt.Sprintf(`[{"type":"top","criteria":"=","format":%d,"value":"6","percent":true}]`, format))

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.
f.SetConditionalFormat("Sheet1", "A1:A10", `[{"type":"2_color_scale","criteria":"=","min_type":"min","max_type":"max","min_color":"#F8696B","max_color":"#63BE7B"}]`)

This conditional type can be modified with min_type, max_type, min_value, max_value, min_color and max_color, 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.
f.SetConditionalFormat("Sheet1", "A1:A10", `[{"type":"3_color_scale","criteria":"=","min_type":"min","mid_type":"percentile","max_type":"max","min_color":"#F8696B","mid_color":"#FFEB84","max_color":"#63BE7B"}]`)

This conditional type can be modified with min_type, mid_type, max_type, min_value, mid_value, max_value, min_color, mid_color and max_color, see below.

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

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

// Data Bars: Gradient Fill.
f.SetConditionalFormat("Sheet1", "K1:K10", `[{"type":"data_bar", "criteria":"=", "min_type":"min","max_type":"max","bar_color":"#638EC6"}]`)

The available min/mid/max types are: 

min        (for min_type only)
num
percent
percentile
formula
max        (for max_type only)

mid_type - Used for 3_color_scale. Same as min_type, see above.

max_type - Same as min_type, see above.

min_value - The min_value and max_value properties are available when the conditional formatting type is 2_color_scale, 3_color_scale or data_bar. The mid_value is available for 3_color_scale.

mid_value - Used for 3_color_scale. Same as min_value, see above.

max_value - Same as min_value, see above.

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

// Color scales: 3 color.
f.SetConditionalFormat("Sheet1", "B1:B10", `[{"type":"3_color_scale","criteria":"=","min_type":"min","mid_type":"percentile","max_type":"max","min_color":"#F8696B","mid_color":"#FFEB84","max_color":"#63BE7B"}]`)

mid_color - Used for 3_color_scale. Same as min_color, see above.

max_color - Same as min_color, see above.

bar_color - Used for data_bar. Same as min_color, see above.

func (*File) SetDefaultFont 

func (f *File) SetDefaultFont(fontName string)

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: 

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) (err 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.

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, settings *FormatHeaderFooter) 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
                        |
 &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                     | Strikethrough 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.FormatHeaderFooter{
    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 ...PageLayoutOption) error

SetPageLayout provides a function to sets worksheet page layout.

Available options: 

PageLayoutOrientation(string)
PageLayoutPaperSize(int)

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)
Example 
f := excelize.NewFile()

if err := f.SetPageLayout(
	"Sheet1",
	excelize.PageLayoutOrientation(excelize.OrientationLandscape),
); err != nil {
	fmt.Println(err)
}
if err := f.SetPageLayout(
	"Sheet1",
	excelize.PageLayoutPaperSize(10),
	excelize.FitToHeight(2),
	excelize.FitToWidth(2),
); err != nil {
	fmt.Println(err)
}

Output:

func (*File) SetPageMargins 

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

SetPageMargins provides a function to set worksheet page margins.

Available options: 

PageMarginBottom(float64)
PageMarginFooter(float64)
PageMarginHeader(float64)
PageMarginLeft(float64)
PageMarginRight(float64)
PageMarginTop(float64)
Example 
f := excelize.NewFile()
const sheet = "Sheet1"

if err := f.SetPageMargins(sheet,
	excelize.PageMarginBottom(1.0),
	excelize.PageMarginFooter(1.0),
	excelize.PageMarginHeader(1.0),
	excelize.PageMarginLeft(1.0),
	excelize.PageMarginRight(1.0),
	excelize.PageMarginTop(1.0),
); err != nil {
	fmt.Println(err)
}

Output:

func (*File) SetPanes 

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

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

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.

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

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

top_left_cell: 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: 

f.SetPanes("Sheet1", `{"freeze":true,"split":false,"x_split":1,"y_split":0,"top_left_cell":"B1","active_pane":"topRight","panes":[{"sqref":"K16","active_cell":"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: 

f.SetPanes("Sheet1", `{"freeze":true,"split":false,"x_split":0,"y_split":9,"top_left_cell":"A34","active_pane":"bottomLeft","panes":[{"sqref":"A11:XFD11","active_cell":"A11","pane":"bottomLeft"}]}`)

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

f.SetPanes("Sheet1", `{"freeze":false,"split":true,"x_split":3270,"y_split":1800,"top_left_cell":"N57","active_pane":"bottomLeft","panes":[{"sqref":"I36","active_cell":"I36"},{"sqref":"G33","active_cell":"G33","pane":"topRight"},{"sqref":"J60","active_cell":"J60","pane":"bottomLeft"},{"sqref":"O60","active_cell":"O60","pane":"bottomRight"}]}`)

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

f.SetPanes("Sheet1", `{"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) 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.

func (*File) SetSheetFormatPr 

func (f *File) SetSheetFormatPr(sheet string, opts ...SheetFormatPrOptions) error

SetSheetFormatPr provides a function to set worksheet formatting properties.

Available options: 

BaseColWidth(uint8)
DefaultColWidth(float64)
DefaultRowHeight(float64)
CustomHeight(bool)
ZeroHeight(bool)
ThickTop(bool)
ThickBottom(bool)
Example 
f := excelize.NewFile()
const sheet = "Sheet1"

if err := f.SetSheetFormatPr(sheet,
	excelize.BaseColWidth(1.0),
	excelize.DefaultColWidth(1.0),
	excelize.DefaultRowHeight(1.0),
	excelize.CustomHeight(true),
	excelize.ZeroHeight(true),
	excelize.ThickTop(true),
	excelize.ThickBottom(true),
); err != nil {
	fmt.Println(err)
}

Output:

func (*File) SetSheetName 

func (f *File) SetSheetName(oldName, newName string)

SetSheetName provides a function to set the worksheet name by given old and new worksheet names. Maximum 31 characters are allowed in sheet title and this function only changes the name of the sheet and will not update the sheet name in the formula or reference associated with the cell. So there may be problem formula error or reference missing.

func (*File) SetSheetPrOptions 

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

SetSheetPrOptions provides a function to sets worksheet properties.

Available options: 

CodeName(string)
EnableFormatConditionsCalculation(bool)
Published(bool)
FitToPage(bool)
AutoPageBreaks(bool)
OutlineSummaryBelow(bool)
Example 
f := excelize.NewFile()
const sheet = "Sheet1"

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

Output:

func (*File) SetSheetRow 

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

SetSheetRow writes an array to row by given worksheet name, starting coordinate and a pointer to array type 'slice'. For example, writes an array to row 6 start with the cell B6 on Sheet1: 

err := f.SetSheetRow("Sheet1", "B6", &[]interface{}{"1", nil, 2})

func (*File) SetSheetViewOptions 

func (f *File) SetSheetViewOptions(name string, viewIndex int, opts ...SheetViewOption) error

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

Available options: 

DefaultGridColor(bool)
RightToLeft(bool)
ShowFormulas(bool)
ShowGridLines(bool)
ShowRowColHeaders(bool)
ZoomScale(float64)
TopLeftCell(string)

Example: 

err = f.SetSheetViewOptions("Sheet1", -1, ShowGridLines(false))
Example 
f := excelize.NewFile()
const sheet = "Sheet1"

if err := f.SetSheetViewOptions(sheet, 0,
	excelize.DefaultGridColor(false),
	excelize.RightToLeft(false),
	excelize.ShowFormulas(true),
	excelize.ShowGridLines(true),
	excelize.ShowRowColHeaders(true),
	excelize.ZoomScale(80),
	excelize.TopLeftCell("C3"),
); err != nil {
	fmt.Println(err)
}

var zoomScale excelize.ZoomScale
fmt.Println("Default:")
fmt.Println("- zoomScale: 80")

if err := f.SetSheetViewOptions(sheet, 0, excelize.ZoomScale(500)); err != nil {
	fmt.Println(err)
}

if err := f.GetSheetViewOptions(sheet, 0, &zoomScale); err != nil {
	fmt.Println(err)
}

fmt.Println("Used out of range value:")
fmt.Println("- zoomScale:", zoomScale)

if err := f.SetSheetViewOptions(sheet, 0, excelize.ZoomScale(123)); err != nil {
	fmt.Println(err)
}

if err := f.GetSheetViewOptions(sheet, 0, &zoomScale); err != nil {
	fmt.Println(err)
}

fmt.Println("Used correct value:")
fmt.Println("- zoomScale:", zoomScale)

Output:

Default:
- zoomScale: 80
Used out of range value:
- zoomScale: 80
Used correct value:
- zoomScale: 123

func (*File) SetSheetVisible 

func (f *File) SetSheetVisible(name string, visible 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. Sheet state values as defined by https://docs.microsoft.com/en-us/dotnet/api/documentformat.openxml.spreadsheet.sheetstatevalues 

visible
hidden
veryHidden

For example, hide Sheet1: 

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

func (*File) UngroupSheets 

func (f *File) UngroupSheets() error

UngroupSheets provides a function to ungroup worksheets.

func (*File) UnmergeCell 

func (f *File) UnmergeCell(sheet string, hcell, vcell string) error

UnmergeCell provides a function to unmerge a given coordinate area. For example unmerge area D3:E9 on Sheet1: 

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

Attention: overlapped areas will also be unmerged.

func (*File) UnprotectSheet 

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

UnprotectSheet provides a function to unprotect an Excel worksheet.

func (*File) UnsetConditionalFormat 

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

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

func (*File) UpdateLinkedValue 

func (f *File) UpdateLinkedValue() error

UpdateLinkedValue fix linked values within a spreadsheet are not updating in Office Excel 2007 and 2010. 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 open XLSX file Excel will be update linked value and generate new value and will prompt save 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) error

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

func (*File) WriteTo 

func (f *File) WriteTo(w io.Writer) (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.

type Fill 

type Fill struct {
	Type    string   `json:"type"`
	Pattern int      `json:"pattern"`
	Color   []string `json:"color"`
	Shading int      `json:"shading"`
}

Fill directly maps the fill settings of the cells.

type FitToHeight 

type FitToHeight int

FitToHeight specified number of vertical pages to fit on

type FitToPage 

type FitToPage bool

FitToPage is a SheetPrOption

type FitToWidth 

type FitToWidth int

FitToWidth specified number of horizontal pages to fit on

type Font 

type Font struct {
	Bold      bool    `json:"bold"`
	Italic    bool    `json:"italic"`
	Underline string  `json:"underline"`
	Family    string  `json:"family"`
	Size      float64 `json:"size"`
	Strike    bool    `json:"strike"`
	Color     string  `json:"color"`
}

Font directly maps the font settings of the fonts.

type FormatHeaderFooter 

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

FormatHeaderFooter directly maps the settings of header and footer.

type FormatPageMargins 

type FormatPageMargins struct {
	Bottom string
	Footer string
	Header string
	Left   string
	Right  string
	Top    string
}

FormatPageMargins directly maps the settings of page margins

type FormatSheetProtection 

type FormatSheetProtection struct {
	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
}

FormatSheetProtection directly maps the settings of worksheet protection.

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 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 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 merge end axis. example: "D4"

func (*MergeCell) GetStartAxis 

func (m *MergeCell) GetStartAxis() string

GetStartAxis returns the merge start axis. example: "C2"

type OutlineSummaryBelow 

type OutlineSummaryBelow bool

OutlineSummaryBelow is an outlinePr, within SheetPr option

type PageLayoutOption 

type PageLayoutOption interface {
	// contains filtered or unexported methods
}

PageLayoutOption is an option of a page layout of a worksheet. See SetPageLayout().

type PageLayoutOptionPtr 

type PageLayoutOptionPtr interface {
	PageLayoutOption
	// contains filtered or unexported methods
}

PageLayoutOptionPtr is a writable PageLayoutOption. See GetPageLayout().

type PageLayoutOrientation 

type PageLayoutOrientation string

PageLayoutOrientation defines the orientation of page layout for a worksheet.

type PageLayoutPaperSize 

type PageLayoutPaperSize int

PageLayoutPaperSize defines the paper size of the worksheet

type PageMarginBottom 

type PageMarginBottom float64

PageMarginBottom specifies the bottom margin for the page.

type PageMarginFooter 

type PageMarginFooter float64

PageMarginFooter specifies the footer margin for the page.

type PageMarginHeader 

type PageMarginHeader float64

PageMarginHeader specifies the header margin for the page.

type PageMarginLeft 

type PageMarginLeft float64

PageMarginLeft specifies the left margin for the page.

type PageMarginRight 

type PageMarginRight float64

PageMarginRight specifies the right margin for the page.

type PageMarginTop 

type PageMarginTop float64

PageMarginTop specifies the top margin for the page.

type PageMarginsOptions 

type PageMarginsOptions interface {
	// contains filtered or unexported methods
}

PageMarginsOptions is an option of a page margin of a worksheet. See SetPageMargins().

type PageMarginsOptionsPtr 

type PageMarginsOptionsPtr interface {
	PageMarginsOptions
	// contains filtered or unexported methods
}

PageMarginsOptionsPtr is a writable PageMarginsOptions. See GetPageMargins().

type PivotTableField 

type PivotTableField struct {
	Data     string
	Name     string
	Subtotal string
}

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 PivotTableOption 

type PivotTableOption struct {
	DataRange       string
	PivotTableRange string
	Rows            []PivotTableField
	Columns         []PivotTableField
	Data            []PivotTableField
	Filter          []PivotTableField
}

PivotTableOption directly maps the format settings of the pivot table.

type Protection 

type Protection struct {
	Hidden bool `json:"hidden"`
	Locked bool `json:"locked"`
}

Protection directly maps the protection settings of the cells.

type Published 

type Published bool

Published is a SheetPrOption

type RichTextRun 

type RichTextRun struct {
	Font *Font
	Text string
}

RichTextRun directly maps the settings of the rich text run.

type RightToLeft 

type RightToLeft bool

RightToLeft is a SheetViewOption. It specifies a flag 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.

type Rows 

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

Rows defines an iterator to a sheet.

func (*Rows) Columns 

func (rows *Rows) Columns() ([]string, error)

Columns return the current row's column values.

func (*Rows) Error 

func (rows *Rows) Error() error

Error will return the error when the error occurs.

func (*Rows) Next 

func (rows *Rows) Next() bool

Next will return true if find the next row element.

type SheetFormatPrOptions 

type SheetFormatPrOptions interface {
	// contains filtered or unexported methods
}

SheetFormatPrOptions is an option of the formatting properties of a worksheet. See SetSheetFormatPr().

type SheetFormatPrOptionsPtr 

type SheetFormatPrOptionsPtr interface {
	SheetFormatPrOptions
	// contains filtered or unexported methods
}

SheetFormatPrOptionsPtr is a writable SheetFormatPrOptions. See GetSheetFormatPr().

type SheetPrOption 

type SheetPrOption interface {
	// contains filtered or unexported methods
}

SheetPrOption is an option of a view of a worksheet. See SetSheetPrOptions().

type SheetPrOptionPtr 

type SheetPrOptionPtr interface {
	SheetPrOption
	// contains filtered or unexported methods
}

SheetPrOptionPtr is a writable SheetPrOption. See GetSheetPrOptions().

type SheetViewOption 

type SheetViewOption interface {
	// contains filtered or unexported methods
}

SheetViewOption is an option of a view of a worksheet. See SetSheetViewOptions().

type SheetViewOptionPtr 

type SheetViewOptionPtr interface {
	SheetViewOption
	// contains filtered or unexported methods
}

SheetViewOptionPtr is a writable SheetViewOption. See GetSheetViewOptions().

type ShowFormulas 

type ShowFormulas bool

ShowFormulas is a SheetViewOption. It specifies a flag indicating whether this sheet should display formulas.

type ShowGridLines 

type ShowGridLines bool

ShowGridLines is a SheetViewOption. It specifies a flag indicating whether this sheet should display gridlines.

type ShowRowColHeaders 

type ShowRowColHeaders bool

ShowRowColHeaders is a SheetViewOption. It specifies a flag indicating whether the sheet should display row and column headings.

type ShowZeros 

type ShowZeros bool

ShowZeros is a SheetViewOption. It specifies a flag 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.)

type SparklineOption 

type SparklineOption 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
}

SparklineOption 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 StreamWriter 

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

StreamWriter defined the type of stream writer.

func (*StreamWriter) AddTable 

func (sw *StreamWriter) AddTable(hcell, vcell, format string) error

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

err := sw.AddTable("A1", "D5", ``)

Create a table of F2:H6 with format set: 

err := sw.AddTable("F2", "H6", `{"table_name":"table","table_style":"TableStyleMedium2","show_first_column":true,"show_last_column":true,"show_row_stripes":false,"show_column_stripes":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) SetRow 

func (sw *StreamWriter) SetRow(axis string, values []interface{}) error

SetRow writes an array to stream rows by giving a worksheet name, starting coordinate and a pointer to an array of values. Note that you must call the 'Flush' method 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    `json:"border"`
	Fill          Fill        `json:"fill"`
	Font          *Font       `json:"font"`
	Alignment     *Alignment  `json:"alignment"`
	Protection    *Protection `json:"protection"`
	NumFmt        int         `json:"number_format"`
	DecimalPlaces int         `json:"decimal_places"`
	CustomNumFmt  *string     `json:"custom_number_format"`
	Lang          string      `json:"lang"`
	NegRed        bool        `json:"negred"`
}

Style directly maps the style settings of the cells.

type ThickBottom 

type ThickBottom bool

ThickBottom specifies if rows have a thick bottom border by default.

type ThickTop 

type ThickTop bool

ThickTop specifies if rows have a thick top border by default.

type TopLeftCell 

type TopLeftCell string

TopLeftCell is a SheetViewOption. It 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).

type ZeroHeight 

type ZeroHeight bool

ZeroHeight specifies if rows are hidden.

type ZoomScale 

type ZoomScale float64

ZoomScale is a SheetViewOption. It 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.

Source Files

View all Source files

Jump to

Keyboard shortcuts

? : This menu
/ : Search site
f or F : Jump to
y or Y : Canonical URL
go.dev uses cookies from Google to deliver and enhance the quality of its services and to analyze traffic. Learn more.