Column Alignment Defaults in XLWT

[Brent Marshall]

I am encountering trouble with a default column alignment setting. I have not found anything in the tutorial, the group, or the Net generally (I hope I have not missed something obvious), and I am hoping someone here can set me straight.

I am writing a workbook with eight worksheets of data, several of which involve two columns of count information prepared using Python's Counter object. I would like to set the alignment of the first column, the one with the counts, so that it is either centered or indented (I want some white space between the numbers in the first column and the information in the second column without creating an empty column between them).

I am able to set the column alignment, but the setting seems to apply only to cells in which I write no data, i.e., to cells in the first column that come below the rows of counter data. I know that setting is being saved, for if I open the resulting workbook and type information into those cells, the typed information is properly aligned. However, the column setting has not effect on my cells of counter data: they have the default right alignment given to numbers.

Here is illustrative code:

#!/usr/bin/python
import xlwt

def write_next_row(asheet, alist, reverse=False):
    global curr_row
    curr_row += 1
    list_count = len(alist)
    for count in range(list_count):
        if reverse:         # fill cells right to left
            asheet.write(curr_row, (list_count - 1) - count, alist[count])
        else:
            asheet.write(curr_row, count, alist[count])

# dummy data simulating Counter output
count_list = [ ['Tom', 12], ['Dick', 10], ['Harry', 7], ['Alan', 2] ]

# set up workbook and sheet
wb = xlwt.Workbook()
sty_header = xlwt.easyxf('font: bold True;'
                         'pattern: pattern solid, fore_color gray25;'
                         'alignment: horizontal center;')
summary_sheet = wb.add_sheet('Summary')
summary_sheet.col(0).set_style(xlwt.easyxf('alignment: indent 1;'))

# write column labels
curr_row = 0
summary_sheet.write(curr_row,0,'Count', sty_header)
summary_sheet.write(curr_row,1,'Name', sty_header)

# write count list
for item in count_list:
    write_next_row(summary_sheet, item, reverse=True)

wb.save('test.xls')
print 'Done!'

I see in the tutorial that cell formatting takes precedence over column formatting. Makes sense. But I explicitly format the cells only of the first row, which has my column labels. My experience suggests that there is a behind-the-scenes style is being set on my count cells that is overriding the column alignment.

Is it possible for me to work around this while still using column formatting. I know that I could go to explicit cell formatting, but I was trying to stick to a simpler approach.

I appreciate any guidance.

Brent

[John Yeung]

I'm a little surprised no one responded to this sooner, but I'll take
a stab now.

The short answer is: Whenever you write a cell, you are writing
everything about that cell, including the styling. There is no such
thing as "write just the value, and leave any previously specified
styling intact" in xlwt. Similarly, you don't write a whole bunch of
values and then later apply styling to the lot of them.

There may be nuances I'm misrepresenting here, which is why I didn't
respond to this sooner myself. But this should be useful at least as
a first approximation to get you headed in a productive direction.
And that direction involves you keeping track of both the values and
styles you want to write, and writing them all in one pass.

John Y.

[Brent Marshall]

John Y.,

Having taken the time to try to craft a good question, I am surprised at the lack of responses, also, so I especially appreciate you taking the time to do so.

I had suspected that the practical result would be as you say: that I must set the style for each cell as I write it. I have gone ahead and done that, and it works.

I had hoped, however, since the column-formatting parameter has been implemented (and works), that there would be a way to leave it in effect for cells that I write, as currently happens in Excel. At some point, maybe I can get into the source and figure out how to modify the cell-writing code not to override that column-default setting.

That said, this issue is less troublesome that the lack of print titles, so we will have to see.

Brent

[John Yeung]

> I had hoped, however, since the column-formatting parameter has been
> implemented (and works), that there would be a way to leave it in effect for
> cells that I write, as currently happens in Excel.

There is a huge gulf between what Excel presents to the user and what
Excel writes. If you study the file format, you will see that a lot
of things that seem like they ought to be simple are not at all
simple. The way I see it, xlwt is driven first and foremost by the
file format. Of course it tries to provide conveniences, and it
succeeds mightily, perhaps even more than most people realize, because
most people have no idea just how ridiculous the file format is.

I haven't studied the format myself in quite some time (it's a painful
endeavor) which is why I hesitate to answer with an authoritative
voice on any particular detail. But my recollection is that column
formatting serving as a default for cells that "don't otherwise
specify formatting" is an illusion of the Excel GUI. That is, the
running instance of Excel is keeping track of that stuff, but when it
comes time to commit the data to disk, it writes formatting cell by
cell as well, for any "populated" cell.

By its nature, xlwt isn't an interactive session. It's really just a
file writer. So it doesn't keep track of a lot of stuff that the
Excel GUI keeps track of. That's left up to us, or to someone
intrepid enough to write a framework that provides a closer
approximation of a live Excel session.

Incidentally, one of the last things I researched myself regarding the
Excel file format is how to specify repeated printing of the top row
or rows on every page. I didn't get very far because the
documentation available (from Microsoft and from OpenOffice.org) does
not match what Excel was actually doing, as far as I could tell. I
even tried to do brute-force reverse engineering based on saving
various example workbooks in Excel and examining the resulting files
with a hex editor. Obviously my efforts were fruitless.

John