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 *

By continuing to use the site, you agree to the use of cookies. more information

The cookie settings on this website are set to "allow cookies" to give you the best browsing experience possible. If you continue to use this website without changing your cookie settings or you click "Accept" below then you are consenting to this.

Close