Exporting Data to Excel from Delphi: The Trick
Anyway, I had concealed this topic somehow for several years after finalized my last project contains an Excel spreadsheet module exported from database. I knew that what I show you below is a kind of old-fashioned and everyone will know how to did it, but I wrote it just for me. Nothing more except for remind my self that I used to have a project which deal with database and Excel.The subject is Delphi, records in a database and Microsoft Excel application. It have really quite simple process: retrieve the data into a component and move the records to Excel sheet grid by grid. You may said that this one is a stupid way, but believe me… this stupid way will work great.
Let’s assumed that you have the way how to retrieve data sources, since I’ll not talk about how to do that. What I tried to explain is a method how to transfer it to Excel. Strike to the point, simply add the blank form with TDBGrid, TButton and few components got from Servers tab such as TExcelApplication, TExcelWorkBook, TExcelWorkSheet and optionally TExcelQueryTable if you plan to do more action.
Whatever the event is, just make a connection to database and display the records into TDBGrid component. Once you do that, I’ll show you the rest of the step.
TButton component purposed to trigger the opening of Microsoft Excel and transfer one by one records into it. But first, you have to catch the total number of the records and fields then use it as the looping procedure, the grid by grid data mover horizontally and vertically start from header until end of records. See below example code:
baris:=q1.RecordCount; // number of rows
kolom:=DBGrid1.FieldCount; // number of columns
objExcel := TExcelApplication.Create(nil);
objExcel.Visible[0] := TRUE;
objWB := objExcel.Workbooks.Add(null,1);
for j:=1 to kolom do
objWB.Worksheets.Application.Cells.Item[1,j]:=DBGrid1.Fields[j-1].DisplayName;
q1.First; // TQuery component
for i:=1 to baris do
begin
for j:=1 to kolom do
objWB.Worksheets.Application.Cells.Item[i+1,j]:=DBGrid1.Fields[j-1].AsString;
q1.Next;
end;
objExcel.Free;
Okay, let’s execute the application, press the button and see the result. Voila! Now you have an Excel with records imported from database directly.
Pretty simple isn’t it?
| PS: If you've benefit from this blog, you can support it by making a small contribution. |
Monday, March 21, 2011 9:36:00 PM
To my mind I have necessary knowlede for solving such situation. One of the best method would become the next tool. It helped me and some my friends for some minutes and demonstrated many other interesting facilities, general of them is ability working out other kinds of troubles with ms excel - excel 2002 font problem.
Wednesday, October 14, 2020 6:09:00 PM
I use this tool: https://www.stellarinfo.com/repair-excel-file.php to repair my Excel file.