NPOI介绍

绝地灬酷狼 2021-07-04 21:04 558阅读 0赞

> 用于读取和写入Microsoft Office二进制和OOXML文件格式的.NET库。

## Excel 操作 ##

**详细代码如下**

Export Excel

var newFile = @"newbook.core.xlsx";
    using (var fs = new FileStream(newFile, FileMode.Create, FileAccess.Write)) { 
        IWorkbook workbook = new XSSFWorkbook();
        ISheet sheet1 = workbook.CreateSheet("Sheet1");
        sheet1.AddMergedRegion(new CellRangeAddress(0, 0, 0, 10));
        var rowIndex = 0;
        IRow row = sheet1.CreateRow(rowIndex);
        row.Height = 30 * 80;
        row.CreateCell(0).SetCellValue("this is content");
        sheet1.AutoSizeColumn(0);
        rowIndex++;
        var sheet2 = workbook.CreateSheet("Sheet2");
        var style1 = workbook.CreateCellStyle();
        style1.FillForegroundColor = HSSFColor.Blue.Index2;
        style1.FillPattern = FillPattern.SolidForeground;
        var style2 = workbook.CreateCellStyle();
        style2.FillForegroundColor = HSSFColor.Yellow.Index2;
        style2.FillPattern = FillPattern.SolidForeground;
        var cell2 = sheet2.CreateRow(0).CreateCell(0);
        cell2.CellStyle = style1;
        cell2.SetCellValue(0);
        cell2 = sheet2.CreateRow(1).CreateCell(0);
        cell2.CellStyle = style2;
        cell2.SetCellValue(1);
        workbook.Write(fs);
    }

Export Word

var newFile2 = @"newbook.core.docx";
    using (var fs = new FileStream(newFile2, FileMode.Create, FileAccess.Write)) { 
        XWPFDocument doc = new XWPFDocument();
        var p0 = doc.CreateParagraph();
        p0.Alignment = ParagraphAlignment.CENTER;
        XWPFRun r0 = p0.CreateRun();
        r0.FontFamily = "microsoft yahei";
        r0.FontSize = 18;
        r0.IsBold = true;
        r0.SetText("This is title");
        var p1 = doc.CreateParagraph();
        p1.Alignment = ParagraphAlignment.LEFT;
        p1.IndentationFirstLine = 500;
        XWPFRun r1 = p1.CreateRun();
        r1.FontFamily = "·ÂËÎ";
        r1.FontSize = 12;
        r1.IsBold = true;
        r1.SetText("This is content, content content content content content content content content content");
        doc.Write(fs);
    }

--------------------

## NPOI ##

NPOI 1.2.x主要由`POIFS、DDF、HPSF、HSSF、SS、Util`六部分组成。

### `NPOI.POIFS` ###

OLE2/ActiveX文档属性读写库

### `NPOI.DDF` ###

Microsoft Office Drawing读写库

### `NPOI.HPSF` ###

OLE2/ActiveX文档读写库

### `NPOI.HSSF` ###

Microsoft Excel BIFF(Excel 97-2003)格式读写库

### `NPOI.SS` ###

Excel公用接口及Excel公式计算引擎

### `NPOI.Util` ###

基础类库，提供了很多实用功能，可用于其他读写文件格式项目的开发

*  `HSSFWorkbook`:是操作Excel2003以前（包括2003）的版本，扩展名是`.xls`
 *  `XSSFWorkbook`:是操作Excel2007的版本，扩展名是`.xlsx`

--------------------

## NPOI进行Excel和Word操作 ##

### 名称空间及描述 ###

<table> 
 <thead> 
  <tr> 
   <th>Assembly Name</th> 
   <th>Module/Namespace</th> 
   <th>Description</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>NPOI.DLL</td> 
   <td>OLE2/ActiveX</td> 
   <td>OLE2/ActiveX文档属性读写库</td> 
  </tr> 
  <tr> 
   <td>NPOI.DLL</td> 
   <td>NPOI.DDF</td> 
   <td>Microsoft Office Drawing读写库</td> 
  </tr> 
  <tr> 
   <td>NPOI.DLL</td> 
   <td>NPOI.HPSF</td> 
   <td>OLE2/ActiveX文档读写库</td> 
  </tr> 
  <tr> 
   <td>NPOI.DLL</td> 
   <td>NPOI.HSSF</td> 
   <td>Microsoft Excel BIFF(Excel 97-2003)格式读写库</td> 
  </tr> 
  <tr> 
   <td>NPOI.DLL</td> 
   <td>NPOI.SS</td> 
   <td>Excel公用接口及Excel公式计算引擎</td> 
  </tr> 
  <tr> 
   <td>NPOI.DLL</td> 
   <td>NPOI.Util</td> 
   <td>基础类库，提供了很多实用功能，可用于其他读写文件格式项目的开发</td> 
  </tr> 
  <tr> 
   <td>NPOI.OOXML.DLL</td> 
   <td>NPOI.XSSF</td> 
   <td>Excel 2007(xlsx)格式读写库</td> 
  </tr> 
  <tr> 
   <td>NPOI.OOXML.DLL</td> 
   <td>NPOI.XWPF</td> 
   <td>Word 2007(docx)格式读写库</td> 
  </tr> 
  <tr> 
   <td>NPOI.OpenXml4Net.DLL</td> 
   <td>NPOI.OpenXml4Net</td> 
   <td>OpenXml底层zip包读写库</td> 
  </tr> 
  <tr> 
   <td>NPOI.OpenXmlFormats.DLL</td> 
   <td>NPOI.OpenXmlFormats</td> 
   <td>微软Office OpenXml对象关系库</td> 
  </tr> 
 </tbody> 
</table>

### 例子 ###

1.  ExportExcel

var newFile = @"newbook.core.xlsx";
       using (var fs = new FileStream(newFile, FileMode.Create, FileAccess.Write)) { 
           IWorkbook workbook = new XSSFWorkbook();
           ISheet sheet1 = workbook.CreateSheet("Sheet1");
           sheet1.AddMergedRegion(new CellRangeAddress(0, 0, 0, 10));
           var rowIndex = 0;
           IRow row = sheet1.CreateRow(rowIndex);
           row.Height = 30 * 80;
           row.CreateCell(0).SetCellValue("this is content");
           sheet1.AutoSizeColumn(0);
           rowIndex++;
           var sheet2 = workbook.CreateSheet("Sheet2");
           var style1 = workbook.CreateCellStyle();
           style1.FillForegroundColor = HSSFColor.Blue.Index2;
           style1.FillPattern = FillPattern.SolidForeground;
           var style2 = workbook.CreateCellStyle();
           style2.FillForegroundColor = HSSFColor.Yellow.Index2;
           style2.FillPattern = FillPattern.SolidForeground;
           var cell2 = sheet2.CreateRow(0).CreateCell(0);
           cell2.CellStyle = style1;
           cell2.SetCellValue(0);
           cell2 = sheet2.CreateRow(1).CreateCell(0);
           cell2.CellStyle = style2;
           cell2.SetCellValue(1);
           workbook.Write(fs);
       }

1.  ExportWord

var newFile2 = @"newbook.core.docx";
       using (var fs = new FileStream(newFile2, FileMode.Create, FileAccess.Write)) { 
           XWPFDocument doc = new XWPFDocument();
           var p0 = doc.CreateParagraph();
           p0.Alignment = ParagraphAlignment.CENTER;
           XWPFRun r0 = p0.CreateRun();
           r0.FontFamily = "microsoft yahei";
           r0.FontSize = 18;
           r0.IsBold = true;
           r0.SetText("This is title");
           var p1 = doc.CreateParagraph();
           p1.Alignment = ParagraphAlignment.LEFT;
           p1.IndentationFirstLine = 500;
           XWPFRun r1 = p1.CreateRun();
           r1.FontFamily = "·ÂËÎ";
           r1.FontSize = 12;
           r1.IsBold = true;
           r1.SetText("This is content, content content content content content content content content content");
           doc.Write(fs);
       }