The Excel workbook, as a widely used data management tool, can be combined with Python to enable the automation of large-scale data processing. Using Python to set, update, and read cell values in Excel can significantly improve work efficiency, reduce repetitive tasks, and enhance the flexibility and scalability of data processing workflows, thus creating added value. This approach is applicable across a range of fields, from automating financial reports to generating data analysis reports, and can greatly boost productivity in various work contexts.

This article will demonstrate how to set, update, and retrieve cell values in Excel files using Spire.XLS for Python.

• Set cell values in Excel Files with Python
• Update cell values in Excel Files with Python
• Retrieve cell values in Excel Files with Python

Install Spire.XLS for Python

This scenario requires Spire.XLS for Python and plum-dispatch v1.7.4. They can be easily installed in your Windows through the following pip commands.

pip install Spire.XLS

If you are unsure how to install, please refer to: How to Install Spire.XLS for Python on Windows

Set cell values in Excel Files with Python

We can use the Worksheet.Range.get_Item() method from Spire.XLS for Python to obtain a specified cell in an Excel worksheet as a CellRange object, such as Range.get_Item(2, 1) or Range.get_Item("A2") (row 2, column 1). Then, we can use the CellRange.Value property to set the cell value, or other properties within this class to set text, numbers, boolean values, and other types of data. The following is an example of the procedure:

• Create a Workbook object.
• Get the first default worksheet using Workbook.Worksheets.get_Item() method.
• Obtain the specified cell as a CellRange object using Worksheet.Range.get_Item() method.
• Use properties within the CellRange class, such as Text, Value, DateTimeValue, Formula, and NumberValue, to set cell values.
• Format the cells.
• Save the workbook using Workbook.SaveToFile().

from spire.xls import Workbook, FileFormat, DateTime, HorizontalAlignType
import datetime

# Create an instance of Workbook to create an Excel workbook
workbook = Workbook()

# Get the first default worksheet
sheet = workbook.Worksheets.get_Item(0)

# Get cell and set text
cell = sheet.Range.get_Item(2, 2)
cell.Text = "Text example"

# Get cell and set a regular value
cell1 = sheet.Range.get_Item(3, 2)
cell1.Value = "$123456"

# Get cell and set a date value
cell2 = sheet.Range.get_Item(4, 2)
cell2.DateTimeValue = DateTime.get_Now()

# Get cell and set a boolean value
cell3 = sheet.Range.get_Item(5, 2)
cell3.BooleanValue = True

# Get cell and set a formula
cell4 = sheet.Range.get_Item(6, 2)
cell4.Formula = "=SUM(B7)"

# Get cell, set a number value, and set number format
cell5 = sheet.Range.get_Item(7, 2)
cell5.NumberValue = 123456
cell5.NumberFormat = "#,##0.00"

# Get cell and set a formula array
cell6 = sheet.Range.get_Item(8, 2)
cell6.HtmlString = "<p><span style='color: blue; font-size: 18px;'>Blue font 18 pixel size</span></p>"

# Set formatting
cellRange = sheet.Range.get_Item(2, 2, 7, 2)
cellRange.Style.Font.FontName = "Arial"
cellRange.Style.Font.Size = 14
cellRange.Style.HorizontalAlignment = HorizontalAlignType.Left

# Auto-fit the column width
sheet.AutoFitColumn(2)

# Save the file
workbook.SaveToFile("output/SetExcelCellValue.xlsx", FileFormat.Version2016)
workbook.Dispose()

Update cell values in Excel Files with Python

To update a cell value in Excel, we can retrieve the cell to update and use the same approach as above to reset its value, thus updating the cell value. Below is an example of the procedure:

• Create a Workbook object.
• Load the Excel file using Workbook.LoadFromFile() method.
• Get a worksheet using Workbook.Worksheets.get_Item() method.
• Obtain the cell to update using Worksheet.Range.get_Item() method.
• Use properties under the CellRange class to reset the cell value.
• Save the workbook with Workbook.SaveToFile() method.

from spire.xls import Workbook

# Create an instance of Workbook
workbook = Workbook()

# Load the Excel file
workbook.LoadFromFile("output/SetExcelCellValue.xlsx")

# Get the worksheet
sheet = workbook.Worksheets.get_Item(0)

# Get the cell
cell = sheet.Range.get_Item(2, 2)

# Change the cell value to a number
cell.NumberValue = 45150
# Set the cell number format
cell.NumberFormat = "[Green]#,##0;[RED]-#,##0"

# Save the workbook
workbook.SaveToFile("output/UpdateExcelCellValue.xlsx")
workbook.Dispose()

Retrieve cell values in Excel Files with Python

The CellRange.Value property can also be used to directly read cell values. Below is an example of the procedure to read cell values in Excel files:

• Create a Workbook object.
• Load the Excel file with Workbook.LoadFromFile() method.
• Get a worksheet using Workbook.Worksheets.get_Item() method.
• Loop through the specified cell range and use the CellRange.Value property to get the cell value.
• Print the results.

from spire.xls import Workbook

# Create an instance of Workbook
workbook = Workbook()

# Load the Excel file
workbook.LoadFromFile("output/SetExcelCellValue.xlsx")

# Get the worksheet
sheet = workbook.Worksheets.get_Item(0)

# Loop through cells from row 2 to 8 in column 2
for i in range(2, 8):
    # Get the cell
    cell = sheet.Range.get_Item(i, 2)
    # Get the cell value
    value = cell.Value
    # Output the value
    print(value)

workbook.Dispose()

Apply for a Temporary License

If you'd like to remove the evaluation message from the generated documents, or to get rid of the function limitations, please request a 30-day trial license for yourself.