Delphi programming to quickly import database data into Excel

In developing database applications, you often export the same type of data to Excel files, and use Excel's powerful editing capabilities to further process the data. There are many ways in which we can use OLE technology to create an Automation object in Delphi that transmits data through that object. You can also use ADO to export data from a dataset using ADO, a technology that is independent of the database backend, by establishing a connection to the Excel data store.

But both of these technologies have a common disadvantage, that is slow, the amount of data is not good, users will not have too much feeling, but once the data volume, for example, more than 1000, the speed is unbearable, then there is no better way, both can quickly export data, without installing additional software. Maybe a lot of people think of the way the Clipboard, this way speed is fast, but also have a bad side, that is the large amount of data consumption memory, and in Excel call the Paste method, you need to lock input, this use, it is a little inconvenient

I'm here for you. A better way to use file flow is to write directly to the Excel file via TFileStream. I wrote a function that allows data in a dataset to be imported directly into an Excel file. I tested the 1M data and finished it in less than 10 seconds. Attached source program.

First, define the following arrays in your program:

arXlsBegin: array[0..5] of Word = ($809, 8, 0, $10, 0, 0);
arXlsEnd: array[0..1] of Word = ($0A, 00);
arXlsString: array[0..5] of Word = ($204, 0, 0, 0, 0, 0);
arXlsNumber: array[0..4] of Word = ($203, 14, 0, 0, 0);
arXlsInteger: array[0..4] of Word = ($27E, 10, 0, 0, 0);
arXlsBlank: array[0..4] of Word = ($201, 6, 0, 0, $17);

Then call the following function.

Procedure Exportexcelfile (filename:string; bwritetitle:boolean; adataset:tdataset);


var


I, J:integer;


Col, Row:word;


Abookmark:tbookmark;


Afilestream:tfilestream;


procedure Inccolrow; Increase row number


begin


if Col = Adataset.fieldcount-1 then


begin


INC (Row);


Col: = 0;


End


Else


Inc. (COL);


end;


procedure Writestringcell (avalue:string);//write String data


var


L:word;


begin


L: = Length (Avalue);


arxlsstring[1]: = 8 + L;


arxlsstring[2]: = Row;


arxlsstring[3]: = Col;


arxlsstring[5]: = L;


Afilestream.writebuffer (arxlsstring, SizeOf (arxlsstring));


afilestream.writebuffer (pointer (avalue) ^, L);


Inccolrow;


end;


procedure Writeintegercell (Avalue:integer);//write Integer


var


V:integer;


begin


arxlsinteger[2]: = Row;


arxlsinteger[3]: = Col;


Afilestream.writebuffer (Arxlsinteger, SizeOf (Arxlsinteger));


V: = (avalue SHL 2) or 2;


Afilestream.writebuffer (V, 4);


Inccolrow;


end;


procedure Writefloatcell (avalue:double);//write floating-point number


begin


arxlsnumber[2]: = Row;


arxlsnumber[3]: = Col;


Afilestream.writebuffer (Arxlsnumber, SizeOf (Arxlsnumber));


Afilestream.writebuffer (Avalue, 8);


Inccolrow;


end;


begin


if fileexists (filename) then deletefile (filename); File exists, delete
first

Afilestream: = Tfilestream.create (FileName, fmcreate);


Try


//write file header


Afilestream.writebuffer (Arxlsbegin, SizeOf (Arxlsbegin));


//Write column header


Col: = 0; Row: = 0;


if Bwritetitle then


begin


for I: = 0 to Adataset.fieldcount-1 do


Writestringcell (adataset.fields[i). FieldName);


end;


//write data in a dataset


Adataset.disablecontrols;


Abookmark: = Adataset.getbookmark;


Adataset.first;


while does adataset.eof do


begin


for I: = 0 to Adataset.fieldcount-1 do


Case Adataset.fields[i]. DataType of


Ftsmallint, Ftinteger, Ftword, Ftautoinc, ftbytes:


Writeintegercell (adataset.fields[i). Asinteger);


ftfloat, Ftcurrency, FTBCD:


Writefloatcell (adataset.fields[i). Asfloat)


Else


Writestringcell (Adataset.fields[i]. asstring);


end;


Adataset.next;


end;


//write file Tail


Afilestream.writebuffer (Arxlsend, SizeOf (arxlsend));


if Adataset.bookmarkvalid (Abookmark) then Adataset.gotobookmark (Abookmark);


Finally


Afilestream.free;


Adataset.enablecontrols;


end;


end;

The above procedure, passes the test in the DELPHI6!