将excel文件中的内容写入数据库的问题

我刚刚写了个可以将dbgrid的内容写出到excel文件
我现在想知道如何将excel 文件的内容写入到数据库？？？
谢谢

12 个解决方案

#1

给钱！

#2

都能写EXCEL了，还不能读
按定义好的顺序把内容一个个读出来，拼成SQL语句往数据库里插

#3

楼上的老兄能不能给段代码小弟看看，学学啊
很感谢！！！！

#4

mark
关键是如何访问单元格

#5

还请大虾们帮帮忙啊？

#6

我给你些代码把，前几天刚弄好的
procedure TSALESBILLFrm.ImportExcel(FFileName: string);
 var
  msexcel : olevariant;
  wbook, wsheet : olevariant;
 i,j : integer;
  temp : string;
  Save_Cursor:TCursor;
  cbatchno:string;
begin
  Save_Cursor := Screen.Cursor;
  Screen.Cursor := crHourGlass;    { Show hourglass cursor }
  // 2 倒入 TExcelBill
  MsExcel := CreateOleObject('Excel.Application');
  WBook := MsExcel.Application;
  WBook.Visible := False;
  wbook.workbooks.Open(FFileName);//打开Excel文档
  WSheet := WBook.ActiveSheet;
 try
  if not SALESBILLDM.ADOExceltObj.Active then SALESBILLDM.ADOExceltObj.open;
  for i := 2 to WSheet.Rows.count    do
    begin
      temp:='';
      for j := 1 to 13 do
      begin
        temp :=temp + VarToStr(wsheet.cells[i, j].value);
      end;
    //  Memo1.Lines.Add(temp);
      if  trim(temp) = '' then break else
     begin
       with SALESBILLDM.ADOExceltObj do
        begin
          Append;
          fieldbyname('aa').AsString:=VarToStr(wsheet.cells[i, 1].value);
          cbatchno:= VarToStr(wsheet.cells[i, 2].value);
          fieldbyname('bb').AsString:=VarToStr(wsheet.cells[i, 3].value);
          fieldbyname('cc').AsString:=VarToStr(wsheet.cells[i, 4].value);
          post;
       end;
     end;
  end;
 MsExcel.workbooks.close ;
 MsExcel.quit;
 // 批量处理转入正是表
 TransDataFromTExcelBill();
 // 纪录导入纪录
  Addimportlog(FFileName,cbatchno);
  Screen.Cursor := Save_Cursor;
  ShowInformation('执行完成！');
 except
  MsExcel.workbooks.close ;
  MsExcel.quit;
  Screen.Cursor := Save_Cursor;
  ShowInformation('执行失败，请重试！');
 end;
end;

#7

应该能看懂把,主要是excel文件和表的得字段，对准了

#8

把Excel当作数据库联上就行了

#9

不是很难的,论坛上一大堆关于操作Excel 的代码,先搜索一下再说吧2

#10

mark

#11

procedure TFrm_main.Button2Click(Sender: TObject);
var
  eclApp,WorkBook :Variant ;
  xlsFileName :string;
  a_FiledCount:integer;       //数据库表中的列数
  b_filedCount:integer;      //excel 文件中的 列数
  b_row :integer;           // excel 文件的行熟
  i,j :integer;
  a_flag :boolean;
  sucess_row:integer;
  sucess_rows:string;
  iss:string;
begin 
  ExcelApplication1.Connect;
  ExcelApplication1.Visible[0]:=false; //true
  ExcelApplication1.Workbooks.Add(null,0);
  Excelworkbook1.ConnectTo(ExcelApplication1.workbooks[ExcelApplication1.Workbooks.count]);
  //Excelworkbook1.ConnectTo(ExcelApplication1.workbooks[1] as Excelworkbook);
  Excelworksheet1.ConnectTo(ExcelWorkbook1.Sheets[1] as _worksheet);
  ExcelWorkSheet1.Cells.NumberFormat :='@';//在数据为字符串类型时加这句
  cursor:=crHourGlass;
  sucess_row:=0;
  OpenDialog1.Title :='Excel文件导入到数据库表';
  OpenDialog1.InitialDir :=ExtractFilePath(Application.ExeName );
  if OpenDialog1.Execute then
    begin
      xlsFileName :=ExtractFileName(OpenDialog1.FileName);
      edit1.text:=OpenDialog1.FileName;
    end
  else
    begin
      exit ;
    end;
  try
    eclApp := CreateOleObject('Excel.Application');
    WorkBook :=CreateOleObject('Excel.Sheet');
  except
    showmessage('您系统未安装MS-EXCEL');
    exit;
    TerminateOLE;//杀死excel进程
  end;
  try
    workBook :=eclApp.WorkBooks.add ;
    eclApp.workBooks.open(OpenDialog1.FileName );
  except
    on  EOleException do
      begin
        WorkBook.close;
        eclApp.quit;
        eclApp:=Unassigned;
        exit;
      end;
          end;
          eclApp.visible :=false;
          try   //try ..finally
            try //try  ..except
                 With  Data_Mod.table1    do
                   begin
                     close ;
                     active :=true;
                     a_FiledCount :=FieldCount;
                   end;
                   b_filedCount :=eclApp.ActiveSheet.UsedRange.columns.Count;//返回excel 表中的列数
                   b_row :=eclApp.activesheet.UsedRange.rows.count;        //返回excel 表中的行数
                   if (a_FiledCount <>b_FiledCount)  then   //当数据 表和导入的excel表中的列数不一样，说明导入的excel文件不是正确的
                     begin
                       showmessage('您选择导入的excel文件错误'+#13+#10+'请您重新选择');
                       WorkBook.close;
                       eclApp.quit;
                       eclApp:=Unassigned;
                       exit;
                     end
                   else
                     begin
                       for  i :=1 to  b_filedCount do
                         begin
                           // if    eclApp.activesheet.cells.item[2,i].value<>Data_Mod.table1.Fields[i-1].FieldName  //判断字段名是否相等
                           if    eclApp.activesheet.cells.item[1,i].value<>Data_Mod.table1.Fields.Fields[i-1].FieldName   then
                             begin
                               showmessage('1您选择导入的excel文件错误'+#13+#10+'请您重新选择');
                               WorkBook.close;
                               eclApp.quit;
                               eclApp:=Unassigned;
                               Data_Mod.table1.Close ;
                               exit;
                             end;
                         end; //for   i:=.....
                     end;     //end with  else
                  for  i :=2 to  b_row do            //行
                      begin
                            iss:=inttostr(i-1);
                            a_flag :=Data_Mod.table1.Locate(eclApp.activesheet.cells.item[1,1],eclApp.activesheet.cells.item[i,1],[loCaseInsensitive]);
                                      if    (a_flag =true) then
                                            begin
                                               showmessage('该记录已经存在');
                                               Data_Mod.table1.Next ;
                                               continue;
                                            end
                                        else
                                           sucess_row:=sucess_row+1; 
                            With  Data_Mod.table1  do
                               begin
                                     close ;
                                     //TableName :=true;
                                     active :=true;
                                     Append;
                               end;
                            For j :=1 to    b_filedCount do    //列
                               begin     //开始导入数据库
                                 Data_Mod.table1.FieldByName(eclApp.activesheet.cells.item[1,j]).Value :=eclApp.activesheet.cells[i,j].value;
                                 Excelworksheet1.Disconnect;
                                 Excelworkbook1.Disconnect;
                                 ExcelApplication1.Disconnect;
                               end;      //end with For j :=1 to    b_filedCount do
                             Data_Mod.table1.Post ;
                             //Data_Mod.table1.Refresh ;
                             label2.Caption:='正在导入第'+iss+'条数据,请等待...';
                             label2.Font.Color:=clred;
                             //Form1.Font.Color:=clred;
                             Caption:= '正在导入第'+iss+'条数据,请等待...'; 
                  end;
                 //Form1.Caption:= '';
                 sucess_rows:=inttostr(sucess_row);
                 label2.Caption:='成功导入'+sucess_rows+'条数据!';
                 label2.Font.Color:=clred;
                 Caption:= '成功导入'+sucess_rows+'条数据';
                 showmessage('成功导入'+sucess_rows+'条数据'); 
            except
              WorkBook.close;
              eclApp.quit;
              eclApp:=Unassigned;
              Data_Mod.table1.Close ;
            end;   //end  try  except
          finally  //操作错误，退出
            WorkBook.close;
            eclApp.quit;
            eclApp:=Unassigned;
            Data_Mod.table1.Close ;
            TerminateOLE;//杀死excel进程
          end;
end;

#12

希望对你有用！