1. Products
  2.   Spreadsheet
  3.   Python
  4.   XlsxWriter

Open Source Python API for Google Sheets

Write Excel XML Spreadsheet Files via Open Source Python Library.

XlsxWriter is an open source python API for writing files in the Excel 2007+ XLSX file format. Using the API you can write text, formulas, numbers, and hyperlinks into multiple worksheets. Furthermore, the API allows insertion charts, merger cells, formats cells, apply filters, validation data, insertion of PNG/JPEG/BMP/WMF/EMF images, use of rich multi-format strings, and more.

XlsxWriter claims to provide more Excel Features than any of the alternative python modules. The API provides a high rate of accuracy while create new Excel files, in most cases the files produced using XlsxWriter are 100% equivalent to files produced by Excel.

Previous Next

Getting Started with XlsxWriter

You need to have Python 2.7 or higher installed on your system to run XlsxWriter smoothly. The recommended way to install via PIP. Please use the following command.

Install XlsxWriter via PIP Command

pip install XlsxWriter

Create Spreadsheet via Python Library

XlsxWriter API allows the creation of Microsoft Spreadsheets using Python and the XlsxWriter module. It allows software programmers to create a blank spreadsheet using XlsxWriter.Workbook() method. You can add worksheets to your workbook using workbook.add_worksheet() method. After adding the worksheets, the API allows a wide range of free features to work with excel sheets using Python.

Create & Modify Spreadsheet via Python Library<

import xlsxwriter

# Create an new Excel file and add a worksheet.
workbook = xlsxwriter.Workbook('demo.xlsx')
worksheet = workbook.add_worksheet()

# Widen the first column to make the text clearer.
worksheet.set_column('A:A', 20)

# Add a bold format to use to highlight cells.
bold = workbook.add_format({'bold': True})

# Write some simple text.
worksheet.write('A1', 'Hello')

# Text with formatting.
worksheet.write('A2', 'World', bold)

# Write some numbers, with row/column notation.
worksheet.write(2, 0, 123)
worksheet.write(3, 0, 123.456)


Add Charts in XLSX using Python

The Open Source spreadsheet library XlsxWriter allows software developers to add charts in XLSX file format using just a few lines of code. After creating your new worksheet in excel, you can add a chart by using workbook.add_chart() method. Using the Python API, you can add area charts, bar charts, column charts, line charts, pie charts, doughnut charts, scatter charts, stock charts, and radar charts for free.

Add Bar Chart to XLSX Spreadsheet via Python Library<

import xlsxwriter

workbook = xlsxwriter.Workbook('chart_bar.xlsx')
worksheet = workbook.add_worksheet()
bold = workbook.add_format({'bold': 1})

# Add the worksheet data that the charts will refer to.
headings = ['Number', 'Batch 1', 'Batch 2']
data = [
    [2, 3, 4, 5, 6, 7],
    [10, 40, 50, 20, 10, 50],
    [30, 60, 70, 50, 40, 30],

worksheet.write_row('A1', headings, bold)
worksheet.write_column('A2', data[0])
worksheet.write_column('B2', data[1])
worksheet.write_column('C2', data[2])

# Create a new bar chart.

chart1 = workbook.add_chart({'type': 'bar'})

# Configure the first series.
    'name':       '=Sheet1!$B$1',
    'categories': '=Sheet1!$A$2:$A$7',
    'values':     '=Sheet1!$B$2:$B$7',

Work with Excel Formulas using Python

XlsxWriter library gives developers the capability to write formulas inside Microsoft Excel file format programmatically. You can simply add a formula to your file using worksheet.write_forumula() method. Excel stores formulas in the format of the US English version, so the formulas must be in the US English format.

Add Array Formula via Python Library<

import xlsxwriter

# Create a new workbook and add a worksheet
workbook = xlsxwriter.Workbook('array_formula.xlsx')
worksheet = workbook.add_worksheet()

# Write some test data.
worksheet.write('B1', 500)
worksheet.write('B2', 10)
worksheet.write('B5', 1)
worksheet.write('B6', 2)
worksheet.write('B7', 3)
worksheet.write('C1', 300)
worksheet.write('C2', 15)
worksheet.write('C5', 20234)
worksheet.write('C6', 21003)
worksheet.write('C7', 10000)

# Write an array formula that returns a single value
worksheet.write_formula('A1', '{=SUM(B1:C1*B2:C2)}')

# Same as above but more verbose.
worksheet.write_array_formula('A2:A2', '{=SUM(B1:C1*B2:C2)}')

# Write an array formula that returns a range of values
worksheet.write_array_formula('A5:A7', '{=TREND(C5:C7,B5:B7)}')