使用System.Data.OleDb.OleDbDataAdapter读取Excel到DataTable并用Oracle.DataAccess.Client.OracleBulkCopy将DataT...

 通过OleDbConnection的方法GetOleDbSchemaTable获取获取Excel的Sheet名

        /// <summary>
        /// 获取Excel的Sheet名
        /// </summary>
        /// <param name="strExcelPath"></param>
        /// <returns></returns>
        public static List<string> GetExcelSheetNames(string strExcelPath)
        {
            List<string> lstSheetNames = new List<string>();
            try
            {
                string strCon = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + strExcelPath + ";" + "Extended Properties=Excel 8.0;";
                string strSheetName = "";
                using (OleDbConnection con = new OleDbConnection(strCon))
                {
                    con.Open();
                    DataTable excelDataTableDescribe = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);//dtbl1 rows.Count  Sheet个数  第3列为 Sheet名

                    if (excelDataTableDescribe == null || excelDataTableDescribe.Columns.Count < 3 || excelDataTableDescribe.Rows.Count == 0) return lstSheetNames;

                    foreach (DataRow row in excelDataTableDescribe.Rows)
                    {
                        strSheetName = row[2].ToString().Trim();//row["TABLE_NAME"].ToString();
                        lstSheetNames.Add(strSheetName);
                    }
                }
            }
            catch (Exception ex) 
            { MessageBox.Show(ex.Message); }
            return lstSheetNames;
        }

通过OleDbDataAdapter的Fill方法将数据填充到DataTable

      public static DataTable GetData(string strPath, string strSheetName)
        {
            DataTable dtbl = new DataTable();
            try
            {
                string strCon = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + strPath + ";" + "Extended Properties=Excel 8.0;";

                String strCmd = "select * from [" + strSheetName + "]";
                OleDbDataAdapter cmd = new OleDbDataAdapter(strCmd, strCon);
                cmd.Fill(dtbl);

            }
            catch (Exception ex) { MessageBox.Show(ex.Message); }
            return dtbl;
        }

OracleBulkCopy

 /// <summary>
        /// 批量插入数据
        /// </summary>
        /// <param name="dt">数据表</param>
        /// <param name="targetTable">模板表名</param>
        /// <param name="dicStrFields">数据表字段与目标表字段映射</param>
        /// <param name="db">数据库连接</param>
        /// <returns></returns>
        public static bool BulkToDB(DataTable dt, string targetTable, Dictionary<string, string> dicStrFields, DBUtility.IDBConnection db)
        {
            if (dt == null || dt.Rows.Count == 0) return false;
            Application.DoEvents();

            int index = Regex.Matches(db.ConnectionString, ";").OfType<Match>().Select(t => t.Index).Max();
            string connstr = db.ConnectionString.Substring(0, index + 1);
            OracleBulkCopy bulkCopy = new OracleBulkCopy(connstr, OracleBulkCopyOptions.UseInternalTransaction);   //用其它源的数据有效批量加载Oracle表中
            bulkCopy.BatchSize = 100000;
            bulkCopy.BulkCopyTimeout = 260;
            bulkCopy.DestinationTableName = targetTable;    //服务器上目标表的名称
            bulkCopy.BatchSize = dt.Rows.Count;   //每一批次中的行数
            try
            {
                //foreach (DataColumn column in dt.Columns)
                foreach (KeyValuePair<string, string> kv in dicStrFields)
                {
                    bulkCopy.ColumnMappings.Add(kv.Value, kv.Key);//源列名->目标列名
                    //bulkCopy.ColumnMappings.Add(column.ColumnName, column.ColumnName);//源列名->目标列名
                }
                bulkCopy.WriteToServer(dt);   //将提供的数据源中的所有行复制到目标表中
                return true;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (bulkCopy != null)
                    bulkCopy.Close();
            }
        }

 //不读取Excel第一行
             string strConHDRYes = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + strPath + ";" + "Extended Properties='Excel 8.0;HDR=Yes;IMEX=1'";
             //读取第一行
             string strCon = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + strPath + ";" + "Extended Properties='Excel 8.0;HDR=No;IMEX=1'";

  OleDbDataAdapter cmd = new OleDbDataAdapter(strCmd, strCon);//列名

 cmd.Fill(ds,0,1 ,"res");

HDR=Yes 不读取第一行

不同的Excel版本使用的链接字符串不一样 注意：

"Extended Properties='Excel 8.0;HDR=No;IMEX=1'";这里有个单引号哦

                   case ".xls":
                        //string strCon = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + strPath + ";" + "Extended Properties=Excel 8.0;";
                        //第一行默认为列名  HDR=Yes时不读取第一行，DataTable的ColumnName可得到列名
                        strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + strExcelPath + ";Extended Properties='Excel 8.0;HDR=Yes;IMEX=1;'";
                        break;
                    case ".xlsx":
                        strCon = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + strExcelPath + ";Extended Properties='Excel 12.0;HDR=Yes;IMEX=1;'";
                        break;
                    default:
                        strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + strExcelPath + ";Extended Properties='Excel 8.0;HDR=Yes;IMEX=1;'";
                        break;

　　

 

 

转载于:https://www.cnblogs.com/shengfly/p/8301845.html