3

Possible Duplicate:
Integers from excel files become floats?

I have an excel spreadsheet that contains 1984, which xlrd handles as a number type, and thus gives me the value back as the float 1984.0. I want to get the original value as it appears in the spreadsheet, as a string "1984". How do I get this?

Community
  • 1
  • 1
Alex Gaynor
  • 14,353
  • 9
  • 63
  • 113
  • afaik you can get the formatting data from the xls spreadsheet ... that may solve your problem ... (or it may not...) – Joran Beasley Nov 09 '12 at 22:04
  • 2
    Excel doesn't have integers. It has a numerical type (which encompasses dates, integers, floats) and formatting. The formatting determines how the numerical type is displayed. That numerical type is a floating point number. – Steven Rumbalski Nov 09 '12 at 22:07
  • Is there anything preventing you from just converting the type from float to int in Python? – Markus Unterwaditzer Nov 09 '12 at 22:08
  • 1
    If you *must* have the data exactly how it appears in Excel I suggest opening Excel via a COM object. You'll want the `Range`'s `Text` property (not the `Value` property), which gives the textual representation that you see in the cell. `xlrd` essentially gives you the `Value` property of the cell. – Steven Rumbalski Nov 09 '12 at 22:09
  • Note: the StackOverflow question [Integers from excel files become floats](http://stackoverflow.com/questions/8825681/integers-from-excel-files-become-floats) includes an [answer](http://stackoverflow.com/a/8826770/1322401) from xlrd's author, John Machin. – Steven Rumbalski Nov 09 '12 at 22:14

4 Answers4

2

So internally in Excel, that 1984 is stored as a decimal number, so 1984.0 is correct. You could have changed the number formatting to show it as 1984.00, or whatever.

So are you asking how to query the cell formatting to tell that the number format is no decimals? If so you might look into using the formatting_info=True parameter of open_workbook

sheet = open_workbook( 'types.xls',formatting_info=True ).sheet_by_index(0)

Have you come across the python-excel.pdf document from http://www.python-excel.org/ ? It is pretty good tutorial for learning to use xlrd and xlwt. Unfortunately, they say:

We've already seen that open_workbook has a parameter to load formatting information from Excel files. When this is done, all the formatting information is available, but the details of how it is presented are beyond the scope of this tutorial.

Craig Schmidt
  • 640
  • 5
  • 10
1

if cell.ctype==xlrd.XL_CELL_NUMBER

then excel is storing 1984 as a float and you would need to convert to a string in python

In excel

="1984" would be a string '1984 would be a string, note that ' does not display 1984 is a #

stilldodge
  • 204
  • 1
  • 2
0

The only kind of number is a float. The formatting attached to the cell determines if it represents a date, a decimal, or an integer. Look up the format string, and hopefully it will let you discern how the number is to be displayed.

Josh Lee
  • 171,072
  • 38
  • 269
  • 275
-1

Use string formatting:

"%d" % mynumber

>>> "%d" % 1984.0
'1984'
Eric
  • 95,302
  • 53
  • 242
  • 374