NPOI 是 POI 项目的 .NET 版本。POI是一个开源的Java读写Excel、WORD等微软OLE2组件文档的项目。
使用 NPOI 你就可以在没有安装 Office 或者相应环境的机器上对 WORD/EXCEL 文档进行读写。NPOI是构建在POI 3.x版本之上的，它可以在没有安装Office的情况下对Word/Excel文档进行读写操作。
NPOI官方网站：http://npoi.codeplex.com/
下载：Install-Package NPOI
本节内容，介绍NPOI的类型说明，简单演示，DataTable互转，基本样式封装，NPOIHelper
类型说明
NPOI中主要有HSSFWorkbook、HSSFSheet、HSSFRow、HSSFCell，对应的接口为IWorkbook、ISheet、IRow、ICell
分别对应Excel文件、工作表、行、列。
简单演示一下写出Excel，读入Excel
//写出
var workbook = new HSSFWorkbook();
var sheet = workbook.CreateSheet("Sheet1");//创建工作表
var row = sheet.CreateRow(0);//在工作表中添加一行
var cell = row.CreateCell(0);//在行中添加一列
cell.SetCellValue("test");//设置列的内容
using (var fs = new FileStream("1.xls", FileMode.Create))
{
workbook.Write(fs);
}
//读取
using (var fs = new FileStream("1.xls", FileMode.Open))
{
workbook = new HSSFWorkbook(fs);
sheet = workbook.GetSheetAt(0);//获取第一个工作表
row = sheet.GetRow(0);//获取工作表第一行
cell = row.GetCell(0);//获取行的第一列
var value = cell.ToString();//获取列的值
}
封装DataTable转Excel
/// <summary>
/// Table转Excel文件流
/// </summary>
/// <param name="table"></param>
/// <returns></returns>
public static MemoryStream TableToExcel(DataTable table)
{
var ms = new MemoryStream();
using (table)
{
var workbook = new HSSFWorkbook();
var sheet = workbook.CreateSheet();
var headerRow = sheet.CreateRow(0); //head
foreach (DataColumn column in table.Columns)
headerRow.CreateCell(column.Ordinal).SetCellValue(column.Caption);//If Caption not set, returns the ColumnName value //body
var rowIndex = 1;
foreach (DataRow row in table.Rows)
{
var dataRow = sheet.CreateRow(rowIndex);
foreach (DataColumn column in table.Columns)
dataRow.CreateCell(column.Ordinal).SetCellValue(row[column].ToString());
rowIndex++;
}
AutoSizeColumns(sheet);
workbook.Write(ms);
ms.Flush();
ms.Position = 0;
}
return ms;
}
Excel转DataTable
/// <summary>
/// Excel文件流导出Table
/// </summary>
/// <param name="excelStream"></param>
/// <returns></returns>
static DataTable TableToExcel(Stream excelStream)
{
var table = new DataTable();
var book = new HSSFWorkbook(excelStream);
var sheet = book.GetSheetAt(0);
var headerRow = sheet.GetRow(0);//第一行为标题行
var cellCount = headerRow.LastCellNum;//LastCellNum = PhysicalNumberOfCells
var rowCount = sheet.LastRowNum;//LastRowNum = PhysicalNumberOfRows - 1 //header
for (int i = headerRow.FirstCellNum; i < cellCount; i++)
{
var column = new DataColumn(headerRow.GetCell(i).StringCellValue);
table.Columns.Add(column);
} //body
for (var i = sheet.FirstRowNum + 1; i < rowCount; i++)
{
var row = sheet.GetRow(i);
var dataRow = table.NewRow();
if (row != null)
{
for (int j = row.FirstCellNum; j < cellCount; j++)
{
if (row.GetCell(j) != null)
dataRow[j] = GetCellValue(row.GetCell(j));
}
}
table.Rows.Add(dataRow);
} return table;
} /// <summary>
/// 根据Excel列类型获取列的值
/// </summary>
/// <param name="cell">Excel列</param>
/// <returns></returns>
private static string GetCellValue(ICell cell)
{
if (cell == null)
return string.Empty;
switch (cell.CellType)
{
case CellType.Blank:
return string.Empty;
case CellType.Boolean:
return cell.BooleanCellValue.ToString();
case CellType.Error:
return cell.ErrorCellValue.ToString();
case CellType.Numeric:
case CellType.Unknown:
default:
return cell.ToString();//This is a trick to get the correct value of the cell. NumericCellValue will return a numeric value no matter the cell value is a date or a number
case CellType.String:
return cell.StringCellValue;
case CellType.Formula:
try
{
var e = new HSSFFormulaEvaluator(cell.Sheet.Workbook);
e.EvaluateInCell(cell);
return cell.ToString();
}
catch
{
return cell.NumericCellValue.ToString();
}
}
}
封装基本样式
/// <summary>
/// 获取单元格样式
/// </summary>
/// <param name="hssfworkbook">Excel操作类</param>
/// <param name="font">单元格字体</param>
/// <param name="fillForegroundColor">图案的颜色</param>
/// <param name="fillPattern">图案样式</param>
/// <param name="fillBackgroundColor">单元格背景</param>
/// <param name="ha">垂直对齐方式</param>
/// <param name="va">垂直对齐方式</param>
/// <returns></returns>
public static ICellStyle GetCellStyle(HSSFWorkbook hssfworkbook, IFont font, HSSFColor fillForegroundColor, FillPatternType fillPattern, HSSFColor fillBackgroundColor, HorizontalAlignment ha, VerticalAlignment va)
{
ICellStyle cellstyle = hssfworkbook.CreateCellStyle();
cellstyle.FillPattern = fillPattern;
cellstyle.Alignment = ha;
cellstyle.VerticalAlignment = va;
if (fillForegroundColor != null)
{
cellstyle.FillForegroundColor = fillForegroundColor.GetIndex();
}
if (fillBackgroundColor != null)
{
cellstyle.FillBackgroundColor = fillBackgroundColor.GetIndex();
}
if (font != null)
{
cellstyle.SetFont(font);
}
//有边框
cellstyle.BorderBottom = CellBorderType.THIN;
cellstyle.BorderLeft = CellBorderType.THIN;
cellstyle.BorderRight = CellBorderType.THIN;
cellstyle.BorderTop = CellBorderType.THIN;
return cellstyle;
}
合并单元格
/// <summary>
/// 合并单元格
/// </summary>
/// <param name="sheet">要合并单元格所在的sheet</param>
/// <param name="rowstart">开始行的索引</param>
/// <param name="rowend">结束行的索引</param>
/// <param name="colstart">开始列的索引</param>
/// <param name="colend">结束列的索引</param>
public static void SetCellRange(ISheet sheet, int rowstart, int rowend, int colstart, int colend)
{
CellRangeAddress cellRangeAddress = new CellRangeAddress(rowstart, rowend, colstart, colend);
sheet.AddMergedRegion(cellRangeAddress);
}
复制样式
var sheet = workbook.GetSheetAt(0);
var cell = sheet.CreateRow(17).CreateCell(0);
cell.SetCellValue(" ");
var style = sheet.GetRow(7).GetCell(0).CellStyle;
sheet.AddMergedRegion(new CellRangeAddress(17, 17, 0, 4));
sheet.GetRow(17).GetCell(0).CellStyle = style;
NPOIHelper
点击下载：NPOI.zip
除了NPOI还有以下操作Excel方式.
- MyXls(http://sourceforge.net/projects/myxls/)
- Koogra(http://sourceforge.net/projects/koogra/)
- ExcelLibrary(http://code.google.com/p/excellibrary/)
- ExcelPackage(http://excelpackage.codeplex.com/)
- EPPlus(http://epplus.codeplex.com/)
- LinqToExcel(http://code.google.com/p/linqtoexcel/)
- NetOffice(http://netoffice.codeplex.com/) 需安装Office Excel