Microsoft automation: dataset export and printing

Problem/Question/Abstract:

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:

Excel: TExcelApplication;
Worksheet: TExcelWorksheet;
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:

Excel.Connect;
lcid := GetUserDefaultLCID;
Workbook.ConnectTo(Excel.Workbooks.Add(TOleEnum(xlWBATWorksheet), lcid);
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:

with ds do
begin
  DisableControls;
  //The first row is for the titles:
  for i := 1 to ds.FieldCount do
    if ds.Fields[i - 1].Visible then
    begin
      Worksheet.Cells.Item[1, i].Value :=
        ds.Fields[i - 1].DisplayLabel;

      Worksheet.Cells.Item[1, i].ColumnWidth :=
        ds.Fields[i - 1].DisplayWidth;
    end;
  //Some special formatting for the whole title’s row:
  Worksheet.Range['A1', 'A1'].EntireRow.Interior.Color := clGray;
  Worksheet.Range['A1', 'A1'].Font.FontStyle := 'Bold';

  L := 2;

  FIRST;
  while not (EOF) do

  begin
    for i := 1 to ds.FieldCount do
      if ds.Fields[i - 1].Visible then
      begin
        //Some special conditions for specific fields; additional formatting
        or checks could be added here
          if GetLookUpTableName(ds.Fields[i - 1].FieldName, sTable) then
          Worksheet.Cells.Item[L, i].Value :=
            GetLookUpValue(sTable, ds.Fields[i - 1].Text)
        else
          Worksheet.Cells.Item[L, i].Value :=
            ds.Fields[i - 1].Text;

      end;
    Inc(L);
    NEXT;
  end;
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:

//After exporting the Dataset to a Tab-delimited text file and closing this file:

Excel.Connect;
lcid := GetUserDefaultLCID;
WbK := Excel.Workbooks.Open(tFileName, EmptyParam, EmptyParam,
  EmptyParam, EmptyParam, EmptyParam, EmptyParam, EmptyParam, EmptyParam,
  EmptyParam, EmptyParam, EmptyParam, EmptyParam, lcid);
ws := wbk.worksheets[1];
Excel.Visible[lcid] := True;
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:

procedure TModule.PrintGrid3(ds: TDataSet; Header: string);

var
  S: AnsiString;
  VisCol, L, i: Integer;
  sTable: string;

begin
  with ds do
  begin
    DisableControls;

    for i := 0 to ds.FieldCount - 1 do
      if ds.Fields[i].Visible then
      begin
        S := S + ds.Fields[i].DisplayLabel;
        if i <> ds.FieldCount - 1 then
          S := S + #9;
        Inc(VisCol);
      end;
    S := S + #13;

    FIRST;
    while not (EOF) do
    begin
      for i := 0 to ds.FieldCount - 1 do
        if ds.Fields[i].Visible then
        begin
          S := S + ds.Fields[i].Text {+ #9};
          if i <> ds.FieldCount - 1 then
            S := S + #9;
          Inc(L);
        end;
      S := S + #13;
      NEXT;
    end;

      // Now copy the string :
      Clipboard.SetTextBuf(PChar(S);

      //Connect Excel:
      Excel.Connect;
      lcid := GetUserDefaultLCID;
      Workbook.ConnectTo(Excel.Workbooks.Add(TOleEnum(xlWBATWorksheet), lcid));
      Worksheet.ConnectTo(Workbook.Worksheets[1] as _Worksheet);
      Worksheet.Name := 'MyData';

      //Paste the string and clear the memory:
      Worksheet.Cells.Item[1, 1].Select;
      Worksheet.Paste;
      Clipboard.Clear;

That is it.The result is the same: your data is on place. Next lines show how to generate a ready for printing report from it and to present the PrintPreview form on the screen of your client:

                        //Formating column widths without any calculations:
                  Worksheet.Columns.AutoFit;
                        //Column titles:
                        Worksheet.Range['A1', 'A1'].EntireRow.Interior.Color := clGray;
                        Worksheet.Range['A1', 'A1'].EntireRow.HorizontalAlignment := 1;
                        Worksheet.Range['A1', 'A1'].Font.FontStyle := 'Bold';
                        Worksheet.PageSetup.PrintGridlines := true;
                        //Header and footer:
                        Worksheet.PageSetup.CenterHeader := Header;
                        Worksheet.PageSetup.LeftFooter := &#8216;Some Text&#8217;;
                        Worksheet.PageSetup.FirstPageNumber := 1;
                        Excel.Visible[lcid] := True;
                        Worksheet.PrintOut(EmptyParam, EmptyParam, 1, 1);
                        Excel.ScreenUpdating[lcid] := True;
                        Workbook.Close(False);
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.