Tony Qu

作者：aTao.Xiang

      前面我们学习了通过NPOI向Excel中设置公式，那么有些读者可能会问：“NPOI能不能获取公式的返回值呢？”，答案是可以！
一、获取模板文件中公式的返回值
如在D盘中有一个名为text.xls的Excel文件，其内容如下： 

注意C1单元格中设置的是公式“$A1*$B1”，而不是值“12”。利用NPOI，只需要写简单的几句代码就可以取得此公式的返回值： 

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

输出结果为：

可见NPOI成功的“解析”了此.xls文件中的公式。注意NumericCellValue属性会自动根据单元格的类型处理，如果为空将返0，如果为数值将返回数值，如果为公式将返回公式计算后的结果。单元格的类型可以通过CellType属性获取。

二、获取NPOI生成的Excel文件中公式的返回值

        上例中是从一个已经存在的Excel文件中获取公式的返回值，那么如果Excel文件是通过NPOI创建的，直接用上面的方法获取，可能得不到想要的结果。如：

 IWorkbook hssfworkbook = new HSSFWorkbook();
 ISheet sheet1 = hssfworkbook.CreateSheet("Sheet1");
 IRow row = sheet1.CreateRow(0);
 row.CreateCell(0).SetCellValue(3);
 row.CreateCell(1).SetCellValue(4);
 ICell cell = row.CreateCell(2); 

 cell.CellFormula = "$A1+$B1";
 System.Console.WriteLine(cell.NumericCellValue);

      执行上面代码，将输出结果“0”，而不是我们想要的结果“7”。那么将如何解决呢？这时要用到HSSFFormulaEvaluator类。在第8行后加上这两句就可以了：

HSSFFormulaEvaluator e = new HSSFFormulaEvaluator(hssfworkbook);

cell = e.EvaluateInCell(cell);

运行结果如下：

作者：aTao.Xiang

我们知道，在大多数编程语言中都有随机数函数。在Excel中，同样存在着这样一个函数—RAND()函数，用于生成随机数。先来看一个最简单的例子：

ISheet 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函数的几个简单应用，还有很多随机数的例子都可以根据这些，再结合不同的其它函数引申出来。

作者：aTao.Xiang

今天，我们一起学习Excel中的查询函数－－LOOKUP。其基本语法形式为LOOKUP(lookup_value,lookup_vector,result_vector)。还是以例子加以说明更容易理解：

ISheet sheet1 = hssfworkbook.CreateSheet("Sheet1");
IRow row1 = sheet1.CreateRow(0);
row1.CreateCell(0).SetCellValue("收入最低");
row1.CreateCell(1).SetCellValue("收入最高");
row1.CreateCell(2).SetCellValue("税率");
IRow row2 = sheet1.CreateRow(1);
row2.CreateCell(0).SetCellValue(0);
row2.CreateCell(1).SetCellValue(3000);
row2.CreateCell(2).SetCellValue(0.1);
IRow row3 = sheet1.CreateRow(2);
row3.CreateCell(0).SetCellValue(3001);
row3.CreateCell(1).SetCellValue(10000);
row3.CreateCell(2).SetCellValue(0.2);
IRow row4 = sheet1.CreateRow(3);
row4.CreateCell(0).SetCellValue(10001);
row4.CreateCell(1).SetCellValue(20000);
row4.CreateCell(2).SetCellValue(0.3);
IRow row5 = sheet1.CreateRow(4);
row5.CreateCell(0).SetCellValue(20001);
row5.CreateCell(1).SetCellValue(50000);
row5.CreateCell(2).SetCellValue(0.4);
IRow row6 = sheet1.CreateRow(5);
row6.CreateCell(0).SetCellValue(50001);
row6.CreateCell(2).SetCellValue(0.5);
IRow row8 = sheet1.CreateRow(7);
row8.CreateCell(0).SetCellValue("收入");
row8.CreateCell(1).SetCellValue("税率");
IRow row9 = sheet1.CreateRow(8);
row9.CreateCell(0).SetCellValue(7800);
row9.CreateCell(1).CellFormula = "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).CellFormula = "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用于在表格或数值数组的首行查找指定的数值，并由此返回表格或数组当前列中指定行处的数值。读者可以自已去尝试。

作者：aTao.Xiang

一、COUNTIF 
     这一节，我们一起来学习Excel中另一个常用的函数–COUNTIF函数，看函数名就知道这是一个用来在做满足某条件的计数的函数。先来看一看它的语法：COUNTIF(range,criteria)，参数说明如下：

Range	需要进行读数的计数
Criteria	条件表达式，只有当满足此条件时才进行计数

接下来看一个例子，代码如下：

ISheet sheet1 = hssfworkbook.CreateSheet("Sheet1");
IRow row1 = sheet1.CreateRow(0);
row1.CreateCell(0).SetCellValue("姓名");
row1.CreateCell(1).SetCellValue("成绩");
IRow row2 = sheet1.CreateRow(1);
row2.CreateCell(0).SetCellValue("令狐冲");
row2.CreateCell(1).SetCellValue(85);
IRow row3 = sheet1.CreateRow(2);
row3.CreateCell(0).SetCellValue("任盈盈");
row3.CreateCell(1).SetCellValue(90);
IRow row4 = sheet1.CreateRow(3);
row4.CreateCell(0).SetCellValue("任我行");
row4.CreateCell(1).SetCellValue(70);
IRow row5 = sheet1.CreateRow(4);
row5.CreateCell(0).SetCellValue("左冷婵");
row5.CreateCell(1).SetCellValue(45);
IRow row6 = sheet1.CreateRow(5);
row6.CreateCell(0).SetCellValue("岳不群");
row6.CreateCell(1).SetCellValue(50);
IRow 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条件的对应的项

我们还是以例子来加以说明：

 

执行结果如下：

如上图，SUMIF统计出了不同人一季度的销售额。

作者：aTao.Xiang

在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单元格的用于逻辑判断的公式。

ISheet sheet1 = hssfworkbook.CreateSheet("Sheet1");
IRow row1 = sheet1.CreateRow(0);
row1.CreateCell(0).SetCellValue("姓名");
row1.CreateCell(1).SetCellValue("身份证号");
row1.CreateCell(2).SetCellValue("性别");
row1.CreateCell(3).SetCellValue("语文");
row1.CreateCell(4).SetCellValue("是否合格");
IRow row2 = sheet1.CreateRow(1);
row2.CreateCell(0).SetCellValue("令狐冲");
row2.CreateCell(1).SetCellValue("420821198808101014");
row2.CreateCell(2).CellFormula = "IF(MOD(MID(B2,18,1),2)=0,\"男\",\"女\")";
row2.CreateCell(3).SetCellValue(85);
row2.CreateCell(4).CellFormula = "IF(D2>60,IF(D2>90,\"优秀\",\"合格\"),\"不合格\")";

其中最关键的两句执行结果如下：

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

row2.CreateCell(4).CellFormula = "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分以上为合格，否则为不合格。

作者：aTao.Xiang

      这一节我们开始学习Excel另一类非常常见的函数—字符串函数。 在Excel中提供了非常丰富的字符串函数，在NPOI中同样得到了很好的支持。
一、 大小写转换类函数
      LOWER(String)：将一个文字串中的所有大写字母转换为小写字母。
      UPPER(String)：将文本转换成大写形式。
      PROPER(String)：将文字串的首字母及任何非字母字符之后的首字母转换成大写。将其余的字母转换成小写。
      对应的C#代码与前几节讲的设置公式的代码类似：

IRow row1 = sheet1.CreateRow(0);
row1.CreateCell(0).SetCellValue("待操作字符串");
row1.CreateCell(1).SetCellValue("操作函数");
row1.CreateCell(2).SetCellValue("操作结果");
IRow 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)

作者：aTao.Xiang

Excel中有非常丰富的日期处理函数，在NPOI中同样得到了很好的支持。如下图：

  
对应的与前面的基本公式设置类似：

ISheet sheet1 = hssfworkbook.CreateSheet("Sheet1");
IRow row1 = sheet1.CreateRow(0);
IRow row2 = sheet1.CreateRow(1);
row1.CreateCell(0).SetCellValue("姓名");
row1.CreateCell(1).SetCellValue("参加工作时间");
row1.CreateCell(2).SetCellValue("当前日期");
row1.CreateCell(3).SetCellValue("工作年限");
ICell cel1 = row2.CreateCell(0);
ICell cel2 = row2.CreateCell(1);
ICell cel3 = row2.CreateCell(2);
ICell cel4 = row2.CreateCell(3);
cel1.SetCellValue("aTao.Xiang");
cel2.SetCellValue(new DateTime(2004, 7, 1));
cel3.CellFormula = "TODAY()";
cel4.CellFormula = "CONCATENATE(DATEDIF(B2,TODAY(),\"y\"),\"年\",DATEDIF(B2,TODAY(),\"ym\"),\"个月\")";
//在poi中日期是以double类型表示的，所以要格式化
ICellStyle cellStyle = hssfworkbook.CreateCellStyle();
IDataFormat 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.CellFormula = "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)

作者：aTao.Xiang

这节我们开始学习Excel中最常用的函数—Sum求和函数。

首先，我们先看一上最简单的Sum函数：Sum(num1,num2,…)。使用效果如图 

图中的E1=Sum(A1,C1)表示将A1与C1的和填充在E1处，与公式”E1=A1+C1”等效。对应的生成代码与上一节中的基本计算公式类似：

ISheet sheet1 = hssfworkbook.CreateSheet("Sheet1");
IRow row1 = sheet1.CreateRow(0);
ICell cel1 = row1.CreateCell(0);
ICell cel2 = row1.CreateCell(1);
ICell cel3 = row1.CreateCell(2);
ICell celSum1 = row1.CreateCell(3);
ICell celSum2 = row1.CreateCell(4);
ICell celSum3 = row1.CreateCell(5);
cel1.SetCellValue(1);
cel2.SetCellValue(2);
cel3.SetCellValue(3);
celSum2.CellFormula = "sum(A1,C1)";

当然，把每一个单元格作为Sum函数的参数很容易理解，但如果要求和的单元格很多，那么公式就会很长，既不方便阅读也不方便书写。所以Excel提供了另外一种多个单元格求和的写法：

如上图中的“Sum(A1:C1)”表示求从A1到C1所有单元格的和，相当于A1+B1+C1。

对应的代码为：

celSum1.CellFormula = “sum(A1:C1)”;

最后，还有一种求和的方法。就是先定义一个区域，如”range1”，然后再设置Sum(range1)，此时将计算区域中所有单元格的和。

定义区域的代码为：

IName range = hssfworkbook.CreateName();

range.RefersToFormula = “Sheet1!$A1:$C1″;

range.NameName = “range1″;

执行此代码后的Excel文件将在的公式菜单下的名称管理器(Excel2007的菜单路径,2003稍有不同)中看到如下区域定义：

给单元格F1加上公式：

celSum3.CellFormula = “sum(range1)”;

生成的Excel如下图所示：

作者：aTao.Xiang

从这节开始，我们将开始学习Excel高级一点的功能–公式。为某个单元格指定公式后，单元格中的类容将根据公式计算得出，如图： 

图中设置的是一个基本表达式”1+2*3”，单元格A1中将显示此表达式计算的结果”7”，如图所示。对应的C#生成代码也很简单，如下：

ISheet sheet1 = hssfworkbook.CreateSheet("Sheet1");
IRow row1=sheet1.CreateRow(0);
ICell cel1 = row1.CreateCell(0);
ICell cel2 = row1.CreateCell(1);
ICell cel3 = row1.CreateCell(2);
cel1.CellFormula = "1+2*3";
cel2.SetCellValue(5);

同样，NPOI也支持单元格引用类型的公式设置，如下图中的C1=A1*B1。

对应的公式设置代码为：

cel3.CellFormula = "A1*B1";

是不是很简单呢？但要注意，在利用NPOI写程序时，行和列的计数都是从0开始计算的，但在设置公式时又是按照Excel的单元格命名规则来的。

作者：Tony Qu

NPOI，顾名思义，就是POI的.NET版本。那POI又是什么呢？POI是一套用Java写成的库，能够帮助开发者在没有安装微软Office的情况下读写Office 97-2003的文件，支持的文件格式包括xls, doc, ppt等。在本文发布时，POI的最新版本是3.8 beta 5。

NPOI 1.x是基于POI 3.x版本开发的，NPOI目前最新发布的版本是1.2.4（对应于POI 3.8.4），在该版本中仅支持读写Excel文件格式（xls），在最新的NPOI.ScratchPad中有Word文件格式（doc）的实现，但由于目前不稳定，不与NPOI 1.2.4发布版本一起发布。

如果你有任何关于NPOI的问题，可以加QQ群189925337 。

 

官方网站

Codeplex分站：http://npoi.codeplex.com

Googlecode分站：http://code.google.com/p/npoi/

官方博客：http://tonyqus.sinaapp.com

官方教程：http://tonyqus.sinaapp.com/archives/38 

 

团队介绍

Tony Qu

来自于中国上海，是这个项目的发起人和开发人员，2008年9月开始了NPOI的开发，负责NPOI所有底层库的开发、测试和bug修复。

个人博客地址为http://tonyqus.cnblogs.com/

Hüseyin Tüfekçilerli

来自于土耳其的伊斯坦布尔，也是这个项目的开发人员，2008年11月参与了NPOI的开发，主要负责POIFS Browser 1.0的开发工作。

个人博客地址为http://huseyint.com/

aTao.Xiang

来自中国，2009年8月开始参与该项目，主要参与了NPOI 1.2中文版教程的撰写工作和推广工作

个人博客地址为http://www.cnblogs.com/atao/

Antony

来自中国广州，2011年下旬加入NPOI团队，目前承担了NPOI 1.3的主要开发工作，已完成Excel2Html和Word2Html的代码转换工作。

Jeff Stedfast

来自美国MA州，2011年底加入NPOI团队，主要负责改进NPOI的命名，并消除一些不必须要的警告信息，他还负责测试Mono下的NPOI行为。

个人博客地址为http://jeffreystedfast.blogspot.com/

Christian Leutloff

来自德国，2011年底加入NPOI团队，主要负责FxCop报的错误，对NPOI进行代码优化。

 

许可证说明

NPOI采用的是Apache 2.0许可证（poi也是采用这个许可证），这意味着它可以被用于任何商业或非商业项目，你不用担心因为使用它而必须开放你自己的源代码，所以它对于很多从事业务系统开发的公司来说绝对是很不错的选择。

当然作为一个开源许可证，肯定也是有一些义务的，例如如果你在系统中使用NPOI，你必须保留NPOI中的所有声明信息。对于源代码的任何修改，必须做出明确的标识。

完整的apache 2.0许可证请见http://www.phpx.com/man/Apache-2/license.html

 

回顾与展望

2008-2011 已经完成的任务

a. 提供xls的读写功能

b. 支持Excel公式

c. NPOI 1.2中文教程

d. NPOI.Examples

e. 1300个单元测试 （基于微软VS测试框架）

f. Excel转Html工具库，Word转Html工具库

g. POIFS Browser 1.2版本

 

2012年计划任务

a. 提供word读取功能，命名空间NPOI.HWPF （暂不支持写）

b. 提供OOXML读写功能（类似System.Packaging的功能）

c. 提供OOXML-Excel读写功能（也许还会有OOXML-Word和OOXML-PowerPoint）

d. NPOI官方网站建设

e. 出一本纸质书，名字未定

f. NPOI 1.2.4电子教程

 

为什么用NPOI？

因为它能够帮助你生成真正的Office文件格式，比如说Excel文件格式。我曾经看到好几个园友发过有关如何生成Excel报表的帖子，大家确实花了很多心思，具体的帖子如下：

生成Excel高级报表（用的是Office PIA库）

http://www.cnblogs.com/xiaobier/archive/2008/10/13/1310399.html

Datagrid数据导出到excel文件的三种方法

http://www.cnblogs.com/xieduo/articles/606202.html

其中提到了的2种方法分别是：cvs法、html法

.net 操作 EXCEL

http://www.cnblogs.com/jhobo/archive/2009/02/17/1392423.html

其中提到了Office PIA法、OLEDB法、OPENXML法

特别是其中的cvs法和html法值得提一下，这得感谢Microsoft Office对于格式的强大兼容性，貌似国产某某Office软件就无法正常显示基于html的xls文件。

好了，回到我们的问题上，为什么要用NPOI呢？

第一点，你不需要在服务器上安装微软的Office，可以避免版权问题。

第二点，使用起来比Office PIA的API更加方便，更人性化。

第三点，你不用去花大力气维护NPOI，NPOI Team会不断更新、改善NPOI，绝对省成本。

第四点，很多事情是html和cvs法做不到的，比如说公式计算[Cell C1]=A1+B1*A2、单元格高级样式（如文本旋转、对齐、宽度）等，其中公式计算可以适当减轻服务器端的计算压力

第五点，你很难保证你的客户机器上都安装了Office 2007或Office Compatiblity Pack for Office 2003，出于向前兼容的考虑，还是生成Office 97-2003格式比较可靠。

 

在开发中哪些地方可以使用NPOI

a. 生成Excel报表

b. Excel中的文本提取（主要用于数据挖掘系统）

c. 批量生成Excel文件

d. 基于Excel文件模板生成新的Excel

e. 研究Excel BIFF文件格式及OLE2文件格式

f. Excel转Html, Word转Html