Monthly Archives: July 2011

CSV sucks

5 Replies

For future reference: [CSV][csv] is a terrible format for spreadsheet data.

Any by spreadsheet data I mean CSV is a terrible format for text data that will be opened in [Microsoft Excel][excel] (unless you only speak English).

My objection to CSV as a spreadsheet format is it has no way to indicate the text encoding, and Excel (at least Excel 2008 for Mac and Excel 2007 for Windows) has no way of choosing the encoding when opening a CSV file. When your data is text from any number of non-English languages you will likely be dealing with characters outside the standard ‘latin-1’ character set.

Even if Excel did have a little pop-up menu for choosing the encoding when opening a CSV file, it would mean instructing your Excel-happy friends to “open as UTF-8” whenever they wanted to open your CSV data.

Wot you mean you don’t have Excel-happy friends?

[csv]: http://en.wikipedia.org/wiki/Comma-separated_values
[excel]: http://office.microsoft.com/en-us/excel/

Styling your Excel data with xlwt

1 Reply

This post is about how to create styles in [Excel spreadsheets][excel] with [the most excellent xlwt][xlwt] for [Python][python]. The documentation for xlwt (version 0.7.2) is a little sketchy on how to use formatting. So here goes…

To apply formatting to a cell you pass an instance of the `xlwt.XFStyle` class as the fourth argument to the `xlwt.Worksheet.write` method. The best way to create an instance is to use the `xlwt.easyxf` helper, which takes a string that specifies the formatting for a cell.

The other thing about using styles is you should only make one instance of each, then pass that same style object every time you want to apply it to a cell.

An example which uses a few styles:

import xlwt

styles = dict(
bold = ‘font: bold 1’,
italic = ‘font: italic 1’,
# Wrap text in the cell
wrap_bold = ‘font: bold 1; align: wrap 1;’,
# White text on a blue background
reversed = ‘pattern: pattern solid, fore_color blue; font: color white;’,
# Light orange checkered background
light_orange_bg = ‘pattern: pattern fine_dots, fore_color white, back_color orange;’,
# Heavy borders
bordered = ‘border: top thick, right thick, bottom thick, left thick;’,
# 16 pt red text
big_red = ‘font: height 320, color red;’,
)

I have no idea what it is based on, but 20 = 1 pt. So 320 = 16 pt text.

book = xlwt.Workbook()
sheet = book.add_sheet(‘Style demo’)

for idx, k in enumerate(sorted(styles)):
style = xlwt.easyxf(styles[k])
sheet.write(idx, 0, k)
sheet.write(idx, 1, styles[k], style)

book.save(‘Example.xls’)

It isn’t included with [the current distribution on the cheese shop][pypi], but there is [a useful Excel spreadsheet demonstrating cell patterns][sheet] in the source repository.

You can find the complete list of possible cell formats by reading [the source for `xlwt.Styles`][styles].

[excel]: http://office.microsoft.com/en-us/excel/
[xlwt]: http://www.python-excel.org/
[python]: http://www.python.org/
[styles]: https://secure.simplistix.co.uk/svn/xlwt/tags/0.7.2/xlwt/Style.py
[pypi]: http://pypi.python.org/pypi/xlwt/0.7.2
[sheet]: https://secure.simplistix.co.uk/svn/xlwt/tags/0.7.2/xlwt/doc/pattern_examples.xls