How to View an Excel File in Python

In this tutorial, we will explore how to use Python for viewing Excel files effectively. Excel files, commonly used for data storage such as storing tabular data, are well-supported by several Python libraries that facilitate reading and manipulation. We will focus on the popular and best library "IronXL" for this purpose.

How to View an Excel File in Python

1. Install the IronXL Library.
2. Load the Excel Workbook.
3. Specify the Excel Worksheet.
4. Select a Specific Range of Data.
5. Print the Selected Data Range on the Screen.
6. Read Cell Value.
7. Read Complete Row from Excel File.
8. Read the Complete Column from the Excel file.

Introduction to Python Excel Viewer

Before diving into the code, let's discuss the benefits of using Python to view Excel files. Python is well known for its simplicity and versatility, making it a powerful and robust programming language. By leveraging Python libraries, we can automate tasks related to data analysis, manipulation, and visualization, including handling Excel files.

Why Python for Excel?

Automation: Python enables the automation of repetitive tasks associated with Excel, such as data extraction, transformation, and analysis.

Integration: Python seamlessly integrates with other data science libraries like NumPy, pandas, and Matplotlib, enabling comprehensive data analysis workflows.

Cross-platform: Python, in its latest Python version, runs on multiple platforms, making it suitable for users across different operating systems.

Customization: Python provides flexibility to customize Excel workflows according to specific requirements, unlike conventional Excel macros.

Before proceeding further, let's understand what IronXL is, what features it provides, and how it is better than others.

Why IronXL?

IronXL is a Python library developed and maintained by Iron Software that allows software engineers to work with Excel and other spreadsheet files in Python applications and websites. Below are some of its notable key features:

Importing Data: IronXL can read data from XLS, XLSX, CSV, and TSV files.

Export Work Sheets: You can export data to XLS, XLSX, CSV, TSV, and JSON formats.

Encryption and Decryption: IronXL supports encrypting and decrypting XLSX, XLSM, and XLTX files with passwords.

Excel Formulas: Every time a sheet is edited, the formulas are recalculated.

Intuitive Ranges Setting: You can specify ranges using a syntax like "A1:B10".

Sorting: Ranges, columns, and rows can be sorted.

Cell Styling: Customize font, size, background pattern, border, and alignment.

Cross-Platform Support: IronXL is compatible with Python 3.7+ on Windows, macOS, Linux, Docker, Azure, and AWS.

Reading Excel File using IronXL

Let's begin step by step to read an Excel file.

Step 1: Installing IronXL Library

Before working with Excel files in Python, we need to ensure that the IronXL Library is installed. Install it with the following command.

pip install IronXL

pip install IronXL

This command installs the IronXL Library in our project.

Step 2: Load Excel file

The next step involves loading an Excel workbook into our project. I will be using the following Excel spreadsheet throughout this tutorial.

The following code will load the existing Excel file in the memory stream.

from ironxl import *     
# Supported for XLSX files, XLS, XLSM, XLTX, CSV, and TSV
License.LicenseKey = "IRONSUITE.ABC.XYZ.COM.15796-ABC.TRIAL.EXPIRES.27.MAY.2024"
workbook = WorkBook.Load("test_excel.xlsx")  # Load existing Excel files

from ironxl import *     
# Supported for XLSX files, XLS, XLSM, XLTX, CSV, and TSV
License.LicenseKey = "IRONSUITE.ABC.XYZ.COM.15796-ABC.TRIAL.EXPIRES.27.MAY.2024"
workbook = WorkBook.Load("test_excel.xlsx")  # Load existing Excel files

The above code demonstrates how to use the IronXL library in Python to load an Excel workbook named "test_excel.xlsx" and access its contents. By setting the LicenseKey attribute with a valid license key, the library enables support for various Excel file formats including XLSX, XLS, XLSM, XLTX, CSV, and TSV. You can get your free license key from here.

Step 3: Select Excel Spreadsheet

The next step is to select an Excel spreadsheet to work on. Excel files consist of multiple sheets; therefore, it is necessary to select an active spreadsheet. The following code will specify the spreadsheet.

# Select worksheet at index 0
worksheet = workbook.WorkSheets[0]

# Select worksheet at index 0
worksheet = workbook.WorkSheets[0]

The above line of code selects the first worksheet with zero-indexed from the loaded Excel workbook, enabling access to the data and properties of that specific sheet for further manipulation or analysis.

Step 4: Viewing Data

As we have loaded the workbook and selected the spreadsheet, let's write code to read an Excel file and print its data.

# Read from ranges of cells elegantly.
for cell in worksheet["A1:H10"]:
    print("Cell {} has value '{}'".format(cell.AddressString, cell.Text))

# Read from ranges of cells elegantly.
for cell in worksheet["A1:H10"]:
    print("Cell {} has value '{}'".format(cell.AddressString, cell.Text))

This code snippet demonstrates a sophisticated method for reading from cell ranges in an Excel worksheet using the IronXL library. It iterates over the specified range of cells (from A1 to H10 in this case) and prints out each cell's address and value. This provides a concise and effective method for accessing and processing data within the specified range of cells.

Step 5: Read Cell Value

IronXL provides simpler methods to read cell values. We can efficiently read specific cell values from large datasets. The following code reads the cell value and prints it on the screen.

# Read Integer value
int_cell_value = worksheet["H2"].IntValue 
print(int_cell_value)
# Read String value
text_cell_value = worksheet["B2"].StringValue 
print(text_cell_value)

# Read Integer value
int_cell_value = worksheet["H2"].IntValue 
print(int_cell_value)
# Read String value
text_cell_value = worksheet["B2"].StringValue 
print(text_cell_value)

This code snippet demonstrates how to extract an integer value from cell H2 and a string value from cell B2 in an Excel worksheet using the IronXL library. It then prints out the extracted values, providing clear and organized output for further processing or display.

Step 6: Select Complete Row

IronXL provides a method to select a specific row from an Excel file. The following code will read a specific row from the Excel file and print it on the screen.

# Get row from worksheet
row_1 = worksheet.GetRow(1)
print(row_1)

# Get row from worksheet
row_1 = worksheet.GetRow(1)
print(row_1)

This code snippet demonstrates how to retrieve a specific row from an Excel worksheet using the IronXL library. It selects the first row (row index 1) from the worksheet and then prints it out, allowing for further processing or analysis of the row's data. In this way, we can get all the rows from the Excel sheet.

Step 7: Select Complete Column

IronXL provides a method to select a specific Column from an Excel file. The following code will read a specific Column from the Excel file and print it on the screen.

# Get Column from worksheet
column_a = worksheet.GetColumn(1)
print(column_a)

# Get Column from worksheet
column_a = worksheet.GetColumn(1)
print(column_a)

This code snippet illustrates how to extract a specific column from an Excel worksheet using the IronXL library. It retrieves the data from column A (column index 1) and prints it out, providing access to the column's contents for further manipulation or analysis.

Conclusion

In this tutorial, we explored how to use Python for Excel file viewing, focusing on the IronXL library. Python's versatility makes it ideal for automating Excel-related tasks, and IronXL enhances this capability by providing features like importing options for individual developers and organizations. With IronXL and Python, handling Excel files becomes more efficient, enabling developers to unlock the full potential of Excel data within their applications.