begin try ExcelApp := CreateOleObject('Excel.Application'); WorkBook := ExcelApp.WorkBooks.Open(opendialog.FileName);//使用opendialog对话框指定 //excel档路径 ExcelApp.Visible := false; ExcelRowCount := WorkBook.WorkSheets[1].UsedRange.Rows.Count; for i := 1 to excelrowcount + 1 do begin
excelx := excelapp.Cells[i,1].Value; excely := excelapp.Cells[i,2].Value; if ((excelapp.Cells[i,1].Value = '') and (ExcelApp.Cells[i,2].Value = '')) then //指定excel档的第 i 行 ,第 1,2(看情况而定)行如果为空就退出,这样的设定,最好是你的档案力这两行//对应数据库中不能为空的数据 exit else with query1 do begin close; sql.clear; sql.add(insert into test(name,address) values(:name,:address)); parambyname('name').asstring := excelx;//excel档的第一列插入到test表的 name栏位; parambyname('address').asstring := excely;//excel档的第二列插入到test表的 address 栏位; execsql;
type Tlenordtefr = class(TForm) dxBarManager1: TdxBarManager; dxBarManager1Bar1: TdxBar; dxBarLargeButton1: TdxBarLargeButton; dxBarLargeButton2: TdxBarLargeButton; dxBarLargeButton3: TdxBarLargeButton; StatusBar1: TStatusBar; ADOConnection1: TADOConnection; ADOQuery1: TADOQuery; ADOQuery2: TADOQuery; DataSource1: TDataSource; DataSource2: TDataSource; ADOQuery3: TADOQuery; OpenDialog1: TOpenDialog; DBGridEh1: TDBGridEh; procedure dxBarLargeButton1Click(Sender: TObject); procedure dxBarLargeButton2Click(Sender: TObject); procedure dxBarLargeButton3Click(Sender: TObject); private { Private declarations } a,b:Integer; public { Public declarations } connstr:Cardinal; end;var lenordtefr: Tlenordtefr;implementation uses Syslike,syshome,Activex; const conntime=1; {$R *.dfm}procedure Tlenordtefr.dxBarLargeButton1Click(Sender: TObject); var sFilename,NSql,CSQL:string; i:integer; sl:string; begin inherited; if OpenDialog1.Execute then begin sFilename:= Trim(OpenDialog1.FileName) ; ADOConnection1.Connected := false; ADOConnection1.ConnectionString := 'Provider=Microsoft.Jet.OLEDB.4.0;Data Source='+ sFilename+ ';Extended Properties=Excel 8.0;Persist Security Info=False';//"Excel 8.0;IMEX=1" ADOConnection1.Connected := True; a := GetTickCount; ADOQuery1.Close; ADOQuery1.SQL.Clear; ADOQuery1.SQL.Add('Select * from [Sheet1$]'); ADOQuery1.Open; b := GetTickCount; DBGridEh1.DataSource:=DataSource1; for i:=0 to DBGridEh1.Columns.Count-1 do DBGridEh1.Columns.Items[i].Width := 100;//设置列宽 //ShowMessage(IntToStr(a)); //ShowMessage(IntToStr(b)); //ShowMessage(inttostr(ADOQuery1.RecordCount)); //StatusBar1.Panels[0].Text:=('读取EXCEL表数据成功,读取共:'+inttostr(ADOQuery1.RecordCount)+' 条,耗时:'+floattostr((b-a)/1000)+'秒'); sl:='读取EXCEL表数据成功,读取共:'+inttostr(ADOQuery1.RecordCount)+' 条,耗时:'+floattostr((b-a)/1000)+'秒'; Application.MessageBox(PWidechar(sl),'提示',32); // ShowMessage('读取EXCEL表数据成功,读取共:'+inttostr(ADOQuery1.RecordCount)+' 条,耗时:'+floattostr((b-a)/1000)+'秒'); end end;procedure Tlenordtefr.dxBarLargeButton2Click(Sender: TObject); var i : integer; sl:string; begin if messagebox(Self.Handle,'你确定要上传表格的数据至服务器吗?','系统提示',mb_yesno+mb_iconquestion)=idyes then with ADOQuery2 do begin Close; sql.Clear; SQL.Add('select MAX(发货仓库) as YDNO from lenordte'); //lenordte判断这个表内是否有数据 Open; if FieldByName('YDNO').AsString<>'' then begin MessageBox(Self.Handle,'RDC扫描数据临时导入表还有未完成的数据,请稍后在提交数据或取消导入!','系统提示',mb_iconquestion); end else begin adoquery2.close; adoquery2.sql.text := 'select * from lenordte where 1=2'; //lenordte 为数据库表名 adoquery2.open; if inttostr(AdoQuery1.Fields.Count)=inttostr(AdoQuery2.Fields.Count) then begin AdoQuery1.FindFirst; if not AdoQuery1.isEmpty then begin a := GetTickCount; with AdoQuery1 do while not eof do begin AdoQuery2.Append; for I := 0 to AdoQuery1.fieldCount -1 do begin AdoQuery2.fields[i].value := Fields[i].value; end; Next; ADOQuery2.Post; end; b := GetTickCount; //StatusBar1.Panels[0].Text:=('数据上传成功,共上传:'+inttostr(ADOQuery1.RecordCount)+' 条,耗时:'+floattostr((b-a)/1000)+'秒'); sl:='数据上传成功,共上传:'+inttostr(ADOQuery1.RecordCount)+' 条,耗时:'+floattostr((b-a)/1000)+'秒'; Application.MessageBox(PWidechar(sl),'提示',32); //ShowMessage('数据上传成功,共上传:'+inttostr(ADOQuery1.RecordCount)+' 条,耗时:'+floattostr((b-a)/1000)+'秒'); //StatusBar1.Panels[0].Text:=('导入数据量:'+inttostr(AdoQuery1.RecordCount)); end; with ADOQuery3 do //处理代码 begin close; sql.Clear; sql.Add('exec P_lenord'); ExecSQL; end; with ADOQuery1 do begin close; end; end else begin Application.MessageBox('Excel表字段与服务器表字段不一至,请核对后重新提交数据','系统提示', MB_OK + MB_ICONWARNING); end; end; end; end; 这个是我目前用的,有更好的希望分享一下,谢谢!
begin try ExcelApp := CreateOleObject('Excel.Application'); WorkBook := ExcelApp.WorkBooks.Open(opendialog.FileName);//使用opendialog对话框指定
//excel档路径 ExcelApp.Visible := false; ExcelRowCount := WorkBook.WorkSheets[1].UsedRange.Rows.Count; for i := 1 to excelrowcount + 1 do begin
excelx := excelapp.Cells[i,1].Value; excely := excelapp.Cells[i,2].Value; if ((excelapp.Cells[i,1].Value = '') and (ExcelApp.Cells[i,2].Value = '')) then
//指定excel档的第 i 行 ,第 1,2(看情况而定)行如果为空就退出,这样的设定,最好是你的档案力这两行//对应数据库中不能为空的数据 exit else with query1 do begin close;
sql.clear;
sql.add(insert into test(name,address) values(:name,:address));
parambyname('name').asstring := excelx;//excel档的第一列插入到test表的 name栏位;
parambyname('address').asstring := excely;//excel档的第二列插入到test表的 address 栏位;
execsql;
end;
end; finally WorkBook.Close; ExcelApp.Quit; ExcelApp := Unassigned; WorkBook := Unassigned; end; end;
excelapp,workbook:variant;
特别注意:Extended Properties="Excel 8.0;HDR=YES;IMEX=1";或Extended Properties="Excel 5.0;HDR=YES;IMEX=1";需要在配置时在【所有】面板进行修改。TADOTable中的tablename是xls文件中的sheet,连接后可以下拉选择。
另外和普通Delphi程序一样。配置TDataSetProvider、tdatasource和dbgrid这类组件。数据可以直接导入到tclientdataset表中,并在数据显示控件中显示和编辑了。
配置了tadoquery,不知道怎么回事总是提示from的表找不到和错误。
Tlenordtefr = class(TForm)
dxBarManager1: TdxBarManager;
dxBarManager1Bar1: TdxBar;
dxBarLargeButton1: TdxBarLargeButton;
dxBarLargeButton2: TdxBarLargeButton;
dxBarLargeButton3: TdxBarLargeButton;
StatusBar1: TStatusBar;
ADOConnection1: TADOConnection;
ADOQuery1: TADOQuery;
ADOQuery2: TADOQuery;
DataSource1: TDataSource;
DataSource2: TDataSource;
ADOQuery3: TADOQuery;
OpenDialog1: TOpenDialog;
DBGridEh1: TDBGridEh;
procedure dxBarLargeButton1Click(Sender: TObject);
procedure dxBarLargeButton2Click(Sender: TObject);
procedure dxBarLargeButton3Click(Sender: TObject);
private
{ Private declarations }
a,b:Integer;
public
{ Public declarations }
connstr:Cardinal;
end;var
lenordtefr: Tlenordtefr;implementation
uses Syslike,syshome,Activex;
const conntime=1;
{$R *.dfm}procedure Tlenordtefr.dxBarLargeButton1Click(Sender: TObject);
var
sFilename,NSql,CSQL:string;
i:integer;
sl:string;
begin
inherited;
if OpenDialog1.Execute then
begin
sFilename:= Trim(OpenDialog1.FileName) ;
ADOConnection1.Connected := false;
ADOConnection1.ConnectionString := 'Provider=Microsoft.Jet.OLEDB.4.0;Data Source='+
sFilename+
';Extended Properties=Excel 8.0;Persist Security Info=False';//"Excel 8.0;IMEX=1"
ADOConnection1.Connected := True;
a := GetTickCount;
ADOQuery1.Close;
ADOQuery1.SQL.Clear;
ADOQuery1.SQL.Add('Select * from [Sheet1$]');
ADOQuery1.Open;
b := GetTickCount;
DBGridEh1.DataSource:=DataSource1;
for i:=0 to DBGridEh1.Columns.Count-1 do
DBGridEh1.Columns.Items[i].Width := 100;//设置列宽
//ShowMessage(IntToStr(a));
//ShowMessage(IntToStr(b));
//ShowMessage(inttostr(ADOQuery1.RecordCount));
//StatusBar1.Panels[0].Text:=('读取EXCEL表数据成功,读取共:'+inttostr(ADOQuery1.RecordCount)+' 条,耗时:'+floattostr((b-a)/1000)+'秒');
sl:='读取EXCEL表数据成功,读取共:'+inttostr(ADOQuery1.RecordCount)+' 条,耗时:'+floattostr((b-a)/1000)+'秒';
Application.MessageBox(PWidechar(sl),'提示',32);
// ShowMessage('读取EXCEL表数据成功,读取共:'+inttostr(ADOQuery1.RecordCount)+' 条,耗时:'+floattostr((b-a)/1000)+'秒');
end
end;procedure Tlenordtefr.dxBarLargeButton2Click(Sender: TObject);
var
i : integer;
sl:string;
begin
if messagebox(Self.Handle,'你确定要上传表格的数据至服务器吗?','系统提示',mb_yesno+mb_iconquestion)=idyes then
with ADOQuery2 do
begin
Close;
sql.Clear;
SQL.Add('select MAX(发货仓库) as YDNO from lenordte'); //lenordte判断这个表内是否有数据
Open;
if FieldByName('YDNO').AsString<>'' then
begin
MessageBox(Self.Handle,'RDC扫描数据临时导入表还有未完成的数据,请稍后在提交数据或取消导入!','系统提示',mb_iconquestion);
end
else
begin adoquery2.close;
adoquery2.sql.text := 'select * from lenordte where 1=2'; //lenordte 为数据库表名
adoquery2.open; if inttostr(AdoQuery1.Fields.Count)=inttostr(AdoQuery2.Fields.Count) then
begin AdoQuery1.FindFirst;
if not AdoQuery1.isEmpty then
begin
a := GetTickCount;
with AdoQuery1 do while not eof do
begin AdoQuery2.Append;
for I := 0 to AdoQuery1.fieldCount -1 do
begin
AdoQuery2.fields[i].value := Fields[i].value;
end;
Next;
ADOQuery2.Post; end;
b := GetTickCount;
//StatusBar1.Panels[0].Text:=('数据上传成功,共上传:'+inttostr(ADOQuery1.RecordCount)+' 条,耗时:'+floattostr((b-a)/1000)+'秒');
sl:='数据上传成功,共上传:'+inttostr(ADOQuery1.RecordCount)+' 条,耗时:'+floattostr((b-a)/1000)+'秒';
Application.MessageBox(PWidechar(sl),'提示',32);
//ShowMessage('数据上传成功,共上传:'+inttostr(ADOQuery1.RecordCount)+' 条,耗时:'+floattostr((b-a)/1000)+'秒');
//StatusBar1.Panels[0].Text:=('导入数据量:'+inttostr(AdoQuery1.RecordCount));
end;
with ADOQuery3 do //处理代码
begin
close;
sql.Clear;
sql.Add('exec P_lenord');
ExecSQL;
end;
with ADOQuery1 do
begin
close;
end;
end
else
begin
Application.MessageBox('Excel表字段与服务器表字段不一至,请核对后重新提交数据','系统提示', MB_OK + MB_ICONWARNING);
end;
end;
end;
end;
这个是我目前用的,有更好的希望分享一下,谢谢!
请问这个是sql.Add('exec P_lenord');???? P_lenord是存储过程?
上传到数据库也有问题。。求教
把你的源码上传到csdn或地址地方,我给你修改一下