批量Excel数据导入Oracle数据库

由于一直基于Oracle数据库上做开发，因此常常会需要把大量的Excel数据导入到Oracle数据库中，其实如果从事SqlServer数据库的开发，那么思路也是一样的，本文主要介绍如何导入Excel数据进入Oracle数据库的内容。

一般我们拿到的Excel数据，都会有一个表头说明，然后下面是一连串的数据内容，如下图所示：

 

而Oracle中数据库一般为英文名称，中文名称就需要转义，为了方便导入，我把中文名称对照数据库的字段，把表头修改为对应的字段名称，如果没有数据库对应的字段，那么删除Excel的无用列即可，如下所示。

 

首先我们在导入Excel的例子中加载显示要导入的数据，一个是为了直观，第二个也是为了检查数据的有效性，避免出错，界面如下所示：

 

在介绍导入操作前，我们先要分析下数据，否则就很容易出现错误的语句，一般日期的格式、数字的格式就要特别注意，文本格式一般看是否超出字段的长度，一般成功导入前都会发生好多次的错误问题，解决了这些格式的问题，基本上就OK了。如下面日期和数字的格式问题，就必须注意转换为对应的内容格式：

 

 

下面介绍具体的显示数据和导入数据的操作代码：

 显示Excel数据的代码如下所示：

 代码

         private   string  connectionStringFormat  =   " Provider = Microsoft.Jet.OLEDB.4.0 ; Data Source = '{0}';Extended Properties=Excel 8.0 " ;
         private  DataSet myDs  =   new  DataSet();

         private   void  btnViewData_Click( object  sender, EventArgs e)
        {
             if  ( this .txtFilePath.Text  ==   "" )
            {
                MessageUtil.ShowTips( " 请选择指定的Excel文件 " );
                 return ;
            }

             string  connectString  =   string .Format(connectionStringFormat,  this .txtFilePath.Text);
             try
            {
                myDs.Tables.Clear();
                myDs.Clear();
                OleDbConnection cnnxls  =   new  OleDbConnection(connectString);
                OleDbDataAdapter myDa  =   new  OleDbDataAdapter( " select * from [Sheet1$] " , cnnxls);
                myDa.Fill(myDs,  " c " );

                dataGrid1.DataSource  =  myDs.Tables[ 0 ];
            }
             catch  (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

 

 

导入操作的代码如下所示(由于数据格式需要验证，以及需要判断数据库是否存在指定关键字的记录，如果存在，那么更新，否则插入新的记录，如果仅仅是第一次导入，操作代码可以更为精简一些）：

 代码

         private   void  btnSaveData_Click( object  sender, EventArgs e)
        {
             if  ( this .txtFilePath.Text  ==   "" )
            {
                MessageUtil.ShowTips( " 请选择指定的Excel文件 " );
                 return ;
            }

             if  (MessageUtil.ShowYesNoAndWarning( " 该操作将把数据导入到系统的用户数据库中，您确定是否继续？ " )  ==  DialogResult.Yes)
            {
                InsertData();
            }
        }

         private   bool  CheckIsDate( string  columnName)
        {
             string  str  =   " ,PREPARE_DATE,COPY_DATE,COPY_VALIDITY,BUSINESS_VALIDITY,OPENING_APPROVAL_DATE,OPENING_DATE,EDITTIME,LICENSE_DATE,LICENSE_VALIDITY,TEMP_OPENING_DATE,LICENSE_START_DATE,ADDTIME,EDITTIME, " ;
             return  str.Contains( " , "   +  columnName.ToUpper()  +   " , " );
        }

         private   bool  CheckIsNumeric( string  columnName)
        {
             string  str  =   " ,FIXED_CAPITAL,REG_CAPITAL,MARGIN,PARK_AREA,PARK_SPACE_NUMBER, " ;
             return  str.Contains( " , "   +  columnName.ToUpper()  +   " , " );
        }

         private   void  InsertData()
        {
             int  intOk  =   0 ;
             int  intFail  =   0 ;

             if  (myDs  !=   null   &&  myDs.Tables[ 0 ].Rows.Count  >   0 )
            {
                 string  accessConnectString  =  config.GetConnectionString( " DataAccess " );
                OracleConnection conn  =   new  OracleConnection(accessConnectString);
                conn.Open();
                OracleCommand com  =   null ;

                 #region  组装字段列表
                 string  insertColumnString  =   " ID, " ;
                DataTable dt  =  myDs.Tables[ 0 ];
                 int  k  =   0 ;
                 foreach  (DataColumn col  in  dt.Columns)
                {
                    insertColumnString  +=   string .Format( " {0}, " , col.ColumnName);
                }
                insertColumnString  =  insertColumnString.Trim( ' , ' );

                 #endregion

                 try
                {
                     foreach  (DataRow dr  in  dt.Rows)
                    {
                         if  (dr[ 0 ].ToString()  ==   "" )
                        {
                             continue ;
                        }

                         #region  组装Sql语句
                         string  insertValueString  =   " SEQ_TBPARK_ENTERPRISE.Nextval, " ;
                         string  updateValueString  =   "" ;
                         string  COMPANY_CODE  =  dr[ " COMPANY_CODE " ].ToString().Replace( " <空> " ,  "" );

                         #region  拼接Sql字符串

                         for ( int  i  =   0 ; i  <  dt.Columns.Count; i ++ )
                        {
                             string  originalValue  =  dr[i].ToString().Replace( " <空> " ,  "" );
                             // if (!CheckIsDate(dt.Rows[0][i].ToString()))
                             if  ( ! CheckIsDate(dt.Columns[i].ColumnName))
                            {
                                 if  ( ! string .IsNullOrEmpty(originalValue))
                                {
                                     if  (CheckIsNumeric(dt.Columns[i].ColumnName))
                                    {
                                        insertValueString  +=   string .Format( " '{0}', " , Convert.ToDecimal(originalValue));
                                        updateValueString  +=   string .Format( " {0}='{1}', " , dt.Columns[i].ColumnName, Convert.ToDecimal(originalValue));
                                    }
                                     else
                                    {
                                        insertValueString  +=   string .Format( " '{0}', " , originalValue);
                                        updateValueString  +=   string .Format( " {0}='{1}', " , dt.Columns[i].ColumnName, originalValue);
                                    }
                                }
                                 else
                                {
                                    insertValueString  +=   string .Format( " NULL, " );
                                    updateValueString  +=   string .Format( " {0}=NULL, " , dt.Columns[i].ColumnName);
                                }
                            }
                             else
                            {
                                 if  ( ! string .IsNullOrEmpty(originalValue))
                                {
                                    insertValueString  +=   string .Format( " to_date('{0}','yyyy-mm-dd'), " , Convert.ToDateTime(originalValue).ToString( " yyyy-MM-dd " ));
                                    updateValueString  +=   string .Format( " {0}=to_date('{1}','yyyy-mm-dd'), " , dt.Columns[i].ColumnName, Convert.ToDateTime(originalValue).ToString( " yyyy-MM-dd " ));
                                }
                                 else
                                {
                                    insertValueString  +=   string .Format( " NULL, " );
                                    updateValueString  +=   string .Format( " {0}=NULL, " , dt.Columns[i].ColumnName);
                                }
                            }
                        }
                        insertValueString  =  insertValueString.Trim( ' , ' );
                        updateValueString  =  updateValueString.Trim( ' , ' ); 
                         #endregion

                         string  insertSql  =   string .Format( @" INSERT INTO tbpark_enterprise ({0}) VALUES({1}) " , insertColumnString, insertValueString);
                         string  updateSql  =   string .Format( " Update tbpark_enterprise set {0} Where COMPANY_CODE='{1}'  " , updateValueString, COMPANY_CODE);
                         string  checkExistSql  =   string .Format( " Select count(*) from tbpark_enterprise where COMPANY_CODE='{0}'  " , COMPANY_CODE);
                         #endregion

                         #region  写入数据
                         try
                        {
                            com  =   new  OracleCommand();
                            com.Connection  =  conn;
                            com.CommandText  =  checkExistSql;
                             object  objCount  =  com.ExecuteScalar();

                             bool  succeed  =   false ;
                             bool  exist  =  Convert.ToInt32(objCount)  >   0 ;
                             if  (exist)
                            {
                                 // 需要更新
                                 // WriteString(updateSql);
                                com.CommandText  =  updateSql;
                                succeed  =  com.ExecuteNonQuery()  >   0 ;
                            }
                             else
                            {
                                 // 需要插入
                                 // WriteString2(insertSql);
                                com.CommandText  =  insertSql;
                                succeed  =  com.ExecuteNonQuery()  >   0 ;
                            }

                             if  (succeed)
                            {
                                intOk ++ ;
                            }
                             else
                            {
                                intFail ++ ;
                            }
                        }
                         catch  (Exception ex)
                        {
                            intFail ++ ;
                            WriteString(com.CommandText);
                            LogHelper.Error(ex);
                             break ;
                        }

                         #endregion
                    }

                     #region  关闭
                     if  (conn  !=   null   &&  conn.State  !=  ConnectionState.Closed)
                    {
                        conn.Close();
                    }
                     if  (com  !=   null )
                    {
                        com.Dispose();
                    }
                     #endregion
                }
                 catch  (Exception ex)
                {
                    LogHelper.Error(ex);
                    MessageUtil.ShowError(ex.ToString());
                }

                 if  (intOk  >   0   ||  intFail  >   0 )
                {
                     string  tips  =   string .Format( " 数据导入成功：{0}个，失败：{1}个 " , intOk, intFail);
                    MessageUtil.ShowTips(tips);
                }
            }
        }

 

以上代码，为了方便，使用了输出脚本的方式进行验证对比，一般情况下也是用得着的。

最后附上该程序的源码，和大家分享学习：http://files.cnblogs.com/wuhuacong/ImportExcelToOracle.rar