private void btnInsert_Click(object sender, EventArgs e) { if (openFileDialog1.ShowDialog() == DialogResult.OK) ds = ImportExcel(openFileDialog1.FileName); else return; Thread thread = new Thread(new ThreadStart(InsertDBMeth)); thread.Start(); } DataSet ds; /// <summary> /// 插入数据库方法 /// </summary> private void InsertDBMeth() { int odr = 0; OracleConnection conn = new OracleConnection(DBHelper.strConnection);//获得conn连接 try { conn.Open(); OracleCommand cmd = conn.CreateCommand(); cmd.CommandText = "Insert into TestInfo values(:CID,:PathSid,:CName,:Sid)"; int dsLength = ds.Tables[0].Rows.Count;//获得Excel中数据长度 for (int i = 1; i < dsLength; i++) { cmd.Parameters.Add("CID", OracleType.Number).Value = ds.Tables[0].Rows[i][0]; cmd.Parameters.Add("PathSid", OracleType.VarChar).Value = ds.Tables[0].Rows[i][1]; cmd.Parameters.Add("CName", OracleType.VarChar).Value = ds.Tables[0].Rows[i][2]; cmd.Parameters.Add("Sid", OracleType.Number).Value = ds.Tables[0].Rows[i][3]; odr = cmd.ExecuteNonQuery();//提交 } //如果查到了数据,才使控制分页按钮生效 if (odr > 0) { MessageBox.Show("插入成功"); } conn.Close(); } catch (Exception ee) { MessageBox.Show(ee.Message.ToString(), "错误", MessageBoxButtons.OK, MessageBoxIcon.Error); } } /// <summary> /// 将Excel保存到DataSet /// </summary> /// <param name="file">Excel表名</param> /// <returns></returns> private static DataSet ImportExcel(string file) { DataSet dsExcel = new DataSet(); FileInfo fileinto = new FileInfo(file); if (!fileinto.Exists) return null; string strConn = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + file + ";Extended Properties='Excel 8.0;HDR=NO;IMEX=1'"; OleDbConnection conn = new OleDbConnection(strConn); try { conn.Open(); string strSql = "select * from [社区表$]"; OleDbDataAdapter da = new OleDbDataAdapter(strSql, conn); da.Fill(dsExcel); } catch (Exception ex) { MessageBox.Show(ex.ToString()); } finally { conn.Close(); } return dsExcel; }
获取Excel文件的第一个表名
/// <summary> /// C#中获取Excel文件的第一个表名 /// Excel文件中第一个表名的缺省值是Sheet1$, 但有时也会被改变为其他名字. 如果需要在C#中使用OleDb读写Excel文件, 就需要知道这个名字是什么. 以下代码就是实现这个功能的: /// </summary> /// <param name="excelFileName"></param> /// <returns></returns> public static string GetExcelFirstTableName(string excelFileName) { string tableName = null; if (File.Exists(excelFileName)) { using (OleDbConnection conn = new OleDbConnection("Provider=Microsoft.Jet." + "OLEDB.4.0;Extended Properties=\"Excel 8.0\";Data Source=" + excelFileName)) { conn.Open(); DataTable dt = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null); tableName = dt.Rows[0][2].ToString().Trim(); } } return tableName; }