DEV Community

jelizaveta
jelizaveta

Posted on

Efficiently Read Excel Data Using Spire.XLS for Python

In today's data-driven world, Python has become the preferred tool for data processing and analysis. Excel files, as one of the most common data storage formats, pose a challenge for many developers and data analysts in terms of efficiently and accurately reading and processing Excel data within Python. Traditional Python Excel libraries can sometimes be inadequate when handling complex Excel files (such as those containing formulas, styles, merged cells, etc.), which can lead to performance issues.

This article introduces a powerful and high-performance Python library— Spire.XLS for Python . It helps you effortlessly manage various Excel files and achieve efficient data reading. Through this article, you will learn about the installation, basic usage, and advanced data reading techniques of Spire.XLS for Python, helping you advance in data processing.

Why Choose Spire.XLS for Python?

Spire.XLS for Python is a specialized Excel processing library designed for Python developers. It can run independently without the need for Microsoft Office and supports various Excel file formats (XLS, XLSX, XLSM, XLSB, etc.). It offers a rich API to meet various complex Excel processing needs.

Advantages Over Other Common Python Excel Libraries

  • Comprehensive Functionality : It not only supports basic data reading and writing but can also handle complex Excel elements such as formulas, charts, images, comments, conditional formatting, data validation, macros, etc., while perfectly preserving the format and properties of these elements.
  • High Performance : Optimized for handling large files, providing fast read and write speeds.
  • Format Compatibility : Capable of processing various versions of Excel files while ensuring accuracy in data and format.
  • Ease of Use : The intuitive API design ensures a gentle learning curve, allowing even beginners to quickly get started.

When precise retention of Excel formatting and complex elements is required or in scenarios demanding high-performance read/write operations, Spire.XLS for Python is undoubtedly your ideal choice.

Installation and Basic Usage of Spire.XLS for Python

Installing Spire.XLS for Python

Installing Spire.XLS for Python is straightforward; simply use the pip command:

pip install Spire.XLS
Enter fullscreen mode Exit fullscreen mode

Creating a Simple Excel File (Preparation)

To demonstrate the reading operation, we first create an Excel file containing some basic data. You can manually create a file named Sample.xlsx, or generate it using the following Python code:

sheet.Range["B1"].Value = "Age"
sheet.Range["C1"].Value = "Birth Date"
sheet.Range["D1"].Value = "Score"

sheet.Range["A2"].Value = "Zhang San"
sheet.Range["B2"].Value = "25"
sheet.Range["C2"].Value = "1998-05-10"
sheet.Range["D2"].Value = "85.5"

sheet.Range["A3"].Value = "Li Si"
sheet.Range["B3"].Value = "30"
sheet.Range["C3"].Value = "1993-11-20"
sheet.Range["D3"].Value = "92"

# Auto-fit columns
sheet.AutoFitColumn(1)
sheet.AutoFitColumn(2)
sheet.AutoFitColumn(3)
sheet.AutoFitColumn(4)

# Save the file
workbook.SaveToFile("Sample.xlsx", ExcelVersion.Version2016)
workbook.Dispose()
print("Sample.xlsx file has been successfully created!")
Enter fullscreen mode Exit fullscreen mode

Reading Excel Workbook and Worksheet

Now that we have the Sample.xlsx file, let's demonstrate how to use Spire.XLS for Python to load it and access the worksheet:

from spire.xls import *
from spire.xls.common import *

# Create a workbook object
workbook = Workbook()

# Load the Excel file
workbook.LoadFromFile("Sample.xlsx")

# Get the first worksheet
sheet = workbook.Worksheets[0]
print(f"The name of the first worksheet is: {sheet.Name}")

# Release resources
workbook.Dispose()
Enter fullscreen mode Exit fullscreen mode

Advanced Reading of Excel Data

Reading Cell Data

Spire.XLS for Python provides various methods to read cell data, including by index and by name:

from spire.xls.common import *

workbook = Workbook()
workbook.LoadFromFile("Sample.xlsx")
sheet = workbook.Worksheets[0]

# Read cell contents by row and column indices
cell_a1_value = sheet.Range[1, 1].Value
cell_b2_value = sheet.Range[2, 2].Value

print(f"The value of cell A1: {cell_a1_value}")
print(f"The value of cell B2: {cell_b2_value}")

workbook.Dispose()
Enter fullscreen mode Exit fullscreen mode

Iterating Through Rows and Columns

Efficiently iterating through all rows and columns in a worksheet is a common operation in data processing:

# Get the number of used rows and columns
last_row = sheet.LastRow
last_column = sheet.LastColumn

# Iterate through all rows and columns
for row inrange(1, last_row + 1):
    row_data = []
for col inrange(1, last_column + 1):
        cell = sheet.Range[row, col]
        value = cell.Value
if cell.ValueType == CellValueType.IsDateTime:
            value = datetime.strptime(value, "%Y-%m-%d")
        row_data.append(value)
print(row_data)

# Example: Calculate the total of the score column
total_score = 0
for row inrange(2, last_row + 1):
    score_cell = sheet.Range[row, 4]
if score_cell.ValueType == CellValueType.IsNumber:
        total_score += float(score_cell.Value)
print(f"\nTotal Score: {total_score}")

workbook.Dispose()
Enter fullscreen mode Exit fullscreen mode

Reading Data from a Specific Range

Sometimes, we only need to read data from a specific range within the Excel file:

from spire.xls import *
from spire.xls.common import *

workbook = Workbook()
workbook.LoadFromFile("Sample.xlsx")
sheet = workbook.Worksheets[0]

# Read data from the A1:C3 range
range_data = sheet.Range["A1:C3"]

print("--- Reading data from the A1:C3 range ---")
for row_index inrange(range_data.Row, range_data.LastRow + 1):
    row_values = []
for col_index inrange(range_data.Column, range_data.LastColumn + 1):
        cell_value = sheet.Range[row_index, col_index].Value
        row_values.append(cell_value)
print(row_values)

workbook.Dispose()
Enter fullscreen mode Exit fullscreen mode

Handling Complex Data Types

Spire.XLS for Python can accurately read various complex data types. For example, if a cell contains a formula, you can retrieve either the formula itself or its calculated result:

from spire.xls import *
from spire.xls.common import *

# Create an Excel file containing a formula
workbook_formula = Workbook()
sheet_formula = workbook_formula.Worksheets[0]
sheet_formula.Range["A1"].Value = "10"
sheet_formula.Range["A2"].Value = "20"
sheet_formula.Range["A3"].Formula = "=SUM(A1:A2)"

workbook_formula.SaveToFile("FormulaSample.xlsx", ExcelVersion.Version2016)
workbook_formula.Dispose()

# Read the Excel file containing the formula
workbook = Workbook()
workbook.LoadFromFile("FormulaSample.xlsx")
sheet = workbook.Worksheets[0]

# Read the formula cell
formula_cell = sheet.Range["A3"]
print(f"The formula of cell A3: {formula_cell.Formula}")
print(f"The calculated result of cell A3: {formula_cell.Value}")

workbook.Dispose()
Enter fullscreen mode Exit fullscreen mode

Best Practices and Considerations

  • Resource Release : After completing Excel operations, always call the workbook.Dispose() method to release resources, especially when handling large files or long-running applications to avoid memory leaks.
  • Error Handling : When performing file operations, it is advisable to use try-except blocks to capture potential exceptions, such as FileNotFoundError, InvalidCastException, etc., to enhance program robustness.
  • Performance Optimization : For particularly large Excel files, consider reading in chunks or only loading specific areas to reduce memory consumption and improve processing speed.

Conclusion

In this article, we explored how to use the Spire.XLS for Python library to efficiently and accurately read Excel data. From the basic installation and file loading to advanced reading of cells, ranges, and complex data types, Spire.XLS for Python demonstrates its powerful functionality and convenience.

Whether for data analysis, report generation, or automating office processes, Spire.XLS for Python can be an invaluable assistant in handling Excel files. It adeptly addresses the challenges faced by traditional libraries when managing complex Excel files, greatly enhancing your development efficiency. We encourage you to try it out yourself and experience the convenience brought by Spire.XLS for Python!

Top comments (0)