Working with XlsxWriter Module - Python
Last Updated :
28 Apr, 2025
XlsxWriter is a Python module that provides various methods to work with Excel using Python. It can be used to read, write, applying formulas. Also, it supports features such as formatting, images, charts, page setup, auto filters, conditional formatting and many others.
This tutorial aims at providing knowledge about the XlsxWriter module from basics to advance with the help well explained examples and concepts.
Installation
Before we jump into the code, we need to install module using this command:
pip install XlsxWriter
Creating and Writing to Excel
Let's begin with a simple example where we create an Excel file and write some data into it.
Example:
Python
import xlsxwriter
workbook = xlsxwriter.Workbook('sample.xlsx')
worksheet = workbook.add_worksheet()
worksheet.write('A1', 'Hello..')
worksheet.write('B1', 'Geeks')
worksheet.write('C1', 'For')
worksheet.write('D1', 'Geeks')
workbook.close()
Output:
Explanation:
- Workbook('sample.xlsx') creates a new Excel file named sample.xlsx.
- add_worksheet() adds a new sheet to the workbook.
- write(cell, data) writes data to specific cells. You can refer to cells by labels like 'A1', 'B1' or using row-column indices like (0, 0) for A1.
Note: In XlsxWriter, both rows and columns are zero-indexed — meaning the first row and column start at 0.
Writing to Rows and Columns
We can also write a list of values directly to a row or colum
Example:
Python
import xlsxwriter
workbook = xlsxwriter.Workbook('sample.xlsx')
worksheet = workbook.add_worksheet()
content = ["Welcome", "to", "Geeks", "for", "Geeks"]
# Writing data to a column manually
row = 0
col = 0
for item in content:
worksheet.write(row, col, item)
row += 1
workbook.close()
Output:
Or, we can do it more efficiently using built-in methods:
Example using write_row() and write_column():
Python
import xlsxwriter
workbook = xlsxwriter.Workbook('sample.xlsx')
worksheet = workbook.add_worksheet()
content = ["Welcome", "to", "Geeks", "for", "Geeks"]
# Write list to a row
worksheet.write_row(0, 1, content)
# Write list to a column
worksheet.write_column(1, 0, content)
workbook.close()
Output:
Explanation:
- write_row(row, col, data_list): Writes an entire list horizontally starting at the given (row, column).
- write_column(row, col, data_list): Writes the list vertically starting at the given (row, column).
XlsxWriter module provides the write_formula() and write_array_formula() methods to directly write the formulas in Excel.
- write_formula() method is used to directly write the formula to a worksheet cell
- write_array_formula() method is used to write an array formula to a worksheet cell. Array formula in Excel is that formula that performs on a set of values.
Python
import xlsxwriter
workbook = xlsxwriter.Workbook('sample.xlsx')
worksheet = workbook.add_worksheet()
content = [1, 2]
worksheet.write_row(0, 1, content)
worksheet.write_column(1, 0, content)
# Writing formulas
worksheet.write_formula('A4', '=SUM(A2, A3)')
worksheet.write_formula('D1', '=PRODUCT(B1, C1)')
workbook.close()
Output:
Python
import xlsxwriter
workbook = xlsxwriter.Workbook('sample.xlsx')
worksheet = workbook.add_worksheet()
content = [1, 2, 3, 4, 5]
worksheet.write_row(0, 1, content)
worksheet.write_column(1, 0, content)
# Using array formulas
worksheet.write_array_formula('A7', '{=SUM(A1:A6)}')
worksheet.write_array_formula('G1', '{=PRODUCT(B1:F1)}')
workbook.close()
Output:
Explanation:
- write_formula(cell, formula): Inserts a regular Excel formula.
- write_array_formula(start_row, start_col, end_row, end_col, formula): Inserts an array formula that works on ranges of cells.
Adding Charts
XlsxWriter provides a class Chart that acts as a base class for implementing charts. The chart object is created using the add_chart() method. This method also specifies the type of the chart. After creating the chart, the chart is added to the specified cell using the insert_chart() method or it can be set using the set_chart() method.
Example 1: Adding a Column Chart
Python
import xlsxwriter
workbook = xlsxwriter.Workbook('sample.xlsx')
worksheet = workbook.add_worksheet()
content = [1, 2, 3, 5, 3, 2, 2]
worksheet.write_column(0, 0, content)
# Create a column chart
chart = workbook.add_chart({'type': 'column'})
# Define the data series
chart.add_series({'values': '=Sheet1!$A$1:$A$7'})
# Insert chart into the worksheet
worksheet.insert_chart('C1', chart)
workbook.close()
Output:
Example 2: Line Chart with Diamond Markers
Python
import xlsxwriter
workbook = xlsxwriter.Workbook('sample.xlsx')
worksheet = workbook.add_worksheet()
content = [1, 2, 3, 5, 3, 2, 2]
worksheet.write_column(0, 0, content)
# Create a line chart
chart = workbook.add_chart({'type': 'line'})
# Add a series with diamond-shaped points
chart.add_series({
'values': '=Sheet1!$A$1:$A$7',
'marker': {'type': 'diamond'},
})
worksheet.insert_chart('C1', chart)
workbook.close()
Output:
Explanation:
- add_chart({'type': 'chart_type'}): Creates a chart object (like column, line, pie, etc.).
- add_series({'values': 'range'}): Defines what data the chart will use.
- insert_chart(row, col, chart): Embeds the chart at a specified position inside the worksheet.
Adding Tables
Tables can be added using the add_table() method. The data parameter of the table is used to specify the data for the cells of the table.. The header_row parameter is used to turn off or on the header row to the table.
Example: Adding a Table
Python
import xlsxwriter
# Create a new workbook and worksheet
workbook = xlsxwriter.Workbook('sample.xlsx')
worksheet = workbook.add_worksheet()
# Define the data
data = [
[1, 2, 3],
[4, 5, 6],
[7, 8, 9],
[10, 11, 12],
]
# Add the table
worksheet.add_table('B2:D5', {'data': data})
# Close the workbook
workbook.close()
Output:
Working with Pandas and XlsxWriter
Pandas can write Excel files using XlsxWriter or OpenPyxl as the engine. This allows you to handle both small and large datasets easily.
Example 1: Writing a Single DataFrame
Python
import pandas as pd
# Create a dataframe
df = pd.DataFrame({'Data': ['Geeks', 'For', 'geeks', 'is', 'portal', 'for', 'geeks']})
# Write the dataframe to an Excel file
with pd.ExcelWriter('sample.xlsx', engine='xlsxwriter') as writer:
df.to_excel(writer, sheet_name='Sheet1')
Output:
Example 2: Writing to multiple dataframes.
Python
import pandas as pd
# Create multiple dataframes
df1 = pd.DataFrame({'Data': [11, 12, 13, 14]})
df2 = pd.DataFrame({'Data': [21, 22, 23, 24]})
df3 = pd.DataFrame({'Data': [31, 32, 33, 34]})
df4 = pd.DataFrame({'Data': [41, 42, 43, 44]})
# Write to different positions in the same sheet
with pd.ExcelWriter('sample.xlsx', engine='xlsxwriter') as writer:
df1.to_excel(writer, sheet_name='Sheet1')
df2.to_excel(writer, sheet_name='Sheet1', startcol=3)
df3.to_excel(writer, sheet_name='Sheet1', startrow=6)
df4.to_excel(writer, sheet_name='Sheet1', startrow=7, startcol=4, header=False, index=False)
Output:
Python
import pandas as pd
from datetime import datetime, date
# Create dataframe with datetime and date columns
df = pd.DataFrame({
'Date and time': [
datetime(2018, 1, 11, 11, 30, 55),
datetime(2018, 2, 12, 1, 20, 33),
datetime(2018, 3, 13, 11, 10),
datetime(2018, 4, 14, 16, 45, 35),
datetime(2018, 5, 15, 12, 10, 15),
],
'Dates only': [
date(2018, 6, 21),
date(2018, 7, 22),
date(2018, 8, 23),
date(2018, 9, 24),
date(2018, 10, 25),
],
})
# Write with datetime formats
with pd.ExcelWriter('sample.xlsx', engine='xlsxwriter',
datetime_format='mmm d yyyy hh:mm:ss',
date_format='mmmm dd yyyy') as writer:
df.to_excel(writer, sheet_name='Sheet1')
worksheet = writer.sheets['Sheet1']
worksheet.set_column('B:C', 20)
Output:
Python
import pandas as pd
# Define data
data1 = ["Math", "Physics", "Computer", "Hindi", "English", "Chemistry"]
data2 = [95, 78, 80, 80, 60, 95]
data3 = [90, 67, 78, 70, 63, 90]
df = pd.DataFrame({
'Subject': data1,
'Mid Term Exam Scores': data2,
'End Term Exam Scores': data3,
})
# Write dataframe with custom header style
with pd.ExcelWriter('sample.xlsx', engine='xlsxwriter') as writer:
df.to_excel(writer, sheet_name='Sheet1', startrow=1, header=False)
workbook = writer.book
worksheet = writer.sheets['Sheet1']
header_format = workbook.add_format({
'bold': True,
'italic': True,
'text_wrap': True,
'valign': 'top',
'font_color': 'green',
'border': 2,
})
for col_num, value in enumerate(df.columns.values):
worksheet.write(0, col_num + 1, value, header_format)
Output:
Creating Charts using Pandas and XlsxWriter
Till now we have seen different methods for adding the data to the Excel files using the Pandas and the XlsxWriter module. Now after the data is inserted we can simply create the charts using the add_chart() method as we have seen above.
Example: Adding a Column Chart
Python
import pandas as pd
df = pd.DataFrame({
'Subject': ["Math", "Physics", "Computer", "Hindi", "English", "Chemistry"],
'Mid Exam Score': [90, 78, 60, 80, 60, 90],
'End Exam Score': [45, 39, 30, 40, 30, 60],
})
with pd.ExcelWriter('sample.xlsx', engine='xlsxwriter') as writer:
df.to_excel(writer, sheet_name='Sheet1')
workbook = writer.book
worksheet = writer.sheets['Sheet1']
worksheet.set_column('B:D', 20)
chart = workbook.add_chart({'type': 'column'})
# Configure series
chart.add_series({
'name': ['Sheet1', 0, 2],
'categories': ['Sheet1', 1, 1, 6, 1],
'values': ['Sheet1', 1, 2, 6, 2],
})
chart.add_series({
'name': ['Sheet1', 0, 3],
'categories': ['Sheet1', 1, 1, 6, 1],
'values': ['Sheet1', 1, 3, 6, 3],
})
chart.set_title({'name': 'Exam Score Distribution'})
chart.set_x_axis({'name': 'Subjects'})
chart.set_y_axis({'name': 'Marks'})
worksheet.insert_chart('F2', chart, {'x_offset': 25, 'y_offset': 10})
Output:
Relate articles: