NPOI使用手冊-有解無憂

1.認識NPOI

2. 使用NPOI生成xls檔案

2.1 創建基本內容

2.1.1創建Workbook和Sheet

2.1.2創建DocumentSummaryInformation和SummaryInformation

2.1.3創建單元格

2.1.5創建批注

2.1.6創建頁眉和頁腳

2.2 單元格操作

2.2.1設定格式

2.2.2合并單元格

2.2.3對齊相關設定

2.2.4使用邊框

2.2.5字體設定

2.2.6背景和紋理

2.2.7設定寬度和高度

2.3 使用Excel公式

2.3.1基本計算

2.3.2 SUM函式

2.3.3日期函式

2.3.4字串相關函式

2.3.5 IF函式

2.3.6 CountIf和SumIf函式

2.3.7 Lookup函式

2.3.8亂數函式

2.3.9通過NPOI獲得公式的回傳值

2.4 創建圖形

2.4.1畫線

2.4.2畫矩形

2.4.3畫圓形

2.4.4畫Grid

2.4.5插入圖片

2.5列印相關設定

2.6 高級功能

2.6.1調整表單顯示比例

2.6.2設定密碼

2.6.3組合行、列

2.6.4鎖定列

2.6.5顯示/隱藏網格線

2.6.6設定初始視圖的行、列

2.6.7資料有效性

2.6.8生成下拉式選單

3. 專案實踐

3.1基于.xls模板生成Excel檔案

3.2生成九九乘法表

3.3生成一張工資單

3.4從xls檔案中抽取文本

3.5巧妙使用ExcelChart

3.6匯入Excel檔案

NPOI 1.2教程 - 1 認識NPOI

本章將介紹NPOI的一些基本資訊，包括以下幾個部分

· 什么是NPOI

· 著作權說明

· 相關資源

· 團隊介紹

· 未來展望

· 各Assembly的作用

1.1 什么是NPOI

NPOI，顧名思義，就是POI的.NET版本，那POI又是什么呢？POI是一套用Java寫成的庫，能夠幫助開發者在沒有安裝微軟Office的情況下讀寫Office 97-2003的檔案，支持的檔案格式包括xls, doc, ppt等，在本文發布時，POI的最新版本是3.5 beta 6，

NPOI 1.x是基于POI 3.x版本開發的，與poi 3.2對應的版本是NPOI 1.2，目前最新發布的版本是1.2.1，在該版本中僅支持讀寫Excel檔案和Drawing格式，其他檔案格式將在以后的版本中得到支持，

1.2 著作權說明

NPOI采用的是Apache 2.0許可證（poi也是采用這個許可證），這意味著它可以被用于任何商業或非商業專案，你不用擔心因為使用它而必須開放你自己的源代碼，所以它對于很多從事業務系統開發的公司來說絕對是很不錯的選擇，

當然作為一個開源許可證，肯定也是有一些義務的，例如如果你在系統中使用NPOI，你必須保留NPOI中的所有宣告資訊，對于源代碼的任何修改，必須做出明確的標識，

完整的apache 2.0許可證請見http://www.phpx.com/man/Apache-2/license.html

1.3 相關資源

官方網站：http://npoi.codeplex.com/

POIFS Browser 1.2

下載地址：http://npoi.codeplex.com/Release/ProjectReleases.aspx?ReleaseId=24305

QQ交流群: 78142590

1.4 團隊介紹

Tony Qu來自于中國上海，是這個專案的發起人和開發人員，時區是GMT+8，2008年9月開始了NPOI的開發，負責NPOI所有底層庫的開發、測驗和bug修復，

個人blog地址為http://tonyqus.cnblogs.com/

HüseyinTüfekçilerli來自于土耳其的伊斯坦布爾，也是這個專案的開發人員，時區是GMT+2，2008年11月參與了NPOI的開發，主要負責POIFS Browser 1.0的開發作業，

個人blog地址為http://huseyint.com/

aTao.Xiang，來自中國，2009年8月開始參與該專案，主要參與了NPOI 1.2中文版的撰寫作業和推廣作業

個人blog地址為http://www.cnblogs.com/atao/

1.5 回顧與展望

目前POI版本中的HWPF（用于Word的讀寫庫）還不是很穩定，并非正式發布版本，且負責HWPF的關鍵開發人員已經離開，所以NPOI可能考慮自己重新開發HWPF，另外，目前微軟正在開發Open XML Format SDK，NPOI可能會放棄對ooxml的支持，當然這取決于用戶的需求和Open XML Format SDK的穩定性和速度，從目前而言，NPOI有幾大優勢

第一，完全基于.NET 2.0，而非.NET 3.0/3.5，

第二，讀寫速度快（有個國外的兄弟回復說，他原來用ExcelPackage生成用了4-5個小時，現在只需要4-5分鐘）

第三，穩定性好（相對于用Office OIA而言，畢竟那東西是基于Automation做的，在Server上跑個Automation的東西，想想都覺得可怕），跑過了將近1000個測驗用例（來自于POI的testcase目錄）

第四，API簡單易用，當然這得感謝POI的設計師們

第五，完美支持Excel 2003格式（據說myxls無法正確讀取xls模板，但NPOI可以），以后也許是所有Office 2003格式

希望NPOI把這些優勢繼續發揚下去，這樣NPOI才會更有競爭力，

1.6 NPOI 1.2中各Assembly的作用

NPOI目前有好幾個assembly，每個的作用各有不同，開發人員可以按需加載相應的assembly，在這里大概羅列一下：

NPOI.Util  基礎輔助庫
NPOI.POIFS   OLE2格式讀寫庫
NPOI.DDF   Microsoft Drawing格式讀寫庫
NPOI.SS   Excel公式計算庫
NPOI.HPSF   OLE2的Summary Information和Document Summary Information屬性讀寫庫
NPOI.HSSF   Excel BIFF格式讀寫庫

NPOI 1.2教程 - 2.1.1 創建Workbook和Sheet

作者：Tony Qu

NPOI官方網站：http://npoi.codeplex.com/

創建Workbook說白了就是創建一個Excel檔案，當然在NPOI中更準確的表示是在記憶體中創建一個Workbook物件流，

本節作為第2章的開篇章節，將做較為詳細的講解，以幫助NPOI的學習者更好的理解NPOI的組成和使用，

NPOI.HSSF是專門負責Excel BIFF格式的命名空間，供開發者使用的物件主要位于NPOI.HSSF.UserModel和NPOI.HSSF.Util命名空間下，下面我們要講到的Workbook的創建用的就是NPOI.HSSF.UserModel.HSSFWorkbook類，這個類負責創建.xls檔案，

在開始創建Workbook之前，我們先要在專案中參考一些必要的NPOI assembly，如下所示：

NPOI.dll

NPOI.POIFS.dll

NPOI.HSSF.dll

NPOI.Util.dll

要創建一個新的xls檔案其實很簡單，只要我們初始化一個新的HSSFWorkbook實體就行了，如下所示：

using NPOI.HSSF.UserModel;

...

HSSFWorkbook hssfworkbook = new HSSFWorkbook();

是不是很方便啊，沒有任何引數或設定，但這么創建有一些限制，這樣創建出來的Workbook在Excel中打開是會報錯的，因為Excel規定一個Workbook必須至少帶1個Sheet，這也是為什么在Excel界面中，新建一個Workbook默認都會新建3個Sheet，所以必須加入下面的創建Sheet的代碼才能保證生成的檔案正常：

HSSFSheetsheet = hssfworkbook.CreateSheet("newsheet");

如果要創建標準的Excel檔案，即擁有3個Sheet，可以用下面的代碼：

hssfworkbook.CreateSheet("Sheet1");

hssfworkbook.CreateSheet("Sheet2");

hssfworkbook.CreateSheet("Sheet3");

最后就是把這個HSSFWorkbook實體寫入檔案了，代碼也很簡單，如下所示：

FileStreamfile =new FileStream(@"test.xls", FileMode.Create);

hssfworkbook.Write(file);

file.Close();

這里假設檔案名是test.xls，，在創建完FileStream之后，直接呼叫HSSFWorkbook類的Write方法就可以了，

最后你可以打開test.xls檔案確認一下，是不是有3個空的Sheet，

相關范例請見NPOI 1.2正式版中的CreateEmptyExcelFile專案，

NPOI 1.2教程 - 2.1.2 創建DocumentSummaryInformation和SummaryInformation

作者：Tony Qu

NPOI官方網站：http://npoi.codeplex.com/

昨天收到了不少回復，有msn上的，也有blog上的，我代表NPOI Team向所有支持和關注NPOI的兄弟表示感謝，讓我們共同完善NPOI，

前一節中我們講解了如何創建一個新的Workbook，但在此程序中大家也許會發現一個細節，這些檔案沒有包括DocummentSummaryInformation和SummaryInformation頭，如果你還不是很清楚我在說什么，可以看POIFS Browser打開test.xls檔案后的截圖：

你會發現只有Workbook目錄，其他什么都沒有，但事實上一個正常的xls檔案，比如說Excel生成的xls檔案是類似下面的結構：

是不是多出來DocumentSummaryInformation和SummaryInformation兩個頭？很多人可能對DocumentSummaryInformation和SummaryInformation很陌生，可能第一次聽說這玩意，沒事，這很正常，因為普通用戶很少會去使用這些東西，但它們其實比想象中有用，

請看上圖中的資訊，如作者、標題、標記、備注、主題等資訊，其實這些資訊都是存盤在DocummentSummaryInformation和SummaryInformation里面的，這么一說我想大家應該明白了吧，這些資訊是為了快速提取檔案資訊準備，在Windows XP中，也有對應的查看和修改界面，只是沒有Vista這么方便，如下所示：

這恐怕也是很多人對于這些資訊漠不關心的原因吧，因為沒有人愿意通過右擊檔案->屬性這樣復雜的操作去查看一些摘要資訊，

提示

DocummentSummaryInformation和SummaryInformation并不是Office檔案的專利，只要是OLE2格式，都可以擁有這兩個頭資訊，主要目的就是為了在沒有完整讀取檔案資料的情況下獲得檔案的摘要資訊，同時也可用作桌面搜素的依據，要了解DocummentSummaryInformation的全部屬性請見http://msdn.microsoft.com/en-us/library/aa380374(VS.85).aspx；要了解SummaryInformation的全部屬性請見http://msdn.microsoft.com/en-us/library/aa369794(VS.85).aspx，

好了，說到這里，我想大家對于接下來我們要創建的內容有了初步的認識，下面我們就馬上動手創建，

首先參考以下這些命名空間：

using NPOI.HSSF.UserModel;

using NPOI.HPSF;

using NPOI.POIFS.FileSystem;

其中與DocummentSummaryInformation和SummaryInformation密切相關的是HPSF命名空間，

首先創建Workbook

HSSFWorkbook hssfworkbook =new HSSFWorkbook();

然后創建DocumentSummaryInformation

DocumentSummaryInformationdsi = PropertySetFactory.CreateDocumentSummaryInformation();

dsi.Company ="NPOI Team";

再創建SummaryInformation

SummaryInformationsi = PropertySetFactory.CreateSummaryInformation();

si.Subject ="NPOI SDK Example";

因為是范例，這里僅各設定了一個屬性，其他都沒有設定，

現在我們把創建好的物件賦給Workbook，這樣才能保證這些資訊被寫入檔案，

hssfworkbook.DocumentSummaryInformation= dsi;

hssfworkbook.SummaryInformation= si;

最后和2.1.1節一樣，我們把Workbook通過FileStream寫入檔案，

相關范例請見NPOI 1.2正式版中的CreatePOIFSFileWithProperties

NPOI 1.2教程 - 2.1.3 創建單元格

作者：Tony Qu

NPOI官方網站：http://npoi.codeplex.com/

用過Excel的人都知道，單元格是Excel最有意義的東西，我們做任何操作恐怕都要和單元格打交道，在Excel中我們要添加一個單元格只需要點擊任何一個單元格，然后輸入內容就是了，但是Excel底層其實沒有這么簡單，不同的單元格是有不同的型別的，比如說數值單元格是用NumberRecord表示，文本單元格是用LabelSSTRecord表示，空單元格是用BlankRecord表示，這也就意味著，在設定單元格時，你必須告訴NPOI你需要創建哪種型別的單元格，

要創建單元格首先要創建單元格所在的行，比如，下面的代碼創建了第0行：

HSSFSheet sheet1 = hssfworkbook.CreateSheet("Sheet1");

HSSFRow row1=sheet1.CreateRow(0);

行建好了，就可以建單元格了，比如創建A1位置的單元格：

row1.CreateCell(0).SetCellValue(1);

這里要說明一下，SetCellValue有好幾種多載，你可以設定單元格為bool、double、DateTime、string和HSSFRichTextString型別，其中對于string型別的多載呼叫的就是HSSFRichTextString型別的多載，所以是一樣的，HSSFRichTextString可用于有字體或者Unicode的文本，

如果你覺得每一行要宣告一個HSSFRow很麻煩，可以用下面的方式：

sheet1.CreateRow(0).CreateCell(0).SetCellValue("This is a Sample");

這么用有個前提，那就是第0行還沒創建過，否則得這么用：

sheet1.GetRow(0).CreateCell(0).SetCellValue("This is a Sample");

注意：這里的行在Excel里是從1開始的，但是NPOI內部是從0開始的；列在Excel里面是用字母表示的，而NPOI中也是用從0開始的數字表示的，所以要注意轉換，

如果你要獲得某一個已經創建的單元格物件，可以用下面的代碼：

sheet1.GetRow(row_index).GetCell(column_index);

本節僅講解最基本的單元格創建，有關單元格格式設定、樣式等高級話題請見：2.2節單元格相關操作，

相關范例請見NPOI 1.2正式版中的SetCellValuesInXls專案，

NPOI 1.2教程 - 2.1.4 創建批注

作者：Tony Qu

NPOI官方網站：http://npoi.codeplex.com/

很多人不怎么用Excel中的批注，所以我特地截了張圖，讓大家知道本節我們要創建的到底是什么東西，

在過去，我們恐怕沒有辦法實作這一功能，因為無論是cvs法、html法、oledb法都沒有提供這樣的介面，當然Office PIA法可以做到，但是性能實在太差，而且穩定性不好，經常莫名其妙crash（這是某某兄弟給我的反饋，我參考了下，呵呵），在以后的教程中，你將看到更多在過去無法通過傳統方法實作的東西，好戲才剛剛開始，

批注主要有三個屬性需要設定，一個是批注的位置和大小、一個是批注的文本、還有一個是批注的作者，

批注的位置和大小，在Excel中是與單元格密切相關的，NPOI中通過HSSFClientAnchor的實體來表示，它的建構式比較復雜，有8個引數，它們分別是

引數	說明
dx1	第1個單元格中x軸的偏移量
dy1	第1個單元格中y軸的偏移量
dx2	第2個單元格中x軸的偏移量
dy2	第2個單元格中y軸的偏移量
col1	第1個單元格的列號
row1	第1個單元格的行號
col2	第2個單元格的列號
row2	第2個單元格的行號

例如，如果我們打算讓注釋顯示在B3和E5之間，就應該這么寫：

HSSFPatriarch patr = sheet.CreateDrawingPatriarch();

HSSFComment comment1 = patr.CreateComment(new HSSFClientAnchor(0, 0, 0, 0, 1, 2 , 4, 4));

下面我們設定這個批注的內容和作者，這個比較簡單：

comment1.String=newHSSFRichTextString("Hello World");

comment1.Author="NPOI Team";

最后一步就是把批注賦給某個單元格：

HSSFCell cell= sheet.CreateRow(1).CreateCell(1);

cell.CellComment= comment1;

對于批注，你有兩種選擇，一種是隱藏（默認），一種是顯示（即表單一打開就顯示該批注），可以通過comment1.Visible屬性來控制，

看了上面這張圖大家就應該明白了，這里有2個批注，下面那個是顯示的，上面那個是隱藏的，

相關范例請見NPOI 1.2正式版中的SetCellCommentInXls，

NPOI 1.2教程 - 2.1.6 創建頁眉和頁腳

作者：Tony Qu

NPOI官方網站：http://npoi.codeplex.com/

很多人并不知道Excel的頁眉和頁腳功能，因為在界面上是顯示不了頁眉和頁腳的，必須在列印頁面中才能看到，這也直接導致了其設定界面也顯得更隱秘，你必須進入頁面設定 –>頁眉和頁腳才能設定，以下是Office 2007中的設定界面，

當你按“自定義頁眉”或“自定義頁腳”時，你會看到以下界面，Excel把頁眉、頁腳分成了左中右三部分，這一點絕非單純體現在界面上，在底層的存盤中也是如此，如果你設定的是“左”的內容，底層的存盤字串就會在開頭加上&L，如果是“右”的內容則會加上&R，所以HeaderRecord中的字串看上去是這樣的："&C&LFooter A&R”，這個字串的意思是僅設定了“左”的內容，內容是Footer A，

看了這些我想你應該對頁眉和頁腳有所了解了，回過頭來說NPOI，NPOI中主要是靠HSSFSheet.Header和HSSFSheet.Footer來設定的，這兩個屬性分別是HSSFHeader和HSSFFooter型別的，

參考代碼如下：

HSSFSheet s1= hssfworkbook.CreateSheet("Sheet1");

s1.CreateRow(0).CreateCell(1).SetCellValue(123);

//set headertext

s1.Header.Center="This is a test sheet";

//set footertext

s1.Footer.Left="Copyright NPOI Team";

s1.Footer.Right="created by Tony Qu（瞿杰）";

以上代碼中我添加了頁眉的Center內容，Footer的Left和Right內容，在列印預覽中看到的效果大概是這樣的：

頁眉

頁腳

至于一些Excel特殊字符，比如說頁碼可以用&P，當前日期可以用&D，其他的東西你就自己研究吧，

本范例完整代碼請見NPOI.Examples中的CreateHeaderFooterInXls專案，

NPOI 1.2教程 - 2.2.1 設定單元格格式

作者：Tony Qu

NPOI官方網站：http://npoi.codeplex.com/

在Excel中我們經常要設定格式，比如說日期格式（yyyymmdd）、小數點格式（1.20）、貨幣格式（$2000）、百分比格式（99.99%）等等，這些東西在過去我們恐怕只能在服務器端生成好，不但增加了服務器端的代碼量，還造成了不必要的字串替換操作，如今NPOI將讓服務器從這種完全沒有必要的操作中解放出來，一切都將由Excel在客戶端處理，

使用NPOI時要注意，所有的格式都是通過CellStyle.DataFormat賦給單元格的，而不是直接賦給單元格，

案例一日期格式

假設我們現在需要顯示的日期的格式為2008年5月5日，可以用下面的代碼生成：

HSSFSheet sheet = hssfworkbook.CreateSheet("newsheet");

HSSFCell cell= sheet.CreateRow(0).CreateCell(0);

cell.SetCellValue(newDateTime(2008,5,5));

//set dateformat

HSSFCellStylecellStyle = hssfworkbook.CreateCellStyle();

HSSFDataFormatformat = hssfworkbook.CreateDataFormat();

cellStyle.DataFormat= format.GetFormat("yyyy年m月d日");

cell.CellStyle=cellStyle;

由于這里的“yyyy年m月d日”屬于自定義格式（區別于Excel內嵌的格式），所以必須用hssfworkbook.CreateDataFormat()創建一個HSSFDataFormat實體，然后使用format.GetFormat來獲取相應的格式，只要是Excel支持的格式表示方式，這種方式都能夠實作，

案例二保留2位小數

假設我們有個單元格的值為1.2，怎么顯示成1.20呢？在Excel中可以用“0.00”來表示，所以下面的代碼就能完成：

// Create arow and put some cells in it. Rows are 0 based.

HSSFCell cell= sheet.CreateRow(0).CreateCell(0);

//set valuefor the cell

cell.SetCellValue(1.2);

//numberformat with 2 digits after the decimal point - "1.20"

HSSFCellStylecellStyle = hssfworkbook.CreateCellStyle();

cellStyle.DataFormat= HSSFDataFormat.GetBuiltinFormat("0.00");

cell.CellStyle= cellStyle;

這里與上面有所不同，用的是HSSFDataFormat.GetBuiltinFormat()方法，之所以用這個，是因為0.00是Excel內嵌的格式，完整的Excel內嵌格式串列大家可以看這個視窗中的自定義串列：

這里就不一一列出了，

案例三貨幣格式

貨幣格式在金融的專案中經常用到，比如說人民幣符號¥，美元符號$等，這里可以用下面的代碼表示：

HSSFCellcell2 = sheet.CreateRow(1).CreateCell(0);

cell2.SetCellValue(20000);

HSSFCellStylecellStyle2 = hssfworkbook.CreateCellStyle();

HSSFDataFormatformat = hssfworkbook.CreateDataFormat();

cellStyle2.DataFormat= format.GetFormat("¥#,##0");

cell2.CellStyle= cellStyle2;

注意，這里還加入了千分位分隔符，所以是#,##，至于為什么這么寫，你得去問微軟，呵呵，

案例四百分比

百分比在報表中也很常用，其實基本上和上面一樣，只是格式表示是0.00%，代碼如下：

cellStyle4.DataFormat= HSSFDataFormat.GetBuiltinFormat("0.00%");

由于這里是內嵌格式，所以直接用HSSFDataFormat.GetBuiltinFormat即可，

案例五中文大寫

在表示金額時，我們時常會用到，我也見過不少兄弟實作了數字轉中文大小寫的工具類，以后你可以嘗試讓Excel去處理這一切，代碼和剛才差不多，也是改格式的表示：

HSSFDataFormatformat = hssfworkbook.CreateDataFormat();

cellStyle6.DataFormat= format.GetFormat("[DbNum2][$-804]0");

由于是自定義格式，所以用了HSSFDataFormat.GetFormat，相信你對這兩種獲取格式的形式的區別越來越熟悉了，

案例六科學計數法

這東西數學課上我們都學過，雖然用的不多，但是既然Excel支持，這里也提一下：

cellStyle3.DataFormat= HSSFDataFormat.GetBuiltinFormat("0.00E+00");

下面展示下以上這些例子的顯示效果：

最后總結一下HSSFDataFormat.GetFormat和HSSFDataFormat.GetBuiltinFormat的區別：

當使用Excel內嵌的（或者說預定義）的格式時，直接用HSSFDataFormat.GetBuiltinFormat靜態方法即可，

當使用自己定義的格式時，必須先呼叫HSSFWorkbook.CreateDataFormat()，因為這時在底層會先找有沒有匹配的內嵌FormatRecord，如果沒有就會新建一個FormatRecord，所以必須先呼叫這個方法，然后你就可以用獲得的HSSFDataFormat實體的GetFormat方法了，當然相對而言這種方式比較麻煩，所以內嵌格式還是用HSSFDataFormat.GetBuiltinFormat靜態方法更加直接一些，不過自定義的格式也不是天馬行空隨便定義，還是要參照Excel的格式表示來定義，具體請看相關的Excel教程，

注意：自定義的FormatRecord是嵌入xls檔案內部的，所以不用擔心對方Excel中有沒有定義過這種格式，都是能夠正常使用的，

相關范例請參考NPOI 1.2正式版中的NumberFormatInXls專案，

NPOI 1.2教程 - 2.2.2 單元格合并

作者：Tony Qu

NPOI官方網站：http://npoi.codeplex.com/

合并單元格在制作表格時很有用，比如說表格的標題就經常是把第一行的單元格合并居中，那么在NPOI中應該如何實作單元格的合并呢？

為了實作這一功能，NPOI引入了新的概念，即Region，因為合并單元格，其實就是設定一個區域，下面說一下Region類的引數，Region總共有4個引數，如下所示

Region的引數	說明
FirstRow	區域中第一個單元格的行號
FirstColumn	區域中第一個單元格的列號
LastRow	區域中最后一個單元格的行號
LastColumn	區域中最后一個單元格的列號

由于單元格的合并都是在表的基礎上建立的，所以我們得先建Sheet：

HSSFWorkbookhssfworkbook =newHSSFWorkbook();

HSSFSheetsheet = hssfworkbook.CreateSheet("newsheet");

接下來我們根據實際場景來做一些演示，

場景一標題行的合并

這種場景是最常見的，比如說我們要建立一張銷售情況表，英文叫Sales Report

我們先設定居中和字體樣式，這里我們采用20號字體，代碼如下：

HSSFRow row =sheet.CreateRow(0);

HSSFCell cell= row.CreateCell(0);

cell.SetCellValue("Sales Report");

HSSFCellStylestyle = hssfworkbook.CreateCellStyle();

style.Alignment= HSSFCellStyle.ALIGN_CENTER;

HSSFFont font= hssfworkbook.CreateFont();

font.FontHeight= 20*20;

style.SetFont(font);

cell.CellStyle= style;

要產生圖中的效果，即把A1:F1這6個單元格合并，然后添加合并區域：

sheet.AddMergedRegion(new Region(0, 0, 0,5));

場景二多行合并

看完場景一，你可不要認為多行合并就需要一行一行做，其實也只需要一行代碼，比如說我們要把C3:E5合并為一個單元格，那么就可以用下面的代碼：

sheet.AddMergedRegion(new Region(2, 2, 4,4));

提示即使你沒有用CreateRow和CreateCell創建過行或單元格，也完全可以直接創建區域然后把這一區域合并，Excel的區域合并資訊是單獨存盤的，和RowRecord、ColumnInfoRecord不存在直接關系，

相關范例請參考NPOI 1.2正式版中的MergedCellInXls專案，

NPOI 1.2教程 - 2.2.3 單元格對齊相關設定

作者：Tony Qu

NPOI官方網站：http://npoi.codeplex.com/

本節將圍繞“對齊”選項卡中的設定展開，雖然實際上你會發現該選項卡中的很多設定和對齊沒有什么關系，合并單元格已經在2.2.2節講過了，這里就不提了，

首先我們用代碼創建必要的單元格，代碼如下：

HSSFWorkbookhssfworkbook =new HSSFWorkbook();

HSSFSheetsheet1 = hssfworkbook.CreateSheet("Sheet1");

HSSFRow row =sheet1.CreateRow(0);

row.CreateCell(0).SetCellValue("Test");

這里我們假設在A0單元格中加入了文本Test，

請注意接下來我們要做的所有操作都是在CellStyle的基礎上完成的，所以我們創建一個HSSFCellStyle：

HSSFCellStylestyle=hssfworkbook.CreateCellStyle();

水平對齊

這里用的是HSSFCellStyle.Alignment，默認值自然是常規，即HSSFCellStyle.ALIGN_GENERAL，

如果是左側對齊就是

style.Alignment= HSSFCellStyle.ALIGN_LEFT;

如果是居中對齊就是

style.Alignment= HSSFCellStyle.ALIGN_CENTER;

如果是右側對齊就是

style.Alignment= HSSFCellStyle.ALIGN_RIGHT;

如果是跨列舉中就是

style.Alignment= HSSFCellStyle.ALIGN_CENTER_SELECTION;

如果是兩端對齊就是

style.Alignment= HSSFCellStyle.ALIGN_JUSTIFY;

如果是填充就是

style.Alignment= HSSFCellStyle.ALIGN_FILL;

注意：以上選項僅當有足夠的寬度時才能產生效果，不設定寬度恐怕看不出區別，

垂直對齊

這里用的是HSSFCellStyle.VerticalAlignment，默認值為居中，即HSSFCellStyle.VERTICAL_CENTER

如果是靠上就是

style.VerticalAlignment=HSSFCellStyle.VERTICAL_TOP

如果是居中就是

style.VerticalAlignment=HSSFCellStyle.VERTICAL_CENTER

如果是靠下就是

style.VerticalAlignment=HSSFCellStyle.VERTICAL_BOTTOM

如果是兩端對齊就是

style.VerticalAlignment=HSSFCellStyle.VERTICAL_JUSTIFY

注意：以上選項僅當有足夠的高度時才能產生效果，不設定高度恐怕看不出區別，

自動換行

自動換行翻譯成英文其實就是Wrap的意思，所以這里我們應該用WrapText屬性，這是一個布爾屬性

style.WrapText=true;

效果如下所示：

文本縮進

這是一個不太引人注意的選項，所以這里給張圖出來，讓大家知道是什么，縮進說白了就是文本前面的空白，我們同樣可以用屬性來設定，這個屬性叫做Indention，

style.Indention= 3;

文本旋轉

文本方向大家一定在Excel中設定過，上圖中就是調整界面，主要引數是度數，那么我們如何在NPOI中設定呢？

style.Rotation=(short)90;

以上代碼是把單元格A1中的文本逆時針旋轉90度，等同于下圖中的設定：

請注意，這里的Rotation取值是從-90到90，而不是0-180度，

最后別忘了把樣式變數style賦給HSSFCellStyle.CellStyle，否則就前功盡棄了，呵呵！

以上的一些功能，比如文本旋轉和自動換行，使用傳統的cvs和html法恐怕是無法實作的，隨著學習的不斷深入，你將越來越意識到使用NPOI生成Excel其實如此簡單，

相關范例請參考NPOI 1.2正式版中的SetAlignmentInXls和RotateTextInXls，

NPOI 1.2教程 - 2.2.4 設定單元格邊框

作者：Tony Qu

NPOI官方網站：http://npoi.codeplex.com/

很多表格中都要使用邊框，本節將為你重點講解NPOI中邊框的設定和使用，

邊框和其他單元格設定一樣也是在HSSFCellStyle上操作的，HSSFCellStyle有2種和邊框相關的屬性，分別是:

邊框相關屬性	說明	范例
Border+方向	邊框型別	BorderTop, BorderBottom,BorderLeft, BorderRight
方向+BorderColor	邊框顏色	TopBorderColor,BottomBorderColor, LeftBorderColor, RightBorderColor

其中邊框型別分為以下幾種：

邊框范例圖	對應的靜態值
	HSSFCellStyle.BORDER_DOTTED
	HSSFCellStyle.BORDER_HAIR
	HSSFCellStyle.BORDER_DASH_DOT_DOT
	HSSFCellStyle.BORDER_DASH_DOT
	HSSFCellStyle.BORDER_DASHED
	HSSFCellStyle.BORDER_THIN
	HSSFCellStyle.BORDER_MEDIUM_DASH_DOT_DOT
	HSSFCellStyle.BORDER_SLANTED_DASH_DOT
	HSSFCellStyle.BORDER_MEDIUM_DASH_DOT
	HSSFCellStyle.BORDER_MEDIUM_DASHED
	HSSFCellStyle.BORDER_MEDIUM
	HSSFCellStyle.BORDER_THICK
	HSSFCellStyle.BORDER_DOUBLE

至于顏色那就很多了，全部在HSSFColor下面，如HSSFColor.GREEN,HSSFColor.RED，都是靜態實體，可以直接參考，

下面我們假設我們要把一個單元格的四周邊框都設定上，可以用下面的代碼：

HSSFSheetsheet = hssfworkbook.CreateSheet("newsheet");

// Create arow and put some cells in it. Rows are 0 based.

HSSFRow row =sheet.CreateRow(1);

// Create acell and put a value in it.

HSSFCell cell= row.CreateCell(1);

// Style thecell with borders all around.

HSSFCellStylestyle = hssfworkbook.CreateCellStyle();

style.BorderBottom=HSSFCellStyle.BORDER_THIN;

style.BorderLeft=HSSFCellStyle.BORDER_THIN;

style.BorderRight=HSSFCellStyle.BORDER_THIN;

style.BorderTop= HSSFCellStyle.BORDER_THIN ;

cell.CellStyle=style;

這段代碼使用了最普通的細邊框，使得這個單元格看上去像塊空心磚頭，

注意：這里我們沒有設定邊框的顏色，但這不會影響最終的效果，因為Excel會用默認的黑色給邊框上色，

如果要設定顏色的話，也很簡單，如下：

style.BottomBorderColor=HSSFColor.GREEN.index;

以上代碼將底部邊框設定為綠色，要注意，不是直接把HSSFColor.GREEN賦給XXXXBorderColor屬性，而是把index的值賦給它，

相關范例請參考NPOI 1.2正式版中的SetBorderStyleInXls專案，

NPOI 1.2教程 - 2.2.5 設定單元格字體

作者：Tony Qu

NPOI官方網站：http://npoi.codeplex.com/

本節我們將繼續使用NPOI來設定單元格格式，這一節我們主要講如何設定“字體”，

在設定字體之前，我們首先要做的就是創建字體物件，這和創建數字格式很相似，

HSSFFont font= hssfworkbook.CreateFont();

這句話會在Excel檔案內部創建相應的FontRecord，所以你不用客戶因為自己機器上的Excel沒有相應的字體設定而導致設定丟失，

字體在設定完成后，我們就可以把它賦給單元格樣式，代碼如下：

HSSFCellStylestyle1 = hssfworkbook.CreateCellStyle();

style1.SetFont(font);

cell1.CellStyle=style1;

這里的cell1是HSSFCell的一個實體，

好了，下面我們就開始對字體進行設定，

字體名稱

這里的字體名稱是通過HSSFFont.FontName進行設定的，至于具體的名稱，只要是常用字體都可以，比如說Arial, Verdana等，當然也可以是中文字體名，如宋體、黑體等，不過設定字體名稱有個前提，那就是假設打開這個xls檔案的客戶機上有這種字體，如果沒有，Excel將使用默認字體，

下面就是設定字體名稱為“宋體”的代碼：

font.FontName="宋體";

字號

與字號有關的屬性有兩個，一個是FontHeight，一個是FontHeightInPoints，區別在于，FontHeight的值是FontHeightInPoints的20倍，通常我們在Excel界面中看到的字號，比如說12，對應的是FontHeightInPoints的值，而FontHeight要產生12號字體的大小，值應該是240，所以通常建議你用FontHeightInPoint屬性，

如果要設定字號為12，代碼就是

font.FontHeightInPoints= 12;

字體顏色

這里可能會與CellStyle上的ForegroundColor和BackgroundColor產生混淆，其實所有的字體顏色都是在HSSFFont的實體上設定的，CellStyle的ForegroundColor和BackgroundColor分別指背景填充色和填充圖案的顏色，和文本顏色無關，

要設定字體顏色，我們可以用HSSFFont.Color屬性，顏色可以通過HSSFColor獲得，代碼如下所示：

font.Color =HSSFColor.RED.index;

這行代碼把文本設定為紅色，

下劃線

通常我們所說的下劃線都是單線條的，其實Excel支持好幾種下劃線，如下所示：

型別	對應的值
單下劃線	HSSFFont.U_SINGLE
雙下劃線	HSSFFont.U_DOUBLE
會計用單下劃線	HSSFFont.U_SINGLE_ACCOUNTING
會計用雙下劃線	HSSFFont.U_DOUBLE_ACCOUNTING
無下劃線	HSSFFont.U_NONE

當你要設定下劃線時，可以用HSSFFont.Underline屬性，這是一個byte型別的值，例如

font.Underline=HSSFFont.U_SINGLE

這行代碼就是設定單下劃線的代碼，

上標下標

設定這東西可以用HSSFFont.TypeOffset屬性，值有以下幾種：

TypeOffset的值	說明
HSSFFont.SS_SUPER	上標
HSSFFont.SS_SUB	下標
HSSFFont.SS_NONE	普通，默認值

所以如果你要上標的話，可以用下面的代碼：

font.TypeOffset=HSSFFont.SS_SUPER;

洗掉線

設定這東西可以用HSSFFont.IsStrikeout屬性，當為true時，表示有洗掉線；為false則表示沒有洗掉線，

相關范例請參考NPOI 1.2正式版中的ApplyFontInXls的專案，

NPOI 1.2教程 - 2.2.6設定單元格的背景和圖案

作者：Tony Qu

NPOI官方網站：http://npoi.codeplex.com/

本節我們將用NPOI來為單元格添加背景和圖案，

在之前的教程中，我們已經提到HSSFCellStyle有兩個背景顏色屬性，一個叫FillBackgroundColor，另一個叫FillForegroundColor，但其實這指的都是背景顏色，那為什么還有ForegroundColor呢？為了能夠幫助大家理解，我們舉一個實際的例子，下面這個圖案是Excel的一個單元格：

線是白色的，背景是紅色的，這里的線其實就是下面的Excel界面中的圖案：

至于線的顏色則是圖案顏色，即白色，

所以以上單元格如果要用NPOI來設定就可以用以下代碼完成：

//fillbackground

HSSFCellStylestyle8 = hssfworkbook.CreateCellStyle();

style8.FillForegroundColor= NPOI.HSSF.Util.HSSFColor.WHITE.index;

style8.FillPattern= HSSFCellStyle.SQUARES;

style8.FillBackgroundColor= NPOI.HSSF.Util.HSSFColor.RED.index;

sheet1.CreateRow(7).CreateCell(0).CellStyle= style8;

現在是不是清楚一些了，這里的FillPattern就圖案樣式，所有的列舉值都是HSSFCellStyle的常量；FillForegroundColor就是圖案的顏色，而FillBackgroundColor則是背景的顏色，即紅色，

下面羅列一下圖案樣式及其對應的值：

圖案樣式	常量
	HSSFCellStyle.NO_FILL
	HSSFCellStyle.ALT_BARS
	HSSFCellStyle.FINE_DOTS
	HSSFCellStyle.SPARSE_DOTS
	HSSFCellStyle.LESS_DOTS
	HSSFCellStyle.LEAST_DOTS
	HSSFCellStyle.BRICKS
	HSSFCellStyle.BIG_SPOTS
	HSSFCellStyle.THICK_FORWARD_DIAG
	HSSFCellStyle.THICK_BACKWARD_DIAG
	HSSFCellStyle.THICK_VERT_BANDS
	HSSFCellStyle.THICK_HORZ_BANDS
	HSSFCellStyle.THIN_HORZ_BANDS
	HSSFCellStyle.THIN_VERT_BANDS
	HSSFCellStyle.THIN_BACKWARD_DIAG
	HSSFCellStyle.THIN_FORWARD_DIAG
	HSSFCellStyle.SQUARES
	HSSFCellStyle.DIAMONDS

通過這張表，你將很容易找到自己需要的樣式，不用再去一個一個猜測了，

相關范例請參考NPOI 1.2正式版中的ColorfullMatrixTable和FillBackgroundInXls，

NPOI 1.2教程 - 2.2.7 設定單元格的寬度和高度

作者：Tony Qu

NPOI官方網站：http://npoi.codeplex.com/

在Excel中，單元格的寬度其實就是列的寬度，因為Excel假設這一列的單元格的寬度肯定一致，所以要設定單元格的寬度，我們就得從列的寬度下手，HSSFSheet有個方法叫SetColumnWidth，共有兩個引數：一個是列的索引（從0開始），一個是寬度，

現在假設你要設定B列的寬度，就可以用下面的代碼：

HSSFWorkbookhssfworkbook =newHSSFWorkbook();

HSSFSheetsheet1 = hssfworkbook.CreateSheet("Sheet1");

sheet1.SetColumnWidth(1,100 * 256);

這里你會發現一個有趣的現象，SetColumnWidth的第二個引數要乘以256，這是怎么回事呢？其實，這個引數的單位是1/256個字符寬度，也就是說，這里是把B列的寬度設定為了100個字符，

剛才說的是如何設定，那如何去讀取一個列的寬度呢？直接用GetColumnWidth方法，這個方法只有一個引數，那就是列的索引號，如下所示：

int col1width = sheet1.GetColumnWidth(1);

說完寬度，我們來說高度，在Excel中，每一行的高度也是要求一致的，所以設定單元格的高度，其實就是設定行的高度，所以相關的屬性也應該在HSSFRow上，它就是HSSFRow.Height和HeightInPoints，這兩個屬性的區別在于HeightInPoints的單位是點，而Height的單位是1/20個點，所以Height的值永遠是HeightInPoints的20倍，

要設定第一行的高度，可以用如下代碼：

sheet1.CreateRow(0).Height= 200*20;

或者

sheet1.CreateRow(0).HeightInPoints= 200;

如果要獲得某一行的行高，可以直接拿HSSFRow.Height屬性的回傳值，

你可能覺得一行一行設定行高或者一列一列設定列寬很麻煩，那你可以考慮使用HSSFSheet.DefaultColumnWidth、HSSFSheet.DefaultRowHeight和HSSFSheet.DefaultRowHeightInPoints屬性，

一旦設定了這些屬性，如果某一行或者某一列沒有設定寬度，就會使用默認寬度或高度，代碼如下：

sheet1.DefaultColumnWidth=100*256;

sheet1.DefaultRowHeight=30*20;

相關范例請見NPOI 1.2正式版中的SetWidthAndHeightInXls專案

2.3.1用NPOI操作EXCEL－－基本計算

從這節開始，我們將開始學習Excel高級一點的功能--公式，為某個單元格指定公式后，單元格中的類容將根據公式計算得出，如圖：

圖中設定的是一個基本運算式”1+2*3”，單元格A1中將顯示此運算式計算的結果”7”，如圖所示，對應的C#生成代碼也很簡單，如下：

同樣，NPOI也支持單元格參考型別的公式設定，如下圖中的C1=A1*B1，

對應的公式設定代碼為：

cel3.SetCellFormula("A1*B1");

是不是很簡單呢？但要注意，在利用NPOI寫程式時，行和列的計數都是從0開始計算的，但在設定公式時又是按照Excel的單元格命名規則來的，

2.3.2用NPOI操作EXCEL－－SUM函式

這節我們開始學習Excel中最常用的函式—Sum求和函式，

首先，我們先看一上最簡單的Sum函式：Sum(num1,num2,...)，使用效果如圖

圖中的E1=Sum(A1,C1)表示將A1與C1的和填充在E1處，與公式”E1=A1+C1”等效，對應的生成代碼與上一節中的基本計算公式類似：

HSSFSheet sheet1 = hssfworkbook.CreateSheet("Sheet1");
HSSFRow row1 = sheet1.CreateRow(0);
HSSFCell cel1 = row1.CreateCell(0);
HSSFCell cel2 = row1.CreateCell(1);
HSSFCell cel3 = row1.CreateCell(2);
HSSFCell celSum1 = row1.CreateCell(3);
HSSFCell celSum2 = row1.CreateCell(4);
HSSFCell celSum3 = row1.CreateCell(5);

cel1.SetCellValue(1);
cel2.SetCellValue(2);
cel3.SetCellValue(3);
celSum2.SetCellFormula("sum(A1,C1)");

當然，把每一個單元格作為Sum函式的引數很容易理解，但如果要求和的單元格很多，那么公式就會很長，既不方便閱讀也不方便書寫，所以Excel提供了另外一種多個單元格求和的寫法：

如上圖中的“Sum(A1:C1)”表示求從A1到C1所有單元格的和，相當于A1+B1+C1，
對應的代碼為：

celSum1.SetCellFormula("sum(A1:C1)");

最后，還有一種求和的方法，就是先定義一個區域，如”range1”，然后再設定Sum(range1)，此時將計算區域中所有單元格的和，
定義區域的代碼為：

HSSFName range = hssfworkbook.CreateName();
range.Reference = "Sheet1!$A1:$C1";
range.NameName = "range1";

執行此代碼后的Excel檔案將在的公式選單下的名稱管理器(Excel2007的選單路徑,2003稍有不同)中看到如下區域定義：

給單元格F1加上公式：

celSum3.SetCellFormula("sum(range1)");

生成的Excel如下圖所示：

2.3.3用NPOI操作EXCEL－－日期函式

Excel中有非常豐富的日期處理函式，在NPOI中同樣得到了很好的支持，如下圖：
對應的與前面的基本公式設定類似：

HSSFSheet sheet1 = hssfworkbook.CreateSheet("Sheet1");
HSSFRow row1 = sheet1.CreateRow(0);
HSSFRow row2 = sheet1.CreateRow(1);
row1.CreateCell(0).SetCellValue("姓名");
row1.CreateCell(1).SetCellValue("參加作業時間");
row1.CreateCell(2).SetCellValue("當前日期");
row1.CreateCell(3).SetCellValue("作業年限");

HSSFCell cel1 = row2.CreateCell(0);
HSSFCell cel2 = row2.CreateCell(1);
HSSFCell cel3 = row2.CreateCell(2);
HSSFCell cel4 = row2.CreateCell(3);

cel1.SetCellValue("aTao.Xiang");
cel2.SetCellValue(new DateTime(2004, 7, 1));
cel3.SetCellFormula("TODAY()");
cel4.SetCellFormula("CONCATENATE(DATEDIF(B2,TODAY(),\"y\"),\"年\",DATEDIF(B2,TODAY(),\"ym\"),\"個月\")");

//在poi中日期是以double型別表示的，所以要格式化
HSSFCellStyle cellStyle = hssfworkbook.CreateCellStyle();
HSSFDataFormat format = hssfworkbook.CreateDataFormat();
cellStyle.DataFormat = format.GetFormat("yyyy-m-d");

cel2.CellStyle = cellStyle;
cel3.CellStyle = cellStyle;

下面對上例中用到的幾個主要函式作一些說明：
TODAY()：取得當前日期;
DATEDIF(B2,TODAY(),"y")：取得B2單元格的日期與前日期以年為單位的時間間隔，(“Y”:表示以年為單位,”m”表示以月為單位;”d”表示以天為單位);
CONCATENATE(str1,str2,...)：連接字串，

另外附上Excel中常用的日期函式串列，只需要將此句代碼作適當修改即可：

cel4.SetCellFormula("CONCATENATE(DATEDIF(B2,TODAY(),\"y\"),\"年\",DATEDIF(B2,TODAY(),\"ym\"),\"個月\")");

函式名	函式說明	語法
DATE	回傳代表特定日期的系列數，	DATE(year,month,day)
DATEDIF	計算兩個日期之間的天數、月數或年數，	DATEDIF(start_date,end_date,unit)
DATEVALUE	函式 DATEVALUE的主要功能是將以文字表示的日期轉換成一個系列數，	DATEVALUE(date_text)
DAY	回傳以系列數表示的某日期的天數，用整數 1到 31 表示，	DAY(serial_number)
DAYS360	按照一年 360天的演算法（每個月以 30 天計，一年共計 12 個月），回傳兩日期間相差的天數，	DAYS360(start_date,end_date,method)
EDATE	回傳指定日期 (start_date)之前或之后指定月份數的日期系列數，使用函式 EDATE可以計算與發行日處于一月中同一天的到期日的日期，	EDATE(start_date,months)
EOMONTH	回傳 start-date之前或之后指定月份中最后一天的系列數，用函式 EOMONTH可計算特定月份中最后一天的時間系列數，用于證券的到期日等計算，	EOMONTH(start_date,months)
HOUR	回傳時間值的小時數，即一個介于 0 (12:00 A.M.)到 23 (11:00 P.M.)之間的整數，	HOUR(serial_number)
MINUTE	回傳時間值中的分鐘，即一個介于 0到 59 之間的整數，	MINUTE(serial_number)
MONTH	回傳以系列數表示的日期中的月份，月份是介于 1（一月）和 12（十二月）之間的整數，	MONTH(serial_number)
NETWORKDAYS	回傳引數 start-data和 end-data 之間完整的作業日數值，作業日不包括周末和專門指定的假期	NETWORKDAYS(start_date,end_date,holidays)
NOW	回傳當前日期和時間所對應的系列數，	NOW( )
SECOND	回傳時間值的秒數，回傳的秒數為 0至 59 之間的整數，	SECOND(serial_number)
TIME	回傳某一特定時間的小數值，函式 TIME回傳的小數值為從 0 到 0.99999999 之間的數值，代表從 0:00:00 (12:00:00 A.M)到 23:59:59 (11:59:59 P.M)之間的時間，	TIME(hour,minute,second)
TIMEVALUE	回傳由文本串所代表的時間的小數值，該小數值為從 0到 0.999999999 的數值，代表從 0:00:00 (12:00:00 AM)到 23:59:59 (11:59:59 PM)之間的時間，	TIMEVALUE(time_text)
TODAY	回傳當前日期的系列數，系列數是 Microsoft Excel用于日期和時間計算的日期-時間代碼，	TODAY( )
WEEKDAY	回傳某日期為星期幾，默認情況下，其值為 1（星期天）到 7（星期六）之間的整數，	WEEKDAY(serial_number,return_type)
WEEKNUM	回傳一個數字，該數字代表一年中的第幾周，	WEEKNUM(serial_num,return_type)
WORKDAY	回傳某日期（起始日期）之前或之后相隔指定作業日的某一日期的日期值，作業日不包括周末和專門指定的假日，	WORKDAY(start_date,days,holidays)
YEAR	回傳某日期的年份，回傳值為 1900到 9999 之間的整數，	YEAR(serial_number)
YEARFRAC	回傳 start_date和 end_date 之間的天數占全年天數的百分比，	YEARFRAC(start_date,end_date,basis)

2.3.4用NPOI操作EXCEL－－字串函式

      這一節我們開始學習Excel另一類非常常見的函式—字串函式，在Excel中提供了非常豐富的字串函式，在NPOI中同樣得到了很好的支持，
一、大小寫轉換類函式
      LOWER(String)：將一個文字串中的所有大寫字母轉換為小寫字母，
      UPPER(String)：將文本轉換成大寫形式，
      PROPER(String)：將文字串的首字母及任何非字母字符之后的首字母轉換成大寫，將其余的字母轉換成小寫，
      對應的C#代碼與前幾節講的設定公式的代碼類似：

HSSFRow row1 = sheet1.CreateRow(0);
row1.CreateCell(0).SetCellValue("待操作字串");
row1.CreateCell(1).SetCellValue("操作函式");
row1.CreateCell(2).SetCellValue("操作結果");

HSSFRow row2 = sheet1.CreateRow(1);
row2.CreateCell(0).SetCellValue("This is a NPOI example!");
row2.CreateCell(1).SetCellValue("LOWER(A2)");
//將此句中的“LOWER(A2)”換成UPPER (A2)、PROPER (A2)可以看到不同效果，
row2.CreateCell(2).SetCellFormula("LOWER(A2)");

二、取出字串中的部分字符
LEFT(text,num_chars)：LEFT(text,num_chars)其中Text是包含要提取字符的文本串，Num_chars指定要由 LEFT 所提取的字符數，

MID(text,start_num,num_chars)：MID(text,start_num,num_chars)其中Text是包含要提取字符的文本串，Start_num是文本中要提取的第一個字符的位置，num_chars表示要提取的字符的數，

      RIGHT(text,num_chars)：RIGHT(text,num_chars)其中Text是包含要提取字符的文本串，Num_chars指定希望 RIGHT 提取的字符數，
      代碼與上面類似，就不寫了，

三、去除字串的空白
      TRIM(text)：其中Text為需要清除其中空格的文本，需要注意的是，與C#中的Trim不同，Excel中的Trim函式不僅會洗掉字串頭尾的字符，字串中的多余字符也會洗掉，單詞之間只會保留一個空格，

四、字串的比較
EXACT(text1,text2)：比較兩個字串是否相等，區分大小寫，

執行效果如下：

在此只簡單的講了一下常用的函式，Excel中還有很多的字串函式，在此附上，讀者可以一個一個去測驗，

函式名	函式說明	語法
ASC	將字串中的全角（雙位元組）英文字母更改為半角（單位元組）字符，	ASC(text)
CHAR	回傳對應于數字代碼的字符，函式 CHAR可將其他型別計算機檔案中的代碼轉換為字符，	CHAR(number)
CLEAN	洗掉文本中不能列印的字符，對從其他應用程式中輸入的字串使用 CLEAN函式，將洗掉其中含有的當前作業系統無法列印的字符，例如，可以洗掉通常出現在資料檔案頭部或尾部、無法列印的低級計算機代碼，	CLEAN(text)
CODE	回傳文字串中第一個字符的數字代碼，回傳的代碼對應于計算機當前使用的字符集，	CODE(text)
CONCATENATE	將若干文字串合并到一個文字串中，	CONCATENATE (text1,text2,...)
DOLLAR	依斬訓幣格式將小數四舍五入到指定的位數并轉換成文字，	DOLLAR 或 RMB(number,decimals)
EXACT	該函式測驗兩個字串是否完全相同，如果它們完全相同，則回傳 TRUE；否則，回傳 FALSE，函式 EXACT 能區分大小寫，但忽略格式上的差異，利用函式 EXACT可以測驗輸入檔案內的文字，	EXACT(text1,text2)
FIND	FIND 用于查找其他文本串 (within_text)內的文本串 (find_text)，并從 within_text的首字符開始回傳 find_text的起始位置編號，	FIND(find_text,within_text,start_num)
FIXED	按指定的小數位數進行四舍五入，利用句點和逗號，以小數格式對該數設定格式，并以文字串形式回傳結果，	FIXED(number,decimals,no_commas)
JIS	將字串中的半角（單位元組）英文字母或片假名更改為全角（雙位元組）字符，	JIS(text)
LEFT	LEFT 基于所指定的字符數回傳文本串中的第一個或前幾個字符， LEFTB 基于所指定的位元組數回傳文本串中的第一個或前幾個字符，此函式用于雙位元組字符，	LEFT(text,num_chars) LEFTB(text,num_bytes)
LEN	LEN 回傳文本串中的字符數， LENB 回傳文本串中用于代表字符的位元組數，此函式用于雙位元組字符，	LEN(text) LENB(text)
LOWER	將一個文字串中的所有大寫字母轉換為小寫字母，	LOWER(text)
MID	MID 回傳文本串中從指定位置開始的特定數目的字符，該數目由用戶指定， MIDB 回傳文本串中從指定位置開始的特定數目的字符，該數目由用戶指定，此函式用于雙位元組字符，	MID(text,start_num,num_chars) MIDB(text,start_num,num_bytes)
PHONETIC	提取文本串中的拼音 (furigana)字符，	PHONETIC(reference)
PROPER	將文字串的首字母及任何非字母字符之后的首字母轉換成大寫，將其余的字母轉換成小寫，	PROPER(text)
REPLACE	REPLACE 使用其他文本串并根據所指定的字符數替換某文本串中的部分文本， REPLACEB 使用其他文本串并根據所指定的字符數替換某文本串中的部分文本，此函式專為雙位元組字符使用，	REPLACE(old_text,start_num,num_chars,new_text) REPLACEB(old_text,start_num,num_bytes,new_text)
REPT	按照給定的次數重復顯示文本，可以通過函式 REPT來不斷地重復顯示某一文字串，對單元格進行填充，	REPT(text,number_times)
RIGHT	RIGHT 根據所指定的字符數回傳文本串中最后一個或多個字符， RIGHTB 根據所指定的字符數回傳文本串中最后一個或多個字符，此函式用于雙位元組字符，	RIGHT(text,num_chars) RIGHTB(text,num_bytes)
SEARCH	SEARCH 回傳從 start_num開始首次找到特定字符或文本串的位置上特定字符的編號，使用 SEARCH可確定字符或文本串在其他文本串中的位置，這樣就可使用 MID或 REPLACE 函式更改文本， SEARCHB 也可在其他文本串 (within_text)中查找文本串 (find_text)，并回傳 find_text的起始位置編號，此結果是基于每個字符所使用的位元組數，并從 start_num開始的，此函式用于雙位元組字符，此外，也可使用 FINDB在其他文本串中查找文本串，	SEARCH(find_text,within_text,start_num) SEARCHB(find_text,within_text,start_num)
SUBSTITUTE	在文字串中用 new_text替代 old_text，如果需要在某一文字串中替換指定的文本，請使用函式 SUBSTITUTE；如果需要在某一文字串中替換指定位置處的任意文本，請使用函式 REPLACE，	SUBSTITUTE(text,old_text,new_text,instance_num)
T	將數值轉換成文本，	T(value)
TEXT	將一數值轉換為按指定數字格式表示的文本，	TEXT(value,format_text)
TRIM	除了單詞之間的單個空格外，清除文本中所有的空格，在從其他應用程式中獲取帶有不規則空格的文本時，可以使用函式 TRIM，	TRIM(text)
UPPER	將文本轉換成大寫形式，	UPPER(text)
VALUE	將代表數字的文字串轉換成數字，	VALUE(text)
WIDECHAR	將單位元組字符轉換為雙位元組字符，	WIDECHAR(text)
YEN	使用￥（日圓）貨幣格式將數字轉換成文本，并對指定位置后的數字四舍五入，	YEN(number,decimals)

2.3.5用NPOI操作EXCEL－－If函式

在Excel中,IF(logical_test,value_if_true,value_if_false)用來用作邏輯判斷，其中Logical_test表示計算結果為 TRUE 或 FALSE 的任意值或運算式 ; value_if_true表示當運算式Logical_test的值為TRUE時的回傳值；value_if_false表示當運算式Logical_test的值為FALSE時的回傳值，同樣在NPOI中也可以利用這個運算式進行各種邏輯運算，如下代碼分別設定了B2和D2單元格的用于邏輯判斷的公式，

HSSFSheet sheet1 = hssfworkbook.CreateSheet("Sheet1");

HSSFRow row1 = sheet1.CreateRow(0);
row1.CreateCell(0).SetCellValue("姓名");
row1.CreateCell(1).SetCellValue("身份證號");
row1.CreateCell(2).SetCellValue("性別");
row1.CreateCell(3).SetCellValue("語文");
row1.CreateCell(4).SetCellValue("是否合格");

HSSFRow row2 = sheet1.CreateRow(1);
row2.CreateCell(0).SetCellValue("令狐沖");
row2.CreateCell(1).SetCellValue("420821198808101014");
row2.CreateCell(2).SetCellFormula("IF(MOD(MID(B2,18,1),2)=0,\"男\",\"女\")");
row2.CreateCell(3).SetCellValue(85);
row2.CreateCell(4).SetCellFormula("IF(D2>60,IF(D2>90,\"優秀\",\"合格\"),\"不合格\")");

其中最關鍵的兩句執行結果如下：

row2.CreateCell(2).SetCellFormula("IF(MOD(MID(B2,18,1),2)=0,\"男\",\"女\")");

row2.CreateCell(4).SetCellFormula("IF(D2>60,IF(D2>90,\"優秀\",\"合格\"),\"不合格\")");

下面分別對這幾個函式作一些說明：
MOD(MID(B2,18,1),2)：我們知道18位身份證號的第18位表示性別，偶數為男性，奇數為女性，所以用了MID(B2,18,1)取第18位數字（與C#中一般從0計數不同，第二個引數是從1算起，有關MID函式的更多資訊，請參見字串函式），用MOD取余函式判斷奇偶，在Excel中對資料型別的控制沒有C#中那么嚴格，如此例中我截取出來的是字串，但當我做取余運算時Excel會自動轉換，
IF(D2>60,IF(D2>90,"優秀","合格"),"不合格")：這是IF的嵌套使用，表示90分以上為優秀，60分以上為合格，否則為不合格，

2.3.6用NPOI操作EXCEL－－COUNTIF和SUMIF函式

一、COUNTIF
這一節，我們一起來學習Excel中另一個常用的函式--COUNTIF函式，看函式名就知道這是一個用來在做滿足某條件的計數的函式，先來看一看它的語法：COUNTIF(range,criteria)，引數說明如下：

Range	需要進行讀數的計數
Criteria	條件運算式，只有當滿足此條件時才進行計數

接下來看一個例子，代碼如下：

HSSFSheet sheet1 = hssfworkbook.CreateSheet("Sheet1");

HSSFRow row1 = sheet1.CreateRow(0);
row1.CreateCell(0).SetCellValue("姓名");
row1.CreateCell(1).SetCellValue("成績");

HSSFRow row2 = sheet1.CreateRow(1);
row2.CreateCell(0).SetCellValue("令狐沖");
row2.CreateCell(1).SetCellValue(85);

HSSFRow row3 = sheet1.CreateRow(2);
row3.CreateCell(0).SetCellValue("任盈盈");
row3.CreateCell(1).SetCellValue(90);

HSSFRow row4 = sheet1.CreateRow(3);
row4.CreateCell(0).SetCellValue("任我行");
row4.CreateCell(1).SetCellValue(70);

HSSFRow row5 = sheet1.CreateRow(4);
row5.CreateCell(0).SetCellValue("左冷嬋");
row5.CreateCell(1).SetCellValue(45);

HSSFRow row6 = sheet1.CreateRow(5);
row6.CreateCell(0).SetCellValue("岳不群");
row6.CreateCell(1).SetCellValue(50);

HSSFRow row7 = sheet1.CreateRow(6);
row7.CreateCell(0).SetCellValue("合格人數：");
row7.CreateCell(1).SetCellFormula("COUNTIF(B2:B6,\">60\")");

執行結果如下：

我們可以看到，CountIf函式成功的統計出了區域“B2：B6”中成績合格的人數（這里定義成績大于60為合格），

二、SUMIF

接下來，順便談談另一個與CountIF類似的函式—SumIf函式，此函式用于統計某區域內滿足某條件的值的求和(CountIf是計數)，與CountIF不同，SumIF有三個引數，語法為SumIF(criteria_range, criteria,sum_range)，各引數的說明如下：

criteria_range	條件測驗區域，第二個引數Criteria中的條件將與此區域中的值進行比較
criteria	條件測驗值，滿足條件的對應的sum_range項將進行求和計算
sum_range	匯總資料所在區域，求和時會排除掉不滿足Criteria條件的對應的項

我們還是以例子來加以說明：

Code
HSSFSheet sheet1 = hssfworkbook.CreateSheet("Sheet1");

HSSFRow row1 = sheet1.CreateRow(0);
row1.CreateCell(0).SetCellValue("姓名");
row1.CreateCell(1).SetCellValue("月份");
row1.CreateCell(2).SetCellValue("銷售額");

HSSFRow row2 = sheet1.CreateRow(1);
row2.CreateCell(0).SetCellValue("令狐沖");
row2.CreateCell(1).SetCellValue("一月");
row2.CreateCell(2).SetCellValue(1000);

HSSFRow row3 = sheet1.CreateRow(2);
row3.CreateCell(0).SetCellValue("任盈盈");
row3.CreateCell(1).SetCellValue("一月");
row3.CreateCell(2).SetCellValue(900);

HSSFRow row4 = sheet1.CreateRow(3);
row4.CreateCell(0).SetCellValue("令狐沖");
row4.CreateCell(1).SetCellValue("二月");
row4.CreateCell(2).SetCellValue(2000);

HSSFRow row5 = sheet1.CreateRow(4);
row5.CreateCell(0).SetCellValue("任盈盈");
row5.CreateCell(1).SetCellValue("二月");
row5.CreateCell(2).SetCellValue(1000);

HSSFRow row6 = sheet1.CreateRow(5);
row6.CreateCell(0).SetCellValue("令狐沖");
row6.CreateCell(1).SetCellValue("三月");
row6.CreateCell(2).SetCellValue(3000);

HSSFRow row7 = sheet1.CreateRow(6);
row7.CreateCell(0).SetCellValue("任盈盈");
row7.CreateCell(1).SetCellValue("三月");
row7.CreateCell(2).SetCellValue(1200);

HSSFRow row8 = sheet1.CreateRow(7);
row8.CreateCell(0).SetCellValue("令狐沖一季度銷售額：");
row8.CreateCell(2).SetCellFormula("SUMIF(A2:A7,\"=令狐沖\",C2:C7)");

HSSFRow row9 = sheet1.CreateRow(8);
row9.CreateCell(0).SetCellValue("任盈盈一季度銷售額：");
row9.CreateCell(2).SetCellFormula("SUMIF(A2:A7,\"=任盈盈\",C2:C7)");

執行結果如下：

如上圖，SUMIF統計出了不同人一季度的銷售額，

2.3.7用NPOI操作EXCEL－－LOOKUP函式

今天，我們一起學習Excel中的查詢函式－－LOOKUP，其基本語法形式為LOOKUP(lookup_value,lookup_vector,result_vector)，還是以例子加以說明更容易理解：

Code
HSSFSheet sheet1 = hssfworkbook.CreateSheet("Sheet1");
HSSFRow row1 = sheet1.CreateRow(0);
row1.CreateCell(0).SetCellValue("收入最低");
row1.CreateCell(1).SetCellValue("收入最高");
row1.CreateCell(2).SetCellValue("稅率");

HSSFRow row2 = sheet1.CreateRow(1);
row2.CreateCell(0).SetCellValue(0);
row2.CreateCell(1).SetCellValue(3000);
row2.CreateCell(2).SetCellValue(0.1);

HSSFRow row3 = sheet1.CreateRow(2);
row3.CreateCell(0).SetCellValue(3001);
row3.CreateCell(1).SetCellValue(10000);
row3.CreateCell(2).SetCellValue(0.2);

HSSFRow row4 = sheet1.CreateRow(3);
row4.CreateCell(0).SetCellValue(10001);
row4.CreateCell(1).SetCellValue(20000);
row4.CreateCell(2).SetCellValue(0.3);

HSSFRow row5 = sheet1.CreateRow(4);
row5.CreateCell(0).SetCellValue(20001);
row5.CreateCell(1).SetCellValue(50000);
row5.CreateCell(2).SetCellValue(0.4);

HSSFRow row6 = sheet1.CreateRow(5);
row6.CreateCell(0).SetCellValue(50001);
row6.CreateCell(2).SetCellValue(0.5);

HSSFRow row8 = sheet1.CreateRow(7);
row8.CreateCell(0).SetCellValue("收入");
row8.CreateCell(1).SetCellValue("稅率");

HSSFRow row9 = sheet1.CreateRow(8);
row9.CreateCell(0).SetCellValue(7800);
row9.CreateCell(1).SetCellFormula("LOOKUP(A9,$A$2:$A$6,$C$2:$C$6)");

這是一個根據工資查詢相應稅率的例子，我們首先創建了不同工資區間對應稅率的字典，然后根據具體的工資在字典中找出對應的稅率，執行后生成的Excel如下：

下面對各引數加以說明：
第一個引數：需要查找的內容，本例中指向A9單元格，也就是7800；
第二個引數：比較物件區域，本例中的工資需要與$A$2:$A$6中的各單元格中的值進行比較；第三個引數：查找結果區域，如果匹配到會將此區域中對應的資料回傳，如本例中回傳$C$2:$C$6中對應的值，
可能有人會問，字典中沒有7800對應的稅率啊，那么Excel中怎么匹配的呢？答案是模糊匹配，并且LOOKUP函式只支持模糊匹配，Excel會在$A$2:$A$6中找小于7800的最大值，也就是A3對應的3001，然后將對應的$C$2:$C$6區域中的C3中的值回傳，這就是最終結果0.2的由來，這下明白了吧：）

VLOOKUP
另外，LOOKUP函式還有一位大哥－－VLOOKUP，兩兄弟有很多相似之處，但大哥本領更大，Vlookup用對比數與一個“表”進行對比，而不是Lookup函式的某1列或1行，并且Vlookup可以選擇采用精確查詢或是模糊查詢方式，而Lookup只有模糊查詢，
將上例中設定公式的代碼換成：

row9.CreateCell(1).SetCellFormula("VLOOKUP(A9,$A$2:$C$6,3,TRUE)");

執行后生成的Excel樣式如下：

第一個引數：需要查找的內容，這里是A9單元格；
第二個引數：需要比較的表，這里是$A$2:$C$6，注意VLOOKUP匹配時只與表中的第一列進行匹配，
第三個引數：匹配結果對應的列序號，這里要對應的是稅率列，所以為3，
第四個引數：指明是否模糊匹配，例子中的TRUE表示模糊匹配，與上例中一樣，匹配到的是第三行，如果將此引數改為FALSE，因為在表中的第1列中找不到7800，所以會報“#N/A”的計算錯誤，

另外，還有與VLOKUP類似的HLOOKUP，不同的是VLOOKUP用于在表格或數值陣列的首列查找指定的數值，并由此回傳表格或陣列當前行中指定列處的數值，而HLOOKUP用于在表格或數值陣列的首行查找指定的數值，并由此回傳表格或陣列當前列中指定行處的數值，讀者可以自已去嘗試，

2.3.8用NPOI操作EXCEL－－亂數函式

我們知道，在大多數編程語言中都有亂數函式，在Excel中，同樣存在著這樣一個函式—RAND()函式，用于生成亂數，先來看一個最簡單的例子：

HSSFSheet sheet1 = hssfworkbook.CreateSheet("Sheet1");
sheet1.CreateRow(0).CreateCell(0).SetCellFormula("RAND()");

RAND()函式將回傳一個0-1之間的亂數，執行后生成的Excel檔案如下：

這只是最簡單直接的RAND()函式的應用，只要我們稍加修改，就可以作出很多種變換，如
取0-100之前的隨機整數，可設定公式為：

sheet1.CreateRow(0).CreateCell(0).SetCellFormula("int(RAND()*100)");

取10-20之間的隨機實數，可設定公式為：

sheet1.CreateRow(0).CreateCell(0).SetCellFormula("rand()*(20-10)+10");

隨機小寫字母：

sheet1.CreateRow(0).CreateCell(0).SetCellFormula("CHAR(INT(RAND()*26)+97)");

隨機大寫字母：

sheet1.CreateRow(0).CreateCell(0).SetCellFormula("CHAR(INT(RAND()*26)+65)")

隨機大小寫字母：

sheet1.CreateRow(0).CreateCell(0).SetCellFormula("CHAR(INT(RAND()*26)+if(INT(RAND()*2)=0,65,97))");

上面幾例中除了用到RAND函式以外，還用到了CHAR函式用來將ASCII碼換為字母，INT函式用來取整，值得注意的是INT函式不會四舍五入，無論小數點后是多少都會被舍去，
這里只是RAND函式的幾個簡單應用，還有很多亂數的例子都可以根據這些，再結合不同的其它函式引申出來，

2.3.9用NPOI操作EXCEL－－通過NPOI獲得公式的回傳值

前面我們學習了通過NPOI向Excel中設定公式，那么有些讀者可能會問：“NPOI能不能獲取公式的回傳值呢？”，答案是可以！
一、獲取模板檔案中公式的回傳值
如在D盤中有一個名為text.xls的Excel檔案，其內容如下：

注意C1單元格中設定的是公式“$A1*$B1”，而不是值“12”，利用NPOI，只需要寫簡單的幾句代碼就可以取得此公式的回傳值：

HSSFWorkbook wb = new HSSFWorkbook(new FileStream("d:/test.xls",FileMode.Open));
HSSFCell cell = wb.GetSheet("Sheet1").GetRow(0).GetCell(2);
System.Console.WriteLine(cell.NumericCellValue);

輸出結果為：

可見NPOI成功的“決議”了此.xls檔案中的公式，注意NumericCellValue屬性會自動根據單元格的型別處理，如果為空將返0，如果為數值將回傳數值，如果為公式將回傳公式計算后的結果，單元格的型別可以通過CellType屬性獲取，

二、獲取NPOI生成的Excel檔案中公式的回傳值
上例中是從一個已經存在的Excel檔案中獲取公式的回傳值，那么如果Excel檔案是通過NPOI創建的，直接用上面的方法獲取，可能得不到想要的結果，如：

1 HSSFWorkbook hssfworkbook = new HSSFWorkbook();
2 HSSFSheet sheet1 = hssfworkbook.CreateSheet("Sheet1");
3 HSSFRow row = sheet1.CreateRow(0);
4 row.CreateCell(0).SetCellValue(3);
5 row.CreateCell(1).SetCellValue(4);
6 HSSFCell cell = row.CreateCell(2);
7
8 cell.SetCellFormula("$A1+$B1");
9 System.Console.WriteLine(cell.NumericCellValue);

執行上面代碼，將輸出結果“0”，而不是我們想要的結果“7”，那么將如何解決呢？這時要用到HSSFFormulaEvaluator類，在第8行后加上這兩句就可以了：

HSSFFormulaEvaluator e = new HSSFFormulaEvaluator(hssfworkbook);
cell = e.EvaluateInCell(cell);

運行結果如下：

2.4.1用NPOI操作EXCEL－－畫線

之所有說NPOI強大，是因為常用的Excel操作她都可以通過編程的方式完成，這節開始，我們開始學習NPOI的畫圖功能，先從最簡單的開始，畫一條直線：

對應的代碼為：

HSSFSheet sheet1 = hssfworkbook.CreateSheet("Sheet1");
HSSFPatriarch patriarch = sheet1.CreateDrawingPatriarch();
HSSFClientAnchor a1 = new HSSFClientAnchor(255, 125, 1023, 150, 0, 0,2, 2);
HSSFSimpleShape line1 = patriarch.CreateSimpleShape(a1);

line1.ShapeType = HSSFSimpleShape.OBJECT_TYPE_LINE;
line1.LineStyle = HSSFShape.LINESTYLE_SOLID;
//在NPOI中線的寬度12700表示1pt,所以這里是0.5pt粗的線條，
line1.LineWidth = 6350;

通常，利用NPOI畫圖主要有以下幾個步驟：
1. 創建一個Patriarch；
2. 創建一個Anchor，以確定圖形的位置；
3. 呼叫Patriarch創建圖形；
4. 設定圖形型別(直線，矩形，圓形等)及樣式（顏色，粗細等），

關于HSSFClientAnchor(dx1,dy1,dx2,dy2,col1,row1,col2,row2)的引數，有必要在這里說明一下：
dx1：起始單元格的x偏移量，如例子中的255表示直線起始位置距A1單元格左側的距離；
dy1：起始單元格的y偏移量，如例子中的125表示直線起始位置距A1單元格上側的距離；
dx2：終止單元格的x偏移量，如例子中的1023表示直線起始位置距C3單元格左側的距離；
dy2：終止單元格的y偏移量，如例子中的150表示直線起始位置距C3單元格上側的距離；
col1：起始單元格列序號，從0開始計算；
row1：起始單元格行序號，從0開始計算，如例子中col1=0,row1=0就表示起始單元格為A1；
col2：終止單元格列序號，從0開始計算；
row2：終止單元格行序號，從0開始計算，如例子中col2=2,row2=2就表示起始單元格為C3；

最后，關于LineStyle屬性，有如下一些可選值，對應的效果分別如圖所示：

2.4.2用NPOI操作EXCEL－－畫矩形

上一節我們講了NPOI中畫圖的基本步驟：
1. 創建一個Patriarch；
2. 創建一個Anchor，以確定圖形的位置；
3. 呼叫Patriarch創建圖形；
4. 設定圖形型別(直線，矩形，圓形等)及樣式（顏色，粗細等），

這一節我們將按照這個步驟創建一個矩形，廢話少說，上代碼：

HSSFSheet sheet1 = hssfworkbook.CreateSheet("Sheet1");
HSSFPatriarch patriarch = sheet1.CreateDrawingPatriarch();
HSSFClientAnchor a1 = new HSSFClientAnchor(255, 125, 1023, 150, 0, 0, 2, 2);
HSSFSimpleShape rec1 = patriarch.CreateSimpleShape(a1);
//此處設定圖形型別為矩形
rec1.ShapeType = HSSFSimpleShape.OBJECT_TYPE_RECTANGLE;
//設定填充色
rec1.SetFillColor(125, 125, 125);
//設定邊框樣式
rec1.LineStyle = HSSFShape.LINESTYLE_DASHGEL;
//設定邊框寬度
rec1.LineWidth = 25400;
//設定邊框顏色
rec1.SetLineStyleColor(100, 0, 100);

代碼執行效果：

其中SetFillColor和SetLineStyleColor函式的三個引數分別是RGB三色值，具體表示什么顏色，找個Photoshop試試：）
關于HSSFClientAnchor引數說明、邊框樣式，邊框寬度的說明可以參見前一篇博文：
http://www.cnblogs.com/atao/archive/2009/09/13/1565645.html

2.4.3用NPOI操作EXCEL－－畫圓形

前面我們學習了NPOI中的畫簡單直線和矩形的功能，今天我們一起學習一下它支持的另一種簡單圖形－－圓形，同樣，按照前面所講的繪圖“四步曲”：
1. 創建一個Patriarch；
2. 創建一個Anchor，以確定圖形的位置；
3. 呼叫Patriarch創建圖形；
4. 設定圖形型別(直線，矩形，圓形等)及樣式（顏色，粗細等），
還是以例子加以說明：

HSSFSheet sheet1 = hssfworkbook.CreateSheet("Sheet1");
HSSFPatriarch patriarch = sheet1.CreateDrawingPatriarch();
HSSFClientAnchor a1 = new HSSFClientAnchor(0, 0, 1023, 0, 0, 0, 1, 3);
HSSFSimpleShape rec1 = patriarch.CreateSimpleShape(a1);
rec1.ShapeType = HSSFSimpleShape.OBJECT_TYPE_OVAL;

rec1.SetFillColor(125, 125, 125);
rec1.LineStyle = HSSFShape.LINESTYLE_DASHGEL;
rec1.LineWidth = 12700;
rec1.SetLineStyleColor(100, 0, 100);
WriteToFile();

這里rec1.ShapeType =HSSFSimpleShape.OBJECT_TYPE_OVAL;表示圖形為橢圓，適當調整HSSFClientAnchor的各引數可以得到圓形，
關于HSSFClientAnchor建構式和邊框、填充色等前兩節都有介紹，這里不再重述，詳情情見：畫矩形和畫線，

上面代碼執行生成的Excel如下：

2.4.4用NPOI操作EXCEL－－畫Grid

　　在NPOI中，本身沒有畫Grid的方法，但我們知道Grid其實就是由橫線和豎線構成的，所在我們可以通過畫線的方式來模擬畫Grid，

HSSFSheet sheet1 = hssfworkbook.CreateSheet("Sheet1");

HSSFRow row = sheet1.CreateRow(2);
row.CreateCell(1);
row.HeightInPoints = 240;
sheet1.SetColumnWidth(2, 9000);
int linesCount = 20;

HSSFPatriarch patriarch = sheet1.CreateDrawingPatriarch();
//因為HSSFClientAnchor中dx只能在0-1023之間,dy只能在0-255之間，所以這里采用比例的方式
double xRatio = 1023.0 / (linesCount*10);
double yRatio = 255.0 / (linesCount*10);

//畫豎線
int x1 = 0;
int y1 = 0;
int x2 = 0;
int y2 = 200;
for (int i = 0; i < linesCount; i++)
{
    HSSFClientAnchor a2 = new HSSFClientAnchor();
    a2.SetAnchor((short)2, 2, (int)(x1 * xRatio), (int)(y1 * yRatio),
            (short)2, 2, (int)(x2 * xRatio), (int)(y2 * yRatio));
    HSSFSimpleShape shape2 = patriarch.CreateSimpleShape(a2);
    shape2.ShapeType = (HSSFSimpleShape.OBJECT_TYPE_LINE);

    x1 += 10;
    x2 += 10;
}

//畫橫線
x1 = 0;
y1 = 0;
x2 = 200;
y2 = 0;
for (int i = 0; i < linesCount; i++)
{
    HSSFClientAnchor a2 = new HSSFClientAnchor();
    a2.SetAnchor((short)2, 2, (int)(x1 * xRatio), (int)(y1 * yRatio),
            (short)2, 2, (int)(x2 * xRatio), (int)(y2 * yRatio));
    HSSFSimpleShape shape2 = patriarch.CreateSimpleShape(a2);
    shape2.ShapeType = (HSSFSimpleShape.OBJECT_TYPE_LINE);

    y1 += 10;
    y2 += 10;
}

請注意HSSFClientAnchor物件中的dx只能取0-1023之間的數，dy只能取0-255之間的數，我們可以理解為是將單元格的寬和高平分成了1023和255份，設定dx和dy時相當于按比例取對應的座標，最終生成的Excel如下：

2.4.5用NPOI操作EXCEL－－插入圖片

我們知道，在Excel中是可以插入圖片的，操作選單是“插入－>圖片”，然后選擇要插入圖片，可以很容易地在Excel插入圖片，同樣，在NPOI中，利用代碼也可以實作同樣的效果，在NPOI中插入圖片的方法與畫圖的方法有點類似：

//add picture data to this workbook.
byte[] bytes = System.IO.File.ReadAllBytes(@"D:\MyProject\NPOIDemo\ShapeImage\image1.jpg");
int pictureIdx = hssfworkbook.AddPicture(bytes, HSSFWorkbook.PICTURE_TYPE_JPEG);

//create sheet
HSSFSheet sheet = hssfworkbook.CreateSheet("Sheet1");

// Create the drawing patriarch. This is the top level container for all shapes.
HSSFPatriarch patriarch = sheet.CreateDrawingPatriarch();

//add a picture
HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 1023, 0, 0, 0, 1, 3);
HSSFPicture pict = patriarch.CreatePicture(anchor, pictureIdx);

與畫簡單圖形不同的是，首先要將圖片讀入到byte陣列，然后添加到workbook中；最后呼叫的是patriarch.CreatePicture(anchor, pictureIdx)方法顯示圖片，而不是patriarch.CreateSimpleShape(anchor)方法，上面這段代碼執行后生成的Excel檔案樣式如下：

我們發現，插入的圖片被拉伸填充在HSSFClientAnchor指定的區域，有時可能我們并不需要拉伸的效果，怎么辦呢？很簡單，在最后加上這樣一句用來自動調節圖片大小：

pict.Resize();

添加代碼后再執行上述代碼，生成的Excel樣式如下：

圖片已經自動伸縮到原始大小了，

NPOI 1.2教程 - 2.5 列印相關設定

作者：Tony Qu

NPOI官方網站：http://npoi.codeplex.com/

列印設定主要包括方向設定、縮放、紙張設定、頁邊距等，NPOI 1.2支持大部分列印屬性，能夠讓你輕松滿足客戶的列印需要，

首先是方向設定，Excel支持兩種頁面方向，即縱向和橫向，

在NPOI中如何設定呢？你可以通過HSSFSheet.PrintSetup.Landscape來設定，Landscape是布爾型別的，在英語中是橫向的意思，如果Landscape等于true，則表示頁面方向為橫向；否則為縱向，

接著是縮放設定，

這里的縮放比例對應于HSSFSheet.PrintSetup.Scale，而頁寬和頁高分別對應于HSSFSheet.PrintSetup.FitWidth和HSSFSheet.PrintSetup.FitHeight，要注意的是，這里的PrintSetup.Scale應該被設定為0-100之間的值，而不是小數，

接下來就是紙張設定了，對應于HSSFSheet.PrintSetup.PaperSize，但這里的PaperSize并不是隨便設定的，而是由一些固定的值決定的，具體的值與對應的紙張如下表所示：

值	紙張
1	US Letter 8 1/2 x 11 in
2	US Letter Small 8 1/2 x 11 in
3	US Tabloid 11 x 17 in
4	US Ledger 17 x 11 in
5	US Legal 8 1/2 x 14 in
6	US Statement 5 1/2 x 8 1/2 in
7	US Executive 7 1/4 x 10 1/2 in
8	A3 297 x 420 mm
9	A4 210 x 297 mm
10	A4 Small 210 x 297 mm
11	A5 148 x 210 mm
12	B4 (JIS) 250 x 354
13	B5 (JIS) 182 x 257 mm
14	Folio 8 1/2 x 13 in
15	Quarto 215 x 275 mm
16	10 x 14 in
17	11 x 17 in
18	US Note 8 1/2 x 11 in
19	US Envelope #9 3 7/8 x 8 7/8
20	US Envelope #10 4 1/8 x 9 1/2
21	US Envelope #11 4 1/2 x 10 3/8
22	US Envelope #12 4 \276 x 11
23	US Envelope #14 5 x 11 1/2
24	C size sheet
25	D size sheet
26	E size sheet
27	Envelope DL 110 x 220mm
28	Envelope C5 162 x 229 mm
29	Envelope C3 324 x 458 mm
30	Envelope C4 229 x 324 mm
31	Envelope C6 114 x 162 mm
32	Envelope C65 114 x 229 mm
33	Envelope B4 250 x 353 mm
34	Envelope B5 176 x 250 mm
35	Envelope B6 176 x 125 mm
36	Envelope 110 x 230 mm
37	US Envelope Monarch 3.875 x 7.5 in
38	6 3/4 US Envelope 3 5/8 x 6 1/2 in
39	US Std Fanfold 14 7/8 x 11 in
40	German Std Fanfold 8 1/2 x 12 in
41	German Legal Fanfold 8 1/2 x 13 in
42	B4 (ISO) 250 x 353 mm
43	Japanese Postcard 100 x 148 mm
44	9 x 11 in
45	10 x 11 in
46	15 x 11 in
47	Envelope Invite 220 x 220 mm
48	RESERVED--DO NOT USE
49	RESERVED--DO NOT USE
50	US Letter Extra 9 \275 x 12 in
51	US Legal Extra 9 \275 x 15 in
52	US Tabloid Extra 11.69 x 18 in
53	A4 Extra 9.27 x 12.69 in
54	Letter Transverse 8 \275 x 11 in
55	A4 Transverse 210 x 297 mm
56	Letter Extra Transverse 9\275 x 12 in
57	SuperA/SuperA/A4 227 x 356 mm
58	SuperB/SuperB/A3 305 x 487 mm
59	US Letter Plus 8.5 x 12.69 in
60	A4 Plus 210 x 330 mm
61	A5 Transverse 148 x 210 mm
62	B5 (JIS) Transverse 182 x 257 mm
63	A3 Extra 322 x 445 mm
64	A5 Extra 174 x 235 mm
65	B5 (ISO) Extra 201 x 276 mm
66	A2 420 x 594 mm
67	A3 Transverse 297 x 420 mm
68	A3 Extra Transverse 322 x 445 mm
69	Japanese Double Postcard 200 x 148 mm
70	A6 105 x 148 mm
71	Japanese Envelope Kaku #2
72	Japanese Envelope Kaku #3
73	Japanese Envelope Chou #3
74	Japanese Envelope Chou #4
75	Letter Rotated 11 x 8 1/2 11 in
76	A3 Rotated 420 x 297 mm
77	A4 Rotated 297 x 210 mm
78	A5 Rotated 210 x 148 mm
79	B4 (JIS) Rotated 364 x 257 mm
80	B5 (JIS) Rotated 257 x 182 mm
81	Japanese Postcard Rotated 148 x 100 mm
82	Double Japanese Postcard Rotated 148 x 200 mm
83	A6 Rotated 148 x 105 mm
84	Japanese Envelope Kaku #2 Rotated
85	Japanese Envelope Kaku #3 Rotated
86	Japanese Envelope Chou #3 Rotated
87	Japanese Envelope Chou #4 Rotated
88	B6 (JIS) 128 x 182 mm
89	B6 (JIS) Rotated 182 x 128 mm
90	12 x 11 in
91	Japanese Envelope You #4
92	Japanese Envelope You #4 Rotated
93	PRC 16K 146 x 215 mm
94	PRC 32K 97 x 151 mm
95	PRC 32K(Big) 97 x 151 mm
96	PRC Envelope #1 102 x 165 mm
97	PRC Envelope #2 102 x 176 mm
98	PRC Envelope #3 125 x 176 mm
99	PRC Envelope #4 110 x 208 mm
100	PRC Envelope #5 110 x 220 mm
101	PRC Envelope #6 120 x 230 mm
102	PRC Envelope #7 160 x 230 mm
103	PRC Envelope #8 120 x 309 mm
104	PRC Envelope #9 229 x 324 mm
105	PRC Envelope #10 324 x 458 mm
106	PRC 16K Rotated
107	PRC 32K Rotated
108	PRC 32K(Big) Rotated
109	PRC Envelope #1 Rotated 165 x 102 mm
110	PRC Envelope #2 Rotated 176 x 102 mm
111	PRC Envelope #3 Rotated 176 x 125 mm
112	PRC Envelope #4 Rotated 208 x 110 mm
113	PRC Envelope #5 Rotated 220 x 110 mm
114	PRC Envelope #6 Rotated 230 x 120 mm
115	PRC Envelope #7 Rotated 230 x 160 mm
116	PRC Envelope #8 Rotated 309 x 120 mm
117	PRC Envelope #9 Rotated 324 x 229 mm
118	PRC Envelope #10 Rotated 458 x 324 mm

（此表摘自《Excel Binary File Format (.xls) Structure Specification.pdf》）

HSSFSheet下面定義了一些xxxx_PAPERSIZE的常量，但都是非常常用的紙張大小，如果滿足不了你的需要，可以根據上表自己給PaperSize屬性賦值，所以，如果你要設定紙張大小可以用這樣的代碼：

HSSFSheet.PrintSetup.PaperSize=HSSFSheet.A4_PAPERSIZE;

或

HSSFSheet.PrintSetup.PaperSize=9;(A4 210*297mm)

再下來就是列印的起始頁碼，它對應于HSSFSheet.PrintSetup.PageStart和HSSFSheet.PrintSetup.UsePage，如果UsePage=false，那么就相當于“自動”，這時PageStart不起作用；如果UsePage=true，PageStart才會起作用，所以在設定PageStart之前，必須先把UsePage設定為true，

“列印”欄中的“網格線”設定對應于HSSFSheet.IsPrintGridlines，請注意，這里不是HSSFSheet.PrintSetup下面，所以別搞混了，這里之所以不隸屬于PrintSetup是由底層存盤該資訊的record決定的，底層是把IsGridsPrinted放在GridsetRecord里面的，而不是PrintSetupRecord里面的，盡管界面上是放在一起的，另外還有一個HSSFSheet.IsGridsPrinted屬性，這個屬性對應于底層的gridset Record，但這個record是保留的，從微軟的檔案顯示沒有任何意義，所以這個屬性請不要去設定，

“單色列印”則對應于HSSFSheet.PrintSetup.NoColors，這是布爾型別的，值為true時，表示單色列印，

“草稿品質”對應于HSSFSheet.PrintSetup.IsDraft，也是布爾型別的，值為true時，表示用草稿品質列印，

這里的列印順序是由HSSFSheet.PrintSetup.LeftToRight決定的，它是布爾型別的，當為true時，則表示“先行后列”；如果是false，則表示“先列后行”，

在NPOI 1.2中，“行號列標”、“批注”和“錯誤單元格列印為”、“頁邊距”暫不支持，將在以后的版本中支持，

有關列印的范例可以參考NPOI 1.2正式版中的 SetPrintSettingsInXls專案，

2.6.1用NPOI操作EXCEL－－調整表單顯示比例

在Excel中，可以通過調整右下角的滾動條來調整Sheet的顯示比例，如圖：

在NPOI中，也能通過代碼實作這樣的功能，并且代碼非常簡單：

HSSFSheet sheet1 = hssfworkbook.CreateSheet("Sheet1");
sheet1.CreateRow(0).CreateCell(0).SetCellValue("This is a test.");
//50% zoom
sheet1.SetZoom(1,2);

我們發現，SetZoom有兩個引數，其中第一個引數表示縮放比例的分子，第二個引數表示縮放比例的分母，所以SetZoom(1,2)就表示縮小到1/2，也就是50%，代碼執行后生成的Excel樣式如下：

如果將SetZoom的引數改成(2,1)，代碼執行后生成的Excel樣式如下，表示擴大兩倍：

2.6.2用NPOI操作EXCEL－－設定密碼

有時，我們可能需要某些單元格只讀，如在做模板時，模板中的資料是不能隨意讓別人改的，在Excel中，可以通過“審閱->保護作業表”來完成，如下圖：

那么，在NPOI中有沒有辦法通過編碼的方式達到這一效果呢？答案是肯定的，

HSSFSheet sheet1 = hssfworkbook.CreateSheet("Sheet1");

HSSFRow row1 = sheet1.CreateRow(0);
HSSFCell cel1 = row1.CreateCell(0);
HSSFCell cel2 = row1.CreateCell(1);

HSSFCellStyle unlocked = hssfworkbook.CreateCellStyle();
unlocked.IsLocked = false;

HSSFCellStyle locked = hssfworkbook.CreateCellStyle();
locked.IsLocked = true;

cel1.SetCellValue("沒被鎖定");
cel1.CellStyle = unlocked;

cel2.SetCellValue("被鎖定");
cel2.CellStyle = locked;

sheet1.ProtectSheet("password");

正如代碼中所看到的，我們通過設定CellStype的ISLocked為True，表示此單元格將被鎖定，相當于在Excel中執行了如下操作：

然后通過ProtectSheet設定密碼，

執行結果如下：

沒被鎖定的列可以任意修改，

被鎖定的列不能修改，

輸入密碼可以解除鎖定，

NPOI 1.2教程 - 組合行、列

作者：Tony Qu

NPOI官方網站：http://npoi.codeplex.com/

Excel 2007中有一個面板是專門用于設定組合功能的，叫做“分級顯示”面板，如下所示：

可能我們在過去生成Excel檔案的時候根本不會用這個功能，也沒辦法用，因為cvs法和html法沒辦法控制這些東西，這里簡單的介紹一下什么叫做組合：

組合分為行組合和列組合，所謂行組合，就是讓n行組合成一個集合，能夠進行展開和合攏操作，在Excel中顯示如下：

圖中左側就是用于控制行組合折疊的圖示，圖中上部就是用于控制列組合的，是不是有點像TreeView中的折疊節點？很多時候由于資料太多，為了讓用戶對于大量資料一目了然，我們可以使用行列組合來解決顯示大綱，這和Visual Studio 里面的region的概念是類似的，

細心的朋友可能已經注意到了，我們其實可以對一行做多次組合操作，這就是分級顯示的概念，圖中就把行2-3分為2個組合，第2行到第4行為一個組合，第2行到第5行一個組合，所以是分兩級，

在NPOI中，要實作分組其實并不難，你只需要呼叫HSSFSheet.GroupRow和HSSFSheet.GroupColumn這兩個方法就可以了，

首先我們來看HSSFSheet.GroupRow，GroupRow有2個引數，分別是fromRow和toRow，表示起始行號和結束行號，這些行號都是從0開始算起的，

HSSFWorkbookhssfworkbook =newHSSFWorkbook();

HSSFSheet s =hssfworkbook.CreateSheet("Sheet1");

s.GroupRow(1,3);

上面的代碼把第2行到第4行做了組合，

要組合列，其實代碼很相似，如下所示：

s.GroupColumn(1,3)

上面的代碼把B至D列做了組合，

正如上圖中Excel的“分級顯示”面板所示，有“組合”，也一定有“取消組合”，NPOI中你可以用HSSFSheet.UngroupRow和HSSFSheet.UngroupColumn，引數和GroupXXX是一樣的，如果要取消第2到第4行的組合，就可以用下面的代碼：

s.UngroupColumn(1,3)

相關范例請見NPOI 1.2正式版中的GroupRowAndColumnInXls專案，

2.6.4用NPOI操作EXCEL－－鎖定列

在Excel中，有時可能會出現列數太多或是行數太多的情況，這時可以通過鎖定列來凍結部分列，不隨滾動潭訓動，方便查看，在Excel中設定凍結列的方法如下：

同樣，利用NPOI，通過代碼也能實作上面的效果：

HSSFSheet sheet1 = hssfworkbook.CreateSheet("Sheet1");
HSSFRow row1 = sheet1.CreateRow(0);
row1.CreateCell(0).SetCellValue("凍結列");
sheet1.CreateFreezePane(1, 0, 1, 0);

代碼執行結果如下：

下面對CreateFreezePane的引數作一下說明：
第一個引數表示要凍結的列數；
第二個引數表示要凍結的行數，這里只凍結列所以為0；
第三個引數表示右邊區域可見的首列序號，從1開始計算；
第四個引數表示下邊區域可見的首行序號，也是從1開始計算，這里是凍結列，所以為0；

舉例說明也許更好理解，將各引數設定為如下：

sheet1.CreateFreezePane(2,0,5,0);

得到的效果如下圖：

注意圖中C、D和E列默認是看不到的，滾動才看得到，這就是第三個引數5起了作用，是不是很好理解了呢：）

接下來，看一下凍結行的效果，將上面的代碼稍作修改：

HSSFSheet sheet1 = hssfworkbook.CreateSheet("Sheet1");
HSSFRow row1 = sheet1.CreateRow(0);
row1.CreateCell(0).SetCellValue("凍結行");
sheet1.CreateFreezePane(0, 1, 0, 1);

執行后生成的Excel檔案效果見下圖：

那么，如果要行和列同時凍結該怎么做呢？聰明的你一定能想得到，呵呵~~

NPOI 1.2教程 - 顯示/隱藏Excel網格線

作者：Tony Qu

NPOI官方網站：http://npoi.codeplex.com/

有些時候，我們需要網格線，而有些時候我們不需要，這取決于實際的業務需求，前兩天inmegin兄就問我，怎么把網格給去掉，因為他們要把Excel檔案當Word使，也許是因為Excel排版方便吧，

Excel中的網格線設定是以表（Sheet）為單位進行管理的，這也就意味著你可以讓一個表顯示網格線，而另一個表不顯示，這是不沖突的，

在Excel 2007中，我們通常用“作業表選項”面板來設定這個屬性:

在面板中，你會發現有2個多選框，一個是查看，一個是列印，也就是說Excel是把查看和列印網格線作為兩個設定來處理的，存盤的Record也是不同的，

在NPOI中，如果要讓網格線在查看時顯示/隱藏，你可以HSSFSheet.DisplayGridlines屬性，默認值為true（這也是為什么默認情況下我們能夠看到網格線），下面的代碼就是讓網格線在查看時不可見的：

HSSFWorkbookhssfworkbook =newHSSFWorkbook();

HSSFSheet s1= hssfworkbook.CreateSheet("Sheet1");

s1.DisplayGridlines=false;

如果要在列印時顯示/隱藏網格線，你可以用HSSFSheet.IsGridlinesPrinted屬性，默認值為false（這就是默認情況下列印看不到網格線的原因），代碼和上面差不多：

s1.IsGridsPrinted=true;

上面的代碼將在列印時顯示網格線，列印的效果如下所示，

在此也提醒大家，如果這個Excel最終客戶有列印意向，可別忘了把IsGridPrinted屬性也設定上，

相關范例可以參考NPOI 1.2正式版中的DisplayGridlinesInXls專案，

2.6.6用NPOI操作EXCEL－－設定初始視圖的行、列

有些時候，我們可能希望生成的Excel檔案在被打開的時候自動將焦點定位在某個單元格或是選中某個區域中，在NPOI中可以通過SetAsActiveCell和SetActiveCellRange等幾個方法實作，

首先我們看一下設定初始視圖中選中某個單元格的方法：

//use HSSFCell.SetAsActiveCell() to select B6 as the active column
HSSFSheet sheet1 = hssfworkbook.CreateSheet("Sheet A");
CreateCellArray(sheet1);
sheet1.GetRow(5).GetCell(1).SetAsActiveCell();
//set TopRow and LeftCol to make B6 the first cell in the visible area
sheet1.TopRow = 5;
sheet1.LeftCol = 1;

其中CreateCellArray(sheet1)方法用來寫示范資料，其代碼為（下同）：

static void CreateCellArray(HSSFSheet sheet)
{
    for (int i = 0; i < 300; i++)
    {
        HSSFRow row=sheet.CreateRow(i);
        for (int j = 0; j < 150; j++)
        {
            HSSFCell cell = row.CreateCell(j);
            cell.SetCellValue(i*j);
        }
    }
}

生成的Excel打開時效果如下，注意B6為默認選中狀態，TopRow和LeftCol設定B6為當前可見區域的第一個單元格：

如果不設定TopRow和LeftCol屬性，默認的可見域的第一個單元格為A1，如下是另一種設定活動單元格的方法，但沒有設定此Sheet的TopRow和LeftCol：

HSSFSheet sheet2 = hssfworkbook.CreateSheet("Sheet B");
sheet2.Sheet.SetActiveCell(1, 5);

對應生成的Excel顯示為:

除了設定某個單元格為選中狀態外，還NPOI可以設定某個區域為選中狀態：

//use Sheet.SetActiveCellRange to select a cell range
HSSFSheet sheet3 = hssfworkbook.CreateSheet("Sheet C");
CreateCellArray(sheet3);
sheet3.Sheet.SetActiveCellRange(2, 5, 1, 5);

以上代碼設定了Sheet C的選中區域為B3:F6：

還有更強大的，設定多個選中區域：

//use Sheet.SetActiveCellRange to select multiple cell ranges
HSSFSheet sheet4 = hssfworkbook.CreateSheet("Sheet D");
CreateCellArray(sheet4);
List<CellRangeAddress8Bit> cellranges = new List<CellRangeAddress8Bit>();
cellranges.Add(new CellRangeAddress8Bit(1,3,2,5));
cellranges.Add(new CellRangeAddress8Bit(6,7,8,9));
sheet4.Sheet.SetActiveCellRange(cellranges,1,6,9);

如果一個Excel檔案中有多個Sheet，還可以通過如下陳述句設定打開時的初始Sheet：

hssfworkbook.ActiveSheetIndex = 2;

2.6.7用NPOI操作EXCEL－－資料有效性

在有些情況下（比如Excel引入），我們可能不允許用戶在Excel隨意輸入一些無效資料，這時就要在模板中加一些資料有效性的驗證，在Excel中，設定資料有效性的方步驟如下：
（1）先選定一個區域；
資料有效性”中設定資料有效性驗證（如圖），à（2）在選單“資料

同樣，利用NPOI，用代碼也可以實作：

HSSFSheet sheet1 = hssfworkbook.CreateSheet("Sheet1");

sheet1.CreateRow(0).CreateCell(0).SetCellValue("日期列");
CellRangeAddressList regions1 = new CellRangeAddressList(1, 65535, 0, 0);
DVConstraint constraint1 = DVConstraint.CreateDateConstraint(DVConstraint.OperatorType.BETWEEN, "1900-01-01", "2999-12-31", "yyyy-MM-dd");
HSSFDataValidation dataValidate1 = new HSSFDataValidation(regions1, constraint1);
dataValidate1.CreateErrorBox("error", "You must input a date.");
sheet1.AddValidationData(dataValidate1);

上面是一個在第一列要求輸入1900-1-1至2999-12-31之間日期的有效性驗證的例子，生成的Excel效果如下，當輸入非法時將給出警告：

下面對剛才用到的幾個方法加以說明：
CellRangeAddressList類表示一個區域，建構式中的四個引數分別表示起始行序號，終止行序號，起始列序號，終止列序號，所以第一列所在區域就表示為：

//所有序號都從零算起，第一行標題行除外，所以第一個引數是1，65535是一個Sheet的最大行數
new CellRangeAddressList(1, 65535, 0, 0);

另外，CreateDateConstraint的第一個引數除了設定成DVConstraint.OperatorType.BETWEEN外，還可以設定成如下一些值，大家可以自己一個個去試看看效果：

最后，dataValidate1.CreateErrorBox(title,text)，用來創建出錯時的提示資訊，第一個引數表示提示框的標題，第二個引數表示提示框的內容，

理解了上面這些，創建一個整數型別的有效性驗證也不難實作：

sheet1.CreateRow(0).CreateCell(1).SetCellValue("數值列");
CellRangeAddressList regions2 = new CellRangeAddressList(1, 65535, 1, 1);
DVConstraint constraint2 = DVConstraint.CreateNumericConstraint(DVConstraint.ValidationType.INTEGER,DVConstraint.OperatorType.BETWEEN, "0", "100");
HSSFDataValidation dataValidate2 = new HSSFDataValidation(regions2, constraint2);
dataValidate2.CreateErrorBox("error", "You must input a numeric between 0 and 100.");
sheet1.AddValidationData(dataValidate2);

生成的Excel效果為：

下一節我們將學習利用資料有效性創建下拉串列的例子，

2.6.8用NPOI操作EXCEL－－生成下拉串列

上一節我們講了簡單的資料有效性驗證，這一節我們學習一下資料有效性的另一個應用－－下拉串列，在Excel中，并沒有類似Web中的下拉控制元件，其下拉效果是通過資料有效性來實作的，設定步驟為：
（1）選定一個要生成下拉串列的區域；
（2）設定資料有效性為序列，并在來源中填充可選下拉的值，用“,”隔開（如圖），

對應的效果為：

同樣，利用NPOI代碼也可以實作上面的效果：

HSSFSheet sheet1 = hssfworkbook.CreateSheet("Sheet1");

CellRangeAddressList regions = new CellRangeAddressList(0, 65535, 0, 0);
DVConstraint constraint = DVConstraint.CreateExplicitListConstraint(new string[] { "itemA", "itemB", "itemC" });
HSSFDataValidation dataValidate = new HSSFDataValidation(regions, constraint);
sheet1.AddValidationData(dataValidate);

下面對代碼作一下簡要說明：
先設定一個需要提供下拉的區域，關于CellRangeAddressList建構式引數的說明請參見上一節：

CellRangeAddressList regions = new CellRangeAddressList(0, 65535, 0, 0);

然后將下拉項作為一個陣列傳給CreateExplicitListConstraint作為引數創建一個約束，根據要控制的區域和約束創建資料有效性就可以了，

但是這樣會有一個問題：Excel中允許輸入的序列來源長度最大為255個字符，也就是說當下拉項的總字串長度超過255是將會出錯，那么如果下拉項很多的情況下應該怎么處理呢？答案是通過參考的方式，步驟如下：
先創建一個Sheet專門用于存盤下拉項的值，并將各下拉項的值寫入其中：

HSSFSheet sheet2 = hssfworkbook.CreateSheet("ShtDictionary");
sheet2.CreateRow(0).CreateCell(0).SetCellValue("itemA");
sheet2.CreateRow(1).CreateCell(0).SetCellValue("itemB");
sheet2.CreateRow(2).CreateCell(0).SetCellValue("itemC");

然后定義一個名稱，指向剛才創建的下拉項的區域：

HSSFName range = hssfworkbook.CreateName();
range.Reference = "ShtDictionary!$A1:$A3";
range.NameName = "dicRange";

最后，設定資料約束時指向這個名稱而不是字符陣列：

HSSFSheet sheet1 = hssfworkbook.CreateSheet("Sheet1");
CellRangeAddressList regions = new CellRangeAddressList(0, 65535, 0, 0);

DVConstraint constraint = DVConstraint.CreateFormulaListConstraint("dicRange");
HSSFDataValidation dataValidate = new HSSFDataValidation(regions, constraint);
sheet1.AddValidationData(dataValidate);

執行這段代碼，生成的Excel效果如下：

在名稱管理器中會發現有一個名為"dicRange"的名稱，指向"ShtDictionary!$A1:$A3"的下拉項區域：

在資料有效性中會發現來源變成了"=dicRange"，指向上面定義的名稱，而不是以前的"itemA,itemB,itemC"：

NPOI 1.2教程 – 3.1 基于.xls模板生成Excel檔案

作者：Tony Qu

NPOI官方網站：http://npoi.codeplex.com/

NPOI QQ交流群: 78142590

盡管NPOI能夠從頭開始生成Excel檔案，但在實際生產環境中有很多現成的表格，我們不可能沒事就去從頭開始生成一個Excel，更多時候我們更愿意選擇比較偷懶的方法——那就是用模板檔案，NPOI一大特色之一就是能夠輕松讀取Office Excel 97-2003的格式，即使里面有NPOI不支持的VBA宏、圖表以及Pivot表之類的高級記錄，NPOI也能夠保證不丟失資料（說實話，要完全能夠識別所有的Excel內部記錄幾乎是不可能的，更何況如今又多出了Office Excel 2007binary file，即.xlsb），

現在我們轉入正題，出于演示目的，我做了一個簡單的銷售量表，里面應用了文字顏色、背景色、文本居中、公式、千分位分隔符、邊框等效果，當然實際的生產環境里可能還有更加復雜的Excel模板，如下圖

我們的程式就是要填充12個月的銷售量，Total能夠自動根據填充的值計算出總量，

（這里要提一下，以往如果我們用HTML方式輸出xls，我們必須在服務器端做Total計算，并且這個值在下載后永遠都是靜態的，沒有公式，即使用戶要修改里面的資料，總值也不會改變，這也是為什么NPOI一直提倡生成真正的Excel檔案，）

代碼其實很簡單：

//read thetemplate via FileStream, it is suggested to use FileAccess.Read to prevent filelock.

//book1.xlsis an Excel-2007-generated file, so some new unknown BIFF records are added.

FileStreamfile =new FileStream(@"template/book1.xls", FileMode.Open,FileAccess.Read);

HSSFWorkbook hssfworkbook = new HSSFWorkbook(file);
HSSFSheet sheet1 = hssfworkbook.GetSheet("Sheet1");
sheet1.GetRow(1).GetCell(1).SetCellValue(200200);
sheet1.GetRow(2).GetCell(1).SetCellValue(300);
sheet1.GetRow(3).GetCell(1).SetCellValue(500050);
sheet1.GetRow(4).GetCell(1).SetCellValue(8000);
sheet1.GetRow(5).GetCell(1).SetCellValue(110);
sheet1.GetRow(6).GetCell(1).SetCellValue(100);
sheet1.GetRow(7).GetCell(1).SetCellValue(200);
sheet1.GetRow(8).GetCell(1).SetCellValue(210);
sheet1.GetRow(9).GetCell(1).SetCellValue(2300);
sheet1.GetRow(10).GetCell(1).SetCellValue(240);
sheet1.GetRow(11).GetCell(1).SetCellValue(180123);
sheet1.GetRow(12).GetCell(1).SetCellValue(150);

//Force excel to recalculate all the formulawhile open

sheet1.ForceFormulaRecalculation=true;

FileStreamfile = new FileStream(@"test.xls", FileMode.Create);
hssfworkbook.Write(file);
file.Close();

首先打開模板檔案時要使用FileAccess.Read，這樣可以保證檔案不被占用，

這里的ForceFormulaRecalculation是強制要求Excel在打開時重新計算的屬性，在擁有公式的xls檔案中十分有用，大家使用時可別忘了設，

是不是比你想象的簡單？你甚至不用去了解它是在何時讀取檔案內容的，對于NPOI的使用者來說基本上和讀取普通檔案沒有什么兩樣，

最終生成的效果如下所示：

發覺沒，就連千分位分隔符也都保留著，一切就像人工填寫的一樣，

本范例完整代碼請見NPOI.Examples中的GenerateXlsFromXlsTemplate專案，

3.2用NPOI操作EXCEL－－生成九九乘法表

還記得小學時候學的九九乘法表嗎？這節我們一起學習利用NPOI通過C#代碼生成一張Excel的九九乘法表，要生成九九乘法表，回圈肯定是少不了的，如下：

HSSFSheet sheet1 = hssfworkbook.CreateSheet("Sheet1");
HSSFRow row;
HSSFCell cell;
for (int rowIndex = 0; rowIndex < 9; rowIndex++)
{
     row = sheet1.CreateRow(rowIndex);
     for (int colIndex = 0; colIndex <= rowIndex; colIndex++)
     {
           cell = row.CreateCell(colIndex);
           cell.SetCellValue(String.Format("{0}*{1}={2}", rowIndex + 1, colIndex + 1, (rowIndex + 1) * (colIndex + 1)));
     }
}

代碼其實很簡單，就是回圈呼叫cell.SetCellValue(str)寫入9行資料，每一行寫的單元格數量隨行數遞增，執行完后生成的Excel樣式如下：

完整的代碼如下：

Code
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using NPOI.HSSF.UserModel;
using System.IO;
using NPOI.HPSF;

namespace TimesTables
{
    public class Program
    {
        static HSSFWorkbook hssfworkbook;

        static void Main(string[] args)
        {
            InitializeWorkbook();

            HSSFSheet sheet1 = hssfworkbook.CreateSheet("Sheet1");
            HSSFRow row;
            HSSFCell cell;
            for (int rowIndex = 0; rowIndex < 9; rowIndex++)
            {
                row = sheet1.CreateRow(rowIndex);
                for (int colIndex = 0; colIndex <= rowIndex; colIndex++)
                {
                    cell = row.CreateCell(colIndex);
                    cell.SetCellValue(String.Format("{0}*{1}={2}", rowIndex + 1, colIndex + 1, (rowIndex + 1) * (colIndex + 1)));
                }
            }

            WriteToFile();
        }

        static void WriteToFile()
        {
            //Write the stream data of workbook to the root directory
            FileStream file = new FileStream(@"test.xls", FileMode.Create);
            hssfworkbook.Write(file);
            file.Close();
        }

        static void InitializeWorkbook()
        {
            hssfworkbook = new HSSFWorkbook();

            //create a entry of DocumentSummaryInformation
            DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
            dsi.Company = "NPOI Team";
            hssfworkbook.DocumentSummaryInformation = dsi;

            //create a entry of SummaryInformation
            SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
            si.Subject = "NPOI SDK Example";
            hssfworkbook.SummaryInformation = si;
        }
    }
}

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using NPOI.HSSF.UserModel;
using System.IO;
using NPOI.HPSF;

namespace TimesTables
{
    public class Program
    {
        static HSSFWorkbook hssfworkbook;

        static void Main(string[] args)
        {
            InitializeWorkbook();

            HSSFSheet sheet1 = hssfworkbook.CreateSheet("Sheet1");
            HSSFRow row;
            HSSFCell cell;
            for (int rowIndex = 0; rowIndex < 9; rowIndex++)
            {
                row = sheet1.CreateRow(rowIndex);
                for (int colIndex = 0; colIndex <= rowIndex; colIndex++)
                {
                    cell = row.CreateCell(colIndex);
                    cell.SetCellValue(String.Format("{0}*{1}={2}", rowIndex + 1, colIndex + 1, (rowIndex + 1) * (colIndex + 1)));
                }
            }

            WriteToFile();
        }

        static void WriteToFile()
        {
            //Write the stream data of workbook to the root directory
            FileStream file = new FileStream(@"test.xls", FileMode.Create);
            hssfworkbook.Write(file);
            file.Close();
        }

        static void InitializeWorkbook()
        {
            hssfworkbook = new HSSFWorkbook();

            //create a entry of DocumentSummaryInformation
            DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
            dsi.Company = "NPOI Team";
            hssfworkbook.DocumentSummaryInformation = dsi;

            //create a entry of SummaryInformation
            SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
            si.Subject = "NPOI SDK Example";
            hssfworkbook.SummaryInformation = si;
        }
    }
}

3.3用NPOI操作EXCEL－－生成一張工資單

這一節，我們將綜合NPOI的常用功能（包括創建和填充單元格、合并單元格、設定單元格樣式和利用公式），做一個工資單的實體，先看創建標題行的代碼：

//寫標題文本
HSSFSheet sheet1 = hssfworkbook.CreateSheet("Sheet1");
HSSFCell cellTitle = sheet1.CreateRow(0).CreateCell(0);
cellTitle.SetCellValue("XXX公司2009年10月工資單");

//設定標題行樣式
HSSFCellStyle style = hssfworkbook.CreateCellStyle();
style.Alignment = HSSFCellStyle.ALIGN_CENTER;
HSSFFont font = hssfworkbook.CreateFont();
font.FontHeight = 20 * 20;
style.SetFont(font);

cellTitle.CellStyle = style;

//合并標題行
sheet1.AddMergedRegion(new Region(0, 0, 1, 6));

其中用到了我們前面講的設定單元格樣式和合并單元格等內容，接下來我們回圈創建公司每個員工的工資單：

DataTable dt=GetData();
HSSFRow row;
HSSFCell cell;
HSSFCellStyle celStyle=getCellStyle();

HSSFPatriarch patriarch = sheet1.CreateDrawingPatriarch();
HSSFClientAnchor anchor;
HSSFSimpleShape line;
int rowIndex;
for (int i = 0; i < dt.Rows.Count; i++)
{
    //表頭資料
    rowIndex = 3 * (i + 1);
    row = sheet1.CreateRow(rowIndex);

    cell = row.CreateCell(0);
    cell.SetCellValue("姓名");
    cell.CellStyle = celStyle;

    cell = row.CreateCell(1);
    cell.SetCellValue("基本工資");
    cell.CellStyle = celStyle;

    cell = row.CreateCell(2);
    cell.SetCellValue("住房公積金");
    cell.CellStyle = celStyle;

    cell = row.CreateCell(3);
    cell.SetCellValue("績效獎金");
    cell.CellStyle = celStyle;

    cell = row.CreateCell(4);
    cell.SetCellValue("社保扣款");
    cell.CellStyle = celStyle;

    cell = row.CreateCell(5);
    cell.SetCellValue("代扣個稅");
    cell.CellStyle = celStyle;

    cell = row.CreateCell(6);
    cell.SetCellValue("實發工資");
    cell.CellStyle = celStyle;

    DataRow dr = dt.Rows[i];
    //設定值和計算公式
    row = sheet1.CreateRow(rowIndex + 1);
    cell = row.CreateCell(0);
    cell.SetCellValue(dr["FName"].ToString());
    cell.CellStyle = celStyle;

    cell = row.CreateCell(1);
    cell.SetCellValue((double)dr["FBasicSalary"]);
    cell.CellStyle = celStyle;

    cell = row.CreateCell(2);
    cell.SetCellValue((double)dr["FAccumulationFund"]);
    cell.CellStyle = celStyle;

    cell = row.CreateCell(3);
    cell.SetCellValue((double)dr["FBonus"]);
    cell.CellStyle = celStyle;

    cell = row.CreateCell(4);
    cell.SetCellFormula(String.Format("$B{0}*0.08",rowIndex+2));
    cell.CellStyle = celStyle;

    cell = row.CreateCell(5);
    cell.SetCellFormula(String.Format("SUM($B{0}:$D{0})*0.1",rowIndex+2));
    cell.CellStyle = celStyle;

    cell = row.CreateCell(6);
    cell.SetCellFormula(String.Format("SUM($B{0}:$D{0})-SUM($E{0}:$F{0})",rowIndex+2));
    cell.CellStyle = celStyle;

    //繪制分隔線
    sheet1.AddMergedRegion(new Region(rowIndex+2, 0, rowIndex+2, 6));
    anchor = new HSSFClientAnchor(0, 125, 1023, 125, 0, rowIndex + 2, 6, rowIndex + 2);
    line = patriarch.CreateSimpleShape(anchor);
    line.ShapeType = HSSFSimpleShape.OBJECT_TYPE_LINE;
    line.LineStyle = HSSFShape.LINESTYLE_DASHGEL;

}

其中為了檔案列印為單元格增加了黑色邊框的樣式（如果不設定邊框樣式，列印出來后是沒有邊框的），另外，注意回圈程序中excel中的行號隨資料源中的行號變化處理，完整代碼如下：

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using NPOI.HSSF.UserModel;
using System.IO;
using NPOI.HPSF;
using NPOI.HSSF.Util;
using System.Data;

namespace Payroll
{
    public class Program
    {
        static HSSFWorkbook hssfworkbook;

        static void Main(string[] args)
        {
            InitializeWorkbook();

            //寫標題文本
            HSSFSheet sheet1 = hssfworkbook.CreateSheet("Sheet1");
            HSSFCell cellTitle = sheet1.CreateRow(0).CreateCell(0);
            cellTitle.SetCellValue("XXX公司2009年10月工資單");

            //設定標題行樣式
            HSSFCellStyle style = hssfworkbook.CreateCellStyle();
            style.Alignment = HSSFCellStyle.ALIGN_CENTER;
            HSSFFont font = hssfworkbook.CreateFont();
            font.FontHeight = 20 * 20;
            style.SetFont(font);

            cellTitle.CellStyle = style;

            //合并標題行
            sheet1.AddMergedRegion(new Region(0, 0, 1, 6));

            DataTable dt=GetData();
            HSSFRow row;
            HSSFCell cell;
            HSSFCellStyle celStyle=getCellStyle();

            HSSFPatriarch patriarch = sheet1.CreateDrawingPatriarch();
            HSSFClientAnchor anchor;
            HSSFSimpleShape line;
            int rowIndex;
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                //表頭資料
                rowIndex = 3 * (i + 1);
                row = sheet1.CreateRow(rowIndex);

                cell = row.CreateCell(0);
                cell.SetCellValue("姓名");
                cell.CellStyle = celStyle;

                cell = row.CreateCell(1);
                cell.SetCellValue("基本工資");
                cell.CellStyle = celStyle;

                cell = row.CreateCell(2);
                cell.SetCellValue("住房公積金");
                cell.CellStyle = celStyle;

                cell = row.CreateCell(3);
                cell.SetCellValue("績效獎金");
                cell.CellStyle = celStyle;

                cell = row.CreateCell(4);
                cell.SetCellValue("社保扣款");
                cell.CellStyle = celStyle;

                cell = row.CreateCell(5);
                cell.SetCellValue("代扣個稅");
                cell.CellStyle = celStyle;

                cell = row.CreateCell(6);
                cell.SetCellValue("實發工資");
                cell.CellStyle = celStyle;

                DataRow dr = dt.Rows[i];
                //設定值和計算公式
                row = sheet1.CreateRow(rowIndex + 1);
                cell = row.CreateCell(0);
                cell.SetCellValue(dr["FName"].ToString());
                cell.CellStyle = celStyle;

                cell = row.CreateCell(1);
                cell.SetCellValue((double)dr["FBasicSalary"]);
                cell.CellStyle = celStyle;

                cell = row.CreateCell(2);
                cell.SetCellValue((double)dr["FAccumulationFund"]);
                cell.CellStyle = celStyle;

                cell = row.CreateCell(3);
                cell.SetCellValue((double)dr["FBonus"]);
                cell.CellStyle = celStyle;

                cell = row.CreateCell(4);
                cell.SetCellFormula(String.Format("$B{0}*0.08",rowIndex+2));
                cell.CellStyle = celStyle;

                cell = row.CreateCell(5);
                cell.SetCellFormula(String.Format("SUM($B{0}:$D{0})*0.1",rowIndex+2));
                cell.CellStyle = celStyle;

                cell = row.CreateCell(6);
                cell.SetCellFormula(String.Format("SUM($B{0}:$D{0})-SUM($E{0}:$F{0})",rowIndex+2));
                cell.CellStyle = celStyle;

                //繪制分隔線
                sheet1.AddMergedRegion(new Region(rowIndex+2, 0, rowIndex+2, 6));
                anchor = new HSSFClientAnchor(0, 125, 1023, 125, 0, rowIndex + 2, 6, rowIndex + 2);                 line = patriarch.CreateSimpleShape(anchor);                 line.ShapeType = HSSFSimpleShape.OBJECT_TYPE_LINE;                 line.LineStyle = HSSFShape.LINESTYLE_DASHGEL;             }             WriteToFile();         }         static DataTable GetData()         {             DataTable dt = new DataTable();             dt.Columns.Add("FName",typeof(System.String));             dt.Columns.Add("FBasicSalary",typeof(System.Double));             dt.Columns.Add("FAccumulationFund", typeof(System.Double));             dt.Columns.Add("FBonus", typeof(System.Double));             dt.Rows.Add("令狐沖", 6000, 1000, 2000);             dt.Rows.Add("任盈盈", 7000, 1000, 2500);             dt.Rows.Add("林平之", 5000, 1000, 1500);             dt.Rows.Add("岳靈珊", 4000, 1000, 900);             dt.Rows.Add("任我行", 4000, 1000, 800);             dt.Rows.Add("風清揚", 9000, 5000, 3000);             return dt;         }                  static HSSFCellStyle getCellStyle()         {             HSSFCellStyle cellStyle = hssfworkbook.CreateCellStyle();             cellStyle.BorderBottom = HSSFCellStyle.BORDER_THIN;             cellStyle.BorderLeft = HSSFCellStyle.BORDER_THIN;             cellStyle.BorderRight = HSSFCellStyle.BORDER_THIN;             cellStyle.BorderTop = HSSFCellStyle.BORDER_THIN;             return cellStyle;         }         static void WriteToFile()         {             //Write the stream data of workbook to the root directory             FileStream file = new FileStream(@"test.xls", FileMode.Create);             hssfworkbook.Write(file);             file.Close();         }         static void InitializeWorkbook()         {             hssfworkbook = new HSSFWorkbook();             //create a entry of DocumentSummaryInformation             DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();             dsi.Company = "NPOI Team";             hssfworkbook.DocumentSummaryInformation = dsi;             //create a entry of SummaryInformation             SummaryInformation si = PropertySetFactory.CreateSummaryInformation();             si.Subject = "NPOI SDK Example";             hssfworkbook.SummaryInformation = si;         }     } }

生成的Excel檔案樣式如下：

3.4用NPOI操作EXCEL－－從Excel中抽取文本

　　我們知道，搜索引擎最擅長處理的就是文本，而Excel中的內容并不是以文本方式存盤的，那么如果想要搜索引擎爬蟲能夠抓取到Excel中的內容是比較困難的，除非搜索引擎爬蟲對Excel格式進行專門的處理，那么有沒有辦法解決此問題呢？有,通過NPOI將Excel內容文本化！

　　如下，有這樣一張Excel，如果想讓它被搜索引擎收錄，常用的方式是以HTML形式展現，但將一個個這樣的Excel手工做成HTML頁面顯然比較麻煩，接下來，我們將提供一種方案，自動將Excel中的內容以HTML形式展現，

　　其實基本思想也很簡單，就是通過NPOI讀取每個Cell中的內容，然后以HTML的形式輸出，但要保證輸出的HTML頁面布局與Excel中的一致，還有點小技巧，下面是構造Table的代碼：

private HSSFSheet sht;
protected String excelContent;

protected void Page_Load(object sender, EventArgs e)
{
    HSSFWorkbook wb = new HSSFWorkbook(new FileStream(Server.MapPath("App_Data/quotation.xls"), FileMode.Open));
    sht = wb.GetSheet("Sheet1");

    //取行Excel的最大行數
    int rowsCount = sht.PhysicalNumberOfRows;
    //為保證Table布局與Excel一樣，這里應該取所有行中的最大列數（需要遍歷整個Sheet），
    //為少一交全Excel遍歷，提高性能，我們可以人為把第0行的列數調整至所有行中的最大列數，
    int colsCount = sht.GetRow(0).PhysicalNumberOfCells;

    int colSpan;
    int rowSpan;
    bool isByRowMerged;

    StringBuilder table = new StringBuilder(rowsCount * 32);

    table.Append("<table border='1px'>");
    for (int rowIndex = 0; rowIndex < rowsCount; rowIndex++)
    {
        table.Append("<tr>");
        for (int colIndex = 0; colIndex < colsCount; colIndex++)
        {
            GetTdMergedInfo(rowIndex, colIndex, out colSpan, out rowSpan, out isByRowMerged);
            //如果已經被行合并包含進去了就不輸出TD了，
            //注意被合并的行或列不輸出的處理方式不一樣，見下面一處的注釋說明了列合并后不輸出TD的處理方式，
            if (isByRowMerged)
            {
                continue;
            }

            table.Append("<td");
            if (colSpan > 1)
                table.Append(string.Format(" colSpan={0}", colSpan));
            if (rowSpan > 1)
                table.Append(string.Format(" rowSpan={0}", rowSpan));
            table.Append(">");

            table.Append(sht.GetRow(rowIndex).GetCell(colIndex));

            //列被合并之后此行將少輸出colSpan-1個TD，
            if (colSpan > 1)
                colIndex += colSpan - 1;

            table.Append("</td>");

        }
        table.Append("</tr>");
    }
    table.Append("</table>");

    this.excelContent = table.ToString();
}

　　其中用到的GetTdMergedInfo方法代碼如下：

/// <summary>
///  獲取Table某個TD合并的列數和行數等資訊，與Excel中對應Cell的合并行數和列數一致，
/// </summary>
/// <param name="rowIndex">行號</param>
/// <param name="colIndex">列號</param>
/// <param name="colspan">TD中需要合并的行數</param>
/// <param name="rowspan">TD中需要合并的列數</param>
/// <param name="rowspan">此單元格是否被某個行合并包含在內，如果被包含在內，將不輸出TD，</param>
/// <returns></returns>
private void GetTdMergedInfo(int rowIndex, int colIndex, out int colspan, out int rowspan, out bool isByRowMerged)
{
    colspan = 1;
    rowspan = 1;
    isByRowMerged = false;
    int regionsCuont = sht.NumMergedRegions;
    Region region;
    for (int i = 0; i < regionsCuont; i++)
    {
        region = sht.GetMergedRegionAt(i);
        if (region.RowFrom == rowIndex && region.ColumnFrom == colIndex)
        {
            colspan = region.ColumnTo - region.ColumnFrom + 1;
            rowspan = region.RowTo - region.RowFrom + 1;

            return;
        }
        else if (rowIndex > region.RowFrom && rowIndex <= region.RowTo && colIndex>=region.ColumnFrom && colIndex<=region.ColumnTo)
        {
            isByRowMerged = true;
        }
    }
}

最后在apsx頁面中輸出構建好的Table：

<%=excelContent %>

執行效果如下：

我們發現，與Excel中的布局完全一樣（這里沒有處理單元格的樣式，只處理了內容，有興趣的讀者也可以將Excel中單元格的樣式也應用在HTML中），這里為保證布局一致，主要是將Excel中的Region資訊決議成Table的colSpan和rowSpan屬性，如果對這兩個屬性不太了解，可以結合以下代碼和示例加以了解：

以上HTML代碼對應的Table展現為：

3.5用NPOI操作EXCEL－－巧妙使用Excel Chart

　　在NPOI中，本身并不支持Chart等高級物件的創建，但通過l模板的方式可以巧妙地利用Excel強大的透視和圖表功能，請看以下例子，

首先建立模板檔案，定義兩列以及指向此區域的名稱“sales”：

創建資料表，資料來源填入剛才定義的區域：

最后生成的資料透視表所在Sheet的樣式如下：

至此，模板已經建好，另存為“D:\MyProject\NPOIDemo\Chart\Book2.xls”，我們發現，模板就相當于一個“空架子”，僅僅有操作方式沒并沒有任何資料，下一步，我們往這個“空架子”中填入資料，我們通過如下代碼往這個“空架子”中寫入資料：

static void Main(string[] args)
{
    HSSFWorkbook wb = new HSSFWorkbook(new FileStream(@"D:\MyProject\NPOIDemo\Chart\Book2.xls", FileMode.Open));

    HSSFSheet sheet1 = wb.GetSheet("Sheet1");

    HSSFRow row = sheet1.CreateRow(1);
    row.CreateCell(0).SetCellValue("令狐沖");
    row.CreateCell(1).SetCellValue(50000);

    row = sheet1.CreateRow(2);
    row.CreateCell(0).SetCellValue("任盈盈");
    row.CreateCell(1).SetCellValue(30000);

    row = sheet1.CreateRow(3);
    row.CreateCell(0).SetCellValue("風清揚");
    row.CreateCell(1).SetCellValue(80000);

    row = sheet1.CreateRow(4);
    row.CreateCell(0).SetCellValue("任我行");
    row.CreateCell(1).SetCellValue(20000);

    //Write the stream data of workbook to the root directory
    FileStream file = new FileStream(@"test.xls", FileMode.Create);
    wb.Write(file);
    file.Close();
}

打開生成的test.xls檔案，發現資料已經被填進去了：

再看資料透視表，也有資料了：

總結：
Excel有著強大的報表透視和圖表功能，而且簡單易用，利用NPOI，可以對其進行充分利用，在做圖形報表、透視報表時將非常有用！

NPOI實踐: .NET匯入Excel檔案的另一種選擇

作者：Tony Qu

官方網站：http://npoi.codeplex.com | NPOI QQ交流群: 78142590

NPOI之所以強大，并不是因為它支持匯出Excel，而是因為它支持匯入Excel，并能“理解”OLE2檔案結構，這也是其他一些Excel讀寫庫比較弱的方面，通常，讀入并理解結構遠比匯出來得復雜，因為匯入你必須假設一切情況都是可能的，而生成你只要保證滿足你自己需求就可以了，如果把匯入需求和生成需求比做兩個集合，那么生成需求通常都是匯入需求的子集，這一規律不僅體現在Excel讀寫庫中，也體現在pdf讀寫庫中，目前市面上大部分的pdf庫僅支持生成，不支持匯入，

如果你不相信NPOI能夠很好的理解OLE2檔案格式，那就去下載POIFS Brower，具體可以參考這篇文章的介紹：Office檔案格式解惑，當然單單理解OLE2是不夠的，因為Excel檔案格式是BIFF，但BIFF是以OLE2為基礎的，做個很形象的比喻就是：OLE2相當于磁盤的FAT格式，BIFF相當于檔案和檔案夾，NPOI負責理解BIFF格式的代碼基本都在HSSF命名空間里面，

好了，剛才廢話了一會兒，主要是給大家打打基礎，現在進入正題，

本文將以DataTable為容器讀入某xls的第一個作業表的資料（最近群里面很多人問這個問題），

在開始之前，我們先來補些基礎知識，每一個xls都對應一個唯一的HSSFWorkbook，每一個HSSFWorkbook會有若干個HSSFSheet，而每一個HSSFSheet包含若干HSSFRow（Excel 2003中不得超過65535行），每一個HSSFRow又包含若干個HSSFCell（Excel 2003中不得超過256列），

為了遍歷所有的單元格，我們就得獲得某一個HSSFSheet的所有HSSFRow，通常可以用HSSFSheet.GetRowEnumerator()，如果要獲得某一特定行，可以直接用HSSFSheet.GetRow(rowIndex)，另外要遍歷我們就必須知道邊界，有一些屬性我們是可以用的，比如HSSFSheet.FirstRowNum（作業表中第一個有資料行的行號）、HSSFSheet.LastRowNum（作業表中最后一個有資料行的行號）、HSSFRow.FirstCellNum（一行中第一個有資料列的列號）、HSSFRow.LastCellNum（一行中最后一個有資料列的列號），

基礎知識基本上補得差不多了，現在開工！

首先我們要準備一個用于打開檔案流的函式InitializeWorkbook，由于檔案讀完后就沒用了，所以這里直接用using（養成好習慣，呵呵），

HSSFWorkbookhssfworkbook;

void InitializeWorkbook(string path)
{
//read the template via FileStream, it is suggested to use FileAccess.Readto prevent file lock.

//book1.xlsis an Excel-2007-generated file, so some new unknown BIFF records are added.

    using (FileStream file =new FileStream(path, FileMode.Open,FileAccess.Read))
    {
        hssfworkbook = newHSSFWorkbook(file);
    }
}

接下來我們要開始寫最重要的函式ConvertToDataTable，即把HSSF的資料放到一個DataTable中，

HSSFSheetsheet = hssfworkbook.GetSheetAt(0);
System.Collections.IEnumerator rows = sheet.GetRowEnumerator();

while(rows.MoveNext())
{
    HSSFRow row = (HSSFRow)rows.Current;
    //TODO::Create DataTable row    for (int i = 0; i < row.LastCellNum; i++)
    {
        HSSFCell cell = row.GetCell(i);
        //TODO::set cell value to the cell of DataTables    }

上面的結構大家都應該能看懂吧，無非就是先遍歷行，再遍歷行中的每一列，這里引出了一個難點，由于Excel的單元格有好幾種型別，型別不同顯示的東西就不同，具體的型別有布爾型、數值型、文本型、公式型、空白、錯誤，

publicenum HSSFCellType
{
    Unknown = -1,
    NUMERIC = 0,
    STRING = 1,
    FORMULA = 2,
    BLANK = 3,
    BOOLEAN = 4,
    ERROR = 5,
}

這里的HSSFCellType描述了所有的型別，但細心的朋友可能已經發現了，這里沒有日期型，這是為什么呢？這是因為Excel底層并沒有一定日期型，而是通過數值型來替代，至于如何區分日期和數字，都是由文本顯示的樣式決定的，在NPOI中則是由HSSFDataFormat來處理，為了能夠方便的獲得所需要的型別所對應的文本，我們可以使用HSSFCell.ToString()來處理，

于是剛才的代碼則變成了這樣：

HSSFSheetsheet = hssfworkbook.GetSheetAt(0);
System.Collections.IEnumerator rows = sheet.GetRowEnumerator();

DataTable dt = new DataTable();
for (int j = 0; j < 5;j++)
{
    dt.Columns.Add(Convert.ToChar(((int)'A')+j).ToString());
}

while(rows.MoveNext())
{
    HSSFRow row = (HSSFRow)rows.Current;
    DataRow dr = dt.NewRow();

    for (int i = 0; i < row.LastCellNum; i++)
    {
        HSSFCell cell = row.GetCell(i);

        if (cell == null)
        {
            dr[i] = null;
        }
        else        {            dr[i] = cell.ToString();        }    }    dt.Rows.Add(dr);}

是不是很簡單，呵呵！

當然，如果你要對某個特定的單元格型別做特殊處理，可以通過判HSSFCell.CellType來解決，比如下面的代碼：

        switch(cell.CellType)
        {
            case HSSFCellType.BLANK:
                dr[i] = "[null]";
                break;
            case HSSFCellType.BOOLEAN:
                dr[i] =cell.BooleanCellValue;
                break;
            case HSSFCellType.NUMERIC:
                dr[i] =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 dateor a number.

               break;
            case HSSFCellType.STRING:
                dr[i] =cell.StringCellValue;
                break;
            case HSSFCellType.ERROR:
                dr[i] = cell.ErrorCellValue;
                break;
            case HSSFCellType.FORMULA:
            default:
                dr[i] = "="+cell.CellFormula;
                break;
        }

這里只是舉個簡單的例子，

完整代碼下載：http://files.cnblogs.com/tonyqus/ImportXlsToDataTable.zip

注意，此代碼中不包括NPOI的assembly，否則檔案會很大，所以建議去npoi.codeplex.com下載，

轉載請註明出處，本文鏈接：https://www.uj5u.com/net/48230.html

標籤：C#

上一篇：簡單實用演算法——人民幣金額大寫轉換

下一篇：【翻譯】.NET 5 RC1發布