就是导进去SQL的时候是一张新表里面有excel表的数据, 研究了两天了,论坛上得各种方法都试了,还是没做出来。各种求......
解决方案 »
- 一句(关键)代码实现全透明_GDI+版_压轴
- 关于DLL问题,DLL传参给EXE
- 杀毒软件与可执行文件的问题,请高手指点!
- 我做的帮助文件中的检索,为什么检索不了,除了Full-text search=Yes这句话,还需要什么东西?
- dbgrid或dbgridEh中某一字段的值从另一个表中提取,如何实现?对面的大虾看过来!!看过来!!!!!有MM
- 求三个sql语句的差别
- 不知哪位高手的If语句学的好。帮帮忙来看看这个复杂的判断语句~!
- 我来。。。。给大家。。。。送钱了。。
- 一个小问题:(
- 大家清欣赏,对你的技巧有帮助
- 怎么实现根据运行时的某一变量的值,决定执不执行某一函数?
- Delphi XE2 试用新功能:ZIP压缩文件的操作
//xlsFileName : string;
excelApp : Variant;
i,j,l : integer;
begin
if self.OpenDialog1.Execute then
begin
try
excelApp := CreateOleObject('excel.application');
excelApp.workbooks.open(OpenDialog1.FileName);
except
Messagebox(0,'Microsoft Excel 可能没有安装!','提示!',mb_Ok);
excelApp.quit;
exit;
end;
end
else
exit;
ADOQuery1.Close;
ADOQuery1.SQL.Text:='select * from TableName where 1=-1';
ADOQuery1.Open;
l :=excelApp.worksheets[1].range['a1'].currentregion.rows.count;
for I := 2 to excelApp.worksheets[1].range['a1'].currentregion.rows.count do
try
adoquery1.Append;
for j:=1 to ADOQuery1.fields.Count do
begin
adoquery1.Fields.Fields[j-1].Value:=excelApp.worksheets[1].Cells[i,j].Value;
//ComboBox1.Items.Add(excelApp.worksheets[1].Cells[i,j].Value);
end;
adoquery1.Post;
StatusBar1.Panels.Items[0].Text:=IntToStr(i);
StatusBar1.Panels.Items[1].Text:=IntToStr(l);
ProgressBar1.Position:=Round(100*(i/l));;
except
showmessage('导入第'+inttostr(I-1)+'条据时失败');
end;
ExcelApp.Quit;
setbar; ShowMessage('导入成功!重新打开刷新');
end;
strSQL:='CREATE TABLE AA (a1 varchar[20],a2 varchar[20])'
with adoquery1 do
begin
Close;
Sql.clear;
sql.add(strSQL);
execsql;
end
然后导入Excel数据
http://topic.csdn.net/u/20110826/12/6d8347d0-9d0e-4dd1-be22-a60198e6fe4e.html
Excel导入数据:with adoquery1 do
close;
sql.Clear;
ParamCheck := false;
sql.text:='insert into A(a1,a2,a3) SELECT * FROM [EXCEL 8.0;DATABASE='+strPath+'].[SHEET1$]';
//如果是导入Sheet2,修改成[SHEET2$]
//strPath:Excel的全路径
execsql;
end;
var
ExcelApp: Variant;
strPath:string;
begin
if OpenDialog1.Execute then begin
ExcelApp := CreateOleObject( 'Excel.Application' );
ExcelApp.WorkBooks.Open(OpenDialog1.FileName);
ExcelApp.ActiveWorkBook.Save;
ExcelApp.Quit;
varclear(ExcelApp);
ADOConnection1.ConnectionString := 'Provider=Microsoft.Jet.OLEDB.4.0;Data Source=' + OpenDialog1.FileName + ';Extended Properties=excel 8.0;Persist Security Info=false';
ADOConnection1.Connected := True; with adoquery1 do
close;
adoquery1.sql.Clear;
adoquery1.ParamCheck := false;
adoquery1.SQL.Text :='CREATE TABLE A(a1 varchar[20],a2 varchar[20],a3 varchar[20])';
adoquery1.sql.text :='insert into A(a1,a2,a3) SELECT * FROM [EXCEL 8.0;DATABASE='+strPath+'].[SHEET1$]';
adoquery1.execsql;
end;
ADOConnection1.Connected:=false;
messagebox(handle,'数据处理完毕','提示',MB_OK+MB_ICONINFORMATION);
end;最后提示对象打开时不能操作。 头大了
close;
adoquery1.sql.Clear;
adoquery1.ParamCheck := false;
adoquery1.SQL.Text :='CREATE TABLE A(a1 varchar[20],a2 varchar[20],a3 varchar[20])';
adoquery1.sql.text :='insert into A(a1,a2,a3) SELECT * FROM [EXCEL 8.0;DATABASE='+strPath+'].[SHEET1$]';
adoquery1.execsql;
end;
你这块写的有问题啊
adoquery1.sql.text :='insert into A(a1,a2,a3) SELECT * FROM [EXCEL 8.0;DATABASE='+strPath+'].[SHEET1$]';
你这句赋值会覆盖第一次赋值的,adoquery1它不会顺序去执行SQL里面的语句。
分开写,分开执行。
with adoquery1 do
close;
adoquery1.sql.Clear;
adoquery1.ParamCheck := false;
adoquery1.SQL.Text :='CREATE TABLE A(a1 varchar[20],a2 varchar[20],a3 varchar[20])';
adoquery1.execsql;
adoquery1.sql.Clear;
adoquery1.sql.text :='insert into A(a1,a2,a3) SELECT * FROM [EXCEL 8.0;DATABASE='+strPath+'].[SHEET1$]';
adoquery1.execsql;
end;
其次你这段代码相当乱啊,是不是你省略了一些内容procedure TForm1.Button1Click(Sender: TObject);
var
ExcelApp: Variant;
strPath:string;
begin
if OpenDialog1.Execute then begin
ExcelApp := CreateOleObject( 'Excel.Application' );
ExcelApp.WorkBooks.Open(OpenDialog1.FileName);
ExcelApp.ActiveWorkBook.Save;
ExcelApp.Quit;
varclear(ExcelApp);
//你上面这几句话用OLE连接Excel什么都没干,不明白你的意思
ADOConnection1.ConnectionString := 'Provider=Microsoft.Jet.OLEDB.4.0;Data Source=' + OpenDialog1.FileName + ';Extended Properties=excel 8.0;Persist Security Info=false';
ADOConnection1.Connected := True;
//你用ADOConnection1是连接Excel干吗?
//你只用adoquery1连接到SQL Sever上先执行创建数据表,然后再执行导入就可以了 with adoquery1 do
close;
adoquery1.sql.Clear;
adoquery1.ParamCheck := false;
adoquery1.SQL.Text :='CREATE TABLE A(a1 varchar[20],a2 varchar[20],a3 varchar[20])';
adoquery1.sql.text :='insert into A(a1,a2,a3) SELECT * FROM [EXCEL 8.0;DATABASE='+strPath+'].[SHEET1$]';
adoquery1.execsql;
end;
ADOConnection1.Connected:=false;
messagebox(handle,'数据处理完毕','提示',MB_OK+MB_ICONINFORMATION);
end;
ExcelApp := CreateOleObject( 'Excel.Application' );
ExcelApp.WorkBooks.Open(OpenDialog1.FileName);
ExcelApp.ActiveWorkBook.Save;
ExcelApp.Quit;
varclear(ExcelApp);
这个我是想 获取它的文件路径,你提供的代码里面strpath不是需要路径么?在加一句strpath:=opendialog1.filename,ok不?
with adoquery1 do
close;
adoquery1.sql.Clear;
adoquery1.ParamCheck := false;
adoquery1.SQL.Text :='CREATE TABLE A(a1 varchar[20],a2 varchar[20],a3 varchar[20])';
adoquery1.execsql; adoquery1.sql.text :='insert into A(a1,a2,a3) SELECT * FROM [EXCEL 8.0;DATABASE='+strPath+'].[SHEET1$]'; adoquery1.execsql;红色部分提示语法错误,不能同时执行多条SQL么?
var
strPath:string;
begin
ADOConnection1.ConnectionString := 'Provider=Microsoft.Jet.OLEDB.4.0;User ID=admin;Data Source=c:\test.mdb;Persist Security Info=False;';
ADOConnection1.Connected:=True;
strPath:='C:\test.xls';
with adoquery1 do
begin
close;
adoquery1.sql.Clear;
adoquery1.ParamCheck := false;
adoquery1.SQL.Text :='CREATE TABLE A(a1 varchar(20),a2 varchar(20),a3 varchar(20))';
//这里的创建表SQL语句刚才写的有点问题
adoquery1.execsql;
adoquery1.sql.Clear;
adoquery1.sql.text :='insert into A(a1,a2,a3) SELECT * FROM [EXCEL 8.0;DATABASE='+strPath+'].[SHEET1$]';
adoquery1.execsql;
end;
end;
var
strPath:string;
begin
ADOConnection1.ConnectionString:='Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Data Source=127.0.0.1;Initial Catalog=Test';
ADOConnection1.Connected:=True;
strPath:='C:\test.xls';
with adoquery1 do
begin
close;
adoquery1.sql.Clear;
adoquery1.ParamCheck := false;
adoquery1.SQL.Text :='CREATE TABLE A(a1 varchar(20),a2 varchar(20),a3 varchar(20))';
adoquery1.execsql;
adoquery1.sql.Clear;
adoquery1.sql.text :='Insert into TestA(a1,a2,a3) SELECT * FROM OpenDataSource(''Microsoft.Jet.OLEDB.4.0'',''Data Source="c:\test.xls";Extended Properties=Excel 8.0'')...[Sheet1$]';//这个是SQL的语句,之前的是Access的
adoquery1.execsql;
end;
end;
''Microsoft.Jet.OLEDB.4.0'',''Data Source="strPath";Extended Properties=Excel 8.0'')...[Sheet1$]'要不我加你Q吧 高手 把QQ给我。
Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
Dialogs, ComObj, DB, ADODB, StdCtrls;type
TForm1 = class(TForm)
Button1: TButton;
ADOConnection1: TADOConnection;
OpenDialog1: TOpenDialog;
DataSource1: TDataSource;
ADOQuery1: TADOQuery;
Button2: TButton;
procedure Button1Click(Sender: TObject);
private
{ Private declarations }
public
{ Public declarations }
end;var
Form1: TForm1;implementation{$R *.dfm}procedure TForm1.Button1Click(Sender: TObject);
var
ExcelApp: Variant;
strPath:string;
tablename:string;
begin
if OpenDialog1.Execute then begin
ExcelApp := CreateOleObject( 'Excel.Application' );
ExcelApp.WorkBooks.Open(OpenDialog1.FileName);
ExcelApp.ActiveWorkBook.Save;
ExcelApp.Quit;
varclear(ExcelApp);
//ADOConnection1.ConnectionString:='Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Data Source=' + OpenDialog1.FileName + ';Initial Catalog=Test';
//ADOConnection1.Connected:=True; //tablename :=TStringList.Create;
//ADOConnection1.ConnectionString := 'Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Data Source=' + OpenDialog1.FileName + ';Extended Properties=excel 8.0;Persist Security Info=false';
//ADOConnection1.Connected := True;
//tablename:= ExtractFileName(OpenDialog1.FileName);
strpath:= extractfilepath(application.ExeName);
with adoquery1 do
close;
(OpenDialog1.FileName);
adoquery1.sql.Clear;
adoquery1.ParamCheck := false;
//adoquery1.SQL.Text :='CREATE TABLE :tablename(a1 varchar(20),a2 varchar(20),a3 varchar(20))';
//adoquery1.SQL.Text :='set @TempSql= 'create table ' + @Tablename
//Execute (@Tempsql)';
adoquery1.SQL.Text :='CREATE TABLE Testd(a1 varchar(20),a2 varchar(20),a3 varchar(20))';
adoquery1.execsql;
adoquery1.sql.Clear;
adoquery1.sql.text :='Insert into Testd(a1,a2,a3) SELECT * FROM OpenDataSource(''Microsoft.Jet.OLEDB.4.0'',''Data Source="+strPath+";Extended Properties=Excel 8.0'')...[Sheet1$]';
adoquery1.execsql;end;
messagebox(handle,'数据处理完毕','提示',MB_OK+MB_ICONINFORMATION);
end;end.
var
strPath:string;
begin
if OpenDialog1.Execute then
begin
strpath:=OpenDialog1.FileName;
//strPath 必须是Excel的全路径,你之前的代码仅仅获取的是程序的当前目录
with adoquery1 do
begin
close;
sql.Clear;
ParamCheck := false;
SQL.Text :='CREATE TABLE Testd(a1 varchar(20),a2 varchar(20),a3 varchar(20))';
execsql;
sql.Clear;
sql.text :='Insert into Testd(a1,a2,a3) SELECT * FROM OpenDataSource(''Microsoft.Jet.OLEDB.4.0'',''Data Source="'+strPath+'";Extended Properties=Excel 8.0'')...[Sheet1$]';
execsql;
end;
end;
end;
Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
Dialogs, ComObj, DB, ADODB, StdCtrls;type
TForm1 = class(TForm)
Button1: TButton;
ADOConnection1: TADOConnection;
OpenDialog1: TOpenDialog;
DataSource1: TDataSource;
ADOQuery1: TADOQuery;
Button2: TButton;
procedure Button1Click(Sender: TObject);
private
{ Private declarations }
public
{ Public declarations }
end;var
Form1: TForm1;implementation{$R *.dfm}procedure TForm1.Button1Click(Sender: TObject);
varExcelApp: Variant;
strPath:string;
//tablename:string;
begin
if OpenDialog1.Execute then begin
ExcelApp := CreateOleObject( 'Excel.Application' );
ExcelApp.WorkBooks.Open(OpenDialog1.FileName);
ExcelApp.ActiveWorkBook.Save;
ExcelApp.Quit;
varclear(ExcelApp);
strpath:=OpenDialog1.FileName;
with adoquery1 do
close;
adoquery1.sql.Clear;
adoquery1.ParamCheck := false;
adoquery1.SQL.Text :='CREATE TABLE Testj(a1 varchar(20),a2 varchar(20),a3 varchar(20))';
adoquery1.execsql;
adoquery1.sql.Clear;
adoquery1.sql.text :='Insert into Testj(a1,a2,a3) SELECT * FROM OpenDataSource(''Microsoft.Jet.OLEDB.4.0'',''Data Source="+strPath+";Extended Properties=Excel 8.0'')...sheet1$';
adoquery1.execsql;end;
messagebox(handle,'数据处理完毕','提示',MB_OK+MB_ICONINFORMATION);
end;end.改过来了,同样的错误。我去其他论坛看看 好像很多人出现这样的错误
2、你看看你的Excel里面是不是有Sheet1这个工作表
其实就是SQL语句的事,你看看下面的帖子
http://topic.csdn.net/u/20091129/17/4b0211b7-c1f2-431f-8655-61196a982f1e.html