Go Excelize Cells

Cell

The RichTextRun defines the properties of rich text.

type RichTextRun struct {
    Font *Font
    Text string
}

The HyperlinkOpts is used to specify optional hyperlink properties, such as the display text and screen tip.

type HyperlinkOpts struct {
    Display *string
    Tooltip *string
}

The FormulaOpts is used to specify special formula types in the SetCellFormula function.

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

Set the value of a cell

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

Set the value of a cell based on the given sheet name and cell coordinates. This function is goroutine-safe. The specified coordinates should not be in the first row of the table, and plural characters should be used for text setting.

Supported data types
int
int8
int16
int32
int64
uint
uint8
uint16
uint32
uint64
float32
float64
string
[]byte
time.Duration
time.Time
bool
nil

Please note that this function sets the value of a cell of type time.Time with the m/d/yy h:mm number format by default, and you can change this setting using SetCellStyle. If you need to set special Excel dates that cannot be represented by the Go language time.Time type, such as January 0, 1900, or February 29, 1900, set the value of the cell to 0 or 60, and then set it with a style that has a date number format.

Set boolean value

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

Set the value of a boolean cell based on the given sheet name and cell coordinates.

Set default string value

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

Set the value of a string cell based on the given sheet name and cell coordinates, and the characters will not be subject to special character filtering.

Set integer

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

Set the value of an integer cell based on the given sheet name and cell coordinates.

Set float

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

Set the value of a float cell based on the given sheet name, cell coordinates, float value, precision of the fractional part of the float, and the type of the float.

Set string value

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

Set the value of a string cell based on the given sheet name and cell coordinates, the characters will be subject to special character filtering, and the accumulated length of the string should not exceed 32767, any excess characters will be ignored.

Set Cell Style

func (f *File) SetCellStyle(sheet, hCell, vCell string, styleID int) error

Set the value of a cell based on the given sheet name, cell coordinates, and style index. This function is thread-safe. The style index can be obtained through the NewStyle function. Note that the diagonalDown and diagonalUp within the same coordinate area need to maintain consistent colors. SetCellStyle will override the existing style of the cell without stacking or merging the styles.

Example 1: Set border style for cell D7 in the sheet named 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)

Set border style for a cell

The four borders of cell D7 are set with different styles and colors, which are related to the parameters passed to the NewStyle function. For setting different styles, please refer to the documentation in this chapter.

Example 2: Set gradient style for cell D7 in the sheet 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)

Set gradient style for a cell

Cell D7 is filled with gradient colors, which is related to the parameters passed to the NewStyle function. For setting different styles, please refer to the documentation in this chapter.

Example 3: Set solid color fill for cell D7 in the sheet 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)

Set solid color fill for a cell

Cell D7 is filled with a solid color.

Example 4: Set character spacing and rotation angle for cell D7 in the sheet 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)

Set character spacing and rotation angle

Example 5: Dates and times 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 cell D7 in the worksheet 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)

Set time format for cell

Cell D7 is set with a time format. Note that when the cell width is too narrow to display the complete content, it will show as ####. You can adjust the column width by dragging or by calling the SetColWidth function to set the column width to an appropriate size for normal display.

Example 6: Set the font, font size, color, and italic style for cell D7 in the worksheet 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)

Set font, font size, color, and italic style for cell

Example 7: Lock and hide cell D7 in the worksheet 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 or hide cells, protect the worksheet. On the “Review” tab, click “Protect Sheet”.

## Set Hyperlink

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

Set the hyperlink for a cell based on the given worksheet, cell coordinates, link resource, and resource type. The resource type includes two types: external link address External and internal position link within the workbook Location. The maximum number of hyperlinks in each worksheet is limited to 65530. This method only sets the hyperlink for the cell without affecting the cell value. If you need to set the cell value, please set it separately through functions like SetCellStyle or SetSheetRow.

Example 1: Add an external link to cell A3 in 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 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: Add a location link to cell A3 in the worksheet named Sheet1:
```
err := f.SetCellHyperLink("Sheet1", "A3", "Sheet1!A40", "Location")
```
Set Rich Text Format

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

Set the rich text format for the specified cell based on the given worksheet, cell coordinates, and rich text format.

For example, set the rich text format for cell A1 in the worksheet named Sheet1:

Set Rich Text Format

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",
            },
        },
        // ... (Other rich text runs)
    }); err != nil {
        fmt.Println(err)
        return
    }
    // ... (Other cell style settings and saving)
}

Get Rich Text Format

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

Get the rich text format of the specified cell based on the given worksheet and cell coordinates.

Get cell value

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

Get the value of the specified cell based on the given worksheet and cell coordinates, and the return value will be converted to the string type. If the cell format can be applied to the cell value, the applied value will be returned; otherwise, the original value will be returned. The values of all cells within a merged area are the same. This function is concurrency-safe.

Get cell data type

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

Get the data type of the specified cell based on the given worksheet and cell coordinates.

Get values of all cells by column

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

Retrieve the values of all cells on the specified worksheet by column, and return them in the form of a two-dimensional array, with the cell values converted to the string type. If the cell format can be applied to the cell value, the applied value will be used; otherwise, the original value will be used.

For example, to retrieve and iterate through all the cell values on the worksheet named Sheet1 by column:

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 values of all cells by row

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

Retrieve the values of all cells on the specified worksheet by row, and return them in the form of a two-dimensional array, with the cell values converted to the string type. If the cell format can be applied to the cell value, the applied value will be used; otherwise, the original value will be used. GetRows retrieves rows with cells containing values or formulas. Empty cells at the end of a row will be skipped, and the number of cells in each row may vary.

For example, to retrieve and iterate through all the cell values on the worksheet named Sheet1 by row:

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)

Retrieve the hyperlink of the specified cell based on the given worksheet and cell coordinates. If the cell contains a hyperlink, it will return true and the link address; otherwise, it will return false and an empty link address.

For example, to retrieve the hyperlink of the cell at coordinates H6 on the worksheet named Sheet1:

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

Get style index

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

Retrieve the style index of the specified cell based on the given worksheet and cell coordinates, and the retrieved index can be used as a parameter when calling the SetCellStyle function to set the cell style.

Merge Cells

func (f *File) MergeCell(sheet, hCell, vCell string) error

Merge the cells in the given worksheet and cell coordinate region. Only the value of the upper-left cell in the merged region will be retained, and the values of other cells will be ignored. For example, merge the cells in the region D3:E9 on the worksheet named Sheet1:

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

If the given cell coordinate region overlaps with existing merged cells, the existing merged cells will be removed.

Unmerge Cells

func (f *File) UnmergeCell(sheet string, hCell, vCell string) error

Unmerge the cells in the given worksheet and cell coordinate region. For example, unmerge the cells in the region D3:E9 on the worksheet named Sheet1:

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

If the given cell coordinate region contains multiple merged cells, all merged cells will be unmerged.

Get Merged Cells

Get the coordinate region and values of all merged cells in the given worksheet.

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

Get the Value of Merged Cells

func (m *MergeCell) GetCellValue() string

GetCellValue returns the value of the merged cell.

Get the Upper-Left Cell Coordinate of the Merged Cell Region

func (m *MergeCell) GetStartAxis() string

GetStartAxis returns the coordinate of the upper-left cell of the merged cell region, for example, C2.

Get the Lower-Right Cell Coordinate of the Merged Cell Region

func (m *MergeCell) GetEndAxis() string

GetEndAxis returns the coordinate of the lower-right cell of the merged cell region, for example, D4.

Add Comment

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

Add a comment to the given worksheet, cell coordinate, and style parameters (author and text information). The author information has a maximum length of 255 characters, and the maximum text content length is 32512 characters. Characters beyond this range will be ignored. For example, add a comment to the cell Sheet1!$A$3:

Add a comment in an Excel document

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 Comments

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

Get all cell comments in the given worksheet.

Delete Comment

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

Delete the comment at the given worksheet and cell coordinate. For example, delete the comment in the cell Sheet1!$A$30:

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