#region 导入Excel
/// <summary>
/// 读取Excel
/// </summary>
/// <param name="Path">带文件名称的Excel路径</param>
/// <returns>返回一个数据集</returns>
public static DataSet ImportExcelToDataSet(string Path)
{
DataSet ds = new DataSet();
try
{
string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + Path + ";" + "Extended Properties=Excel 8.0;";
using (OleDbConnection conn = new OleDbConnection(strConn))
{
Workbook workbook = GetExcelObj(Path);
conn.Open();
foreach (object workbookNew in workbook.Sheets)
{
Worksheet woorksheet = (Worksheet)workbookNew;//(Worksheet)workbook.ActiveSheet;
string strExcel = "select * from [" + woorksheet.Name + "$]";
OleDbDataAdapter myCommand = new OleDbDataAdapter(strExcel, strConn);
myCommand.Fill(ds, woorksheet.Name);
}
}
}
finally
{
GC.Collect();
GC.WaitForPendingFinalizers();
}
return ds;
}
/// <summary>
/// 读取Excel
/// </summary>
/// <param name="Path">带文件名称的Excel路径</param>
/// <returns>返回一个数据表</returns>
public static System.Data.DataTable ImportExcelToDataTable(string Path)
{
System.Data.DataTable dtSource = new System.Data.DataTable();
try
{
string tableName = "";
string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + Path + ";" + "Extended Properties=Excel 8.0;";
using (OleDbConnection conn = new OleDbConnection(strConn))
{
Workbook workbook = GetExcelObj(Path);
Worksheet woorksheet = (Worksheet)workbook.ActiveSheet;
tableName = woorksheet.Name;
workbook.Close(false, null, false);
conn.Open();
string strExcel = "select * from [" + tableName + "$]";
OleDbDataAdapter myCommand = new OleDbDataAdapter(strExcel, strConn);
DataSet ds = new DataSet();
myCommand.Fill(ds, tableName);
dtSource = ds.Tables[tableName];
}
}
catch (OleDbException olex)
{
throw new Exception("该Excel文件正在使用中...未能覆盖,请先关闭目标文件,重新导入!");
}
catch (Exception ex)
{
throw ex;
}
finally
{
GC.Collect();
GC.WaitForPendingFinalizers();
}
return dtSource;
}
#region 私用方法
/// <summary>
/// 获取Excel对象
/// </summary>
private static Workbook GetExcelObj(string Path)
{
ApplicationClass excelApp = new ApplicationClass();
Workbook workbook = excelApp.Workbooks.Open(Path, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
return workbook;
}
#endregion
#endregion