README
¶
Excelize
Introduction
Excelize is a library written in pure Go and providing a set of functions that allow you to write to and read from XLSX files. Support reads and writes XLSX file generated by Microsoft Excel™ 2007 and later. Support save file without losing original charts of XLSX. This library needs Go version 1.8 or later. The full API docs can be seen using go's built-in documentation tool, or online at godoc.org and docs reference.
Basic Usage
Installation
go get github.com/360EntSecGroup-Skylar/excelize
Create XLSX file
Here is a minimal example usage that will create XLSX file.
package main
import (
"fmt"
"github.com/360EntSecGroup-Skylar/excelize"
)
func main() {
xlsx := excelize.NewFile()
// Create a new sheet.
index := xlsx.NewSheet("Sheet2")
// Set value of a cell.
xlsx.SetCellValue("Sheet2", "A2", "Hello world.")
xlsx.SetCellValue("Sheet1", "B2", 100)
// Set active sheet of the workbook.
xlsx.SetActiveSheet(index)
// Save xlsx file by the given path.
err := xlsx.SaveAs("./Book1.xlsx")
if err != nil {
fmt.Println(err)
}
}
Reading XLSX file
The following constitutes the bare to read a XLSX document.
package main
import (
"fmt"
"github.com/360EntSecGroup-Skylar/excelize"
)
func main() {
xlsx, err := excelize.OpenFile("./Book1.xlsx")
if err != nil {
fmt.Println(err)
return
}
// Get value from cell by given worksheet name and axis.
cell := xlsx.GetCellValue("Sheet1", "B2")
fmt.Println(cell)
// Get all the rows in the Sheet1.
rows := xlsx.GetRows("Sheet1")
for _, row := range rows {
for _, colCell := range row {
fmt.Print(colCell, "\t")
}
fmt.Println()
}
}
Add chart to XLSX 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.
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}
xlsx := excelize.NewFile()
for k, v := range categories {
xlsx.SetCellValue("Sheet1", k, v)
}
for k, v := range values {
xlsx.SetCellValue("Sheet1", k, v)
}
xlsx.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"}}`)
// Save xlsx file by the given path.
err := xlsx.SaveAs("./Book1.xlsx")
if err != nil {
fmt.Println(err)
}
}
Add picture to XLSX file
package main
import (
"fmt"
_ "image/gif"
_ "image/jpeg"
_ "image/png"
"github.com/360EntSecGroup-Skylar/excelize"
)
func main() {
xlsx, err := excelize.OpenFile("./Book1.xlsx")
if err != nil {
fmt.Println(err)
return
}
// Insert a picture.
err = xlsx.AddPicture("Sheet1", "A2", "./image1.png", "")
if err != nil {
fmt.Println(err)
}
// Insert a picture to worksheet with scaling.
err = xlsx.AddPicture("Sheet1", "D2", "./image2.jpg", `{"x_scale": 0.5, "y_scale": 0.5}`)
if err != nil {
fmt.Println(err)
}
// Insert a picture offset in the cell with printing support.
err = xlsx.AddPicture("Sheet1", "H2", "./image3.gif", `{"x_offset": 15, "y_offset": 10, "print_obj": true, "lock_aspect_ratio": false, "locked": false}`)
if err != nil {
fmt.Println(err)
}
// Save the xlsx file with the origin path.
err = xlsx.Save()
if 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.
Credits
Some struct of XML originally by tealeg/xlsx.
Licenses
This program is under the terms of the BSD 3-Clause License. See https://opensource.org/licenses/BSD-3-Clause.
Documentation
¶
Overview ¶
Copyright 2016 - 2018 The excelize Authors. All rights reserved. Use of this source code is governed by a BSD-style license that can be found in the LICENSE file.
Package excelize providing a set of functions that allow you to write to and read from XLSX files. Support reads and writes XLSX file generated by Microsoft Excel™ 2007 and later. Support save file without losing original charts of XLSX. This library needs Go version 1.8 or later.
See https://xuri.me/excelize for more information about this package.
Index ¶
- Constants
- Variables
- func HSLToRGB(h, s, l float64) (r, g, b uint8)
- func RGBToHSL(r, g, b uint8) (h, s, l float64)
- func ReadZipReader(r *zip.Reader) (map[string][]byte, int, error)
- func ThemeColor(baseColor string, tint float64) string
- func TitleToNumber(s string) int
- func ToAlphaString(value int) string
- type AutoPageBreaks
- type CodeName
- type Comment
- type DataValidation
- func (dd *DataValidation) SetDropList(keys []string) error
- func (dd *DataValidation) SetError(style DataValidationErrorStyle, title, msg string)
- func (dd *DataValidation) SetInput(title, msg string)
- func (dd *DataValidation) SetRange(f1, f2 int, t DataValidationType, o DataValidationOperator) error
- func (dd *DataValidation) SetSqref(sqref string)
- func (dd *DataValidation) SetSqrefDropList(sqref string, isCurrentSheet bool) error
- type DataValidationErrorStyle
- type DataValidationOperator
- type DataValidationType
- type DefaultGridColor
- type EnableFormatConditionsCalculation
- type ErrSheetNotExist
- type File
- func (f *File) AddChart(sheet, cell, format string) error
- func (f *File) AddComment(sheet, cell, format string) error
- func (f *File) AddDataValidation(sheet string, dv *DataValidation)
- func (f *File) AddPicture(sheet, cell, picture, format string) error
- func (f *File) AddPictureFromBytes(sheet, cell, format, name, extension string, file []byte) error
- func (f *File) AddShape(sheet, cell, format string) error
- func (f *File) AddTable(sheet, hcell, vcell, format string) error
- func (f *File) AutoFilter(sheet, hcell, vcell, format string) error
- func (f *File) CopySheet(from, to int) error
- func (f *File) DeleteSheet(name string)
- func (f *File) GetActiveSheetIndex() int
- func (f *File) GetCellFormula(sheet, axis string) string
- func (f *File) GetCellHyperLink(sheet, axis string) (bool, string)
- func (f *File) GetCellStyle(sheet, axis string) int
- func (f *File) GetCellValue(sheet, axis string) string
- func (f *File) GetColOutlineLevel(sheet, column string) uint8
- func (f *File) GetColVisible(sheet, column string) bool
- func (f *File) GetColWidth(sheet, column string) float64
- func (f *File) GetComments() (comments map[string][]Comment)
- func (f *File) GetPicture(sheet, cell string) (string, []byte)
- func (f *File) GetRowHeight(sheet string, row int) float64
- func (f *File) GetRowOutlineLevel(sheet string, rowIndex int) uint8
- func (f *File) GetRowVisible(sheet string, rowIndex int) bool
- func (f *File) GetRows(sheet string) [][]string
- func (f *File) GetSheetIndex(name string) int
- func (f *File) GetSheetMap() map[int]string
- func (f *File) GetSheetName(index int) string
- func (f *File) GetSheetPrOptions(name string, opts ...SheetPrOptionPtr) error
- func (f *File) GetSheetViewOptions(name string, viewIndex int, opts ...SheetViewOptionPtr) error
- func (f *File) GetSheetVisible(name string) bool
- func (f *File) InsertCol(sheet, column string)
- func (f *File) InsertRow(sheet string, row int)
- func (f *File) MergeCell(sheet, hcell, vcell string)
- func (f *File) NewConditionalStyle(style string) (int, error)
- func (f *File) NewSheet(name string) int
- func (f *File) NewStyle(style string) (int, error)
- func (f *File) RemoveCol(sheet, column string)
- func (f *File) RemoveRow(sheet string, row int)
- func (f *File) Rows(sheet string) (*Rows, error)
- func (f *File) Save() error
- func (f *File) SaveAs(name string) error
- func (f *File) SetActiveSheet(index int)
- func (f *File) SetCellBool(sheet, axis string, value bool)
- func (f *File) SetCellDefault(sheet, axis, value string)
- func (f *File) SetCellFormula(sheet, axis, formula string)
- func (f *File) SetCellHyperLink(sheet, axis, link, linkType string)
- func (f *File) SetCellInt(sheet, axis string, value int)
- func (f *File) SetCellStr(sheet, axis, value string)
- func (f *File) SetCellStyle(sheet, hcell, vcell string, styleID int)
- func (f *File) SetCellValue(sheet, axis string, value interface{})
- func (f *File) SetColOutlineLevel(sheet, column string, level uint8)
- func (f *File) SetColVisible(sheet, column string, visible bool)
- func (f *File) SetColWidth(sheet, startcol, endcol string, width float64)
- func (f *File) SetConditionalFormat(sheet, area, formatSet string) error
- func (f *File) SetPanes(sheet, panes string)
- func (f *File) SetRowHeight(sheet string, row int, height float64)
- func (f *File) SetRowOutlineLevel(sheet string, rowIndex int, level uint8)
- func (f *File) SetRowVisible(sheet string, rowIndex int, visible bool)
- func (f *File) SetSheetBackground(sheet, picture string) error
- func (f *File) SetSheetName(oldName, newName string)
- func (f *File) SetSheetPrOptions(name string, opts ...SheetPrOption) error
- func (f *File) SetSheetRow(sheet, axis string, slice interface{})
- func (f *File) SetSheetViewOptions(name string, viewIndex int, opts ...SheetViewOption) error
- func (f *File) SetSheetVisible(name string, visible bool)
- func (f *File) UpdateLinkedValue()
- func (f *File) Write(w io.Writer) error
- func (f *File) WriteTo(w io.Writer) (int64, error)
- type FitToPage
- type HSL
- type Published
- type RightToLeft
- type Rows
- type SheetPrOption
- type SheetPrOptionPtr
- type SheetViewOption
- type SheetViewOptionPtr
- type ShowFormulas
- type ShowGridLines
- type ShowRowColHeaders
- type ZoomScale
Examples ¶
Constants ¶
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 = "shared" )
const ( Bar = "bar" BarStacked = "barStacked" BarPercentStacked = "barPercentStacked" Bar3DClustered = "bar3DClustered" Bar3DStacked = "bar3DStacked" Bar3DPercentStacked = "bar3DPercentStacked" Col = "col" ColStacked = "colStacked" ColPercentStacked = "colPercentStacked" Col3DClustered = "col3DClustered" Col3D = "col3D" Col3DStacked = "col3DStacked" Col3DPercentStacked = "col3DPercentStacked" Doughnut = "doughnut" Line = "line" Pie = "pie" Pie3D = "pie3D" Radar = "radar" Scatter = "scatter" )
This section defines the currently supported chart types.
const ( DataValidationTypeCustom DataValidationTypeDate DataValidationTypeDecimal DataValidationTypeTextLeng DataValidationTypeTime // DataValidationTypeWhole Integer DataValidationTypeWhole )
Data validation types.
const ( DataValidationOperatorBetween DataValidationOperatorEqual DataValidationOperatorGreaterThan DataValidationOperatorGreaterThanOrEqual DataValidationOperatorLessThan DataValidationOperatorLessThanOrEqual DataValidationOperatorNotBetween DataValidationOperatorNotEqual )
Data validation operators.
const ( SourceRelationship = "http://schemas.openxmlformats.org/officeDocument/2006/relationships" 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" 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" SourceRelationshipCompatibility = "http://schemas.openxmlformats.org/markup-compatibility/2006" NameSpaceDrawingML = "http://schemas.openxmlformats.org/drawingml/2006/main" NameSpaceDrawingMLChart = "http://schemas.openxmlformats.org/drawingml/2006/chart" NameSpaceDrawingMLSpreadSheet = "http://schemas.openxmlformats.org/drawingml/2006/spreadsheetDrawing" NameSpaceSpreadSheet = "http://schemas.openxmlformats.org/spreadsheetml/2006/main" NameSpaceXML = "http://www.w3.org/XML/1998/namespace" )
Source relationship and namespace.
const (
EMU int = 9525
)
Define the default cell size and EMU unit of measurement.
const XMLHeader = "<?xml version=\"1.0\" encoding=\"UTF-8\" standalone=\"yes\"?>\n"
XMLHeader define an XML declaration can also contain a standalone declaration.
Variables ¶
var HSLModel = color.ModelFunc(hslModel)
HSLModel converts any color.Color to a HSL color.
var ( // XMLHeaderByte define an XML declaration can also contain a standalone // declaration. XMLHeaderByte = []byte(XMLHeader) )
Functions ¶
func ReadZipReader ¶
ReadZipReader can be used to read an XLSX in memory without touching the filesystem.
func ThemeColor ¶ added in v1.4.0
ThemeColor applied the color with tint value.
func TitleToNumber ¶
TitleToNumber provides a function to convert Excel sheet column title to int (this function doesn't do value check currently). For example convert AK and ak to column title 36:
excelize.TitleToNumber("AK")
excelize.TitleToNumber("ak")
func ToAlphaString ¶
ToAlphaString provides a function to convert integer to Excel sheet column title. For example convert 36 to column title AK:
excelize.ToAlphaString(36)
Types ¶
type Comment ¶ added in v1.4.0
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 DataValidation ¶ added in v1.4.0
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"`
Prompt *string `xml:"prompt,attr"`
PromptTitle *string `xml:"promptTitle"`
ShowDropDown bool `xml:"showDropDown,attr"`
ShowErrorMessage bool `xml:"showErrorMessage,attr"`
ShowInputMessage bool `xml:"showInputMessage,attr"`
Sqref string `xml:"sqref,attr"`
Type string `xml:"type,attr"`
Formula1 string `xml:"formula1"`
Formula2 string `xml:"formula2"`
}
DataValidation directly maps the a single item of data validation defined on a range of the worksheet.
func NewDataValidation ¶ added in v1.4.0
func NewDataValidation(allowBlank bool) *DataValidation
NewDataValidation return data validation struct.
func (*DataValidation) SetDropList ¶ added in v1.4.0
func (dd *DataValidation) SetDropList(keys []string) error
SetDropList data validation list.
func (*DataValidation) SetError ¶ added in v1.4.0
func (dd *DataValidation) SetError(style DataValidationErrorStyle, title, msg string)
SetError set error notice.
func (*DataValidation) SetInput ¶ added in v1.4.0
func (dd *DataValidation) SetInput(title, msg string)
SetInput set prompt notice.
func (*DataValidation) SetRange ¶ added in v1.4.0
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 ¶ added in v1.4.0
func (dd *DataValidation) SetSqref(sqref string)
SetSqref provides function to set data validation range in drop list.
func (*DataValidation) SetSqrefDropList ¶ added in v1.4.0
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("E1:E3", true)
xlsx.AddDataValidation("Sheet1", dvRange)
type DataValidationErrorStyle ¶ added in v1.4.0
type DataValidationErrorStyle int
DataValidationErrorStyle defined the style of data validation error alert.
const ( DataValidationErrorStyleStop DataValidationErrorStyle DataValidationErrorStyleWarning DataValidationErrorStyleInformation )
Data validation error styles.
type DataValidationOperator ¶ added in v1.4.0
type DataValidationOperator int
DataValidationOperator operator enum.
type DataValidationType ¶ added in v1.4.0
type DataValidationType int
DataValidationType defined the type of data validation.
type DefaultGridColor ¶ added in v1.2.0
type DefaultGridColor bool
DefaultGridColor is a SheetViewOption.
type EnableFormatConditionsCalculation ¶ added in v1.2.0
type EnableFormatConditionsCalculation bool
EnableFormatConditionsCalculation is a SheetPrOption
type ErrSheetNotExist ¶ added in v1.3.0
type ErrSheetNotExist struct {
SheetName string
}
ErrSheetNotExist defines an error of sheet is not exist
func (ErrSheetNotExist) Error ¶ added in v1.3.0
func (err ErrSheetNotExist) Error() string
type File ¶
type File struct {
ContentTypes *xlsxTypes
Path string
Sheet map[string]*xlsxWorksheet
SheetCount int
Styles *xlsxStyleSheet
Theme *xlsxTheme
WorkBook *xlsxWorkbook
WorkBookRels *xlsxWorkbookRels
XLSX map[string][]byte
// contains filtered or unexported fields
}
File define a populated XLSX file struct.
func NewFile ¶
func NewFile() *File
NewFile provides a function to create new file by default template. For example:
xlsx := NewFile()
func OpenFile ¶
OpenFile take the name of an XLSX file and returns a populated XLSX file struct for it.
func OpenReader ¶
OpenReader take an io.Reader and return a populated XLSX file.
func (*File) AddChart ¶
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!$A$29:$D$32:
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}
xlsx := excelize.NewFile()
for k, v := range categories {
xlsx.SetCellValue("Sheet1", k, v)
}
for k, v := range values {
xlsx.SetCellValue("Sheet1", k, v)
}
xlsx.AddChart("Sheet1", "E1", `{"type":"col3DClustered","dimension":{"width":640,"height":480},"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"}],"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":"bottom","show_legend_key":false},"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}}`)
// Save xlsx file by the given path.
err := xlsx.SaveAs("./Book1.xlsx")
if err != nil {
fmt.Println(err)
}
}
The following shows the type of chart supported by excelize:
Type | 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 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 doughnut | doughnut chart line | line chart pie | pie chart pie3D | 3D pie chart radar | radar chart scatter | scatter 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
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.
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 that can be set are:
reverse_order maximum minimum
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.
func (*File) AddComment ¶
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:
xlsx.AddComment("Sheet1", "A30", `{"author":"Excelize: ","text":"This is a comment."}`)
func (*File) AddDataValidation ¶ added in v1.4.0
func (f *File) AddDataValidation(sheet string, dv *DataValidation)
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")
xlsx.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")
xlsx.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"})
xlsx.AddDataValidation("Sheet1", dvRange)
func (*File) AddPicture ¶
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 (
"fmt"
_ "image/gif"
_ "image/jpeg"
_ "image/png"
"github.com/360EntSecGroup-Skylar/excelize"
)
func main() {
xlsx := excelize.NewFile()
// Insert a picture.
err := xlsx.AddPicture("Sheet1", "A2", "./image1.jpg", "")
if err != nil {
fmt.Println(err)
}
// Insert a picture scaling in the cell with location hyperlink.
err = xlsx.AddPicture("Sheet1", "D2", "./image1.png", `{"x_scale": 0.5, "y_scale": 0.5, "hyperlink": "#Sheet2!D8", "hyperlink_type": "Location"}`)
if err != nil {
fmt.Println(err)
}
// Insert a picture offset in the cell with external hyperlink, printing and positioning support.
err = xlsx.AddPicture("Sheet1", "H2", "./image3.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"}`)
if err != nil {
fmt.Println(err)
}
err = xlsx.SaveAs("./Book1.xlsx")
if 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 ¶ added in v1.4.0
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() {
xlsx := excelize.NewFile()
file, err := ioutil.ReadFile("./image1.jpg")
if err != nil {
fmt.Println(err)
}
err = xlsx.AddPictureFromBytes("Sheet1", "A2", "", "Excel Logo", ".jpg", file)
if err != nil {
fmt.Println(err)
}
err = xlsx.SaveAs("./Book1.xlsx")
if err != nil {
fmt.Println(err)
}
}
func (*File) AddShape ¶
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:
xlsx.AddShape("Sheet1", "G6", `{"type":"rect","color":{"line":"#4286F4","fill":"#8eb9ff"},"paragraph":[{"text":"Rectangle Shape","font":{"bold":true,"italic":true,"family":"Berlin Sans FB Demi","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) AddTable ¶
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:
xlsx.AddTable("Sheet1", "A1", "D5", ``)
Create a table of F2:H6 on Sheet2 with format set:
xlsx.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 at least two lines include string type header. Multiple tables coordinate areas 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) AutoFilter ¶
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 = xlsx.AutoFilter("Sheet1", "A1", "D4", "")
Filter data in an autofilter:
err = xlsx.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) CopySheet ¶
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 := xlsx.NewSheet("Sheet2")
err := xlsx.CopySheet(1, index)
return err
func (*File) DeleteSheet ¶
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) GetActiveSheetIndex ¶
GetActiveSheetIndex provides a function to get active sheet of XLSX. If not found the active sheet will be return integer 0.
func (*File) GetCellFormula ¶
GetCellFormula provides a function to get formula from cell by given worksheet name and axis in XLSX file.
func (*File) GetCellHyperLink ¶
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 := xlsx.GetCellHyperLink("Sheet1", "H6")
func (*File) GetCellStyle ¶
GetCellStyle provides a function to get cell style index by given worksheet name and cell coordinates.
func (*File) GetCellValue ¶
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 ¶ added in v1.3.0
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:
xlsx.GetColOutlineLevel("Sheet1", "D")
func (*File) GetColVisible ¶
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:
xlsx.GetColVisible("Sheet1", "D")
func (*File) GetColWidth ¶
GetColWidth provides a function to get column width by given worksheet name and column index.
func (*File) GetComments ¶ added in v1.4.0
GetComments retrieves all comments and returns a map of worksheet name to the worksheet comments.
func (*File) GetPicture ¶
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:
xlsx, err := excelize.OpenFile("./Book1.xlsx")
if err != nil {
fmt.Println(err)
return
}
file, raw := xlsx.GetPicture("Sheet1", "A2")
if file == "" {
return
}
err := ioutil.WriteFile(file, raw, 0644)
if err != nil {
fmt.Println(err)
}
func (*File) GetRowHeight ¶
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:
xlsx.GetRowHeight("Sheet1", 1)
func (*File) GetRowOutlineLevel ¶ added in v1.3.0
GetRowOutlineLevel provides a function to get outline level number of a single row by given worksheet name and row index. For example, get outline number of row 2 in Sheet1:
xlsx.GetRowOutlineLevel("Sheet1", 2)
func (*File) GetRowVisible ¶
GetRowVisible provides a function to get visible of a single row by given worksheet name and row index. For example, get visible state of row 2 in Sheet1:
xlsx.GetRowVisible("Sheet1", 2)
func (*File) GetRows ¶
GetRows return all the rows in a sheet by given worksheet name (case sensitive). For example:
for _, row := range xlsx.GetRows("Sheet1") {
for _, colCell := range row {
fmt.Print(colCell, "\t")
}
fmt.Println()
}
func (*File) GetSheetIndex ¶
GetSheetIndex provides a function to get worksheet index of XLSX by given sheet name. If given worksheet name is invalid, will return an integer type value 0.
func (*File) GetSheetMap ¶
GetSheetMap provides a function to get worksheet name and index map of XLSX. For example:
xlsx, err := excelize.OpenFile("./Book1.xlsx")
if err != nil {
return
}
for index, name := range xlsx.GetSheetMap() {
fmt.Println(index, name)
}
func (*File) GetSheetName ¶
GetSheetName provides a function to get worksheet name of XLSX by given worksheet index. If given sheet index is invalid, will return an empty string.
func (*File) GetSheetPrOptions ¶ added in v1.2.0
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)
Example ¶
package main
import (
"fmt"
"github.com/360EntSecGroup-Skylar/excelize"
)
func main() {
xl := excelize.NewFile()
const sheet = "Sheet1"
var (
codeName excelize.CodeName
enableFormatConditionsCalculation excelize.EnableFormatConditionsCalculation
published excelize.Published
fitToPage excelize.FitToPage
autoPageBreaks excelize.AutoPageBreaks
)
if err := xl.GetSheetPrOptions(sheet,
&codeName,
&enableFormatConditionsCalculation,
&published,
&fitToPage,
&autoPageBreaks,
); err != nil {
panic(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)
}
Output: Defaults: - codeName: "" - enableFormatConditionsCalculation: true - published: true - fitToPage: false - autoPageBreaks: false
func (*File) GetSheetViewOptions ¶ added in v1.2.0
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)
Example:
var showGridLines excelize.ShowGridLines
err = f.GetSheetViewOptions("Sheet1", -1, &showGridLines)
Example ¶
package main
import (
"fmt"
"github.com/360EntSecGroup-Skylar/excelize"
)
func main() {
xl := excelize.NewFile()
const sheet = "Sheet1"
var (
defaultGridColor excelize.DefaultGridColor
rightToLeft excelize.RightToLeft
showFormulas excelize.ShowFormulas
showGridLines excelize.ShowGridLines
showRowColHeaders excelize.ShowRowColHeaders
zoomScale excelize.ZoomScale
)
if err := xl.GetSheetViewOptions(sheet, 0,
&defaultGridColor,
&rightToLeft,
&showFormulas,
&showGridLines,
&showRowColHeaders,
&zoomScale,
); err != nil {
panic(err)
}
fmt.Println("Default:")
fmt.Println("- defaultGridColor:", defaultGridColor)
fmt.Println("- rightToLeft:", rightToLeft)
fmt.Println("- showFormulas:", showFormulas)
fmt.Println("- showGridLines:", showGridLines)
fmt.Println("- showRowColHeaders:", showRowColHeaders)
fmt.Println("- zoomScale:", zoomScale)
if err := xl.SetSheetViewOptions(sheet, 0, excelize.ShowGridLines(false)); err != nil {
panic(err)
}
if err := xl.GetSheetViewOptions(sheet, 0, &showGridLines); err != nil {
panic(err)
}
fmt.Println("After change:")
fmt.Println("- showGridLines:", showGridLines)
}
Output: Default: - defaultGridColor: true - rightToLeft: false - showFormulas: false - showGridLines: true - showRowColHeaders: true - zoomScale: 0 After change: - showGridLines: false
func (*File) GetSheetVisible ¶
GetSheetVisible provides a function to get worksheet visible by given worksheet name. For example, get visible state of Sheet1:
xlsx.GetSheetVisible("Sheet1")
func (*File) InsertCol ¶
InsertCol provides a function to insert a new column before given column index. For example, create a new column before column C in Sheet1:
xlsx.InsertCol("Sheet1", "C")
func (*File) InsertRow ¶
InsertRow provides a function to insert a new row before given row index. For example, create a new row before row 3 in Sheet1:
xlsx.InsertRow("Sheet1", 2)
func (*File) MergeCell ¶
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:
xlsx.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.
func (*File) NewConditionalStyle ¶
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 ¶
NewSheet provides a function to create a new sheet by given worksheet name, when creating a new XLSX file, the default sheet will be create, when you create a new file.
func (*File) NewStyle ¶
NewStyle provides a function to create style for cells by given style format. 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:
xlsx := excelize.NewFile()
xlsx.SetCellValue("Sheet1", "A6", 42920.5)
style, _ := xlsx.NewStyle(`{"custom_number_format": "[$-380A]dddd\\,\\ dd\" de \"mmmm\" de \"yyyy;@"}`)
xlsx.SetCellStyle("Sheet1", "A6", "A6", style)
Cell Sheet1!A6 in the Excel Application: martes, 04 de Julio de 2017
func (*File) RemoveCol ¶
RemoveCol provides a function to remove single column by given worksheet name and column index. For example, remove column C in Sheet1:
xlsx.RemoveCol("Sheet1", "C")
func (*File) RemoveRow ¶
RemoveRow provides a function to remove single row by given worksheet name and row index. For example, remove row 3 in Sheet1:
xlsx.RemoveRow("Sheet1", 2)
func (*File) Rows ¶ added in v1.3.0
Rows return a rows iterator. For example:
rows, err := xlsx.Rows("Sheet1")
for rows.Next() {
for _, colCell := range rows.Columns() {
fmt.Print(colCell, "\t")
}
fmt.Println()
}
func (*File) SaveAs ¶
SaveAs provides a function to create or update to an xlsx file at the provided path.
func (*File) SetActiveSheet ¶
SetActiveSheet provides a function to set default active worksheet of XLSX by given index. Note that active index is different with the index that got by function GetSheetMap, and it should be greater than 0 and less than total worksheet numbers.
func (*File) SetCellBool ¶ added in v1.3.0
SetCellBool provides a function to set bool type value of a cell by given worksheet name, cell coordinates and cell value.
func (*File) SetCellDefault ¶
SetCellDefault provides a function to set string type value of a cell as default format without escaping the cell.
func (*File) SetCellFormula ¶
SetCellFormula provides a function to set cell formula by given string and worksheet name.
func (*File) SetCellHyperLink ¶
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. The below is example for external link.
xlsx.SetCellHyperLink("Sheet1", "A3", "https://github.com/360EntSecGroup-Skylar/excelize", "External")
// Set underline and font color style for the cell.
style, _ := xlsx.NewStyle(`{"font":{"color":"#1265BE","underline":"single"}}`)
xlsx.SetCellStyle("Sheet1", "A3", "A3", style)
A this is another example for "Location":
xlsx.SetCellHyperLink("Sheet1", "A3", "Sheet1!A40", "Location")
func (*File) SetCellInt ¶
SetCellInt provides a function to set int type value of a cell by given worksheet name, cell coordinates and cell value.
func (*File) SetCellStr ¶
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 ¶
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 := xlsx.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)
}
xlsx.SetCellStyle("Sheet1", "H9", "H9", style)
Set gradient fill with vertical variants shading styles for cell H9 on Sheet1:
style, err := xlsx.NewStyle(`{"fill":{"type":"gradient","color":["#FFFFFF","#E0EBF5"],"shading":1}}`)
if err != nil {
fmt.Println(err)
}
xlsx.SetCellStyle("Sheet1", "H9", "H9", style)
Set solid style pattern fill for cell H9 on Sheet1:
style, err := xlsx.NewStyle(`{"fill":{"type":"pattern","color":["#E0EBF5"],"pattern":1}}`)
if err != nil {
fmt.Println(err)
}
xlsx.SetCellStyle("Sheet1", "H9", "H9", style)
Set alignment style for cell H9 on Sheet1:
style, err := xlsx.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)
}
xlsx.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:
xlsx.SetCellValue("Sheet1", "H9", 42920.5)
style, err := xlsx.NewStyle(`{"number_format": 22}`)
if err != nil {
fmt.Println(err)
}
xlsx.SetCellStyle("Sheet1", "H9", "H9", style)
Set font style for cell H9 on Sheet1:
style, err := xlsx.NewStyle(`{"font":{"bold":true,"italic":true,"family":"Berlin Sans FB Demi","size":36,"color":"#777777"}}`)
if err != nil {
fmt.Println(err)
}
xlsx.SetCellStyle("Sheet1", "H9", "H9", style)
Hide and lock for cell H9 on Sheet1:
style, err := xlsx.NewStyle(`{"protection":{"hidden":true, "locked":true}}`)
if err != nil {
fmt.Println(err)
}
xlsx.SetCellStyle("Sheet1", "H9", "H9", style)
func (*File) SetCellValue ¶
SetCellValue provides a function to set value of a cell. 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 ¶ added in v1.3.0
SetColOutlineLevel provides a function to set outline level of a single column by given worksheet name and column name. For example, set outline level of column D in Sheet1 to 2:
xlsx.SetColOutlineLevel("Sheet1", "D", 2)
func (*File) SetColVisible ¶
SetColVisible provides a function to set visible of a single column by given worksheet name and column name. For example, hide column D in Sheet1:
xlsx.SetColVisible("Sheet1", "D", false)
func (*File) SetColWidth ¶
SetColWidth provides a function to set the width of a single column or multiple columns. For example:
xlsx := excelize.NewFile()
xlsx.SetColWidth("Sheet1", "A", "H", 20)
err := xlsx.Save()
if err != nil {
fmt.Println(err)
}
func (*File) SetConditionalFormat ¶
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:
xlsx.SetConditionalFormat("Sheet1", "D1:D10", fmt.Sprintf(`[{"type":"cell","criteria":">","format":%d,"value":"6"}]`, format))
The value property can also be an cell reference:
xlsx.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 = xlsx.NewConditionalStyle(`{"font":{"color":"#9A0511"},"fill":{"type":"pattern","color":["#FEC7CE"],"pattern":1}}`)
if err != nil {
fmt.Println(err)
}
xlsx.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 = xlsx.NewConditionalStyle(`{"font":{"color":"#9A0511"},"fill":{"type":"pattern","color":["#FEC7CE"],"pattern":1}}`)
// Light yellow format for neutral conditional.
format2, err = xlsx.NewConditionalStyle(`{"font":{"color":"#9B5713"},"fill":{"type":"pattern","color":["#FEEAA0"],"pattern":1}}`)
// Light green format for good conditional.
format3, err = xlsx.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...
xlsx.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...
xlsx.SetConditionalFormat("Sheet1", "A1:A10", fmt.Sprintf(`[{"type":"average","criteria":"=","format":%d, "above_average": true}]`, format1))
// Top/Bottom rules: Below Average...
xlsx.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...
xlsx.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...
xlsx.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.
xlsx.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:
xlsx.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.
xlsx.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.
xlsx.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.
xlsx.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.
xlsx.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) SetPanes ¶
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:
xlsx.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:
xlsx.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:
xlsx.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:
xlsx.SetPanes("Sheet1", `{"freeze":false,"split":false}`)
func (*File) SetRowHeight ¶
SetRowHeight provides a function to set the height of a single row. For example, set the height of the first row in Sheet1:
xlsx.SetRowHeight("Sheet1", 1, 50)
func (*File) SetRowOutlineLevel ¶ added in v1.3.0
SetRowOutlineLevel provides a function to set outline level number of a single row by given worksheet name and row index. For example, outline row 2 in Sheet1 to level 1:
xlsx.SetRowOutlineLevel("Sheet1", 2, 1)
func (*File) SetRowVisible ¶
SetRowVisible provides a function to set visible of a single row by given worksheet name and row index. For example, hide row 2 in Sheet1:
xlsx.SetRowVisible("Sheet1", 2, false)
func (*File) SetSheetBackground ¶
SetSheetBackground provides a function to set background picture by given worksheet name and file path.
func (*File) SetSheetName ¶
SetSheetName provides a function to set the worksheet name be given old and new worksheet name. 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 ¶ added in v1.2.0
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)
Example ¶
package main
import (
"github.com/360EntSecGroup-Skylar/excelize"
)
func main() {
xl := excelize.NewFile()
const sheet = "Sheet1"
if err := xl.SetSheetPrOptions(sheet,
excelize.CodeName("code"),
excelize.EnableFormatConditionsCalculation(false),
excelize.Published(false),
excelize.FitToPage(true),
excelize.AutoPageBreaks(true),
); err != nil {
panic(err)
}
}
Output:
func (*File) SetSheetRow ¶ added in v1.3.0
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:
xlsx.SetSheetRow("Sheet1", "B6", &[]interface{}{"1", nil, 2})
func (*File) SetSheetViewOptions ¶ added in v1.2.0
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)
Example:
err = f.SetSheetViewOptions("Sheet1", -1, ShowGridLines(false))
Example ¶
package main
import (
"fmt"
"github.com/360EntSecGroup-Skylar/excelize"
)
func main() {
xl := excelize.NewFile()
const sheet = "Sheet1"
if err := xl.SetSheetViewOptions(sheet, 0,
excelize.DefaultGridColor(false),
excelize.RightToLeft(false),
excelize.ShowFormulas(true),
excelize.ShowGridLines(true),
excelize.ShowRowColHeaders(true),
excelize.ZoomScale(80),
); err != nil {
panic(err)
}
var zoomScale excelize.ZoomScale
fmt.Println("Default:")
fmt.Println("- zoomScale: 80")
if err := xl.SetSheetViewOptions(sheet, 0, excelize.ZoomScale(500)); err != nil {
panic(err)
}
if err := xl.GetSheetViewOptions(sheet, 0, &zoomScale); err != nil {
panic(err)
}
fmt.Println("Used out of range value:")
fmt.Println("- zoomScale:", zoomScale)
if err := xl.SetSheetViewOptions(sheet, 0, excelize.ZoomScale(123)); err != nil {
panic(err)
}
if err := xl.GetSheetViewOptions(sheet, 0, &zoomScale); err != nil {
panic(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 ¶
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 http://msdn.microsoft.com/en-us/library/office/documentformat.openxml.spreadsheet.sheetstatevalues.aspx
visible hidden veryHidden
For example, hide Sheet1:
xlsx.SetSheetVisible("Sheet1", false)
func (*File) UpdateLinkedValue ¶
func (f *File) UpdateLinkedValue()
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?forum=excel
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>
type HSL ¶ added in v1.4.0
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.
type Rows ¶ added in v1.3.0
type Rows struct {
// contains filtered or unexported fields
}
Rows defines an iterator to a sheet
type SheetPrOption ¶ added in v1.2.0
type SheetPrOption interface {
// contains filtered or unexported methods
}
SheetPrOption is an option of a view of a worksheet. See SetSheetPrOptions().
type SheetPrOptionPtr ¶ added in v1.2.0
type SheetPrOptionPtr interface {
SheetPrOption
// contains filtered or unexported methods
}
SheetPrOptionPtr is a writable SheetPrOption. See GetSheetPrOptions().
type SheetViewOption ¶ added in v1.2.0
type SheetViewOption interface {
// contains filtered or unexported methods
}
SheetViewOption is an option of a view of a worksheet. See SetSheetViewOptions().
type SheetViewOptionPtr ¶ added in v1.2.0
type SheetViewOptionPtr interface {
SheetViewOption
// contains filtered or unexported methods
}
SheetViewOptionPtr is a writable SheetViewOption. See GetSheetViewOptions().
type ShowRowColHeaders ¶ added in v1.2.0
type ShowRowColHeaders bool
ShowRowColHeaders is a SheetViewOption.
Source Files
¶
- cell.go
- chart.go
- col.go
- comment.go
- datavalidation.go
- date.go
- excelize.go
- file.go
- hsl.go
- lib.go
- picture.go
- rows.go
- shape.go
- sheet.go
- sheetpr.go
- sheetview.go
- styles.go
- table.go
- templates.go
- vmlDrawing.go
- xmlChart.go
- xmlComments.go
- xmlContentTypes.go
- xmlDecodeDrawing.go
- xmlDrawing.go
- xmlSharedStrings.go
- xmlStyles.go
- xmlTable.go
- xmlTheme.go
- xmlWorkbook.go
- xmlWorksheet.go