Monday 5 April 2010

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 :

ActionCodes
Declare two variables : ExcelApplication and Sheet as variantExcelApplication : variant;
Sheet : variant;
Create an MS Excel application using try…except… clause and CreateOleObject function. Then make it visibleExcelApplication := CreateOleObject('Excel.Application');
ExcelApplication.Visible := true;
Create a blank Excel workbookExcelApplication.WorkBooks.Add(-4167);
Create a blank Excel worksheetExcelApplication.WorkBooks[1].WorkSheets[1].Name := 'my data';
Set the working worksheet into Sheet variableSheet := 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 )

4 comments:

  1. For me exist some new softwares which I frequently use for work with excel files. But one day I ran upon a tool, which stroke me and helped with old issues. I conceive that this tool can many good resources for resolving varied troubles with excel documents - Excel files fix.

    ReplyDelete
  2. And what about if not having installed EXCEL?

    How can be done this if no M$EXCEL / OppenOffice Calc, etc is installed...
    -Create XLS (Excel97 format) from Delphi code
    -Include Border styles for each cell
    -Include background color and font format for each cell
    -Include some cells with ColSpan and/or RowSpan
    -Add more than just one sheet
    -Set Sheets names
    -Set each sheet header and footer
    -Set how many rows and cols will be repeated on each page, different for each sheet
    -etc

    All without having M$EXCEL, etc... on developing computer, also without having them on destination computer.

    It is so tricky i did not found yet a solution.

    Of course a Freeware (no charge at all) solution... paid solutions seem to be out there from near $80 to $300 or more.

    ReplyDelete
  3. s The best and a lot of secret drop-shipping suppliers in the world which will provide your products globally available for you directly to your customers doors. Also get the best drop-shipping manufacturer on your organization.

    ReplyDelete

These links are part of a pay per click advertising program called Infolinks. Infolinks is an In Text advertising service; they take my text and create links within it. If you hover your mouse over these double underlined links, you will see a small dialog box containing an advertisement related to the text. You can choose to move the mouse away and go on with your browsing, or to click on the box and visit the relevant ad. Click here to learn more about Infolinks Double Underline Link Ads.