急问:请教高手,如何实现exacl和access的相互导入和导出? 谢谢 解决方案 » 免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货 呵呵要是从office软件入手就容易了。 使用ADO,用JET 4.0驱动可以直接打开Access数据库,加入Extended=Excel 8.0的指示以后也可以打开Excel文件(前提是Excel工作簿的内容是简单的行列式数据)。如果Excel格式非常复杂就没什么好办法了,要使用Excel自动化或者XLSRW之类的软件来直接读写文件了。 //转贴:关于Excel倒入SqlServer,其实导入Access一样,更改一下Provider//open excelprocedure TForm1.ConnectClick(Sender: TObject);var str,path,xlsName:string;begin//connect excelpath:=extractfilepath(application.exename);OpenDialog1.InitialDir :=path;OpenDialog1.Filter :='*.xls|*.xls';if OpenDialog1.Execute then xlsName :=extractfilename(OpenDialog1.FileName);str:='Provider=Microsoft.Jet.OLEDB.4.0;' + 'Data Source= ' + path + xlsName + ';Extended Properties=Excel 8.0;' + 'Persist Security Info=False';conn.Close;conn.ConnectionString :=str;try conn.Connected :=true; Adotable1.Close; Adotable1.TableDirect:=True ; adotable1.tablename:='sheet1$'; tType.ItemIndex :=-1; try adotable1.Open; dbgrid1.Columns[0].Width :=50; dbgrid1.Columns[1].Width :=50; dbgrid1.Columns[2].Width :=50; dbgrid1.Columns[3].Width :=80; dbgrid1.Columns[4].Width :=80; dbgrid1.Columns[5].Width :=200; except showmessage('Open Error'); end;except self.Caption :='Excel connect error'; exit;end;end;//导入,我使用批处理,adoquery1.locktype->ltBatchOptimisticADOQuery1.Close;ADOQuery1.SQL.Clear;ADOQuery1.SQL.Add('select * from tablename where 0=1');ADOQuery1.Open;ADOTable1.First;while not ADOTable1.Eof do begin ADOQuery1.Append; ADOQuery1.FieldByName('xx').AsString :=trim(Adotable1.fieldbyname('yy').AsString ); //和上句相同,相应字段数据导入即可 ADOQuery1.Post; ADOTable1.Next; end;ADOQuery1.UpdateBatch();showmessage('over');----------------------------下面是个查询的示例,它通过用于 Jet 的 OLE DB 提供程序查询 Excel 电子表格。INSERT INTO urtableSELECT * FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0', 'Data Source="c:\Finance\account.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')---------------------------ADOConnection 指向excel, 用Jet4.0 ,Extended properties设为Excel 8.0SELECT * into table FROM Tab1 IN [ODBC][ODBC;Driver=SQL Server;UID=sa;PWD=;Server=127.0.0.1;DataBase=Demo;]--------------------------------------------------SELECT * FROM OpenDataSource('Microsoft.Jet.OLEDB.4.0','Data Source="C:\temp\b.xls";Extended Properties="Excel 5.0;HDR=Yes;";Persist Security Info=False') 求控件安装密码 infopower4000pro_vcl6 ADO数据库连接用字符串用代码实现,其它可视控件可以绑定这个连接字符串吗? fastreport 如何在程序编译后来设计报表? 请教关于NMFTP的问题! 查询问题,急急 mapobject 的技术 请教查询时间的sql语句! 一个关于程序结束时出现的问题 IE浏览器运行后,都有那些信息隐藏了呀??? 如何分批取后台数据库的数据 COM VS CORBA 动态创建一个TQRExpr,为什么不显示计算结果呢?在线等待!!!
//open excel
procedure TForm1.ConnectClick(Sender: TObject);
var
str,path,xlsName:string;
begin
//connect excel
path:=extractfilepath(application.exename);
OpenDialog1.InitialDir :=path;
OpenDialog1.Filter :='*.xls|*.xls';
if OpenDialog1.Execute then
xlsName :=extractfilename(OpenDialog1.FileName);str:='Provider=Microsoft.Jet.OLEDB.4.0;' +
'Data Source= ' + path + xlsName + ';Extended Properties=Excel 8.0;' +
'Persist Security Info=False';
conn.Close;
conn.ConnectionString :=str;
try
conn.Connected :=true;
Adotable1.Close;
Adotable1.TableDirect:=True ;
adotable1.tablename:='sheet1$';
tType.ItemIndex :=-1;
try
adotable1.Open;
dbgrid1.Columns[0].Width :=50;
dbgrid1.Columns[1].Width :=50;
dbgrid1.Columns[2].Width :=50;
dbgrid1.Columns[3].Width :=80;
dbgrid1.Columns[4].Width :=80;
dbgrid1.Columns[5].Width :=200;
except
showmessage('Open Error');
end;
except
self.Caption :='Excel connect error';
exit;
end;end;//导入,我使用批处理,adoquery1.locktype->ltBatchOptimistic
ADOQuery1.Close;
ADOQuery1.SQL.Clear;
ADOQuery1.SQL.Add('select * from tablename where 0=1');
ADOQuery1.Open;ADOTable1.First;
while not ADOTable1.Eof do
begin
ADOQuery1.Append;
ADOQuery1.FieldByName('xx').AsString :=trim(Adotable1.fieldbyname('yy').AsString );
//和上句相同,相应字段数据导入即可
ADOQuery1.Post;
ADOTable1.Next;
end;
ADOQuery1.UpdateBatch();
showmessage('over');----------------------------
下面是个查询的示例,它通过用于 Jet 的 OLE DB 提供程序查询 Excel 电子表格。
INSERT INTO urtable
SELECT *
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="c:\Finance\account.xls";User ID=Admin;Password=;Extended
properties=Excel 5.0')
---------------------------
ADOConnection 指向excel, 用Jet4.0 ,Extended properties设为Excel 8.0
SELECT * into table FROM Tab1 IN [ODBC]
[ODBC;Driver=SQL Server;UID=sa;PWD=;Server=127.0.0.1;DataBase=Demo;]
--------------------------------------------------
SELECT *
FROM OpenDataSource('Microsoft.Jet.OLEDB.4.0','Data Source="C:\temp\b.xls";Extended Properties="Excel 5.0;HDR=Yes;";Persist Security Info=False')