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()
Create Excel Files
from ironxl import *
# Create new Excel WorkBook document
workbook = WorkBook.Create()
# Convert XLSX to XLS
xlsWorkBook = WorkBook.Create(ExcelFileFormat.XLS)
# Create a blank WorkSheet
worksheet = workbook.CreateWorkSheet("new_sheet")
# Add data and styles to the new worksheet
worksheet["A1"].Value = "Hello World"
worksheet["A1"].Style.WrapText = True
worksheet["A2"].BoolValue = True
worksheet["A2"].Style.BottomBorder.Type = BorderType.Double
# Save the excel file as XLS, XLSX, CSV, TSV, JSON, XML, HTML and streams
workbook.SaveAs("test.xlsx")
Convert Spreadsheet File Types
from ironxl import *
# Import any XLSX, XLS, XLSM, XLTX, CSV, and TSV
workbook = WorkBook.Load("sample.xlsx")
# Export the excel file as XLS, XLSX, XLSM, CSV, TSV, JSON, XML
workbook.SaveAs("sample.xls")
workbook.SaveAs("sample.xlsx")
workbook.SaveAs("sample.tsv")
workbook.SaveAsCsv("sample.csv")
workbook.SaveAsJson("sample.json")
workbook.SaveAsXml("sample.xml")
# Export the excel file as HTML, HTML string
workbook.ExportToHtml("sample.html")
html_string = workbook.ExportToHtmlString()
# Export the excel file as Binary, Byte array, Data set, Stream
binary_data = workbook.ToBinary()
byte_array_data = workbook.ToByteArray()
Excel Conditional Formatting
from ironxl import *
# Load existing spreadsheet
workbook = WorkBook.Load("sample.xlsx")
worksheet = workbook.DefaultWorkSheet
# Create conditional formatting rule
rule = worksheet.ConditionalFormatting.CreateConditionalFormattingRule(ComparisonOperator.LessThan, "8")
# Set style options
rule.FontFormatting.IsBold = True
rule.FontFormatting.FontColor = "#123456"
rule.BorderFormatting.RightBorderColor = "#ffffff"
rule.BorderFormatting.RightBorderType = BorderType.Thick
rule.PatternFormatting.BackgroundColor = "#54bdd9"
rule.PatternFormatting.FillPattern = FillPattern.Diamonds
# Apply formatting on the specified region
worksheet.ConditionalFormatting.AddConditionalFormatting("A3:A8", rule)
# Create another conditional formatting rule
rule1 = worksheet.ConditionalFormatting.CreateConditionalFormattingRule(ComparisonOperator.Between, "7", "10")
# Set style options
rule1.FontFormatting.IsItalic = True
rule1.FontFormatting.UnderlineType = FontUnderlineType.Single
# Apply formatting on the specified region
worksheet.ConditionalFormatting.AddConditionalFormatting("A3:A9", rule1)
# Save changes with applied conditional formatting
workbook.SaveAs("applyConditionalFormatting.xlsx")
Protect Excel Files
from ironxl import *
# Load the Excel workbook
workbook = WorkBook.Load("sample.xlsx")
worksheet = workbook.DefaultWorkSheet
# Open a protected spreadsheet file
protected_workbook = WorkBook.Load("sample.xlsx", "IronSoftware")
# Spreadsheet protection
# Set protection for the spreadsheet file
workbook.Encrypt("IronSoftware")
# Remove protection for the spreadsheet file. Original password is not required.
workbook.Password = None
workbook.Save()
# Worksheet protection
# Set protection for an individual worksheet
worksheet.ProtectSheet("IronXL")
# Remove protection for a particular worksheet. It works without a password!
worksheet.UnprotectSheet()
workbook.Save()
Excel Formulas in C#
from ironxl import *
# Load the Excel workbook
workbook = WorkBook.Load("sample.xlsx")
worksheet = workbook.DefaultWorkSheet
# Set Formulas
worksheet["A1"].Formula = "Sum(B8:C12)"
worksheet["B8"].Formula = "=C9/C11"
worksheet["G30"].Formula = "Max(C3:C7)"
# Force recalculate all formula values in all sheets.
workbook.EvaluateAll()
# Get the formula's calculated value. e.g. "52"
formula_value = worksheet["G30"].ToArray()[0].FormattedCellValue
# Get the formula as a string. e.g. "Max(C3:C7)"
formula_string = worksheet["G30"].Formula
# Save changes with updated formulas and calculated values.
workbook.Save()
Edit Excel Metadata in C#
from ironxl import *
# Load the Excel workbook
workbook = WorkBook.Load("sample.xlsx")
# Set author
workbook.Metadata.Author = "Your Name"
# Set comments
workbook.Metadata.Comments = "Monthly report"
# Set title
workbook.Metadata.Title = "July"
# Set keywords
workbook.Metadata.Keywords = "Report"
# Read the creation date of the Excel file
creation_date = workbook.Metadata.Created
# Read the last printed date of the Excel file
print_date = workbook.Metadata.LastPrinted
# Save the workbook with edited metadata
workbook.SaveAs("editedMetadata.xlsx")
Style Excel Cell Borders & Fonts
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")
Sort Excel Ranges in C#
from ironxl import *
# Load the Excel workbook
workbook = WorkBook.Load("sample.xlsx")
worksheet = workbook.DefaultWorkSheet
# Select a range
selected_range = worksheet["A1:D20"]
# Select a column(B)
column = worksheet.GetColumn(1)
# Sort the range in ascending order (A to Z)
selected_range.SortAscending()
# Sort the range by column(C) in ascending order
selected_range.SortByColumn("C", SortOrder.Ascending)
# Sort the column(B) in descending order (Z to A)
column.SortDescending()
# Save changes with the sorted range and column
workbook.SaveAs("sortExcelRange.xlsx")
Repeat Excel Rows & Columns
from ironxl import *
# Load existing spreadsheet
workbook = WorkBook.Load("sample.xlsx")
worksheet = workbook.DefaultWorkSheet
# Set repeating rows for row(2-4)
worksheet.SetRepeatingRows(1, 3)
# Set repeating columns for column(C-D)
worksheet.SetRepeatingColumns(2, 3)
# Set column break after column(H). Hence, the first page will only contain column(A-G)
worksheet.SetColumnBreak(7)
# Save changes with repeating rows and columns
workbook.SaveAs("repeatingRows.xlsx")
Excel Print Setup
from ironxl import *
# Load the Excel workbook
workbook = WorkBook.Load("sample.xlsx")
worksheet = workbook.DefaultWorkSheet
# Set the print header and footer of the worksheet
worksheet.Header.Center = "My document"
worksheet.Footer.Center = "Page &P of &N"
# Set the header margin
worksheet.PrintSetup.HeaderMargin = 2.33
# Set the size of the paper
# Paper size enum represents different sizes of paper
worksheet.PrintSetup.PaperSize = PaperSize.B4
# Set the print orientation of the worksheet
worksheet.PrintSetup.PrintOrientation = PrintOrientation.Portrait
# Set black and white printing
worksheet.PrintSetup.NoColor = True
workbook.SaveAs("PrintSetup.xlsx")
Cell Data Display Format
from ironxl import *
# Load an existing WorkSheet
workbook = WorkBook.Load("sample.xls")
worksheet = workbook.WorkSheets[0]
# Set data display format to range
worksheet["A3"].Value = DateTime(2020, 1, 1, 12, 12, 12)
worksheet["A4"].Value = DateTime(2022, 3, 3, 10, 10, 10)
worksheet["A5"].Value = DateTime(2021, 2, 2, 11, 11, 11)
selected_range = worksheet["A3:A5"]
# The cell(A3) value will look like 1/1/2020 12:12:12 PM
selected_range.FormatString = "MM/dd/yy h:mm:ss"
# Save changes with applied number formats
workbook.SaveAs("numberFormats.xls")
Combine Excel Ranges
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["A2:A8"]
# Combine two ranges
combined_range = selected_range + worksheet["A9:A10"]
# Iterate over the combined range
for cell in combined_range:
print(cell.Value)
Select Excel Range
from ironxl import *
# Load the Excel workbook
workbook = WorkBook.Load("sample.xls")
worksheet = workbook.WorkSheets[0]
# Get range from worksheet
selected_range = worksheet["A2:A8"]
# Get column from worksheet
column_a = worksheet.GetColumn(0)
# Get row from worksheet
row_1 = worksheet.GetRow(0)
# Iterate over the range
for cell in selected_range:
print(f"{cell.Value}")
Convert Excel to HTML
from ironxl import *
# Load the Excel workbook
workbook = WorkBook.Load("sample.xlsx")
# Set up HTML export options
options = HtmlExportOptions()
options.OutputRowNumbers = True
options.OutputColumnHeaders = True
options.OutputHiddenRows = True
options.OutputHiddenColumns = True
options.OutputLeadingSpacesAsNonBreaking = True
# Export workbook to HTML file
workbook.ExportToHtml("workBook.html", options)
Aggregate Excel Functions
from ironxl import *
# Load existing spreadsheet
workbook = WorkBook.Load("sample.xls")
worksheet = workbook.WorkSheets[0]
# Get range from the worksheet
selected_range = worksheet["A1:A8"]
# Apply sum of all numeric cells within the range
sum_ = selected_range.Sum()
# Apply average value of all numeric cells within the range
avg = selected_range.Avg()
# Identify maximum value of all numeric cells within the range
max_ = selected_range.Max()
# Identify minimum value of all numeric cells within the range
min_ = selected_range.Min()
Create Excel Line Chart
from ironxl import *
# Load the Excel workbook
workbook = WorkBook.Load("test.xlsx")
worksheet = workbook.DefaultWorkSheet
# Set the chart type and its position on the worksheet.
chart = worksheet.CreateChart(ChartType.Line, 10, 10, 18, 20)
# Add the series to the chart
# The first parameter represents the address of the range for the horizontal(category) axis.
# The second parameter represents the address of the range for the vertical(value) axis.
series = chart.AddSeries("B3:B8", "A3:A8")
# Set the chart title.
series.Title = "Line Chart"
# Set the legend position.
# Can be removed by setting it to None.
chart.SetLegendPosition(LegendPosition.Bottom)
# We can change the position of the chart.
chart.Position.LeftColumnIndex = 2
chart.Position.RightColumnIndex = chart.Position.LeftColumnIndex + 3
# Plot all the data that was added to the chart before.
# Multiple calls to this method lead to plotting multiple charts instead of modifying the existing chart.
# Yet there is no possibility to remove a chart or edit its series/position.
# We can just create a new one.
chart.Plot()
# Save changes with the created line chart
workbook.SaveAs("CreateLineChart.xlsx")
Freeze Panes in Excel
from ironxl import *
# Load the Excel workbook
workbook = WorkBook.Load("sample.xls")
worksheet = workbook.WorkSheets[0]
# Create freeze pane from column(A-B) and row(1-3)
worksheet.CreateFreezePane(2, 3)
# Overwriting freeze or split pane to column(A-E) and row(1-5) as well as applying prescroll
# The column will show E, G, ... and the row will show 5, 8, ...
worksheet.CreateFreezePane(5, 5, 6, 7)
# Save changes with the created freeze panes
workbook.SaveAs("createFreezePanes.xls")
# Remove all existing freeze or split pane
worksheet.RemovePane()
Copy Excel Worksheets
from ironxl import *
# Load the first Excel workbook
first_book = WorkBook.Load("sample.xlsx")
second_book = WorkBook.Create()
# Select the first worksheet in the workbook
worksheet = first_book.DefaultWorkSheet
# 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")
# Save changes in the first workbook
first_book.Save()
# Save the second workbook with the copied worksheet
second_book.SaveAs("copyExcelWorksheet.xlsx")
Python Package For Excel Files (Without Using Interop)
Updated June 6, 2024
Share:
In today's data-driven world, the ability to efficiently work with Microsoft Excel files is a crucial skill for professionals across various domains. Python, with its robust ecosystem of libraries and Python modules, offers a powerful platform for handling Excel data and writing Excel files. There are many open-source and paid Python packages available for working with Excel(XLSX) files.
Python libraries for Excel files (XLSX files) offer a vast array of functionalities, from writing Excel files to reading existing Excel files and data analysis, empowering developers to streamline their projects with ease. One such standout Python library is IronXL, which provides a seamless interface for creating, reading, and manipulating Excel files.
In this tutorial, we'll explore the features of IronXL and demonstrate how to integrate it into your Python projects.
IronXL - The Python Excel Library
IronXL for Python is a versatile library that simplifies Excel Spreadsheet operations within Python projects. Its intuitive API allows developers to perform a wide range of tasks, from creating and editing Excel worksheets to extracting and analyzing data.
Here are some key features of IronXL.
Excel Workbook Operations
Create Workbooks: Generate new Excel workbooks from scratch.
Load Existing Workbooks: Access and manipulate pre-existing Excel files.
Save Workbooks: Store modifications made to workbooks persistently.
Worksheet Management
Create Worksheets: Add new worksheets to Excel workbooks.
Access Worksheets: Retrieve and modify data within specific worksheets.
Delete Worksheets: Remove unnecessary worksheets from workbooks.
Data Manipulation
Read Data: Extract information from Excel cells, ranges, and worksheets.
Write Data: Input data into Excel cells, ranges, and worksheets.
Update Data: Modify existing data within Excel files.
File Format Support
XLSX, XLS, and CSV: Full support for Excel file formats, including XLSX, XLS, and CSV.
Cross-Compatibility: Seamlessly work with Excel files across different platforms and environments.
Advanced Functionalities
Formula Management: Handle Excel formulas dynamically, including creation, modification, and calculation.
Cell Formatting: Customize cell formatting options such as font styles, colors, and alignment.
Range Operations: Perform operations on ranges of cells, including sorting, filtering, and copying.
Data Analysis Capabilities
Data Extraction: Extract meaningful insights from Excel data using Python.
Data Visualization: Generate charts and graphs directly from Excel data for visualization and analysis purposes.
Statistical Analysis: Conduct statistical analysis on Excel datasets with ease.
Integration with Python Ecosystem
Seamless Integration: Integrate IronXL seamlessly into Python projects without any compatibility issues.
Interoperability: Leverage IronXL alongside other Python libraries and frameworks for comprehensive data processing tasks.
Ease of Use
Intuitive API: Simple and straightforward API for easy integration and usage.
Documentation: Comprehensive documentation and examples are provided for quick learning and reference.
Performance and Scalability
Efficient Processing: Optimized performance for handling large Excel datasets efficiently.
Scalability: Scale operations seamlessly to accommodate growing datasets and processing requirements.
Cross-Platform Compatibility
Windows, macOS, Linux: Compatible with various operating systems, including Windows, macOS, and Linux.
Cloud Platforms: Deploy and run IronXL on cloud platforms for flexible and scalable solutions.
Community Support and Updates
Active Community: Engage with a vibrant community of users and developers for support and collaboration.
Regular Updates: Receive timely updates and enhancements to ensure continued compatibility and performance improvements.
Prerequisites
Before moving forward, ensure you have the following prerequisites installed on your system:
PyCharm: Download and install PyCharm, a popular Python IDE, to streamline your development workflow.
IronXL Library: Install IronXL using PIP, Python's package manager, by running the following command in your terminal or command prompt:
pip install IronXL
pip install IronXL
PYTHON
.NET 6.0 SDK: IronXL Python is built upon the IronXL .NET library, particularly relying on .NET 6.0. To effectively utilize IronXL Python, it's necessary to have the .NET 6.0 Software Development Kit (SDK) installed on your machine. This SDK provides the necessary components for running IronXL Python without any errors. You can download it from here: https://dotnet.microsoft.com/en-us/download/dotnet/6.0.
Create Python Project in PyCharm
Launch PyCharm and create a new Python project by selecting "New Project".
Choose a name and location for your project, then click "Create."
Once your project is created, you can start writing Python code to interact with Excel files using IronXL.
Install IronXL using PIP
In your PyCharm project, open a terminal window and execute the following command to install IronXL:
pip install IronXL
pip install IronXL
PYTHON
This will download and install the IronXL library, allowing you to use its functionalities in your Python code.
Create Excel File
Now, let's create a new Excel file using IronXL. Below is a sample Python code snippet to create an Excel file:
from ironxl import *
# Create a new Excel file
workbook = WorkBook.Create()
# Add a worksheet
worksheet = workbook.CreateWorkSheet("Sheet1")
# Write data to cells
worksheet["A1"].Value = "Hello"
worksheet["B1"].Value = "World"
# Save the workbook
workbook.SaveAs("example.xlsx")
from ironxl import *
# Create a new Excel file
workbook = WorkBook.Create()
# Add a worksheet
worksheet = workbook.CreateWorkSheet("Sheet1")
# Write data to cells
worksheet["A1"].Value = "Hello"
worksheet["B1"].Value = "World"
# Save the workbook
workbook.SaveAs("example.xlsx")
PYTHON
This code creates a new Excel workbook with a single worksheet named "Sheet1" and writes "Hello" and "World" to cells A1 and B1, respectively. Finally, it saves the workbook as "example.xlsx" in the current directory.
The above screenshot shows how efficiently we can create and write Excel files from scratch in Python using the IronXL Python module.
Read Excel Files
To read data from an existing Excel file, you can use IronXL's WorkBook.Load() method. Here's a simple example of how to read Excel files:
from ironxl import WorkBook
# Load an existing Excel workbook
workbook = WorkBook.Load("example.xlsx")
# Access a worksheet
worksheet = workbook.sheets[0]
# Read data from cells
data = worksheet["A1"].value
print(data) # Output: Hello
from ironxl import WorkBook
# Load an existing Excel workbook
workbook = WorkBook.Load("example.xlsx")
# Access a worksheet
worksheet = workbook.sheets[0]
# Read data from cells
data = worksheet["A1"].value
print(data) # Output: Hello
PYTHON
This code loads the "example.xlsx" file, accesses the first worksheet, and reads the value from cell A1, which is then printed to the console.
For more detailed information on IronXL and its capabilities, please visit the documentation and code examples page.
Conclusion
IronXL for Python is a versatile library that simplifies Excel file manipulation in Python projects. By following this tutorial, we've learned how to create, read, and manipulate Excel files using IronXL. With its intuitive API and powerful features, IronXL opens up endless possibilities for working with Excel data in Python.
Start incorporating IronXL into your Python projects with its free trial for commercial use today and unleash the full potential of Excel manipulation with ease and efficiency.
< PREVIOUS How to Merge Cells in an Excel File Using Python