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

Open Source Python API for Google Sheets

Python library for writing MS Excel 97/2000/XP/2003 XLS files.

Xlwt is an open source python API for writing and formatting data in older(97/2000/XP/2003 XLS) Microsoft Excel file formats. Python developers can easily manipulate older Microsoft Excel files using this pure Python API with no dependencies on modules and packages outside the standard Python distribution. The API provides rich writing and manipulation features including, merge cells, format rows, manipulate dates, use hyperlinks, insert images, use panes and more.

Xlwt is pretty easy and simple to use and the developers can install it using the pip package installer. Please note that the API only supports XLS file format and XLSX file format is currently not supported.

Previous Next

Getting Started with Xlwt

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

Install Xlwt via PIP Command

pip install xlwt

Create XLS using Free Python API

Xlwt API allows creating Microsoft Excel XLS files using Python. In order to create a blank excel file, you can create an instance using xlwt.Workbook() method. You can add sheets in it using Workbook.add_sheet() method. After you have your new workbook ready, you can add new rows & columns to it accordingly.

Create Excel XLS File via Python API

import xlwt
from datetime import datetime

style0 = xlwt.easyxf('font: name Times New Roman, color-index red, bold on',
    num_format_str='#,##0.00')
style1 = xlwt.easyxf(num_format_str='D-MMM-YY')

wb = xlwt.Workbook()
ws = wb.add_sheet('A Test Sheet')

ws.write(0, 0, 1234.56, style0)
ws.write(1, 0, datetime.now(), style1)
ws.write(2, 0, 1)
ws.write(2, 1, 1)
ws.write(2, 2, xlwt.Formula("A3+B3"))

wb.save('example.xls')

Sytle Excel Rows using Python

The Open Source spreadsheet library Xlwt allows software developers to style excel rows programmatically. You can simply style a row by using Workbook.row().set_style() method. The styling method allows you to set font, font height, color, and more.

How to Sytle Excel Rows via Python API

from xlwt import *

w = Workbook()
ws = w.add_sheet('Hey, Dude')

for i in range(6, 80):
    fnt = Font()
    fnt.height = i*20
    style = XFStyle()
    style.font = fnt
    ws.write(i, 1, 'Test')
    ws.row(i).set_style(style)
w.save('row_styles.xls')

Using Panes in XLS via Python

Xlwt library gives developers the capability to freeze rows & columns in XLS file format using panes_frozen property. Using the panes properties provided by the API, you freeze specific rows and columns as well.

How to Use Panes in XLS Spreadsheets in Python Apps

from xlwt import *

w = Workbook()
ws1 = w.add_sheet('sheet 1')
ws2 = w.add_sheet('sheet 2')
ws3 = w.add_sheet('sheet 3')
ws4 = w.add_sheet('sheet 4')
ws5 = w.add_sheet('sheet 5')
ws6 = w.add_sheet('sheet 6')

for i in range(0x100):
    ws1.write(i//0x10, i%0x10, i)

for i in range(0x100):
    ws2.write(i//0x10, i%0x10, i)

for i in range(0x100):
    ws3.write(i//0x10, i%0x10, i)

for i in range(0x100):
    ws4.write(i//0x10, i%0x10, i)

for i in range(0x100):
    ws5.write(i//0x10, i%0x10, i)

for i in range(0x100):
    ws6.write(i//0x10, i%0x10, i)

ws1.panes_frozen = True
ws1.horz_split_pos = 2

ws2.panes_frozen = True
ws2.vert_split_pos = 2

ws3.panes_frozen = True
ws3.horz_split_pos = 1
ws3.vert_split_pos = 1

ws4.panes_frozen = False
ws4.horz_split_pos = 12
ws4.horz_split_first_visible = 2

ws5.panes_frozen = False
ws5.vert_split_pos = 40
ws4.vert_split_first_visible = 2

ws6.panes_frozen = False
ws6.horz_split_pos = 12
ws4.horz_split_first_visible = 2
ws6.vert_split_pos = 40
ws4.vert_split_first_visible = 2

w.save('panes.xls')
 English