How To Create Report With Ms Excel

By : M. Yadi M. This article shows  how to Create Report with Ms Excel by extracting data from Ms Sql. Here you will learn: How to Connect into MS SQL server database How to create Excel Object How to Extract MS SQL server Data into MS Excel

I will show it by creating an application, but before we go into it, please be sure that you already have Microsoft Excel installed in your PC and you have access to MS SQL Server, and be sure that you have already define table in SQL server database. For this sample I have prepared a table name TTable1 in PRACTICE database, you can have another database and table name if you like. Let’s go into the application: 1.       Open Delphi application 2.       Create new VCL Project 3.       Put Objects into the Form Object Parameter Value TAdoConnection Name AdoConnection1 ConnectionString* Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=PRACTICE;Data Source=127.0.0.1;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=ALDI;Use Encryption for Data=False;Tag with column collation when possible=False LoginPrompt False Connected True TAdoQuery Name AdoQuery1 Connection AdoConnection1 SQL select * from TTable1 TButton Name Button1 Caption Open SQL Data and Extract into Excel *) ConnectionString value could be different, depend on your Server/PC name and connection method 4.       Add comObj library 5.       Create Event OnClick for Buttion1 6.       Here is the code procedure TForm1.Button1Click(Sender: TObject); var   iXls, iWb, iRange: OLEVariant;   iArrData : Variant;   iCnt : integer; begin   // reload SQL server Data   ADOQuery1.close;   ADOQuery1.Open;   // Allocate Array Size   iArrData   := VarArrayCreate([1, ADOQuery1.RecordCount+2 , 0, ADOQuery1.FieldCount+2], varVariant);   // Put Fieldname as header row into Array   for iCnt := 0 to ADOQuery1.FieldCount-1 do    iArrData[1 , iCnt] := ADOQuery1.fields[iCnt].FieldName;   // Load SQL data into Array   while not(ADOQuery1.eof ) do   begin     for iCnt := 0 to ADOQuery1.FieldCount-1 do       iArrData[ADOQuery1.recNo+1 , iCnt] := ADOQuery1.fields[iCnt].asString;    ADOQuery1.Next;   end;   // initialize an instance of Excel   iXls := CreateOLEObject('Excel.Application');   // create workbook   iWb := iXls.Workbooks.Add;   // retrieve a range where data must be placed   iRange := iWb.WorkSheets[1].Range[iWb.WorkSheets[1].Cells[1, 1],   iWb.WorkSheets[1].Cells[ADOQuery1.RecordCount+2, ADOQuery1.FieldCount+2 ]];    // copy data from allocated variant array   iRange.Value := iArrData;   // show Excel with our data   iXls.Visible := True;        end;

7.       Lets we test the application by pressing F9 to run the application

8.       If there is no error, you can see the result as below picture

Congratulation, now you know how to create report with Ms Excel by extracting data from MS SQL.  

But Wait!!, the format doesn’t look nice isn’t it? Ok, now I will show you how to have nice format MS Excel.

The steps as follow:

1.       You have to create formatted MS Excel. My format as below picture but you can have your own format

2.       Save the file into template folder. For example my template folder is “C:\template\” and the filename is “sample.xls”

3.       Lets modify our code

First add local variable to define template file name

        Then replace code line as below

Done !, lets Re-Run the application again, if no error the result as below

Now the format is better. You can play with the format as you want and you can combine your template file with Excel-VBA. for example you can have below format :

It’s easy right?  You can have your own MS Excel format.

Related product you might see: