/// <summary>
/// 从excel中得到数据返回dataset
/// </summary>
/// <param name="filePath_Name"></param>
/// <param name="pageName"></param>
/// <returns></returns>
public static DataSet getDataFromExcel(string filePath_Name, string pageName)
{
DataSet OleDsExcle = null;
string strConn = string.Empty;
OleDbConnection OleConn = null;
var fileType = Path.GetExtension(filePath_Name.ToLower()).Trim();
if (fileType == ".xlsx")
{
strConn = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=Excel 12.0;", filePath_Name);
}
else if(fileType == ".xls")
{
strConn = string.Format(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source = {0};Extended Properties='Excel 8.0;HDR=False;IMEX=1'", filePath_Name);
}
try
{
OleConn = new OleDbConnection(strConn);
OleConn.Open();
string sql = string.Format("SELECT * FROM [{0}$]", pageName); //可是更改Sheet名称,比如sheet2,等等
OleDbDataAdapter OleDaExcel = new OleDbDataAdapter(sql, OleConn);
OleDsExcle = new DataSet();
OleDaExcel.Fill(OleDsExcle, pageName);
OleConn.Close();
}
catch (Exception ex)
{
MessageBox.Show("读取Excel文件失败!请确认Excel文件是否\n能打开或已经处于打开状态!", "提示");
string errorMsg = ex.Message;
}
return OleDsExcle;
}