INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

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.

Jobs

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)
  

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
     {step 1}
    Range("A1").Select
    ActiveCell.FormulaR1C1 = "Order No"
    Range("B1").Select
    ActiveCell.FormulaR1C1 = "Cust No"
    Range("C1").Select
      etc..
    Range("J1").Select
    ActiveCell.FormulaR1C1 = "Amount Paid"
     {step 2}
    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
      {step 3}
    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
      etc..
    Range("H3").Select
    ActiveCell.FormulaR1C1 = "2"
    Range("I3").Select
    ActiveCell.FormulaR1C1 = "1000"
    Range("J3").Select
    ActiveCell.FormulaR1C1 = "9000"
     {step 4}
    Columns("C:C").Select
    Selection.NumberFormat = "d-mmm-yy"
     {step 5}
    Columns("H:H").Select
    Selection.NumberFormat = "0.00%"
     {step 6}
    Columns("G:G").Select
    Selection.NumberFormat = "$#,##0.00"
    Columns("I:I").Select
    Selection.NumberFormat = "$#,##0.00"
    Columns("J:J").Select
    Selection.NumberFormat = "$#,##0.00"
     {step 7}
    Range("A1:J3").Select
    Selection.AutoFormat Format:=xlRangeAutoFormatList1, Number:=True, Font:= _
        True, Alignment:=True, Border:=True, Pattern:=True, Width:=True
     {step 8}
    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
              {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
          begin
            HorizontalAlignment := xlcenter;
            VerticalAlignment := xlBottom;
            Wraptext := false;
            Orientation := 0;
            ShrinkTofit := false;
            MergeCells := false;
            Font.Bold := true;
          end;

          Query1.First;
          LineNumber := 1;

                {step 3} , where by iteration the data from the query is transported to excell
          While not query1.Eof do
          begin
            Inc(lineNumber);
            LineString := IntToStr(LineNumber);
            Range['A'+LineString, 'J'+LineString].Value :=
              VarArrayof([Query1OrderNo.value, Query1CustNo.Value
                  
{step 4}  is built in
                   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);
   {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,
                   true,true,true,true,true);
                {step 8}
          Range['A1','J'+LineString].Columns.AutoFit;

            end of macro stuff


          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

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

My Archive

Resources

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