Writing text to a cell that looks like a number

1,229 views

Skip to first unread message

CJ

unread,

Dec 6, 2011, 5:53:06 PM12/6/11

to openpyxl-users

I am trying to write a text string to a cell . This works well in
general but when I try and write a string like 9705E8601 (happens to
be a project account number) excel thinks there is a problem and
converts the cell to an 'inf'. Seems like excel thinks I am trying to
assign a huge value to the cell and doesn't like it. I have tried
formating the cell before assigning the value and after assigning the
value. In both cases my script runs but when I open up the xlsx
file... excel throws an error and converts the cell to 'inf'. The
error states:

"Excel found unreadable content in 'test.xlsx'. Do you want to recover
the contents of this workbook? If you trust the source of this
workbook, click Yes."

The relevant piece of code that I am working with is below:

from openpyxl.workbook import Workbook
from openpyxl.style import NumberFormat

wb = Workbook()
ws = wb.create_sheet()
currentCell = ws.cell(row = 1, column = 2)
currentCell.style.number_format.format_code = NumberFormat.FORMAT_TEXT
currentCell.value = '9705E8601'
currentCell.style.number_format.format_code =
NumberFormat.FORMAT_TEXT
wb.save(filename = outputFile)

Cheers, CJ

David Michael Pennington

unread,

Dec 14, 2011, 5:02:53 PM12/14/11

to openpyxl-users

On Dec 6, 4:53 pm, CJ <cjustinmay...@gmail.com> wrote:
> I am trying to write a text string to a cell . This works well in
> general but when I try and write a string like 9705E8601 (happens to
> be a project account number) excel thinks there is a problem and
> converts the cell to an 'inf'.

> ...[snip]...
> currentCell.value = '9705E8601'

I spent some time experimenting with your code under openpyxl 1.5.6...
normally when there is a string value, it gets written to xl/
sharedStrings.xml in the xlsx zip file.

The problem is that openpyxl is not writing the string if the string
matches this regexp... re.search("\d+E\d+", yourString, re.I)... it
appears to treat your string as a number with a mantissa (i.e. "E"
notation).

I don't know where this is called, but perhaps this much information
would be helpful in tracking down the problem in the code.