你的位置:首页 > ASP.net教程

[ASP.net教程][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中主要有HSSFWorkbookHSSFSheetHSSFRowHSSFCell,对应的接口为IWorkbookISheetIRowICell

  分别对应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方式.

    • 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

 

参考:http://www.cnblogs.com/lwme/archive/2011/11/18/npoi_excel_import_export.html