Search code examples
pythonexcelcsvxlsxwriterworksheet

How can I iterate through Excel Worksheets that aren't explicitly named using XlsxWriter?

I'm a total novice when it comes to programming. I'm trying to write a Python 3 program that will produce an Excel workbook based on the contents of a CSV file. So far, I understand how to create the workbook, and I'm able to dynamically create worksheets based on the contents of the CSV file, but I'm having trouble writing to each individual worksheet.

Note, in the example that follows, I'm providing a static list, but my program dynamically creates a list of names based on the contents of the CSV file: the number of names that will be appended to the list varies from 1 to 60, depending on the assay in question.

import xlsxwriter

workbook = xlsxwriter.Workbook('C:\\Users\\Jabocus\\Desktop\\Workbook.xlsx')
list = ["a", "b", "c", "d"]

for x in list:
    worksheet = workbook.add_worksheet(x)
    worksheet.write("A1", "Hello!")

workbook.close()

If I run the program as it appears above, I get a SyntaxError, and IPython points to workbook.close() as the source of the problem.

However, if I exclude the line where I try to write "Hello!" to cell A1 in every worksheet, the program runs as I'd expect: I end up with Workbook.xlsx on my desktop, and it has 4 worksheets named a, b, c, and d.

The for loop seemed like a good choice to me, because my program will need to handle a variety of CSV formats (I'd rather write one program that can process data from every assay at my lab than a program for each assay).

My hope was that by using worksheet.write() in the way that I did, Python would know that I want to write to the worksheet that I just created (i.e. I thought worksheet would act as the name for each worksheet during each iteration of the loop despite explicitly naming each worksheet something new).

It feels like the iteration is the problem here, and I know that it has something to do with how I'm trying to reference each Worksheet in the write() step (because I'm not giving any of the Worksheet objects an explicit name), but I don't know how to proceed. What's a good way that I might approach this problem?

Solution

  • I'm not sure exactly what is wrong with your code, but I can tell you this:

    I copied your code exactly (except for changing the path to be my desktop) and it worked fine.

    I believe your issue could be one of three things:

    1. You have a buggy/old version of XlsxWriter
    2. You have a file called Workbook.xlsx on your Desktop already that is corrupted or causing some issues (open in another program.)
    3. You have some code other than what you posted.

    To account for all of these possibilities, I would recommend that you:

    1. Reinstall XlsxWriter:

      In a Command Prompt run pip uninstall XlsxWriter followed by pip install XlsxWriter

    2. Change the filename of the workbook you are opening:

      workbook = xlsxwriter.Workbook('C:\\Users\\Jabocus\\Desktop\\Workbook2.xlsx')

    3. Try running the code that you posted exactly, then incrementally add to it until it stops working.