Python output in Excel

Question

Trying to get the output of the cartesian product in excel using xlsxwriter and arrange them column-wise but have not been successful.

Intended Result:

If the first output is (A,B,C,D,E) the output should be displayed in excel in the following manner:

Row 0, Col 0 = A

Row 0, Col 1 = B

Row 0, Col 2 = C

Row 0, Col 3 = D

Row 0, Col 4 = E

then row+=1 and the next set of results are displayed in the same manner.

Code:

list1 = ['A', 'B', 'C', 'D', 'E', 'F']
list2 = ['A', 'B', 'C', 'D', 'E', 'F', 'G', 'H','I']
list3 = ['A', 'B', 'C', 'D', 'E']
list4 = ['A', 'B', 'C', 'D']
list5 = ['A', 'B', 'C', 'D', 'E', 'F', 'G', 'H','I','J','K','L','M']

list = [(p,q,r,s,t) for p in list1 for q in list2 for r in list3 for s in 
        list4 for t in list5]

x = print(list)

import xlsxwriter
workbook = xlsxwriter.workbook('Stat.xlsx')
worksheet = workbook.add_worksheet()

row = 0
col = 0

for Apple, Boy, Cat, Dog, Eagle in (x):
  worksheet.write (row, col, Apple)
  worksheet.write(row, col + 1, Boy)
  worksheet.write(row, col + 1, Cat)
  worksheet.write(row, col + 1, Dog)
  worksheet.write(row, col + 1, Eagle)
  row += 1
workbook.close()

did you try see what x contains? When I run your code it's just a string containing <generator object <genexpr> at 0x000001896A293360> which is evidently quite wrong — Dan
– Dan, Commented Aug 15, 2018 at 16:59
list = [(p,q,r,s,t) for p in list1 for q in list2 for r in list3 for s in list4 for t in list5] — user7970547
– user7970547, Commented Aug 15, 2018 at 17:04
It looks like you aren't incrementing the col value in your for loop. They are all (except Apple) writing to the same column as a result. If you defined column inside the for loop and had an col+=1 in between all of those writes it would put them in different columns. You could also just hardcode the column numbers. — my Year Of Code
– my Year Of Code, Commented Aug 15, 2018 at 17:07
Tried with the changes and now it says TypeError: 'module' object is not callable. E1102:xlsxwriter.workbook is not callable. Am I missing something? — user7970547
– user7970547, Commented Aug 15, 2018 at 17:08

Dan · Accepted Answer · 2018-08-17 08:48:59Z

2

Does this do what you want?

list1 = ['A', 'B', 'C', 'D', 'E', 'F']
list2 = ['A', 'B', 'C', 'D', 'E', 'F', 'G', 'H','I']
list3 = ['A', 'B', 'C', 'D', 'E']
list4 = ['A', 'B', 'C', 'D']
list5 = ['A', 'B', 'C', 'D', 'E', 'F', 'G', 'H','I','J','K','L','M']

# You probably could use product from itertools for this: https://docs.python.org/2/library/itertools.html#itertools.product
list_combined = [(p,q,r,s,t) for p in list1 
                             for q in list2 
                             for r in list3 
                             for s in list4 
                             for t in list5]

import xlsxwriter
workbook = xlsxwriter.Workbook('Stat.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()

edited Aug 17, 2018 at 8:48

answered Aug 15, 2018 at 17:30

Dan

45.8k20 gold badges98 silver badges169 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

user7970547 Over a year ago

Tried running it but it says E1102:xlsxwriter.workbook is not callable.

Dan Over a year ago

Looking at the docs, it should be workbook with a capital W. Try my edit

user7970547 Over a year ago

That was amazing. Thank You so much. :)

user7970547 Over a year ago

Hey @Dan can you look into this: stackoverflow.com/questions/51898765/…

Collectives™ on Stack Overflow

Python output in Excel

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related