基本计算
从这节开始，我们将开始学习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 基于所指定的字符数返回文本串中的第一个或前几个字符。 | LEFT(text,num_chars) |
| LEN | LEN 返回文本串中的字符数。 | LEN(text) |
| LOWER | 将一个文字串中的所有大写字母转换为小写字母。 | LOWER(text) |
| MID | MID 返回文本串中从指定位置开始的特定数目的字符，该数目由用户指定。 | MID(text,start_num,num_chars) |
| PHONETIC | 提取文本串中的拼音 (furigana)字符。 | PHONETIC(reference) |
| PROPER | 将文字串的首字母及任何非字母字符之后的首字母转换成大写。将其余的字母转换成小写。 | PROPER(text) |
| REPLACE | REPLACE 使用其他文本串并根据所指定的字符数替换某文本串中的部分文本。 | REPLACE(old_text,start_num,num_chars,new_text) |
| REPT | 按照给定的次数重复显示文本。可以通过函数 REPT来不断地重复显示某一文字串，对单元格进行填充。 | REPT(text,number_times) |
| RIGHT | RIGHT 根据所指定的字符数返回文本串中最后一个或多个字符。 | RIGHT(text,num_chars) |
| SEARCH | SEARCH 返回从 start_num开始首次找到特定字符或文本串的位置上特定字符的编号。使用 SEARCH可确定字符或文本串在其他文本串中的位置，这样就可使用 MID或 REPLACE 函数更改文本。 | SEARCH(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) |
扫一扫
1078
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
