C#读excel写入数据库

xxm_0122 贡献于2012-09-11

作者 Administrator  创建于2011-03-02 04:42:00   修改者Administrator  修改于2011-03-02 04:42:00字数2858

文档摘要:C#读excel写入数据库
关键词:

#region 读取Excel         ///         /// 将 Excel 文件转成 DataTable 后,再把 DataTable中的数据写入表Products         ///         ///         ///         ///         public static int WriteExcelToDataBase(string excelFileName)         {             int rowsCount = 0;             OleDbConnection objConn = new System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + excelFileName+ ";" + "Extended Properties=Excel 8.0;");             objConn.Open();             try             {                 DataTable schemaTable = objConn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, null);                 string sheetName = string.Empty;                 for (int j = 0; j < schemaTable.Rows.Count; j++)                 {                     sheetName = schemaTable.Rows[j][2].ToString().Trim();//获取 Excel 的表名,默认值是sheet1                     DataTable excelDataTable = DbHelp erSQL.ExcelToDataTable(excelFileName, sheetName, true);                     if (excelDataTable.Columns.Count > 1)                     {                           SqlBulkCopy sqlbulkcopy = new SqlBulkCopy(connectionString, SqlBulkCopyOptions.UseInternalTransaction);                             sqlbulkcopy.DestinationTableName = "Products";//数据库中的表名                             sqlbulkcopy.WriteToServer(excelDataTable);                             sqlbulkcopy.Close();                     }                 }             }             catch (SqlException ex)             {                 throw ex;             }             finally             {                 objConn.Close();                 objConn.Dispose();             }             return rowsCount;         }  ///         /// 读取Excel         ///         ///         ///         ///         public static DataSet ExcelToDS(string Path, string sheetName)         {             string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + Path + ";" + "Extended Properties=Excel 8.0;";  //HDR=Yes;             DataSet ds = null;             using (OleDbConnection conn = new OleDbConnection(strConn))             {                 OleDbDataAdapter myCommand = null;                 try                 {                     conn.Open();                     string strExcel = "";                     strExcel = "select * from [" + sheetName + "$]";                     myCommand = new OleDbDataAdapter(strExcel, strConn);                     ds = new DataSet();                     myCommand.Fill(ds, "table1");                 }                 catch (SqlException ex)                 {                     throw ex;                 }                 finally                 {                     myCommand.Dispose();                     conn.Close();                 }                 return ds;             }         }         ///         /// 将 Excel 文件转成 DataTable         ///         /// Excel文件及其路径         /// 工作表名,如:Sheet1         /// True 第一行是标题,False 第一行是数据         /// DataTable         public static DataTable ExcelToDataTable(string serverMapPathExcel, string strSheetName, bool isTitleOrDataOfFirstRow)         {             string HDR = string.Empty;//如果第一行是数据而不是标题的话, 应该写: "HDR=No;"             if (isTitleOrDataOfFirstRow)             {                 HDR = "YES";//第一行是标题             }             else             {                 HDR = "NO";//第一行是数据             }             //源的定义             string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + serverMapPathExcel + ";" + "Extended Properties='Excel 8.0;HDR=" + HDR + ";IMEX=1';";             //Sql语句             //string strExcel = string.Format("select * from [{0}$]", strSheetName); 这是一种方法             string strExcel = "select * from   [" + strSheetName + "]";             //定义存放的数据表             DataSet ds = new DataSet();             //连接数据源             using (OleDbConnection conn = new OleDbConnection(strConn))             {                 try                 {                     conn.Open();                     //适配到数据源                     OleDbDataAdapter adapter = new OleDbDataAdapter(strExcel, strConn);                                          adapter.Fill(ds, strSheetName);                 }                 catch (System.Data.SqlClient.SqlException ex)                 {                     throw ex;                 }                 finally                 {                     conn.Close();                     conn.Dispose();                 }             }             return ds.Tables[strSheetName];         }         #endregion  

下载文档到电脑,查找使用更方便

文档的实际排版效果,会与网站的显示效果略有不同!!

需要 3 金币 [ 分享文档获得金币 ] 0 人已下载

下载文档