I already talked about using Microsoft Office applications in this article. I gave examples using Word. In this article I use Excel for what it does very well: take an array of data and produce a nice chart.
Excel exposes a number of objects and this makes programming it a little bit confusing at first. The three most important objects are:
- ExcelApplication: this is the whole Excel application.
- WorkBook: This is a spreadsheet file
- WorkSheet: This is a page within a workbook.
There are a lot of other objects or object collections. In this article we will use “Cells” and “Charts”. They are exactly what their names imply.
Each object or collection has a lot of properties and methods. This is where it becomes quite complex. Although most names are explicit, their use isn’t. Microsoft publishes a lot of documentation (http://msdn.microsoft.com/en-us/library/office/bb726434(v=office.12).aspx). Of course none of this documentation is written using Delphi syntax. Nevertheless it is of great help even if most samples are VBA or C#.
There are a large number of Office versions. The programming interface change slightly between each version but all in all, upward compatibility is excellent. The gold rule is to always use the oldest API version suitable for what you need to do. Because of upward compatibility, your application will generally work for the version you selected and all more recent versions.
For my sample application, I used Excel 2010. Microsoft reference is here.
In Delphi, you must use the correct components. See discussion in this article. What I said then for XE4 is valid for XE5 as well as previous versions.
My demo application is simple: A single VCL form with a single button. The button’s OnClick handler connect to excel, create a workbook having a worksheet, fill cells with simple data, create a new chart with the data, export the chart as a PDF file, close the workbook and Excel.
I hardcoded the data to keep the code simple. It is quite trivial to fetch data from anywhere, including some database. How the data is fetched is not today’s article object.
There are a number of traps when writing this kind of application. Most Office API functions have a lot of arguments. Most of them can be left empty. When you specify some argument the code may triggers an access violation or an OLE error. For example, when adding a chart, on argument specifies the chart type. I’ve found that using it will trigger an OLE error. I had to left it empty and then change the property ChartType to actually change the type of chart. This is really annoying because error messages are not explicit at all! It is a try and error play. It is time consuming.
The resulting code is very short and simple indeed:
procedure TForm1.Button1Click(Sender: TObject);
var
WBook : ExcelWorkbook;
WSheet : ExcelWorksheet;
Row : Integer;
WChart : ExcelChart;
LCID : Integer;
begin
// Get the locale identifier for the user default locale
LCID := GetUserDefaultLCID;
//Connect to Excel application, this will launch excel
ExcelApplication1.Connect;
// Make excel visible (This is not required)
ExcelApplication1.Visible[LCID] := TRUE;
// Create a new workbook with a new sheet
WBook := ExcelApplication1.Workbooks.Add(xlWBATWorksheet, LCID);
WSheet := WBook.ActiveSheet as ExcelWorksheet;
// Add some data to the sheet
WSheet.Cells.Item[1, 1] := 'Item';
WSheet.Cells.Item[1, 2] := 'Quantity';
for Row := 0 to High(Data) do begin
WSheet.Cells.Item[2 + Row, 1] := Data[Row].Item;
WSheet.Cells.Item[2 + Row, 2] := Data[Row].Quantity;
end;
// Create a new chart
WChart := WBook.Charts.Add(EmptyParam, EmptyParam,
EmptyParam, EmptyParam, LCID) as ExcelChart;
// Set the chart type
WChart.ChartType := xl3DPie;
// Set the tab name
WChart.Location(xlLocationAsNewSheet, 'MyChart');
// Export the chart as a PDF file
WChart.ExportAsFixedFormat(xlTypePDF, 'MyChart.pdf', xlQualityStandard,
TRUE, FALSE, EmptyParam, EmptyParam,
TRUE, // Open after published
EmptyParam);
// Close the workbook, quit excel and disconnect
WBook.Close(FALSE, EmptyParam, EmptyParam, LCID);
ExcelApplication1.Quit;
ExcelApplication1.Disconnect;
end;
--
Follow me on Twitter
Follow me on LinkedIn
Follow me on Google+
Visit my website: http://www.overbyte.be
This article is available from http://francois-piette.blogspot.be
