Controlling Excel from Delphi: Practical OLE Automation Examples with OLEVariant and CreateOleObject

Controlling Excel from Delphi: Practical OLE Automation Examples with OLEVariant and CreateOleObject

To control Excel from Delphi, the key is using OLE Automation through CreateOleObject and OLEVariant.
This page provides practical sample code for setting cell values, drawing borders, configuring print settings, saving files, and more.
It also covers useful details such as how to work with OLEVariant and how to adjust printing options via PageSetup—all essential techniques for real‑world business applications.

To use Excel from Delphi, you must include the appropriate units:
uses System.Win.ComObj, ExcelXP;
or
uses System.Win.ComObj, Excel2000;
You can check whether ExcelXP or Excel2000 wrappers are installed by opening [Component] ⇒ [Install Packages] and confirming that one of the following packages is enabled:

• “Microsoft Office 2000 Automation Server Wrapper Components”
• “Microsoft Office XP Automation Server Wrapper Components”

To create (launch) an Excel instance, use the following code. The variable ex will hold the Excel application object, which you can then control programmatically.
var ex: OLEVariant; // Excel application
ex := CreateOleObject('Excel.Application');

To create a new workbook, assign it to the variable wb as follows:
var wb: OLEVariant;
wb := ex.Workbooks.Add;
To open an existing workbook:
var wb: OLEVariant;
wb := ex.Workbooks.Open('filename.xlsx');
// After adding or opening a workbook, you can make Excel visible if needed:
ex.Visible := True;

To get the number of worksheets in a workbook:
wb.Worksheets.Count;

To work with a worksheet, use the Worksheets collection. The following code retrieves the first worksheet into the variable ws.
Note that indexing starts at 1.
var ws: OLEVariant;
ws := wb.Worksheets[1];

To access individual cells:
Indexing also starts at 1.
var ws: OLEVariant; // Worksheet
ws := wb.Worksheets[1];
ws.Cells[1,1].Value := 'Set cell value'; // Cell A1

To work with a cell range:
var ws: OLEVariant; // Worksheet
ws := wb.Worksheets[1];
ws.Range['A1:B2'].Value := 'Set range value';

1. Creating the Project

Create a new application via File ⇒ New ⇒ Windows VCL Application – Delphi.
Place a single button on the form.

2. Source Code

unit Unit1;

interface

uses
  Winapi.Windows, Winapi.Messages, System.SysUtils, System.Variants,
  System.Classes, Vcl.Graphics, Vcl.Controls, Vcl.Forms, Vcl.Dialogs,
  Vcl.StdCtrls;

type
  TForm1 = class(TForm)
    Button1: TButton;
    procedure Button1Click(Sender: TObject);
  private
    { Private declarations }
  public
    { Public declarations }
  end;

var
  Form1: TForm1;

implementation

{$R *.dfm}

uses System.Win.ComObj, ExcelXP;

procedure TForm1.Button1Click(Sender: TObject);
var
  ex: OLEVariant;  // Excel application
  wb: OLEVariant;  // Workbook
  ws: OLEVariant;  // Worksheet
  SaveFileName: string;
begin
  // NOTE:
  // Excel uses 1-based indexing.
  // Workbook: ex.Workbooks[1]
  // Worksheet: ex.Workbooks[1].Worksheets[1]
  // Cell: [Row, Column] → ex.Workbooks[1].Worksheets[1].Cells[1,1]

  // Create Excel application
  ex := CreateOleObject('Excel.Application');
  // Create a new workbook
  wb := ex.Workbooks.Add;
  // Make Excel visible
  ex.Visible := True;

  // Remove all sheets except one
  while wb.Worksheets.Count > 1 do
    wb.Worksheets[wb.Worksheets.Count].Delete;

  // Get the first worksheet
  ws := wb.Worksheets[1];

  // Set value 2 into cell B2
  ws.Range['B2'].Value := 2;
  // Set value 4 into cell C2
  ws.Cells[2,3].Value := 4;
  // Set formula =B2*C2 into cell D2
  ws.Cells[2,4].Value := '=B2*C2';

  // Set left border
  ws.Range['B2:D4'].Borders[xlEdgeLeft].Weight := xlThick;
  // Set top border
  ws.Range['B2:D4'].Borders[xlEdgeTop].LineStyle := xlDashDotDot;
  ws.Range['B2:D4'].Borders[xlEdgeTop].Color := $0000FF; // red
  ws.Range['B2:D4'].Borders[xlEdgeTop].Weight := xlThin;
  // Set right border
  ws.Range['B2:D4'].Borders[xlEdgeRight].LineStyle := LongInt(xlDashDot);
  ws.Range['B2:D4'].Borders[xlEdgeRight].Color := $00FF00; // green
  ws.Range['B2:D4'].Borders[xlEdgeRight].Weight := xlThin;
  // Set bottom border
  ws.Range['B2:D4'].Borders[xlEdgeBottom].LineStyle := xlContinuous;
  ws.Range['B2:D4'].Borders[xlEdgeBottom].Color := $FF0000; // blue
  ws.Range['B2:D4'].Borders[xlEdgeBottom].Weight := LongInt(xlMedium);
  // Set inside vertical borders
  ws.Range['B2:D4'].Borders[xlInsideVertical].LineStyle := xlContinuous;
  ws.Range['B2:D4'].Borders[xlInsideVertical].Color := $000000;
  ws.Range['B2:D4'].Borders[xlInsideVertical].Weight := xlHairline;
  // Set inside horizontal borders
  ws.Range['B2:D4'].Borders[xlInsideHorizontal].LineStyle := xlContinuous;
  ws.Range['B2:D4'].Borders[xlInsideHorizontal].Color := $777777;
  ws.Range['B2:D4'].Borders[xlInsideHorizontal].Weight := xlThin;

  // Set number format to "Text"
  ws.Range['B4:D4'].NumberFormatLocal := '@';
  // Formula will not calculate because the format is text
  ws.Cells[4,2].Value := '=B2+C2';
  // Center horizontally
  ws.Cells[4,2].HorizontalAlignment := LongInt(xlCenter);
  // Center vertically
  ws.Cells[4,2].VerticalAlignment := LongInt(xlCenter);

  // Set column width of B–C to 24 characters
  ws.Columns['B:C'].ColumnWidth := 24;
  // Set row height of row 4 to 27 points
  ws.Rows['4:4'].RowHeight := 27;

  // Set date/time format
  ws.Range['C3:C3'].NumberFormatLocal := 'yyyy/mm/dd hh:mm:ss(aaa)';
  // Set value
  ws.Range['C3:C3'].Value := '2022/1/1 22:22:14';

  // Merge cells C3:D4
  ws.Range['C3:D4'].Merge;
  // Equivalent:
  // ws.Range[ws.Cells[3,3], ws.Cells[4,4]].Merge;

  // Copy rows 2–4 to clipboard
  ws.Rows['2:4'].Copy;

  // Select A6 and paste
  ws.Cells[6,1].Select;
  ws.Paste;

  // Select A12 and paste again
  ws.Cells[12,1].Select;
  ws.Paste;

  // Clear clipboard
  ex.CutCopyMode := False;

  // Select A1
  ws.Cells[1,1].Select;

  // Set paper size to A4
  ws.PageSetup.PaperSize := xlPaperA4;
  // Set orientation (xlLandscape = landscape, xlPortrait = portrait)
  ws.PageSetup.Orientation := xlLandscape;
  // Fit to page width
  ws.PageSetup.Zoom := False;
  ws.PageSetup.FitToPagesWide := 1;
  ws.PageSetup.FitToPagesTall := False;

  // Set margins (converted from mm to points)
  ws.PageSetup.LeftMargin   := Trunc(20 * 72 / 25.4);
  ws.PageSetup.RightMargin  := Trunc(10 * 72 / 25.4);
  ws.PageSetup.TopMargin    := Trunc(10 * 72 / 25.4);
  ws.PageSetup.BottomMargin := Trunc(20 * 72 / 25.4);

  // Disable overwrite warning
  ex.DisplayAlerts := False;
  SaveFileName := ExtractFilePath(Application.ExeName) + 'a.xlsx';
  // Save workbook
  wb.SaveAs(SaveFileName);
  ex.DisplayAlerts := True;

  // Quit Excel
  ex.Quit;
end;

end.