width of a column?

[Chris Withers]

Hi All,

I'm wondering what the units of column width are in xlrd and xlwt, in
particular:

- do xlrd and xlwt both use the same units for column width?

- if so, what are they?

- how do they relate to the size of the column in pixels and/or the size
that excel shows when you drag the column to a certain size?

cheers,

Chris

--
Simplistix - Content Management, Zope & Python Consulting
- http://www.simplistix.co.uk

[John Machin]

Chris Withers wrote:
> Hi All,
>
> I'm wondering what the units of column width are in xlrd and xlwt, in
> particular:
>
> - do xlrd and xlwt both use the same units for column width?

Yes.

>
> - if so, what are they?

Read the xlrd documentation for the following:
Colinfo.width
Sheet.computed_column_width
Sheet.defcolwidth # note scaled by 256 c.f. the others
Sheet.standardwidth

>
> - how do they relate to the size of the column in pixels and/or the size
> that excel shows when you drag the column to a certain size?

AFAIK, this is an MS trade secret. See xlrd.Sheet.defcolwidth docs.

[ChrisW]

On Mar 15, 5:50 am, John Machin <sjmac...@lexicon.net> wrote:
> Read the xlrd documentation for the following:
> Colinfo.width
> Sheet.computed_column_width
> Sheet.defcolwidth # note scaled by 256 c.f. the others
> Sheet.standardwidth

Nice to see Microsoft got that one all simple, consistent and
understandable then ;-)

> > - how do they relate to the size of the column in pixels and/or the size
> > that excel shows when you drag the column to a certain size?
>
> AFAIK, this is an MS trade secret. See xlrd.Sheet.defcolwidth docs.

Heh, excellent...

Well, what I ended up doing was writing a tiny script to strip the
info from a "sample spreadsheet" (ie: one created to get the
formatting right) and then dumping that in a format that can be used
in the output script:

import xlrd,sys
from pprint import pprint

book = xlrd.open_workbook(sys.argv[1],formatting_info=True)
s = book.sheet_by_index(0)

result = []

for i in range(s.ncols):
result.append((
s.cell_value(0,i),
s.computed_column_width(i),
))

pprint(tuple(result))


I then use this in the sheet generating script as follows:

import xlrd,xlwt

write_book = xlwt.Workbook()
write_sheet = write_book.add_sheet('Stats')

font0 = xlwt.Font()
font0.bold = True

style0 = xlwt.XFStyle()
style0.font = font0

columns = *stuff output by script above*

for i in range(len(columns)):
name,width = columns[i]
write_sheet.write(0,i,name,style0)
write_sheet.col(i).width=width

Hope this helps others who are looking to do similar :-)

cheers,

Chris