Monday, December 22, 2014

Exporting to MS Excel format (XLS)

from Python is relatively easy with xlwt package. The linked page says that xlwt is a
Library to create spreadsheet files compatible with MS Excel 97/2000/XP/2003 XLS files, on any platform, with Python 2.3 to 2.7
To that I can only add that it's fast, too!
I've heard it doesn't support creation of XLS files with formulas, which might or might not be true (haven't tested that myself).

Installation is easy as usual:
$ pip install xlwt
Here's the link to PDF tutorial covering xlwt along with xlrd (read Excel files from Python) and xlutils (utulities for both xlwt and xlrd). You may also want to check out this site for some information.

To create yourself an Excel file, you basically do this:
import xlwt
from datetime import datetime


book = xlwt.Workbook()
sheet = book.add_sheet('SheetName', cell_overwrite_ok=True)  # Ability to overwrite may be extremely handy 

# You might want to change column widths. 700 is 0.21" as I've found out experimentally
sheet.col(0).width = 700

# Or row hights, but in this case enabling height_mismatch helps you achieve the desired effect.
sheet.row(0).height_mismatch = 1
sheet.row(0).height = 260  # 0.18"

sheet.write(0, 0, u"Current date/time:",
            xlwt.easyxf('font: name Arial, height 160; align: vertical bottom, horizontal left; '
                        'pattern: fore_colour white, pattern solid;'))
sheet.write(0, 2, datetime.now(),
            xlwt.easyxf('font: name Arial, height 160; '
                        'align: vertical center, horizontal center; '
                        'borders: left thin, right thin, top thin, bottom thin; '
                        'pattern: fore_colour white, pattern solid;',
                        num_format_str="DD/MM/YY H:MM:SS;@"))
sheet.col(2).width = 4000  # Hopefully this is enough to show datetime

sheet.write_merge(1, 1, 0, 2,
                  123.55,
                  xlwt.easyxf('font: name Arial Cyr, height 160; '
                              'align: vertical center, horizontal right; '
                              'pattern: fore_colour white, pattern solid;',
                              num_format_str="#,##0.00"))

book.save("out.xls")

No comments:

Post a Comment