using IronXL; using System; using System.Linq; // Supported for XLSX, XLS, XLSM, XLTX, CSV and TSV WorkBook workBook = WorkBook.Load("sample.xlsx"); // Select worksheet at index 0 WorkSheet workSheet = workBook.WorkSheets[0]; // Get any existing worksheet WorkSheet firstSheet = workBook.DefaultWorkSheet; // Select a cell and return the converted value int cellValue = workSheet["A2"].IntValue; // Read from ranges of cells elegantly. foreach (var cell in workSheet["A2:A10"]) { Console.WriteLine("Cell {0} has value '{1}'", cell.AddressString, cell.Text); } // Calculate aggregate values such as Min, Max and Sum decimal sum = workSheet["A2:A10"].Sum(); // Linq compatible decimal max = workSheet["A2:A10"].Max(c => c.DecimalValue);

using IronSoftware.Drawing; using IronXL; using IronXL.Styles; using System.Linq; WorkBook workBook = WorkBook.Load("sample.xls"); WorkSheet workSheet = workBook.WorkSheets.First(); var range = workSheet["A1:H10"]; var cell = range.First(); // 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 enum that stands for different types of font underlying range.Style.Font.Underline = FontUnderlineType.SingleAccounting; // Define whether to use horizontal line through the text or not range.Style.Font.Strikeout = false; // Define whether the font is bold or not range.Style.Font.Bold = true; // Define whether the font is italic or not range.Style.Font.Italic = false; // Get or set script property of a font // Font script enum stands for available options 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 range.Style.BottomBorder.Type = BorderType.MediumDashed; // Indicate whether the cell should be auto-sized range.Style.ShrinkToFit = true; // Set alignment of the cell range.Style.VerticalAlignment = VerticalAlignment.Bottom; // Set border color range.Style.DiagonalBorder.SetColor("#20C96F"); // Define border type and border direction as well range.Style.DiagonalBorder.Type = BorderType.Thick; // DiagonalBorderDirection enum stands for direction of diagonal border inside cell range.Style.DiagonalBorderDirection = DiagonalBorderDirection.Forward; // Set background color of cells range.Style.SetBackgroundColor(Color.Aquamarine); // Set fill pattern of the cell // FillPattern enum indicates the style of fill pattern range.Style.FillPattern = FillPattern.Diamonds; // Set the number of spaces to intend the text range.Style.Indention = 5; // Indicate if the text is wrapped range.Style.WrapText = true; workBook.SaveAs("stylingOptions.xls");

USING IRONXL

How to parse an Excel file in Python

Published April 29, 2024

Introduction

Spreadsheets made with Microsoft Excel are widely used in workflows for data processing and analysis in many different sectors. Python is a programming language that is quite versatile and has several libraries available for working with Excel files. One such library, IronXL, was created especially for .NET programs like IronPython and offers a smooth connection with Excel files. We will examine how to parse an Excel file in Python using IronXL in this in-depth tutorial.

Create a new Python project or create a new file with the .py extension
Install IronXL Library
Import the required library.
Import the file which needs to be parsed.
Access the specific sheet and parse the values.
process the values and Close the created objects.

IronXL

With the IronXL Python library, developers can effortlessly read and write Excel files in Python. You can work with several Excel sheets at once in addition to writing Excel files. You can manipulate Excel files using this library without having to install Microsoft Excel on your computer.

IronXL is useful when you need to import data straight into an Excel spreadsheet. Using IronXL simplifies the handling of Excel spreadsheets. It facilitates the simple management of data in an XLSX file across several sheets.

Key characteristics of IronXL

1. Reading and Writing Data to and from Excel Files

IronXL makes it simple for developers to read and write data to and from Excel files. IronXL offers simple ways to read from and manipulate Excel files, whether you're pulling data for analysis or creating reports.

2. Support for a Broad Range of Excel Formats

IronXL is compatible with a number of Excel formats, such as .csv files, .xls files, .xlsx files,.xlsm,.xlsb,.xltx, and. xltm. This adaptability guarantees compatibility with many Excel file formats and versions, enabling smooth working with Excel files of any format.

3. Worksheet and Cell Access

Developers may quickly access specific worksheets and cells in Excel workbooks by using IronXL. This makes it possible to precisely manipulate data at the worksheet and cell levels, making activities like data entry, editing, and extraction easier.

4. Formatting and style

IronXL offers extensive assistance with the formatting and style of Excel files. Excel files can be made more aesthetically pleasing and readable by developers by applying a variety of formatting choices, including font styles, colors, borders, alignment, and more, to individual cells, rows, and columns.

5. Formula Calculation

IronXL has functionality for calculating Excel formulas, enabling programmers to assess formulas inside Excel documents. This capability is perfect for situations where automated data processing and analysis are needed since it allows complicated calculations and formulas to be executed.

6. Chart Generation

With IronXL, developers may use programming to generate and modify charts inside of Excel files. Developers can use this functionality to show correlations, trends, and patterns in data using a variety of chart formats, including pie charts, bar charts, and line charts.

7. Data Validation

IronXL has data validation capabilities that let developers set limitations and guidelines for entering data into Excel files. By restricting users from entering erroneous or incorrect data, this function helps maintain data integrity and guarantees the quality and dependability of Excel documents.

8. Performance Optimization

IronXL has been designed with performance in mind, making it capable of handling big Excel files and datasets with ease. IronXL's specialized algorithms and data structures offer quick and dependable performance while reading, writing, and modifying data—even when working with large Excel spreadsheets.

9. Thorough Documentation and Support

To assist developers in getting started quickly and making the most of IronXL's features, the tool provides a wealth of documentation that includes tutorials, guides, and API references. In addition, Iron Software offers committed assistance to help developers with any queries or problems they could run across when utilizing the library.

To know more about the IronXL documentation refer here.

Prerequisites

Make sure the following prerequisites are installed on your machine before beginning the tutorial:

.NET framework: Your machine must have the .NET 6.0 SDK installed.
Python 3.0+: You must have Python 3.0 or higher installed in order to follow this tutorial.
pip: Since IronXL will be installed via pip, make sure pip, the Python package installer, is installed.

Setting Up Your Environment

1. Creating a File in Visual Studio Code

Launch Visual Studio Code, then create the ParseExcel.py Python file. This file will include our IronXL script for reading Excel files.

How to parse an Excel file in Python: Figure 1 - Open Visual Studio Code editor and create a new file

2. Installing IronXL

In Visual Studio Code, choose Terminal > New Terminal from the menu to launch the command line.

How to parse an Excel file in Python: Figure 2

To install IronXL, execute the subsequent command:

pip install ironxl

pip install ironxl

'INSTANT VB TODO TASK: The following line uses invalid syntax:
'pip install ironxl

VB C#

How to parse an Excel file in Python: Figure 3 - To install IronXL, use the following command: pip install ironxl

Parse Excel files using IronXL

The process to read excel files is made easy with the help of the IronXL library. Parsing Excel files in python can be easily done with a few lines of code.

from ironxl import *     workbook = WorkBook.Load("Demo.xlsx")
worksheet = workbook.WorkSheets [0]  # Accessing the first worksheet
for row in range(worksheet.RowCount):
    for col in range(worksheet.ColumnCount):
        cell_value = worksheet.Columns [col].Rows [row].Value
        print(cell_value)
workbook.Close()

from ironxl import *     workbook = WorkBook.Load("Demo.xlsx")
worksheet = workbook.WorkSheets [0]  # Accessing the first worksheet
for row in range(worksheet.RowCount):
    for col in range(worksheet.ColumnCount):
        cell_value = worksheet.Columns [col].Rows [row].Value
        print(cell_value)
workbook.Close()

'INSTANT VB TODO TASK: The following line uses invalid syntax:
'from ironxl import * workbook = WorkBook.Load("Demo.xlsx") worksheet = workbook.WorkSheets [0] # Accessing the first worksheet for row in range(worksheet.RowCount): for col in range(worksheet.ColumnCount): cell_value = worksheet.Columns [col].Rows [row].Value print(cell_value) workbook.Close()

VB C#

Importing the required IronXL modules into your script is the first step. Next, use the WorkBook class in IronXL to import Excel files. The path of your Excel file should be used instead of "Demo.xlsx" or we can use a different type of file format like CSV file, XLS, and xlsx file formats. We can access an individual Excel sheet inside the workbook once the Excel file has loaded using the WorkBook.Load() method. The IronXL library also allows us to access multiple Excel sheets by using the index or sheet names.

Next, we extract data from the Excel file by iterating over rows and columns while having access to the worksheet. This little piece of code prints the value of each spreadsheet cell as it iterates over them. This logic can be adjusted to meet your unique needs, such as gathering data for additional processing or examination. It is imperative that you close the Excel file using the workbook.Close() method once you have completed processing it in order to free up system resources.

Output generated from the above code

How to parse an Excel file in Python: Figure 4 - Output generated using IronXL to read and extract data from an excel file.

To learn more about the IronXL code refer here.

Conclusion

An efficient way to work with Excel spreadsheets in your Python programs is to parse Excel files using IronXL. Through the combination of IronXL with IronPython, developers may effectively manage data by utilizing the combined capabilities of Excel and .NET. You can easily parse Excel files in your Python applications by following the instructions in this article and using the example. This opens up a world of possibilities for data analysis and manipulation. IronXL is a useful tool for interacting with Excel files in Python programs because of its user-friendly API and comprehensive documentation.

Recall that there are a plethora of options available for processing and presenting data. Having IronXL in your toolbox gives you the ability to manage a variety of activities linked to Excel in your Python programs.

When ready to commit, users can start using IronXL for free with its trial version. After that, license choices start at $749. To know more about the IronXl license, please refer to the license page.

To know more about other products offered by Iron Software, please check their website.

< PREVIOUS
How to Autofit Cells in Excel Using C#

NEXT >
How to Create a new Excel Workbook in C#