ADO连接Excel,Access

注:Excel2003和之后的版本后缀名有所不同

代码

procedure TForm1.FormCreate(Sender: TObject);
begin
ADOConnection1.ConnectionString : = ' Provider=MSDASQL.1;Persist Security Info=False; '
+ ' Driver={Microsoft Excel Driver (*.xls)};DBQ=E:\Book1.xls ' ;
end ;

procedure TForm1.Button1Click(Sender: TObject);
begin
ADOQuery1.Connection : = ADOConnection1;
ADOQuery1.Close;
ADOQuery1.SQL.Text : = ' SELECT * FROM [Sheet1$] ' ;
ADOQuery1.Open;
end ;

DataSet导出到Excel

代码

procedure WriteToExcel(aDataSet: TDataSet; const sName, Title: string );
var
ExcelApplication1: TExcelApplication;
ExcelWorksheet1: TExcelWorksheet;
ExcelWorkbook1: TExcelWorkbook;
i, j, idx: integer;
filename: string ;
begin
filename : = Concat(extractfilepath(application.exename), sName, ' .xls ' );
try
ExcelApplication1 : = TExcelApplication.Create(Application);
ExcelWorksheet1 : = TExcelWorksheet.Create(Application);
ExcelWorkbook1 : = TExcelWorkbook.Create(Application);
ExcelApplication1.Connect;
except
Application.Messagebox( ' Excel not install! ' , ' Error! ' , MB_ICONERROR + mb_Ok);
Exit;
end ;
try
ExcelApplication1.Connect;
ExcelApplication1.Visible[ 0 ] : = True;
ExcelApplication1.Workbooks.Add(EmptyParam, 0 );
ExcelWorkbook1.ConnectTo(ExcelApplication1.Workbooks[ 1 ]);
ExcelWorksheet1.ConnectTo(ExcelWorkbook1.Worksheets[ 1 ] as _worksheet);
aDataSet.First;
for j : = 0 to aDataSet.Fields.Count - 1 do
begin
ExcelWorksheet1.Cells.item[ 3 , j + 1 ] : = aDataSet.Fields[j].DisplayLabel;
ExcelWorksheet1.Cells.item[ 3 , j + 1 ].font.size : = 10 ;
end ;
for i : = 4 to aDataSet.RecordCount + 3 do
begin
for j : = 0 to aDataSet.Fields.Count - 1 do
begin
ExcelWorksheet1.Cells.Item[i,j + 1 ].Value : = aDataSet.Fields[j].Asstring;
ExcelWorksheet1.Cells.Item[i,j + 1 ].font.size : = 10 ;
end ;
aDataSet.Next;
end ;
ExcelWorksheet1.Columns.AutoFit;
ExcelWorksheet1.Cells.item[ 1 , 2 ] : = Title;
ExcelWorksheet1.Cells.Item[ 1 , 2 ].font.size : = 14 ;
// ExcelWorksheet1.SaveAs(filename);
Application.Messagebox(PAnsiChar( ' Excel Successful ' + filename), ' Excel ' , mb_Ok);
finally
ExcelApplication1.Disconnect;
ExcelApplication1.Quit;
ExcelApplication1.Free;
ExcelWorksheet1.Free;
ExcelWorkbook1.Free;
end ;
end ;

 

连接Access:

 

代码

procedure TForm1.Button2Click(Sender: TObject);
const
SConnectionStringAccess = ' Provider=Microsoft.Jet.OLEDB.4.0;Data Source=%s;Persist Security Info=False ' ;
var
FADOConnection:TADOConnection;
begin
FADOConnection: = TADOConnection.Create( nil );
try
FADOConnection.LoginPrompt: = False;
FADOConnection.ConnectionString: = Format(SConnectionStringAccess,[ExtractFilePath(ParamStr( 0 )) + ' test.mdb ' ]);;
FADOConnection.Open;
finally
if FADOConnection.Connected then FADOConnection.Close;
if Assigned(FADOConnection) then FreeAndNil(FADOConnection);
end ;
end ;

 

 

转载于:https://www.cnblogs.com/Jekhn/archive/2010/12/25/1916603.html