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