Write Excel sheet with Delphi

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 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 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 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 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 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 from database directly.