Read cell number format on .xlsx files

[MS]

I know 'formatting_info=True' is not implemented on .xlsx files however I wonder if anyone has created their own hack/patch for this?

I would like to find out if a cell is formatted as Percent or not. 

[python...@raf.org]

Knowing how many digits are displayed is crucial information as well
as it's often different to the number of digits stored.

I assume that you're talking about xlrd, not openpyxl.

Start using openpyxl instead of (or as well as?)
xlrd/xlwt. xlrd/xlwt aren't going to change any more.

openpyxl supports number formats in .xlsx files.
There's an example in its documentation:

>>> import datetime
>>> from openpyxl import Workbook
>>> wb = Workbook()
>>> ws = wb.active
>>> # set date using a Python datetime
>>> ws['A1'] = datetime.datetime(2010, 7, 21)
>>>
>>> ws['A1'].number_format
'yyyy-mm-dd h:mm:ss'

This example creates a workbook but presumably it works
just as well when reading them as well.

cheers,
raf

[python...@raf.org]

Speaking of which, is there an openpyxl tutorial that shows
reading xlsx files in detail? I just had a look at the tutorial
and cookbook at https://openpyxl.readthedocs.io/en/stable/ and
it's almost entirely about creating spreadsheets. There seems
to be only a single four-line example of reading an existing
spreadsheet (unless I missed something).

cheers,
raf

[MS]

Yes I'm talking about xlrd. 

The problem I have is that I need to check the number format on both xls and xlsx files. 

xlrd provides number format support for xls but not xlsx and openpyxl does the opposite. 

xlrd has everything I need, its just missing this one bit. If I can find a patch I'll be in dreamland lol. 

Could you say a bit more about the number of digits please, this sounds interesting. 

Thanks

[python...@raf.org]

MS wrote:

> Yes I'm talking about xlrd.
>
> The problem I have is that I need to check the number format on both xls
> and xlsx files.
>
> xlrd provides number format support for xls but not xlsx and openpyxl does
> the opposite.
>
> xlrd has everything I need, its just missing this one bit. If I can find a
> patch I'll be in dreamland lol.

I think you'll need to use both libraries.

> Could you say a bit more about the number of digits please, this sounds
> interesting.

My main need for xlrd is to import the data in spreadsheets
into a database. It's extremely important to get the numbers
right. What that means for my purposes is importing the
numbers as they would be seen by an Excel user looking at
the file. If a cell contains 0.123345789 and the cell format
is "0.000", Excel/Openffice/LibreOffice display the value as
"0.123". They don't display the entire value that is stored
in the cell but it's the full value that is read and
returned by xlrd. So I need to check the format to know how
many digits to round the cell value to.

> Thanks

cheers,
raf

> --
> You received this message because you are subscribed to the Google Groups "python-excel" group.
> To unsubscribe from this group and stop receiving emails from it, send an email to python-excel...@googlegroups.com.
> To post to this group, send an email to python...@googlegroups.com.
> Visit this group at https://groups.google.com/group/python-excel.
> To view this discussion on the web, visit https://groups.google.com/d/msgid/python-excel/d50bb9e2-cccc-410f-84d5-3855b50e3fb6%40googlegroups.com.
> For more options, visit https://groups.google.com/d/optout.