Delphi Pages Forums  

Go Back   Delphi Pages Forums > Delphi Forum > General

Lost Password?

Reply
 
Thread Tools Display Modes
  #1  
Old 02-12-2007, 09:02 PM
dorjvar dorjvar is offline
Member
 
Join Date: May 2005
Posts: 38
Default An Access Table To Excell

Hello dear friends
My DelphiProject Database Is MSaccess
I have A table In my dataBase and I want Export It To Excell
my tabel Recordcount is(40000) and I want convert
It to excell very fast
(I can export it with OLE but It take a lot of times)
I want use method Like Import/Export that be very fast
Have can I do It in my delphi project
Thanks Dorjvar
Reply With Quote
  #2  
Old 02-12-2007, 10:47 PM
Norrit Norrit is offline
Moderator
 
Join Date: Aug 2001
Location: Landgraaf
Posts: 7,333
Default RE: An Access Table To Excell

Normally you can export the table to csv very fast (access has a routine that does so I believe)...
csv can be imported directly in excel, just set the delimiter for your csv...

Objective reality is a delirium caused by lack of alcohol in blood.
There is no place like 127.0.0.1
Reply With Quote
  #3  
Old 02-12-2007, 10:47 PM
Norrit Norrit is offline
Moderator
 
Join Date: Aug 2001
Location: Landgraaf
Posts: 7,333
Default RE: An Access Table To Excell

Normally you can export the table to csv very fast (access has a routine that does so I believe)...
csv can be imported directly in excel, just set the delimiter for your csv...

Objective reality is a delirium caused by lack of alcohol in blood.
There is no place like 127.0.0.1
Reply With Quote
  #4  
Old 02-12-2007, 10:47 PM
Norrit Norrit is offline
Moderator
 
Join Date: Aug 2001
Location: Landgraaf
Posts: 7,333
Default RE: An Access Table To Excell

Normally you can export the table to csv very fast (access has a routine that does so I believe)...
csv can be imported directly in excel, just set the delimiter for your csv...

Objective reality is a delirium caused by lack of alcohol in blood.
There is no place like 127.0.0.1
Reply With Quote
  #5  
Old 02-13-2007, 12:03 AM
mshkolnik mshkolnik is offline
Senior Member
 
Join Date: Jul 2001
Posts: 4,195
Default RE: An Access Table To Excell

Use the TSMExportToXLS component from SMExport suite:
http://www.scalabium.com/sme

This component doesn't use the ole-automation and very fast

With best regards, Mike Shkolnik
http://www.scalabium.com
Reply With Quote
  #6  
Old 02-13-2007, 08:53 AM
Jasser Jasser is offline
Moderator
 
Join Date: Jan 2005
Location: Saudi Arabia
Posts: 5,059
Default RE: An Access Table To Excell

Try this link. It has small code and it's very fast and does not use OLE:

[link]http://www.swissdelphicenter.ch/en/showcode.php?id=725[/link]

Regards,
Abdulaziz Jasser
Reply With Quote
  #7  
Old 02-14-2007, 05:02 AM
praveenkumar_k praveenkumar_k is offline
Senior Member
 
Join Date: Sep 2006
Posts: 698
Default RE: An Access Table To Excell

Hi,
try this


function TForm1.ExportToExcel(oDataSet: TDataSet; sFile: String): Boolean;
var
iCol,iRow : Integer;

oExcel : TExcelApplication;
oWorkbook : TExcelWorkbook;
oSheet : TExcelWorksheet;
begin
iCol := 0;
iRow := 0;
result := True;

oExcel := TExcelApplication.Create(Application);
oWorkbook := TExcelWorkbook.Create(Application);
oSheet := TExcelWorksheet.Create(Application);

try
oExcel.Visible[0] := False;
oExcel.Connect;
except
result := False;
MessageDlg('Excel may not be installed', mtError, [mbOk], 0);
exit;
end;

//To make Excel visible.
oExcel.Visible[0] := True;

//Document title.
oExcel.Caption := 'Export Data';
oExcel.Workbooks.Add(Null,0);

oWorkbook.ConnectTo(oExcel.Workbooks[1]);
oSheet.ConnectTo(oWorkbook.Worksheets[1] as _Worksheet);

iRow := 1;

//To write the fileds names.
for iCol:=0 to oDataSet.FieldCount-1 do begin
oSheet.Cells.Item[iRow,iCol+1] := oDataSet.FieldDefs.Items[iCol].Name;
oSheet.Cells.Item[iRow,iCol+1] := oDataSet.Fields[iCol].FieldName;
end;

oDataSet.Open;
while NOT oDataSet.Eof do begin
Inc(iRow);

for iCol:=1 to oDataSet.FieldCount do begin
oSheet.Cells.Item[iRow,iCol] := oDataSet.Fields[iCol-1].AsString;
end;

oDataSet.Next;
end;

//Change the wprksheet name.
oSheet.Name := 'Praveen ';

//Change the font properties of all columns.
oSheet.Columns.Font.Color := clPurple;
oSheet.Columns.Font.FontStyle := fsBold;
oSheet.Columns.Font.Size := 10;

//Auto fit all columns.
oSheet.Columns.AutoFit;

//Delete file if exists.
DeleteFile(sFile);

{ oSheet.SaveAs(sFile);
oSheet.Disconnect;
oSheet.Free;

oWorkbook.Disconnect;
oWorkbook.Free;

oExcel.Quit;
oExcel.Disconnect;
oExcel.Free;}
end;

procedure TForm1.Button1Click(Sender: TObject);
var ADOConnection1: TADOConnection;
ADOTable1: TADOTable;
begin
// u have to keep one
// aDOcONNECTION AND oNE aDO tABLE

ExportToExcel(ADOTable1,'C:\Sample1.XLS');
end;


If this helps, Click Accept as Answer Link


[B][COLOR=red]

Have a nice & Peaceful Day My Friend

Regards
KPK
Reply With Quote
  #8  
Old 02-14-2007, 08:07 AM
Jasser Jasser is offline
Moderator
 
Join Date: Jan 2005
Location: Saudi Arabia
Posts: 5,059
Default RE: An Access Table To Excell

Nice code. From where do get it?

Regards,
Abdulaziz Jasser
Reply With Quote
Reply

Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is On

Forum Jump


All times are GMT. The time now is 12:02 PM.


Powered by vBulletin® Version 3.8.8
Copyright ©2000 - 2019, vBulletin Solutions, Inc.