Utils

Add table {#AddTable}

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

AddTable provides the method to add a table in a worksheet by given worksheet name, range reference, and format set.

Example 1, create a table of A1:D5 on Sheet1:

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

Example 2, create a table of F2:H6 on Sheet2 with the format set:

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

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

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

StyleName: The built-in table style names:

TableStyleLight1 - TableStyleLight21
TableStyleMedium1 - TableStyleMedium28
TableStyleDark1 - TableStyleDark11

Index	Style	Index	Style	Index
	TableStyleLight1		TableStyleLight2
TableStyleLight3		TableStyleLight4		TableStyleLight5
TableStyleLight6		TableStyleLight7		TableStyleLight8
TableStyleLight9		TableStyleLight10		TableStyleLight11
TableStyleLight12		TableStyleLight13		TableStyleLight14
TableStyleLight15		TableStyleLight16		TableStyleLight17
TableStyleLight18		TableStyleLight19		TableStyleLight20
TableStyleLight21		TableStyleMedium1		TableStyleMedium2
TableStyleMedium3		TableStyleMedium4		TableStyleMedium5
TableStyleMedium6		TableStyleMedium7		TableStyleMedium8
TableStyleMedium9		TableStyleMedium10		TableStyleMedium11
TableStyleMedium12		TableStyleMedium13		TableStyleMedium14
TableStyleMedium15		TableStyleMedium16		TableStyleMedium17
TableStyleMedium18		TableStyleMedium19		TableStyleMedium20
TableStyleMedium21		TableStyleMedium22		TableStyleMedium23
TableStyleMedium24		TableStyleMedium25		TableStyleMedium26
TableStyleMedium27		TableStyleMedium28		TableStyleDark1
TableStyleDark2		TableStyleDark3		TableStyleDark4
TableStyleDark5		TableStyleDark6		TableStyleDark7
TableStyleDark8		TableStyleDark9		TableStyleDark10
TableStyleDark11

Get tables {#GetTables}

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

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

Delete table {#DeleteTable}

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

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

Auto filter {#AutoFilter}

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

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

Example 1, applying an auto filter to a cell range A1:D4 in the Sheet1:

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

Example 2, filter data in an auto filter:

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

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

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

Setting 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

Office Excel also allows some simple string matching operations:

x == b*      // begins with b
x != b*      // doesn't begin with b
x == *b      // ends with b
x != *b      // doesn't end with b
x == *b*     // contains b
x != *b*     // doesn't 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

Update linked value {#UpdateLinkedValue}

func (f *File) UpdateLinkedValue() error

UpdateLinkedValue fix linked values within a spreadsheet are not updating in Office Excel 2007 and 2010. This function will remove the value tag when met a cell have a linked value. Reference https://learn.microsoft.com/en-us/archive/msdn-technet-forums/e16bae1f-6a2c-4325-8013-e989a3479066. Notice: after open the spreadsheet file Excel will be updating the linked value and generate a new value and will prompt the save file or not.

The effect of clearing the cell cache on the workbook appears as a modification to the <v> tag, for example, the cell cache before clearing:

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

After clearing the cell cache:

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

Split Cell Name {#SplitCellName}

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

SplitCellName splits cell name to column name and row number. For example:

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

Join Cell Name {#JoinCellName}

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

JoinCellName joins cell name from column name and row number.

Column Name To Number {#ColumnNameToNumber}

func ColumnNameToNumber(name string) (int, error)

ColumnNameToNumber provides a function to convert Excel sheet column name to int. Column name case insensitive. The function returns an error if the column name incorrect. For example:

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

Column Number To Name {#ColumnNumberToName}

func ColumnNumberToName(num int) (string, error)

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

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

Cell Name To Coordinates {#CellNameToCoordinates}

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

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

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

Coordinates To Cell Name {#CoordinatesToCellName}

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

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

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

Create conditional style {#NewConditionalStyle}

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

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

Get conditional style {#GetConditionalStyle}

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

GetConditionalStyle returns conditional format style definition by specified style index.

Set conditional format {#SetConditionalFormat}

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

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

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

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

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

Text description character	Symbolic representation
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 that are specific to other conditional format types are shown in the relevant sections below.

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

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

The Value property can also be a cell reference:

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

The available min/mid/max types are:

Parameter	Explanation
min	MinValue value (for `MinType` only)
num	Numeric
percent	Percentage
percentile	Percentile
formula	Formula
max	MaxValue (for `MaxType` only)

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

MaxType - Same as MinType, see above.

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

MidValue - Used for 3_color_scale. Same as MinValue, see above.

MaxValue - Same as MinValue, see above.

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

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

MidColor - Used for 3_color_scale. Same as MinColor, see above.

MaxColor - Same as MinColor, see above.

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

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

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

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

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

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

IconStyle - The available options are:

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

ReverseIcons - Used for set reversed icons sets.

IconsOnly - Used for set displayed without the cell value.

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

For example, highlight highest and lowest values in a range of cells A1:D4 by set conditional formatting on Sheet1:

func main() {
    f := excelize.NewFile()
    defer func() {
        if err := f.Close(); err != nil {
            fmt.Println(err)
        }
    }()
    for r := 1; r <= 4; r++ {
        row := []int{
            rand.Intn(100), rand.Intn(100), rand.Intn(100), rand.Intn(100),
        }
        if err := f.SetSheetRow("Sheet1", fmt.Sprintf("A%d", r), &row); err != nil {
            fmt.Println(err)
            return
        }
    }
    red, err := f.NewConditionalStyle(
        &excelize.Style{
            Font: &excelize.Font{
                Color: "9A0511",
            },
            Fill: excelize.Fill{
                Type:    "pattern",
                Color:   []string{"FEC7CE"},
                Pattern: 1,
            },
        },
    )
    if err != nil {
        fmt.Println(err)
        return
    }
    if err := f.SetConditionalFormat("Sheet1", "A1:D4",
        []excelize.ConditionalFormatOptions{
            {
                Type:     "bottom",
                Criteria: "=",
                Value:    "1",
                Format:   &red,
            },
        },
    ); err != nil {
        fmt.Println(err)
        return
    }
    green, err := f.NewConditionalStyle(
        &excelize.Style{
            Font: &excelize.Font{
                Color: "09600B",
            },
            Fill: excelize.Fill{
                Type:    "pattern",
                Color:   []string{"C7EECF"},
                Pattern: 1,
            },
        },
    )
    if err != nil {
        fmt.Println(err)
        return
    }
    if err := f.SetConditionalFormat("Sheet1", "A1:D4",
        []excelize.ConditionalFormatOptions{
            {
                Type:     "top",
                Criteria: "=",
                Value:    "1",
                Format:   &green,
            },
        },
    ); err != nil {
        fmt.Println(err)
        return
    }
    if err := f.SaveAs("Book1.xlsx"); err != nil {
        fmt.Println(err)
        return
    }
}

Get conditional format {#GetConditionalFormats}

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

GetConditionalFormats returns conditional format settings by given worksheet name.

Remove conditional format {#UnsetConditionalFormat}

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

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

Set panes {#SetPanes}

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

SetPanes provides a function to create and remove freeze panes and split panes by given worksheet name and panes 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.

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

YSplit - Vertical 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 data type.

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

SQRef - Range of the selection. Can be a non-contiguous set of ranges.

Example 1: freeze column A in the Sheet1 and set the active cell on Sheet1!K16:

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

Example 2: freeze rows 1 to 9 in the Sheet1 and set the active cell ranges on Sheet1!A11:XFD11:

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

Example 3: create split panes in the Sheet1 and set the active cell on Sheet1!J60:

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

Example 4, unfreeze and remove all panes on Sheet1:

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

Get panes {#GetPanes}

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

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

Color {#ThemeColor}

func (f *File) GetBaseColor(hexColor string, indexedColor int, themeColor *int) string

GetBaseColor returns the preferred hex color code by giving hex color code, indexed color, and theme color.

func ThemeColor(baseColor string, tint float64) string

ThemeColor applied the color with tint value.

There 3 kinds of colors for the text in the spreadsheet: hex color, indexed color, and theme color. The priority of these colors is hex color takes precedence over theme color, and the theme color takes precedence over indexed color. In addition, the color also supports applying tint value based on the hex color, so we need to use the ThemeColor function to apply the tint for the based color to get the calculated hex color value. For example:

package main

import (
    "fmt"

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

func main() {
    f, err := excelize.OpenFile("Book1.xlsx")
    if err != nil {
        fmt.Println(err)
        return
    }
    defer func() {
        if err := f.Close(); err != nil {
            fmt.Println(err)
        }
    }()
    runs, err := f.GetCellRichText("Sheet1", "A1")
    if err != nil {
        fmt.Println(err)
        return
    }
    for _, run := range runs {
        var hexColor string
        if run.Font != nil {
            baseColor := f.GetBaseColor(run.Font.Color, run.Font.ColorIndexed, run.Font.ColorTheme)
            hexColor = strings.TrimPrefix(excelize.ThemeColor(baseColor, run.Font.ColorTint), "FF")
        }
        fmt.Printf("text: %s, color: %s\r\n", run.Text, hexColor)
    }
}

Convert RGB to HSL {#RGBToHSL}

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

RGBToHSL converts an RGB triple to a HSL triple.

Convert HSL to RGB {#HSLToRGB}

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

HSLToRGB converts an HSL triple to a RGB triple.

File Writer {#FileWriter}

Write {#Write}

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

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

WriteTo {#WriteTo}

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

WriteTo implements io.WriterTo to write the file.

WriteToBuffer {#WriteToBuffer}

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

WriteToBuffer provides a function to get *bytes.Buffer from the saved file.

Add VBA Project {#AddVBAProject}

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

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

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

Excel date to time {#ExcelDateToTime}

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

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

Charset transcoder {#CharsetTranscoder}

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

CharsetTranscoder set user-defined codepage transcoder function for open the spreadsheet from non-UTF-8 encoding.

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

utils.md

utils.md

Utils

Add table {#AddTable}

Get tables {#GetTables}

Delete table {#DeleteTable}

Auto filter {#AutoFilter}

Update linked value {#UpdateLinkedValue}

Split Cell Name {#SplitCellName}

Join Cell Name {#JoinCellName}

Column Name To Number {#ColumnNameToNumber}

Column Number To Name {#ColumnNumberToName}

Cell Name To Coordinates {#CellNameToCoordinates}

Coordinates To Cell Name {#CoordinatesToCellName}

Create conditional style {#NewConditionalStyle}

Get conditional style {#GetConditionalStyle}

Set conditional format {#SetConditionalFormat}

Get conditional format {#GetConditionalFormats}

Remove conditional format {#UnsetConditionalFormat}

Set panes {#SetPanes}

Get panes {#GetPanes}

Color {#ThemeColor}

Convert RGB to HSL {#RGBToHSL}

Convert HSL to RGB {#HSLToRGB}

File Writer {#FileWriter}

Write {#Write}

WriteTo {#WriteTo}

WriteToBuffer {#WriteToBuffer}

Add VBA Project {#AddVBAProject}

Excel date to time {#ExcelDateToTime}

Charset transcoder {#CharsetTranscoder}

Files

utils.md

Latest commit

History

utils.md

File metadata and controls

Utils

Add table {#AddTable}

Get tables {#GetTables}

Delete table {#DeleteTable}

Auto filter {#AutoFilter}

Update linked value {#UpdateLinkedValue}

Split Cell Name {#SplitCellName}

Join Cell Name {#JoinCellName}

Column Name To Number {#ColumnNameToNumber}

Column Number To Name {#ColumnNumberToName}

Cell Name To Coordinates {#CellNameToCoordinates}

Coordinates To Cell Name {#CoordinatesToCellName}

Create conditional style {#NewConditionalStyle}

Get conditional style {#GetConditionalStyle}

Set conditional format {#SetConditionalFormat}

Get conditional format {#GetConditionalFormats}

Remove conditional format {#UnsetConditionalFormat}

Set panes {#SetPanes}

Get panes {#GetPanes}

Color {#ThemeColor}

Convert RGB to HSL {#RGBToHSL}

Convert HSL to RGB {#HSLToRGB}

File Writer {#FileWriter}

Write {#Write}

WriteTo {#WriteTo}

WriteToBuffer {#WriteToBuffer}

Add VBA Project {#AddVBAProject}

Excel date to time {#ExcelDateToTime}

Charset transcoder {#CharsetTranscoder}