Title: How to export a dataset to excel, without knowing VB
Question: The end user of your (database) program, eventually wants the ability to create his own graphs with the data. In most cases they look for Excel. The programmer not familiar with VB or VBA has to learn a little of VB to acomplish this. Instead of trying to understand the vbaxl8 Help file, we can use the macro recorder to see how excell use the commands.
Answer:
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 ms help files for months.
We will use the Macro Recorder and editor to retrieve all the vb ingredients
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, and write it to an Excel file.
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 command made visible.
It should look like this:
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
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);
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"
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
Range['A1','J1'].Value := VarArrayOf(['Order No',
'Cust No','Sale Date','Emp No',
'Ship Via', 'Terms','Items Total',
'Tax Rate','Freight','Amount Paid']);
This methodology I found in the book: Desenvolvendo Aplicacoes com Delphi 6 written by Bruno Sonnino, and adapted it to suite my needs.