程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 數據庫知識 >> Oracle數據庫 >> Oracle數據庫基礎 >> 批量Excel數據導入Oracle數據庫

批量Excel數據導入Oracle數據庫

編輯: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 

主要研究技術:代碼生成工具、Visio二次開發
  1. 上一頁:
  2. 下一頁:
Copyright © 程式師世界 All Rights Reserved