Win32 Excel Generation Slow

[Daniel Bowett]

I am trying to create an excel document that displays a table of data.
It does exactly what I want but takes a long time. I am writing around
1000 rows and it takes around a second to do each row.

Is there a quicker way to write this? The reason I want excel is this
needs to read and manipulated by management.

The function I am using is:

def createExcel(data):
xlApp = Dispatch("Excel.Application")
wb = xlApp.Workbooks.Add()
xlApp.Visible = 1
ws = wb.Worksheets[0];

headers = ["Sales Rank", "UPC", "Description", "Stock", "Manifest
Stock", "Total Stock", "Week Sales", "Price", "Total Price", "Days Cover"]

column = 1
for each in headers:
xlApp.ActiveSheet.Cells(1, column).Value = each
column = column + 1

row = 1
for eachline in data:
xlApp.ActiveSheet.Cells(row, 1).Value = row
xlApp.ActiveSheet.Cells(row, 2).Value = eachline[0]
xlApp.ActiveSheet.Cells(row, 3).Value = eachline[1]
xlApp.ActiveSheet.Cells(row, 4).Value = eachline[2]
xlApp.ActiveSheet.Cells(row, 5).Value = eachline[3]
xlApp.ActiveSheet.Cells(row, 6).Value = eachline[4]
xlApp.ActiveSheet.Cells(row, 7).Value = eachline[5]
xlApp.ActiveSheet.Cells(row, 8).Value = eachline[6]
xlApp.ActiveSheet.Cells(row, 9).Value = eachline[7]
xlApp.ActiveSheet.Cells(row, 10).Value = eachline[8]
row = row + 1

 

[Roger Upole]

I am trying to create an excel document that displays a table of data. It does exactly what I want but takes a long time. I am
writing around 1000 rows and it takes around a second to do each row.

Is there a quicker way to write this? The reason I want excel is this needs to read and manipulated by management.

The function I am using is:

def createExcel(data):
xlApp = Dispatch("Excel.Application")
wb = xlApp.Workbooks.Add()
xlApp.Visible = 1
ws = wb.Worksheets[0];

headers = ["Sales Rank", "UPC", "Description", "Stock", "Manifest Stock", "Total Stock", "Week Sales", "Price", "Total Price",
"Days Cover"]

column = 1
for each in headers:
xlApp.ActiveSheet.Cells(1, column).Value = each
column = column + 1

row = 1
for eachline in data:
xlApp.ActiveSheet.Cells(row, 1).Value = row
xlApp.ActiveSheet.Cells(row, 2).Value = eachline[0]
xlApp.ActiveSheet.Cells(row, 3).Value = eachline[1]
xlApp.ActiveSheet.Cells(row, 4).Value = eachline[2]
xlApp.ActiveSheet.Cells(row, 5).Value = eachline[3]
xlApp.ActiveSheet.Cells(row, 6).Value = eachline[4]
xlApp.ActiveSheet.Cells(row, 7).Value = eachline[5]
xlApp.ActiveSheet.Cells(row, 8).Value = eachline[6]
xlApp.ActiveSheet.Cells(row, 9).Value = eachline[7]
xlApp.ActiveSheet.Cells(row, 10).Value = eachline[8] row = row + 1

If you preformat the data including the row number, you can
insert it en masse using a Range object. This runs in just a
couple of seconds:

from win32com.client import Dispatch
data=[(x,'data1','data2','data3','data4','data5','data6','data7','data8','data9') for x in xrange(1000)]


def createExcel(data):
xlApp = Dispatch("Excel.Application")
wb = xlApp.Workbooks.Add()
xlApp.Visible = 1
ws = wb.Worksheets[0];

headers = ["Sales Rank", "UPC", "Description", "Stock", "Manifest Stock", "Total Stock", "Week Sales", "Price", "Total
Price", "Days Cover"]

column = 1
for each in headers:
xlApp.ActiveSheet.Cells(1, column).Value = each
column = column + 1
xlApp.ActiveSheet.Range("A2:J1001").Value=data

createExcel(data)


Roger

 

[Paul McGuire]

I am trying to create an excel document that displays a table of data. It
does exactly what I want but takes a long time. I am writing around 1000
rows and it takes around a second to do each row.

Is there a quicker way to write this? The reason I want excel is this
needs to read and manipulated by management.

Are there many many formulas in your worksheet? Try setting calculate to
manual, and turn off screenupdating while creating your rows. Then when
done, do a manual calculate, and turn auto calc and screenupdating back on.
(These are all open to the COM interface, although I don't recall the exact
function names.)

-- Paul