Delphi uses Excel to create a chart in a PDF document

Microsoft Excel exposes all his features thru a COM interface which can easily be used from a Delphi application. In this article, I use that feature to create a 3D pie chart from data available within a Delphi program and produce a PDF document.

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