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;
}
NPOIHelper
版本：<package id="NPOI" version="2.1.3.1" targetFramework="net45" />
點擊下載：NPOI.zip
除了NPOI還有以下操作Excel方式.
參考：http://www.cnblogs.com/lwme/archive/2011/11/18/npoi_excel_import_export.html
