Hello Marco and welcome to the wonderful world of Python,
Your problem is that the file is a text file so the values you are reading
are text which you then write to the Excel sheet.
So you need to convert the text value to a float value. Now the Big Cahonas
has already been there so it's included:
float( [x])
Convert a string or a number to floating point. If the argument is a
string, it must contain a possibly signed decimal or floating point number,
possibly embedded in whitespace. Otherwise, the argument may be a plain or
long integer or a floating point number, and a floating point number with
the same value (within Python's floating point precision) is returned. If
no argument is given, returns 0.0.
Note: When passing in a string, values for NaN and Infinity may be
returned, depending on the underlying C library. The specific set of
strings accepted which cause these values to be returned depends entirely
on the C library and is known to vary
If you do this change it might work ;-)
Add these functions:
def isNaN(x):
return isinstance(x, float) and x!=x
def isInf(x):
return !isNaN(x) && isNaN( (x) - (x) )
in writeExcelRow change:
for column in columns:
fcolumn = float(column)
if(isNaN(fcolumn) or isInf(fcolumn)):
# do some error handling
else:
worksheet.write(lno, cno, column,style)
cno = cno + 1
I'm sure that there are smarter ways of doing this and someone will
probably point them out ;-)
// Anders
--
English is not my first, or second, language
so anything strange, or insulting, is due to
the translation.
Please correct me so I may improve my English!
On Tue, 14 Oct 2008 03:03:52 -0700 (PDT), MM wrote:
> Hi to all,
>
> I'm trying to import a tab separated values file onto Excel with the
> following script:
>
> import csv
> from pyExcelerator import *
>
> w = Workbook()
> worksheet = w.add_sheet('sim1')
>
> def writeExcelRow(worksheet, lno, columns):
> style = XFStyle()
> style.num_format_str = '0.00E+00'
> cno = 0
> for column in columns:
> worksheet.write(lno, cno, column,style)
> cno = cno + 1
>
> nrow = 0
> csvfile = file('res1.txt','r')
> csvreader = csv.reader(csvfile, delimiter='\t')
>
> for line in csvreader:
> writeExcelRow(worksheet,nrow,line)
> nrow += 1
>
> csvfile.close()
> w.save('numbers.xls')
>
> All goes well and the resulting file "numbers.xls" has all the numbers
> in the right place....
>
> The problem is that excel sees the numbers as text and gives the error
> "numbers stored as text" that I have to correct manually.
>
> The file res1.txt has the structure of a tab separated values of
> floating point numbers.
>
> Thank you for the help.
>
> Marco
--
http://mail.python.org/mailman/listinfo/python-list
