unit MainForm;

interface

uses
Windows, Messages, SysUtils, Classes, Graphics, Controls, Forms, Dialogs,
OleServer, StdCtrls, Excel97, Buttons;

type
TMain = class(TForm)
ExcelApplication1: TExcelApplication;
WkBk: TExcelWorkbook;
WS: TExcelWorksheet;
Chart1: TExcelChart;
InlineChartBtn: TButton;
ChartPageBtn: TButton;
CloseBtn: TBitBtn;
PivotTableBtn: TButton;
procedure FormClose(Sender: TObject; var Action: TCloseAction);
procedure FormShow(Sender: TObject);
procedure InlineChartBtnClick(Sender: TObject);
procedure ChartPageBtnClick(Sender: TObject);
procedure CloseBtnClick(Sender: TObject);
procedure PivotTableBtnClick(Sender: TObject);
procedure ExcelApplication1WorkbookBeforeClose(Sender: TObject; var Wb,
Cancel: OleVariant);
private
lcid: integer;
procedure EnterData;
procedure SetUpChart;
end;

var
Main: TMain;

implementation

{$R *.DFM}

uses ComObj, ActiveX;

procedure TMain.FormShow(Sender: TObject);
begin
{ No need to start Excel ourselves, since the ExcelApplication's AutoConnect
property is True }
lcid := GetUserDefaultLCID;
ExcelApplication1.Visible[lcid]:=True;

{ Create a new workbook }
{ The TOleEnum cast in the next line just prevents compiler range warnings }
WkBk.ConnectTo(ExcelApplication1.Workbooks.Add(TOleEnum(xlWBATWorksheet), lcid));
WS.ConnectTo(WkBk.Worksheets[1] as _Worksheet);
WS.Name := 'Data';
EnterData;
end;

procedure TMain.FormClose(Sender: TObject; var Action: TCloseAction);
begin
WS.Disconnect;
WkBk.Disconnect;
ExcelApplication1.Disconnect;
end;

procedure TMain.CloseBtnClick(Sender: TObject);
begin
Close;
end;

procedure TMain.ChartPageBtnClick(Sender: TObject);
var
SheetType: OleVariant;
begin
SheetType := TOleEnum(xlChart); // cast to avoid compiler range warnings
Chart1.ConnectTo(Wkbk.Sheets.Add(EmptyParam, EmptyParam, 1, SheetType, lcid) as _Chart);
Chart1.Name := 'Chart';
SetUpChart;
end;

procedure TMain.EnterData;
var
Row: integer;
ColumnTitles: olevariant;
begin
ExcelApplication1.ScreenUpdating[lcid] := False;
try
{ Using the Range object, you can enter one cell at a time ... }
WS.Range['B1', 'B1'].Value := 'Sample test scores';
WS.Range['A1', 'A1'].EntireRow.Interior.Color := clGray;
with WS.Range['B1', 'B1'].Font do
begin
Size := 22;
Name := 'Times New Roman';
FontStyle := 'Bold';
end;
WS.Range['A2', 'A2'].Value := 'Test';

{ Or several cells at once }
WS.Range['B3', 'E10'].Value := '=TRUNC(RAND()*10)';
WS.Range['B3', 'E10'].Font.Name := 'Courier New';

{ The Cells object allows you to access cells using row and column variables }
for Row := 3 to 10 do
WS.Cells.Item[Row, 1].Value := 'Test ' + IntToStr(Row - 2);

{ You can also use variant arrays, like this }
ColumnTitles := VarArrayOf(['Molly', 'Dolly', 'Polly', 'Holly']);
WS.Range['B2', 'E2'].Value := ColumnTitles;

finally
ExcelApplication1.ScreenUpdating[lcid] := True;
end;
end;

procedure TMain.InlineChartBtnClick(Sender: TObject);
var
ChObj: ChartObject;
begin
ChObj := (WS.ChartObjects(EmptyParam, lcid) as ChartObjects).Add(0, 170, 400, 250);
{ The following cast is essential! Without it, any attempt to use
the Chart object with early binding fails. }
Chart1.ConnectTo(ChObj.Chart as _Chart);
SetUpChart;
end;

procedure TMain.PivotTableBtnClick(Sender: TObject);
var
PT: PivotTable;
After: OleVariant;
begin
After := WkBk.Worksheets['Data'];
WS.ConnectTo(WkBk.Worksheets.Add(EmptyParam, After, 1, EmptyParam, lcid) as _WorkSheet);
WS.Name := 'Pivot table';
WS.Range['A2', 'A2'].Activate;
WS.PivotTableWizard(xlDatabase, 'Data!R2C1:R10C5', EmptyParam,
'PivotTable1', EmptyParam, EmptyParam, EmptyParam,
EmptyParam, EmptyParam, EmptyParam, EmptyParam,
EmptyParam, EmptyParam, EmptyParam, EmptyParam,
EmptyParam, lcid);
PT := WS.PivotTables('PivotTable1', lcid) as PivotTable;
PT.AddFields('Molly','Dolly', EmptyParam, EmptyParam);
(PT.PivotFields('Test') as PivotField).Orientation := xlDatafield;

{ Add heading }
WS.Range['B1', 'B1'].Value := 'Test scores: Molly and Dolly';
WS.Range['B1', 'B1'].EntireRow.Interior.Color := clGray;
with WS.Range['B1', 'B1'].Font do
begin
Size := 22;
Name := 'Times New Roman';
FontStyle := 'Bold';
end;
end;

procedure TMain.SetUpChart;
var
DataSheet: _Worksheet;
Ax: Axis;
Rnge, ChType: OleVariant;
begin
Datasheet := WkBk.Worksheets['Data'] as _Worksheet;
Rnge := Datasheet.Range['A2','E10']; // the data range, including titles
ChType := TOleEnum(xl3DColumn);
Chart1.ChartWizard(Rnge, ChType, EmptyParam, xlColumns, 1, 1, True,
WS.Range['B1', 'B1'].Text, // The chart title
'Test', 'Scores', EmptyParam, lcid);
Ax := Chart1.Axes(xlValue, xlPrimary, lcid) as Axis;
Ax.AxisTitle.Font.FontStyle := 'Italic';
end;



procedure TMain.ExcelApplication1WorkbookBeforeClose(Sender: TObject;
var Wb, Cancel: OleVariant);
begin
{ Stop DisplayAlerts to avoid being prompted to save before quitting. }
ExcelApplication1.DisplayAlerts[lcid] := False;
end;

end.