How to not exceed the maximum number of fonts when generating XLS spreadsheets

[Erik Wilson]

I am taking several comma delimited CSV files and using them to generate an XLS spreadsheet in which the names of the files become separate tabs in the spreadsheet. The code I have produces the results I want except for when opening the spreadsheet I get the following warning: "Some text formatting may have changed in this file because the maximum number of fonts was exceeded. It may help to close other documents and try again." I am pretty sure that the problem arises from the code trying to change the format of cells beyond the 65536 row limit, but I'm not sure how to limit the row changes. I need no more than a few hundred rows across three columns.

import csv, glob, xlwt, sys, os
csvFiles = os.path.join(LogFileFolder, "*")
wb = xlwt.Workbook()
colNames = ['iNFADS_FAC','CAT','Crosswalk_FAC','FAC']
for filename in glob.glob(csvFiles):
    (f_path, f_name) = os.path.split(filename)
    (f_short_name, f_extension) = os.path.splitext(f_name)
    ws = wb.add_sheet(f_short_name)
    with open(filename, 'rb') as csvf: 
        csvReader = csv.reader(csvf)
        for rowx, row in enumerate(csvReader): 
            for colx, value in enumerate(row):
                if value in colNames:
                    ws.write(rowx, colx, value, xlwt.easyxf(
                        "border: top medium, right medium, bottom double, left medium;" \ 
                        "font: bold on; pattern: pattern solid, fore_color pale_blue;" \ 
                        "align: vert centre, horiz centre"))
                elif value not in colNames:
                    ws.write(rowx, colx, float(value), xlwt.easyxf("align: vert centre, horiz centre"))
##This second "xlwt.easyxf(align...)" part is the offending section of the code, if
##I remove just that part then the problem goes away. Is there a way to keep
##it within the 65536 limit here?
                else:
                    pass
wb.set_active_sheet = 1      
outXLS = os.path.join(LogFileFolder, "FAC-CAT Code Changes.xls")
wb.save(outXLS) 

[John Machin]

On Thursday, February 23, 2017 at 2:41:57 PM UTC+11, DanRivMil wrote:

I am taking several comma delimited CSV files and using them to generate an XLS spreadsheet in which the names of the files become separate tabs in the spreadsheet. The code I have produces the results I want except for when opening the spreadsheet I get the following warning: "Some text formatting may have changed in this file because the maximum number of fonts was exceeded. It may help to close other documents and try again." I am pretty sure that the problem arises from the code trying to change the format of cells beyond the 65536 row limit, but I'm not sure how to limit the row changes. I need no more than a few hundred rows across three columns.

I am really at a loss here ... what part of your code is " trying to change the format of cells beyond the 65536 row limit" ?
What is a "row change"? Note that 65536 is the maximum number of rows in a sheet, how does this stop you writing only a "few hundred" rows in a sheet?
To fix your problem with too many fonts, see my xlwt.easyxf() suggestion below.

import csv, glob, xlwt, sys, os
csvFiles = os.path.join(LogFileFolder, "*")
wb = xlwt.Workbook()
colNames = ['iNFADS_FAC','CAT','Crosswalk_FAC','FAC']
for filename in glob.glob(csvFiles):
    (f_path, f_name) = os.path.split(filename)
    (f_short_name, f_extension) = os.path.splitext(f_name)
    ws = wb.add_sheet(f_short_name)
    with open(filename, 'rb') as csvf: 
        csvReader = csv.reader(csvf)
        for rowx, row in enumerate(csvReader): 
            for colx, value in enumerate(row):
                if value in colNames:
                    ws.write(rowx, colx, value, xlwt.easyxf(
                        "border: top medium, right medium, bottom double, left medium;" \ 
                        "font: bold on; pattern: pattern solid, fore_color pale_blue;" \ 
                        "align: vert centre, horiz centre"))

Create that xf ONCE up the top of your script, and use it many times.
Same with the other usage of xlwt.easyxf
 

[DanRivMil]

Thank you very much, that worked perfectly. Though I must confess I don't understand why moving the easyxf portion does so. As to your question, I am a very new beginner to Python scripting so please pardon my ignorance. I had just read on other forums that the "maximum number of fonts" error was usually related to a code writing  beyond the XLS row limits. I agree that I don't see where my pilfered code might have been writing beyond those limits, I just assumed I must have been missing something that was making it do so. Thank you again.

[John Machin]

Each xf  contains a Font instance. "Moving the easyxf portion" ensured that only 2 xfs (and only 2 fonts) were created.

Attempting to exceed the maximum number of rows in a worksheet raises an exception with an explicit message and hence no file is created.