procedure TForm1.Button3Click(Sender: TObject); var rownum,rolnum,RowBegin,RowEnd,RolBegin,RolEnd,DateBaseRol:integer; ExcelApp,MyWorkBook: OLEVariant; begin RowBegin:=strtoint(edit3.Text); RowEnd:=strtoint(edit4.Text); RolBegin:=strtoint(edit5.Text); RolEnd:=strtoint(edit6.Text); ProgressBar1.Position:=0; begin if opendialog1.Execute then begin try ExcelApp:=CreateOleObject('Excel.Application'); MyWorkBook:=CreateOleobject('Excel.Sheet'); except application.Messagebox('无法打开Xls文件,请确认已经安装EXCEL.','', mb_OK+mb_IconStop); Exit; end; try MyworkBook:= ExcelApp.workBooks.Open(opendialog1.FileName); //打开文件后,对文件进行操作 ProgressBar1.Max:=(RowEnd-RowBegin+1)*(RolEnd-RolBegin+1); try for rownum:=RowBegin to RowEnd do begin adoquery1.Insert; DateBaseRol:=strtoint(TeThemeEdit1.Text); for rolnum:=RolBegin to RolEnd do begin ProgressBar1.Position:=ProgressBar1.Position+1; adoquery1.Fields[DateBaseRol-1].Value:=MyWorkBook.WorkSheets[strtoint(TeThemeEdit3.Text)].Cells[rownum,rolnum].Value; inc(DateBaseRol); end; end; adoquery1.Post; except TeThemeMessage1.MessageDlg('导入数据失败,请核查数据库与Excel表是否正确',mterror,[mbok],0); ProgressBar1.Position:=0; ExcelApp.WorkBooks.close; ExcelApp.Quit; ExcelApp:=Unassigned; end;//try..except finally ProgressBar1.Position:=0; ExcelApp.WorkBooks.close; ExcelApp.Quit; ExcelApp:=Unassigned; end; //try..finally end; end; end;
如果是可作为一个数据库来处理
你在连接到Excel之后,就可以实现DTS
或者直接使用SQL的DTS功能。
sheetname:='sheet1';
ADOConnection1:= TADOConnection.Create(nil);
ADOConnection1.LoginPrompt := False;
ADOConnection1.ConnectionString := 'Provider=Microsoft.Jet.OLEDB.4.0; Data Source=' + FileName + ';Extended Properties=Excel 8.0';
ADOQuery1 := TADOQuery.Create(nil);
ADOQuery1 .Connection := ADOConnection2;
ADOQuery1 .SQL.Text := 'Select * from [' + SheetName + '$]';
ADOQuery1 .Open;
var
rownum,rolnum,RowBegin,RowEnd,RolBegin,RolEnd,DateBaseRol:integer;
ExcelApp,MyWorkBook: OLEVariant;
begin
RowBegin:=strtoint(edit3.Text);
RowEnd:=strtoint(edit4.Text);
RolBegin:=strtoint(edit5.Text);
RolEnd:=strtoint(edit6.Text);
ProgressBar1.Position:=0;
begin
if opendialog1.Execute then
begin
try
ExcelApp:=CreateOleObject('Excel.Application');
MyWorkBook:=CreateOleobject('Excel.Sheet');
except
application.Messagebox('无法打开Xls文件,请确认已经安装EXCEL.','',
mb_OK+mb_IconStop);
Exit;
end;
try
MyworkBook:= ExcelApp.workBooks.Open(opendialog1.FileName);
//打开文件后,对文件进行操作
ProgressBar1.Max:=(RowEnd-RowBegin+1)*(RolEnd-RolBegin+1);
try
for rownum:=RowBegin to RowEnd do
begin
adoquery1.Insert;
DateBaseRol:=strtoint(TeThemeEdit1.Text);
for rolnum:=RolBegin to RolEnd do
begin
ProgressBar1.Position:=ProgressBar1.Position+1;
adoquery1.Fields[DateBaseRol-1].Value:=MyWorkBook.WorkSheets[strtoint(TeThemeEdit3.Text)].Cells[rownum,rolnum].Value;
inc(DateBaseRol);
end;
end;
adoquery1.Post;
except
TeThemeMessage1.MessageDlg('导入数据失败,请核查数据库与Excel表是否正确',mterror,[mbok],0);
ProgressBar1.Position:=0;
ExcelApp.WorkBooks.close;
ExcelApp.Quit;
ExcelApp:=Unassigned;
end;//try..except
finally
ProgressBar1.Position:=0;
ExcelApp.WorkBooks.close;
ExcelApp.Quit;
ExcelApp:=Unassigned;
end; //try..finally
end;
end;
end;