The Python Excel Library
In this article, we'll be discussing two widely used Python libraries for managing Excel spreadsheet files: IronXL and OpenPyXL. These libraries are important for Python developers who need to manipulate, analyze, or create Excel file documents. Each library provides unique features to facilitate the handling of Excel files more effectively and efficiently.
IronXL and OpenPyXL offer developers the capability to automate spreadsheet operations, edit Excel sheet cells, and extract sample data. They also support advanced features such as creating formulas, styling cells, and handling large datasets seamlessly. This ensures that applications can manage Excel files efficiently across various environments without compromising on performance or compatibility.
In this comparison, we will explore the unique features of each library and how they work. We'll also cover their licensing details. Let's look at the differences and capabilities of IronXL and OpenPyXL, focusing on clear and useful information for developers.
OpenPyXL is a Python library tailored for handling Excel files in the XLSX format. The library allows developers to read, write, and modify Excel files. OpenPyXL module supports a variety of Excel functionalities including data manipulation, formatting, and advanced features like charts and pivot tables.
OpenPyXL excels at creating and editing workbooks. It allows you to start with a blank workbook or modify an existing one, add or remove sheets, and navigate through sheets easily. This makes it perfect for tasks ranging from generating reports to automating data entries.
This library supports advanced data handling capabilities such as appending rows, inserting formulas, and applying styles to the active sheet. You can manipulate large datasets by setting cell values programmatically, which simplifies tasks like data analysis and report generation.
With OpenPyXL, you can apply cell formatting such as fonts, colors, and borders. It also supports conditional formatting and setting cell properties like alignment which are essential for creating professionally styled Excel reports.
OpenPyXL can add graphical elements to spreadsheets. You can insert charts to visualize data and even add images to enhance the presentation of information. This feature is particularly useful for creating dashboards and complex reports.
The library allows for the integration of Excel formulas into cells, enabling automated calculations within spreadsheets. It also supports creating named ranges, which can be very handy in organizing data and creating more readable and maintainable sheets.
OpenPyXL provides tools for data validation and sorting, which are crucial for maintaining data integrity and organizing information effectively. It also offers features for filtering data, making it easier to manage large volumes of data within Excel files.
As an open-source library, OpenPyXL benefits from the ongoing contributions of a vibrant community, ensuring it remains relevant and feature-rich. This community involvement provides a vast pool of resources and support for developers using the library.
IronXL is a robust Python library designed for manipulating Excel files without needing Microsoft Excel. It's favored for its simplicity and effectiveness in handling various Excel tasks directly within Python environments.
IronXL simplifies the creation, loading, and saving of Excel formats like XLS, XLSX, and CSV, among others. It ensures compatibility with different Excel versions and provides comprehensive functionality for managing Excel data programmatically.
With IronXL, you can efficiently sort data, manage rows and columns, and customize cell formatting. This includes setting up data types, and formulas, and even implementing Excel functions within cells.
IronXL allows extensive styling options for cells, including setting fonts, borders, and background colors. It supports advanced Excel features like conditional formatting and setting print options for worksheets, enhancing the readability and presentation of your Excel reports.
The library supports editing metadata, managing permissions, and password-protecting files, ensuring that sensitive data is safeguarded.
IronXL is designed to work across multiple operating systems including Windows, macOS, and Linux, ensuring that developers can deploy their applications on various platforms without compatibility issues.
IronXL offers robust functions for handling sheet ranges and individual cells. You can easily merge, copy, and manipulate cell ranges. This capability simplifies managing complex data setups.
IronXL seamlessly integrates with databases and data sources. It supports the import and export of data to and from Excel files. This feature is essential for applications that require dynamic data reporting and analytics.
To begin testing IronXL and OpenPyXL libraries, we need to set up a project in Visual Studio Code, tailored specifically for Python development.
Before we start, make sure Visual Studio Code is installed on your computer. It's also necessary to have Python installed, along with the Python extension for Visual Studio Code. This extension can be added directly from the Extensions view in Visual Studio Code.
Start by creating a new folder on your system, which will serve as the workspace for your project. You can do it easily using the Open folder option from the File menu of VS Code.
Within Visual Studio Code, it's advisable to set up a virtual environment to manage dependencies cleanly. You can do this using the integrated terminal in Visual Studio Code. Visual Studio Code also provides an interface in the Python extension to simplify this process. Through the Python extension's command palette, you can create and activate a virtual environment without typing out the commands manually.
Press Ctrl+Shft+P in VS Code search for "Python: Create Environment" and click on it.
Select an environment type from the dropdown list. In our case, it'll be ".venv":
After that, select the Python interpreter path:
It'll create the virtual Python environment inside the folder.
Installing the IronXL library in Visual Studio Code is a straightforward process. IronXL is particularly useful as it does not require Microsoft Office to be installed, operating instead on top of the .NET framework.
Before installing IronXL, ensure that your environment is already configured with Python and Visual Studio Code. It's important to have the .NET 6.0 runtime installed on your machine since IronXL relies on .NET technologies to function. For systems not already equipped with .NET, such as Linux or macOS, you might need to download it from the .NET official website.
To install IronXL, use the pip package manager, which simplifies the installation of Python libraries. Follow these steps in the terminal within Visual Studio Code:
Open the Terminal: Access the terminal through Visual Studio Code by using the shortcut Ctrl+ or navigating to Terminal > New Terminal from the top menu.
pip install IronXL
The above code command will download and install the IronXL library along with any dependencies required for its operation.
Confirm Installation: After the installation process completes, you can verify that IronXL is installed correctly by attempting to import it in a new Python script:
from ironxl import *If no errors appear, the library is successfully installed and ready for use.
While installing IronXL, if you encounter any issues related to dependencies or compatibility with .NET, ensure that all system requirements are met as specified by IronXL documentation. It may be necessary to adjust system paths or install additional support libraries depending on your operating system.
By following these instructions, IronXL will be installed in your Python environment in Visual Studio Code, setting the stage for enhanced Excel file manipulation and data processing capabilities within your Python projects.
The installation of OpenPyXL can be done using pip, Python’s package installer. It’s recommended to install this library within a virtual environment to avoid conflicts with other Python projects or system packages.
Using pip to install OpenPyXL: Open your command line interface (CLI) in Visual Studio Code or your preferred environment where your Python environment is active. Enter the following command:
pip install openpyxlThis command downloads and installs the OpenPyXL library along with any dependencies needed.
Optional Dependencies: For advanced usage, such as including images or working with large files, OpenPyXL suggests installing additional libraries like lxml and Pillow. To install these, use:
pip install lxml pillowAfter installation, you can verify that OpenPyXL is installed correctly by attempting to import OpenPyXL module in your Python script:
from openpyxl import Workbook // openpyxl import workbook
wb = Workbook()If the above code executes without errors, OpenpyXL is ready to be used in your projects. By following these steps, you can easily set up OpenPyXL and begin automating Excel file operations in your Python applications.
IronXL is a comprehensive Python library designed to enhance Excel file manipulation. This tool simplifies the process of creating, editing, and managing Excel files within Python applications, providing a range of features that cater to various needs, from basic spreadsheet operations to advanced data handling.
IronXL excels in handling different Excel formats, including XLS, XLSX, and CSV. Users can easily import sample data from these formats or export worksheets to them, facilitating versatile data exchange and reporting solutions. You can also select the active sheet to work on a specific sheet.
from ironxl import * workbook = WorkBook.Load("sample.csv")
workbook2 = WorkBook.Load("sample.xlsx")
workbook3 = WorkBook.Load("sample.xls")The library supports Excel formulas, allowing calculations to be carried out within the Excel sheet. Every time a sheet is edited, formulas are automatically recalculated, ensuring data consistency and accuracy.
from ironxl import *
# Create a new Excel workbook
new_workbook = WorkBook()
# Add a worksheet to the workbook
new_worksheet = new_workbook.CreateWorkSheet("NewSheet")
# Set new formulas with different logic
new_worksheet["A1"].Formula = "Average(D1:D5)"
new_worksheet["D1"] = 10
new_worksheet["D2"] = 20
new_worksheet["D3"] = 30
new_worksheet["D4"] = 40
new_worksheet["D5"] = 50
new_worksheet["B1"].Formula = "=D3*D4"
new_worksheet["C1"].Formula = "Min(D1:D5)"
# Force recalculate all formulas in the workbook
new_workbook.EvaluateAll()
# Get the calculated values for each formula
average_value = new_worksheet["A1"].ToArray()[0].FormattedCellValue
product_value = new_worksheet["B1"].ToArray()[0].FormattedCellValue
min_value = new_worksheet["C1"].ToArray()[0].FormattedCellValue
# Save the new workbook to a file with updated formulas and calculated values
new_workbook.SaveAs("sample.xlsx")
IronXL offers extensive cell formatting options. Users can adjust fonts, sizes, backgrounds, borders, and alignment directly in an Excel spreadsheet through code. This feature helps prepare data presentations and reports that require specific stylistic guidelines.
from ironxl import * License.LicenseKey = "License Code";
workbook = WorkBook()
new_worksheet = workbook.CreateWorkSheet("FinanceData")
selected_range = new_worksheet["B2:B6"]
new_worksheet["B2"].Value = "Revenue"
new_worksheet["B3"].Value = 150000
new_worksheet["B4"].Value = 200000
new_worksheet["B5"].Value = 180000
new_worksheet["B6"].Value = 220000
selected_range.Style.SetBackgroundColor("#4CAF50")
selected_range.Style.Font.Underline = FontUnderlineType.Single
selected_range.Style.Font.Strikeout = False
selected_range.Style.Font.Bold = True
selected_range.Style.Font.Italic = False
selected_range.Style.TopBorder.Type = BorderType.Thin
selected_range.Style.BottomBorder.Type = BorderType.Thin
selected_range.Style.LeftBorder.Type = BorderType.Thin
selected_range.Style.RightBorder.Type = BorderType.Thin
selected_range.Style.TopBorder.SetColor("#000000")
selected_range.Style.BottomBorder.SetColor("#000000")
selected_range.Style.LeftBorder.SetColor("#000000")
selected_range.Style.RightBorder.SetColor("#000000")
selected_range.Style.VerticalAlignment = VerticalAlignment.Center
selected_range.Style.HorizontalAlignment = HorizontalAlignment.Center
selected_range.Style.FillPattern = FillPattern.LessDots
selected_range.Style.Indention = 0
selected_range.Style.WrapText = True
# Save the new workbook to a file with updated formulas and calculated values
workbook.SaveAs("styled.xlsx")
To enhance data integrity, IronXL includes data validation features. It also provides robust sorting capabilities for ranges, columns, and rows, making it easier to organize and analyze large datasets.
Security features in IronXL include the ability to encrypt and decrypt Excel files. This ensures that sensitive information is protected and only accessible to authorized users.
from ironxl import *
# Load a different Excel workbook
workbook = WorkBook.Load("data_file.xlsx")
worksheet = workbook.DefaultWorkSheet
# Open a different protected spreadsheet file
protected_workbook = WorkBook.Load("protected_data.xlsx", "ExcelSecure")
# Apply encryption to the spreadsheet file
workbook.Encrypt("ExcelSecure")
# Clear any existing password protection
workbook.Password = None
# Save changes to the workbook
workbook.Save()
worksheet.ProtectSheet("SheetSecure")
worksheet.UnprotectSheet()
workbook.Save()
IronXL is not limited to handling Excel files such as XLSX, XLSM, XLTX, XLTM files. It can also convert Excel documents into JSON, XML, HTML, or binary formats, expanding the possibilities for data utilization across different platforms and programming environments.
from ironxl import *
workbook = WorkBook.Create()
worksheet = workbook.CreateWorkSheet("Sheet1")
worksheet["B1"].Value = "Sample Text"
worksheet["B2"].Style.BottomBorder.SetColor("#0077CC")
workbook.SaveAs("output.xls")
workbook.SaveAs("output.xlsx")
workbook.SaveAs("output.tsv")
workbook.SaveAsCsv("output.csv")
workbook.SaveAsJson("output.json")
workbook.SaveAsXml("output.xml")
workbook.ExportToHtml("output.html")The library simplifies workbook management by allowing users to create, load, and manipulate entire workbooks or individual worksheets. This includes adding or deleting sheets, as well as setting sheet visibility and print settings.
from ironxl import *
# Load the Excel workbook
workbook = WorkBook.Load("sample.xlsx")
# Create a new worksheet
new_worksheet = workbook.CreateWorkSheet("NewSheet")
# Fill data into the new worksheet
for i in range(1, 21):
new_worksheet[f"A{i}"].Value = f"Data {i}"
new_worksheet[f"B{i}"].Value = 21 - i
new_worksheet[f"C{i}"].Value = i % 5
new_worksheet[f"D{i}"].Value = (i * 3) % 7
# Select a range in the new worksheet
selected_range = new_worksheet["A1:D20"]
# Select column B in the new worksheet
column = new_worksheet.GetColumn(1)
# Sort the range in ascending order based on column A
selected_range.SortAscending()
# Sort the range by column C in ascending order
selected_range.SortByColumn("C", SortOrder.Ascending)
# Sort column B in descending order
column.SortDescending()
# Save the changes
workbook.SaveAs("sortedExcelRange.xlsx")
IronXL allows users to dynamically insert new rows and columns into existing Excel worksheets. This feature is particularly useful for modifying data structures in response to changing data needs without disrupting the overall layout of the spreadsheet.
# Add a row before second row
worksheet.InsertRow(1)
# Insert multiple rows before 5th Row
worksheet.InsertRows(4, 4)
# Add a column before column C
worksheet.InsertColumn(2)
# Insert multiple columns before column B
worksheet.InsertColumns(1, 3)With IronXL, users can group or ungroup rows and columns. This functionality enhances the organization of data, allowing for easier navigation and management of complex datasets by collapsing and expanding grouped data as needed.
IronXL supports the repetition of rows and columns across multiple pages of a workbook. This is useful for creating headers or footers that need to appear on every printed page of an Excel document, ensuring consistent presentation and readability.
# Configure rows 2 to 5 to repeat at the top of each printed page
worksheet.SetRepeatingRows(1, 4)
# Configure columns D to F to repeat on the left side of each printed page
worksheet.SetRepeatingColumns(3, 5)
# Insert a page break after column J so that columns A-J appear on the first printed page
worksheet.SetColumnBreak(9)Users can duplicate existing worksheets within the same workbook using IronXL. This feature saves time when creating multiple sheets that share a common format or when backing up important data. It is very helpful for data copying purposes.
# Duplicate the worksheet to the same workbook
worksheet.CopySheet("Copied Sheet")
# Duplicate the worksheet to another workbook with the specified name
worksheet.CopyTo(second_book, "Copied Sheet")IronXL provides the capability to add images to worksheets, extract images from them, or remove images. This enhances the visual appeal of spreadsheets and is useful for reports that include graphical data, logos, or instructional images.
OpenPyXL allows conditional formatting, letting you change the style of cells based on the data they contain. This can include setting colors based on numerical values or applying different fonts and styles based on specific conditions, enhancing the visual presentation of data.
from openpyxl import Workbook
from openpyxl.formatting.rule import ColorScaleRule
wb = Workbook()
ws = wb.active
color_scale = ColorScaleRule(start_type='num', start_value=10, start_color='FF0000',
end_type='num', end_value=20, end_color='00FF00')
ws.conditional_formatting.add('A1:A10', color_scale)
wb.save('conditional_formatting.xlsx')With OpenPyXL, you can implement data validation to control the type of data entered into cells. This includes setting dropdown lists, restricting entries to a certain range of dates or numbers, and generating error messages for invalid inputs, which helps maintain data integrity.
from openpyxl import Workbook
from openpyxl.worksheet.datavalidation import DataValidation
wb = Workbook()
ws = wb.active
dv = DataValidation(type="list", formula1='"Item1,Item2,Item3"', allow_blank=True)
ws.add_data_validation(dv)
dv.add('A1')
ws['A1'] = 'Item1'
wb.save('data_validation.xlsx')
The library supports the creation and customization of various chart types such as line, bar, pie, and scatter plots. This feature enables the visualization of data directly within the Excel sheets, facilitating better data analysis and reporting. We denote the chart plot row and select the reference of values to show in the chart.
from openpyxl import Workbook
from openpyxl.chart import BarChart, Reference
wb = Workbook()
ws = wb.active
for i in range(10):
ws.append([i])
values = Reference(ws, min_col=1, min_row=1, max_col=1, max_row=10)
chart = BarChart()
chart.add_data(values)
ws.add_chart(chart, "E5")
wb.save("chart.xlsx")
OpenPyXL provides the ability to merge multiple cells to create a single larger cell, which is often useful for formatting titles or organizing data. It also allows these cells to be unmerged or split back into individual cells if needed.
You can read and write comments to cells, allowing for additional notes or instructions to be included within the Excel file. This feature is particularly useful for collaborative projects where notes and guidelines may need to be shared between users.
from openpyxl import Workbook
from openpyxl.comments import Comment
wb = Workbook()
ws = wb.active
comment = Comment(text="This is a comment", author="Author")
ws['A1'].comment = comment
wb.save('comments.xlsx')The library offers the capability to create pivot tables from data within your worksheets. This powerful tool helps summarize, analyze, explore, and present your data comprehensively, making it easier to draw conclusions and make informed decisions based on large data sets.
Documentation: IronXL provides extensive documentation for Python developers. It covers a broad range of topics including how to read, write, and edit Excel documents, manage workbooks, style cells, and sort data programmatically. The documentation is designed to be straightforward, supplemented with numerous code examples to assist developers in quickly integrating IronXL functionalities into their applications.
Support: IronXL offers robust support options. Developers can access support through a comprehensive FAQ section, detailed documentation online, and direct email assistance. For more complex inquiries or immediate assistance, there is an option to contact their engineering team directly.
Documentation: OpenPyXL's documentation is thorough and well-organized, offering developers guides on everything from getting started to advanced features like charts, data validation with error message, and conditional formatting. The documentation includes detailed explanations and code snippets which are helpful for both new and experienced users.
Support: OpenPyXL provides support mainly through community forums and issue trackers on their official repositories. Developers can post issues and contribute to the library on GitHub, making it a collaborative and continually evolving tool.
IronXL offers several perpetual license options tailored to different project needs, all ensuring that developers can use the library in various environments without ongoing costs.
Additional options include:
Each license also includes a 30-day money-back guarantee, allowing developers to test IronXL risk-free. These licensing terms make IronXL a flexible option for projects of various scopes and sizes, providing clear pathways for implementation in different professional settings.
OpenPyXL is distributed under the MIT license, which is a permissive free software license. This means that it allows for considerable freedom in usage for both open-source and proprietary software. The MIT license permits reuse within proprietary software provided that all copies of the licensed software include a copy of the MIT License terms and the copyright notice.
In our review of IronXL and OpenPyXL, we've looked at their features, usability, support options, and licensing. Both libraries are excellent for managing Excel files, but they cater to different needs.
IronXL stands out due to its extensive features that allow complex Excel manipulations without needing Microsoft Office. It offers detailed documentation and strong technical support, making it a reliable choice for larger projects or enterprises. Data scientists use IronXL for multiple purposes like data copying, data mining, data analysis, and many other tasks. IronXL licenses start at $749, but it includes a free trial. This trial lets developers fully test IronXL's capabilities before making a purchase, which is a major advantage for any serious project.
OpenPyXL is also powerful, especially with its advanced Excel features like charts such as line chart and conditional formatting. It is available under the MIT license, which offers flexibility for both open-source and commercial use. Its support is community-driven, which is great, but not have the structured support that IronXL provides.
If your project needs high-level features along with professional and ongoing support, IronXL is the better choice. It might cost initially, but the investment is worthwhile for the benefits and security it offers, especially for complex or long-term projects.
pip install IronXL-2024.11-py37-none-win_amd64.whiWant to deploy IronXL to a live project for FREE?
Your trial key should be in the email.
The trial form was submitted
successfully.
If it is not, please contact
support@ironsoftware.com
Get started for FREE
No credit card required
Test in production without watermarks.
Works wherever you need it to.
Get 30 days of fully functional product.
Have it up and running in minutes.
Full access to our support engineering team during your product trial
No credit card or account creation required
Your trial key should be in the email.
If it is not, please contact
support@ironsoftware.com
Get started for FREE
No credit card required
Test in production without watermarks.
Works wherever you need it to.
Get 30 days of fully functional product.
Have it up and running in minutes.
Full access to our support engineering team during your product trial
Licenses from $749. Have a question? Get in touch.
9 .NET API products for your office documents
