from ironxl import * # Supported for XLSX, XLS, XLSM, XLTX, CSV, and TSV workbook = WorkBook.Load("sample.xlsx") # Select worksheet at index 0 worksheet = workbook.WorkSheets[0] # Get any existing worksheet first_sheet = workbook.DefaultWorkSheet # Select a cell and return the converted value cell_value = worksheet["A2"].IntValue # Read from ranges of cells elegantly. for cell in worksheet["A2:A10"]: print("Cell {} has value '{}'".format(cell.AddressString, cell.Text)) # Calculate aggregate values such as Min, and Sum total_sum = worksheet["A2:A10"].Sum()

from ironxl import * # Load existing spreadsheet workbook = WorkBook.Load("sample.xls") worksheet = workbook.WorkSheets[0] # Get a range from an Excel worksheet selected_range = worksheet["A1:H10"] # Get the first cell in the range cell = selected_range.ToArray()[0] # Set background color of the cell with an RGB string cell.Style.SetBackgroundColor("#428D65") # Apply styling to the whole range # Set underline property to the font # FontUnderlineType is an enum that stands for different types of font underline selected_range.Style.Font.Underline = FontUnderlineType.SingleAccounting # Define whether to use a horizontal line through the text or not selected_range.Style.Font.Strikeout = False # Define whether the font is bold or not selected_range.Style.Font.Bold = True # Define whether the font is italic or not selected_range.Style.Font.Italic = False # Get or set the script property of a font # FontScript enum stands for available options selected_range.Style.Font.FontScript = FontScript.Super # Set the type of the border line # There are also TopBorder, LeftBorder, RightBorder, DiagonalBorder properties # BorderType enum indicates the line style of a border in a cell selected_range.Style.BottomBorder.Type = BorderType.MediumDashed # Indicate whether the cell should be auto-sized selected_range.Style.ShrinkToFit = True # Set alignment of the cell selected_range.Style.VerticalAlignment = VerticalAlignment.Bottom # Set border color selected_range.Style.DiagonalBorder.SetColor("#20C96F") # Define border type and border direction as well selected_range.Style.DiagonalBorder.Type = BorderType.Thick # DiagonalBorderDirection enum stands for direction of diagonal border inside cell selected_range.Style.DiagonalBorderDirection = DiagonalBorderDirection.Forward # Set background color of cells selected_range.Style.SetBackgroundColor(Color.Aquamarine) # Set fill pattern of the cell # FillPattern enum indicates the style of fill pattern selected_range.Style.FillPattern = FillPattern.Diamonds # Set the number of spaces to indent the text selected_range.Style.Indention = 5 # Indicate if the text is wrapped selected_range.Style.WrapText = True # Save changes with applied styling options workbook.SaveAs("stylingOptions.xls")

USING IRONXL FOR PYTHON

How to use Python to read Excel spreadsheets

Published June 6, 2024

Introduction

Effective data management and processing is essential for both individuals and enterprises in the data-driven world of today. Because of its convenience and adaptability, Excel spreadsheets continue to be one of the most widely used file formats used for data organization and storage. With its robust libraries, the Python programming language provides an abundance of tools for working with Excel files.

The IronXL library is one of these tools and is one of the most reliable options to read and write Excel files easily. This tutorial will cover the nuances of using IronXL to (in Python) read Excel spreadsheet files, giving users the ability to read Excel files and optimize their workflows for data management.

How to use Python to read Excel spreadsheet

Open Visual Studio Code and create a Python file.
Use pip to install the Python IronXL library.
Open the Excel document that needs to be read.
Excel data iteration using loops
Display recurrent data on the console

IronXL

IronXL is a feature-rich Python library created specifically for use with Excel files. It offers programmers a multitude of options for reading, editing, and modifying spreadsheet data. IronXL, which is built on top of the .NET framework, provides an effective way to interact with Excel files by fusing the performance of .NET with the flexibility of Python.

IronXL's ability to read data from current Excel files with ease is one of its main features. Developers can effortlessly extract data from specific files, such as object cells, rows, lists of column names numeric columns, or columns, or deal with missing values within Excel spreadsheets, enabling seamless integration of Excel data into Python programs. IronXL offers the resources required to efficiently access and manipulate Excel data, whether it be for financial, customer, or sales data retrieval.

Features of IronXL

IronXL facilitates the seamless reading of data from pre-existing Excel files and the writing of data to newly created or pre-existing spreadsheets. This covers a variety of topics such as formulae, formatting, and cell value access.

Key Feature Examples

Cross-Platform Compatibility: IronXL is an adaptable option for Python developers regardless of their operating system because it is made to function flawlessly across a variety of platforms, including Windows, Linux, and macOS.
Efficient efficiency: IronXL, which is built on top of the .NET framework, combines the efficiency of .NET with the flexibility of Python to process and manipulate Excel files efficiently, even when dealing with enormous datasets.
Support for Excel Formats: IronXL is compatible with a wide range of Excel files and supports the following Excel formats .xls (Excel 97-2003), .xlsm (Excel with macros enabled), and the .xlsx file format (Excel 2007 onwards).
Advanced Data Manipulation: IronXL gives users the ability to manipulate data in Excel spreadsheets in a more sophisticated way, allowing them to extract insightful information from their data through operations like sorting, filtering, and aggregating.
Cell Formatting: Excel spreadsheets can be made more aesthetically pleasing and readable by using IronXL's cell formatting features, which include font styles, colors, borders, and alignment.
Formula Calculation: IronXL can be used by users to work with Excel formulas, including formula evaluation, formula reference updating, and dynamic calculation inside Excel spreadsheets.
Integration with the Python environment: IronXL easily incorporates into the Python environment, enabling users to combine its features with those of other Python frameworks and packages for in-depth data visualization and analysis.
Ease of Use: Both inexperienced and seasoned Python developers can utilize IronXL thanks to its user-friendly, straightforward API. Its well-documented interface offers precise instructions on how to make the most of its features.

In summary, IronXL provides a stable and user-friendly way to incorporate Excel features into Python apps, enabling Python developers to overcome the difficulties involved in handling Excel files. Whether you're developing interactive dashboards, automating reporting chores reading Excel files, or developing data analysis tools reading Excel files, IronXL offers the flexibility and resources required to succeed in manipulating Excel files within the Python ecosystem.

To know more about the IronXL library and all of its features checkhere.

Setup Environment

Prerequisites

Make sure you have the following installed on your PC before beginning the tutorial:

Because IronXL is designed with the .NET 6.0 SDK, your machine must have this installed.
Python 3.0+: This lesson assumes that you have installed Python 3.0 or a later version.
pip: Install pip first, the Python package installer, since IronXL will require it.

Install IronXL

Open this file the Visual Studio Code, and then make a Python file called ReadSpreadsheet.py. This file contains our script for reading Excel files with IronXL.

How to use Python to read Excel spreadsheets: Figure 1 - Creating a new Python file

To access the command line in Visual Studio Code, go to the menu and click Terminal > New Terminal.

How to use Python to read Excel spreadsheets: Figure 2 - Where to find the terminal

Installing the library is the first step to take before utilizing IronXL. Using Python's package manager pip, you can quickly install IronXL by running the following command:

 pip install IronXL

Now that IronXL is installed, you may use its Excel spreadsheet file reading capabilities.

How to use Python to read Excel spreadsheets: Figure 3 - Installation output on the terminal

Reading Excel spreadsheet

IronXL makes it easy to read data analysis in an Excel spreadsheet. To commence, let us import data type from a pre-existing Excel sheet file format into our Python environment:

from ironxl import *     # Load the Excel file
workbook = WorkBook.Load("Demo.xlsx")
# select zero indexed sheet positions
worksheet = workbook.DefaultWorkSheet
# Access cell values
cell_value = worksheet["A1"].StringValue
# Access row values
row_values = worksheet.Rows[1].StringValue
# parsing string columns
column_values = worksheet.Columns[0].StringValue
for row in worksheet.Rows:
    print(row.StringValue)

from ironxl import *     # Load the Excel file
workbook = WorkBook.Load("Demo.xlsx")
# select zero indexed sheet positions
worksheet = workbook.DefaultWorkSheet
# Access cell values
cell_value = worksheet["A1"].StringValue
# Access row values
row_values = worksheet.Rows[1].StringValue
# parsing string columns
column_values = worksheet.Columns[0].StringValue
for row in worksheet.Rows:
    print(row.StringValue)

PYTHON

We use the location of our Excel file to instantiate the WorkBook class after importing it from IronXL. In doing so, we generate a workbook object that we can use for the Excel sheet and retrieve the spreadsheet's contents. Once we've loaded it into a workbook object, we can access the Excel spreadsheet's data, including cell values, rows, and columns stored in the object. IronXL Excel workbooks additionally offer simple ways to make and get data from multiple Excel sheets.

We can obtain the value of a single cell (A1), as well as tabular data such as the values of a specific row, column labels, and a list of column names. Working with data often involves looping through the rows and columns of an Excel spreadsheet. IronXL makes this process easier with its handy iteration techniques. We can easily process and manipulate the data contained within the Excel sheet by iterating over each row, column index, and cell.

How to use Python to read Excel spreadsheets: Figure 4 - Input Excel sheet

By reading the above Excel we will get the below output from the ironXL.

How to use Python to read Excel spreadsheets: Figure 5 - Console output from the previous code

To learn about IronXL code refer here.

Conclusion

Finally, IronXL Python allows developers to overcome the difficulties involved in processing Excel files by acting as a bridge between Python and Excel files. IronXL is a feature-rich tool that offers a stable and user-friendly way to include Excel file-using functionality into Python programs. It can read and write data, format cells, visualize data, operate with formulas, and be compatible with multiple platforms. IronXL Python provides the flexibility and tools required to succeed in Excel file manipulation within the Python ecosystem, regardless of your background as a software developer, data scientist, or business analyst.

Whether you work as a software developer, business analyst sales data miner, or data scientist, IronXL can help you become an expert spreadsheet reader and open up a world of possibilities for productive data manipulation. Use IronXL to read an Excel to explore the realm of data management and increase your Python knowledge.

IronXL's $749 Lite edition comes with a year of software support, upgrade options, and a permanent license. Customers have the opportunity to assess the product in practical settings throughout the trial time. Please visit the license page. Alternatively, to find out more about Iron Software, visit this page.