using System.Data.OleDb; using System.Data; //////////////////// String sConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" +
"Data Source=" + Server.MapPath("../ExcelData.xls") + ";" + "Extended Properties=Excel 8.0;";//需添加,见后红字 OleDbConnection objConn = new OleDbConnection(sConnectionString); objConn.Open(); OleDbCommand objCmdSelect =new OleDbCommand("SELECT * FROM myRange1", objConn); OleDbDataAdapter objAdapter1 = new OleDbDataAdapter(); objAdapter1.SelectCommand = objCmdSelect; DataSet objDataset1 = new DataSet(); objAdapter1.Fill(objDataset1, "XLData"); DataGrid1.DataSource = objDataset1.Tables[0].DefaultView; DataGrid1.DataBind(); objConn.Close(); //////////////////////////////////////
/// <summary>
/// 加载Excel表到DataTable,跟原始Excel表形式一样,需要筛选自己有用的数据 /// </summary> /// <param name="filename">需要读取的Excel文件路径</param> /// <param name="sheetname">工作表名称</param> /// <returns>DataTable</returns> public static DataTable LoadExcelToDataTable(string filename, string worksheetname) { DataTable table; //连接字符串 String sConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + filename + ";" + "Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=1\""; OleDbConnection myConn = new OleDbConnection(sConnectionString); string strCom = " SELECT * FROM [" + worksheetname + "$]"; myConn.Open(); OleDbDataAdapter myCommand = new OleDbDataAdapter(strCom, myConn); table = new DataTable(); myCommand.Fill(table); myConn.Close(); return table; } //////////////////////// //连接字符串 String sConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + filename + ";" + "Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=1\""; 这样,后边加上"HDR=Yse;IMEX=1",并且这句必须用引号引住,这样就会把混合类型的数据同一当作文本读取,不会出现丢数据的现象。 ======================================
|
|
来自: 悟静 > 《.net和asp.net》