这里贴的代码是读取本地文件系统中的Excel
//读取EXCEL的方法 (用范围区域读取数据) PRivate static DataTable GetExcelTableByOleDB(string excelFilePath) { //Excel连接 OleDbConnection conn = null; DataTable dataTable = null; try { //数据表 DataSet ds = new DataSet(); //获取文件扩展名 string extension = System.IO.Path.GetExtension(excelFilePath); string fileName = System.IO.Path.GetfileName(excelFilePath); switch (extension) { //HDR=YES,略过第一行数据;IMEX=1,只读方式打开 case ".xls": conn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + excelFilePath + ";" + "Extended Properties=/"Excel 8.0;HDR=YES;IMEX=1;/""); break; case ".xlsx": conn = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + excelFilePath + ";" + "Extended Properties=/"Excel 12.0;HDR=YES;IMEX=1;/""); break; default: conn = null; break; } if (conn == null) { return null; } conn.Open(); //获取Excel中所有Sheet表的信息 System.Data.DataTable schemaTable = conn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, null); //获取Excel的第一个Sheet表名 string tableName = schemaTable.Rows[0][2].ToString().Trim(); string strSql = "select * from [" + tableName + "]"; //获取Excel指定Sheet表中的信息 OleDbCommand objCmd = new OleDbCommand(strSql, conn); OleDbDataAdapter myData = new OleDbDataAdapter(strSql, conn); myData.Fill(ds, tableName);//填充数据 //dataTable即为excel文件中指定表中存储的信息 dataTable = ds.Tables[tableName]; } catch(Exception e){ ExceptionHelper.throwException(e, "解析excel文件出错"); } finally { if (conn != null) { conn.Close(); } } return dataTable; }新闻热点
疑难解答
