The Python Excel Library

 Read Excel Files without Interop
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")

IronXL for Python Blog

April 4, 2024

How to read an Excel file in Python using Visual Studio Code

This post teaches you how to read Excel files in Python using Visual Studio code

April 4, 2024

How to Export to Excel in Python

whether you're exporting data to a Python data frame or adding new data to an existing file.

April 4, 2024

Pandas Read Excel Alternatives (Without Using Interop) | IronXL Python

In this article, we'll compare the functionality and performance of Pandas and IronXL to Read Excel files in Python.

March 7, 2024

How to Write An Excel file in Python

We'll examine its features, usability, and performance advantages while presenting useful code samples to highlight its powers. Also, Using Python to write Excel files with various open-source libraries

March 7, 2024

How to use Python to Read Excel Files

Python has a robust library environment that makes it possible to read and manipulate Excel files with ease. This post will examine how to use Python to read Excel files.