[Solution] NPOI操作Excel

　　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;