NPOI使用说明---计算公式

最新推荐文章于 2024-08-16 15:01:39 发布

歪歪云

最新推荐文章于 2024-08-16 15:01:39 发布

阅读量5.8k

点赞数

分类专栏： NPOI

NPOI 专栏收录该内容

11 篇文章 1 订阅

订阅专栏

基本计算
从这节开始，我们将开始学习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的单元格命名规则来的。
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中有非常丰富的日期处理函数，在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)

字符串函数
      这一节我们开始学习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)：比较两个字符串是否相等，区分大小写。

函数名	函数说明	语法
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)