delphi导入导出excel|oracle

1.导出用DBGrid直接到剪切板，然后粘贴到excle就ok了，有什么Excle的格式之类的可能要设置一下：

{
      1. StrTableName要查询的表名
      2。StrFieldsName查询表的字段名
      3。StrWhere查询条件
      4。StrOrder 查询排序次序
    }

procedure TFrmexportData.exportData(StrTableName:String;StrFieldsName:String='*';StrWhere:String='';StrOrder:String='');
var
  str:String;
  i:integer;
  excelapp,sheet:Variant;
  Col,Row:Integer;
   ADOConn: TADOConnection;
   ADOQueryExp: TADOQuery;
   DataSourceExp: TDataSource;
   DBGrid1: TsuiDBGrid;
begin
  try
  ADOConn:=TADOConnection.Create(nil);
  ADOQueryExp:=TADOQuery.Create(nil);
  DataSourceExp:= TDataSource.Create(nil);
  DBGrid1:= TsuiDBGrid.Create(nil);
  ADOConn.ConnectionString:='Provider=MSDAORA.1;Password=cdch0909;User ID=sa;Data Source=oracemcdch';
  ADOConn.LoginPrompt:=false;
  ADOConn.Connected:=true;
  ADOQueryExp.Connection:=ADOConn;
  DataSourceExp.DataSet:=ADOQueryExp;
  DBGrid1.DataSource:= DataSourceExp;
  with  ADOQueryExp do
  begin
     Close;
     SQL.Clear;
     SQL.Add('select '+StrFieldsName+' from '+StrTableName+' '+StrWhere+' '+StrOrder);
     Open;
     Row:=RecordCount; //记录条数
      if Row<=1 then
   begin
    showmessage('表中没有数据，不能导出');
    exit;
   end;
  end;
  //
  str:='';
  with DBGrid1 do
  begin
   DataSource.DataSet.DisableControls;
   Col:=DataSource.DataSet.FieldCount;
   for i:=0 to DataSource.DataSet.FieldCount-1 do
    str:=str+DataSource.DataSet.Fields[i].DisplayLabel+char(9);
   Str:=Str+#13;
   DataSource.DataSet.First;
   while not DataSource.DataSet.Eof do
   begin
    for i:=0 to DataSource.DataSet.FieldCount-1 do
      Str:=Str+DataSource.DataSet.Fields[i].AsString+char(9); //char(9)=tab格
    Str:=Str+#13; //回车  #9为tab，#13为回车，#10为换行
    DataSource.DataSet.Next;
    Application.ProcessMessages;
   end;  //end while
   DataSource.DataSet.EnableControls;
  end;
  ClipBoard.Clear;
  ClipBoard.Open;
  ClipBoard.AsText:=str;
  ClipBoard.Close;
  excelapp:=CreateOleObject('excel.application');
  excelapp.workbooks.add();
  excelapp.Caption:= TableName+'表数据导出';
  sheet:=excelapp.workbooks[1].worksheets[1];
  Sheet.name:=TableName+'表数据导出';

  for i:=1 to Col do
  begin
  sheet.Columns[i].numberformatlocal:='@';//excel的格式为文本
  end;
//   excelapp.activeSheet.Columns.ColumnsWith:=30;
     sheet.StandardWidth:=30;
  ShowMessage('共导出'+IntToStr(Row)+'条数据');
  sheet.paste;
  ClipBoard.Clear;
  excelapp.visible:=true;
  except
  on E:exception do
   begin
    showmessage(E.Message);
    //excelapp.Free;
   end;
  end;
  //ADOQueryExp.Close;
  //DBGrid1.Free;
  //DataSourceExp.Free;
  //ADOConn.Free;
end;

{====================================================}

2.导入可能就会有一点的慢了：

procedure TFrmexportData.ImportExcelToDB(ExceFileName:String);
var
  excelx,excely,excela,excelb,excelc,exceld:String; //字段名
  excele,excelf,excelg,excelh,exceli,excelj:String; //字段名
  ExcelApp,WorkBook:variant;
  sFileName:String;  //excel文件名
  i,ExcelRowCount,Row:Integer;
  ADOQuerySelectInsert:TADOQuery;
  ADOQueryUpdate:TADOQuery;
  ADOConnect:TADOConnection;
  FailNum:Integer;//导入失败的数据量
begin
  try
    Row:=0;
    FailNum:=0;
    //对象
    ADOQuerySelectInsert:=TADOQuery.Create(nil);
    ADOQueryUpdate:=TADOQuery.Create(nil);
    ADOConnect:=TADOConnection.Create(nil);
    ADOConnect.ConnectionString:='Provider=MSDAORA.1;Password=cdch0909;User ID=sa;Data Source=oracemcdch;Persist Security Info=True';
    ADOConnect.LoginPrompt:=false;
    ADOConnect.Connected:=true;
    ADOQuerySelectInsert.Connection:=ADOConnect;
    ADOQueryUpdate.Connection:= ADOConnect;

    sFileName:=ExceFileName;

    ExcelApp := CreateOleObject('Excel.Application');
    WorkBook:=ExcelApp.WorkBooks.Open(sFileName);
    ExcelApp.visible:=false;
    ExcelRowCount := WorkBook.WorkSheets[1].UsedRange.Rows.Count;
    //进度条
    ProgressBarImport.Position:=0;
   // ProgressBarImport.Step:=1;
    ProgressBarImport.Max:=ExcelRowCount;
    ProgressBarImport.Min:=0;
    //向oracle表写入数据
    for i:=1 to ExcelRowCount+1 do
    begin
     ProgressBarImport.StepIt; //进度条
     
     excelx:=Excelapp.cells[i,1].value;
     excely:=Excelapp.cells[i,2].value;
     excela:=Excelapp.cells[i,3].value;
     excelb:=Excelapp.cells[i,4].value;
     excelc:=Excelapp.cells[i,5].value;
     exceld:=Excelapp.cells[i,6].value;
     excele:=Excelapp.cells[i,7].value;
     excelf:=Excelapp.cells[i,8].value;
     excelg:=Excelapp.cells[i,9].value;
     excelh:=Excelapp.cells[i,10].value;
     exceli:=Excelapp.cells[i,11].value;
     excelj:=Excelapp.cells[i,12].value;
     if (excelx='') then
     exit
     else
      with ADOQuerySelectInsert do
        begin
         Close;
         SQL.Clear;
         SQL.Add('select * from cm_atm where atmno='''+excelx+''''); //excelx为关键字,不得有重复,若有重复,则该行的数据不导入
         Open;
        if Eof then  //插入数据
        begin
          Close;
          SQL.Clear;
          SQL.Add('insert into cm_atm(atmno,operid,savetm,optm,billstid,curstate,bankname,address,I_communityid,deptid,coor_x,coor_y)values(');
          SQL.Add(''''+excelx+''','''+excely+''','''+excela+''','''+excelb+''','''+excelc+''','''+exceld+''','''+excele+''','''+excelf+''',');
          SQL.Add(''''+excelg+''','''+excelh+''','''+exceli+''','''+excelj+''')');
          Row:=Row+1;
          ExecSQL;
        end
        else
          begin   //当查询出表中存在该记录时
          FailNum:=FailNum+1;
          {ADOQueryUpdate.Close;
          ADOQueryUpdate.SQL.Clear;
          ADOQueryUpdate.SQL.Add('update cm_atm set operid='''+excely+''',savetm='''+excela+''',optm='''+excelb+''',billstid='''+excelc+''',curstate='''+exceld+''',bankname='''+excele+''',address='''+excelf+''',I_communityid='''+excelg+''',deptid='''+excelh+''',coor_x='''+exceli+''',coor_y='''+excelj+''' where atmno='''+excelx+'''');
          ADOQueryUpdate.Open;
          ADOQueryUpdate.Close;
          ADOQueryUpdate.Free;  }
          end;
        end;
    end;
    finally
     ShowMessage('成功导入'+IntTostr(Row)+'条数据,修改'+IntTostr(FailNum)+'条数据');

     workBook.close;
     excelapp.quit;
     ExcelApp := Unassigned;
     WorkBook := Unassigned;
     end;

{=======================================================}