(xlrd) - getting number format for a given cell?

[cb]

Hi All!,
first time here, I'm using xlrd for a couple of days for my work
project, and managed to read data from excel files without any
problem, but I have no idea how to read number format for a cell.
alternatively I could read the cell with formatting applied (that is
i.e '$20' instead of '20')
I'd be gratefull for any suggestions.
best regards
y

[John Machin]

On 21/04/2009 8:00 PM, cb wrote:
> Hi All!,
> first time here, I'm using xlrd for a couple of days for my work
> project, and managed to read data from excel files without any
> problem,

Hi cb, welcome to the group.

> but I have no idea how to read number format for a cell.

For a start, you need to include formatting_info=True when you call
xlwt.open_workbook().

Then assuming you have got a Book object and a Sheet object and the cell
of interest is at (rowx, colx):

either:
cell = sheet.cell(rowx, colx) # gets a Cell object
xf_index = cell.xf_index
or:
xf_index = sheet.cell_xf_index(rowx, colx)
then:
xf = book.xf_list[xf_index] # gets an XF object
format_key = xf.format_key
format = book.format_map[format_key] # gets a Format object
format_str = format.format_str # this is the "number format string"

> alternatively I could read the cell with formatting applied (that is
> i.e '$20' instead of '20')

Indeed you could, if some kind soul(s) were to:
(1) document the grammar inherent in number format strings, and write
some code to take a number format string and a value and return the
formatted value
or (2) "borrow" the appropriate code (C or C++ or Java) from some other
open source project and convert it to idiomatic maintainable Python
or (3) provide some funding for somebody else to do (1) or (2).

Cheers,
John

[Nancy Head (CBIZ MMP)]

Did you mean to say xlrd (vs xlwt)?
i.e.

For a start, you need to include formatting_info=True when you

call xlrd.open_workbook().

Nancy

[John Machin]

On 22/04/2009 12:23 AM, Nancy Head (CBIZ MMP) wrote:
>
> Did you mean to say xlrd (vs xlwt)?
> i.e.
> For a start, you need to include formatting_info=True when you
> call xlrd.open_workbook().

Yes. Good pick-up.

Cheers,
John

[Nancy Head (CBIZ MMP)]

I'm trying to copy certain information from one .xls file into another
new file.

Thanks to your help, I'm now successfully pulling the format
- but I'm still having trouble writing it to the new file.

My current error: 'Format' object has no attribute 'font'
Occurs at this line: new_ws.write(row, col, c.value, format)

new_ws is a worksheet object
c.value is read from the original worksheet
format = wb.format_map[format_key] # Format object made following
instructions to cb



I want to copy the font, size, color, etc. from the first file to the
second.
Or is the "etc." too broad?

Mainly I want the format (General, 0, etc. that I can now successfully
pull - Thank You!!)
and the color of the text (red or black). Font (Arial) and size (could
vary) would be great, but I can hardcode for that.

Help? Thx :)

[John Machin]

On 22/04/2009 1:10 AM, Nancy Head (CBIZ MMP) wrote:
>
> I'm trying to copy certain information from one .xls file into another
> new file.
>
> Thanks to your help, I'm now successfully pulling the format
> - but I'm still having trouble writing it to the new file.
>
> My current error: 'Format' object has no attribute 'font'
> Occurs at this line: new_ws.write(row, col, c.value, format)

>
> new_ws is a worksheet object
> c.value is read from the original worksheet
> format = wb.format_map[format_key] # Format object made following
> instructions to cb

The 4th arg of xlwt.Worksheet.write() should be an xlwt.Style.XFStyle
object -- you are feeding it an xlrd.Format object.

xlrd and xlwt were developed quite independently and don't share one
object model. One needs to "unpack" the attributes of xlrd objects and
pack the results into xlwt objects.

The easiest way to make an xlwt.XFStyle object is to use the
xlwt.easyxf() function. Have a look in the xlwt/examples directory. You
need to pass format.format_str (not just format) as the 2nd arg of easyxf.

> I want to copy the font, size, color, etc. from the first file to the
> second.
> Or is the "etc." too broad?
>
> Mainly I want the format (General, 0, etc. that I can now successfully
> pull - Thank You!!)
> and the color of the text (red or black). Font (Arial) and size (could
> vary) would be great, but I can hardcode for that.

Get Chris Withers' xlutils package from PyPI, look in filter.py, find
the workbook method of the BaseFilter class ... most of that is a loop
over a list of xlrd XF objects, converting them to xlwt XFStyle objects.

HTH,
John

[Chris Withers]

Nancy Head (CBIZ MMP) wrote:
>

> I'm trying to copy certain information from one .xls file into another
> new file.

I suggest you take a look at xlutils.copy ;-)

>>> from xlrd import open_workbook
>>> from xlutils.copy import copy
>>> rb = open_workbook('testall.xls')
>>> wb = copy(rb)
>>> wb.get_sheet(0).write(0,0,'changed!')

cheers,

Chris

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

[cb]

Thanks John,
that did the trick!
y

[Nancy Head (CBIZ MMP)]

I understand that xlrd objects are completely separate from xlwt
objects, but I somehow keep "forgetting" it all the same. I've made this
same kind of error more than once.


John & Chris:
With your help, I'm now successfully copying what I want from the
formatting/style! Thanks!!

Nancy

-----Original Message-----
From: python...@googlegroups.com
[mailto:python...@googlegroups.com] On Behalf Of John Machin
Sent: Tuesday, April 21, 2009 11:51 AM
To: python...@googlegroups.com
Subject: [pyxl] Re: (xlrd) - getting number format for a given cell?