Author: Mike Shkolnik
How can I export data into MS Excel workbook?
Answer:
Anyone who worked with OLE automation, know that OLE is very slowly. Especially if
you work using late binding (which have a lot of other advantages which early
binding haven't)
A reason of bad performance is the next: every command (method or property) which
you access (no matter in read or write mode) will be interpretated (a-la script). I
mean that this command must be found in table of available methods/properties by
string name and only if found, a physical memory address for execution will be
calculated.
So if your code contain a lot of access to methods/properties, your code will be
slow.
For example, you need transfer some data from Delphi application into
xls-spreadsheet. You can solve a task in two different ways (now I describe only
late binding for OLE automation and don't describe another methods):
to navigate thru own data and export every data in required cell
to prepare a variant array with copied data and apply this array with data into
desired range of cells
I must say that second method will be faster than first because you'll call less
commands from OLE object and main code will be executed without OLE automation.
Small sample: to export some StringGrid into xls-file.
1 var
2 xls, wb, Range: OLEVariant;
3 arrData: Variant;
4 begin
5 {create variant array where we'll copy our data}
6 arrData := VarArrayCreate([1, yourStringGrid.RowCount, 1,
7 yourStringGrid.ColCount], varVariant);
8
9 {fill array}
10 for i := 1 to yourStringGrid.RowCount do
11 for j := 1 to yourStringGrid.ColCount do
12 arrData[i, j] := yourStringGrid.Cells[j - 1, i - 1];
13
14 {initialize an instance of Excel}
15 xls := CreateOLEObject('Excel.Application');
16
17 {create workbook}
18 wb := xls.Workbooks.Add;
19
20 {retrieve a range where data must be placed}
21 Range := wb.WorkSheets[1].Range[wb.WorkSheets[1].Cells[1, 1],
22 wb.WorkSheets[1].Cells[yourStringGrid.RowCount, yourStringGrid.ColCount]];
23
24 {copy data from allocated variant array}
25 Range.Value := arrData;
26
27 {show Excel with our data}
28 xls.Visible := True;
29 end;
Of course, you must understand that such method is not good for large data arrays
because to allocate in memory large array is not easy task. You must find some
optimal size for data transfer (for example, to copy every 10 rows) and as result
you'll receive an optimal code both for memory use and performance.
Anyway more faster way to transfer data is not use OLE at all:-) You can use my TSMExportToXLS component from SMExport suite (http://www.scalabium.com/smehttp://www.scalabium.com/sme) for this task. There is implemented a direct xls-file creation which doesn't require installed MS Excel at all..
|
