Author: LUBOMIR ROSENSTEIN
There are many ways one can view the Automation. The most pragmatic one is the
following: parts of your application already exist on the client’s machine. Writing
them again is a waste of time. Creating a new program is maybe like creating the
universe, but the privilege to start from scratch every time is reserved only for
God. Using Microsoft Automation, though, is almost as exciting: a lot of hidden
surprises and riffs are waiting for you and sometimes the only way forward is to
experiment. If you have enough perserverance to cope with the constantly changing
Microsoft environment, success will come to you – a truth, which is applicable not
only to programming …
Answer:
Delphi 5 makes the task perhaps slightly easier: a set of nice server components
are on the palette. It is up to you if you want to use them or not, but you should
be aware of one important thing: they are not real Delphi components. An imported
type library is hidden behind them and often is very useful to know which one it
is.
The Office 2000 object model is different from the Office 97 one. As my experience
shows it is still more advisable to use the older library. Otherwise, you have to
make sure that all your clients have Office 2000 installed. Moreover, it is easy to
“rewrite” the server components only in a few minutes: remove the package and
import the desired library into a new one. In my case I use the Excel 97 library. I
have tested it in Office 2000 environment with no problems.
As s for components it is just more convinient to use them instead of calling the
interfaces directly. The wrapper is too thin to disturb the performance but if you
have concerns you can combine both approaches. Sometimes even using Variants is
unavoidable .
CELL BY CELL
Automating Excel is one of the most efficient ways to have a DBGrid or a Dataset
printed. It is easy to import data to Excel and the options for formatting, adding
calculated fields, summaries or even charts are almost unlimited. Excel can be a
very powerfull report generator for any application.
The most obvious approach is to fill the Excel worksheet as a stringgrid: cell by
cell. The field datatype and even the value for each cell can be checked during
this operation and formatted accordingly.
The first task is to connect to a new Excel worksheet. I use 3 components to
accomplish this:
1 Excel: TExcelApplication;
2 Worksheet: TExcelWorksheet;
3 Workbook: TExcelWorkbook;
This follows the logics of the Excel’s object model. Theoretically, you should be
able to connect the worksheet component directly . In practice even using the three
components can be problematic: you can not connect the worksheet before opening the
workbook and at least on my machine every attempt to open a workbook would cause an
error. Thanks to Deborah Pate I already know how to prevent this:
4 Excel.Connect;
5 lcid := GetUserDefaultLCID;
6 Workbook.ConnectTo(Excel.Workbooks.Add(TOleEnum(xlWBATWorksheet), lcid);
7 Worksheet.ConnectTo(Workbook.Worksheets[1] as _Worksheet);
Now the new worksheet is ready for filling. If you wold like to see it at this
point, add
Excel.Visible[lcid] := True;
But in this case, you will gain some speed defining
Excel.ScreenUpdating[lcid] := False;
As I have already mentioned, accessing the cells is as in TStringGrid. Here is the
whole process:
8 with ds do
9 begin
10 DisableControls;
11 //The first row is for the titles:
12 for i := 1 to ds.FieldCount do
13 if ds.Fields[i - 1].Visible then
14 begin
15 Worksheet.Cells.Item[1, i].Value :=
16 ds.Fields[i - 1].DisplayLabel;
17
18 Worksheet.Cells.Item[1, i].ColumnWidth :=
19 ds.Fields[i - 1].DisplayWidth;
20 end;
21 //Some special formatting for the whole title’s row:
22 Worksheet.Range['A1', 'A1'].EntireRow.Interior.Color := clGray;
23 Worksheet.Range['A1', 'A1'].Font.FontStyle := 'Bold';
24
25 L := 2;
26
27 FIRST;
28 while not (EOF) do
29
30 begin
31 for i := 1 to ds.FieldCount do
32 if ds.Fields[i - 1].Visible then
33 begin
34 //Some special conditions for specific fields; additional formatting
35 or checks could be added here
36 if GetLookUpTableName(ds.Fields[i - 1].FieldName, sTable) then
37 Worksheet.Cells.Item[L, i].Value :=
38 GetLookUpValue(sTable, ds.Fields[i - 1].Text)
39 else
40 Worksheet.Cells.Item[L, i].Value :=
41 ds.Fields[i - 1].Text;
42
43 end;
44 Inc(L);
45 NEXT;
46 end;
47 end;
Now turn on the screen updating and you will see the worksheet. It is formatted
according to your preferences and can contain a large amount of data ( I have
tested a table with 134 fields and several thousands records). But I do not
recommend exporting data like this if you have a lot of records.
TEXT FILE MEDIATION
Excel 2000 workbook have a new method – OpenText – which loads and parses a text
file as a new workbook with a single sheet that contains the parsed text-file data.
But even in Excel 97, if you have the Tab character as a delimiter, your text will
be recognized and parsed by the Open method in similar way.
It is faster than filling the worksheet cell by cell. Possible disadvantage is that
the formatting has to be separated from the export. If you want to format any
specific field, you should record and save its position during the file
preparation. It is easy to process formatting after the worksheet is prepared if
you have the coordinates of the field saved.
Next I use variants to access the workbook and the worksheet objects and the
TExcelAplication component:
48 //After exporting the Dataset to a Tab-delimited text file and closing
49 this file:
50
51 Excel.Connect;
52 lcid := GetUserDefaultLCID;
53 WbK := Excel.Workbooks.Open(tFileName, EmptyParam, EmptyParam,
54 EmptyParam, EmptyParam, EmptyParam, EmptyParam, EmptyParam, EmptyParam,
55 EmptyParam, EmptyParam, EmptyParam, EmptyParam, lcid);
56 ws := wbk.worksheets[1];
57 Excel.Visible[lcid] := True;
58 ws.activate;
THE POWERFULL CLIPBOARD
The Delphi huge string prompts another approach: replacing the text file with only
one string, opening an empty worksheet and posting this string onto it. Excel
wisely behaves similary and arranges the cells by itself according to the Tab and
Enter delimiters. This approach is safer since the file routines are being avoided
and it is even faster:
59 procedure TModule.PrintGrid3(ds: TDataSet; Header: string);
60
61 var
62 S: AnsiString;
63 VisCol, L, i: Integer;
64 sTable: string;
65
66 begin
67 with ds do
68 begin
69 DisableControls;
70
71 for i := 0 to ds.FieldCount - 1 do
72 if ds.Fields[i].Visible then
73 begin
74 S := S + ds.Fields[i].DisplayLabel;
75 if i <> ds.FieldCount - 1 then
76 S := S + #9;
77 Inc(VisCol);
78 end;
79 S := S + #13;
80
81 FIRST;
82 while not (EOF) do
83 begin
84 for i := 0 to ds.FieldCount - 1 do
85 if ds.Fields[i].Visible then
86 begin
87 S := S + ds.Fields[i].Text {+ #9};
88 if i <> ds.FieldCount - 1 then
89 S := S + #9;
90 Inc(L);
91 end;
92 S := S + #13;
93 NEXT;
94 end;
95
96 // Now copy the string :
97 Clipboard.SetTextBuf(PChar(S);
98
99 //Connect Excel:
100 Excel.Connect;
101 lcid := GetUserDefaultLCID;
102 Workbook.ConnectTo(Excel.Workbooks.Add(TOleEnum(xlWBATWorksheet), lcid));
103 Worksheet.ConnectTo(Workbook.Worksheets[1] as _Worksheet);
104 Worksheet.Name := 'MyData';
105
106 //Paste the string and clear the memory:
107 Worksheet.Cells.Item[1, 1].Select;
108 Worksheet.Paste;
109 Clipboard.Clear;
110
111 //That is it.The result is the same: your data is on place. Next lines show how to
112 generate a ready for printing report from it and to present the PrintPreview form
113 on the screen of your client:
114
115 //Formating column widths without any calculations:
116 Worksheet.Columns.AutoFit;
117 //Column titles:
118 Worksheet.Range['A1', 'A1'].EntireRow.Interior.Color := clGray;
119 Worksheet.Range['A1', 'A1'].EntireRow.HorizontalAlignment := 1;
120 Worksheet.Range['A1', 'A1'].Font.FontStyle := 'Bold';
121 Worksheet.PageSetup.PrintGridlines := true;
122 //Header and footer:
123 Worksheet.PageSetup.CenterHeader := Header;
124 Worksheet.PageSetup.LeftFooter := ‘Some Text’;
125 Worksheet.PageSetup.FirstPageNumber := 1;
126 Excel.Visible[lcid] := True;
127 Worksheet.PrintOut(EmptyParam, EmptyParam, 1, 1);
128 Excel.ScreenUpdating[lcid] := True;
129 Workbook.Close(False);
130 end;
Of course, you could just print the report without showing the preview (see the
PrintOut method parameters) or proceed in a different direction: sending the data
by fax or e-mail or exporting it again, using now the Excel capacities for data
processing. Word is also applicable for dataset export and printing (the
TextToTable method) but Excel copes better with large datasets. It is worth
experimenting with various solutions in order to reach the best result.
With every new version the Office applications are going to be more and more
complex. New objects, methods and properties are being added and there are already
so many different capacities that even the Office creators would perhaps find it
hard hard to simply count them. It is a strenuous task for the common user to learn
them all. But Microsoft Office is charged with much useful building material for
the inventive developer and Automation is the key for taking advantage of it.
Related resources
Charlie Calvert: Delphi 4 Unleashed
MS Excel.how by Gary White, dBVIPS
Automating Microsoft Excel : Sources of information, Sample project, How do I and
Common problems by Deborah Pate.
|
