Write to Excel File with Python xlwt

xlwt is a handy library for working with spreadsheets programs such as Microsoft Excel. It allows developers to create spreadsheet files compatible with Excel 95-2003, giving them access to a wide range of data that can be used for various purposes. With xlwt, developers can read and write data to these files without having to worry about the quirks that are ubiquitous in Microsoft Excel.

The library has an incredibly intuitive syntax. For example, rows and columns can be populated with data using simple functions that require minimal overhead. This makes it easy to understand how to properly utilize xlwt’s features and make the most of it as a powerful tool. Furthermore, its easy-to-use interface allows even novice developers to quickly get up and running with the programming involved.

Table of Contents

Create a sheet and write data to the cell

import xlwt
from datetime import datetime

text_style = xlwt.easyxf('font: name Times New Roman, height 200,bold True')
number_style = xlwt.easyxf(num_format_str='#,##0.00')
date_style = xlwt.easyxf(num_format_str='D-MMM-YY')

workbook = xlwt.Workbook()
worksheet = workbook.add_sheet('My First Sheet')

worksheet.write_merge(0, 0, 1, 2, 'This is a merged cell', text_style)
worksheet.write(1, 0, datetime.now(), date_style)
worksheet.write(2, 0, 100)
worksheet.write(2, 1, 200)
worksheet.write(2, 2, xlwt.Formula("A3+B3"))

workbook.save('excel.xls')

Merge cells

As you see in the basic example above, we write to 2 cells by using write_merge().

worksheet.write_merge(0, 0, 0, 2, 'This is a merged cell', text_style)

The first 4 parameters are cells at position: r1, r2, c1, c2.

(0, 0, 0, 2) means merging A1, B1 and C1.

Execute a formula

We can use xlwt.Formula() to do a function.

worksheet.write(10, 10, xlwt.Formula("A3+B3"))
worksheet.write(10, 10, Formula("A4*B4*sin(pi()/4)"))
worksheet.write(10, 10, Formula("SUM(C1;C2;;;;;C3;;;C4)"))

Border size and color

border_normal = xlwt.easyxf('borders: left thin, right thin, top thin, bottom thin;')
border_1 = xlwt.easyxf('borders: left 1, right 1, top 1, bottom 1;')
border_2 = xlwt.easyxf('borders: left 2, right 2, top 2, bottom 2;')
border_color_2 = xlwt.easyxf('borders: top_color blue, bottom_color blue, right_color blue, left_color blue, left 2, right 2, top 2, bottom 2;')

Set background color

yellow_color = xlwt.easyxf(
pattern: pattern solid, fore_colour yellow;')

Cell format

There are 2 ways to apply a format to a cell.

# easyxf
number_style = xlwt.easyxf(num_format_str='#,##0.00')
date_style = xlwt.easyxf(num_format_str='D-MMM-YY')

#XFStyle
date_style= xlwt.XFStyle()
date_style.num_format_str = 'DD-MM-YY'
currency_style = xlwt.XFStyle()
currency_style.num_format_str = '$#,##0.00'

List of available format

formats = [
    'general',
    '0',
    '0.00',
    '#,##0',
    '#,##0.00',
    '"$"#,##0_);("$"#,##',
    '"$"#,##0_);[Red]("$"#,##',
    '"$"#,##0.00_);("$"#,##',
    '"$"#,##0.00_);[Red]("$"#,##',
    '0%',
    '0.00%',
    '0.00E+00',
    '# ?/?',
    '# ??/??',
    'M/D/YY',
    'D-MMM-YY',
    'D-MMM',
    'MMM-YY',
    'h:mm AM/PM',
    'h:mm:ss AM/PM',
    'h:mm',
    'h:mm:ss',
    'M/D/YY h:mm',
    '_(#,##0_);(#,##0)',
    '_(#,##0_);[Red](#,##0)',
    '_(#,##0.00_);(#,##0.00)',
    '_(#,##0.00_);[Red](#,##0.00)',
    '_("$"* #,##0_);_("$"* (#,##0);_("$"* "-"_);_(@_)',
    '_(* #,##0_);_(* (#,##0);_(* "-"_);_(@_)',
    '_("$"* #,##0.00_);_("$"* (#,##0.00);_("$"* "-"??_);_(@_)',
    '_(* #,##0.00_);_(* (#,##0.00);_(* "-"??_);_(@_)',
    'mm:ss',
    '[h]:mm:ss',
    'mm:ss.0',
    '##0.0E+0',
    '@'
]