Excel中的數據讀取到內存

悟靜 2013-03-03

展開全文

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"，并且這句必須用引號引住，這樣就會把混合類型的數據同一當作文本讀取，不會出現丟數據的現象。

======================================