读取excel所有sheet到dataset

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.IO;
using System.Data;
using System.Data.OleDb;
namespace xiaowuTest.Test
{
public partial class ReadExcelSheet : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{

}

#region 获取Excel内容
/// <summary>
/// 获取Excel内容
/// </summary>
/// <param name="sheetName">工作表名称，例：sheet1</param>
/// <param name="filePath">Excel路径</param>
/// <returns></returns>
public static DataSet GetTableFromExcel(string [] sheetNames, string filePath, string where = "")
{
DataSet ds = new DataSet();
string connStrTemplate = string.Empty;
string fileType = System.IO.Path.GetExtension(filePath);
if (string.IsNullOrEmpty(fileType)) return null;
if (filePath == ".xls")
{
connStrTemplate = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + filePath + ";" + ";Extended Properties="Excel 8.0;HDR=YES;IMEX=1"";
}
else
{

connStrTemplate = "Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source=" + filePath + ";" + ";Extended Properties="Excel 12.0;HDR=YES;IMEX=1"";

}
DataTable dt = null;
if (!System.IO.File.Exists(filePath))
{
// don't find file
return null;
}
OleDbConnection conn = new OleDbConnection(string.Format(connStrTemplate, filePath));
try
{
conn.Open();
//if (sheetName == null || sheetName.Trim().Length == 0)
//{
// DataTable schemaTable = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
// sheetName = schemaTable.Rows[0]["TABLE_NAME"].ToString().Trim();
//}

//add/2020/8/12
OleDbDataAdapter da = null;

foreach (var item in sheetNames)
{
string strSQL = "Select * From [" + item + "$]";
if (!string.IsNullOrEmpty(where))
{
strSQL = string.Format("Select * From [" + item + "] Where {0}", where);
}
try
{
da = new OleDbDataAdapter(strSQL, conn);
da.Fill(ds);
}
catch (Exception er)
{
da = new OleDbDataAdapter("Select * From [sheet1$]", conn);
da.Fill(ds);
}
}

}
catch (Exception ex)
{
throw ex;
}
finally
{
conn.Close();
}

return ds;
}
#endregion

#region 获取sheet名
/// <summary>
/// 获取sheet名
/// </summary>
/// <param name="excelFile">Excel文件名及路径</param>
/// <returns></returns>
public static string[] GetExcelSheetNames(string fileName)
{
OleDbConnection objConn = null;
System.Data.DataTable dt = null;
try
{
string connString = string.Empty;
string FileType = fileName.Substring(fileName.LastIndexOf("."));
if (FileType == ".xls")
connString = "Provider=Microsoft.Jet.OLEDB.4.0;" +
"Data Source=" + fileName + ";Extended Properties=Excel 8.0;";
else//.xlsx
connString = "Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source=" + fileName + ";" + ";Extended Properties="Excel 12.0;HDR=YES;IMEX=1"";
// 创建连接对象
objConn = new OleDbConnection(connString);
// 打开数据库连接
objConn.Open();
// 得到包含数据架构的数据表
dt = objConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
if (dt == null)
{
return null;
}
String[] excelSheets = new String[dt.Rows.Count];
int i = 0;
// 添加工作表名称到字符串数组
foreach (DataRow row in dt.Rows)
{
string strSheetTableName = row["TABLE_NAME"].ToString();
//过滤无效SheetName
if (strSheetTableName.Contains("$") && strSheetTableName.Replace("'", "").EndsWith("$"))
{
excelSheets[i] = strSheetTableName.Substring(0, strSheetTableName.Length - 1);
}
i++;
}
return excelSheets;
}
catch (Exception ex)
{
return null;
}
finally
{
// 清理
if (objConn != null)
{
objConn.Close();
objConn.Dispose();
}
if (dt != null)
{
dt.Dispose();
}
}
}

#endregion

protected void Button1_Click(object sender, EventArgs e)
{
string excelurl = Server.MapPath("~/FileRoot/") + "用户资费模板.xls";
string[] urls = GetExcelSheetNames(excelurl);
DataSet dsinfo = GetTableFromExcel(urls, excelurl, "");
}
}
}