Pandorym

Pandorym


Wlcm to Quasi-reality Another Dimension.

Pandorym
Author

A freeLander, full stack developer. Love traveling, photographing and coding.

Share


Our Newsletter


Lorem ipsum dolor sit amet, consectetur adipisicing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua.

Tags


avrt

Twitter


NPOI.EXCEL 2.2.1 入門指南

NPOI是.NET版本的POI,在Apache License 2.0 下發佈。支持xls、xlsx、docx文件的讀寫。

PandorymPandorym

NPOI是.NET版本的POI,在Apache License 2.0下發佈。支持xls、xlsx、docx文件的讀寫。

System Requirement:

  • VS2010 with .NET 4.0 runtime
  • VS2005 or VS2008 with .NET 2.0 Runtime (SP1)
  • vs2003 with .NET 1.1
  • Mono
  • medium trust environment in ASP.NET

而這不是一個完備的文檔,僅摘取了使用NPOI操作Excel文件時一部分常用的功能,旨在快速闡明概念進入工作。

本文分為兩部分:

  1. 一些基本的概念、常用函數的文檔;
  2. 示例代碼
    或許先看後面的示例代碼,幫助更大。

基本概念

  1. WorkBook,整個Excel表格,即工作簿;
  2. Sheet:工作表;
  3. Row:行;
  4. Cell:單元格;
  5. HSSF:即Horrible SpreadSheet Format,用於讀取Excel 97~2007的實現,對應「xls」格式;
  6. XSSF:用於讀取Excel 2007及之後的實現,對應「xlsx」格式。

WorkBook中包含若干個Sheet,可以使用Sheet在WorkBook中的序號、或Sheet的名稱來進行操作。

Sheet中包含若干Row,Row中包含若干列;在NPOI中行、列的序號都是從0開始。

在Row中指定列,便定位了指定位置的Cell。

-HSSF-XSSF的兩套操作,實現了同一接口:

interface HSSF XSSF
IWorkbook HSSFWorkbook XSSFWorkbook
ISheet HSSFSheet XSSFSheet
IRow HSSFRow XSSFRow
ICell HSSFCell XSSFCell

在編碼時以接口聲明變量,是推薦並且易用的。

IWorkbook 相關

Interface / Class Namespace Assembly
IWorkbook NPOI.SS.UserModel NPOI.dll
HSSFWorkbook NPOI.HSSF.UserModel NPOI.dll
XSSFWorkbook NPOI.XSSF.UserModel NPOI.OOXML.dll

構造IWorkbook對象

?() 創建一個新的工作簿對象
?(Stream) 載入流中的文件為工作簿對象

另外,若是在載入時不確定流中的文件格式,可以使用WorkbookFactory.Create(Stream)載入,其將自動識別格式,并以載入后的工作簿對象作為返回值。

IWorkbook的常用函式

Name Return tpye Description
AddPicture( Int32 添加圖片到工作簿。
+ Byte[] pictureData, pictureData:圖片數據。
+ NPOI.SS.UserModel.PictureType format) format :圖片格式。
CreateCellStyle() NPOI.SS.UserModel.ICellStyle 創建單元格樣式。
CreateFont() NPOI.SS.UserModel.IFont 創建字體樣式。
CloneSheet(Int32 sheetIndex) NPOI.SS.UserModel.ISheet 獲得指定工作表的副本。
+ sheetIndex:指定工作表的序號。
CreateSheet() NPOI.SS.UserModel.ISheet 創建、添加、并返回一個工作表;
+ 表名為「Sheet + 表的序號」。
CreateSheet(String sheetname) NPOI.SS.UserModel.ISheet 創建、添加、并返回一個工作表。
+ sheetname:創建的工作簿的名稱。
GetBytes() Byte[] 獲得對應文件的字節數據。
GetSheet(String name) NPOI.SS.UserModel.ISheet 獲得指定名稱的工作表。
+ name:工作表的名稱。
GetSheetAt(Int32 index) NPOI.SS.UserModel.ISheet 獲得指定序號的工作表。
+ index:指定的序號。
GetSheetIndex(String name) Int32 獲得表名對應的表在工作簿中的序號。
+ name:指定的表名。
GetSheetIndex(NPOI.SS.UserModel.Sheet sheet) Int32 獲得表在工作簿中的序號。
+ sheet:指定的表。
GetSheetName(Int32 sheetIx) String 獲得表名。
+ sheetIx:表的序號。
IsSheetVeryHidden(int sheetIx) Boolean 檢測工作表狀態是否為Very hidden。
+ SheetIx:表的序號。
RemoveSheetAt(Int32 index) void 移除工作表。
+ index:工作表的序號。
SetSheetHidden(Int32 sheetIx, bool hidden) void 隱藏或顯示工作表。
+ SheetIx:表的序號;
+ hidden :指示是否隱藏。
SetSheetName(Int32 sheetIx, String name) void 設置表名。
+ sheetIx:表的序號;
+ name :將設置的名字。

IWorkbook的常用字段

Name Type Accessibility Description
IsWriteProtected Boolean get; 是否寫保護。
NumberOfSheets Int32 get; 工作簿中表的個數。

ISheet 相關

Interface / Class Namespace Assembly
ISheet NPOI.SS.UserModel NPOI.dll
HSSFSheet NPOI.HSSF.UserModel NPOI.dll
XSSFSheet NPOI.XSSF.UserModel NPOI.OOXML.dll

構造ISheet對象

  • 在創建時通常使用[-IWorkbook.CreateSheet()];
  • 讀取時使用[-IWorkbook.GetSheet()]、[-IWorkbook.GetSheetAt()]。

ISheet的常用函式

Name Return type Description
AddMergedRegion Int32 合併指定範圍的單元格。
+ (NPOI.SS.Util.Region region)
AddMergedRegion Int32 合併指定範圍的單元格。
+ (NPOI.SS.Util.CellRangeAddress region)
AutoSizeColumn(Int32 column) void 設置自動列寬。
+ column:指定列的的序號。
CloneSheet(HSSFWorkbook workbook) NPOI.SS.UserModel.ISheet 將當前工作表克隆到指定工作簿,并返回。
+ workbook:指定的工作簿。
+ Note: [-HSSFSheet]的[_特有函式]
CreateRow(Int32 rownum) NPOI.SS.UserModel.IRow 在表中指定行號處,創建行。
+ rownum:指定的行號。
+ 該操作將覆蓋該行原有的數據。
GetCellComment(Int32 row, Int32 column) NPOI.SS.UserModel.IComment 獲得指定單元格的批註。
+ row :單元格的行號;
+ column:單元格的列號。
GetColumnStyle(Int32 column) NPOI.SS.UserModel.ICellStyle 獲得指定列的樣式。
+ column:指定列的的序號。
+ 如果指定列未應用樣式,將返回[-null]。
GetMergedRegion(Int32 index) NPOI.SS.Util.CellRangeAddress 獲得合併範圍。
+ index:範圍索引。
GetRow(Int32 rownum) NPOI.SS.UserModel.IRow 獲得指定行。
+ rownum:指定的行號。
+ 獲取未創建的行將返回[-null]。
GroupColumn void 設置列群組。
+ (Int32 fromColumn, Int32 toColumn) fromColumn:起始行號;
+ toColumn :終止行號。
GroupRow(Int32 fromRow, Int32 toRow) void 設置行群組。
+ fromRow:起始行號;
+ toRow :終止行號。
UngroupColumn void 解除列群組。
+ (Int32 fromColumn, Int32 toColumn) fromColumn:起始行號;
+ toColumn :終止行號。
UngroupRow(Int32 fromRow, Int32 toRow) void 解除行群組。
+ fromRow:起始行號;
+ toRow :終止行號。
RemoveRow(NPOI.SS.UserModel.Row row) void 將指定Row從工作表中移除。
+ row:將要移除的Row對象。
+ 異常[-InvalidOperationException]:表中不存在Row。
SetColumnWidth(Int32 column, Int32 width) void 設置指定列的寬度。
+ column:指定列的的序號;
+ width :寬度。單位為字符寬度的1/256。
SetDefaultColumnStyle void 設置默認的列樣式。
+ (Int32 column, column:列的序號。
+ NPOI.SS.UserModel.CellStyle style) style :樣式。

ISheet的常用字段

Name Type Accessibility Description
DefaultColumnWidth Int32 get; set; 默認列寬,單位為字符。
+ [_不讚成使用]。
DefaultRowHeight Int32 get; set; 默認行高,單位為twips(1/20 point)。
FirstRowNum Int32 get; 第一个有效行的序号。
LastRowNum Int32 get; 最后一个有效行的序号。
PhysicalNumberOfRows Int32 get; 有效行的個數。
SheetName String get; 表名。
Workbook NPOI.SS.UserModel.Workbook get; 所在的工作簿。

IRow 相關

Interface / Class Namespace Assembly
IRow NPOI.SS.UserModel NPOI.dll
HSSFRow NPOI.HSSF.UserModel NPOI.dll
XSSFRow NPOI.XSSF.UserModel NPOI.OOXML.dll

構造IRow對象

  • 創建時,使用[-ISheet.CreateRow()];
  • 讀取時,使用[-ISheet.GetRow()];

IRow的常用函式

Name Return type Description
CreateCell(Int32 column) NPOI.SS.UserModel.ICell 在本行指定列處創建單元格。
+ column:指定列的序號。
+ 該函式創建的單元格的類型為[-CellType.BLANK]。
CreateCell( NPOI.SS.UserModel.ICell 在本行指定列處創建 指定類型 的單元格。
+ Int32 columnIndex, column:指定列的序號;
+ NPOI.SS.UserModel.CellType type) tpye :單元格的類型。
+ 覆蓋原有單元格。
GetCell(Int16 cellnum) NPOI.SS.UserModel.ICell 獲得指定列處的單元格。
+ cellnum:指定列的序號。
GetCell(Int32 cellnum) NPOI.SS.UserModel.ICell 獲得指定列處的單元格。
+ cellnum:指定列的序號。
GetCell( NPOI.SS.UserModel.ICell 獲得指定列處的單元格。
+ Int cellnum, cellnum:指定列的序號;
+ NPOI.SS.UserModel.MissingCellPolicy policy) policy :指示如何應對空白單元格。
GetCellEnumerator() IEnumerator 迭代器實現。
+ 依次返回行中的單元格。
MoveCell( void 移動單元格到指定列。
+ NPOI.SS.UserModel.Cell cell, cell :單元格;
+ Int32 newColumn) newColumn:將到達的列號。
RemoveAllCells() void 移除本行內的所以單元格。
RemoveCell(NPOI.SS.UserModel.Cell cell) void 移除單元格。
+ cell:將被移除的單元格。

IRow的常用字段

Name Type Accessibility Description
Cells List<NPOI.SS.UserModel.ICell> get; 該行中所有的單元格。
FirstCellNum Int32 get; 第一個有效單元格的列號。
Height Int16 get; set; 行高。單位:twips。
RowStyle NPOI.SS.UserModel.DCellStyle get; set; 該行中,所有單元格的樣式。
IsHidden Boolean get; set; 該行是否可見。
+ 實際上判斷的是該行高度是否為零。
LastCellNum Int32 get; 最後一個有效單元格的列號。
OutlineLevel Int32 get; 分级显示级别。
RowNum Int32 get; set 行號。
+ 0 =< [-value] =< [-RowRecord.MAX_ROW_NUMBER]。
Sheet NPOI.SS.UserModel.ISheet get; 所在的表。
ZeroHeight Boolean get; set; 該行高等是否為零。

ICell 相關

Interface / Class Namespace Assembly
ICell NPOI.SS.UserModel NPOI.dll
HSSFCell NPOI.HSSF.UserModel NPOI.dll
XSSFCell NPOI.XSSF.UserModel NPOI.OOXML.dll

構造ICell對象

  • 創建時,使用[-ISheet.CreateCell()];
  • 讀取時,使用[-ISheet.GetCell()];

ICell的常用函式

Name Return type Description
CopyCellTo(Int32 targetIndex) NPOI.SS.UserModel.ICell 複製單元格到目標索引。
RemoveCellComment() void 移除單元格批註。
SetAsActiveCell() void 設為活動單元格
SetCellErrorValue(Byte errorCode) void 設置單元格的錯誤值。
SetCellType( void 設置單元格類型。
+ NPOI.SS.UserModel.CellType cellType)
SetCellFormula(String formula) void 設置單元格內容為公式。
SetCellValue(DateTime value) void 設置單元格內容為日期。
+ Excel會將將存為數字。
SetCellValue(Boolean value) void 設置單元格的值為布爾值。
SetCellValue(Int16 value) void 設置單元格的值為數值。
SetCellValue(String value) void 設置單元格的值為字符串。
ToString() String 獲得單元格字面值。

ICell的常用字段

Name Type Accessibility Description
BoundWorkbook NPOI.HSSF.Model.InternalWockbook get; 單元格綁定到的工作簿。
+ [-HSSFCell]特有。
BooleanCellValue Boolean get; 單元格內容的布爾值形式。
CellComment NPOI.SS.UserModel.IComment get; set; 批註。
CellFormula String get; set; 公式。
+ 獲取時,若單元格類型不為[-CellType.Formula],將拋出異常。
CellStyle NPOI.SS.UserModel.ICellStyle get; set; 單元格樣式。
CellType NPOI.SS.UserModel.CellType get; 獲得單元格的類型。
ColumnIndex Int32 get; 所在行。
DateCellValue DateTime get; 單元格內容的日期形式。
ErrorCellValue Byte get; 單元格內容的錯誤值形式。
NumericCellValue Double get; 單元格內容的數字形式。
+ 將計算公式。
Row NPOI.SS.UserModel.IRow get; 單元格所在行。
Sheet NPOI.SS.UserModel.ISheet get; 單元格所在的工作表。
StringCellValue String get; 單元格內容的字符串形式。

創建簡單的Excel文件

思路非常簡單:

  1. 依據指定文件格式創建[-IWorkbook]對象;創建[-ISheet]對象;創建[-IRow]對象;創建[-ICell]對象;
  2. 對[-ICell]設置值;
  3. 保存[-IWorkbook]到文件。

Reference:

  • NPOI.dll
  • NPOI.OOXML
using System;
using System.IO;
using NPOI.SS.UserModel;
using NPOI.HSSF.UserModel;
using NPOI.XSSF.UserModel;

namespace Samples {
	class MakeExcelFile {
		
		public static void Main(string[] args) {
			const String outputFilePath   = @"C:\Workbook.XLSX";
			const String fileFormat = "XLSX";
				
			// 根據後綴名創建合適的工作簿對象
			IWorkbook Workbook = null;
			switch (fileFormat) {
				case "XLS":
					Workbook = new HSSFWorkbook();
					break;
				case "XLSX":
					Workbook = new XSSFWorkbook();
					break;
				default:
					throw new Exception("Unknown file format.");
			}
			
			// 創建工作表,「mySheet」為表名
			ISheet sheet = Workbook.CreateSheet("mySheet"); 
			
			IRow row = sheet.CreateRow(0);    // 創建第0行
			ICell cell = row.CreateCell(3);   // 在0行3列創建單元格
			cell.SetCellValue("Hello NPOI");  // 向單元格寫入字符串
			
			// 保存Excel文件
			using (FileStream fs = File.OpenWrite(outputFilePath)) // 打開文件
				Workbook.Write(fs);                                // 保存工作簿至文件流
		}
		
	}
}

讀取Excel文件

讀取文件只需[-WorkbookFactory.Create(Stream)]即可。

為了便於演示,假設有一個Excel文件,它的第一張工作表如下:

A B C D
1 Hello
2 NPOI

我們將讀取「Hello」、「NPOI」,并輸出到屏幕上。

Reference:

  • NPOI.dll
  • NPOI.OOXML
  • NPOI.OpenXml4Net
using System;
using System.IO;
using NPOI.SS.UserModel;

namespace Samples {
	class ReadExcelFile {
	
		public static void Main(string[] args) {
			// XLS文件同樣適用
			const String inputFilePath   = @"C:\Workbook.XLSX";
			
			// 讀取工作簿
			IWorkbook Workbook = null;
			using (FileStream fs = File.OpenRead(inputFilePath))
				Workbook = WorkbookFactory.Create(fs);
			
			// 獲得第1張工作表
			ISheet Sheet = Workbook.GetSheetAt(0);
			
			IRow Row1 = Sheet.GetRow(0);       // 獲得第1行
			ICell r1cA = Row1.GetCell(0);      // 獲得1行A列的單元格
			String r1cA_str = r1cA.ToString(); // 獲得1行A列單元格中的字面值
			Console.WriteLine("value[Row 1, Col A] : {0}", r1cA_str);
			
			IRow Row2 = Sheet.GetRow(1);       // 獲得第2行
			ICell r2cB = Row2.GetCell(1);      // 獲得2行B列的單元格
			String r2cB_str = r2cB.ToString(); // 獲得2行B列單元格中的字面值
			Console.WriteLine("value[Row 2, Col B] : {0}", r2cB_str);
			
			Console.ReadKey();
		}
		// input :
		// value[Row 1, Col A] : Hello
		// value[Row 2, Col B] : NPOI
		
	}
}

插入圖片

  1. 使用[-IWorkBook.AddPicture()],將圖片添加到工作簿
  2. 使用[-Sheet.CreateDrawingPatriarch()],在工作表上創建一個畫布
  3. 使用[-IDrawing.CreatePicture()],在畫布上插入圖片。

[-IWorkBook.AddPicture()]將返回所添加圖片在工作簿中的索引,這需要保存,以用於在第三步中插入。

因為[-IWorkBook.AddPicture()]只接受[-Byte[]]形式的圖片信息,所以你可能需要對圖片進行轉換,最常用的方式是:

Bitmap pic;
using (MemoryStream stream = new MemoryStream()) {
	// 將位圖轉為JPEG編碼。這是任意的,當然你需要保存一致。
	pic.Save(stream, ImageFormat.Jpeg);
	PicIndex = Workbook.AddPicture(stream.ToArray(), PictureType.JPEG);
}

然後在第三步中,使用[-IDrawing.CreatePicture()]插入圖片,你需要使用一個[-IClientAnchor]對象指明插入的位置及範圍。[HSSFClientAnchor]和[-XSSFClientAnchor]的構造函式是一致的:

| IClientAnchor( | |
| + Int32 dx1, | dx1 :相對於第1個單元格左上角的x軸坐標偏移量 |
| + Int32 dy1, | dy1 :相對於第1個單元格左上角的y軸坐標偏移量 |
| + Int32 dx2, | dx2 :相對於第2個單元格左上角的x軸坐標偏移量 |
| + Int32 dy2, | dy2 :相對於第2個單元格左上角的y軸坐標偏移量 |
| + Int32 col1, | col1:第1個單元格的列號(0 based) |
| + Int32 row1, | row1:第1個單元格的行號(0 based) |
| + Int32 col2, | col2:第2個單元格的列號(0 based) |
| + Int32 row2) | row2:第2個單元格的行號(0 based) |

Reference:

  • NPOI.dll
  • NPOI.OOXML
using System;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;

namespace Samples {
	class Add_Picture {
	
		public static void Main(string[] args) {
			IWorkBook Workbook = null;
			ISheet    Sheet    = null;
			Byte[]    picData  = null;
			// GOTO: 獲得IWorkBook、ISheet對象
		   //       將圖片數據存入picData
			
			// 向工作簿添加圖片,并取得索引。 PictureType應與圖片數據相符。
			Int32 PicIndex = Workbook.AddPicture(byteData, PictureType.JPEG);
			
			// 在工作表上創建畫布。
			IDrawing patriarch = Sheet.CreateDrawingPatriarch();
				
			// 創建位置信息。從A2的左上角,到D5的左上角
			IClientAnchor anchor = new XSSFClientAnchor(0, 0, 0, 0, 1, 2, 3, 4); // or HSSFClientAnchor
			// 若要兼顧HSSF與XSSF,可以使用以下Workbook.GetCreationHelper().CreateClientAnchor()替代上行。
			/* IClientAnchor anchor = t.Workbook.GetCreationHelper().CreateClientAnchor();
			 *               anchor.Dx1 = dx1;
			 *               anchor.Dy1 = dy1;
			 *               anchor.Dx2 = dx2;
			 *               anchor.Dy2 = dy2;
			 *               anchor.Col1 = col1;
			 *               anchor.Row1 = row1;
			 *               anchor.Col2 = col2;
			 *               anchor.Row2 = row2;
			 */
			
			// 使用以上位置信息,插入圖片
			patriarch.CreatePicture(anchor, PicIndex);
		}
	}
}

設置行高、列寬

行高列寬分為兩種:

  1. 工作表的默認行高列寬
  2. 每行每列獨立設置的行高列寬。

就當前版本(2.2.1),若是對[-XSSFSheet.DefaultColumnWidth]設值,不僅值無效,且(使用MSExcel 2016 MSO 16.0.74.26.1015)打開時提示格式錯誤,但可修復。

Reference:

  • NPOI.dll
  • NPOI.OOXML
using System;
using NPOI.SS.UserModel;

namespace Samples {
	class Set_ColWidth_And_RowHeighh {
			
		public static void Main(string[] args) {
			ISheet Sheet = null;
			// GOTO: 獲得ISheet對象
			Sheet.DefaultColumnWidth =  15; // 設置默認列寬為15字符寬度。 [Deprecate
			Sheet.DefaultRowHeight   = 200; // 設置默認行高為10。(即,200 * 1/20)
			
			Sheet.SetColumnWidth(0, 2560);  // 設置第1列的寬度為10字符寬度。(2560 * 1/265)
			
			IRow Row = null;
			// GOTO: 獲得IRow對象。
			Row.Height = 400;               // 設置行高為20。(即,400 * 1/20)
		}
		
	}
}

## 設置單元格樣式、字體
單元格樣式包含字體樣式。
1. 使用[-IWorkbook.CreateCellStyle()]創建單元格樣式。
2. 使用[-IWorkBook.CreateFont()]創建字體。
3. 使用[-ICellStyle.SetFont(IFont)]為單元格樣式設置字體。
4. 通過[-ICell.CellStyle],應用單元格樣式。

Reference:
* NPOI.dll
* NPOI.OOXML

```Csharp
using System;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;

namespace Samples {
	class Set_CellStyle {
	
		public static void Main(string[] args) {
			ICell Cell = null;
			// GOTO: 獲得ICell對象
			
			ICellStyle style = Workbook.CreateCellStyle(); // 創建單元格樣式。
			style.Alignment = HorizontalAlignment.Center;  //   設置居中對齊。
				
				IFont font = Workbook.CreateFont(); // 創建字體
				font.FontName = "宋体";             // 設置字體名稱
				font.FontHeight = 8;                // 設置字號
				
			style.SetFont(font);     // 指明style樣式使用font字體
				
			// 應用單元格樣式
			Cell.CellStyle = style;
		}
		
	}
}

合併單元格

  • 使用[-CellRangeAddress]類指明合併的範圍。
  • 使用[-ISheet.AddMergedRegion()]進行合併。

還可以使用[-Region]類指明合併範圍,單這是棄用的方式。

Reference:

  • NPOI.dll
  • NPOI.OOXML
using System;
using NPOI.SS.Util;
using NPOI.SS.UserModel;

namespace Samples {
	class Merge_Cell {
		
		public static void Main(string[] args) {
			ISheet Sheet = null;
			// GOTO: 獲得ISheet對象
			
			// 指定範圍。第1行~第2行,第E列~第F列。
			CellRangeAddress Range = new CellRangeAddress(0, 1, 4, 5);
			Sheet.AddMergedRegion(Range); // 進行合併。
		}
		
	}
}
Pandorym
Author

Pandorym

A freeLander, full stack developer. Love traveling, photographing and coding.

Comments