现在有这么一个问题,我把EXECL导入到SQL SERVER的一个表里,但是很慢,我一个一万多条的EXECL表要20分种左右。有什么方法可以让他快一点吗?谁有这方面的代码?我的代码是:
procedure Tfrm_month.SpeedButton2Click(Sender: TObject);
var ExcelApp,MyWorkBook: OLEVariant;
i: Integer;
begin
try
messageDlg('在导入前请关闭所有EXCEL表!',mtWarning,[mbok],0);
ExcelApp:=CreateOleObject('Excel.Application');
MyWorkBook:=CreateOleobject('Excel.Sheet');
except
application.Messagebox('无法打开Xls文件请确认已经安装EXCEL.','',mb_OK+mb_IconStop);
Exit;
end;
if OpenDialog1.Execute then
MyworkBook:= ExcelApp.workBooks.Open(OpenDialog1.FileName);
i:=2;
while trim(string(MyWorkBook.WorkSheets[1].Cells[i,1]))<>''do begin
adotable1.Append;
adotable1.FieldByName('Name').AsString := MyWorkBook.WorkSheets[1].Cells[i,1].Value;
adotable1.FieldByName('type').AsString := MyWorkBook.WorkSheets[1].Cells[i,2].Value;
adotable1.FieldByName('min').AsString := MyWorkBook.WorkSheets[1].Cells[i,3].Value;
adotable1.FieldByName('fee').AsString := MyWorkBook.WorkSheets[1].Cells[i,4].Value;
adotable1.Post;
inc(i);
end;
ExcelApp.WorkBooks.Close;
ExcelApp.quit;
ExcelApp:=Unassigned;
end;
end;
procedure Tfrm_month.SpeedButton2Click(Sender: TObject);
var ExcelApp,MyWorkBook: OLEVariant;
i: Integer;
begin
try
messageDlg('在导入前请关闭所有EXCEL表!',mtWarning,[mbok],0);
ExcelApp:=CreateOleObject('Excel.Application');
MyWorkBook:=CreateOleobject('Excel.Sheet');
except
application.Messagebox('无法打开Xls文件请确认已经安装EXCEL.','',mb_OK+mb_IconStop);
Exit;
end;
if OpenDialog1.Execute then
MyworkBook:= ExcelApp.workBooks.Open(OpenDialog1.FileName);
i:=2;
while trim(string(MyWorkBook.WorkSheets[1].Cells[i,1]))<>''do begin
adotable1.Append;
adotable1.FieldByName('Name').AsString := MyWorkBook.WorkSheets[1].Cells[i,1].Value;
adotable1.FieldByName('type').AsString := MyWorkBook.WorkSheets[1].Cells[i,2].Value;
adotable1.FieldByName('min').AsString := MyWorkBook.WorkSheets[1].Cells[i,3].Value;
adotable1.FieldByName('fee').AsString := MyWorkBook.WorkSheets[1].Cells[i,4].Value;
adotable1.Post;
inc(i);
end;
ExcelApp.WorkBooks.Close;
ExcelApp.quit;
ExcelApp:=Unassigned;
end;
end;
解决方案 »
- 分可加+在线等:用程序实现将网络文件下载到指定文件夹
- *******公司开表彰大会。开完会,销售人员个个都洋洋得意,好像在过年;开发人员个个都垂头丧气,好像死了娘。老板,说句心里话:我们对
- 关于 delphi6 自带的 quickreport 的 TQRDBText 控件 遇到中文时不能换行的问题??????????????????? 急!!!!
- 请教高手 怎么获取listview(vsReport样式)里选定的某一格 是第几行和第几列
- 关于DBChart的打印输出问题(在线等)?
- 关于指针参数,和指针返回值
- 关于PChar数据类型
- 替换问题?
- 如何改变DBGrid中单元格的字体颜色?
- 可以给我介绍一些关于CASE工具的东东吗?比如,流行的有那些?你们一般都使用他们吗?
- 救命啊!Xp下delphi5为什么不能调试dll么??
- 对象问题
execute master..xp_cmdshell 'bcp........',但EXCEL的格式存在一些问题,必须用BCP从SQL导出一EXCEL,然后以此EXCLE为模板作为源才可以导入。
导入:
CREATE procedure ExcelToSQL2
AS
begin
delete temp_SZ
exec master..xp_cmdshell 'bcp MarkDB.dbo.temp_SZ in \\192.168.0.3\partno\sbook.xls -c -q -S"ERPSERVER" -U"sa" -P"songerppass"'
end;
GO
导出:
CREATE procedure ExcelToSQL
@start char(8),
@end char(8)
as
begin
if exists(select * from sysobjects where name='temp1')
drop table temp1
select A.TG003 AS A01,A.TG001 AS A02, A.TG002 AS A03,A.TG020 AS A04,A.TG015 AS A05,B.TH004 AS A06,
B.TH009 AS A07,B.TH008 AS A08,B.TH012 AS A09 ,A.TG012 AS A10 into MarkDB.dbo.temp1 FROM MarkDB.dbo.COPTG A
, MarkDB.dbo.COPTH B WHERE A.TG001=B.TH001 AND A.TG002=B.TH002 and TG003>=@start and TG003<=@end and A.TG023='Y'
execute master..xp_cmdshell 'bcp MarkDB.dbo.temp1 out \\192.168.0.3\partno\sbook.xls -c -q -S"ERPSERVER" -U"sa" -P"songerppass"'
end
drop table temp1
GO
Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\temp\a.xls;Extended
Properties="Excel 8.0;HDR=Yes;";Persist Security Info=False
Extended Properties参数属性这样写 Excel 5.0;HDR=YES;IMEX=1
IMEX=1就是指混合型转换为文本
SQLServer从Excel取数
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')...sheet1$