Export data to excel using delphi 6

[PlutoDev]

Hi,
I build my application with delphi 6. now I want to export data to microsoft excel, but i don't now VBA.


Could you help me how to export data to excel

Thank's a lot for every body

 

[svanels]

Don,t know VB also but check out How to export data to excel faq102-1562 in the FAQ area of this forum

Regards Steven van Els
SAvanEls@cq-link.sr

 

[Guest_imported]

I had taken this code form a website,the URL i dont know. I think this code will help you a lot.

Prepare the Type Library.

-A pascal unit needs to be created from the type library file.
-Select Project|Import Type Library menu item.
-Press the add button and select the following file
c:\program files\microsoft office\office\excel8.olb
Select OK.
-Unfortunately, this leaves the project in a state that will not compile, this is because the excel_tlb unit redefines the word application to mean something else.
The easiest way to resolve this is to remove the excel_tlb unit from the project and only add it to the uses clause of the units that perform automation.
Documentation
-The help file c:\program files\microsoft office\office\vbaxl8.hlp contains information about the objects available for excel.
-The macro recorder allows VBA code to be created. This can then be taken and translated into Delphi quite easily.
The following code example demonstrates the creation of a simple spreadsheet and population of it with data. Ensure that the unit excel_tlb is included within the module.

-It is strongly recommended that automation code is kept within a separate module as this will prevent problems caused by name clashes.
Unit sheet;
//--------------------------------------------------------------------
interface
//--------------------------------------------------------------------
uses
windows, sysutils, excel_tlb;

Procedure CreateSpreadsheet;
//--------------------------------------------------------------------
implementation
//--------------------------------------------------------------------
Procedure CreateSpreadsheet(filename : string);
var
xla : _Application;
xlw : _Workbook;
LCID : integer;
begin
xla := CoApplication.Create;
LCID := GetUserDefaultLCID;
try
xla.Visible[LCID] := true;
// blank workbook
//xlw := xla.Workbooks.Add(xlWBATWorksheet, LCID);
// new workbook based on template
xlw := xla.Workbooks.Add(
'c:\delphi\excel\sample\demo.xlt',
LCID);
xla.Range['A1', 'A1'].Value := 'Date';
xla.Cells[1, 2].Value := FormatDateTime('dd-mmm-yyyy', Now);
xla.Cells[3, 1].Value := 'Numbers';
xla.Range['B3', 'E3'].Value := VarArrayOf([1, 10, 100, 1000]);
xla.Range['F3', 'F3'].Formula := '=Sum(B3:E3)';
OLEVariant(xla).Run(
'Demo',
FormatDateTime('dd-mmm-yyyy', Now)
);
xlw.SaveAs(
filename,
xlWorkbookNormal,
'','',False,False,
xlNoChange,
xlLocalSessionChanges,
true,0,0,LCID);
finally
xla.Quit;
end;
end;
//--------------------------------------------------------------------
end.
Add the type library unit to the uses clause.
uses
windows, sysutils, excel_tlb;
The first line of code creates an Excel application object.
xla := CoApplication.Create;
The following line of code retrieves the user default locale identifier. This is required by many of Excel’s methods and properties.
LCID := GetUserDefaultLCID;
The following line of code sets the visible property to true. This causes the excel window to become visible. This is useful for checking that the code is working correctly during development.
Note : This call takes the LCID parameter. Unfortunately this is not documented within the Excel help file. The file c:\program files\borland\Delphi 3\imports\excel_tlb.pas shows the function property and method definitions.
xla.visible[LCID] := true;
The following creates a new worksheet, assigning a reference to it within a Delphi variable. Under VBA, the template parameter is optional, Under Delphi it must be supplied.
xlw := xla.Workbooks.Add('c:\delphi\excel\sample\demo.xlt', LCID);
Note : You do not need to supply an Excel template file (.xlt), though it is the best way to set the formatting information. The more that can be done within excel, the less needs to be done within Delphi. This leads to simpler and more maintainable systems.
To create a blank workbook use:
xlw := xla.Workbooks.Add(xlWBATWorksheet, LCID);
The following two lines each set a single cell with a value. They show the two different cell referencing techinques.
xla.Range['A1', 'A1'].Value := 'Date';
xla.Cells[1, 2].Value := FormatDateTime('dd-mmm-yyyy', Now);
The following line demonstrates setting a row of data with one call. This give a significant performance advantage.
xla.Range['A2', 'D2'].Value := VarArrayOf([1, 10, 100, 1000]);
The following line demonstrates setting a formula.
xla.Range['E2', 'E2'].Formula := '=Sum(a2:d2)';
The follwing line of code executes a VBA function which is stored within the template file. The code does looks a bit puzzling at first. By typecasting xla to an OLEVariant forces the call to use late binding rather than early. (Causing the method name and parameters to be resolved at runtime rather than compile time). This is required for this call as Delphi cannot tell how many parameters the macro ‘Demo’ has.
OLEVariant(xla).Run(
'Demo',
FormatDateTime('dd-mmm-yyyy', Now));
The following code saves the spreadsheet as filename. All parameters must be supplied even although most are optional within VBA.
xlw.SaveAs(
filename,
xlWorkbookNormal,
'', '',False,False,
xlNoChange, xlLocalSessionChanges,
true, 0, 0, LCID);
The following line of code closes Excel and de-allocates any memory associated with it.
xla.quit;
Summary
· Always use early binding.
· If late binding is required for some calls, use early binding in general and typecast the object variable to an OLEVariant for the calls that require late binding.
· Do not include the type library unit within your project.
· Keep automation code within a separate unit.
· Use the Excel macro recorder to prototype your automation code.
· Use the vbaxl8.hlp file for Excel object programming information.
· Use the unit excel_tlb.pas to check for Delphi parameter types and numbers.
· Load up Excel spreadsheet templates (.xlt files) that contain predefined formatting and then super-impose data. This is faster and reduces the programming required to create formatted spreadsheets. Templates MUST be stored with the application in the application directory. This will remove the possibility of name clashes. Template files can also contain Excel macros that can be run from Delphi.
· Ensure that the quit method is called for the Excel application object (xla.quit). Not calling xla.quit will quickly exhaust all windows resources as multiple instances of Excel will be created.
· It is easy to check for multiple instances of Excel by using the NT Task Manager Processes Page (press CTL+ALT+Del to access this.)
· On large spreadsheets, setting multiple cells on each automation call will dramatically improve performance. Try not to sacrifice code readability.
Only small spreadsheets can be efficiently populated one cell at a time.
Larger sheets should be populated a row at a time.
The additional complexity of implementing a sheet at a time data population is not worthwhile.
Using the clipboard to transfer data is not recommended as this will corrupt the clipboard and may lead to obscure code.
Saving data as a CSV file and loading it into Excel may be faster, but will require formatting to be done within code and make coding/error handling more complex.
The procedures used for testing:
//-----------------------------------------------------------------------
procedure FillByCell;
var
xla : _Application;
xlw : _Workbook;
LCID : integer;
i,j : integer;
begin
xla := CoApplication.Create;
LCID := GetUserDefaultLCID;
try
xlw := xla.Workbooks.Add(xlWBATWorksheet, LCID);
for i:=1 to ROWS do begin
for j:=1 to 10 do begin
xla.Cells[i,j] := i+j;
end;
end;
xlw.close(false, '', false, LCID);
finally
xla.Quit;
end;
end;
//-----------------------------------------------------------------------
procedure FillByRow;
var
xla : _Application;
xlw : _Workbook;
CellFrom : string;
CellTo : string;
i,j : integer;
Row : array[1..10] of variant;
LCID : integer;
begin
xla := CoApplication.Create;
LCID := GetUserDefaultLCID;
try
xlw := xla.Workbooks.Add(xlWBATWorksheet, LCID);
for i:=1 to ROWS do begin
for j:=1 to 10 do begin
Row[j] := i+j;
end;
CellFrom := 'A' + InttoStr(i);
CellTO := 'J' + InttoStr(i);
xla.Range[CellFrom, CellTo].Value := VarArrayOf(Row);
end;
xlw.close(false, '', False, LCID);
finally
xla.Quit;
end;
end;
//-----------------------------------------------------------------------
procedure FillBySheet;
var
xla : _Application;
xlw : _Workbook;
CellFrom : string;
CellTo : string;
i,j : integer;
range : Variant;
row : array [1..10] of Variant;
LCID : integer;
begin
xla := CoApplication.Create;
LCID := GetUserDefaultLCID;
try
xlw := xla.Workbooks.Add(xlWBATWorksheet, LCID);
Range := VarArrayCreate([1, ROWS], varVariant);
for i:=1 to ROWS do begin
for j:=1 to 10 do begin
row[j] := i+j;
end;
Range := VarArrayOf(row);
end;
CellFrom := 'A' + InttoStr(1);
CellTO := 'J' + InttoStr(ROWS);
xla.Range[CellFrom, CellTo].FormulaArray := Range;
xlw.close(false, '', False, LCID);
finally
xla.Quit;
end;
end;

Appendix 2 – Using a Delphi Wrapper Class
Rather than embedding Automation calls directly within your application, this example demonstrates how a simple Delphi class can be used to encapsulate the Excel objects for your applicaions. This can give you a simpler interface to the objects and may help deal with any changes to the object interfaces in future versions of Excel.
unit sheet;
interface
uses
EXCEL_TLB, windows, sysutils;
//-------------------------------------------------------------------------
type
tExcel = class
private
xla : _Application;
xlw : _Workbook;
LCID : integer;
procedure fSetVisible(Visible : boolean);
function fGetVisible : boolean;
procedure fSetCell(Cell : string; Value : OLEVariant);
function fGetCell(Cell : string) : OleVariant;
public
constructor create;
destructor destroy; override;
procedure AddWorkBook(Template : OleVariant);
procedure SaveAs(filename : string);

property Visible : boolean
read fGetVisible write fSetVisible;
property Cell[Cell : string] : OleVariant
read fGetCell write fSetCell;
end;
//-------------------------------------------------------------------------
Procedure CreateSpreadsheet(filename : string);
//-------------------------------------------------------------------------
implementation
//-------------------------------------------------------------------------
constructor tExcel.create;
begin
LCID := GetUserDefaultLCID;
xla := CoApplication.Create;
end;
//-------------------------------------------------------------------------
destructor tExcel.destroy;
begin
xla.Quit;
inherited;
end;
//-------------------------------------------------------------------------
procedure tExcel.AddWorkBook(Template : OleVariant);
begin
xlw := xla.Workbooks.Add(Template, LCID);
end;
//-------------------------------------------------------------------------
procedure tExcel.fSetVisible(Visible : boolean);
begin
xla.visible[lcid] := Visible;
end;
//-------------------------------------------------------------------------
function tExcel.fGetVisible : boolean;
begin
result := xla.visible[lcid];
end;
//-------------------------------------------------------------------------
procedure tExcel.fSetCell(Cell : string; Value : OLEVariant);
begin
xla.Range['A1', 'A1'].Value := value;
end;
//-------------------------------------------------------------------------
function tExcel.fGetCell(Cell : string) : OleVariant;
begin
result := xla.Range['A1', 'A1'].Value;
end;
//-------------------------------------------------------------------------
procedure tExcel.SaveAs(filename : string);
begin
xlw.SaveAs(
filename,
xlWorkbookNormal,
'',
'',
False,
False,
xlNoChange,
xlLocalSessionChanges,
true,
0,
0,
LCID);
end;

The following code example uses this class to create a spreadsheet.

Procedure CreateSpreadsheet(filename : string);
var
xl : tExcel;
begin
xl := tExcel.create;
try
xl.AddWorkBook('c:\graham\excel\sample2\ssddemo.xlt');
xl.visible := true;
xl.cell['a1'] := 'testing';
xl.SaveAs(filename);
finally
xl.free;
end;
end;

Delphi 3 and Automation with Excel.
Automation allows one application to control another application. The application being controlled is called an automation server (in our case Excel). The application controlling the server is called an automation controller.
There are two ways that automation servers can be accessed:
Late Binding (IDispatch interface)
When using this method, function names and parameter datatypes are resolved at runtime, all parameters are passed as variants.
As no errors in function names or parameter types are reported at compile time, this method is error prone.
As function names and parameter types need to be looked up at runtime, performance is slow.
The only advantage of this method for Delphi programming is that it is the only way optional parameters can be omitted from function calls.
Early Binding (Using type libraries/interfaces)
When using this method, function names and parameter datatypes are all resolved at compile time.
A type library needs to be imported into Delphi. A type library is a language neutral description of all the objects and functions exposed by a server. (This is similar in nature to a C header file).
All parameters need to be supplied, even when calling functions where the documentation states that some are optional. This enables many errors to be detected and corrected before ever running a program.
Performance is better than for late binding.
Due to the advantages of the second approach, the rest of the document demonstrates the basics of creating an application with early binding. All applications that use Excel Automation should use this technique unless there is a strong justification for not doing so.

I think this would ve answered all your doubts.

Parag (jparag2k@yahoo.com)

 

[paulhmorris]

Another simple method would be to export the data to anyfile finishing with xls. and use a TAB char to seperate your fields. Excle will open the file with no problem.

Paul