您现在的位置是:首页>博客详情

NPOI 1.2教程 - 3 使用Excel公式

FreshMan2019年04月13日 23:13NPOI,Excel,Office526

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

2.3.1 用NPOI操作EXCEL--基本计算

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

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

 

复制代码

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);
cel1.SetCellFormula(
"1+2*3");
cel2.SetCellValue(
5);

复制代码

同样,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(200471));
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执行结果如下:

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


2.3.7 用NPOI操作EXCEL--LOOKUP函数

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


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);

运行结果如下: