Setting Attributes of an Excel File

In the previous tutorial ( Creating an Excel File using Delphi ), you have learned how to create an MS Excel file. Now we need to set some attributes of the MS Excel file. In an MS Excel file, a cell has some attributes ie : font, color, border etc. 

To set cell border, use this code :

ExcelApplication.WorkBooks[1].WorkSheets['my data']. Range['A1: D7'].Borders.LineStyle := 7;

Or

Sheet.Range['A1: D7'].Borders.LineStyle := 7;

We can replace ExcelApplication.WorkBooks[1].WorkSheets['my data'] with Sheet because in the previous tutorial we have set our working worksheet into Sheet variable. The LineStyle value can be 0, 1, 2 ……13. You can try each value and see what happen to the worksheet.

To set cell border color :

Sheet.Range['A1:D7'].Borders.color := clblue;

To set cell font attributes :

Sheet.Range['A1: D7'].Font.color := clred;

Sheet.Range['A1: D7'].Font.Bold := true;

To set columns width :

Sheet.Columns[1].ColumnWidth := 4;

Sheet.Columns[2].ColumnWidth := 30;

Sheet.Columns[3].ColumnWidth := 8;

Sheet.Columns[4].ColumnWidth := 20;

To set rows height :

Sheet.Rows[1].RowHeight := 25;

Sheet.Rows[2].RowHeight:= 40;

Entering a value to a cell

In MS Excel worksheet, every cell has a name like A1, B12, AA7, BC8 etc. But we can not access cells with these names. Imagine that a worksheet is a two dimension array of cells. A1 equals to Cells[1,1], B12 equals to Cells[12,2], AA7 equals to Cells[7,27] etc. To enter a value to A1, B1, C1 and D1 cells, use these codes :

Sheet.Cells[1,1] := 'ID';

Sheet.Cells[1,2] := 'NAME';

Sheet.Cells[1,3] := 'AGE';

Sheet.Cells[1,4] := 'PHONE NUMBER';

Now add some codes to our previous project in the previous tutorial. See the codes below, the new added codes are bold.

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'];

 Sheet.Range['A1:D7'].Borders.LineStyle := 7;

 Sheet.Range['A1:D7'].Borders.color := clblue;

 Sheet.Columns[1].ColumnWidth := 4;

 Sheet.Columns[2].ColumnWidth := 30;

 Sheet.Columns[3].ColumnWidth := 8;

 Sheet.Columns[4].ColumnWidth := 20;

 Sheet.Cells[1,1] := 'ID';

 Sheet.Cells[1,2] := 'NAME';

 Sheet.Cells[1,3] := 'AGE';

 Sheet.Cells[1,4] := 'PHONE NUMBER';

end;

procedure TForm1.ExitClick(Sender: TObject);

begin

 Application.Terminate;

end;

end.

Go to Previous Tutorial ( Creating an Excel File using Delphi )

or Next Tutorial ( Export an Interbase Table into an MS Excel File )