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(dat a):
xlApp = Dispatch("Excel .Application")
wb = xlApp.Workbooks .Add()
xlApp.Visible = 1
ws = wb.Worksheets[0];

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

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

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

[Roger Upole]

"Daniel Bowett" <da****@bowetts olutions.comwro te in message news:ma******** *************** *************** @python.org...

>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(dat a):
xlApp = Dispatch("Excel .Application")
wb = xlApp.Workbooks .Add()
xlApp.Visible = 1
ws = wb.Worksheets[0];

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

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

row = 1
for eachline in data:
xlApp.ActiveShe et.Cells(row, 1).Value = row
xlApp.ActiveShe et.Cells(row, 2).Value = eachline[0]
xlApp.ActiveShe et.Cells(row, 3).Value = eachline[1]
xlApp.ActiveShe et.Cells(row, 4).Value = eachline[2]
xlApp.ActiveShe et.Cells(row, 5).Value = eachline[3]
xlApp.ActiveShe et.Cells(row, 6).Value = eachline[4]
xlApp.ActiveShe et.Cells(row, 7).Value = eachline[5]
xlApp.ActiveShe et.Cells(row, 8).Value = eachline[6]
xlApp.ActiveShe et.Cells(row, 9).Value = eachline[7]
xlApp.ActiveShe et.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','dat a2','data3','da ta4','data5','d ata6','data7',' data8','data9') for x in xrange(1000)]
def createExcel(dat a):
xlApp = Dispatch("Excel .Application")
wb = xlApp.Workbooks .Add()
xlApp.Visible = 1
ws = wb.Worksheets[0];

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

column = 1
for each in headers:
xlApp.ActiveShe et.Cells(1, column).Value = each
column = column + 1
xlApp.ActiveShe et.Range("A2:J1 001").Value=dat a

createExcel(dat a)
Roger

----== Posted via Newsfeeds.Com - Unlimited-Unrestricted-Secure Usenet News==----
http://www.newsfeeds.com The #1 Newsgroup Service in the World! 120,000+ Newsgroups
----= East and West-Coast Server Farms - Total Privacy via Encryption =----

[Paul McGuire]

"Daniel Bowett" <da****@bowetts olutions.comwro te in message
news:ma******** *************** *************** @python.org...

>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