0

I know how to create multiple worksheets and write to them if I know in advance how many worksheets I will need. For example, this code works fine if I only have 3 worksheets. (I can use a for loop to dictate the contents of each worksheet):

import xlwt as xlwt

workbook = xlwt.Workbook()
style = xlwt.XFStyle()

ws1 = workbook.add_sheet("Worksheet 1")
ws2 = workbook.add_sheet('Worksheet 2')
ws3 = workbook.add_sheet('Worksheet 3')

# For loop to dictate contents of each worksheet here...
worksheet = ws1
worksheet.write(1, 1, 'Welcome to Worksheet 1', style)

worksheet = ws2
worksheet.write(1, 1, 'Welcome to Worksheet 2', style)

workbook.save(r'C:\Users\...\Desktop\...\mult_worksheets.xls')

However, what I want to do is gather data and create new worksheets that I can write to based on the number of datasets gathered. I won't know ahead of time how many worksheets I will need to create and write to. It may be 1 dataset or up to 10.

I know that worksheet objects are created when you call workbook.add_sheet('Name'). For example:

ws1 = workbook.add_sheet('Worksheet 1')

I need the worksheet instance "ws1" in order to write to that worksheet. How can I create only the number of worksheet instances that I need and be able to write to them?

If I gather a set of 4 datasets, I need to create 4 worksheets, with the names of the worksheets and the contents determined by the datasets.

To clarify, lets say I create a list of dataset names like this:

worksheet_names = ['CA Stats', "TX Stats", "NY Stats", "FL Stats"]

These will be used as the names of the worksheets.

And each worksheet will have its own data. For example:

worksheet_data = ['Welcome to CA Stats', "Welcome to TX Stats", "Welcome to NY Stats", "Welcome to FL Stats"]

Any help is appreciated!

Chris Nielsen
  • 839
  • 1
  • 16
  • 31

3 Answers3

1

You're looking for the enumerate() method:

import xlwt as xlwt
wb = xlwt.Workbook()
names = ['a', 'b', 'c', 'd']
dataset = ['100', '200', '300', '400']
for name in names:
    wb.add_sheet(name)
for n, data in enumerate(dataset):
    ws = wb.get_sheet(n)
    ws.write(1, 1, data)
    # now do more things with ws if you like

The main thing is that the names and dataset are pairwise-ordered the same way (so 'b' is the name of the sheet which will have the data '200' in it).

melipone
  • 36
  • 3
  • I tried this, but don't know how to make it work. For example, I set dataset like so: dataset = ['Prod1 Stats', "Prod2 Stats", "Prod3 Stats", "Prod4 Stats"] It does make 4 empty worksheets, "Worksheet 1", "Worksheet 2", "Worksheet 3", "Worksheet 4" but I don't know how to write different data to each worksheet. and the worksheets should be named 'Prod1 Stats', 'Prod2 Stats', 'Prod3 Stats', 'Prod4 Stats', based on names gathered from the datasets.. – Chris Nielsen Mar 20 '14 at 15:34
  • Your actual _data_ needs to be in a list/iterable. So if they are files you could have a list of filepaths and call `os.path.open(data)` for each item in the list, read the data in the file, and write it to the corresponding worksheet with ws.write(). I'll update my example now. – melipone Mar 20 '14 at 15:37
  • don't know why I wrote os.path.open(), not even sure that method exists. I mean open() :) – melipone Mar 20 '14 at 15:49
  • I should clarify that the worksheet names will come from the datasets, for example, instead of "Prod1 Stats", I will actually have something like "CA Stats". Sorry, I didn't know the numbers in my example would mislead. I edited the question above to clarify. – Chris Nielsen Mar 20 '14 at 15:56
  • Sounds like it's time to learn about [dictionaries](http://docs.python.org/2/tutorial/datastructures.html#dictionaries)! Instead of using enumerate, put your data in a dictionary and iterate over that. Your dictionary could look like `{'CA Stats': 'Welcome to CA Stats', 'TX Stats' : 'Welcome to TX Stats' #etc}` and you can iterate with `for name, data in dataset.iteritems()`. The bottom line is, you need to count how many data sets you've got and access them using wb.get_sheet(index) to dynamically access the worksheet. – melipone Mar 20 '14 at 16:04
  • Yes, I have tried using a dict to generate the Worksheet names. The problem is, a Worksheet instance is not a string. So I can create worksheets with different names, but then I can't write to them because I need to be able to access the worksheet instances. – Chris Nielsen Mar 20 '14 at 16:09
  • "you need to count how many data sets you've got and access them using wb.get_sheet(index) to dynamically access the worksheet" Can you show me how to do this? Dict, List, it doesn't matter. Lets say I have a list of 4 worksheet names, and another list of 4 strings, one for each worksheet. Can you show me code wise how to write each string to its corresponding worksheet? – Chris Nielsen Mar 20 '14 at 16:13
  • Bingo! I think this was the key: ws = wb.get_sheet(n) This will allow me to create a dynamic number of worksheets and write to them without knowing in advance how many I will need. Thanks! – Chris Nielsen Mar 20 '14 at 16:41
0

How are you getting your dataset(s)? Why are you unable to determine how many sheets you need at the point the dataset(s) are retrieved?

Update:

An example:

dataset = [[dataset1], [dataset2], etc...]
for innerset in dataset:
    for data in innerset:
        #create worksheet
        #add data
        #close worksheet
Eugene C.
  • 495
  • 4
  • 13
  • Some tar files have multiple .gz files in them. Each .gz file contains its own data and needs to be parsed for stats reports.. I don't know ahead of time how many .gz files will be in each tar. – Chris Nielsen Mar 20 '14 at 15:20
  • It sounds as if you're manually untarring and unzipping the files. Why don't you use python's tarfile and gzip modules instead? – Eugene C. Mar 20 '14 at 15:30
  • I am using tarfile and gzip to unzip the files.. I am also parsing and generating stats data from each dataset. My question is focused solely on how to create multiple worksheets in the same workbook, the number of worksheets determined by how many datasets were pulled from the tar file. Each worksheet will have a stats report summary pulled from the data.. – Chris Nielsen Mar 20 '14 at 15:38
  • Oh, OK. As melipone said, your dataset needs to be structured into some type of iterable you can loop, e.g. a list of lists. Upon entering each index of the outer list, you'll then loop that index (which is your dataset list) and create your new worksheet, then output data into that worksheet. Basically, you'd be performing a nested loop. – Eugene C. Mar 20 '14 at 15:52
  • My script is already structured in a loop. If I can just solve the simplified problem of having a dynamically created small list of worksheet names, and a corresponding list of strings to be written into each corresponding worksheet, I should be good to go. – Chris Nielsen Mar 20 '14 at 16:00
0

Thanks to melipone, I now have the answer to my question. However, to clarify for anyone else who sees this, I want to point out that enumerate() is not germane to the question and is not necessary. Once the worksheets have been created like this:

worksheet_names = ['a', 'b', 'c', 'd']

for name in worksheet_names:
    wb.add_sheet(name)

It is possible to write to any of the worksheets in any order you want, with any data you want. All you need to know is the index position of the worksheet and it can be accessed with "ws = wb.get_sheet(index_position)". For example:

dataset = ['100', '200', '300', '400']

ws = wb.get_sheet(2)
ws.write(1, 1, dataset[2])
ws.write(2, 1, "This is something else I want to write in the third worksheet")

ws = wb.get_sheet(0)
ws.write(1, 1, "Cell 1, 1 contains data from a dict")
ws.write(2, 1, "This is something else I want to write in the first worksheet")

ws = wb.get_sheet(1)
ws.write(1, 1, "The data in this cell comes from a different dict")
ws.write(2, 1, "Write this in the second worksheet")

ws = wb.get_sheet(3)
ws.write(1, 1, "This is the 4th worksheet")
Chris Nielsen
  • 839
  • 1
  • 16
  • 31