Python - excel : writing to multiple cells takes time

Question

I'm using win32com.client to write data to an excel file. This takes too much time (the code below simulates the amount of data I want to update excel with, and it takes ~2 seconds).

Is there a way to update multiple cells (with different values) in one call rather than filling them one by one? or maybe using a different method which is more efficient?

I'm using python 2.7 and office 2010.

Here is the code:

from win32com.client import Dispatch

xlsApp   = Dispatch('Excel.Application')
xlsApp.Workbooks.Add()
xlsApp.Visible  = True
workSheet = xlsApp.Worksheets(1)

for i in range(300):
    for j in range(20):
        workSheet.Cells(i+1,j+1).Value = (i+10000)*j

if 300 updates take ~2000 ms, then 6 ms for single update seems very reasonable to me. — Axarydax, Feb 16 '11 at 08:45

codeape · Answer 1 · 2011-02-17T09:03:39.827

A few suggestions:

ScreenUpdating off, manual calculation

Try the following:

xlsApp.ScreenUpdating = False
xlsApp.Calculation = -4135 # manual
try:
    #
    worksheet = ...
    for i in range(...):
    # 
finally:
    xlsApp.ScreenUpdating = True
    xlsApp.Calculation = -4105 # automatic

Assign several cells at once

Using VBA, you can set a range's value to an array. Setting several values at once might be faster:

' VBA code
ActiveSheet.Range("A1:D1").Value = Array(1, 2, 3, 4)

I have never tried this using Python, I suggest you try something like:

worksheet.Range("A1:D1").Value = [1, 2, 3, 4]

A different approach

Consider using openpyxl or xlwt. Openpyxls lets you create .xlsx files without having Excel installed. Xlwt does the same thing for .xls files.

do you know where the -4135 and -4105 come from? What documentation did you get this from. Worked great for me, just curious — mrob, Aug 02 '17 at 14:27

score 1 · Answer 2 · answered Apr 24 '14 at 09:03

1

Note that you can set ranges via numeric adresses easily by using the following code:

cl1 = Sheet1.Cells(X1,Y1)
cl2 = Sheet1.Cells(X2,Y2)
Range = Sheet1.Range(cl1,cl2)

answered Apr 24 '14 at 09:03

user3563696

261
2
3

score 1 · Answer 3 · answered Jan 10 '12 at 00:15

used the range suggestion of the other answer, I wrote this:

def writeLineToExcel(wsh,line):
    wsh.Range( "A1:"+chr(len(line)+96).upper()+"1").Value=line

xlApp = Dispatch("Excel.Application")
xlApp.Visible = 1
xlDoc = xlApp.Workbooks.Open("test.xlsx")
wsh = xlDoc.Sheets("Sheet1")
writeLineToExcel(wsh,[1, 2, 3, 4])

you may also write multiple lines at once:

def writeLinesToExcel(wsh,lines): # assume that all lines have the same length
    wsh.Range( "A1:"+chr(len(lines)+96).upper()+str(len(lines[0]))).Value=lines

writeLinesToExcel(wsh,[ [1, 2, 3, 4],
                        [5, 6, 7, 8],
                        [9, 10,11,12],
                        [13,14,15,16],
                        ])

This only works when the line has 26 or less entries. When there are longer sets of values, the range address needs to be: `chr(len(line)/26 + 96).upper() + chr(len(line)%26 + 96).upper()` — rcriii, Aug 21 '13 at 20:52

Python - excel : writing to multiple cells takes time

3 Answers3

Linked