把excel表格导入到access中,并显示到界面上 var bexcelvisible:boolean; filename,pathstr,setcell:string; i,j:integer; begin if Application.MessageBox('启动EXCEL界面吗?','请回答',MB_YESNO+MB_DEFBUTTON2)<>IDNO then bexcelvisible:=true else bexcelvisible:=false; filename:='订单车计划模板.xls'; try excelApplication1.Connect; excelapplication1.Visible[1]:=true; pathstr:=ExtractFileDir(Application.ExeName); pathstr:=pathstr+'\module\'+filename; Excelworkbook1.ConnectTo(excelapplication1.Workbooks.Open(pathstr,emptyparam,emptyparam,emptyparam,emptyparam,emptyparam,emptyparam,emptyparam,emptyparam,emptyparam,emptyparam,emptyparam,emptyparam,emptyparam,emptyparam,lcid(0))); except Messagedlg('没有安装EXCEL',mterror,[mbok],0); abort; end; begin for i:=0 to stringgrid1.RowCount do begin for j:=0 to stringgrid1.RowCount do begin setcell:=chr(65+i)+inttostr(j+2);
end; end; if bexcelvisible then excelApplication1.Visible[0]:=true else begin if savedialog1.Execute then begin try excelworksheet1.SaveAs(savedialog1.FileName); Application.MessageBox('success','gongxi',MB_OK); EXcept Application.MessageBox('failed','error',MB_OK); end; end; excelApplication1.Disconnect; excelApplication1.Quit; end; end; 上面的代码实现了打开excel表格,但没有显示到界面上,导入时,还要有进度条显示唉,其实我自己也不算很理解啊
var
bexcelvisible:boolean;
filename,pathstr,setcell:string;
i,j:integer;
begin
if Application.MessageBox('启动EXCEL界面吗?','请回答',MB_YESNO+MB_DEFBUTTON2)<>IDNO
then bexcelvisible:=true
else bexcelvisible:=false;
filename:='订单车计划模板.xls';
try
excelApplication1.Connect;
excelapplication1.Visible[1]:=true;
pathstr:=ExtractFileDir(Application.ExeName);
pathstr:=pathstr+'\module\'+filename;
Excelworkbook1.ConnectTo(excelapplication1.Workbooks.Open(pathstr,emptyparam,emptyparam,emptyparam,emptyparam,emptyparam,emptyparam,emptyparam,emptyparam,emptyparam,emptyparam,emptyparam,emptyparam,emptyparam,emptyparam,lcid(0)));
except
Messagedlg('没有安装EXCEL',mterror,[mbok],0);
abort;
end;
begin
for i:=0 to stringgrid1.RowCount do
begin
for j:=0 to stringgrid1.RowCount do
begin
setcell:=chr(65+i)+inttostr(j+2);
end;
end;
if bexcelvisible then
excelApplication1.Visible[0]:=true
else
begin
if savedialog1.Execute then
begin
try
excelworksheet1.SaveAs(savedialog1.FileName);
Application.MessageBox('success','gongxi',MB_OK);
EXcept
Application.MessageBox('failed','error',MB_OK);
end;
end;
excelApplication1.Disconnect;
excelApplication1.Quit;
end;
end;
上面的代码实现了打开excel表格,但没有显示到界面上,导入时,还要有进度条显示唉,其实我自己也不算很理解啊
var
MSExcel: Variant;
i: Integer;
begin
begin
OpenDialog1.Filter := '*.XLS|*.XLS';
OpenDialog1.DefaultExt := 'XLS';
if OpenDialog1.Execute then
begin
MSExcel := CreateOLEObject('Excel.Application');
MSExcel.WorkBooks.Open(OpenDialog1.FileName);
MSExcel.Visible := false;
DataSource1.DataSet := MemTableEh1;
// 从有数据的行逐行读入数据
try
ProgressBar1.Max := MSExcel.ActiveSheet.UsedRange.Rows.Count;
Label3.Caption := '正在读取记录 . . .';
Panel3.Visible := true;
Button1.Enabled := false;
Button2.Enabled := false;
MemTableEh1.EmptyTable;
MemTableEh1.Close;
MemTableEh1.Open;
// MemTableEh1.DisableControls; for i := 4 to MSExcel.ActiveSheet.UsedRange.Rows.Count - 1 do
begin
MemTableEh1.Append;
MemTableEh1.FieldByName('订购单号').AsString := MSExcel.Cells[i, 1].Value;
MemTableEh1.FieldByName('物料编码').AsString := MSExcel.Cells[i, 4].Value;
MemTableEh1.FieldByName('厂商').AsString := MSExcel.Cells[i, 3].Value;
MemTableEh1.FieldByName('品名规范').AsString := MSExcel.Cells[i, 5].Value;
MemTableEh1.FieldByName('预计交货日期').AsString := MSExcel.Cells[i,
7].Value;
MemTableEh1.FieldByName('订购单价').AsString := MSExcel.Cells[i, 8].Value;
MemTableEh1.FieldByName('订购数量').AsString := MSExcel.Cells[i, 9].Value;
MemTableEh1.FieldByName('累计验收数量').AsString := MSExcel.Cells[i,
10].Value;
MemTableEh1.FieldByName('验收金额').AsString := MSExcel.Cells[i,
12].Value;
MemTableEh1.FieldByName('请购单号').AsString := MSExcel.Cells[i,
13].Value;
MemTableEh1.FieldByName('核准日期').AsString := MSExcel.Cells[i,
14].Value;
ProgressBar1.Position := i;
Label3.Caption := '正在读取记录 . . .(' + IntToStr
(ProgressBar1.Position - 3) + '/' + IntToStr(ProgressBar1.Max - 4)
+ ')';
Application.ProcessMessages;
end;
MemTableEh1.Post;
MessageBox(Handle, '数据已全部读入,请上传。', '提示', MB_OK + MB_ICONINFORMATION); finally
Panel3.Visible := false;
Button1.Enabled := true;
Button2.Enabled := true;
MSExcel.ActiveWorkBook.Close;
MSExcel.Quit;
MSExcel := Unassigned;
// MemTableEh1.EnableControls;
// Rec:=Rec+MemTableEh1.RecordCount;
// ShowRec;
end;
end;
end;
end;
删除MSExcel := CreateOLEObject('Excel.Application');之后能够运行,但以打开表就弹出对话框,显示“invalid variant operation” 并指向MSExcel.WorkBooks.Open(OpenDialog1.FileName);这一行
procedure TForm1.BitBtn7Click(Sender: TObject);
var
MSExcel: Variant;
i: Integer;
begin
begin
OpenDialog1.Filter := '*.XLS|*.XLS';
OpenDialog1.DefaultExt := 'XLS';
if OpenDialog1.Execute then
begin MSExcel.WorkBook1.Open(OpenDialog1.FileName);
MSExcel.Visible := false;
DataSource2.DataSet := ADOTable2;
// 从有数据的行逐行读入数据
try
ProgressBar1.Max := MSExcel.ActiveSheet.UsedRange.Rows.Count;
Label9.Caption := '正在读取记录 . . .';
Panel1.Visible := true;
ADOTable2.Close;
ADOTable2.Open;
// ADOTable2.DisableControls; for i := 4 to MSExcel.ActiveSheet.UsedRange.Rows.Count - 1 do
begin
ADOTable2.Append;
ADOTable2.FieldByName('车型').AsString := MSExcel.Cells[i, 1].Value;
ADOTable2.FieldByName('整车物料号').AsString := MSExcel.Cells[i, 2].Value;
ADOTable2.FieldByName('数量').AsString := MSExcel.Cells[i, 3].Value;
ADOTable2.FieldByName('特殊要求').AsString := MSExcel.Cells[i, 4].Value;
ADOTable2.FieldByName('订货单位').AsString := MSExcel.Cells[i, 5].Value;
ADOTable2.FieldByName('批次').AsString := MSExcel.Cells[i, 6].Value;
ADOTable2.FieldByName('入库时间').AsString := MSExcel.Cells[i, 7].Value;
ADOTable2.FieldByName('备注').AsString := MSExcel.Cells[i,8].Value;
ProgressBar1.Position := i;
Label9.Caption := '正在读取记录 . . .(' + IntToStr
(ProgressBar1.Position - 3) + '/' + IntToStr(ProgressBar1.Max - 4)
+ ')';
Application.ProcessMessages;
end;
ADOTable2.Post;
MessageBox(Handle, '数据已全部读入,请上传。', '提示', MB_OK + MB_ICONINFORMATION); finally
Panel1.Visible := false; MSExcel.ActiveWorkBook.Close;
MSExcel.Quit;
MSExcel := Unassigned;
// MemTableEh1.EnableControls;
// Rec:=Rec+MemTableEh1.RecordCount;
// ShowRec;
end;
end;
end;
end;