You are currently offline, waiting for your internet to reconnect

How To Automate Excel to Create a New Workbook from Delphi

This article was previously published under Q231614
SUMMARY
This article demonstrates how to create and manipulate an Excel workbook using Automation from Delphi.
MORE INFORMATION
This article parallels other articles that describe the same process using different languages. For more information, please see the following articles in the Microsoft Knowledge Base:
219151 How To Automate Microsoft Excel from Visual Basic

179706 How To Use MFC to Automate Excel and Create/Format a New Workbook

219430 How To Create and Format an Excel Workbook Using Visual J++

Building the Automation Sample

  1. Start Delphi. A new project should be created by default.
  2. Add a button to Form1.
  3. Replace the contents of the code window for Unit1 with the following:
    unit Unit1;interfaceuses  Windows, Messages, SysUtils, Classes, Graphics, Controls, Forms, Dialogs,  StdCtrls;type  TForm1 = class(TForm)    Button1: TButton;    procedure Button1Click(Sender: TObject);    procedure DisplayQuarterlySales(oWS : Variant);  private    { Private declarations }  public    { Public declarations }  end;var  Form1: TForm1;Const xlVAlignCenter = -4108;Const xlThin = 2;Const xlEdgeBottom = 9;Const xlDouble = -4119;Const xlThick = 4;Const xl3DColumn = -4100;Const xlColumns = 2;Const xlLocationAsObject = 2;implementationuses ComObj;{$R *.DFM}procedure TForm1.DisplayQuarterlySales(oWS : Variant);var  oResizeRange, oChart, oSeries: Variant;  iNumQtrs,iRet : Integer;  sMsg : String;begin  // Determine how many quarters to display data for  for iNumQtrs := 4 downto 2 do  begin    sMsg := 'Enter sales data for ' + IntToStr(iNumQtrs) + ' quarter(s)?';    if MessageDlg(sMsg,mtConfirmation,[mbYes,mbNo],0) = mrYes then      break;  end;  sMsg := 'Displaying data for ' + IntToStr(iNumQtrs) + ' quarter(s).';  MessageDlg(sMsg,mtInformation,[mbOK],0);  // Starting at E1, fill headers for the number of columns selected  oResizeRange := oWS.Range['E1:' + Chr(Ord('E') + iNumQtrs - 1) + '1'];  oResizeRange.Formula := '="Q" & COLUMN()-4 & CHAR(10) & "Sales"';  // Change the orientation and WrapText properties for the headers  oResizeRange.Orientation := 38;  oResizeRange.WrapText := True;  // Fill the interior color of the headers  oResizeRange.Interior.ColorIndex := 36;  // Fill the columns with a formula and apply a number format  oResizeRange := oWS.Range['E2:' + Chr(Ord('E') + iNumQtrs - 1) + '6'];  oResizeRange.Formula := '=RAND()*100';  oResizeRange.NumberFormat := '$0.00';  // Apply borders to the Sales  data and headers  oResizeRange := oWS.Range['E1:' + Chr(Ord('E') + iNumQtrs - 1) + '6'];  oResizeRange.Borders.Weight := xlThin;  // Add a totals formula for the sales data and apply a border  oResizeRange := oWS.Range['E8:' + Chr(Ord('E') + iNumQtrs - 1) + '8'];  oResizeRange.Formula := '=SUM(E2:E6)';  oResizeRange.Borders.Item[xlEdgeBottom].LineStyle := xlDouble;  oResizeRange.Borders.Item[xlEdgeBottom].Weight := xlThick;  // Add a chart for the selected data  oResizeRange := oWS.Range['E2:' + Chr(Ord('E') + iNumQtrs - 1) + '6'];  oChart := oWS.Parent.Charts.Add;  oChart.ChartWizard(oResizeRange,xl3DColumn,,xlColumns);  oResizeRange := oWS.Range['A2:A6'];  oChart.SeriesCollection.Item[1].XValues := oResizeRange;  iRet := oChart.SeriesCollection.Count;  for iRet := 1 to iNumQtrs do  begin    sMsg :=  '="Q' + IntToStr(iRet) + '"';    oChart.SeriesCollection.Item[iRet].Name := sMsg;  end;  oChart.Location(xlLocationAsObject,oWS.Name);  // Move the chart so as not to cover your data  oWS.Shapes.Item(1).Top := oWS.Rows.Item[10].Top;  oWS.Shapes.Item(1).Left := oWS.Columns.Item[2].Leftend;procedure TForm1.Button1Click(Sender: TObject);var  oXL, oWB, oSheet, oRng, VArray : Variant;begin  // Start Excel and get Application Object  oXL := CreateOleObject('Excel.Application');  oXL.Visible := True;  // Get a new workbook  oWB := oXL.Workbooks.Add;  oSheet := oWB.ActiveSheet;  // Add table headers going cell by cell  oSheet.Cells[1,1] := 'First Name';  oSheet.Cells[1,2] := 'Last Name';  oSheet.Cells[1,3] := 'Full Name';  oSheet.Cells[1,4] := 'Salary';  // Format A1:D1 as bold, vertical alignment = center  oSheet.Range['A1:D1'].Font.Bold := True;  oSheet.Range['A1:D1'].VerticalAlignment := xlVAlignCenter;  // Create an array to set multiple values at once  VArray := VarArrayCreate([0,4,0,1],varVariant);  VArray[0,0] := 'John';  VArray[0,1] := 'Smith';  VArray[1,0] := 'Tom';  VArray[1,1] := 'Brown';  VArray[2,0] := 'Sue';  VArray[2,1] := 'Thomas';  VArray[3,0] := 'Jane';  VArray[3,1] := 'Jones';  VArray[4,0] := 'Adam';  VArray[4,1] := 'Johnson';  // Fill A2:B6 with an array of values  oSheet.Range['A2:B6'] := VArray;  // Fill C2:C6 with a relative formula (=A2 + ' ' + B2)  oRng := oSheet.Range['C2:C6'];  oRng.Formula := '=A2 & " " & B2';  // Fill D2:D6 with a formula (=RAND()*100000) and apply format  oRng := oSheet.Range['D2:D6'];  oRng.Formula := '=RAND()*100000';  oRng.NumberFormat := '$0.00';  // Autofit columns A:D  oRng := oSheet.Range['A1:D1'];  oRng.EntireColumn.AutoFit;  // Manipulate a variable number of columns for Quarterly Sales Data  DisplayQuarterlySales(oSheet);  // Make sure Excel is visible and give the user control  // of Microsoft Excel's lifetime  oXL.Visible := True;  oXL.UserControl := True;end;end.						
  4. Press the F9 key to run the project. Click the button and Microsoft Excel should start and display a sample worksheet.NOTE: Delphi is manufactured by Borland (Inprise Corporation), a vendor independent of Microsoft; we make no warranty, implied or otherwise, regarding this product's performance of reliability. Contact Inprise for any developer support regarding the Delphi language.
REFERENCES
For more information on Office Automation, please visit the Microsoft Office Development support site at:
Properties

Article ID: 231614 - Last Review: 01/24/2007 15:48:57 - Revision: 4.5

  • Microsoft Excel 2000 Standard Edition
  • Microsoft Excel 2002 Standard Edition
  • Microsoft Excel 97 Standard Edition
  • kbautomation kbhowto KB231614
Feedback