oledb 读取excel 的某个sheet,但是不知道sheet名 using System.Data.OleDb; ///
/// read excel /// ///
dataset
private System.Data.DataTable ReadExcelToDataTable(string strfilePath) { string uploadPathPrex = ConfigurationManager.AppSettings["uploadFilePath"]; string strConn; strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + strfilePath + ";Extended Properties='Excel 8.0;HDR=False;IMEX=1'"; using(OleDbConnection OleConn = new OleDbConnection(strConn)) { try { OleConn.Open(); //return Excel's schema,contains all sheets name,type,create date ,modify date etc.. System.Data.DataTable dtSheetName = OleConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "Table" }); OleDbCommand cmd = new OleDbCommand("SELECT * FROM [" + dtSheetName.Rows[0][2] + "]", OleConn); cmd.CommandType = CommandType.Text; OleDbDataAdapter OleDaExcel = new OleDbDataAdapter(cmd); DataSet OleDsExcle = new DataSet(); OleDaExcel.Fill(OleDsExcle, "AssociateWiseSummary"); //hard code,项目特定格式,这样可以过滤掉excel顶头的两行 return GetNewDataTable(OleDsExcle.Tables[0], "Len(trim(F1))=6"); } catch (Exception ex) { Label1.Text += ShowErrorMsg(ex.Message); return null; } } } private DataTable GetNewDataTable(DataTable dt, string condition) { DataTable ndt = new DataTable(); ndt = dt.Clone(); DataRow[] dr = dt.Select(condition); for (int i = 0; i <= dr.Length - 1; i++) { ndt.ImportRow((DataRow)dr[i]); } return ndt; }
oledb 读取excel 不知名sheet
最新推荐文章于 2019-01-14 08:30:00 发布