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

Answer 1

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.

Answer 2

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