Information générale
Catégorie:
Codage, syntaxe et commandes
>In my application I use the following code to create an Excel spreadsheet and populate it with the contents of an array that was created with a SQL Select into statement. Sometimes the array is large, over a 1000 records, and this process can be lengthy (5-10 minutes). Any advice on how to speed this up?
>
>Wait Window "Creating Excel Spreadsheet..." nowait
>tmpSheet = GetObject('','excel.sheet')
>XLApp = tmpsheet.application
>XLApp.WorkBooks.Add()
>XLSheet = XLApp.ActiveSheet
>XLSheet.Cells(1,1).Value = "Soc_Sec_Num"
>XLSheet.Cells(1,2).Value = "Employee_Name"
>XLSheet.Cells(1,3).Value = "Ded_Code"
>XLSheet.Cells(1,4).Value = "ER"
>XLSheet.Cells(1,5).Value = "UDDS"
>XLSheet.Cells(1,6).Value = "Fund"
>XLSheet.Cells(1,7).Value = "Act"
>XLSheet.Cells(1,8).Value = "Acct"
>FOR i = 1 to _tally
> j = i + 1
> XLSheet.Cells(j,1).Value = customized(i,1)
> XLSheet.Cells(j,2).Value = customized(i,2)
> XLSheet.Cells(j,3).Value = customized(i,3)
> XLSheet.Cells(j,4).Value = customized(i,4)
> XLSheet.Cells(j,5).Value = customized(i,5)
> XLSheet.Cells(j,6).Value = customized(i,6)
> XLSheet.Cells(j,7).Value = customized(i,7)
> XLSheet.Cells(j,8).Value = customized(i,8)
>ENDFOR
>XLApp.ActiveWindow.WindowState = 2
>XLSheet.Cells.Select
>XLSheet.Cells.EntireColumn.AutoFit
>XLSheet.Range("A1").Select
>Wait Window "Excel Spreadsheet Complete!" nowait
>RELEASE tmpSheet
>XLApp.Visible = .t.
Hello Jeff,
I have the same problem here in an app where a generic option offers download and/or uploads from Excel.
Fine but as you discovered ... pumping VFP variables into Excel thru
OLE automation is awfull time-consuming.
I tried different solutions but never was able to speed up the process :
XLSheet.Cells(i,j) = aVFPvar
As as i a am concerned the current workaround is :
1- create a temp DBF cursor,
2- COPY TO myworksheet.xls TYPE XL5
3- open the temp.xls within EXCEL
4- do the cosmetics (autofit,font,...)
5- save it
As it is a destructive process that can potentially destroy sophisticated multi-sheets documents i also offer a more time-consuming refresh option that sends Excel tons of :
XLSheet.Cells(i,j) = aVFPvar
If anyone has a better workararound i'd be glad to read. I alas don't think it is possible to "feed" Excel cells ranges with VFP array.
François
Précédent
Suivant
Répondre
Voir le fil de ce thread
Voir le fil de ce thread à partir de ce message seulement
Voir tous les messages de ce thread
Voir tous les messages de ce thread à partir de ce message seulement