excel导入类

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.IO;
using NPOI.SS.UserModel;
using NPOI.HSSF.UserModel;
using System.Data;

 

namespace Import.ExcelFileHelper
{
/// <summary>
/// Exce文件内容转DataTable
/// </summary>
internal class ExcelHelper
{
/// <summary>
/// 读取excel文件数据内容
/// </summary>
/// <param name="filename">excel文件名</param>
/// <returns>数据结果集</returns>
internal DataTable ExcelToDataTable(string filename)
{
return ExcelToDataTable(filename, 0);
}
/// <summary>
/// 读取excel文件数据内容
/// </summary>
/// <param name="filename">excel文件名</param>
/// <param name="sheetname">sheet页名称</param>
/// <returns>数据结果集</returns>
internal DataTable ExcelToDataTable(string filename, string sheetname)
{
using (FileStream excelFileStream = new FileStream(filename, FileMode.Open))
{
IWorkbook workbook = WorkbookFactory.Create(excelFileStream);//创建输入流
ISheet sheet = workbook.GetSheet(sheetname);//获取sheet页
DataTable table = new DataTable();//实例化一张表格
IRow headerRow = sheet.GetRow(0);///获取sheet的首行
int cellCount = headerRow.LastCellNum; //最后一个方格的编号 即总的列数
int rowCount = sheet.LastRowNum;//最后一行的标号 即总的行数
for (int i = headerRow.FirstCellNum; i < cellCount; i++)
{
DataColumn column = new DataColumn(headerRow.GetCell(i).StringCellValue);
table.Columns.Add(column);
}
for (int i = (sheet.FirstRowNum + 1); i < rowCount; i++)
{
IRow row = sheet.GetRow(i);
DataRow dataRow = table.NewRow();
if (row != null)
{
for (int j = row.FirstCellNum; j > -1 && j < cellCount; j++)
{
if (row.GetCell(j) != null)
dataRow[j] = row.GetCell(j).ToString();
}
}
table.Rows.Add(dataRow);
}
return table;
}
}
/// <summary>
/// 读取excel文件数据内容
/// </summary>
/// <param name="filename">excel文件名</param>
/// <param name="sheetindex">sheet页索引</param>
/// <returns>数据结果集</returns>
internal DataTable ExcelToDataTable(string filename, int sheetindex)
{
using (FileStream excelFileStream = new FileStream(filename, FileMode.Open))
{
IWorkbook workbook = WorkbookFactory.Create(excelFileStream);//创建输入流
ISheet sheet = workbook.GetSheetAt(sheetindex);//获取sheet页索引
DataTable table = new DataTable();
IRow headerRow = sheet.GetRow(0);//第一行为标题行
int cellCount = headerRow.LastCellNum;//获取列头行最后单元格
int rowCount = sheet.LastRowNum;
for (int i = headerRow.FirstCellNum; i < cellCount; i++)
{
DataColumn column = new DataColumn(headerRow.GetCell(i).StringCellValue);
table.Columns.Add(column);
}
for (int i = (sheet.FirstRowNum + 1); i < rowCount; i++)
{
IRow row = sheet.GetRow(i);
DataRow dataRow = table.NewRow();
if (row != null)
{
for (int j = row.FirstCellNum; j > -1 && j < cellCount; j++)
{
if (row.GetCell(j) != null)
dataRow[j] = row.GetCell(j).ToString();
}
}
table.Rows.Add(dataRow);
}
return table;
}
}