Utils

Add table

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:

Add table

err := f.AddTable("Sheet1", &excelize.Table{Range: "A1:D5"})
  • Example 2, create a table of F2:H6 on Sheet2 with the format set:

Add table with format set

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

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

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

StyleName: The built-in table style names:

TableStyleLight1 - TableStyleLight21
TableStyleMedium1 - TableStyleMedium28
TableStyleDark1 - TableStyleDark11
Index Style Index Style Index Style
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

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

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

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

Auto filter

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:

Add auto filter

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

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

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

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

JoinCellName joins cell name from column name and row number.

Column Name To Number

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

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

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

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

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

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

GetConditionalStyle returns conditional format style definition by specified style index.

Set conditional format

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
Value
average Criteria
duplicate (none)
unique (none)
top Criteria
Value
bottom Criteria
Value
blanks (none)
no_blanks (none)
errors (none)
no_errors (none)
2_color_scale MinType
MaxType
MinValue
MaxValue
MinColor
MaxColor
3_color_scale MinType
MidType
MaxType
MinValue
MidValue
MaxValue
MinColor
MidColor
MaxColor
data_bar MinType
MaxType
MinValue
MaxValue
BarBorderColor
BarColor
BarDirection
BarOnly
BarSolid
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:

Set conditional formatting in a range of cells

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

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

GetConditionalFormats returns conditional format settings by given worksheet name.

Remove conditional format

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

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:

Frozen column

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:

Freeze columns and set active cell ranges

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:

Create split panes

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

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

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

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

RGBToHSL converts an RGB triple to a HSL triple.

Convert HSL to RGB

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

HSLToRGB converts an HSL triple to a RGB triple.

File Writer

Write

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

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

WriteTo

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

WriteTo implements io.WriterTo to write the file.

WriteToBuffer

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

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

Add VBA Project

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

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

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

Charset transcoder

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

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

results matching ""

    No results matching ""