写在前面
NPOI是POI项目的.NET迁移版本。POI是一个开源的Java 读写 Excel、Word 等微软Ole2组件文档的项目；使用NPOI可以在没有安装Office或者相应环境的机器上对Word或Excel文档进行读写操作。
NPOI类库中操作EXCEL有两个模块分别是：
1️.HSSF模块，操作拓展名为.xls的Excel，对应Excel2003及以前的版本。
2️.XSSF模块，操作拓展名为.xlsx的Excel，对应Excel2007及以后的版本，可向下兼容xls，故本例使用XSSF下的XSSFWorkbook来操作。
通过NuGet获取NPOI
需要引用的命名空间如下：
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using System.Collections;
using System.Data;
代码实现
public class ExcelProcessor
{
#region 私有变量
private int _perSheetCount = 40000;//每个sheet要保存的条数
private string _filePath;
private IWorkbook _currentWorkbook;
private List<string> _sheets;
private Dictionary<string, DataTable> _dataDict;
#endregion
#region 属性
public List<string> Sheets
{
get { return _sheets ?? (_sheets = GetSheets(_filePath)); }
}
#endregion
#region 构造器
/// <summary>
/// 默认构造器
/// </summary>
/// <param name="filePath"></param>
public ExcelProcessor(string filePath)
{
_filePath = filePath;
_dataDict = new Dictionary<string, DataTable>();
}
/// <summary>
/// 加载数据
/// </summary>
public bool LoadData()
{
try
{
using (var fs = new FileStream(_filePath, FileMode.OpenOrCreate, FileAccess.Read))
{
_currentWorkbook = new XSSFWorkbook(fs);
}
return true;
}
catch (Exception ex)
{
return false;
}
}
/// <summary>
/// 最大接收5万条每页，大于5万时，使用系统默认的值(4万)
/// </summary>
/// <param name="perSheetCounts"></param>
public ExcelProcessor(int perSheetCounts)
{
if (_perSheetCount <= 50000)
_perSheetCount = perSheetCounts;
}
#endregion
#region 公有方法
public List<string> GetSheets(string fileName)
{
var sheets = new List<string>();
if (_currentWorkbook == null)
return sheets;
for (int i = 0; i < _currentWorkbook.NumberOfSheets; i++)
{
sheets.Add(_currentWorkbook.GetSheetName(i));
}
return sheets;
}
public object GetNumericCellValue(string sheetName, int rowIndex, int colIndex)
{
if (!Sheets.Contains(sheetName))
return null;
var cell = _currentWorkbook.GetSheet(sheetName).GetRow(rowIndex).GetCell(colIndex - 1);
return cell.NumericCellValue;
}
public object GetStringCellValue(string sheetName, int rowIndex, int colIndex)
{
if (!Sheets.Contains(sheetName))
return null;
var cell = _currentWorkbook.GetSheet(sheetName).GetRow(rowIndex).GetCell(colIndex - 1);
if (cell == null)
return null;
if (cell.CellType == CellType.Formula)
{
if (cell.CachedFormulaResultType == CellType.String)
return cell.StringCellValue;
if (cell.CachedFormulaResultType == CellType.Numeric)
return cell.NumericCellValue;
if (cell.CachedFormulaResultType == CellType.Boolean)
return cell.BooleanCellValue;
return null;
}
if (cell.CellType == CellType.Numeric)
return cell.NumericCellValue;
if (cell.CellType == CellType.Boolean)
return cell.NumericCellValue;
return cell.StringCellValue;
}
public object GetDateTimeCellValue(string sheetName, int rowIndex, int colIndex)
{
if (!Sheets.Contains(sheetName))
return null;
var cell = _currentWorkbook.GetSheet(sheetName).GetRow(rowIndex).GetCell(colIndex - 1);
if (cell.CellType == CellType.String)
return cell.StringCellValue;
return cell.DateCellValue;
}
public ICell GetCell(string sheetName, int rowIndex, int colIndex)
{
if (!Sheets.Contains(sheetName))
return null;
var sheet = _currentWorkbook.GetSheet(sheetName);
if (sheet == null)
return null;
var row = sheet.GetRow(rowIndex);
if (row == null)
return null;
var cell = row.GetCell(colIndex - 1);
if (cell == null)
return null;
return cell;
}
/// <summary>
/// 获取单元格里面的值
/// </summary>
/// <param name="sheetName">表名</param>
/// <param name="x">行索引从1开始</param>
/// <param name="y">列索引从1开始</param>
/// <returns></returns>
public object GetCellValue(string sheetName, int rowIndex, int colIndex)
{
if (!Sheets.Contains(sheetName))
return null;
DataTable dt = null;
if (!_dataDict.ContainsKey(sheetName))
{
dt = Import(sheetName);
_dataDict.Add(sheetName, dt);
}
else
{
dt = _dataDict[sheetName];
}
if (dt == null)
return null;
if (dt.Rows.Count < rowIndex)
return null;
var rowIdx = rowIndex - 1;
var row = dt.Rows[rowIdx];
var colIdx = colIndex - 1;
return row[colIdx];
}
public void SetCellValues(ICell cell, string cellType, string cellValue)
{
switch (cellType)
{
case "System.String": //字符串类型
double result;
if (double.TryParse(cellValue, out result))
cell.SetCellValue(result);
else
cell.SetCellValue(cellValue);
break;
case "System.DateTime": //日期类型
DateTime dateV;
DateTime.TryParse(cellValue, out dateV);
cell.SetCellValue(dateV);
break;
case "System.Boolean": //布尔型
bool boolV = false;
bool.TryParse(cellValue, out boolV);
cell.SetCellValue(boolV);
break;
case "System.Int16": //整型
case "System.Int32":
case "System.Int64":
case "System.Byte":
int intV = 0;
int.TryParse(cellValue, out intV);
cell.SetCellValue(intV);
break;
case "System.Decimal": //浮点型
case "System.Double":
double doubV = 0;
double.TryParse(cellValue, out doubV);
cell.SetCellValue(doubV);
break;
case "System.DBNull": //空值处理
cell.SetCellValue("");
break;
default:
cell.SetCellValue("");
break;
}
}
public DataTable Import(string sheetName)
{
sheetName = string.IsNullOrEmpty(sheetName) ? "Sheet1" : sheetName;
ISheet sheet = _currentWorkbook.GetSheet(sheetName);
if (sheet == null)
{
sheet = _currentWorkbook.GetSheetAt(0);
}
IEnumerator ie = sheet.GetRowEnumerator();
IRow row = null;
var maxCol = 0;
while (ie.MoveNext())
{
row = ie.Current as IRow;//取一行,为了得到column的总数
if (row.LastCellNum > maxCol)
maxCol = row.LastCellNum;
}
var dt = new DataTable();
for (int i = 0; i < maxCol; i++)
{
dt.Columns.Add(string.Format("Col{0}", i));
}
ie.Reset();
DataRow drow = null;
ICell cell = null;
var isHeader = true;
while (ie.MoveNext())
{
if (isHeader)
{
isHeader = false;
continue;
}
row = ie.Current as IRow;
drow = dt.NewRow();
for (int i = 0; i < row.LastCellNum; i++)
{
if (row.GetCell(i) == null)
{
drow[i] = null;
continue;
}
cell = row.GetCell(i) as ICell;
switch (cell.CellType)
{
case CellType.Blank:
drow[i] = string.Empty;
break;
case CellType.Boolean:
drow[i] = cell.BooleanCellValue;
break;
case CellType.Error:
drow[i] = cell.ErrorCellValue;
break;
case CellType.Formula:
drow[i] = "=" + cell.CellFormula;
break;
case CellType.Numeric:
if (DateUtil.IsCellDateFormatted(cell))
{
drow[i] = cell.DateCellValue;
}
else
{
drow[i] = cell.NumericCellValue;
}
break;
case CellType.String:
drow[i] = cell.StringCellValue;
break;
case CellType.Unknown:
break;
default:
drow[i] = null;
break;
}
}
dt.Rows.Add(drow);
}
return dt;
}
public string Export(string excelFileName, List<DataTable> dataTables)
{
var workbook = new HSSFWorkbook();
ISheet sheet = null;
IRow row = null;
ICell cell = null;
var index = 0;
foreach (var dataTable in dataTables)
{
var tableName = dataTable.TableName;
if (string.IsNullOrEmpty(tableName))
tableName = "Sheet" + (++index);
sheet = workbook.CreateSheet(tableName);
//填充表头
row = sheet.CreateRow(0);
for (int i = 0; i < dataTable.Columns.Count; i++)
{
cell = row.CreateCell(i);
cell.SetCellValue(dataTable.Columns[i].ColumnName);
}
//填充内容
for (int i = 0; i < dataTable.Rows.Count; i++)
{
row = sheet.CreateRow(i + 1);
for (int j = 0; j < dataTable.Columns.Count; j++)
{
cell = row.CreateCell(j);
SetCellValues(cell, dataTable.Columns[j].DataType.ToString(), dataTable.Rows[i][j].ToString());
}
}
}
if (File.Exists(excelFileName)) File.Delete(excelFileName);
using (var fs = new FileStream(excelFileName, FileMode.CreateNew, FileAccess.Write)) workbook.Write(fs);
return excelFileName;
}
public string Export(string excelFileName, DataTable dataTable)
{
HSSFWorkbook workbook = new HSSFWorkbook();
ISheet sheet = null;
IRow row = null;
ICell cell = null;
int sheetCount = 1;//当前的sheet数量
int currentSheetCount = 0;//循环时当前保存的条数，每页都会清零
//表头样式
ICellStyle style = workbook.CreateCellStyle();
style.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
//内容样式
style = workbook.CreateCellStyle();
style.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
sheet = workbook.CreateSheet("Sheet" + sheetCount);
//填充表头
row = sheet.CreateRow(0);
for (int i = 0; i < dataTable.Columns.Count; i++)
{
cell = row.CreateCell(i);
cell.SetCellValue(dataTable.Columns[i].ColumnName);
cell.CellStyle = style;
}
//填充内容
for (int i = 0; i < dataTable.Rows.Count; i++)
{
if (currentSheetCount >= _perSheetCount)
{
sheetCount++;
currentSheetCount = 0;
sheet = workbook.CreateSheet("Sheet" + sheetCount);
}
if (sheetCount == 1)//因为第一页有表头，所以从第二页开始写
row = sheet.CreateRow(currentSheetCount + 1);
else//以后没有表头了，所以从开始写，都是基于0的
row = sheet.CreateRow(currentSheetCount);
currentSheetCount++;
for (int j = 0; j < dataTable.Columns.Count; j++)
{
cell = row.CreateCell(j);
cell.CellStyle = style;
SetCellValues(cell, dataTable.Columns[j].DataType.ToString(), dataTable.Rows[i][j].ToString());
}
}
FileStream fs = new FileStream(excelFileName, FileMode.CreateNew, FileAccess.Write);
workbook.Write(fs);
fs.Close();
return excelFileName;
}
#endregion
}总结
本例中主要侧重对目标excel的单元格数据进行访问，对单元格的数据格式进行了比较详细的区分，可自行参考删减。
