Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations TouchToneTommy on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

How to export data to Excel

Delphi with Other Programs

How to export data to Excel

by  svanels  Posted    (Edited  )
Update for Delphi 2007 and maybe back to Delphi 7 (no proof)
Code: 
 {step 1}
    Range['A1','J1'].Value := VarArrayOf(['Order No',

must be
Code: 
 {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:

Code: 
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:

Code: 
Sub ExportToExcel()
'
' ExportToExcel Macro
' Macro recorded 2/10/2002 by
    [b] [i] {step 1} [/i] [/b]
    Range("A1").Select
    ActiveCell.FormulaR1C1 = "Order No"
    Range("B1").Select
    ActiveCell.FormulaR1C1 = "Cust No"
    Range("C1").Select
     [b] [i] etc.. [/i] [/b]
    Range("J1").Select
    ActiveCell.FormulaR1C1 = "Amount Paid"
     [b][i]{step 2} [/i][/b]
    Range("A1:J1").Select
    Selection.Font.Bold = True
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .ShrinkToFit = False
        .MergeCells = False
    End With
     [b] [i] {step 3} [/i] [/b]
    Range("A2").Select
    ActiveCell.FormulaR1C1 = "100"
    Range("B2").Select
    ActiveCell.FormulaR1C1 = "200"
    Range("C2").Select
    ActiveCell.FormulaR1C1 = "11/6/2001"
    Range("D2").Select
    ActiveCell.FormulaR1C1 = "30"
    Range("E2").Select
    ActiveCell.FormulaR1C1 = "Agent"
    Range("F2").Select
    ActiveCell.FormulaR1C1 = "Fob"
    Range("G2").Select
     [b] [i] etc.. [/i] [/b]
    Range("H3").Select
    ActiveCell.FormulaR1C1 = "2"
    Range("I3").Select
    ActiveCell.FormulaR1C1 = "1000"
    Range("J3").Select
    ActiveCell.FormulaR1C1 = "9000"
    [b] [i] {step 4} [/i] [/b]
    Columns("C:C").Select
    Selection.NumberFormat = "d-mmm-yy"
    [b] [i] {step 5} [/i] [/b]
    Columns("H:H").Select
    Selection.NumberFormat = "0.00%"
    [b] [i] {step 6} [/i] [/b]
    Columns("G:G").Select
    Selection.NumberFormat = "$#,##0.00"
    Columns("I:I").Select
    Selection.NumberFormat = "$#,##0.00"
    Columns("J:J").Select
    Selection.NumberFormat = "$#,##0.00"
    [b] [i] {step 7} [/i] [/b]
    Range("A1:J3").Select
    Selection.AutoFormat Format:=xlRangeAutoFormatList1, Number:=True, Font:= _
        True, Alignment:=True, Border:=True, Pattern:=True, Width:=True
    [b] [i] {step 8} [/i] [/b]
    Columns("C:C").EntireColumn.AutoFit
End Sub

Ingredients for delphi
1)TForm of course
2)TQuery
3)SaveDialog
5)TExcelapplication
6)bitButton
7)dbGrid, dbNavigator, datasource for comparison

The bitbutton (6) will trigger the export


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

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

          Query1.First;
          LineNumber := 1;

               [b] [i] {step 3} , where by iteration the data from the query is transported to excell [/i] [/b]
          While not query1.Eof do
          begin
            Inc(lineNumber);
            LineString := IntToStr(LineNumber);
            Range['A'+LineString, 'J'+LineString].Value :=
              VarArrayof([Query1OrderNo.value, Query1CustNo.Value
                  
[b] [i] {step 4}  is built in [/i] [/b]
                   FormatDateTime('d-mmm-yy',Query1SaleDate.Value),
                          Query1EmpNo.Value, Query1ShipVIA.Value,
                          Query1Terms.Value, Query1ItemsTotal.Value,
                          Query1TaxRate.Value, Query1Freight.Value,
                          Query1AmountPaid.Value]);
            Query1.Next;
          end;


          LineString := IntToStr(LineNumber);
  [b] [i] {step 5 and 6} [/i] [/b]
          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';
             [b] [i] {step 7} [/i] [/b]
          Range['A1','J'+LineString].AutoFormat(xlRangeAutoFormatlist1, true,
                   true,true,true,true,true);
               [b] [i] {step 8} [/i] [/b]
          Range['A1','J'+LineString].Columns.AutoFit;

            [b][i]end of macro stuff [/i][/b]


          ActiveWorkbook.SaveAs(FileName,xlNormal, ', ', false, false,
                     xlNochange,xlUserResolution,False,EmptyParam,EmptyParam,
                     LCID);
          Quit;
        finally
        disconnect;
      end;  //try
    end;  //with Excelapplication1
  end;  //if  execute
 end; //with Savedialog1

end;

Comments:

Range("A1").Select
ActiveCell.FormulaR1C1 = "Order No"
as described in step 1
Has the equivalent in Delphi

Range['A1','A1'].select;
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

Code: 
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
Steven
Register to add this FAQ to your archive
Register to rate this FAQ  : BAD 1 2 3 4 5 6 7 8 9 10 GOOD
Please Note: 1 is Bad, 10 is Good :-)

Part and Inventory Search

Back
Top