Cell

Set cell value

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

SetCellValue provides a function to set the value of a cell. The following shows the supported data types:

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

Set boolean value

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

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

Set RAW value

func (f *File) SetCellDefault(sheet, axis, value string)

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

Set integer value

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

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

Set string value

func (f *File) SetCellStr(sheet, axis, value string)

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

Set cell style

func (f *File) SetCellStyle(sheet, hcell, vcell string, styleID int)

SetCellStyle provides a function to add style attribute for cells by given worksheet name, coordinate area and style ID. Style indexes can be obtained with the NewStyle function. Note that diagonalDown and diagonalUp type border should be use same color in the same coordinate area.

  • Example 1, create borders of cell D7 on Sheet1:
style, err := xlsx.NewStyle(`{"border":[{"type":"left","color":"0000FF","style":3},{"type":"top","color":"00FF00","style":4},{"type":"bottom","color":"FFFF00","style":5},{"type":"right","color":"FF0000","style":6},{"type":"diagonalDown","color":"A020F0","style":7},{"type":"diagonalUp","color":"A020F0","style":8}]}`)
if err != nil {
    fmt.Println(err)
}
xlsx.SetCellStyle("Sheet1", "D7", "D7", style)

"Set a border style for a cell"

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 := xlsx.NewStyle(`{"fill":{"type":"gradient","color":["#FFFFFF","#E0EBF5"],"shading":1}}`)
if err != nil {
    fmt.Println(err)
}
xlsx.SetCellStyle("Sheet1", "D7", "D7", style)

"Set a gradient style for the cell"

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 := xlsx.NewStyle(`{"fill":{"type":"pattern","color":["#E0EBF5"],"pattern":1}}`)
if err != nil {
    fmt.Println(err)
}
xlsx.SetCellStyle("Sheet1", "D7", "D7", style)

"Set a solid fill for the cell"

The cell D7 is set with a solid fill.

  • Example 4, set the character spacing and rotation angle for the D7 cell named Sheet1:
xlsx.SetCellValue("Sheet1", "D7", "Style")
style, err := xlsx.NewStyle(`{"alignment":{"horizontal":"center","ident":1,"justify_last_line":true,"reading_order":0,"relative_indent":1,"shrink_to_fit":true,"text_rotation":45,"vertical":"","wrap_text":true}}`)
if err != nil {
    fmt.Println(err)
}
xlsx.SetCellStyle("Sheet1", "D7", "D7", style)

"Set the character spacing and rotation angle"

  • 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:
xlsx.SetCellValue("Sheet1", "D7", 42920.5)
xlsx.SetColWidth("Sheet1", "D", "D", 13)
style, err := xlsx.NewStyle(`{"number_format": 22}`)
if err != nil {
    fmt.Println(err)
}
xlsx.SetCellStyle("Sheet1", "D7", "D7", style)

"Set the time format for the cell"

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:
xlsx.SetCellValue("Sheet1", "D7", "Excel")
style, err := xlsx.NewStyle(`{"font":{"bold":true,"italic":true,"family":"Berlin Sans FB Demi","size":36,"color":"#777777"}}`)
if err != nil {
    fmt.Println(err)
}
xlsx.SetCellStyle("Sheet1", "D7", "D7", style)

"Set font, font size, color, and skew style for cells"

  • Example 7, locking and hiding the worksheet D7 cell named Sheet1:
style, err := xlsx.NewStyle(`{"protection":{"hidden":true, "locked":true}}`)
if err != nil {
    fmt.Println(err)
}
xlsx.SetCellStyle("Sheet1", "D7", "D7", style)

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

func (f *File) SetCellHyperLink(sheet, axis, link, linkType string)

SetCellHyperLink provides a function to set cell hyperlink by given worksheet name and link URL address. LinkType defines two types of hyperlink External for website or Location for moving to one of cell in this workbook. The below is an example of an external link.

  • Example 1, adding an external link to the A3 cell of the worksheet named Sheet1:
xlsx.SetCellHyperLink("Sheet1", "A3", "https://github.com/360EntSecGroup-Skylar/excelize", "External")
// Set the font and underline style for the cell
style, _ := xlsx.NewStyle(`{"font":{"color":"#1265BE","underline":"single"}}`)
xlsx.SetCellStyle("Sheet1", "A3", "A3", style)
  • Example 2, adding an internal location link to the A3 cell named Sheet1:
xlsx.SetCellHyperLink("Sheet1", "A3", "Sheet1!A40", "Location")

Get cell value

func (f *File) GetCellValue(sheet, axis string) string

The value of the cell is retrieved according to the given worksheet and cell coordinates, and the return value is converted to the string type. 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.

Get all cell value

func (f *File) GetRows(sheet string) [][]string

Gets the value of all cells on the worksheet based on the given worksheet name (case sensitive), 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 on a worksheet named Sheet1:

for _, row := range xlsx.GetRows("Sheet1") {
    for _, colCell := range row {
        fmt.Print(colCell, "\t")
    }
    fmt.Println()
}
func (f *File) GetCellHyperLink(sheet, axis string) (bool, string)

Gets a cell hyperlink based on the given worksheet name (case sensitive) and cell coordinates. 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 := xlsx.GetCellHyperLink("Sheet1", "H6")

Get style index

func (f *File) GetCellStyle(sheet, axis string) int

The cell style index is obtained from the given worksheet name (case sensitive) and cell coordinates, 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, hcell, vcell string)

Merge cells based on the given worksheet name (case sensitive) and cell coordinate regions. For example, merge cells in the D3:E9 area on a worksheet named Sheet1:

xlsx.MergeCell("Sheet1", "D3", "E9")

If the given cell coordinate area overlaps with other existing merged cells, the existing merged cells will be deleted.

Get merge cells

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

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

Add comment

func (f *File) AddComment(sheet, cell, format string) error

AddComment provides the method to add comment in a sheet by given worksheet index, cell and format set (such as author and text). Note that the max author length is 255 and the max text length is 32512. For example, add a comment in Sheet1!$A$3:

"Add a comment to an Excel document"

xlsx.AddComment("Sheet1", "A3", `{"author":"Excelize: ","text":"This is a comment."}`)

Get comment

func (f *File) GetComments() (comments map[string][]Comment)

GetComments retrieves all comments and returns a map of worksheet name to the worksheet comments.

Set cell formula

func (f *File) SetCellFormula(sheet, axis, formula string)

The formula on the cell is taken according to the given worksheet name (case sensitive) and cell formula settings. The result of the formula is calculated when the worksheet is opened by the Office Excel application, and Excelize does not currently provide a formula calculation engine, so the formula results cannot be calculated.

Get cell formula

func (f *File) GetCellFormula(sheet, axis string) string

Get the formula on the cell based on the given worksheet name (case sensitive) and cell coordinates.

results matching ""

    No results matching ""