Title: How to export data to Excel
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