How to populate a list in excel using win32com module

Question

I am trying to write lists into excel, the list contains a couple of columns.

I have included my code below. It seems to only extract the first value. I am fairly new to python, what am I missing?

Code:

import win32com.client as win32
#
Z = [3,4,6,8,9,11,40]
Q = ['x','y','z','e','g','AA','BB']

excel = win32.gencache.EnsureDispatch('Excel.Application')
wb = excel.Workbooks.Add()
ws = wb.Worksheets.Add()
ws.Name = "MyNewSheet"
ws.Range(ws.Cells(1,1),ws.Cells(1,2)).Value = ['Z','Q']
for i,e in enumerate (Z):
ws.Range("A2:A8").Value = [ i for i in (Z)]
for i,e in enumerate (Z):
ws.Range("B2:B8").Value = [ i for i in (Q)]
wb.SaveAs('beta.xlsx')
excel.Application.Quit()

Stephen Rauch · Accepted Answer · 2017-03-06 04:38:01Z

2

It appears that it wants to write the values as rows. So, this re-aligns the writes to be by row:

Code:

import win32com.client as win32

Z = [3, 4, 6, 8, 9, 11, 40]
Q = ['x', 'y', 'z', 'e', 'g', 'AA', 'BB']

excel = win32.gencache.EnsureDispatch('Excel.Application')
wb = excel.Workbooks.Add()
ws = wb.Worksheets.Add()
ws.Name = "MyNewSheet"
ws.Range("A1:B1").Value = ['Z', 'Q']
ws.Range("A2:B8").Value = list(zip(Z, Q))
wb.SaveAs('beta.xlsx')
excel.Application.Quit()

Results:

Z   Q
3   x
4   y
6   z
8   e
9   g
11  AA
40  BB

answered Mar 6, 2017 at 4:38

Stephen Rauch♦

50.1k32 gold badges118 silver badges143 bronze badges

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

1 Comment

HAMIN Over a year ago

Thanks, that's exactly what I wanted.

Collectives™ on Stack Overflow

How to populate a list in excel using win32com module

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related