#region 导入1（Interop）
/// <summary>
/// 导入1（Interop）
/// </summary>
/// <param name="filePath"></param>
/// <returns></returns>
public static DataSet Interop(string filePath)
{
DataSet ds = null;
DataTable dt = null;
Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbook workbook = null;
Microsoft.Office.Interop.Excel.Worksheet worksheet = null;
Microsoft.Office.Interop.Excel.Sheets sheets = null;
Microsoft.Office.Interop.Excel.Range range = null;
object missing = System.Reflection.Missing.Value;
try
{
if (excel == null)
{
return null;
}
//打开Excel文件
workbook = excel.Workbooks.Open(
filePath, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing);
//获取所有的sheet表
sheets = workbook.Worksheets;
ds = new DataSet();
for (int i = 1; i <= sheets.Count; i++)
{
//获取第一个表
worksheet = (Microsoft.Office.Interop.Excel.Worksheet)sheets.get_Item(i);
int rowCount = worksheet.UsedRange.Rows.Count;
int colCount = worksheet.UsedRange.Columns.Count;
int rowIndex = 1;
int colIndex = 1;
DataColumn dc;
dt = new DataTable();
dt.TableName = "table" + i.ToString();
//读取列名
for (int j = 0; j < colCount; j++)
{
range = worksheet.Cells[rowIndex, colIndex + j];
dc = new DataColumn();
dc.DataType = Type.GetType("System.String");
dc.ColumnName = range.Text.ToString().Trim();
//添加列
dt.Columns.Add(dc);
}
//读取行数据
for (int k = 1; k < rowCount; k++)
{
DataRow dr = dt.NewRow();
for (int l = 0; l < colCount; l++)
{
range = worksheet.Cells[rowIndex + k, colIndex + l];
dr[l] = range.Text.ToString();
}
dt.Rows.Add(dr);
}
ds.Tables.Add(dt);
}
}
catch (Exception ex)
{
throw;
}
finally
{
workbook.Close();
excel.Quit();
Marshal.ReleaseComObject(worksheet);
Marshal.ReleaseComObject(workbook);
Marshal.ReleaseComObject(excel);
worksheet = null;
workbook = null;
excel = null;
GC.Collect();
}
return ds;
}
#endregion
#region 导入2(Oledb)
/// <summary>
/// 导入2(Oledb)
/// </summary>
/// <param name="filePath"></param>
/// <returns></returns>
public static DataSet Oledb(string filePath)
{
DataSet ds = null;
OleDbConnection conn;
string strConn = string.Empty;
string sheetName = string.Empty;
try
{
// Excel 2003 版本连接字符串
strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath + ";Extended Properties='Excel 8.0; HDR=YES; IMEX=1;'";
conn = new OleDbConnection(strConn);
conn.Open();
}
catch
{
// Excel 2007 以上版本连接字符串
strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filePath + ";Extended Properties='Excel 12.0;HDR=Yes;IMEX=1;'";
conn = new OleDbConnection(strConn);
conn.Open();
}
//获取所有的 sheet 表
DataTable dtSheetName = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "Table" });
ds = new DataSet();
for (int i = 0; i < dtSheetName.Rows.Count; i++)
{
DataTable dt = new DataTable();
dt.TableName = "table" + i.ToString();
//获取表名
sheetName = dtSheetName.Rows[i]["TABLE_NAME"].ToString();
OleDbDataAdapter oleda = new OleDbDataAdapter("select * from [" + sheetName + "]", conn);
oleda.Fill(dt);
ds.Tables.Add(dt);
}
//关闭连接，释放资源
conn.Close();
conn.Dispose();
return ds;
}
#endregion
#region 导入3(NPOI)
/// <summary>
/// 导入3(NPOI)
/// </summary>
/// <param name="filePath"></param>
/// <returns></returns>
public static DataSet NPOI(string filePath)
{
DataSet ds = null;
try
{
FileStream fileStream = new FileStream(filePath, FileMode.Open);
XSSFWorkbook workbook = new XSSFWorkbook(fileStream);
ISheet sheet = null;
IRow row = null;
ds = new DataSet();
DataTable dt = null;
for (int i = 0; i < workbook.Count; i++)
{
dt = new DataTable();
dt.TableName = "table" + i.ToString();
//获取sheet表
sheet = workbook.GetSheetAt(i);
//起始行索引
int rowIndex = sheet.FirstRowNum;
//获取行数
int rowCount = sheet.LastRowNum;
//获取第一行
IRow firstRow = sheet.GetRow(rowIndex);
//起始列索引
int colIndex = firstRow.FirstCellNum;
//获取列数
int colCount = firstRow.LastCellNum;
DataColumn dc = null;
//获取列数
for (int j = colIndex; j < colCount; j++)
{
dc = new DataColumn(firstRow.GetCell(j).StringCellValue);
dt.Columns.Add(dc);
}
//跳过第一行列名
rowIndex++;
for (int k = rowIndex; k <= rowCount; k++)
{
DataRow dr = dt.NewRow();
row = sheet.GetRow(k);
for (int l = colIndex; l < colCount; l++)
{
if (row.GetCell(l) == null)
{
continue;
}
dr[l] = row.GetCell(l).StringCellValue;
}
dt.Rows.Add(dr);
}
ds.Tables.Add(dt);
}
sheet = null;
workbook = null;
fileStream.Close();
fileStream.Dispose();
}
catch (Exception ex)
{
throw;
}
return ds;
}
#endregion
#region 导入4(Aspose)
/// <summary>
/// 导入4(Aspose)
/// </summary>
/// <param name="filePath"></param>
/// <returns></returns>
public static DataSet Aspose(string filePath)
{
DataSet ds = null;
try
{
Aspose.Cells.Workbook workbook = new Aspose.Cells.Workbook(filePath);
ds = new DataSet();
DataTable dt = null;
int rowIndex = 0;
int colIndex = 0;
for (int i = 0; i < workbook.Worksheets.Count; i++)
{
dt = new DataTable();
dt.TableName = "table" + i.ToString();
Aspose.Cells.Worksheet worksheet = workbook.Worksheets[i];
//获取每个sheet表的所有单元格
Aspose.Cells.Cells cells = worksheet.Cells;
dt = cells.ExportDataTableAsString(rowIndex, colIndex, cells.MaxDataRow + 1, cells.MaxDataColumn + 1, true);
ds.Tables.Add(dt);
}
}
catch (Exception ex)
{
throw;
}
return ds;
}
#endregion
#region 导入5(EPPlus)
/// <summary>
/// 导入5(EPPlus)
/// </summary>
/// <param name="filePath"></param>
/// <returns></returns>
public static DataSet EPPlus(string filePath)
{
DataSet ds = null;
try
{
FileStream fileStream = new FileStream(filePath, FileMode.Open,FileAccess.Read, FileShare.ReadWrite);
ExcelPackage package = new ExcelPackage(fileStream);
ExcelPackage.LicenseContext = LicenseContext.NonCommercial;
ExcelWorksheets worksheets = package.Workbook.Worksheets;
ExcelWorksheet worksheet = null;
ds = new DataSet();
DataTable dt = null;
for (int i = 0; i < worksheets.Count; i++)
{
dt = new DataTable();
dt.TableName = "table" + i.ToString();
worksheet = worksheets[i];
int rowCount = worksheet.Dimension.End.Row;
int colCount = worksheet.Dimension.End.Column;
int rowIndex = worksheet.Dimension.Start.Row;
int colIndex = worksheet.Dimension.Start.Column;
DataColumn dc = null;
for (int j = colIndex; j <= colCount; j++)
{
dc = new DataColumn(worksheet.Cells[rowIndex, j].Value.ToString());
dt.Columns.Add(dc);
}
rowIndex++;
for (int k = rowIndex; k <= rowCount; k++)
{
DataRow dr = dt.NewRow();
for (int l = colIndex; l <= colCount; l++)
{
if (worksheet.GetValue(k, l) == null)
{
continue;
}
dr[l - 1] = worksheet.GetValue(k, l).ToString();
}
dt.Rows.Add(dr);
}
ds.Tables.Add(dt);
}
package.Dispose();
worksheet = null;
worksheets = null;
package = null;
fileStream.Close();
fileStream.Dispose();
}
catch(Exception ex)
{
throw;
}
return ds;
}
#endregion
