Articles   Members Online:
-Article/Tip Search
-News Group Search over 21 Million news group articles.
Member Area
-Account Center
-Top 10 NEW!!
-Submit Article/Tip
-Forums Upgraded!!
-My Articles
-Edit Information
-Become a Member
-Why sign up!
-Chat Online!
-Indexes NEW!!
-Build your resume
-Find a job
-Post a job
-Resume Search
-Link to us
Visit Embarcadero
Embarcadero Community
Microsoft automation: dataset export and printing Turn on/off line numbers in source code. Switch to Orginial background IDE or DSP color Comment or reply to this aritlce/tip for discussion. Bookmark this article to my favorite article(s). Print this article
Delphi 5.x
User Rating
No Votes
# Votes
DSP, Administrator
Reference URL:

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 …


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 

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 . 


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;
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';
25    L := 2;
27    FIRST;
28    while not (EOF) do
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;
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. 


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:
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 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);
61  var
62    S: AnsiString;
63    VisCol, L, i: Integer;
64    sTable: string;
66  begin
67    with ds do
68    begin
69      DisableControls;
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;
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;
96        // Now copy the string :
97        Clipboard.SetTextBuf(PChar(S);
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';
106       //Paste the string and clear the memory:
107       Worksheet.Cells.Item[1, 1].Select;
108       Worksheet.Paste;
109       Clipboard.Clear;
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: 
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 by Gary White, dBVIPS
Automating Microsoft Excel : Sources of information, Sample project, How do I and 
Common problems by Deborah Pate.

Vote: How useful do you find this Article/Tip?
Bad Excellent
1 2 3 4 5 6 7 8 9 10


Share this page
Download from Google

Copyright © Mendozi Enterprises LLC