Win32 Excel Generation Slow

[Roger Upole]

"Daniel Bowett" <daniel at bowettsolutions.com> wrote in message news:mailman.928.1164969844.32031.python-list at 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(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 =----