关于数据导出和导入的简单问题,谢谢

Question

我用delphi一年多了,最近编制工资程序,用到了整批数据的导出和导入问题,不知道用什么控件和设置?用的是oracle数据库.
我想把execl中的数据导入oracle中,再从oracle中导到execl中,主要是能在程序中控制!

Answer 1

oracle中导到execl
 程序段：FBOUT 给定一个DBGRID 和要保存的文件名即可，调用的xlsfile,控件可下载或mail
FUNCTION Fbout(dbgrid:tdbgrid;xfilename:string):BOOLEAN;
 var i,j,x:integer;
    XLSfile: TXLSfile;
   //子过程（针对不同类型的字段分别按不同方式写入）
   procedure addcell(row,col:integer;txt:string;xtype:integer=2);
   var
     SetAtribut:TSetOfAtribut;
    begin
      SetAtribut:=[];
       Include(SetAtribut,acBottomBorder);
       Include(SetAtribut,acTopBorder);
       Include(SetAtribut,acLeftBorder);
       Include(SetAtribut,acRightBorder);
      case xtype  of  //以下判断有助于避免身份证号变成科学记数法的问题
        0:begin
           Include(SetAtribut,acCenter);
           XLSFIle.AddWordCell(col,row,SetAtribut,StrToInt(txt));
          end;
        1:begin
           Include(SetAtribut,acRight);
           XLSFIle.AddDoubleCell(col,row,SetAtribut,StrToFloat(txt));
          end;
        2:begin
           Include(SetAtribut,acLeft);
           XLSFIle.AddStrCell(col,row,SetAtribut,txt);
          end;
      end;
   end;
begin
RESULT:=FALSE;
IF DBGRID.DataSource.DataSet.Active=FALSE THEN  EXIT;
if xfilename='' then exit;
   XLSfile:=TXLSfile.create(application);
   xlsfile.FileName:=xfilename;
   if copy(xlsfile.FileName,length(xlsfile.FileName)-3,4)<>'.xls' then xlsfile.FileName:=xlsfile.FileName+'.xls';
   xlsfile.clear;
   dbgrid.DataSource.DataSet.First;
   i:=0;
   j:=0;
    for i:=0 to  dbgrid.Columns.Count-1 do
       addcell(j+1,i+1,dbgrid.Columns[i].title.Caption,2);
   j:=j+1;
   while not dbgrid.DataSource.DataSet.Eof do
    begin
      for i:=0 to  dbgrid.Columns.Count-1 do
     if dbgrid.Columns[i].Field.AsString<>'' then
       begin
          if dbgrid.Columns[i].Field.DataType=ftinteger then x:=0 else
          if dbgrid.Columns[i].Field.DataType=ftfloat then x:=1 else
          if dbgrid.Columns[i].Field.DataType=ftword then x:=1 else
          x:=2;
          addcell(j+1,i+1,dbgrid.Columns[i].Field.AsString,x);
       end
       else
          addcell(j+1,i+1,'',2);
     j:=j+1;
     dbgrid.DataSource.DataSet.Next;
   end;
xlsfile.Write;
XLSfile.Free;
RESULT:=TRUE;
end;

Answer 2

execl中的数据导入oracle中的方法：
一、将excel数据存为.csv格式
二、逐个INSERT
oracle中的数据导入excel中的方法：
一、先选出所要的数据
二、写入EXCEL
如：
procedure Tfrmptax.Button3Click(Sender: TObject);
  var  ExcelApp,WorkBook:OLEVariant;
       i,j :integer;
       xlsFileName:string;
       recycle_length:integer;
       s_date:string;   begin
   s_date:=edit2.Text;
   savedialog2.Execute;
   xlsFileName:=savedialog2.FileName ;
        if xlsfilename=''  then
        begin
        showmessage('操作成功取消！');
        end
        else
           begin
           if edit2.text=''then
           begin                with query4 do
                begin
                CommUtils.initialQuery(Query4,'select count(T_id),sum(T_taxable),sum(T_amountax) from tax');
                open;
                recycle_length:=query4.Fields[0].AsInteger ;                end;                progressbar1.Max := recycle_length;
                progressbar1.position:=0;
                try
                 ExcelApp:= CreateOleObject( 'Excel.Application' );
                 WorkBook:=CreateOleobject('Excel.Sheet');
                except
                 application.Messagebox('无法打开Xls文件，请确认已 经安装EXCEL.','',mb_OK+mb_IconStop);                 Exit;
                 end;
                //workBook:= ExcelApp.workBooks.Open(ExtractFileDir(Application.ExeName)+'excel.xls');
                WorkBook:=ExcelApp.workbooks.Add;
                ExcelApp.Cells(1,1):='条数';
                ExcelApp.Cells(1,2):='工号';                ExcelApp.Cells(1,3):='姓名';
                ExcelApp.Cells(1,4):='人民币合计';
                ExcelApp.Cells(1,5):='减除费用额';                ExcelApp.Cells(1,6):='应纳税所得税';
                ExcelApp.Cells(1,7):='税率';
                ExcelApp.Cells(1,8):='速算扣除数';
                ExcelApp.Cells(1,9):='扣缴所得税额';
                j:=1;
                //.
                CommUtils.initialTable(table3,TBLNAME_TAX+'.dbf');
                //.
                table3.Open;
                table3.First;
                for i:=2 to recycle_length+1 do
                begin                ExcelApp.Cells(i,1):=j;
                ExcelApp.Cells(i,2):=table3.fieldbyname('T_id').Asstring;
                ExcelApp.Cells(i,3):=table3.fieldbyname('T_name').Asstring;
                ExcelApp.Cells(i,4):=table3.fieldbyname('T_total').Asfloat;
                ExcelApp.Cells(i,5):=table3.fieldbyname('T_deduct').Asfloat;                ExcelApp.Cells(i,6):=table3.fieldbyname('T_taxable').Asstring;
                ExcelApp.Cells(i,7):=FloatToStr(table3.fieldbyname('T_taxrate').asFloat*100)+'%';
                ExcelApp.Cells(i,8):=table3.fieldbyname('T_quick').Asfloat;
                ExcelApp.Cells(i,9):=table3.fieldbyname('T_amountax').Asfloat;                j:=j+1;                table3.next;
                progressbar1.position:=progressbar1.position+1;
                progressbar1.refresh ;
                end;
                ExcelApp.Cells(recycle_length+2,1):='合计';
                ExcelApp.Cells(recycle_length+2,6):=query4.Fields[1].Asfloat;
                ExcelApp.Cells(recycle_length+2,9):=query4.Fields[2].Asfloat;                WorkBook.SaveAS(xlsFileName);                WorkBook.close;
                 ShowMessage('导入Excel成功！');
                end
               else
               begin
                 with query4 do
                 begin
                 CommUtils.initialQuery(Query4,'select count(T_id),sum(T_taxable),sum(T_amountax) from tax'
                  +' where T_date = '+ #39+s_date+#39);
                 open;
                 recycle_length:=query4.Fields[0].AsInteger ;
                 end;                 with query5 do
                 begin
                 CommUtils.initialQuery(Query5,'select T_id,T_name,T_total,T_deduct,T_taxrate,T_quick, T_taxable,T_amountax from tax '
                  +'where T_date ='+ #39+s_date+#39+'order by T_id');
                 open;
                 end;                progressbar1.Max := recycle_length;
                progressbar1.position:=0;
                try
                 ExcelApp:= CreateOleObject( 'Excel.Application' );
                 WorkBook:=CreateOleobject('Excel.Sheet');
                except
                 application.Messagebox('无法打开Xls文件，请确认已 经安装EXCEL.','',mb_OK+mb_IconStop);                 Exit;
                 end;
                //workBook:= ExcelApp.workBooks.Open(ExtractFileDir(Application.ExeName)+'excel.xls');
                WorkBook:=ExcelApp.workbooks.Add;
                ExcelApp.Cells(1,1):='条数';
                ExcelApp.Cells(1,2):='工号';                ExcelApp.Cells(1,3):='姓名';
                ExcelApp.Cells(1,4):='人民币合计';
                ExcelApp.Cells(1,5):='减除费用额';                ExcelApp.Cells(1,6):='应纳税所得税';
                ExcelApp.Cells(1,7):='税率';
                ExcelApp.Cells(1,8):='速算扣除数';
                ExcelApp.Cells(1,9):='扣缴所得税额';                j:=1;
                CommUtils.initialTable(table3,TBLNAME_TAX+'.dbf');
                table3.Open;
                table3.First;
                for i:=2 to recycle_length+1 do
                begin                ExcelApp.Cells(i,1):=j;
                ExcelApp.Cells(i,2):=Query5.fieldbyname('T_id').Asstring;
                ExcelApp.Cells(i,3):=Query5.fieldbyname('T_name').Asstring;
                ExcelApp.Cells(i,4):=Query5.fieldbyname('T_total').Asfloat;
                ExcelApp.Cells(i,5):=Query5.fieldbyname('T_deduct').Asfloat;                ExcelApp.Cells(i,6):=Query5.fieldbyname('T_taxable').Asstring;
                ExcelApp.Cells(i,7):=FloatToStr(Query5.fieldbyname('T_taxrate').asFloat*100)+'%';
                ExcelApp.Cells(i,8):=Query5.fieldbyname('T_quick').Asfloat;
                ExcelApp.Cells(i,9):=Query5.fieldbyname('T_amountax').Asfloat;                j:=j+1;                query5.next;
                progressbar1.position:=progressbar1.position+1;
                progressbar1.refresh ;
                end;
                ExcelApp.Cells(recycle_length+2,1):='合计';
                ExcelApp.Cells(recycle_length+2,6):=query4.Fields[1].Asfloat;
                ExcelApp.Cells(recycle_length+2,9):=query4.Fields[2].Asfloat;                WorkBook.SaveAS(xlsFileName);                WorkBook.close;
                 ShowMessage('导入Excel成功！');               end;           end;end;