Python: Print output on multiple Excel sheets

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()


list1

2 Answers
2

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:

biglist

max_row

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])

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.

sheet_name

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.

Thanks for contributing an answer to Stack Overflow!

But avoid …

To learn more, see our tips on writing great answers.

Some of your past answers have not been well-received, and you're in danger of being blocked from answering.

Please pay close attention to the following guidance:

But avoid …

To learn more, see our tips on writing great answers.

Required, but never shown

Required, but never shown

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.