NPOI使用说明---函数

示例一、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算起),用MOD取余函数判断奇偶。在Excel中对数据类型的控制没有C#中那么严格,如此例中我截取出来的是字符串,但当我做取余运算时Excel会自动转换。
      IF(D2>60,IF(D2>90,"优秀","合格"),"不合格"):这是IF的嵌套使用,表示90分以上为优秀,60分以上为合格,否则为不合格。示例二、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是计数)。
与CountIF不同,SumIF有三个参数,语法为SumIF(criteria_range, criteria,sum_range),各参数的说明如下:

criteria_range

条件测试区域,第二个参数Criteria中的条件将与此区域中的值进行比较

criteria

条件测试值,满足条件的对应的sum_range项将进行求和计算

sum_range

汇总数据所在区域,求和时会排除掉不满足Criteria条件的对应的项

我们还是以例子来加以说明:
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统计出了不同人一季度的销售额

示例四、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用于在表格或数值数组的首行查找指定的数值,并由此返回表格或数组当前列中指定行处的数值。读者可以自已去尝试。

示例六、随机数函数

      我们知道,在大多数编程语言中都有随机数函数。在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函数的几个简单应用,还有很多随机数的例子都可以根据这些,再结合不同的其它函数引申出来。

示例七、通过NPOI获得公式的返回值

     前面我们学习了通过NPOI向Excel中设置公式,那么有些读者可能会问:“NPOI能不能获取公式的返回值呢?”,答案是可以!
7.1、获取模板文件中公式的返回值
如在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属性获取。
7.2、获取NPOI生成的Excel文件中公式的返回值
      上例中是从一个已经存在的Excel文件中获取公式的返回值,那么如果Excel文件是通过NPOI创建的,直接用上面的方法获取,可能得不到想要的结果。如:
HSSFWorkbook hssfworkbook = new HSSFWorkbook();
HSSFSheet sheet1 = hssfworkbook.CreateSheet("Sheet1");
HSSFRow row = sheet1.CreateRow(0);
row.CreateCell(0).SetCellValue(3);
row.CreateCell(1).SetCellValue(4);
HSSFCell cell = row.CreateCell(2);
cell.SetCellFormula("$A1+$B1");
System.Console.WriteLine(cell.NumericCellValue);
      执行上面代码,将输出结果“0”,而不是我们想要的结果“7”。那么将如何解决呢?这时要用到HSSFFormulaEvaluator类。在第8行后加上这两句就可以了:
HSSFFormulaEvaluator e = new HSSFFormulaEvaluator(hssfworkbook);
cell = e.EvaluateInCell(cell);

好的,我理解了你的问题。关于用NPOI操作Excel并巧妙使用Excel Chart,以下是我的回答: NPOI是一个用于操作Office文件的.NET库,其中包括Excel文件。通过NPOI,可以轻松地读取、写入和修改Excel文件,同时也可以操作Excel图表。 要创建Excel图表,可以使用NPOI中的Chart类。该类提供了创建不同类型图表的方法,例如折线图、柱形图、饼图等等。在创建图表时,需要指定数据源和图表类型。例如,以下代码创建一个柱形图,并将其插入到Excel工作表中: ``` // 创建一个工作簿 var workbook = new HSSFWorkbook(); // 创建一个工作表 var sheet = workbook.CreateSheet("Sheet1"); // 创建一个柱形图 var chart = sheet.CreateDrawingPatriarch().CreateChart(new HSSFClientAnchor(0, 0, 0, 0, 0, 5, 10, 20)); chart.ChartType = NPOI.SS.UserModel.Charts.ChartType.Bar; // 设置图表数据源 var chartData = chart.ChartData; var chartSeries = chartData.AddSeries(new string[] { "A", "B", "C" }, new[] { 1.2, 3.4, 5.6 }); // 将图表插入到工作表中 sheet.CreateRow(25).HeightInPoints = 250; var anchor = new HSSFClientAnchor(0, 0, 0, 0, 0, 0, 10, 20); anchor.AnchorType = 2; sheet.CreateDrawingPatriarch().CreateChart(anchor).Chart = chart; ``` 以上代码创建一个柱形图,并将其插入到Excel工作表的指定位置。可以通过修改数据源和图表类型来创建不同类型的图表。 希望我的回答对你有所帮助。如果你有任何其他问题,请随时问我。
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值