Cell

RichTextRun directly maps the settings of the rich text run.

class RichTextRun:
    font: Optional[Font] = None
    text: str = ""

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

class HyperlinkOpts:
    display: Optional[str] = None
    tooltip: Optional[str] = None

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

class FormulaOpts:
    type: Optional[str] = None
    ref: Optional[str] = None

Set cell value

def set_cell_value(
    sheet: str,
    cell: str,
    value: Union[bool, float, int, str, date, datetime, None],
) -> None

Set the value of a cell. 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 datetime type value. You can set numbers format by the set_cell_style 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 Python language datetime 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

def set_cell_bool(sheet: str, cell: str, value: bool) -> None

Set the bool type value of a cell by given worksheet name, cell reference, and cell value.

Set integer value

def set_cell_int(sheet: str, cell: str, value: int) -> None

Set the int type value of a cell by given worksheet name, cell reference, and cell value.

Set string value

def set_cell_str(sheet: str, cell: str, value: str) -> None

Set string type value of a cell. The total number of characters that a cell can contain 32767 characters.

Set cell style

def set_cell_style(
    sheet: str,
    top_left_cell: str,
    bottom_right_cell: str,
    style_id: int,
) -> None

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 new_style function. Note that diagonalDown and diagonalUp type border should use the same color in the same range. This function 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 = f.new_style(excelize.Style(
    border=[
        excelize.Border(type="left", color="0000FF", style=3),
        excelize.Border(type="top", color="00FF00", style=4),
        excelize.Border(type="bottom", color="FFFF00", style=5),
        excelize.Border(type="right", color="FF0000", style=6),
        excelize.Border(type="diagonalDown", color="A020F0", style=8),
        excelize.Border(type="diagonalUp", color="A020F0", style=8),
    ]
))
f.set_cell_style("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 new_style 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 = f.new_style(
    excelize.Style(
        fill=excelize.Fill(
            type="gradient",
            color=["FFFFFF", "E0EBF5"],
            shading=1,
        )
    )
)
f.set_cell_style("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 new_style 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 = f.new_style(
    excelize.Style(
        fill=excelize.Fill(type="pattern", color=["E0EBF5"], pattern=1),
    )
)
f.set_cell_style("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.set_cell_value("Sheet1", "D7", "Style")
style = f.new_style(
    excelize.Style(
        alignment=excelize.Alignment(
            horizontal="center",
            indent=1,
            justify_last_line=True,
            reading_order=0,
            relative_indent=1,
            shrink_to_fit=True,
            text_rotation=45,
            vertical="",
            wrap_text=True,
        ),
    )
)
f.set_cell_style("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.set_cell_value("Sheet1", "D7", 42920.5)
f.set_col_width("Sheet1", "D", "D", 13)
style = f.new_style(excelize.Style(num_fmt=22))
f.set_cell_style("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 set_col_width 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.set_cell_value("Sheet1", "D7", "Excel")
style = f.new_style(
    excelize.Style(
        font=excelize.Font(
            bold=True,
            italic=True,
            family="Times New Roman",
            size=36,
            color="777777",
        )
    )
)
f.set_cell_style("Sheet1", "D7", "D7", style)

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

style = f.new_style(
    excelize.Style(
        protection=excelize.Protection(hidden=True, locked=True),
    )
)
f.set_cell_style("Sheet1", "D7", "D7", style)

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

Set hyperlink

def set_cell_hyperlink(
    sheet: str,
    cell: str,
    link: str,
    link_type: str,
    *opts: HyperlinkOpts,
) -> None

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 set_cell_style or set_sheet_row. Below is an example of an external link.

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

display = "https://github.com/xuri/excelize"
try:
    f.set_cell_hyperlink(
        "Sheet1",
        "A3",
        display,
        "External",
        excelize.HyperlinkOpts(display=display, tooltip="Excelize on GitHub"),
    )
    # Set underline and font color style for the cell.
    style = f.new_style(
        excelize.Style(
            font=excelize.Font(color="1265BE", underline="single")
        )
    )
    f.set_cell_style("Sheet1", "A3", "A3", style)
except (RuntimeError, TypeError) as err:
    print(err)

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

f.set_cell_hyperlink("Sheet1", "A3", "Sheet1!A40", "Location")

Set cell rich text

def set_cell_rich_text(
    sheet: str,
    cell: str,
    runs: List[RichTextRun],
) -> None

Set a cell with rich text by given worksheet.

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

f = excelize.new_file()
try:
    f.set_row_height("Sheet1", 1, 35)
    f.set_col_width("Sheet1", "A", "A", 44)
    f.set_cell_rich_text(
        "Sheet1",
        "A1",
        [
            excelize.RichTextRun(
                text="bold",
                font=excelize.Font(
                    bold=True,
                    color="2354E8",
                    family="Times New Roman",
                ),
            ),
            excelize.RichTextRun(
                text=" and ",
                font=excelize.Font(
                    family="Times New Roman",
                ),
            ),
            excelize.RichTextRun(
                text="italic ",
                font=excelize.Font(
                    bold=True,
                    color="E83723",
                    italic=True,
                    family="Times New Roman",
                ),
            ),
            excelize.RichTextRun(
                text="text with color and font-family,",
                font=excelize.Font(
                    bold=True,
                    color="2354E8",
                    family="Times New Roman",
                ),
            ),
            excelize.RichTextRun(
                text="\r\nlarge text with ",
                font=excelize.Font(
                    size=14,
                    color="AD23E8",
                ),
            ),
            excelize.RichTextRun(
                text="strike",
                font=excelize.Font(
                    color="E89923",
                    strike=True,
                ),
            ),
            excelize.RichTextRun(
                text=" superscript",
                font=excelize.Font(
                    color="DBC21F",
                    vert_align="superscript",
                ),
            ),
            excelize.RichTextRun(
                text=" and ",
                font=excelize.Font(
                    size=14,
                    color="AD23E8",
                    vert_align="baseline",
                ),
            ),
            excelize.RichTextRun(
                text="underline",
                font=excelize.Font(
                    color="23E833",
                    vert_align="single",
                ),
            ),
            excelize.RichTextRun(
                text=" subscript.",
                font=excelize.Font(
                    color="017505",
                    vert_align="subscript",
                ),
            ),
        ],
    )
    style = f.new_style(
        excelize.Style(
            alignment=excelize.Alignment(wrap_text=True),
        )
    )
    f.set_cell_style("Sheet1", "A1", "A1", style)
    f.save_as("Book1.xlsx")
except (RuntimeError, TypeError) as err:
    print(err)
finally:
    err = f.close()
    if err:
        print(err)

Get cell rich text

def get_cell_rich_text(sheet: str, cell: str) -> List[RichTextRun]

Get the rich text of cells by given worksheet.

Get cell value

def get_cell_value(sheet: str, cell: str, *opts: Options) -> str

Get formatted value from cell by given worksheet name and cell reference in spreadsheet. 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. All cells' values will be the same in a merged range.

Get all cell value by rows

def get_rows(sheet: str, *opts: Options) -> List[List[str]]

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. This function 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 = f.get_rows("Sheet1")
for row in rows:
    for cell in row:
        print(f"{cell}\t", end="")
    print()

Get hyperlink

def get_cell_hyperlink(sheet: str, cell: str) -> Tuple[bool, str]

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:

try:
    link, target = f.get_cell_hyperlink("Sheet1", "H6")
except (RuntimeError, TypeError) as err:
    print(err)

Get style index

def get_cell_style(sheet: str, cell: str) -> int

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 set_cell_style function when copying the cell style.

Merge cells

def merge_cell(sheet: str, top_left_cell: str, bottom_right_cell: str) -> None

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:

try:
    f.merge_cell("Sheet1", "D3", "E9")
except (RuntimeError, TypeError) as err:
    print(err)

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

Unmerge cells

def unmerge_cell(
    sheet: str, top_left_cell: str, bottom_right_cell: str
) -> None

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

try:
    f.unmerge_cell("Sheet1", "D3", "E9")
except (RuntimeError, TypeError) as err:
    print(err)

Attention: overlapped areas will also be unmerged.

Add comment

def add_comment(sheet: str, opts: Comment) -> None

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:

f.add_comment("Sheet1", excelize.Comment(
    cell="A5",
    author="Excelize",
    paragraph=[
        excelize.RichTextRun(text="Excelize: ", font=excelize.Font(bold=True)),
        excelize.RichTextRun(text="This is a comment."),
    ],
    height=40,
    width=180,
))

Get comment

def get_comments(sheet: str) -> List[Comment]

Retrieves all comments in a worksheet by given worksheet name.

Delete comment

def delete_comment(sheet: str, cell: str) -> None

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

f.delete_comment("Sheet1", "A30")

Set cell formula

def set_cell_formula(
    sheet: str, cell: str, formula: str, *opts: FormulaOpts
) -> None

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 calc_cell_value 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 update_linked_value after setting the cell formula functions.

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

f.set_cell_formula("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:

f.set_cell_formula("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:

f.set_cell_formula("Sheet1", "A3", "{\"a\",\"b\",\"c\"}")

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

f.set_cell_formula(
    "Sheet1",
    "A3",
    '{1,2;"a","b"}',
    excelize.FormulaOpts(ref="A3:A3", type="array"),
)

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

f.set_cell_formula(
    "Sheet1",
    "A3",
    "A1:A2",
    excelize.FormulaOpts(ref="A3:A3", type="array"),
)

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

f.set_cell_formula(
    "Sheet1",
    "C1",
    "A1+B1",
    excelize.FormulaOpts(ref="C1:C5", type="shared"),
)

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

import excelize

f = excelize.new_file()
try:
    for idx, row in enumerate([["A", "B", "C"], [1, 2]]):
        f.set_sheet_row("Sheet1", f"A{idx+1}", row)
    f.add_table("Sheet1", excelize.Table(
        range="A1:C2",
        name="Table1",
        style_name="TableStyleMedium2"
    ))
    f.set_cell_formula(
        "Sheet1",
        "C2",
        "SUM(Table1[[A]:[B]])",
        excelize.FormulaOpts(type="dataTable")
    )
    f.save_as("Book1.xlsx")
except (RuntimeError, TypeError) as err:
    print(err)
finally:
    err = f.close()
    if err:
        print(err)

Get cell formula

def get_cell_formula(sheet: str, cell: str) -> str

Get formula from cell by given worksheet name and cell reference in spreadsheet.

Calculate cell value

def calc_cell_value(sheet: str, cell: str, *opts: Options) -> str

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: