NPOI使用说明---函数

最新推荐文章于 2024-08-16 15:01:39 发布
最新推荐文章于 2024-08-16 15:01:39 发布
阅读量2.3k 收藏 7
点赞数 1
分类专栏： 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中关于公式的使用Formula
jamenu的博客
02-28 1070
​ 鄙人在做Excel数据导出时,一般不使用Excel的公式Formula,因为数据都在程序里获取到了,可以直接用(统计加总/平均…​ 但一些需求要求固定使用Excel公式计算(可能是为了’美观’,亦或是’不信任程序的计算结果’),则需要在NPOI中设置Excel的公式.谨以此文介绍其用法以及一些细节.前面的用例都是动态状态下的使用,如果在动态打印Excel的情况下使用公式,则必须准确的获取到列号(like A,B,C,D…像是SUM(),AVERAGE(),ABS()…2.日期计算公式的使用。
NPOI操作Excel(三)--解析Excel
zls365365的博客
02-04 402
通过前面两篇的基础学习，我们对NPOI有了一定了了解，下面就开始进入实战，解析下面格式的Excel（下面只是列举了几个例子），并保存入库首先我们先分析一下，要解析这样的Excel，需要把指...
NPOI使用手册 试用说明 操作教程 最新版
04-17
NPOI使用手册 试用说明 操作教程 最新版 京华志&精华志出品 希望大家互相学习，互相进步 支持CSDN 支持微软
NPOI操作Excel常用函数
weixin_30254435的博客
06-27 289
最近因项目接触了NPOI，感觉还是蛮不错的，网络上的教程普遍版本较老，本篇记录所常用操作，采用NPOI 2.0版本。 推荐： NPOI官方网站 NPOI 1.2.4/1.2.5 官方教程 新建Excel HSSFWorkbookhssfworkbook=newHSSFWorkbook(); ISheetsheet1=hssfworkbook.CreateSheet("sh...
C#工具库-NPOI
最新发布
小红楼
08-16 1773
NPOI是一个基于c#语言的，开源的，能够在不安装Microsoft Office组件的条件下读写Microsoft Office 的库。前身是Java的POI库,有“先贤”将其翻译成了c#语言的库，而这种由java到c#库的演变并非个例，比如DotNetty之于Netty之于在我的有限认知里面它算是c#里面读写excel最好的库（主观）。开源协议宽松和读写速度快。C#编程中引用NPOI类库的方法也很简单，通过Nuget进行包管理。项目中需要引用第三方库时，强烈建议优先使用nuget方式进行引用。
NPOI之Excel——合并单元格、设置样式、输入公式
极客神殿
03-02 4536
首先建立一个空白的工作簿用作测试，并在其中建立空白工作表，在表中建立空白行，在行中建立单元格，并填入内容： //建立空白工作簿 IWorkbook workbook = new HSSFWorkbook(); //在工作簿中：建立空白工作表 ISheet sheet = workbook.CreateSheet(); //在工作表中：建立行，参数为行号，从0计 IRow row = sheet.CreateRow(0); //在行中：建立单元格，参数为列号，从0计 ICell cell = row.Crea
NPOI使用说明---计算公式
龙傲天的博客
08-27 6021
基本计算 从这节开始，我们将开始学习Excel高级一点的功能--公式。为某个单元格指定公式后，单元格中的类容将根据公式计算得出，如图：图中设置的是一个基本表达式”1+2*3”，单元格A1中将显示此表达式计算的结果”7”，如图所示。对应的C#生成代码也很简单，如下： HSSFSheet sheet1 = hssfworkbook.CreateSheet("Sheet1"); HSSFRow row1...
NPOI使用手册
01-31
### NPOI 使用手册与案例详解 #### 一、NPOI简介 **NPOI** 是一个基于 Apache POI 的 .NET 库，用于在没有安装 Microsoft Office 的情况下读写 Office 文件。它最初是为了满足 .NET 开发者在 C# 和其他 .NET 语言...
NPOI使用手册.doc
07-15
2. **版权说明**：NPOI遵循Apache 2.0许可证，这意味着可以自由地在商业或非商业项目中使用，但需保留版权信息，并对源代码修改进行明确标识。 **使用NPOI生成XLS文件** 1. **创建Workbook和Sheet**：首先创建...
NPOI使用手册及dll问卷集
08-31
4. **公式和函数支持**：NPOI不仅支持写入静态数据，还支持Excel的内置公式和函数，使得数据计算和分析变得简单。 5. **读取Excel文件**：除了写入，NPOI还能读取已有的Excel文件，获取其中的单元格数据、样式和...
NPOI封装C#函数集合：个人常用工具方法
使用NPOI可以带来以下几个方面的优势： - 独立于Office环境：可以在没有安装Office的服务器或者开发环境中运行。 - 提升性能：相比使用Office自动化对象，NPOI在处理大型文档时具有更好的性能。 - 安全性：避免了...
C#NPOI获取EXCEL公式计算值方法.txt
07-23
C#NPOI获取EXCEL单元格公式计算值,测试日期、数字和字符串均没有问题,参数类型：ICell，没用不收费，请勿转发，个人原创。
c#源码NPOI常用方法使用实例
03-25
npoi的demo实例，欢迎下载使用。包含各种实例及函数的调用。
NPOI库文件下载。
10-26
NPOI库文件，支持网站对office的各种操作，。。。。。。。。。如果需要支持识别word系统自带编号请给我留言。。。。。。。。。。。。。。。
NPOI公式计算、工作薄保护(备忘)
leo628的博客
04-30 1226
1、NPOI操作Excel ，在公式计算这一块会出现公式相关联单元格数值更新，公式对应值没有更新，仅需使用NPOI下的HSSFFormulaEvaluator类的静态函数EvaluateAllFormulaCells 对整个工作薄进行重新计算即可 例子：HSSFFormulaEvaluator.EvaluateAllFormulaCells(workbook); 2、对指定工作薄进行加密保护 ...
NPOI 单元格套用公式
liangyaomu的专栏
09-09 5921
HSSFWorkbook hssfworkbook = new HSSFWorkbook(); ISheet sheet = hssfworkbook.CreateSheet("Sheet1"); hssfworkbook.CreateSheet("Sheet2"); hssfworkbook.CreateSheet("Sheet3"); //Title IRow row = sh
npoi 执行公式_c# NPOI通过单元格里的公式，计算数值
weixin_39705850的博客
12-22 1242
业务场景公司B是母公司A的子公司，每个月都需要将耗材销售情况统计向总公司报账。其中计算的内容如下：1、该时间段客户a、b、c 。。。z的分别购买耗材金额，即该客户端销售额2、对于a、b、c 。。。z公司，每销售一个单位数量的耗材都有居间费(抽成)以上Excel中A-P列是从系统导出来的，其中Q(佣金总额)、P(业绩)列是公式拖拽生成的。Q列的公式 H2 * M2(M列如果没有，默认为0)R列的公式...
NPOI 1.2.3教程 - 4 基本公式 BasicFormula
Taskcnjia的专栏
01-18 1902
<br />using System; using System.Text; using System.IO; using NPOI.HSSF.UserModel; using NPOI.HPSF; using NPOI.POIFS.FileSystem; using NPOI.SS.UserModel; namespace UseBasicFormulaInXls { class Program { static void Main(strin
3.5 用NPOI操作EXCEL－－巧妙使用Excel Chart
06-06
好的，我理解了你的问题。关于用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、付费专栏及课程。

余额充值