Delphi 3 - Delphi and Microsoft Office: Automating Excel and Word - Page 4

By: Charles Calvert

Abstract: The goal of this two-part paper is to get you up to speed automating Excel and Word from a Delphi application.

Delphi and Microsoft Office: Automating Excel and Word
Copyright © 1997 by Charlie Calvert
Here is the examples.zip file.

Creating a Chart
It is just as easy to create and work with a Chart as it is to do everything else in Excel automation. In the example shown in this section, refer to the online program called Excel4.dpr. The listing for this program is shown in Listing 4. I include the listing here so that you can take a quick glance through it, and then refer back to it during the discussion of its inner workings that follows this listing. In other words, I don't expect you to understand the program completely at a single glance, but will instead spend the remainder of this section discussing it in some depth.

Listing 4: The Excel4 programs shows how to work with charts.

unit Main;
{
  Main.pas
  Copyright (c) 1997 by Charlie Calvert
  Creating data and a chart in Excel and copying both to Word.
}

interface

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

type
  TForm1 = class(TForm)
    Button1: TButton;
    procedure Button1Click(Sender: TObject);
    procedure FormDestroy(Sender: TObject);
  private
    XLApp: Variant;
    WordApp: Variant;
  public
    procedure HandleData;
    procedure ChartData;
    procedure CopyData;
    procedure CopyChartToWord;
    procedure CopyCellsToWord;
    procedure MailDocument;
  end;

var
  Form1: TForm1;

implementation

uses
  ComObj, XLConst, WordConst,
  ActiveX;

{$R *.DFM}

procedure TForm1.Button1Click(Sender: TObject);
begin
  XLApp := CreateOleObject('Excel.Application');
  XLApp.Visible := True;
  XLApp.Workbooks.Add[XLWBatWorksheet];
  XLApp.Workbooks[1].Worksheets[1].Name := 'Delphi Data';
  HandleData;
  ChartData;
  CopyData;
  MailDocument;
end;

procedure TForm1.HandleData;
var
  Sheet: Variant;
  i: Integer;
begin
  Sheet := XLApp.Workbooks[1].Worksheets['Delphi Data'];
  for i := 1 to 10 do
    Sheet.Cells[i, 1] := i;
end;

procedure TForm1.ChartData;
var
  ARange: Variant;
  Sheets: Variant;
begin
  XLApp.Workbooks[1].Sheets.Add(,,1,xlChart);
  Sheets := XLApp.Sheets;
  ARange := Sheets.Item['Delphi Data'].Range['A1:A10'];
  Sheets.Item['Chart1'].SeriesCollection.Item[1].Values := ARange;
  Sheets.Item['Chart1'].ChartType := xl3DPie;
  Sheets.Item['Chart1'].SeriesCollection.Item[1].HasDataLabels := True;

  XLApp.Workbooks[1].Sheets.Add(,,1,xlChart);
  Sheets.Item['Chart2'].SeriesCollection.Item[1].Values := ARange;
  Sheets.Item['Chart2'].SeriesCollection.Add(ARange);
  Sheets.Item['Chart2'].SeriesCollection.NewSeries;
  Sheets.Item['Chart2'].SeriesCollection.Item[3].Values :=
    VarArrayOf([1,2,3,4,5, 6,7,8,9,10]);
  Sheets.Item['Chart2'].ChartType := xl3DColumn;
end;

procedure TForm1.CopyData;
var
  Sheets: Variant;
begin
  SetFocus;
  
  Sheets := XLApp.Sheets;

  Sheets.Item['Delphi Data'].Activate;
  Sheets.Item['Delphi Data'].Range['A1:A10'].Select;
  Sheets.Item['Delphi Data'].UsedRange.Copy;

  CopyCellsToWord;

  Sheets.Item['Chart1'].Select;
  XLApp.Selection.Copy;

  CopyChartToWord;
end;

procedure TForm1.CopyChartToWord;
var
  Range: Variant;
  i, NumPars: Integer;
begin
  NumPars := WordApp.Documents.Item(1).Paragraphs.Count;
  Range := WordApp.Documents.Item(1).Range(
    WordApp.Documents.Item(1).Paragraphs.Item(NumPars).Range.Start,
    WordApp.Documents.Item(1).Paragraphs.Item(NumPars).Range.End);
  Range.Text := 'This is graph: ';

  for i := 1 to 3 do WordApp.Documents.Item(1).Paragraphs.Add;

  Range := WordApp.Documents.Item(1).Range(
    WordApp.Documents.Item(1).Paragraphs.Item(NumPars + 1).Range.Start,
    WordApp.Documents.Item(1).Paragraphs.Item(NumPars + 1).Range.End);

  Range.PasteSpecial(,,,,wdPasteOleObject);  
end;

procedure TForm1.CopyCellsToWord;
var
  Range: Variant;
  i: Integer;
begin
  WordApp := CreateOleObject('Word.Application');
  WordApp.Visible := True;
  WordApp.Documents.Add;
  Range := WordApp.Documents.Item(1).Range;
  Range.Text := 'This is a column from a spreadsheet: ';
  for i := 1 to 3 do WordApp.Documents.Item(1).Paragraphs.Add;
  Range := 
    WordApp.Documents.Item(1).Range(WordApp.Documents.Item(1).      
      Paragraphs.Item(3).Range.Start);
  Range.Paste;
  for i := 1 to 3 do WordApp.Documents.Item(1).Paragraphs.Add;
end;

procedure TForm1.FormDestroy(Sender: TObject);
begin
  if not VarIsEmpty(XLApp) then begin
    XLApp.DisplayAlerts := False;  // Discard unsaved files....
    XLApp.Quit;
  end;

  if not VarIsEmpty(WordApp)then begin
    WordApp.Documents.Item(1).Close(wdDoNotSaveChanges);
    WordApp.Quit;
  end;
end;

procedure TForm1.MailDocument;
begin
  WordApp.Documents.Item(1).SaveAs('c:foo.doc');
  WordApp.Options.SendMailAttach := True;
  WordApp.Documents.Item(1).SendMail;
end;

end.

This code pops up a copy of Excel, inserts some data into it, creates two graphs of the data, then pops up a copy of Word, copies the cells from the worksheet to a new Word document, and then copies one of the charts into the same document. When you are through, you have a word document containing some spreadsheet cells with Delphi data in them, and below these cells, a graph. You may not see the graph at first when looking at your copy of Word. To find the graph, scroll the document down a bit. By default, there is a fairly large margin at the top of a graph, so you may need to scroll down further than you think. After creating the Word document, the user has a chance to mail it via Microsoft mail.

The Button1Click method drives the entire application:

procedure TForm1.Button1Click(Sender: TObject);
begin
  XLApp := CreateOleObject('Excel.Application');
  XLApp.Visible := True;
  XLApp.Workbooks.Add[XLWBatWorksheet];
  XLApp.Workbooks[1].Worksheets[1].Name := 'Delphi Data';
  HandleData;
  ChartData;
  CopyData;
  MailDocument;
end;

Its starts by creating an Excel Application object, then sets the Visible property of the object to true, and adds a new workbook and stuffs a single worksheet into it. The Delphi application then calls my custom HandleData method to insert data into the spreadsheet:

procedure TForm1.HandleData;
var
  Sheet: Variant;
  i: Integer;
begin
  Sheet := XLApp.Workbooks[1].Worksheets['Delphi Data'];
  for i := 1 to 10 do
    Sheet.Cells[i, 1] := i;
end;

This method was explained earlier in this article, so I won't cover it again.

Now that you have a worksheet, and some data, then next step is to create a graph. The following procedure from the Excel4 program should get you started working with charts:

procedure TForm1.ChartData;
var
  ARange: Variant;
  Sheets: Variant;
begin
  XLApp.Workbooks[1].Sheets.Add(,,1,xlChart);
  Sheets := XLApp.Sheets;
  ARange := Sheets.Item['Delphi Data'].Range['A1:A10'];
  Sheets.Item['Chart1'].SeriesCollection.Item[1].Values := ARange;
  Sheets.Item['Chart1'].ChartType := xl3DPie;
  Sheets.Item['Chart1'].SeriesCollection.Item[1].HasDataLabels := True;

  XLApp.Workbooks[1].Sheets.Add(,,1,xlChart);
  Sheets.Item['Chart2'].SeriesCollection.Item[1].Values := ARange;
  Sheets.Item['Chart2'].SeriesCollection.Add(ARange);
  Sheets.Item['Chart2'].SeriesCollection.NewSeries;
  Sheets.Item['Chart2'].SeriesCollection.Item[3].Values :=
    VarArrayOf([1,2,3,4,5, 6,7,8,9,10]);
  Sheets.Item['Chart2'].ChartType := xl3DColumn;
end;

This function creates two different charts. I've arranged things this way so you can get a look at some of the different techniques needed to create charts.

The code starts by adding a single chart to a Sheets object in a workbook:

XLApp.Workbooks[1].Sheets.Add(,,1,xlChart);

As you can see, I just ignore the first two parameters, then explicitly state that I want to insert 1 sheet, and define its type as xlChart. The Add method was described in more depth earlier in this article.

A lot of the trick to working with Excel is to find the right object to work with. In my opinion, the Sheets object provides a simple and convenient way to create a chart, but it is not the only way of doing so. Remember that the Sheets object contains both the Worksheets and Charts objects inside a Workbook, so you can use it to add either worksheets or charts.

You should further understand that I am talking about adding Charts to a Sheets object, which is different from adding ChartObjects to a worksheet. In other words, you can insert a graph into a worksheet, but that is a different operation from the one I show here. The key to embedding a chart into a work sheet is the Excel ChartObjects collection, which is not discussed further in this article.

Once the chart has been created, the code then finds a range of data in the sheet to work on. In this particular example, the range is the same as produced back in the Excel 3 application, when I inserted 10 numbers into the A column, and then supplied a formula to add them up. In particular, note that I create a range object, then set the SeriesCollection of a Chart object to this range:

  ARange := Sheets.Item['Delphi Data'].Range['A1:A10'];
  Sheets.Item['Chart1'].SeriesCollection.Item[1].Values := ARange;

That is all you need to do to graph a range of data. As I will explain in a moment, you may want to manipulate the chart further, but just doing what I have done here is enough to start charting data.

It is probably worthwhile stepping back and looking at the SeriesCollection object and see what it represents. To get started, you needed to understand that a Series is simply a range of data that you want to graph. A SeriesCollection is a collection of ranges of data, that is, it is a collection of Series. For instance, if you had the values 1, 2, 3 in three cells in a spread sheet, then that would represent a range of three numbers which could be placed in a Series. By default, the graph of that series might look something like this, where each dash represents one unit in the Series 1, 2, 3:

-

--

---

If you had several Series together in one place, then that would be a SeriesCollection.

To see a SeriesCollection on the Excel side, load an Excel chart, right click on it, select the Source Data item from the menu, and turn to the Series page, as shown in Figure 1:

Figure 1: Showing a series inside of Excel.

This is the series created for the first graph made by the Excel4 program. As you can see, the series is described with a single cryptic line:

='Delphi Data'!$A$1:$A$10

If you think about it for a second, you can see how this line corresponds to the code you wrote in Delphi. To help you see the relationship, I will write a single line of pseudo-code designed to highlight the relationship:

  SeriesCollection.Item[1].Values := 
    Sheets.Item['Delphi Data'].Range[A1:A10];

This line will not compile, but it contains the essence of what happens in the two lines of Delphi code which creates the Series shown from the SeriesCollection. It is similar to the data seen on the Excel side.

When you create a new chart, there is one Series made for you automatically. By default, it charts whatever value is in cell A1 of a particular Worksheet. In this case, I have changed that series to point to a new range of data. In other words, I have changed the "Values" associated with the Series. As you will see in one moment, you can add additional series if you so desire.

After creating the chart, the code defines it further by stating its type:

Sheets.Item['Chart1'].ChartType := xl3DPie;

And then goes on to specify that the chart has a series of data labels:

Sheets.Item['Chart1'].SeriesCollection.Item[1].HasDataLabels := True;

Its time now to look at the second chart created by the ChartData method:

  XLApp.Workbooks[1].Sheets.Add(,,1,xlChart);
  Sheets.Item['Chart2'].SeriesCollection.Item[1].Values := ARange;
  Sheets.Item['Chart2'].SeriesCollection.Add(ARange);
  Sheets.Item['Chart2'].SeriesCollection.NewSeries;
  Sheets.Item['Chart2'].SeriesCollection.Item[3].Values :=
    VarArrayOf([1,2,3,4,5,6,7,8,9,10]);

This chart graphs a SeriesCollection that contains not one, but three Series. The first and second Series are identical to the Series graphed by the first chart, but the third Series is slightly different, in that its values come not from an Excel worksheet, but from a range of data directly specified inside Delphi.

Take a moment to consider what is happening here. The first set of data graphed is specified exactly as in the previous example:

Sheets.Item['Chart2'].SeriesCollection.Item[1].Values := ARange;

Adding a new range specifies the next Series:

Sheets.Item['Chart2'].SeriesCollection.Add(ARange);

Finally, the code creates a new Series with no particular data:

Sheets.Item['Chart2'].SeriesCollection.NewSeries;

The program than creates a variant array containing the values you want to chart in this third Series:

  Sheets.Item['Chart2'].SeriesCollection.Item[3].Values :=
    VarArrayOf([1,2,3,4,5,6,7,8,9,10]);

This is not the time nor place to discuss variant arrays, but the subject is treated in Chapter 25 of the Delphi User's Guide. The series created by this code is shown on the Excel side in Figure 2.

Figure 2: A variant array created in Delphi as it is depicted inside Excel.

In this case, I need not change the type of graph, since the default column chart does the job adequately. I could, however, add the following line of code to create a new effect:

Sheets.Item['Chart2'].ChartType := xl3DColumn;

You are at last ready to copy the worksheet and two charts that you have created from Excel into Word. This is a relatively complex operation, so I will start a new section in this article to discuss it.


Server Response from: ETNASC01