Write to Excel file with Python xlwt

xlwt is a library which can generate spreadsheet files compatible with Microsoft Excel versions 95 to 2003.

Create a sheet and write data to 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 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',
    '@'
]

Leave a Comment

Your email address will not be published. Required fields are marked *