Style-Related xlwt Error

[Keyton Weissinger]

I'm attempting to generate a largish excel spreadsheet. The data is
all straight-text without any formatting, font work, etc. I'm
occasionally getting the following error:

More than 4094 XFs (styles)

I'm not manipulating any styles. Any thoughts?

Thank you.

Best,
Keyton Weissinger

[Chris Withers]

On 25/09/2010 11:14, Keyton Weissinger wrote:
> I'm attempting to generate a largish excel spreadsheet. The data is
> all straight-text without any formatting, font work, etc. I'm
> occasionally getting the following error:
>
> More than 4094 XFs (styles)
>
> I'm not manipulating any styles. Any thoughts?

I'm guessing we can't see your code?

If not, can you distil it down to as small an amount of python which
reproduces the issue?

cheers,

Chris

--
Simplistix - Content Management, Batch Processing & Python Consulting
- http://www.simplistix.co.uk

[John Machin]

On 26/09/2010 7:57 AM, Chris Withers wrote:
> On 25/09/2010 11:14, Keyton Weissinger wrote:
>> I'm attempting to generate a largish excel spreadsheet. The data is
>> all straight-text without any formatting, font work, etc. I'm
>> occasionally getting the following error:
>>
>> More than 4094 XFs (styles)
>>
>> I'm not manipulating any styles. Any thoughts?

Are you *creating* any styles?

>
> I'm guessing we can't see your code?

I'm guessing that your tracebacks are also state secrets :-(

> If not, can you distil it down to as small an amount of python which
> reproduces the issue?

Essentially, you must be using Worksheet.write(row_index, column_index,
data_value, xf_aka_style) ... what is xf_aka_style, how do you create
it/them, is creation done once or is it done within your worksheet /
row_index / column_index loops?

What version of xlwt are you using?

Can "largish" be expressed numerically?

You "occasionally" get that error after how many worksheets / rows /
columns? Have you noticed anything that may be correlated with getinng
the error or not getting it?

[Keyton Weissinger]

Sorry for the slow response(s)... Neither stack trace nor code is
state-secret-worthy. ;-)

Here's what we have for stack trace:

Traceback (most recent call last):

File "/home/keyton/webapps/django_prod/lib/python2.5/django/core/
handlers/base.py", line 86, in get_response
response = callback(request, *callback_args, **callback_kwargs)

File "/home/keyton/webapps/django_prod/lib/python2.5/django/contrib/
auth/decorators.py", line 67, in __call__
return self.view_func(request, *args, **kwargs)

File "/home/keyton/webapps/django_prod/schoolicity/archive_tools/
views.py", line 614, in generate_archive
archive = create_archive(current_school)

File "/home/keyton/webapps/django_prod/schoolicity/archive_tools/
views.py", line 397, in create_archive
appt_excel = generate_excel(filename, col_title_list,
data_row_list)

File "/home/keyton/webapps/django_prod/schoolicity/excel/util.py",
line 313, in generate_excel
export_sheet.write(row_idx, col_idx, current_value, s)

File "/home/keyton/lib/python2.5/xlwt-0.7.2-py2.5.egg/xlwt/
Worksheet.py", line 1003, in write
self.row(r).write(c, label, style)

File "/home/keyton/lib/python2.5/xlwt-0.7.2-py2.5.egg/xlwt/Row.py",
line 227, in write
style_index = self.__parent_wb.add_style(style)

File "/home/keyton/lib/python2.5/xlwt-0.7.2-py2.5.egg/xlwt/
Workbook.py", line 303, in add_style
return self.__styles.add(style)

File "/home/keyton/lib/python2.5/xlwt-0.7.2-py2.5.egg/xlwt/Style.py",
line 90, in add
return self._add_style(style)[1]

File "/home/keyton/lib/python2.5/xlwt-0.7.2-py2.5.egg/xlwt/Style.py",
line 149, in _add_style
raise ValueError("More than 4094 XFs (styles)")

ValueError: More than 4094 XFs (styles)


Here's the code:

def generate_excel(filename, col_title_list, data_row_list):
import StringIO
output = StringIO.StringIO()
export_wb = Workbook()
export_sheet = export_wb.add_sheet('Export')
col_idx = 0
for col_title in col_title_list:
export_sheet.write(0, col_idx, col_title)
col_idx += 1
row_idx = 1
for row_item_list in data_row_list:
col_idx = 0
for current_value in row_item_list:
if not current_value == None:
current_value_is_date = False
if isinstance(current_value, datetime.datetime):
current_value =
xlrd.xldate.xldate_from_datetime_tuple((current_value.year,
current_value.month, \
current_value.day,
current_value.hour, current_value.minute, \

current_value.second), 0)
current_value_is_date = True
elif isinstance(current_value, datetime.date):
current_value =
xlrd.xldate.xldate_from_date_tuple((current_value.year,
current_value.month, \

current_value.day), 0)
current_value_is_date = True
elif isinstance(current_value, datetime.time):
current_value =
xlrd.xldate.xldate_from_time_tuple((current_value.hour,
current_value.minute, \

current_value.second))
current_value_is_date = True
elif isinstance(current_value, models.Model):
current_value = str(current_value)
if current_value_is_date:
s = XFStyle()
s.num_format_str = 'M/D/YY'
export_sheet.write(row_idx, col_idx,
current_value, s)
else:
export_sheet.write(row_idx, col_idx,
cleanup_cell_value(current_value))
col_idx += 1
row_idx += 1
export_wb.save(output)
output.seek(0)
return output


The data_row_list is a python list of items representing each row.
Each item in the list is a list of values, one per column. The other
parameters are self-explanatory...

xlwt version is 0.7.2.

I appreciate any insight anyone can offer..

Thank you very much!

Keyton

[Chris Withers]

On 27/09/2010 00:46, Keyton Weissinger wrote:
> if current_value_is_date:
> s = XFStyle()
> s.num_format_str = 'M/D/YY'
> export_sheet.write(row_idx, col_idx,
> current_value, s)

...and we have a winner.

Create the style *once* in your outermost part of the function and
re-used it, rather than creating a new style each and every time you
write a date cell...

[John Machin]

On 27/09/2010 9:46 AM, Keyton Weissinger wrote:
> Sorry for the slow response(s)... Neither stack trace nor code is
> state-secret-worthy. ;-)
>
> Here's what we have for stack trace:
>
> Traceback (most recent call last):

[snip]

> Here's the code:
>
> def generate_excel(filename, col_title_list, data_row_list):
> import StringIO
> output = StringIO.StringIO()

Nothing to do with your problem, but: consider using cStringIO

> export_wb = Workbook()
> export_sheet = export_wb.add_sheet('Export')
> col_idx = 0
> for col_title in col_title_list:
> export_sheet.write(0, col_idx, col_title)
> col_idx += 1

Nothing to do with your problem, but: consider using enumerate e.g.
instead of the above 4 lines, do this. Ditto with your iteration over
rows and columns later.

for col_idx, col_title in enumerate(col_title_list):
export_sheet.write(0, col_idx, col_title)

> row_idx = 1
> for row_item_list in data_row_list:
> col_idx = 0
> for current_value in row_item_list:
> if not current_value == None:
> current_value_is_date = False

Nothing to do with your problem, but: why are you mucking about with
xlrd.xldate.xldate_from_*_tuple ...

> if isinstance(current_value, datetime.datetime):
> current_value =
> xlrd.xldate.xldate_from_datetime_tuple((current_value.year,
> current_value.month, \
> current_value.day,
> current_value.hour, current_value.minute, \
>
> current_value.second), 0)
> current_value_is_date = True
> elif isinstance(current_value, datetime.date):
> current_value =
> xlrd.xldate.xldate_from_date_tuple((current_value.year,
> current_value.month, \
>
> current_value.day), 0)
> current_value_is_date = True
> elif isinstance(current_value, datetime.time):
> current_value =
> xlrd.xldate.xldate_from_time_tuple((current_value.hour,
> current_value.minute, \
>
> current_value.second))
> current_value_is_date = True

... instead of replacing all of that by:

if isinstance(current_value, (datetime.datetime, datetime.date,
datetime.time)):
current_value_is_date = True

?

> elif isinstance(current_value, models.Model):
> current_value = str(current_value)
> if current_value_is_date:
> s = XFStyle()
> s.num_format_str = 'M/D/YY'

Everything to do with your problem: you are creating a new style for
each date etc value in your data. Don't do that. Create the style ONCE
at the start of the function. Give it a meaningful name. Hint: "s" is
not a meaningful name.

> export_sheet.write(row_idx, col_idx,
> current_value, s)
> else:
> export_sheet.write(row_idx, col_idx,
> cleanup_cell_value(current_value))
> col_idx += 1
> row_idx += 1
> export_wb.save(output)
> output.seek(0)
> return output
>
>
> The data_row_list is a python list of items representing each row.
> Each item in the list is a list of values, one per column. The other
> parameters are self-explanatory...
>
> xlwt version is 0.7.2.
>
> I appreciate any insight anyone can offer..

[snip]

>> Essentially, you must be using Worksheet.write(row_index, column_index,
>> data_value, xf_aka_style) ... what is xf_aka_style, how do you create
>> it/them, is creation done once or is it done within your worksheet /
>> row_index / column_index loops?

The above can't be construed as insight?

[Keyton Weissinger]

Thank you both, Chris and John.

John, your comments were indeed insightful. I apologize for posting my
code instead of taking in said insight. That said, I wouldn't've
received the other excellent comments if I'd left it as insight so I'm
selfishly glad I did so. Thank you again.

Best,
Keyton