Excel to DataBase Excel数据导入到数据库

欢迎各位下载我写的一个Excel与数据库互导数据的小程序   http://download.csdn.net/detail/lxp520llq/4507689

class ExceltoDatabase    {        public static string FillData(string openFilePath, string ID, string pwd, string dataBase, string tableName, string dataBaseType)        {            DataTable dt = ExcelToDt(openFilePath);            if (dataBaseType=="Oracle")            {                using (OracleConnection conn = new OracleConnection("Data Source=" + dataBase + ";uid=" + ID + ";pwd=" + pwd + ";"))                {                    conn.Open();                    for (int i = 1; i < dt.Columns.Count; i++)        {                        for (int j = 0; j < dt.Rows.Count; j++)                        {                            string cmd = "insert into " + tableName + " (" + dt.Columns[i].ColumnName.ToString() + ") values (" + dt.Rows[j][dt.Columns[i].ColumnName.ToString()].ToString() + ")";                            OracleCommand cmdStr = new OracleCommand(cmd, conn);                            cmdStr.ExecuteNonQuery();                        }        }                }            }            if (dataBaseType == "SQL Server")            {                using (SqlConnection conn = new SqlConnection("DataBase=" + dataBase + ";uid=" + ID + ";pwd=" + pwd + ";"))                {                    conn.Open();                    for (int i = 1; i < dt.Columns.Count; i++)                    {                        for (int j = 0; j < dt.Rows.Count; j++)                        {                            string cmd = "insert ino " + tableName + " (" + dt.Columns[i].ColumnName.ToString() + ") values (" + dt.Rows[j][dt.Columns[i].ColumnName.ToString()].ToString() + ")";                            SqlCommand cmdStr = new SqlCommand(cmd, conn);                            cmdStr.ExecuteNonQuery();                        }                    }                }            }            return "导入成功";        }        public static DataTable ExcelToDt(string Path)        {            //2007或者2010版本的Office            //string conn_str = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + Path + "; Extended Properties=Excel 12.0;";            //2003的Office            string conn_str = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + Path + ";" + "Extended Properties=Excel 8.0;";            object missing = Missing.Value;//在COM调用时进行占位用的，如果某个参数不会被使用，则使用这个进行占位            Microsoft.Office.Interop.Excel.Application excelApp = new Microsoft.Office.Interop.Excel.Application();            excelApp.Visible = false;            try            {                excelApp.Workbooks.Open(Path, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing);                Microsoft.Office.Interop.Excel.Worksheet ws = (Microsoft.Office.Interop.Excel.Worksheet)excelApp.Workbooks[1].Worksheets[1];//打开第一个工作薄中的第一张表                string sheetName = ws.Name;//获取工作表的Name                DataTable dt = new DataTable();                using (OleDbConnection conn = new OleDbConnection(conn_str))                {                    conn.Open();                    OleDbDataAdapter adp = new OleDbDataAdapter("select * from [" + sheetName + "$]", conn);                    adp.Fill(dt);                }                excelApp.Workbooks.Close();                excelApp.Quit();                releaseObject(ws);                excelApp = null;                return dt;            }            catch (Exception)            {                throw;            }        }        #region 释放内存资源        private static void releaseObject(object obj)        {            try            {                System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);//释放Com对象                obj = null;            }            catch (Exception)            {                obj = null;            }            finally            {                GC.Collect();            }        }        #endregion    }