如何把Excel数据导入到ORACLE表中

[gejiajie0718]

项目中需要把报表导入到oracle中 该怎么实现 比如说前期excel和oracle需要做什么准备工作 希望好心人能提示一下 万分感谢！ 最好给个有源码的例子，是不是太贪心啦。 望解答 谢谢！

[贝隆]

你可以把这个问题分为两个部分
1、如何读取Excel文件
2、如何通过Sql语句把数据写入数据库
3、结合1和2
http://download.csdn.net/source/2341786
http://download.csdn.net/source/1644211

[gejiajie0718]

Dim rs As New ADODB.Recordset '定义记录集
Dim conn As New ADODB.Connection '定义数据连接
Private Sub CommandButton1_Click()
Set conn = New ADODB.Connection
Set rs = New ADODB.Recordset
conn.Open "Provider=MSDAORA.1;Password=ynsoft;User ID=system;Data Source=ORCL;Persist Security Info=True"
'rs.Open "insert into dyfcammeter t (t.no,t.zx311) values(116,2)", conn, adOpenStatic, adLockOptimistic
rs.Open "select * from dyfcammeter t order by t.no desc", conn, adOpenStatic, adLockOptimistic
rs.Close
End Sub
查出来的数据集怎么写入到excel里 求解释

[gejiajie0718]

Dim rs As New ADODB.Recordset '定义记录集
Dim conn As New ADODB.Connection '定义数据连接
Dim i As Integer
Private Sub CommandButton1_Click()
Set conn = New ADODB.Connection
Set rs = New ADODB.Recordset
conn.Open "Provider=MSDAORA.1;Password=ynsoft;User ID=system;Data Source=ORCL;Persist Security Info=True"
'rs.Open "insert into dyfcammeter t (t.no,t.zx311) values(116,2)", conn, adOpenStatic, adLockOptimistic
rs.Open "select * from dyfcammeter t order by t.no desc", conn, adOpenStatic, adLockOptimistic
Cells.Clear
For i = 0 To rs.Fields.Count - 1
Cells(1, i + 1) = rs.Fields(i).Name
Next i
Range("a2").CopyFromRecordset rs
Cells.Columns.AutoFit
rs.Close
End Sub

好了 记录下

[gejiajie0718]

Private Sub CommandButton1_Click()
Dim cnOra As ADODB.Connection
Dim rsOra As ADODB.Recordset
Dim j, m As Integer
Dim SQL As String
Set cnOra = New ADODB.Connection
Set rsOra = New ADODB.Recordset
m = 1
j = 1
On Error GoTo err
cnOra.Open "Provider=MSDAORA.1;Password=ynsoft;User ID=system;Data Source=ORCL;Persist Security Info=True"
rsOra.Open "insert into dual(DUMMY) values (2)", cnOra, adOpenForwardOnly
' rsOra.Open "select * from dual", cnOra, adOpenForwardOnly
' For m = 1 To rsOra.Fields.Count
' Sheet1.Cells(j, m) = rsOra(m - 1).Name
' Next
' j = j + 1
' rsOra.MoveFirst
' While Not rsOra.EOF
' For m = 1 To rsOra.Fields.Count
' Cells(j, m) = rsOra(m - 1).Value
' Next
' rsOra.MoveNext
' j = j + 1
' Wend
' Exit Sub
err:
MsgBox ("失败！")
'关闭连接
rsOra.Close
cnOra.Close
End Sub


查询可以 插入数据报错 求解

[nklfs]

给你一个最简单的手工处理方法：

前提条件（约束）：
1、本机有excel文件，并且每个sheet是一个独立的表
2、本机安装了Oracle的客户端工具
3、本机安装了excel和access软件

整体思路：
用access作桥梁，把excel的数据导入到oracle或者其他数据库，特点是“只要手工操作，几乎不用写什么代码或者VBA，只要简单的SQL就Ok了”

操作步骤：
1、打开Access，选择导入数据，导入excel的数据，每个sheet的表头作为表格表头，sheet名称作为table名称
2、这样，你就把excel的数据导入了access数据库，并且建立了关系型数据；
3、在access中，创建Oracle的链接表（这个查帮助吧。），把需要操作的oracle数据库的表格连接到access中，作为一个独立的link table
4、写一个select into或者 insert into select语句，就可以把刚刚导入的数据插入到Oracle中。

[雷泽归妹]

用smimport控件，非常的快。
结合CxGrid组件，可以实现类似Toad的导入向导。

unit frmMain;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, cxGraphics, cxControls, cxLookAndFeels, cxLookAndFeelPainters,
  cxStyles, dxSkinsCore, dxSkinsDefaultPainters, cxCustomData, cxFilter, cxData,
  cxDataStorage, cxEdit, DB, cxDBData, cxGridLevel, cxClasses, cxGridCustomView,
  cxGridCustomTableView, cxGridTableView, cxGridDBTableView, cxGrid,
  cxGridCustomPopupMenu, cxGridPopupMenu, DBClient, SMIBase, SMI2TXT, RzButton,
  Menus, SMI2DS, UniProvider, ODBCUniProvider, AccessUniProvider, MemDS,
  DBAccess, Uni, ExtCtrls, RzPanel, RzTabs, ComCtrls, RzListVw, StdCtrls, Mask,
  RzEdit, SMI2Cell, SMI2XLS, RzRadChk, RzRadGrp, RzLabel, RzSpnEdt, ImgList;

type
  TMenuNotifyEvent = procedure(Sender: TMenuItem) of object;

  //数据来源的枚举类型
  TImportFrom=(ifClipBoard,ifTextFile,ifCSVFile,ifXLSFile);

  //定义记录数据导入控件主要属性的记录，方便设置
  TSMIConfig =record
    impFrom:TImportFrom; //来源
    FldDli:TSMIFieldDelimiter;//字段分隔符
    FldDliCst:Char;//自定义
    RowSpt:TSMIRecordSeparator; //行分隔符
    TxtQaf:TSMITextQualifier;//文本标识符
    RowFirst:Integer;//第一行
    RowLast:Integer;//最后一行
    RowFieldNames:Integer;//字段名所在行
  end;

  TForm1 = class(TForm)
    cxGridPopupMenu1: TcxGridPopupMenu;
    SMImportFromClipboard: TSMImportFromClipboard;
    DataSource1: TDataSource;
    popCol: TPopupMenu;
    SMImportFromDataSet: TSMImportFromDataSet;
    UniConnection1: TUniConnection;
    UniTable1: TUniTable;
    AccessUniProvider1: TAccessUniProvider;
    RzPageControl1: TRzPageControl;
    TabSheet1: TRzTabSheet;
    RzPanel1: TRzPanel;
    RzButton2: TRzButton;
    RzButton1: TRzButton;
    RzButton3: TRzButton;
    cxGridImp: TcxGrid;
    grdImp: TcxGridDBTableView;
    cxGridImpLevel1: TcxGridLevel;
    TabSheet2: TRzTabSheet;
    grdData: TcxGridDBTableView;
    cxGridDataLevel1: TcxGridLevel;
    cxGridData: TcxGrid;
    RzPanel2: TRzPanel;
    RzButton4: TRzButton;
    UniQuery1: TUniQuery;
    UniDataSource1: TUniDataSource;
    grdDataWellName: TcxGridDBColumn;
    grdDataXPos: TcxGridDBColumn;
    grdDataYPos: TcxGridDBColumn;
    grdDataInfo: TcxGridDBColumn;
    pgBar: TProgressBar;
    SMImportFromXLS: TSMImportFromXLS;
    SMImportFromText: TSMImportFromText;
    TabSheet4: TRzTabSheet;
    RzRadioGroup1: TRzRadioGroup;
    RzGroupBox1: TRzGroupBox;
    RzEdit1: TRzEdit;
    RzButton5: TRzButton;
    lvMenu: TRzListView;
    RzButton6: TRzButton;
    OpenDialog1: TOpenDialog;
    RzRadioGroup2: TRzRadioGroup;
    RzEdit3: TRzEdit;
    RzRadioGroup4: TRzRadioGroup;
    RzGroupBox3: TRzGroupBox;
    RzLabel2: TRzLabel;
    RzLabel3: TRzLabel;
    RzLabel4: TRzLabel;
    RzPanel3: TRzPanel;
    RzLabel1: TRzLabel;
    RzEdit2: TRzEdit;
    RzButton7: TRzButton;
    RzRadioGroup3: TRzRadioGroup;
    RzPanel4: TRzPanel;
    RzButton8: TRzButton;
    RzButton9: TRzButton;
    RzButton10: TRzButton;
    RzSpinEdit1: TRzSpinEdit;
    RzSpinEdit2: TRzSpinEdit;
    RzSpinEdit3: TRzSpinEdit;
    RzLabel5: TRzLabel;
    RzEdit4: TRzEdit;
    RzLabel6: TRzLabel;
    lblInfo: TRzLabel;
    RzLabel8: TRzLabel;
    RzButton11: TRzButton;
    RzButton12: TRzButton;
    RzButton13: TRzButton;
    procedure SMImportFromClipboardCreateStructure(Sender: TObject;
      Columns: TSMIColumns);
    procedure RzButton1Click(Sender: TObject);
    procedure cxGridPopupMenu1Popup(ASenderMenu: TComponent;
      AHitTest: TcxCustomGridHitTest; X, Y: Integer; var AllowPopup: Boolean);

    procedure RzButton2Click(Sender: TObject);
    procedure FormCreate(Sender: TObject);
    procedure RzButton3Click(Sender: TObject);
    procedure RzButton4Click(Sender: TObject);
    procedure SMImportFromDataSetAfterRecordEvent(Sender: TObject;
      var Abort: Boolean);
    procedure RzButton5Click(Sender: TObject);
    procedure AddMenuText(MenuText: String);
    procedure RzButton6Click(Sender: TObject);
    procedure RzRadioGroup1Changing(Sender: TObject; NewIndex: Integer;
      var AllowChange: Boolean);
    procedure RzButton7Click(Sender: TObject);
    procedure RzButton10Click(Sender: TObject);
    procedure RzButton8Click(Sender: TObject);
    procedure RzButton9Click(Sender: TObject);
    procedure RzRadioGroup2Changing(Sender: TObject; NewIndex: Integer;
      var AllowChange: Boolean);
    procedure RzRadioGroup4Changing(Sender: TObject; NewIndex: Integer;
      var AllowChange: Boolean);
    procedure RzRadioGroup3Changing(Sender: TObject; NewIndex: Integer;
      var AllowChange: Boolean);
    procedure RzSpinEdit1Change(Sender: TObject);
    procedure RzSpinEdit2Change(Sender: TObject);
    procedure RzSpinEdit3Change(Sender: TObject);
    procedure SMImportFromTextCreateStructure(Sender: TObject;
      Columns: TSMIColumns);
    procedure SMImportFromXLSCreateStructure(Sender: TObject;
      Columns: TSMIColumns);
    procedure grdImpCustomDrawIndicatorCell(Sender: TcxGridTableView;
      ACanvas: TcxCanvas; AViewInfo: TcxCustomGridIndicatorItemViewInfo;
      var ADone: Boolean);
    procedure SMImportFromClipboardAfterRecordEvent(Sender: TObject;
      var Abort: Boolean);
    procedure SMImportFromTextAfterRecordEvent(Sender: TObject;
      var Abort: Boolean);
    procedure SMImportFromXLSAfterRecordEvent(Sender: TObject;
      var Abort: Boolean);
    procedure RzButton11Click(Sender: TObject);
    procedure grdDataCustomDrawIndicatorCell(Sender: TcxGridTableView;
      ACanvas: TcxCanvas; AViewInfo: TcxCustomGridIndicatorItemViewInfo;
      var ADone: Boolean);
    procedure UniTable1AfterOpen(DataSet: TDataSet);
    procedure UniTable1BeforeOpen(DataSet: TDataSet);
    procedure RzButton12Click(Sender: TObject);
    procedure RzButton13Click(Sender: TObject);
  private
    { Private declarations }
    mDataSet:TClientDataSet;
    iSelCol,iStep:integer;
    mConf:TSMIConfig;
    indStyle,OddStyle:TCxStyle;
    //右键菜单对应字段的偏移量，在这个偏移量之前的是系统给定的固定菜单项数目
    //在这里使用了两个菜单项：
    //一个是动态生成的与数据列表同名的菜单项，选择这个菜单项则取消匹配
    //一个是分隔条
    iMenuPos:integer;

    procedure PopMenuItemClick(Sender:TMenuItem);
    procedure Changetep(iMod:integer);
    function SplitString(const Source, ch: string): TStringList;

  public
    { Public declarations }
  end;

[雷泽归妹]

procedure TForm1.RzRadioGroup1Changing(Sender: TObject; NewIndex: Integer;
  var AllowChange: Boolean);
var
  sFilter:String;
begin
  RzButton3Click(NIL);
  //清除文件名编辑框
  rzedit2.Clear;
  //设置其它选项的默认值
  RzRadioGroup2.ItemIndex:=0 ;
  RzRadioGroup4.ItemIndex:=1;
  RzRadioGroup3.ItemIndex:=0;
  //设置默认的字段分隔符为制表键
  mconf.FldDli:=fdTab;
  //如果是选择了从剪贴板导入数据，那么隐藏文件选择
  if NewIndex=0 then
  begin
    rzPanel3.Visible:=False;
    mconf.impFrom:=ifClipBoard;
  end
  else
  begin
    rzEdit2.Clear;
    case NewIndex of
    1:
      begin
        sFilter:='文本文件（*.TXT）|*.TXT';
        mconf.impFrom:=ifTextFile;
      end;
    2:
      begin
        sFilter:='逗号分割的数据文件（*.CSV）|*.CSV';
        RzRadioGroup2.ItemIndex:=2;
        mconf.FldDli:=fdComma;
        mconf.impFrom:=ifCSVFile;
      end;
    3:
      begin
        sFilter:='Excel文件（*.XLS）|*.XLS';
        mconf.impFrom:=ifXLSFile;
      end;
    end;
    rzPanel3.Visible:=True;

    //设置打开文件对话框的过滤条件
    OpenDialog1.Filter:=sFilter;
  end;
end;

procedure TForm1.RzRadioGroup2Changing(Sender: TObject; NewIndex: Integer;
  var AllowChange: Boolean);
begin
  case NewIndex of
  0: //Tab
  begin
    mconf.FldDli:=fdTab;
  end;
  1: //;
  begin
    mconf.FldDli:=fdSemicolon;
  end;
  2://,
  begin
    mconf.FldDli:=fdComma;
  end;
  3://{ }空格
  begin
    mconf.FldDli:=fdSpace;
  end;
  4://自定义
  begin
    mconf.FldDli:=fdCustom;
    mconf.FldDliCst:=rzedit3.EditText[1];
  end;
  end;
end;

[雷泽归妹]

procedure TForm1.RzRadioGroup3Changing(Sender: TObject; NewIndex: Integer;
  var AllowChange: Boolean);
begin
  case NewIndex of
  0://回车换行
  begin
    mconf.RowSpt:=rsCRLF;
  end;
  1://回车
  begin
    mconf.RowSpt:=rsCR;
  end;
  2://换行
  begin
    mconf.RowSpt:=rsLF;
  end;
  end;
end;

procedure TForm1.RzRadioGroup4Changing(Sender: TObject; NewIndex: Integer;
  var AllowChange: Boolean);
begin
  case NewIndex of
  0://无文本界定符
  begin
    mconf.TxtQaf:=tqNone;
  end;
  1://双引号
  begin
    mconf.TxtQaf:=tqQuot;
  end;
  2://单引号
  begin
    mconf.TxtQaf:=tqApos;
  end;
  end;
end;

procedure TForm1.RzSpinEdit1Change(Sender: TObject);
begin
  //设置起始行
  mconf.RowFirst:=trunc(rzspinedit1.Value);
  //设置标题行为起始行-1
  rzspinedit3.Max:=trunc(rzspinedit1.Value)-1;
  //如果起始行大于1,那么设置字段名所在行为1否则不设置字段名所在行，所有的行都为数据
  if mconf.RowFirst>1 then
  begin
    rzspinedit3.Value:=1;
    mconf.RowFieldNames:=1;
  end
  else
  begin
    rzspinedit3.Value:=0;
    mconf.RowFieldNames:=0;
  end;

end;

procedure TForm1.RzSpinEdit2Change(Sender: TObject);
begin
  //设置数据的结束行，如果没有设置，那么设置默认的结束行为2147483647
  mconf.RowLast:=trunc(rzspinedit2.Value);
  if mconf.RowLast=0 then mconf.RowLast:=2147483647;
end;

procedure TForm1.RzSpinEdit3Change(Sender: TObject);
begin
  //设置字段名所在行
  mconf.RowFieldNames:=trunc(rzspinedit3.Value);
end;

procedure TForm1.SMImportFromClipboardAfterRecordEvent(Sender: TObject;
  var Abort: Boolean);
begin
  //显示从剪贴板导入数据时的信息
  with SMImportFromClipboard do
  begin
    lblInfo.Caption:=inttostr(Statistic.TotalImported);
  end;
  //允许处理Windows事件
  application.ProcessMessages;
end;

procedure TForm1.SMImportFromClipboardCreateStructure(Sender: TObject;
  Columns: TSMIColumns);
var
  i: Integer;
begin
  {删除记录集中的所有字段}
  mDataSet.Close;
  mDataSet.FieldDefs.Clear;

  {根据剪贴板中的内容创建记录集字段}
  for i := 0 to Columns.Count-1 do
  begin
    //设置字段的大小
    if Columns[i].Size = 0 then
      Columns[i].Size := 20;
    //如果指定了字段名所在的行，那么字段名就从指定的行获取，
    //如果没有指定字段名所在的行，那么就按Field[序列号]的形式给每个字段指定一个字段名
    if (Columns[i].FieldName = '') then
      Columns[i].FieldName := 'Field' + IntToStr(i);
    //根据列的类型创建列
    case Columns[i].DataType of
      itString: mDataSet.FieldDefs.Add(Columns[i].FieldName, ftString, Columns[i].Size, False);
      itInteger: mDataSet.FieldDefs.Add(Columns[i].FieldName, ftInteger, 0, False);
      itFloat: mDataSet.FieldDefs.Add(Columns[i].FieldName, ftFloat, 0, False);
      itDateTime: mDataSet.FieldDefs.Add(Columns[i].FieldName, ftDateTime, 0, False);
      itDate: mDataSet.FieldDefs.Add(Columns[i].FieldName, ftDate, 0, False);
      itTime: mDataSet.FieldDefs.Add(Columns[i].FieldName, ftTime, 0, False);
      itBoolean: mDataSet.FieldDefs.Add(Columns[i].FieldName, ftBoolean, 0, False);
    else//如果不属于上面任何一种类型，那么按照字符类型创建字段
      mDataSet.FieldDefs.Add(Columns[i].FieldName, ftString, 10, False);
    end;
  end;

  //创建记录集
  mDataSet.CreateDataset;

  {打开记录集}
  mDataSet.Active := True;
  mDataSet.LogChanges := False;

  {设置默认的对照关系}
  SMImportFromClipboard.Mappings.Clear;
  SMImportFromClipboard.Columns2Mapping;

  //更新数据表中的字段信息，用来显示数据
  grdImp.DataController.DataSource:=DataSource1;
  //动态创建CXGrid里面的列
  grdImp.DataController.CreateAllItems(False);
end;

[咸清]

直接导入就可以了。oracle支持cvs格式和excel格式数据。

[xiaoyu871225]

为什么要整那么麻烦撒，楼主又没说要用代码实现，直接导就OK，我知道Toad 工具是可以直接到excel文件的