Open In App

Working with XlsxWriter Module – Python

Last Updated : 28 Apr, 2025
Comments
Improve
Suggest changes
Like Article
Like
Report

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

python xlsxwriter install

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:

create workbook xlsxwriter

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:

writing to row or column xlsxwriter python

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:

writing to row or column xlsxwriter python

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).

Working with Formulas

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.

Example 1: Using the write_formula() method

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:

xlsxwriter write formula python

Example 2: Using the write_array_formula() method

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:

array sum xlsxwriter Python

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:

xlsxwriter add chart python

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:

xslxwriter python add chart

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:

xlsxwriter table python

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:

pandas and xlsxwriter 1

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:

xlsxwriter and pandas writing multiple dataframe

Example 3: Formatting Datetime Columns

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:

datetime xlsxwriter and pandas

Example 4: Adding a Custom Header Format

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:

xlsxwriter and pandas user fromat

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:

xlsaxwriter and pandas add chart

Relate articles:



Next Article
Working with Excel Spreadsheets in Python
author
abhishek1
Improve
Article Tags :
Practice Tags :

Similar Reads

We use cookies to ensure you have the best browsing experience on our website. By using our site, you acknowledge that you have read and understood our Cookie Policy & Privacy Policy
Lightbox