README
¶
Excelize
Introduction
Excelize is a library written in pure Golang 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.
Basic Usage
Installation
go get github.com/xuri/excelize
Create XLSX file
Here is a minimal example usage that will create XLSX file.
package main
import (
"fmt"
"os"
"github.com/xuri/excelize"
)
func main() {
xlsx := excelize.NewFile()
// Create a new sheet.
xlsx.NewSheet(2, "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(2)
// Save xlsx file by the given path.
err := xlsx.SaveAs("./Workbook.xlsx")
if err != nil {
fmt.Println(err)
os.Exit(1)
}
}
Reading XLSX file
The following constitutes the bare to read a XLSX document.
package main
import (
"fmt"
"os"
"strconv"
"github.com/xuri/excelize"
)
func main() {
xlsx, err := excelize.OpenFile("./Workbook.xlsx")
if err != nil {
fmt.Println(err)
os.Exit(1)
}
// Get value from cell by given sheet index and axis.
cell := xlsx.GetCellValue("Sheet1", "B2")
fmt.Println(cell)
// Get sheet index.
index := xlsx.GetSheetIndex("Sheet2")
// Get all the rows in a sheet.
rows := xlsx.GetRows("sheet" + strconv.Itoa(index))
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 sheet at all.
package main
import (
"fmt"
"os"
"github.com/xuri/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":"bar3D","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 Line Chart"}}`)
// Save xlsx file by the given path.
err := xlsx.SaveAs("./Workbook.xlsx")
if err != nil {
fmt.Println(err)
os.Exit(1)
}
}
Add picture to XLSX file
package main
import (
"fmt"
"os"
_ "image/gif"
_ "image/jpeg"
_ "image/png"
"github.com/xuri/excelize"
)
func main() {
xlsx, err := excelize.OpenFile("./Workbook.xlsx")
if err != nil {
fmt.Println(err)
os.Exit(1)
}
// Insert a picture.
err = xlsx.AddPicture("Sheet1", "A2", "./image1.png", "")
if err != nil {
fmt.Println(err)
}
// Insert a picture to sheet 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)
os.Exit(1)
}
}
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
¶
Index ¶
- Constants
- func ReadZipReader(r *zip.Reader) (map[string]string, int, error)
- func TitleToNumber(s string) int
- func ToAlphaString(value int) string
- type File
- func (f *File) AddChart(sheet, cell, format string)
- func (f *File) AddComment(sheet, cell, format string)
- func (f *File) AddPicture(sheet, cell, picture, format string) error
- func (f *File) AddShape(sheet, cell, format string)
- func (f *File) AddTable(sheet, hcell, vcell, format string)
- 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) GetCellStyle(sheet, axis string) int
- func (f *File) GetCellValue(sheet, axis string) string
- func (f *File) GetColVisible(sheet, column string) bool
- func (f *File) GetColWidth(sheet, column string) float64
- func (f *File) GetPicture(sheet, cell string) (string, []byte)
- func (f *File) GetRowHeight(sheet string, row int) float64
- 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) GetSheetVisible(name string) bool
- func (f *File) MergeCell(sheet, hcell, vcell string)
- func (f *File) NewSheet(index int, name string)
- func (f *File) NewStyle(style string) (int, error)
- func (f *File) Save() error
- func (f *File) SaveAs(name string) error
- func (f *File) SetActiveSheet(index int)
- func (f *File) SetCellDefault(sheet, axis, value string)
- func (f *File) SetCellFormula(sheet, axis, formula string)
- func (f *File) SetCellHyperLink(sheet, axis, link 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) SetColVisible(sheet, column string, visible bool)
- func (f *File) SetColWidth(sheet, startcol, endcol string, width float64)
- func (f *File) SetRowHeight(sheet string, rowIndex int, height float64)
- 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) SetSheetVisible(name string, visible bool)
- func (f *File) UpdateLinkedValue()
- func (f *File) Write(w io.Writer) error
Constants ¶
const ( Bar = "bar" Bar3D = "bar3D" Doughnut = "doughnut" Line = "line" Pie = "pie" Pie3D = "pie3D" Radar = "radar" Scatter = "scatter" )
This section defines the currently supported chart types.
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 ¶
This section is empty.
Functions ¶
func ReadZipReader ¶
ReadZipReader can be used to read an XLSX in memory without touching the filesystem.
func TitleToNumber ¶
TitleToNumber provides function to convert Excel sheet column title to int (this function doesn't do value check currently). For example convert AK to column title 36:
excelize.TitleToNumber("AK")
func ToAlphaString ¶
ToAlphaString provides function to convert integer to Excel sheet column title. For example convert 36 to column title AK:
excelize.ToAlphaString(36)
Types ¶
type File ¶
type File struct {
ContentTypes *xlsxTypes
Path string
Sheet map[string]*xlsxWorksheet
SheetCount int
Styles *xlsxStyleSheet
WorkBook *xlsxWorkbook
WorkBookRels *xlsxWorkbookRels
XLSX map[string]string
// contains filtered or unexported fields
}
File define a populated XLSX file struct.
func NewFile ¶
func NewFile() *File
NewFile provides 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 bar chart with data Sheet1!$A$29:$D$32:
package main
import (
"fmt"
"os"
"github.com/xuri/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", "F2", `{"type":"bar3D","series":[{"name":"=Sheet1!$A$30","categories":"=Sheet1!$B$29:$D$29","values":"=Sheet1!$B$30:$D$30"},{"name":"=Sheet1!$A$31","categories":"=Sheet1!$B$29:$D$29","values":"=Sheet1!$B$31:$D$31"},{"name":"=Sheet1!$A$32","categories":"=Sheet1!$B$29:$D$29","values":"=Sheet1!$B$32:$D$32"}],"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 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},"show_blanks_as":"zero"}`)
// Save xlsx file by the given path.
err := xlsx.SaveAs("./Workbook.xlsx")
if err != nil {
fmt.Println(err)
os.Exit(1)
}
}
The following shows the type of chart supported by excelize:
| Type | Chart | +----------+----------------+ | bar | bar chart | | bar3D | 3D bar 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.
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) 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"
"os"
_ "image/gif"
_ "image/jpeg"
_ "image/png"
"github.com/xuri/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 to sheet with scaling.
err = xlsx.AddPicture("Sheet1", "D2", "./image1.png", `{"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)
}
err = xlsx.SaveAs("./Workbook.xlsx")
if err != nil {
fmt.Println(err)
os.Exit(1)
}
}
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 chart 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 sheet index, 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_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. The two chart coordinate areas can not have an intersection.
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 sheet index, 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 worksheet 1:
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 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...
xlsx.NewSheet(2, "sheet2")
err := xlsx.CopySheet(1, 2)
if err != nil {
fmt.Println(err)
os.Exit(1)
}
func (*File) DeleteSheet ¶
DeleteSheet provides function to delete worksheet in a workbook by given sheet 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 function to get active sheet of XLSX. If not found the active sheet will be return integer 0.
func (*File) GetCellFormula ¶
GetCellFormula provides function to get formula from cell by given sheet index and axis in XLSX file.
func (*File) GetCellStyle ¶
GetCellStyle provides function to get cell style index by given worksheet name and cell coordinates.
func (*File) GetCellValue ¶
GetCellValue provides function to get formatted value from cell by given sheet index 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) GetColVisible ¶
GetColVisible provides a function to get visible of a single column by given worksheet index and column name. For example, get visible state of column D in Sheet1:
xlsx.GetColVisible("Sheet1", "D")
func (*File) GetColWidth ¶
GetColWidth provides function to get column width by given sheet name and column index.
func (*File) GetPicture ¶
GetPicture provides 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("./Workbook.xlsx")
if err != nil {
fmt.Println(err)
os.Exit(1)
}
file, raw := xlsx.GetPicture("Sheet1", "A2")
if file == "" {
os.Exit(1)
}
err := ioutil.WriteFile(file, raw, 0644)
if err != nil {
fmt.Println(err)
os.Exit(1)
}
func (*File) GetRowHeight ¶
GetRowHeight provides function to get row height by given worksheet name and row index.
func (*File) GetRowVisible ¶
GetRowVisible provides a function to get visible of a single row by given worksheet index and row index. For example, get visible state of row 3 in Sheet1:
xlsx.GetRowVisible("Sheet1", 2)
func (*File) GetRows ¶
GetRows return all the rows in a sheet by given "sheet" + index. For now you should use sheet_name like "sheet3" where "sheet" is a constant part and "3" is a sheet number. For example, if sheet named as "SomeUniqueData" and it is second if spreadsheet program interface - you should use "sheet2" here. For example:
index := xlsx.GetSheetIndex("Sheet2")
rows := xlsx.GetRows("sheet" + strconv.Itoa(index))
for _, row := range rows {
for _, colCell := range row {
fmt.Print(colCell, "\t")
}
fmt.Println()
}
func (*File) GetSheetIndex ¶
GetSheetIndex provides function to get worksheet index of XLSX by given sheet name. If given sheet name is invalid, will return an integer type value 0.
func (*File) GetSheetMap ¶
GetSheetMap provides function to get sheet map of XLSX. For example:
xlsx, err := excelize.OpenFile("./Workbook.xlsx")
if err != nil {
fmt.Println(err)
os.Exit(1)
}
for k, v := range xlsx.GetSheetMap() {
fmt.Println(k, v)
}
func (*File) GetSheetName ¶
GetSheetName provides function to get sheet name of XLSX by given worksheet index. If given sheet index is invalid, will return an empty string.
func (*File) GetSheetVisible ¶
GetSheetVisible provides function to get worksheet visible by given worksheet name. For example, get visible state of Sheet1:
xlsx.GetSheetVisible("Sheet1")
func (*File) MergeCell ¶
MergeCell provides 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) NewSheet ¶
NewSheet provides function to create a new sheet by given index, when creating a new XLSX file, the default sheet will be create, when you create a new file, you need to ensure that the index is continuous.
func (*File) NewStyle ¶
NewStyle provides 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 | @ |
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. Vigin 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 |
func (*File) SaveAs ¶
SaveAs provides function to create or update to an xlsx file at the provided path.
func (*File) SetActiveSheet ¶
SetActiveSheet provides function to set default active sheet of XLSX by given index.
func (*File) SetCellDefault ¶
SetCellDefault provides function to set string type value of a cell as default format without escaping the cell.
func (*File) SetCellFormula ¶
SetCellFormula provides function to set cell formula by given string and sheet index.
func (*File) SetCellHyperLink ¶
SetCellHyperLink provides function to set cell hyperlink by given sheet index and link URL address. Only support external link currently.
func (*File) SetCellInt ¶
SetCellInt provides function to set int type value of a cell by given worksheet name, cell coordinates and cell value.
func (*File) SetCellStr ¶
SetCellStr provides 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 function to add style attribute for cells by given worksheet sheet index, 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)
func (*File) SetCellValue ¶
SetCellValue provides function to set value of a cell. The following shows the supported data types:
int int8 int16 int32 int64 float32 float64 string []byte time.Time 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) SetColVisible ¶
SetColVisible provides a function to set visible of a single column by given worksheet index and column name. For example, hide column D in Sheet1:
xlsx.SetColVisible("Sheet1", "D", false)
func (*File) SetColWidth ¶
SetColWidth provides 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)
os.Exit(1)
}
func (*File) SetRowHeight ¶
SetRowHeight provides a function to set the height of a single row. For example:
xlsx := excelize.NewFile()
xlsx.SetRowHeight("Sheet1", 0, 50)
err := xlsx.Save()
if err != nil {
fmt.Println(err)
os.Exit(1)
}
func (*File) SetRowVisible ¶
SetRowVisible provides a function to set visible of a single row by given worksheet index and row index. For example, hide row 3 in Sheet1:
xlsx.SetRowVisible("Sheet1", 2, false)
func (*File) SetSheetBackground ¶
SetSheetBackground provides function to set background picture by given sheet index.
func (*File) SetSheetName ¶
SetSheetName provides function to set the sheet name be given old and new sheet 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) SetSheetVisible ¶
SetSheetVisible provides 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>
Source Files
¶
- cell.go
- chart.go
- col.go
- comment.go
- date.go
- excelize.go
- file.go
- lib.go
- picture.go
- rows.go
- shape.go
- sheet.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
- xmlWorkbook.go
- xmlWorksheet.go