Mega Code Archive
Master detail report using excel
<![CDATA[
Title: Master detail report using excel
Question: How to make a master detail report using Excel
Answer:
Hi everybody!
Please excuse my English.
This is my first article.
Let me tell you that I am not a professional programmer, in fact I didnt study to be a programmer, but I like it and I am learning by myself.
I couldnt have accomplished this article with out the help from others two rticles
http://www.delphi3000.com/articles/article_1814.asp from Mekan Gara and http://www.delphi3000.com/articles/article_1282.asp from Lubomir Rosenstein,
but I needed an master detail report, So I took this articles and I started to find out how to do it and this is the result.
If you have some comments or improvements, please let me know.
1.- Start a new project
2.- Include ComObj and Excel97 units to your uses clause
3.- Drop one Progressbar and one button on the form
4.- Drop two Ttable components and one TdataSource
5.- For this example were going to use the DBDEMOS database, so tie the first table to DBDEMOS alias using Database property, and Name it Master, and choose Customer.db in TableName property.
6.- In the second Ttable component, also tie it to DBDEMOS database, and name it Detail and choose orders.db in TableName property
7.-Tie DataSource1 to Master table in Dataset property
8.- In Detail table, tie MasterSource property to DataSource1
9.- Click on Masterfields in Detail table and choose CustNo in Available indexes combo box, then select CustNo from Detail Fields, and CustNo from Master Fields, and click Add button. And you will see in Joined Field box CustNo-CustNo
click Ok button and we have our detail table ready.
10.- Set to active both Ttable components.
Double click at Ttable components, and right click over the window that show up and choose Add all records, select a field, and in Object inspector change the property Visible to True or False, depending on which fields you want to appear in the report.
The code:
unit main;
interface
uses
Windows, Messages, SysUtils, Classes, Graphics, Controls, Forms, Dialogs,
ComCtrls, StdCtrls,ComObj, Excel97, DBTables, Db;
type
TForm1 = class(TForm)
Button1: TButton;
ProgressBar1: TProgressBar;
DataSource1: TDataSource;
master: TTable;
Detalle: TTable;
DataSource2: TDataSource;
procedure ExportToExcelMasterTable,DetailTable:TDataSet;ReportTitle:String);
procedure Button1Click(Sender: TObject);
private
{ Private declarations }
public
{ Public declarations }
end;
var
Form1: TForm1;
Implementation
{$R *.DFM}
{ TForm1 }
Const
MaxCells:Array[1..25] of string[1] =('A','B','C','D','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T',
'U','V','W','X','Y','Z');
procedure TForm1.ExportToExcel(MasterTable, DetailTable: TDataSet;
ReportTitle: String);
var
Excel:Variant;
Worksheet:Variant;
HeaderLine,DataLine,H,I,J,k,Done,Detail,VisibleFields:Integer;
FromCell,ToCell:String;
begin
try
Excel:=CreateOleObject(Excel.Application); ///Open excel
Excel.Visible:=False;
Excel.WorkBooks.Add(-4167);
Excel.WorkBooks[1].Worksheets[1].Name:='Sheet1';
Worksheet:=Excel.Workbooks[1].Worksheets['Sheet1'];
except
MessageBox(handle,Can''t Open Excel','Error',MB_IconError);
Exit;
end;
HeaderLine:=2; //setup initial values
DataLine:=3;
Done:=0;
VisibleFields:=0;
MasterTable.First;
ProgressBar1.Max:=masterTable.RecordCount;
Screen.Cursor := crHourglass;
Worksheet.Range[C1,C1].Value:=ReportTitle;
while not MasterTable.Eof do begin
try
For i:=1 to MasterTable.FieldCount do
if MasterTable.Fields[i-1].Visible then begin
WorkSheet.Cells[HeaderLine,i].Value:=MasterTable.Fields[i-1].DisplayName;
WorkSheet.Cells[HeaderLine,i].ColumnWidth:=MasterTable.Fields[i-1].DisplayWidth;
Inc(VisibleFields);
end;
{here we are going to give some format to headers }
FromCell:=A+IntToStr(HeaderLine);
{You can format the entire Row, but if you do it, when you print the document you will get a lot of empty pages}
ToCell:=MaxCells[VisibleFields-1]+IntToStr(HeaderLine);
VisibleFields:=0;
{If your excel Its in Spanish like mine, change Bold to Negrita}
WorkSheet.Range[FromCell,ToCell].Font.FontStyle:='Bold';
WorkSheet.Range[FromCell,ToCell].Interior.Color:=ClSilver;
WorkSheet.Range[FromCell,ToCell].Font.Size:=09;
WorkSheet.Range[FromCell,ToCell].Borders.Weight:=Xlmedium;
WorkSheet.Range[FromCell,ToCell].HorizontalAlignment:=XlCenter;
{Ok, let's feed master data}
For h:=1 to MasterTable.FieldCount do
if MasterTable.Fields[h-1].Visible then begin
WorkSheet.Cells[Dataline,h].Value:=MasterTable.Fields[h-1].Text;
end;
Detail:=DataLine+1;
DetailTable.Close;
DetailTable.Open;
DetailTable.First;{Put detail table at the beginning}
{Headers for the detail data}
For k:=1 to DetailTable.FieldCount do begin
if DetailTable.Fields[k-1].Visible then begin
WorkSheet.Cells[Detail,k].Value:=DetailTable.Fields[k-1].DisplayName;
WorkSheet.Cells[Detail,k].Font.FontStyle:='Bold';
WorkSheet.Cells[Detail,k].HorizontalAlignment:=XlCenter;
end;
end;
Inc(Detail);
{feed detail data}
While not DetailTable.Eof do Begin
for j:=1 To DetailTable.FieldCount do
if DetailTable.Fields[j-1].Visible=True then begin
WorkSheet.Cells[Detail,J].Value:=DetailTable.Fields[j-1].Text;
end;
Inc(Detail);
DetailTable.Next; //Process next record
end;
HeaderLine:=Detail+1;
DataLine:=HeaderLine+1;
Inc(Done);
ProgressBar1.Position:=Done;
MasterTable.Next;
Application.ProcessMessages;
{If something goes wrong}
except
MessageBox(handle,'Data transfer error','Error',MB_IconError);
Excel.Visible:=True;
Screen.Cursor := crDefault;
exit;
end;
end;
Excel.Visible:=True;
Screen.Cursor := crDefault;
end;
{How to call it.}
procedure TForm1.Button1Click(Sender: TObject);
begin
ExportToExcel(Master,detil,'Cool!');
end;
end.
Thats all folks, a hope this help you.
You can download the complete project at:
http://www.mexred.com/examples/mastdet.zip
Enjoy.
]]>