Python: Print output on multiple Excel sheets

Question

I have 4 lists each having 33 values each and wish to print the combination in excel. Excel limits the number of rows in each sheet to 1,048,576 and the number of combinations exceeds the sheet limit by 137,345 values.

How should I continue printing the result in next sheet in the same workbook?

a = [100, 101, 102,...,133]
b = [250, 251, 252,...,283]
c = [300, 301, 302,...,333]
d = [430, 431, 432,...,463]

list_combined = [(p,q,r,s)   for p in a 
                             for q in b 
                             for r in c 
                             for s in d]

import xlsxwriter

workbook = xlsxwriter.Workbook('combined.xlsx')

worksheet = workbook.add_worksheet()

for row, group in enumerate(list_combined):
    for col in range(5):
        worksheet.write (row, col, group[col])
workbook.close()

When you mean "print a list into excel" you mean to _write_ the data into an excel file, right? — , Sep 02 '18 at 21:18
Possible duplicate of [Python XlsxWriter - Write to many sheets](https://stackoverflow.com/questions/37522394/python-xlsxwriter-write-to-many-sheets) — , Sep 02 '18 at 21:22
Also you are mentioning 4 lists that you do not use, and then make refernce to variables called `list1` and so without showing their definition. Are you not missing sth out there? — , Sep 02 '18 at 21:26
Please, just look: https://www.google.com/search?q=xlswriter+python+write+multiple+sheets — , Sep 02 '18 at 21:27
@J.C.Rocamonde I wish to print the result derived in list_combined on to excel. — user7970547, Sep 03 '18 at 17:04
I think either my answer or the other user's have already tackled such problem — , Sep 03 '18 at 17:14
We are telling you how to print into multiple sheets. How you divide your data is a decision you have to make by thinking, not a problem we have to solve. SO is for particular programming issues. — , Sep 03 '18 at 17:15
You asked: how to dump data into multiple sheets (same file); we answered. Now, the data you save and how you save it depends on what you want to do. — , Sep 03 '18 at 17:15

score 0 · Answer 1 · 2018-09-03T17:25:17.063

First, Python calls need to place the parenthesis just after the name. Spaces are not allowed:

~~worksheet.write (row, col, group[col])~~ worksheet.write(row, col, group[col])

Second, to write into multiple sheets, you need to do as follows: Example taken from this SO answer

import xlsxwriter
list_name = ["first sheet", "second sheet", "third sheet"]

workbook = xlsxwriter.Workbook(<Your full path>)
for sheet_name in list_name:
    worksheet = workbook.add_worksheet(sheet_name)
    worksheet.write('A1', sheet_name)

workbook.close()

If you do not want to pass any name to the sheet, remove the sheet_name argument, and a default name will be given.

To split data into sheets you can easily adapt the code into:

for piece in iterable_data_set:
    # consider "piece" a piece of data you want to put into each sheet
    # `piece` must be an nxm matrix that contains dumpable data.
    worksheet = workbook.add_worksheet()
    for i in range(len(piece)):
        for j in range(len(piece[i])):
            worksheet.write(i, j, piece[i][j])

I recommend you first look for the answer to your question to avoid duplicate answers. If once looking for them none solve your problem, then you can go and ask it, also telling how your problem is different from others found in other questions.

score 0 · Answer 2 · answered Sep 03 '18 at 09:01

You could set an upper limit and switch to a new worksheet once you get to the limit.

Here is an example with a lower limit than the limit supported by Excel for testing:

import xlsxwriter

workbook = xlsxwriter.Workbook('test.xlsx')
worksheet = workbook.add_worksheet()

# Simulate a big list
biglist = range(1, 1001)

# Set max_row to your required limit. Zero indexed.
max_row = 100
row_num = 0

for data in biglist:

    # If we hit the upper limit then create and switch to a new worksheet
    # and reset the row counter.
    if row_num == max_row:
        worksheet = workbook.add_worksheet()
        row_num = 0

    worksheet.write(row_num, 0, data)
    row_num += 1

workbook.close()

Output:

You asked “How should I continue printing in the next sheet in the same workbook?” The example shows how. Just replace `biglist` with your list and `max_row` with the Excel max -1. — jmcnamara, Sep 03 '18 at 17:29

Python: Print output on multiple Excel sheets

2 Answers2