Creating an Excel File using Delphi

In my opinion, knowing how to create an Excel file using Delphi is very important. Especially when you work with a database, sometimes - not always - you need to make a report in an Excel file. To do this, you have to use Object Linking and Embedding (OLE) function. OLE function will link to or embed data from other applications (servers) in another application (the client). In this case, MS Excel is the server application and our Delphi project is the client application.

Before we go further, create a user interface like picture below :

The OLE function for creating an Excel application is :

function CreateOleObject(const ClassName: string)

This function is in ComObj unit. Add this unit into the uses declaration in the program. Then type the codes below :

unit Unit1;

interface

uses

  Windows, Messages, SysUtils, Classes, Graphics, Controls, Forms, Dialogs,

  StdCtrls, ComObj;

type

  TForm1 = class(TForm)

    CreateExcel: TButton;

    Exit: TButton;

    procedure ExitClick(Sender: TObject);

    procedure CreateExcelClick(Sender: TObject);

  private

    { Private declarations }

  public

    { Public declarations }

  end;

var

  Form1: TForm1;

implementation

{$R *.DFM}

procedure TForm1.CreateExcelClick(Sender: TObject);

var

 ExcelApplication : variant;

 Sheet : variant;

begin

 try

  begin

   ExcelApplication := CreateOleObject('Excel.Application');

   ExcelApplication.Visible := true;

  end;

 except

  Showmessage('Cannot create an Excel file, make sure that MS Excel is installed on your system');

  Application.Terminate;

 end;

 ExcelApplication.WorkBooks.Add(-4167);    

 ExcelApplication.WorkBooks[1].WorkSheets[1].Name := 'my data';

 Sheet := ExcelApplication.WorkBooks[1].WorkSheets['my data'];

end;

procedure TForm1.ExitClick(Sender: TObject);

begin

 Application.Terminate;

end;

end.

The explanation :

Action	Codes
Declare two variables : ExcelApplication and Sheet as variant	ExcelApplication : variant; Sheet : variant;
Create an MS Excel application using try…except… clause and CreateOleObject function. Then make it visible	ExcelApplication := CreateOleObject('Excel.Application'); ExcelApplication.Visible := true;
Create a blank Excel workbook	ExcelApplication.WorkBooks.Add(-4167);
Create a blank Excel worksheet	ExcelApplication.WorkBooks[1].WorkSheets[1].Name := 'my data';
Set the working worksheet into Sheet variable	Sheet := ExcelApplication.WorkBooks[1].WorkSheets['my data'];

Why do we need to set the working worksheet into Sheet variable ? It is in order to avoid typing a long character string : ExcelApplication.WorkBooks[1].WorkSheets['my data'] to access the worksheet.

Go to Next Tutorial ( Setting Attributes of an Excel File )