Articles   Members Online:
-Article/Tip Search
-News Group Search over 21 Million news group articles.
Member Area
-Account Center
-Top 10 NEW!!
-Submit Article/Tip
-Forums Upgraded!!
-My Articles
-Edit Information
-Become a Member
-Why sign up!
-Chat Online!
-Indexes NEW!!
-Build your resume
-Find a job
-Post a job
-Resume Search
-Link to us
Visit Embarcadero
Embarcadero Community
Fast data transfer to MS Excel Turn on/off line numbers in source code. Switch to Orginial background IDE or DSP color Comment or reply to this aritlce/tip for discussion. Bookmark this article to my favorite article(s). Print this article
Delphi 2.x
User Rating
No Votes
# Votes
DSP, Administrator
Reference URL:
			Author: Mike Shkolnik

How can I export data into MS Excel workbook?


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 

So if your code contain a lot of access to methods/properties, your code will be 

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);
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];
14    {initialize an instance of Excel}
15    xls := CreateOLEObject('Excel.Application');
17    {create workbook}
18    wb := xls.Workbooks.Add;
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]];
24    {copy data from allocated variant array}
25    Range.Value := arrData;
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 ( for this task. There is implemented a direct xls-file creation which doesn't require installed MS Excel at all.. 

Vote: How useful do you find this Article/Tip?
Bad Excellent
1 2 3 4 5 6 7 8 9 10


Share this page
Download from Google

Copyright © Mendozi Enterprises LLC