本文主要提供了一个利用NPOI读取一个Excel的实际值的类。本文使用的NPOI版本为1.2.5,NPOI.dll可以到NPOI的官网http://npoi.codeplex.com/下载。如果有写得不当的地方,希望能得到大家的斧正。
![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.IO;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
namespace Common.Excel
{
public static class NPOIHandler
{
public static DataSet ExcelToDataSet( string excelPath)
{
return ExcelToDataSet(excelPath, true);
}
public static DataSet ExcelToDataSet( string excelPath, bool firstRowAsHeader)
{
int sheetCount;
return ExcelToDataSet(excelPath, firstRowAsHeader, out sheetCount);
}
public static DataSet ExcelToDataSet( string excelPath, bool firstRowAsHeader, out int sheetCount)
{
using (DataSet ds = new DataSet())
{
using (FileStream fileStream = new FileStream(excelPath, FileMode.Open, FileAccess.Read))
{
HSSFWorkbook workbook = new HSSFWorkbook(fileStream);
HSSFFormulaEvaluator evaluator = new HSSFFormulaEvaluator(workbook);
sheetCount = workbook.NumberOfSheets;
for ( int i = 0; i < sheetCount; ++i)
{
HSSFSheet sheet = workbook.GetSheetAt(i) as HSSFSheet;
DataTable dt = ExcelToDataTable(sheet, evaluator, firstRowAsHeader);
ds.Tables.Add(dt);
}
return ds;
}
}
}
public static DataTable ExcelToDataTable( string excelPath, string sheetName)
{
return ExcelToDataTable(excelPath, sheetName, true);
}
public static DataTable ExcelToDataTable( string excelPath, string sheetName, bool firstRowAsHeader)
{
using (FileStream fileStream = new FileStream(excelPath, FileMode.Open, FileAccess.Read))
{
HSSFWorkbook workbook = new HSSFWorkbook(fileStream);
HSSFFormulaEvaluator evaluator = new HSSFFormulaEvaluator(workbook);
HSSFSheet sheet = workbook.GetSheet(sheetName) as HSSFSheet;
return ExcelToDataTable(sheet, evaluator, firstRowAsHeader);
}
}
private static DataTable ExcelToDataTable(HSSFSheet sheet, HSSFFormulaEvaluator evaluator, bool firstRowAsHeader)
{
if (firstRowAsHeader)
{
return ExcelToDataTableFirstRowAsHeader(sheet, evaluator);
}
else
{
return ExcelToDataTable(sheet, evaluator);
}
}
private static DataTable ExcelToDataTableFirstRowAsHeader(HSSFSheet sheet, HSSFFormulaEvaluator evaluator)
{
using (DataTable dt = new DataTable())
{
HSSFRow firstRow = sheet.GetRow( 0) as HSSFRow;
int cellCount = GetCellCount(sheet);
for ( int i = 0; i < cellCount; i++)
{
if (firstRow.GetCell(i) != null)
{
dt.Columns.Add(firstRow.GetCell(i).StringCellValue ?? string.Format( " F{0} ", i + 1), typeof( string));
}
else
{
dt.Columns.Add( string.Format( " F{0} ", i + 1), typeof( string));
}
}
for ( int i = 1; i <= sheet.LastRowNum; i++)
{
HSSFRow row = sheet.GetRow(i) as HSSFRow;
DataRow dr = dt.NewRow();
FillDataRowByHSSFRow(row, evaluator, ref dr);
dt.Rows.Add(dr);
}
dt.TableName = sheet.SheetName;
return dt;
}
}
private static DataTable ExcelToDataTable(HSSFSheet sheet, HSSFFormulaEvaluator evaluator)
{
using (DataTable dt = new DataTable())
{
if (sheet.LastRowNum != 0)
{
int cellCount = GetCellCount(sheet);
for ( int i = 0; i < cellCount; i++)
{
dt.Columns.Add( string.Format( " F{0} ", i), typeof( string));
}
for ( int i = 0; i < sheet.FirstRowNum; ++i)
{
DataRow dr = dt.NewRow();
dt.Rows.Add(dr);
}
for ( int i = sheet.FirstRowNum; i <= sheet.LastRowNum; i++)
{
HSSFRow row = sheet.GetRow(i) as HSSFRow;
DataRow dr = dt.NewRow();
FillDataRowByHSSFRow(row, evaluator, ref dr);
dt.Rows.Add(dr);
}
}
dt.TableName = sheet.SheetName;
return dt;
}
}
private static void FillDataRowByHSSFRow(HSSFRow row, HSSFFormulaEvaluator evaluator, ref DataRow dr)
{
if (row != null)
{
for ( int j = 0; j < dr.Table.Columns.Count; j++)
{
HSSFCell cell = row.GetCell(j) as HSSFCell;
if (cell != null)
{
switch (cell.CellType)
{
case CellType.BLANK:
dr[j] = DBNull.Value;
break;
case CellType.BOOLEAN:
dr[j] = cell.BooleanCellValue;
break;
case CellType.NUMERIC:
if (DateUtil.IsCellDateFormatted(cell))
{
dr[j] = cell.DateCellValue;
}
else
{
dr[j] = cell.NumericCellValue;
}
break;
case CellType.STRING:
dr[j] = cell.StringCellValue;
break;
case CellType.ERROR:
dr[j] = cell.ErrorCellValue;
break;
case CellType.FORMULA:
cell = evaluator.EvaluateInCell(cell) as HSSFCell;
dr[j] = cell.ToString();
break;
default:
throw new NotSupportedException( string.Format( " Catched unhandle CellType[{0}] ", cell.CellType));
}
}
}
}
}
private static int GetCellCount(HSSFSheet sheet)
{
int firstRowNum = sheet.FirstRowNum;
int cellCount = 0;
for ( int i = sheet.FirstRowNum; i <= sheet.LastRowNum; ++i)
{
HSSFRow row = sheet.GetRow(i) as HSSFRow;
if (row != null && row.LastCellNum > cellCount)
{
cellCount = row.LastCellNum;
}
}
return cellCount;
}
}
}
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.IO;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
namespace Common.Excel
{
public static class NPOIHandler
{
public static DataSet ExcelToDataSet( string excelPath)
{
return ExcelToDataSet(excelPath, true);
}
public static DataSet ExcelToDataSet( string excelPath, bool firstRowAsHeader)
{
int sheetCount;
return ExcelToDataSet(excelPath, firstRowAsHeader, out sheetCount);
}
public static DataSet ExcelToDataSet( string excelPath, bool firstRowAsHeader, out int sheetCount)
{
using (DataSet ds = new DataSet())
{
using (FileStream fileStream = new FileStream(excelPath, FileMode.Open, FileAccess.Read))
{
HSSFWorkbook workbook = new HSSFWorkbook(fileStream);
HSSFFormulaEvaluator evaluator = new HSSFFormulaEvaluator(workbook);
sheetCount = workbook.NumberOfSheets;
for ( int i = 0; i < sheetCount; ++i)
{
HSSFSheet sheet = workbook.GetSheetAt(i) as HSSFSheet;
DataTable dt = ExcelToDataTable(sheet, evaluator, firstRowAsHeader);
ds.Tables.Add(dt);
}
return ds;
}
}
}
public static DataTable ExcelToDataTable( string excelPath, string sheetName)
{
return ExcelToDataTable(excelPath, sheetName, true);
}
public static DataTable ExcelToDataTable( string excelPath, string sheetName, bool firstRowAsHeader)
{
using (FileStream fileStream = new FileStream(excelPath, FileMode.Open, FileAccess.Read))
{
HSSFWorkbook workbook = new HSSFWorkbook(fileStream);
HSSFFormulaEvaluator evaluator = new HSSFFormulaEvaluator(workbook);
HSSFSheet sheet = workbook.GetSheet(sheetName) as HSSFSheet;
return ExcelToDataTable(sheet, evaluator, firstRowAsHeader);
}
}
private static DataTable ExcelToDataTable(HSSFSheet sheet, HSSFFormulaEvaluator evaluator, bool firstRowAsHeader)
{
if (firstRowAsHeader)
{
return ExcelToDataTableFirstRowAsHeader(sheet, evaluator);
}
else
{
return ExcelToDataTable(sheet, evaluator);
}
}
private static DataTable ExcelToDataTableFirstRowAsHeader(HSSFSheet sheet, HSSFFormulaEvaluator evaluator)
{
using (DataTable dt = new DataTable())
{
HSSFRow firstRow = sheet.GetRow( 0) as HSSFRow;
int cellCount = GetCellCount(sheet);
for ( int i = 0; i < cellCount; i++)
{
if (firstRow.GetCell(i) != null)
{
dt.Columns.Add(firstRow.GetCell(i).StringCellValue ?? string.Format( " F{0} ", i + 1), typeof( string));
}
else
{
dt.Columns.Add( string.Format( " F{0} ", i + 1), typeof( string));
}
}
for ( int i = 1; i <= sheet.LastRowNum; i++)
{
HSSFRow row = sheet.GetRow(i) as HSSFRow;
DataRow dr = dt.NewRow();
FillDataRowByHSSFRow(row, evaluator, ref dr);
dt.Rows.Add(dr);
}
dt.TableName = sheet.SheetName;
return dt;
}
}
private static DataTable ExcelToDataTable(HSSFSheet sheet, HSSFFormulaEvaluator evaluator)
{
using (DataTable dt = new DataTable())
{
if (sheet.LastRowNum != 0)
{
int cellCount = GetCellCount(sheet);
for ( int i = 0; i < cellCount; i++)
{
dt.Columns.Add( string.Format( " F{0} ", i), typeof( string));
}
for ( int i = 0; i < sheet.FirstRowNum; ++i)
{
DataRow dr = dt.NewRow();
dt.Rows.Add(dr);
}
for ( int i = sheet.FirstRowNum; i <= sheet.LastRowNum; i++)
{
HSSFRow row = sheet.GetRow(i) as HSSFRow;
DataRow dr = dt.NewRow();
FillDataRowByHSSFRow(row, evaluator, ref dr);
dt.Rows.Add(dr);
}
}
dt.TableName = sheet.SheetName;
return dt;
}
}
private static void FillDataRowByHSSFRow(HSSFRow row, HSSFFormulaEvaluator evaluator, ref DataRow dr)
{
if (row != null)
{
for ( int j = 0; j < dr.Table.Columns.Count; j++)
{
HSSFCell cell = row.GetCell(j) as HSSFCell;
if (cell != null)
{
switch (cell.CellType)
{
case CellType.BLANK:
dr[j] = DBNull.Value;
break;
case CellType.BOOLEAN:
dr[j] = cell.BooleanCellValue;
break;
case CellType.NUMERIC:
if (DateUtil.IsCellDateFormatted(cell))
{
dr[j] = cell.DateCellValue;
}
else
{
dr[j] = cell.NumericCellValue;
}
break;
case CellType.STRING:
dr[j] = cell.StringCellValue;
break;
case CellType.ERROR:
dr[j] = cell.ErrorCellValue;
break;
case CellType.FORMULA:
cell = evaluator.EvaluateInCell(cell) as HSSFCell;
dr[j] = cell.ToString();
break;
default:
throw new NotSupportedException( string.Format( " Catched unhandle CellType[{0}] ", cell.CellType));
}
}
}
}
}
private static int GetCellCount(HSSFSheet sheet)
{
int firstRowNum = sheet.FirstRowNum;
int cellCount = 0;
for ( int i = sheet.FirstRowNum; i <= sheet.LastRowNum; ++i)
{
HSSFRow row = sheet.GetRow(i) as HSSFRow;
if (row != null && row.LastCellNum > cellCount)
{
cellCount = row.LastCellNum;
}
}
return cellCount;
}
}
}