How can I read Excel .xls using xlrd to either fetch a floating point number as formatted in Excel or find the format Excel uses in the sheet?

[Deac Lancaster]

Hi,  First timer here.

I want to be able to read a number from .xls either in Excel's format OR find the format Excel is using so I can apply it to what I read.

  

I'm now getting the number in full precision over 6 decimal digits and want to be able to put it in the same number of decimal places shown in the spreadsheet.

I used John Machin's code but always get format_str = 0.00.  Here is John's code that I used:

      xf_index = sheet.cell_xf_index(rowx, colx)

    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"

I would love to have a 1 or 2 in the format string when the spreadsheet shows 1 or 2 decimal places instead of 0.0.   

What I need to do?   Any help mucho appreciated.

[raf]

Hi,

The format string you are getting, "0.00", is telling
you that there are 2 decimal places because there are
two zeroes after the decimal point. It's up to you to
then make use of that information to round the value in
the cell to that many decimal places.

And I agree that floating point numbers in cells are
useless without knowing how many digits they need to be
rounded to in order to match the expectations of humans
looking at the spreadsheets.

Warning: The xlrd module handles getting formatting
information for .xls files but not for .xlsx files,
even though it can read the values in them. For .xlsx
files, you need to use the openpyxl module instead
to obtain the formatting information.

Attached is a module/program that does this. As a
module, it provides the same API for reading csv, xls,
and xlsx files (i.e. obtaining all cells as text). As a
program, it outputs the contents of such files (or an
individual worksheet from them) as csv.

You won't want to use it as is, because it's in Python2
and it converts the character set in csv files from
UTF-32/16/8 to Latin-1 when it encounters it (you don't
want to know). But it does show how to obtain and
interpret the formatting information for both xls and
xlsx files. The only non-standard modules it uses are
xlrd and openpyxl.

Warning: I think that the language of formatting
specifiers is probably very complex in general which is
why libraries don't apply them to the values for you.
But I've been using the attached code for over a decade
to read many many thousands of spreadsheets from many
different people and it's worked well for me (although
the openpyxl code is much more recent). But it's
possible that it won't handle some format specifiers
that you encounter. But probably not. All this code is
doing for numbers is counting the number of zeroes
after the decimal place and rounding accordingly.

cheers,
raf

[derek]

In the Google groups, there is a reply from John Machin to a very similar question, where he says:

"xlrd analyses these format strings only as far as attempting to guess if
  the number in a cell is intended to represent a date. Nobody has
written a general-purpose format string parser."

Discussion is here:
https://groups.google.com/forum/#!topic/python-excel/cAQ1ndsCVxk

[derek]

Some further Googling turned up this:

http://uucode.com/blog/2013/10/22/using-xlrd-and-formatting-excel-numbers/

No idea if this is still useful, or not.

[Deac Lancaster]

Raf,

YES, YES, YES!   Much thanks for such a complete answer.   The “0.00” meaning 2 decimal places really makes sense, I just didn’t think of it.  I just retested it and found that is consistent with my files.  Thank you so so much!!

Thanks so much also for your .xls and .xlsx processing program, I’ll study it carefully to see what I can steal.  Ha!  I will have to adjust since, being new, I’m on python3.8 but the general structure of your program will be a nice help.

My project does involve both .xls and .xlsx files; if I can get the formatting issue solved I’ll be done with .xls for the moment.  Today I did start to learn and use openpyxl, but with your response I’ll stop that and try to resolve the formatting issue.

Thanks again for your generosity. 

I hope you’re in good health.

Regards,

-deac
 😎

                

[raf]

Deac Lancaster wrote:

> Raf,
>
> YES, YES, YES! Much thanks for such a complete answer. The “0.00”
> meaning 2 decimal places really makes sense, I just didn’t think of it. I
> just retested it and found that is consistent with my files. Thank you so
> so much!!
>
>
> Thanks so much also for your .xls and .xlsx processing program, I’ll study
> it carefully to see what I can steal. Ha! I will have to adjust since,
> being new, I’m on python3.8 but the general structure of your program will
> be a nice help.
>
> My project does involve both .xls and .xlsx files; if I can get the
> formatting issue solved I’ll be done with .xls for the moment. Today I did
> start to learn and use openpyxl, but with your response I’ll stop that and
> try to resolve the formatting issue.
>
> Thanks again for your generosity.
>
> I hope you’re in good health.
>
> Regards,
> -deac

Glad it helped. You will still need openpyxl, though.
It's a/the library of choice for xlsx files.
xlrd is frozen / no longer being updated.
Take care.

cheers,
raf

[Deac-33 Lancaster]

Raf,

I just asked if there's a way to use openpyxl to find the cell format excel uses.  

I forgot to specify that I am now looking at .xlsx files.  

I can get .xlsx data fine, but still looking for the Excel format as you showed me to find using xlrd for .xls files.

[raf]

Deac-33 Lancaster wrote:

> Raf,
>
> I just asked if there's a way to use openpyxl to find the cell format excel
> uses.
> I forgot to specify that I am now looking at .xlsx files.
>
> I can get .xlsx data fine, but still looking for the Excel format as you
> showed me to find using xlrd for .xls files.

The xls2csv program I attached did both. It used xlrd to read .xls
files and it used openpyxl to read .xlsx files. Look at it again.

cheers,
raf