Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Delphi with Other Programs

How to export data to Excel by svanels
Posted: 10 Feb 02 (Edited 13 Mar 08)

Update for Delphi 2007 and maybe back to Delphi 7 (no proof)


 {step 1}
    Range['A1','J1'].Value := VarArrayOf(['Order No',

must be


 {step 1}
    Range['A1','J1'].Value2 := VarArrayOf(['Order No',

.Value must be changed to .Value2

I have tested it in Delphi6, but it should work also in Delphi5 or later versions and MS office 10 and Office XP

There are various ways to export data, but the most efficient way is to use the objects, methods and properties from the server.
In this case TExelApplication at the Servers palette.
These informations can be found in vbaxl8.hlp (excel) or vbawrd8.hlp (word) somewhere in the office directory.
The big problem is that we have to pass all the parameters to the procedures, which are a lot, but luckily there is a way to expose the parameters, without studying the m$ help files for 2 months.
We will use the Macro Recorder and editor to retrieve all the vb ingredients. These are found in Excel.

Objective: Export data from a query  (DBDEMOS table Orders.db) to excel

The query will retrieve 10 columns and the Total value of the order is greater then $15000

the SQL property of the TQuery:


SELECT OrderNo, CustNo, SaleDate, EmpNo, ShipVIA, Terms, ItemsTotal, TaxRate, Freight, AmountPaid
FROM "orders.db" Orders where ItemsTotal > 15000

First we have to prepare the excel layout.

Open Excel, go to Tools --> Macro --> Record new macro

We will:
1) prepare the header with the column names
2) apply bold font, and centering
3) Fill in 2 rows of data
4) Apply the 4-mar-97 date format
5) Apply percent format
6) Apply $10,000.00 format to 3 columns
7) Use Autoformat to give some nice colors to the table
8) Show all columns with autofit

Just record the macro by typing in all the necessary stuf and formating etc. After that you use the macro editor to make the commands made visible.

It should look like this:


Sub ExportToExcel()
' ExportToExcel Macro
' Macro recorded 2/10/2002 by
     {step 1}
    ActiveCell.FormulaR1C1 = "Order No"
    ActiveCell.FormulaR1C1 = "Cust No"
    ActiveCell.FormulaR1C1 = "Amount Paid"
     {step 2}
    Selection.Font.Bold = True
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .ShrinkToFit = False
        .MergeCells = False
    End With
      {step 3}
    ActiveCell.FormulaR1C1 = "100"
    ActiveCell.FormulaR1C1 = "200"
    ActiveCell.FormulaR1C1 = "11/6/2001"
    ActiveCell.FormulaR1C1 = "30"
    ActiveCell.FormulaR1C1 = "Agent"
    ActiveCell.FormulaR1C1 = "Fob"
    ActiveCell.FormulaR1C1 = "2"
    ActiveCell.FormulaR1C1 = "1000"
    ActiveCell.FormulaR1C1 = "9000"
     {step 4}
    Selection.NumberFormat = "d-mmm-yy"
     {step 5}
    Selection.NumberFormat = "0.00%"
     {step 6}
    Selection.NumberFormat = "$#,##0.00"
    Selection.NumberFormat = "$#,##0.00"
    Selection.NumberFormat = "$#,##0.00"
     {step 7}
    Selection.AutoFormat Format:=xlRangeAutoFormatList1, Number:=True, Font:= _
        True, Alignment:=True, Border:=True, Pattern:=True, Width:=True
     {step 8}
End Sub

Ingredients for delphi

1)TForm of course
7)dbGrid, dbNavigator, datasource for comparison
The bitbutton (6) will trigger the export


procedure TForm1.bbtnExportToExcelClick(Sender: TObject);
LineNumber, LCID : Integer;
LineString : string;
  with SaveDialog1 do
    FileName :=';
    Filter:= 'Excel files|*.XLS;All Files|*.*';
    DefaultExt := 'XLS';
    Title := 'Exporting to Excel';
    if execute then
      LCID := GetUserDefaultLCID;
      with ExcelApplication1 do
          visible[LCID] := true;
              commands found in macro
              {step 1}
          Range['A1','J1'].Value := VarArrayOf(['Order No',
                'Cust No','Sale Date','Emp No',
                'Ship Via', 'Terms','Items Total',
                'Tax Rate','Freight','Amount Paid']);

             {step 2}
          with Range['A1','J1']do
            HorizontalAlignment := xlcenter;
            VerticalAlignment := xlBottom;
            Wraptext := false;
            Orientation := 0;
            ShrinkTofit := false;
            MergeCells := false;
            Font.Bold := true;

          LineNumber := 1;

                {step 3} , where by iteration the data from the query is transported to excell
          While not query1.Eof do
            LineString := IntToStr(LineNumber);
            Range['A'+LineString, 'J'+LineString].Value :=
              VarArrayof([Query1OrderNo.value, Query1CustNo.Value
{step 4}  is built in
                          Query1EmpNo.Value, Query1ShipVIA.Value,
                          Query1Terms.Value, Query1ItemsTotal.Value,
                          Query1TaxRate.Value, Query1Freight.Value,

          LineString := IntToStr(LineNumber);
   {step 5 and 6}
          Range['H2','G'+LineString].NumberFormat := '0.00%';
          Range['G2','G'+LineString].NumberFormat := '$#,##0.00';
          Range['I2','I'+LineString].NumberFormat := '$#,##0.00';
          Range['J2','J'+LineString].NumberFormat := '$#,##0.00';
              {step 7}
          Range['A1','J'+LineString].AutoFormat(xlRangeAutoFormatlist1, true,
                {step 8}

            end of macro stuff

          ActiveWorkbook.SaveAs(FileName,xlNormal, ', ', false, false,
      end;  //try
    end;  //with Excelapplication1
  end;  //if  execute
 end; //with Savedialog1



    ActiveCell.FormulaR1C1 = "Order No"
   as described in step 1
Has the equivalent in Delphi

    ActiveCell.FormulaR1Ci := 'Order No';

But entering the cells one by one is not very efficient, that is why a variant array is used to enter the values line by line


Range['A1','J1'].Value := VarArrayOf(['Order No',
                'Cust No','Sale Date','Emp No',
                'Ship Via', 'Terms','Items Total',
                'Tax Rate','Freight','Amount Paid']);

In this case a query is used to export for educational purpose and show the automation process. The user will be prompted to give a filename to created excel file. This can easily be extended to string grids or other visual controls

Best regards

Back to Embarcadero: Delphi FAQ Index
Back to Embarcadero: Delphi Forum

My Archive

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close