将Excel数据导入access数据库,我的程序哪里出错?(20分)

O

okzjq

Unregistered / Unconfirmed
GUEST, unregistred user!
procedure TMTStudentForm.BitBtn4Click(Sender: TObject);
var
OpenDialog:TOpenDialog;
iCount:integer;
begin
inherited;
if MessageDlg('是否真的导入Excel数据?',mtConfirmation,[mbYes,mbNo],0)
=mrYes then
begin
openDialog:=TOpenDialog.Create(self);
try
openDialog.DefaultExt:='xls';
openDialog.Filter:='Microsof Excel 文件(*.xls)|*.xls';
openDialog.Execute;

if openDialog.FileName<>'' then
try
Screen.Cursor:=crSqlWait;
ExcelConn.Close;
ExcelConn.ConnectionString:='Provider=Microsoft.Jet.OLEDB.4.0;User ID=Admin;'
+'Data Source='+openDialog.FileName+';Mode=Read;Extended Properties=Excel 8.0;'
+'Jet OLEDB:System database="";Jet OLEDB:Registry Path="";Jet OLEDB:Database Password="";'
+'Jet OLEDB:Engine Type=35;Jet OLEDB:Database Locking Mode=0;Jet OLEDB:Global Partial Bulk Ops=2;'
+'Jet OLEDB:Global Bulk Transactions=1;Jet OLEDB:New Database Password="";';
Excelconn.Open();

With ExcelQuery do
begin
Close;
SQl.Clear;
sql.Add('select 学号,班级,姓名,性别,民族,籍贯,政治面貌,出生年月,');
sql.Add('入学年月,宿舍电话,家庭地址 from [sheet1$]');
Prepared:=true;
open
end;

ADOQuery.Last;
ADOQuery.DisableControls;
Excelconn.BeginTrans;
ExcelQuery.First;
while not ExcelQuery.Eof do
begin
with adoCommand do
begin
CommandText:='Insert into SXSJK values('
+''''+Trim(ExcelQuery.Fields[0].AsString)+''''+','
+''''+Trim(ExcelQuery.Fields[1].AsString)+''''+','
+''''+Trim(ExcelQuery.Fields[2].AsString)+''''+','
+''''+Trim(ExcelQuery.Fields[3].AsString)+''''+','
+''''+Trim(ExcelQuery.Fields[4].AsString)+''''+','
+''''+Trim(ExcelQuery.Fields[5].AsString)+''''+','
+''''+Trim(ExcelQuery.Fields[6].AsString)+''''+','
+''''+Trim(ExcelQuery.Fields[7].AsString)+''''+','
+''''+Trim(ExcelQuery.Fields[8].AsString)+''''+','
+''''+Trim(ExcelQuery.Fields[9].AsString)+''''+','
+''''+Trim(ExcelQuery.Fields[10].AsString)+''''+','
+''''+Trim(ExcelQuery.Fields[11].AsString)+''''+','
+''''+Trim(ExcelQuery.Fields[12].AsString)+''''+','
+''''+Trim(ExcelQuery.Fields[13].AsString)+''''+','
+''''+Trim(ExcelQuery.Fields[14].AsString)+''''+','
+''''+Trim(ExcelQuery.Fields[15].AsString)+''''+')';
Execute;

ExcelQuery.Next;
end;

ExcelConn.CommitTrans;
MessageDlg('数据导入成功!',mtInformation,[mbOK],0);
except
ExcelConn.RollbackTrans;
MessageDlg('数据导入失败!'+#13+'请检查Excel数据是否符合规格',mtInformation,[mbOK],0);
end;

finally
ADOQuery.EnableControls;
ExcelQuery.Close;
ExcelConn.Close;
OpenDialog.Free;


screen.Cursor:=crDefault;
end;
ADOQuery.Close;
ADOQuery.Open;
end;
上面程序运行时出错,提示:raised exception EOLECeption with message"
sheet1$不是一个有效的字符和标点",

可我在另一个单独的程序里,代码如下,却能正常运行,请问是怎么回事呢?
unit Unit1;

interface

uses
Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
Dialogs, StdCtrls, ExtCtrls, DBCtrls, Grids, DBGrids, DB, ADODB, ComCtrls;

type
TForm1 = class(TForm)
ADOQuery1: TADOQuery;
DataSource1: TDataSource;
ExcelConn: TADOConnection;
ExcelQuery: TADOQuery;
DBGrid1: TDBGrid;
DBNavigator1: TDBNavigator;
Button1: TButton;
StatusBar1: TStatusBar;
ADOConnection1: TADOConnection;
ADOCommand1: TADOCommand;
procedure Button1Click(Sender: TObject);
private
{ Private declarations }
public
{ Public declarations }
end;

var
Form1: TForm1;

implementation

uses Unit3;



{$R *.dfm}

procedure TForm1.Button1Click(Sender: TObject);
var
OpenDialog:TOpenDialog;
iCount:integer;
begin
if MessageDlg('是否真的导入Excel数据?',mtConfirmation,[mbYes,mbNo],0)
=mrYes then
try
openDialog:=TOpenDialog.Create(self);
openDialog.DefaultExt:='xls';
openDialog.Filter:='Microsof Excel 文件(*.xls)|*.xls';
openDialog.Execute;

if openDialog.FileName<>'' then
try
Screen.Cursor:=crSqlWait;
ExcelConn.Close;
ExcelConn.ConnectionString:='Provider=Microsoft.Jet.OLEDB.4.0;User ID=Admin;'
+'Data Source='+openDialog.FileName+';Mode=Read;Extended Properties=Excel 8.0;'
+'Jet OLEDB:System database="";Jet OLEDB:Registry Path="";Jet OLEDB:Database Password="";'
+'Jet OLEDB:Engine Type=35;Jet OLEDB:Database Locking Mode=0;Jet OLEDB:Global Partial Bulk Ops=2;'
+'Jet OLEDB:Global Bulk Transactions=1;Jet OLEDB:New Database Password="";';
Excelconn.Open();

//选择EXCEL字段


With ExcelQuery do
begin
Close;
SQl.Clear;
sql.Add('select number,name from [sheet1$]');
Prepared:=true;
Open;
end;

iCount:=ExcelQuery.RecordCount;
ADOQuery1.Last;
ADOQuery1.DisableControls;
Excelconn.BeginTrans;
ExcelQuery.First;
form3.ProgressBar1.Max:=iCount;
form3.ProgressBar1.Position:=0;
form3.Label1.Caption:='共有'+intToStr(iCount)+'条记录';
self.WindowState:=wsMinimized;

while not ExcelQuery.Eof do
begin
with adoCommand1 do
begin
//加判断条件
CommandText:='Insert into xsjk values('
+''''+Trim(ExcelQuery.FieldByName('Number').AsString)+''''+','
+''''+Trim(ExcelQuery.FieldByName('Name').AsString)+''''+')';
Execute;
end;
form3.ProgressBar1.Position:=form3.ProgressBar1.Position+1;
form3.Show;
form3.Label2.caption:='已拷贝'+IntToStr(form3.ProgressBar1.Position)+
'条记录';
application.ProcessMessages;

ExcelQuery.Next;
form1.StatusBar1.Refresh;
statusbar1.Panels[1].Text:='当前数:'+IntToStr(ExcelQuery.RecNo)
+'(共'+IntToStr(iCount)+'条)';
end;
if assigned(Form3) then
form3.Close;
self.WindowState:=wsNormal;

ExcelConn.CommitTrans;
MessageDlg('数据导入成功!',mtInformation,[mbOK],0);
except
ExcelConn.RollbackTrans;
MessageDlg('数据导入失败!',mtInformation,[mbOK],0);
end;

finally
ADOQuery1.EnableControls;
//ExcelQuery.EnableControls;
ExcelQuery.Close;
ExcelConn.Close;
OpenDialog.Free;
screen.Cursor:=crDefault;
StatusBar1.Panels[1].Text:='';
end;
ADOQuery1.Close;
ADOQuery1.Open;

end;

end.


 
两天了,为什么没人帮我啊?
 
我想知道,你这样图什么,用access直接就可以打开xcel呀!难道这不等于导入么?
 
是可以直接导入啊,但对于操作人员,它就不懂啊!
 
不用adoquery,用adotable就好用很多的那,我作过一个通用的excel导入到sql2000的都没有问题
 
to caffen:
你好,能不能提供一些代码给我看一下?
 
你用ADOTABLE不就完了
反正是个遍历嘛,直接把表名赋给tablename有什么问题吗?
 
顶部