批量Excel数据导入Oracle数据库-阿里云开发者社区

开发者社区> 数据库> 正文
登录阅读全文

批量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);
                }
            }
        }

 本文转自博客园伍华聪的博客,原文链接:批量Excel数据导入Oracle数据库,如需转载请自行联系原博主。



版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

分享:
数据库
使用钉钉扫一扫加入圈子
+ 订阅

分享数据库前沿,解构实战干货,推动数据库技术变革

其他文章
最新文章
相关文章