Writing array with pywin32 to excel

Question

I am trying to write an array into Excel. The code snippet is below.

import win32com.client as win32 import sys import numpy as np #-------------- if name=="main":

name="nlve9.xlsm"
excel=win32.Dispatch('Excel.Application')
sheet=excel.Workbooks(name).Worksheets('NLVE')
#--------------
testress=np.zeros(5000)
for i in range(0,5000):
    testress[i]=float(i)

sheet.Range("AC18:AC5016").value=testress
#excel.ScreenUpdating = False
#for i in range(18,5017):
#    sheet.Range("AC" + str(i)).value=testress[i-18]
#excel.ScreenUpdating = True
sys.exit()

When this runs, I get a column of zero the length of testress.  When I replace the last line with below it works but it is excruciatingly slow.  THs is part of an optimization problem so this will run hundreds of times. Hence, I need this to be fast.

for i in range(18,5017):
    #    sheet.Range("AC" + str(i)).value=testress[i-18]

What am I doing wrong with the first method(sheet.Range("AC18:AC5016").value=testress)?

Answer 1

If you are using the Range.Value property to set an array, Excel needs a 2D array of row & column values, even if your data is a 1D array.

[[r1c1,r1c2,...],[r2c1,r2c2,...] ...]

As an example:

import win32com.client as wc

xl = wc.gencache.EnsureDispatch('Excel.Application')
xl.Visible = True

wb = xl.Workbooks.Add()
sh = wb.Sheets[1]

sh.Range('A1:A10').Value = [[i] for i in range(10)]

yielding:

EDIT:

From the OP's code, change:

testress=np.zeros(5000)
for i in range(0,5000):
    testress[i]=float(i)

sheet.Range("AC18:AC5016").value=testress

to:

rowCount = 5000
testress = [[i] for i in range(rowCount)]
sheet.Range('AC18:AC'+str(18+rowCount-1)).Value = testress