By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,929 Members | 1,245 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,929 IT Pros & Developers. It's quick & easy.

Win32 Excel Generation Slow

P: n/a
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

Dec 1 '06 #1
Share this Question
Share on Google+
2 Replies


P: n/a

"Daniel Bowett" <da****@bowettsolutions.comwrote in message news:ma**************************************@pyth on.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(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

----== 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 =----
Dec 1 '06 #2

P: n/a
"Daniel Bowett" <da****@bowettsolutions.comwrote in message
news:ma**************************************@pyth on.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
Dec 1 '06 #3

This discussion thread is closed

Replies have been disabled for this discussion.