由于一直基于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 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);
}
}
}
{
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);
}
}
}
本文转自博客园伍华聪的博客，原文链接：批量Excel数据导入Oracle数据库，如需转载请自行联系原博主。
