Cell

RichTextRun directly maps the settings of the rich text run.

type RichTextRun struct {
    Font *Font
    Text string
}

HyperlinkOpts can be passed to SetCellHyperlink to set optional hyperlink attributes (e.g. text to display and screen tip text).

type HyperlinkOpts struct {
    Display *string
    Tooltip *string
}

FormulaOpts can be passed to SetCellFormula to use other formula types.

type FormulaOpts struct {
    Type *string // Formula type
    Ref  *string // Shared formula reference
}

Set cell value

func (f *File) SetCellValue(sheet, cell string, value interface{}) error

SetCellValue provides a function to set the value of a cell. This function is concurrency safe. The specified coordinates should not be in the first row of the table, a complex number can be set with string text. The following shows the supported data types:

Note that default date format is m/d/yy h:mm of time.Time type value. You can set numbers format by the SetCellStyle function. If you need to set the specialized date in Excel like January 0, 1900 or February 29, 1900, these times can not representation in Go language time.Time data type. Please set the cell value as number 0 or 60, then create and bind the date-time number format style for the cell.

Set boolean value

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

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

Set RAW value

func (f *File) SetCellDefault(sheet, cell, value string) error

SetCellDefault provides a function to set the string type value of a cell as a default format without escaping the cell.

Set integer value

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

SetCellInt provides a function to set the int type value of a cell by given worksheet name, cell reference, and cell value.

Set unsigned integer value

func (f *File) SetCellUint(sheet, cell string, value uint64) error

SetCellUint provides a function to set unsigned integer data type value of a cell by given worksheet name, cell reference and cell value.

Set floating point value

func (f *File) SetCellFloat(sheet, cell string, value float64, precision, bitSize int) error

SetCellFloat sets a floating point value into a cell. The precision parameter specifies how many places after the decimal will be shown while -1 is a special value that will use as many decimal places as necessary to represent the number. bitSize is 32 or 64 depending on if a float32 or float64 was originally used for the value.

Set string value

func (f *File) SetCellStr(sheet, cell, value string) error

SetCellStr provides a function to set the string type value of a cell. The total number of characters that a cell can contain 32767 characters.

Set cell style

func (f *File) SetCellStyle(sheet, topLeftCell, bottomRightCell string, styleID int) error

SetCellStyle provides a function to add style attribute for cells by given worksheet name, range reference and style ID. This function is concurrency safe. Style indexes can be obtained with the NewStyle function. Note that diagonalDown and diagonalUp type border should use the same color in the same range. SetCellStyle will overwrite the existing styles for the cell, it won't append or merge style with existing styles.

• Example 1, create borders of cell D7 on Sheet1:

style, err := f.NewStyle(&excelize.Style{
    Border: []excelize.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: 8},
        {Type: "diagonalUp", Color: "A020F0", Style: 8},
    },
})
if err != nil {
    fmt.Println(err)
}
err = f.SetCellStyle("Sheet1", "D7", "D7", style)

The four borders of the cell D7 are set with different styles and colors. This is related to the parameters when calling the NewStyle function. You need to set different styles to refer to the documentation for that chapter.

• Example 2, setting the gradient style for the worksheet D7 cell named Sheet1:

style, err := f.NewStyle(&excelize.Style{
    Fill: excelize.Fill{Type: "gradient", Color: []string{"FFFFFF", "E0EBF5"}, Shading: 1},
})
if err != nil {
    fmt.Println(err)
}
err = f.SetCellStyle("Sheet1", "D7", "D7", style)

The cell D7 is set with the color fill of the gradient effect. The gradient fill effect is related to the parameter when the NewStyle function is called. You need to set different styles to refer to the documentation of this chapter.

• Example 3, set a solid fill for the D7 cell named Sheet1:

style, err := f.NewStyle(&excelize.Style{
    Fill: excelize.Fill{Type: "pattern", Color: []string{"E0EBF5"}, Pattern: 1},
})
if err != nil {
    fmt.Println(err)
}
err = f.SetCellStyle("Sheet1", "D7", "D7", style)

The cell D7 is set with a solid fill.

• Example 4, set the character spacing and rotation angle for the D7 cell named Sheet1:

f.SetCellValue("Sheet1", "D7", "Style")
style, err := f.NewStyle(&excelize.Style{
    Alignment: &excelize.Alignment{
        Horizontal:      "center",
        Indent:          1,
        JustifyLastLine: true,
        ReadingOrder:    0,
        RelativeIndent:  1,
        ShrinkToFit:     true,
        TextRotation:    45,
        Vertical:        "",
        WrapText:        true,
    },
})
if err != nil {
    fmt.Println(err)
}
err = f.SetCellStyle("Sheet1", "D7", "D7", style)

• Example 5, the date and time in Excel are represented by real numbers, for example 2017/7/4 12:00:00 PM can be represented by the number 42920.5. Set the time format for the worksheet D7 cell named Sheet1:

f.SetCellValue("Sheet1", "D7", 42920.5)
f.SetColWidth("Sheet1", "D", "D", 13)
style, err := f.NewStyle(&excelize.Style{NumFmt: 22})
if err != nil {
    fmt.Println(err)
}
err = f.SetCellStyle("Sheet1", "D7", "D7", style)

The cell D7 is set to the time format. Note that when the cell width with the time format applied is too narrow to be fully displayed, it will be displayed as ####, you can drag and drop the column width or set the column to the appropriate size by calling the SetColWidth function to make it normal display.

• Example 6, setting the font, font size, color, and skew style for the worksheet D7 cell named Sheet1:

f.SetCellValue("Sheet1", "D7", "Excel")
style, err := f.NewStyle(&excelize.Style{
    Font: &excelize.Font{
        Bold:   true,
        Italic: true,
        Family: "Times New Roman",
        Size:   36,
        Color:  "777777",
    },
})
if err != nil {
    fmt.Println(err)
}
err = f.SetCellStyle("Sheet1", "D7", "D7", style)

• Example 7, locking and hiding the worksheet D7 cell named Sheet1:

style, err := f.NewStyle(&excelize.Style{
    Protection: &excelize.Protection{
        Hidden: true,
        Locked: true,
    },
})
if err != nil {
    fmt.Println(err)
}
err = f.SetCellStyle("Sheet1", "D7", "D7", style)

To lock a cell or hide a formula, protect the worksheet. On the "Review" tab, click "Protect Worksheet".

Set hyperlink

func (f *File) SetCellHyperLink(sheet, cell, link, linkType string, opts ...HyperlinkOpts) error

SetCellHyperLink provides a function to set cell hyperlinks by given worksheet name and link URL address. LinkType defines two types of hyperlinks External for the website or Location for moving to one of the cells in this workbook. The maximum limit of hyperlinks in a worksheet is 65530. This function is only used to set the hyperlink of the cell and doesn't affect the value of the cell. If you need to set the value of the cell, please use the other functions such as SetCellStyle or SetSheetRow. Below is an example of an external link.

• Example 1, adding an external link to the A3 cell of the worksheet named Sheet1:

display, tooltip := "https://github.com/xuri/excelize", "Excelize on GitHub"
if err := f.SetCellHyperLink("Sheet1", "A3",
    "https://github.com/xuri/excelize", "External", excelize.HyperlinkOpts{
        Display: &display,
        Tooltip: &tooltip,
    }); err != nil {
    fmt.Println(err)
}
// Set the font and underline style for the cell
style, err := f.NewStyle(&excelize.Style{
    Font: &excelize.Font{Color: "1265BE", Underline: "single"},
})
if err != nil {
    fmt.Println(err)
}
err = f.SetCellStyle("Sheet1", "A3", "A3", style)

• Example 2, adding an internal location link to the A3 cell named Sheet1:

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

Set cell rich text

func (f *File) SetCellRichText(sheet, cell string, runs []RichTextRun) error

SetCellRichText provides a function to set a cell with rich text by given worksheet.

For example, set rich text on the A1 cell of the worksheet named Sheet1:

package main

import (
    "fmt"

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

func main() {
    f := excelize.NewFile()
    defer func() {
        if err := f.Close(); err != nil {
            fmt.Println(err)
        }
    }()
    if err := f.SetRowHeight("Sheet1", 1, 35); err != nil {
        fmt.Println(err)
        return
    }
    if err := f.SetColWidth("Sheet1", "A", "A", 44); err != nil {
        fmt.Println(err)
        return
    }
    if err := f.SetCellRichText("Sheet1", "A1", []excelize.RichTextRun{
        {
            Text: "bold",
            Font: &excelize.Font{
                Bold:   true,
                Color:  "2354E8",
                Family: "Times New Roman",
            },
        },
        {
            Text: " and ",
            Font: &excelize.Font{
                Family: "Times New Roman",
            },
        },
        {
            Text: "italic ",
            Font: &excelize.Font{
                Bold:   true,
                Color:  "E83723",
                Italic: true,
                Family: "Times New Roman",
            },
        },
        {
            Text: "text with color and font-family,",
            Font: &excelize.Font{
                Bold:   true,
                Color:  "2354E8",
                Family: "Times New Roman",
            },
        },
        {
            Text: "\r\nlarge text with ",
            Font: &excelize.Font{
                Size:  14,
                Color: "AD23E8",
            },
        },
        {
            Text: "strike",
            Font: &excelize.Font{
                Color:  "E89923",
                Strike: true,
            },
        },
        {
            Text: " superscript",
            Font: &excelize.Font{
                Color:     "DBC21F",
                VertAlign: "superscript",
            },
        },
        {
            Text: " and ",
            Font: &excelize.Font{
                Size:      14,
                Color:     "AD23E8",
                VertAlign: "baseline",
            },
        },
        {
            Text: "underline",
            Font: &excelize.Font{
                Color:     "23E833",
                Underline: "single",
            },
        },
        {
            Text: " subscript.",
            Font: &excelize.Font{
                Color:     "017505",
                VertAlign: "subscript",
            },
        },
    }); err != nil {
        fmt.Println(err)
        return
    }
    style, err := f.NewStyle(&excelize.Style{
        Alignment: &excelize.Alignment{
            WrapText: true,
        },
    })
    if err != nil {
        fmt.Println(err)
        return
    }
    if err := f.SetCellStyle("Sheet1", "A1", "A1", style); err != nil {
        fmt.Println(err)
        return
    }
    if err := f.SaveAs("Book1.xlsx"); err != nil {
        fmt.Println(err)
    }
}

Get cell rich text

func (f *File) GetCellRichText(sheet, cell string) ([]RichTextRun, error)

GetCellRichText provides a function to get the rich text of cells by given worksheet.

Get cell value

func (f *File) GetCellValue(sheet, cell string, opts ...Options) (string, error)

GetCellValue provides a function to get formatted value from cell by given worksheet name and cell reference in spreadsheet. The return value is converted to the string type. This function is concurrency safe. If the cell format can be applied to the value of a cell, the applied value will be returned, otherwise the original value will be returned. All cells' values will be the same in a merged range.

Get cell type

func (f *File) GetCellType(sheet, cell string) (CellType, error)

GetCellType provides a function to get the cell's data type by given worksheet name and cell reference in spreadsheet file.

Get all cell value by cols

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

GetCols gets the value of all cells by columns on the worksheet based on the given worksheet name, returned as a two-dimensional array, where the value of the cell is converted to the string type. If the cell format can be applied to the value of the cell, the applied value will be used, otherwise the original value will be used.

For example, get and traverse the value of all cells by columns on a worksheet named Sheet1:

cols, err := f.GetCols("Sheet1")
if err != nil {
    fmt.Println(err)
    return
}
for _, col := range cols {
    for _, rowCell := range col {
        fmt.Print(rowCell, "\t")
    }
    fmt.Println()
}

Get all cell value by rows

func (f *File) GetRows(sheet string, opts ...Options) ([][]string, error)

GetRows return all the rows in a sheet by given worksheet name, returned as a two-dimensional array, where the value of the cell is converted to the string type. If the cell format can be applied to the value of the cell, the applied value will be used, otherwise the original value will be used. GetRows fetched the rows with value or formula cells, the continually blank cells in the tail of each row will be skipped, so the length of each row may be inconsistent.

For example, get and traverse the value of all cells by rows on a worksheet named Sheet1:

rows, err := f.GetRows("Sheet1")
if err != nil {
    fmt.Println(err)
    return
}
for _, row := range rows {
    for _, colCell := range row {
        fmt.Print(colCell, "\t")
    }
    fmt.Println()
}

Get hyperlink

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

GetCellHyperLink gets a cell hyperlink based on the given worksheet name and cell reference. If the cell has a hyperlink, it will return true and the link address, otherwise it will return false and an empty link address.

For example, get a hyperlink to a H6 cell on a worksheet named Sheet1:

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

Get style index

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

The cell style index is obtained from the given worksheet name and cell reference, and the obtained index can be used as a parameter to call the SetCellValue function when copying the cell style.

Merge cells

func (f *File) MergeCell(sheet, topLeftCell, bottomRightCell string) error

MergeCell provides a function to merge cells by given range reference and sheet name. Merging cells only keeps the upper-left cell value, and discards the other values. For example, merge cells in the D3:E9 area on a worksheet named Sheet1:

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

If you create a merged cell that overlaps with another existing merged cell, those merged cells that already exist will be removed.

Unmerge cells

func (f *File) UnmergeCell(sheet, topLeftCell, bottomRightCell string) error

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

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

Attention: overlapped areas will also be unmerged.

Get merge cells

GetMergeCells provides a function to get all merged cells from a specific worksheet.

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

Get merged cell value

func (m *MergeCell) GetCellValue() string

GetCellValue returns merged cell value.

Get the top left cell reference of merged range

func (m *MergeCell) GetStartAxis() string

GetStartAxis returns the top left cell reference of merged range, for example: C2.

Get the bottom right cell reference of merged range

func (m *MergeCell) GetEndAxis() string

GetEndAxis returns the bottom right cell reference of merged range, for example: D4.

Get picture cells

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

GetPictureCells returns all picture cell references in a worksheet by a specific worksheet name.

Add comment

func (f *File) AddComment(sheet string, comment Comment) error

AddComment provides the method to add comments 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$3:

err := f.AddComment("Sheet1", excelize.Comment{
    Cell:   "A3",
    Author: "Excelize",
    Paragraph: []excelize.RichTextRun{
        {Text: "Excelize: ", Font: &excelize.Font{Bold: true}},
        {Text: "This is a comment."},
    },
})

Get comment

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

GetComments retrieves all comments in a worksheet by given worksheet name.

Delete comment

func (f *File) DeleteComment(sheet, cell string) error

DeleteComment provides the method to delete comment in a sheet by given worksheet name. For example, delete the comment in Sheet1!$A$30:

err := f.DeleteComment("Sheet1", "A30")

Set cell formula

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

SetCellFormula provides a function to set the formula on the cell is taken according to the given worksheet name and cell formula settings. The result of the formula cell can be calculated when the worksheet is opened by the Office Excel application or can be using the CalcCellValue function also can get the calculated cell value. If the Excel application doesn't calculate the formula automatically when the workbook has been opened, please call UpdateLinkedValue after setting the cell formula functions.

• Example 1, set normal formula =SUM(A1,B1) for the cell A3 on Sheet1:

err := f.SetCellFormula("Sheet1", "A3", "=SUM(A1,B1)")

• Example 2, set one-dimensional vertical constant array (column array) formula 1;2;3 for the cell A3 on Sheet1:

err := f.SetCellFormula("Sheet1", "A3", "={1;2;3}")

• Example 3, set one-dimensional horizontal constant array (row array) formula "a","b","c" for the cell A3 on Sheet1:

err := f.SetCellFormula("Sheet1", "A3", "={\"a\",\"b\",\"c\"}")

• Example 4, set two-dimensional constant array formula {1,2;"a","b"} for the cell A3 on Sheet1:

formulaType, ref := excelize.STCellFormulaTypeArray, "A3:A3"
err := f.SetCellFormula("Sheet1", "A3", "={1,2;\"a\",\"b\"}",
    excelize.FormulaOpts{Ref: &ref, Type: &formulaType})

• Example 5, set range array formula A1:A2 for the cell A3 on Sheet1:

formulaType, ref := excelize.STCellFormulaTypeArray, "A3:A3"
err := f.SetCellFormula("Sheet1", "A3", "=A1:A2",
    excelize.FormulaOpts{Ref: &ref, Type: &formulaType})

• Example 6, set shared formula =A1+B1 for the cells C1:C5 on Sheet1, C1 is the master cell:

formulaType, ref := excelize.STCellFormulaTypeShared, "C1:C5"
err := f.SetCellFormula("Sheet1", "C1", "=A1+B1",
    excelize.FormulaOpts{Ref: &ref, Type: &formulaType})

• Example 7, set table formula =SUM(Table1[[A]:[B]]) for the cell C2 on Sheet1:

package main

import (
    "fmt"

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

func main() {
    f := excelize.NewFile()
    defer func() {
        if err := f.Close(); err != nil {
            fmt.Println(err)
        }
    }()
    for idx, row := range [][]interface{}{{"A", "B", "C"}, {1, 2}} {
        if err := f.SetSheetRow("Sheet1", fmt.Sprintf("A%d", idx+1), &row); err != nil {
            fmt.Println(err)
            return
        }
    }
    if err := f.AddTable("Sheet1",
        &excelize.Table{
            Range:     "A1:C2",
            Name:      "Table1",
            StyleName: "TableStyleMedium2",
        }); err != nil {
        fmt.Println(err)
        return
    }
    formulaType := excelize.STCellFormulaTypeDataTable
    if err := f.SetCellFormula("Sheet1", "C2", "=SUM(Table1[[A]:[B]])",
        excelize.FormulaOpts{Type: &formulaType}); err != nil {
        fmt.Println(err)
        return
    }
    if err := f.SaveAs("Book1.xlsx"); err != nil {
        fmt.Println(err)
    }
}

Get cell formula

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

GetCellFormula provides a function to get formula from cell by given worksheet name and cell reference in spreadsheet.

Calculate cell value

func (f *File) CalcCellValue(sheet, cell string, opts ...Options) (string, error)

CalcCellValue provides a function to get calculated cell value. This feature is currently in working processing. Iterative calculation, implicit intersection, explicit intersection, array formula, table formula and some other formulas are not supported currently.

Supported formulas: