分享一个excel导入oracle的方法

        1，先将excel文件转换为DataSet类型结果

        public DataSet xlsToDataSet(string filePath, string sheetName)
        {
            DataSet ds = new DataSet();
            //string filePath = this._filePath + fileName;
            string strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filePath + ";Extended Properties='Excel 12.0;HDR=No;IMEX=1'";
            OleDbDataAdapter oada = new OleDbDataAdapter("select * from [" + sheetName + "$]", strConn);
            oada.Fill(ds);
            return ds;
        }

2，xls文件转换为DataTable类型结果,只增加列名，调用了上一个方法

        public DataTable xlsToDataTable(string filePath, string sheetName, string[] colNames)
        {
            DataTable dt = new DataTable();
            int i = 0;
            for (i = 0; i < colNames.Length; i++)
            {
                dt.Columns.Add(colNames[i].ToString().Trim(), typeof(string));
            }
            //读取excle文件到临时DataTable中
            DataTable tmpDt = xlsToDataSet(filePath, sheetName).Tables[0];

            //将临时DataTable tmpDt中数据格式化
            DataRow dr = null;
            int countx = 0;
            try
            {
                for (i = 0; i < tmpDt.Rows.Count; i++)
                {
                    dr = dt.NewRow();
                    for (int j = 0; j < colNames.Length; j++)
                    {
                        dr[j] = tmpDt.Rows[i][j].ToString().Trim();
                    }

                    dt.Rows.Add(dr);
                }
            }
            catch (Exception ex)
            {
            }
            return dt;
        }

3，将DataTable中的数据拼INSERT sql语句，插入数据库

            public int batInsert(string tablename, string[] colNames, DataTable dt)
            {
                connDB();
                OracleDataAdapter OracleAdapter = new OracleDataAdapter();
                //string insertSql="insert into t_gb64kc (ny,xh,ddj,cc,jgj,clgl) values(:ny,:xh,:ddj,:cc,:jgj,:clgl)"
                string insertSql = "insert into " + tablename + " (";
                string sqlTmp = " values(";
                for (int i = 0; i < colNames.Length; i++)
                {
                    insertSql += colNames[i].Trim() + ",";
                    sqlTmp += ":" + colNames[i].Trim() + ",";
                }
                insertSql = insertSql.Trim(',') + ") " + sqlTmp.Trim(',') + ")";

                OracleAdapter.InsertCommand = new OracleCommand();
                OracleAdapter.InsertCommand.CommandType = CommandType.Text;
                OracleAdapter.InsertCommand.CommandText = insertSql;
                OracleParameter[] insertParms = arrayToSqlParam(colNames);
                OracleAdapter.InsertCommand.Parameters.AddRange(insertParms);
                OracleAdapter.InsertCommand.Connection = this._conn;

                int r = OracleAdapter.Update(dt);
                dt.AcceptChanges();
                return r;
            }

OVER