CODEWITHSUNDEEP
excelpython-2.7csvxlsxwriter
user2771721
user2771721

Reputation: 502

Python xlsxwriter module: Iterating through same set of format and conditions on multiple worksheets

Using the xlsxwriter module, I created a Python script that would copy a csv file content to an excel worksheet. A second worksheet is then be created with lookups, calculations, conditioining formatting - a template - based on the first worksheet. No problems.

My issue starts when I try to loop the code, i.e. copy 3 csv file content into 3 worksheets in 1 workbook, and then create 3 more worksheets that applies the same template, but looking up based on different worksheet. Here's a snippet of the 'copy csv to excel worksheet' code:

workbook = Workbook(csvFile[:-4] + '.xlsx', {'strings_to_numbers': True})
align = workbook.add_format({'align':'center','text_wrap':True})
for csvfile in (glob.glob(filepath + '\\*.csv')):                
    worksheet = workbook.add_worksheet(header[2])
    with open(csvfile, 'rb') as f:
        reader = csv.reader(f)
        for r, row in enumerate(reader):
            for c, col in enumerate(row):
                worksheet.set_column('A:A',65)
                worksheet.set_column('B:P',12)
                worksheet.write(r, c, col, align)

I want to do something like this:

workbook = Workbook(csvFile[:-4] + '.xlsx', {'strings_to_numbers': True})
align = workbook.add_format({'align':'center','text_wrap':True})
for i, csvfile in (glob.glob(filepath + '\\*.csv')):   
    worksheet[i] = workbook.add_worksheet(header[i])             
    with open(csvfile, 'rb') as f:
        reader = csv.reader(f)
        for r, row in enumerate(reader):
            for c, col in enumerate(row):
                worksheet[i].set_column('A:A',65)
                worksheet[i].set_column('B:P',12)
                worksheet[i].write(r, c, col, align)

xlsxwriter module doesn't allow me to do that. The code above would throw the following error:

File "C:\workspace\automagic.py", line 103, in create_xls
worksheet[i] = workbook.add_worksheet(header[i]) 
NameError: global name 'worksheet' is not defined

I would hate to have to copy and paste the same code with just the worksheet names changed.

UPDATE: Taking jmcnamara's comment into consideration, I was able to modify my code as shown below:

worksheet = ['worksheet0','worksheet1','worksheet2']
workbook = Workbook(csvFile[:-4] + '.xlsx', {'strings_to_numbers': True})
align = workbook.add_format({'align':'center','text_wrap':True})    

for i, csvfile in enumerate(glob.glob(filepath + '\\*.csv')):  
    testtype = csvfile.split('.')[3].split('_')[1]
    worksheet[i] = workbook.add_worksheet(testtype)
    with open(csvfile, 'rb') as f:
            reader = csv.reader(f)
            for r, row in enumerate(reader):
                for c, col in enumerate(row):
                    worksheet[i].set_column('A:A',65)
                    worksheet[i].set_column('B:P',12)
                    worksheet[i].write(r, c, col, align)

Upvotes: 2

Views: 1761

Answers (1)

jmcnamara
jmcnamara

Reputation: 41574

xlsxwriter module doesn't allow me to do that. The code above would throw the following error

That isn't an XlsxWriter error it is a general python error. You get the same error from the following:

$ cat error_test.py

def some_func():
    i = 1
    worksheet[i] = 123

some_func()


$ python error_test.py
Traceback (most recent call last):
  File "error_test.py", line 7, in <module>
    some_func()
  File "error_test.py", line 5, in some_func
    worksheet[i] = 123
NameError: global name 'worksheet' is not defined

You need to declare the variable worksheet somewhere and probably use worksheet.append() as well.

Upvotes: 2

Related Questions