Free Python API for Working with Excel XLSX Files

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.

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.

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)

workbook.close()

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.

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.
chart1.add_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.

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)}')

workbook.close()