如何将Excel数据导入Access数据库? 给一段代码

Question

如何将Excel数据导入Access数据库?, 
表名:crk

Answer 1

excel可以直接作为数据库操作
使用ADO然后搞进去

Answer 2

procedure TExInput.Excel1Click(Sender: TObject); //实现Excel导入SQL
var
  sTableName, ErrInfo: string;
var
TableName,CBMonth ,sqlStr:String;
begin
 TableName :='BCTZ'+ComboBoxYear.Text+ComboBoxMonth.Text;
  sqlStr:='create table BCTZ'+ComboBoxYear.Text+ComboBoxMonth.Text+'( BmNo char(20),RsNo char(20),TzRq char(20), BcNo char(20), Name char(20), BmMc char(20))';
 ADOQuery1.sql.clear;
 ADOQuery1.sql.add(sqlStr);
 ADOQuery1.execsql;
  try
    ADOConn.Connected := False;
    ADOConn.ConnectionString := Format(context, [Trim(edtPasswd.Text), Trim(edtUser.Text), Trim(edtServerName.Text), Trim(edtServerIP.Text)]);
    ADOConn.Connected := True;
  except
    Application.MessageBox('数据库连接出错！  ', '提示信息', MB_OK or MB_ICONERROR);
    Abort;
  end;  OpenDialog1.DefaultExt := 'xls';
  OpenDialog1.Filter := 'Excel File|*.xls';
  OpenDialog1.InitialDir := ExtractFilePath(Application.ExeName);
  if OpenDialog1.Execute then
  begin
    ADOCommand1.ConnectionString := Format(SExcelText, [OpenDialog1.FileName]);
    try
      sTableName :='BCTZ'+ComboBoxYear.Text+ComboBoxMonth.Text;// Trim(edtTableName.Text);      ExcuteSQL(adoQuery, 'delete from ' + sTableName);
      ADOCommand1.CommandText := 'insert into ' + sTableName + ' IN [ODBC]'
        + ' [ODBC; Driver=SQL Server; UID=' + ADOConn.Properties['User ID'].Value + '; PWD=' + ADOConn.Properties['Password'].Value
        + '; Server=' + ADOConn.Properties['Data Source'].Value + '; DataBase='
        + ADOConn.Properties['Initial Catalog'].Value + ';] select * from [Sheet1$]';
Source="C:\temp\Temp.xls";Extended Properties="Excel 5.0;HDR=Yes;";Persist Security Info=False'')...sheet1$'
      ADOCommand1.Execute;
      ADOCommand1.ConnectionString := '';
    except
      ErrInfo := '导入数据时出错！'; //导入数据时出错！
      Application.MessageBox('导入数据时出错！', '提示信息', MB_OK or MB_ICONERROR);
      Exit;
    end;
    if ErrInfo = '' then
    begin
      Application.MessageBox('导入数据已完成！', '提示信息', MB_OK or MB_ICONINFORMATION); //导入数据已完成！
    end;
  end;
end;
连接的SQL，将以上链接改成access试验一下

Answer 3

从自己的代码中拿一段出来,作参考喔.
procedure TForm1.Button3Click(Sender: TObject);
var filename,path:string;
    Excelapp:variant;
    tstr,ssqname,num1,num2,num3,num4,num5,num6,num7:String;
    ado1:TADOQuery;
    pc:string;
    SysTime: TsystemTime;
begin
path:=ExtractFilePath(application.ExeName);
fileName:='';
opendialog1.FileName:='';
opendialog1.Execute;
if opendialog1.FileName='' then Exit;
Excelapp:=CreateOleObject('Excel.Application');
filename:=opendialog1.FileName;
Dbgrid1.Cursor:=crHourGlass;
Excelapp.workBooks.Open(FileName);
num1:='0';num2:='';num3:='';num4:='';num5:='';num6:='';num7:='';ssqname:='0';
ado1:=TADOQuery.Create(self);
ado1.Connection:=ADOQuery1.Connection;//写入批次表,方便删除
GetSystemTime(SysTime);
pc:=IntToStr(SysTime.wYear)+IntToStr(SysTime.wMonth)+IntToStr(SysTime.wDay)+IntToStr(SysTime.wHour)+IntToStr(SysTime.wMinute)+IntToStr(SysTime.wSecond);
adoConnection1.Execute('INSERT INTO tbpc(fd_pc_name)values('+pc+')');
adoConnection1.Execute('delete from tb6_tmp');
adoConnection1.Execute('INSERT INTO  tb6_tmp(fd_tssq_name,fd_tssq_num1,fd_tssq_num2,fd_tssq_num3,fd_tssq_num4,fd_tssq_num5,fd_tssq_num6,fd_tssq_num7)' +   'SELECT 期号,红球1,红球2,红球3, 红球4,红球5,红球6,蓝球' +
        ' FROM [excel 8.0;database=' + fileName+ '].[Sheet1$] where 期号<>NULL');
adoConnection1.Execute('update tb6_tmp set fd_tssq_pc='+pc);
adoConnection1.Execute('INSERT INTO  tb6(fd_ssq_name,fd_ssq_num1,fd_ssq_num2,fd_ssq_num3,fd_ssq_num4,fd_ssq_num5,fd_ssq_num6,fd_ssq_num7,fd_ssq_pc)' +   'SELECT fd_tssq_name,fd_tssq_num1,fd_tssq_num2,fd_tssq_num3,fd_tssq_num4,fd_tssq_num5,fd_tssq_num6,fd_tssq_num7,fd_tssq_pc from tb6_tmp' );  with  ado1 do
      begin
      close();
      sql.Clear;
      sql.Text:='select * from tb6_tmp';
      open();      first;      while(NOT ado1.Eof) do
      begin      ssqname:=ado1.Fieldbyname('fd_tssq_name').AsString;
      num1:=ado1.Fieldbyname('fd_tssq_num1').AsString;
      num2:=ado1.Fieldbyname('fd_tssq_num2').AsString;
      num3:=ado1.Fieldbyname('fd_tssq_num3').AsString;
      num4:=ado1.Fieldbyname('fd_tssq_num4').AsString;
      num5:=ado1.Fieldbyname('fd_tssq_num5').AsString;
      num6:=ado1.Fieldbyname('fd_tssq_num6').AsString;
      num7:=ado1.Fieldbyname('fd_tssq_num7').AsString;
      tstr:='';
      tstr:='fd_bssq_num'+num1+',fd_bssq_num'+num2+',fd_bssq_num'+num3+',fd_bssq_num'+num4+',fd_bssq_num'+num5;
      tstr:=tstr+',fd_bssq_num'+num6+',fd_bssq_bn'+num7+',fd_bssq_name,fd_bssq_pc' ;
      if(num1<>'') then
      begin
      try
      adoConnection1.Execute('INSERT INTO  tb32('+tstr+')values(1,1,1,1,1,1,1,'+ssqname+','+pc+')');
      except
      end;
      end;
      next();
      end;      end ;
ado1.Free;
Excelapp.workBooks.close;
Excelapp.Quit;
Excelapp:=unassigned;
readrec100();
EndProcess('EXCEL.exe');
Dbgrid1.Cursor:=crDefault;
messagebox(application.Handle,pchar('导入完成!批次号:'+pc+'。'),pchar('提示'),MB_OK+MB_ICONINFORMATION);
end;

Answer 4

放一个button控件
代码如下
procedure TForm1.RzBitBtn1Click(Sender: TObject);
var
 i:integer;
 excela:string;
 excelb:string;
 excelc:string;
 exceld:string;
 ExcelApp,workbook,ExcelRowCount:oleVariant;
begin
    if  RzOpenDialog1.Execute then
    begin
      try
        ExcelApp:=CreateOleObject('Excel.Application');
        WorkBook := ExcelApp.WorkBooks.Open(RzOpenDialog1.FileName);//使用opendialog对话框指定
        ExcelApp.Visible := false;
        ExcelRowCount := WorkBook.WorkSheets[1].UsedRange.Rows.Count;
        ADOQuery1.Open;
        for i := 2 to excelrowcount + 1 do
          begin
            excela:=ExcelApp.cells[i,1].value;
            excelb:=ExcelApp.cells[i,2].value;
            excelc:=ExcelApp.cells[i,3].value;
            exceld:=ExcelApp.cells[i,4].value;
            ADOQuery1.Append;
            ADOQuery1.FieldByName('FullName').asstring:=excela;
            ADOQuery1.FieldByName('Date').asstring := excelb;
            ADOQuery1.FieldByName('ComeTime').asstring := excelc;
            ADOQuery1.FieldByName('GoTime').AsString:= exceld;
            ADOQuery1.post;
            ADOQuery1.UpdateBatch;
            end;
            showmessage('导入数据成功!')
       finally
         WorkBook.Close;
         ExcelApp.Quit;
         ExcelApp := Unassigned;
         WorkBook := Unassigned;
       end;
    end;
end;