NPOI使用说明
来源:互联网 发布:lof基金 知乎 编辑:程序博客网 时间:2024/06/05 06:34
要创建一个新的xls文件其实很简单,只要我们初始化一个新的HSSFWorkbook实例就行了,如下所示:
using NPOI.HSSF.UserModel;
...
HSSFWorkbookhssfworkbook =newHSSFWorkbook();
是不是很方便啊,没有任何参数或设置,但这么创建有一些限制,这样创建出来的Workbook在Excel中打开是会报错的,因为Excel规定一个Workbook必须至少带1个Sheet,这也是为什么在Excel界面中,新建一个Workbook默认都会新建3个Sheet。所以必须加入下面的创建Sheet的代码才能保证生成的文件正常:
HSSFSheetsheet = hssfworkbook.CreateSheet("newsheet");
如果要创建标准的Excel文件,即拥有3个Sheet,可以用下面的代码:
hssfworkbook.CreateSheet("Sheet1");
hssfworkbook.CreateSheet("Sheet2");
hssfworkbook.CreateSheet("Sheet3");
最后就是把这个HSSFWorkbook实例写入文件了,代码也很简单,如下所示:
FileStreamfile =new FileStream(@"test.xls", FileMode.Create);
hssfworkbook.Write(file);
file.Close();
这里假设文件名是test.xls,,在创建完FileStream之后,直接调用HSSFWorkbook类的Write方法就可以了。
最后你可以打开test.xls文件确认一下,是不是有3个空的Sheet。
要创建单元格首先要创建单元格所在的行,比如,下面的代码创建了第0行:
HSSFSheetsheet1 = hssfworkbook.CreateSheet("Sheet1");
HSSFRowrow1=sheet1.CreateRow(0);
行建好了,就可以建单元格了,比如创建A1位置的单元格:
row1.CreateCell(0).SetCellValue(1);
这里要说明一下,SetCellValue有好几种重载,你可以设置单元格为bool、double、DateTime、string和HSSFRichTextString类型。其中对于string类型的重载调用的就是HSSFRichTextString类型的重载,所以是一样的,HSSFRichTextString可用于有字体或者Unicode的文本。
如果你觉得每一行要声明一个HSSFRow很麻烦,可以用下面的方式:
sheet1.CreateRow(0).CreateCell(0).SetCellValue("This is a Sample");
这么用有个前提,那就是第0行还没创建过,否则得这么用:
sheet1.GetRow(0).CreateCell(0).SetCellValue("This is a Sample");
注意:这里的行在Excel里是从1开始的,但是NPOI内部是从0开始的;列在Excel里面是用字母表示的,而NPOI中也是用从0开始的数字表示的,所以要注意转换。
如果你要获得某一个已经创建的单元格对象,可以用下面的代码:
sheet1.GetRow(row_index).GetCell(column_index);
批注的位置和大小,在Excel中是与单元格密切相关的,NPOI中通过HSSFClientAnchor的实例来表示,它的构造函数比较复杂,有8个参数,它们分别是
参数
说明
dx1
第1个单元格中x轴的偏移量
dy1
第1个单元格中y轴的偏移量
dx2
第2个单元格中x轴的偏移量
dy2
第2个单元格中y轴的偏移量
col1
第1个单元格的列号
row1
第1个单元格的行号
col2
第2个单元格的列号
row2
第2个单元格的行号
例如,如果我们打算让注释显示在B3和E5之间,就应该这么写:
HSSFPatriarchpatr = sheet.CreateDrawingPatriarch();
HSSFCommentcomment1 = patr.CreateComment(new HSSFClientAnchor(0, 0, 0, 0, 1, 2 , 4, 4));
下面我们设置这个批注的内容和作者,这个比较简单:
comment1.String=newHSSFRichTextString("Hello World");
comment1.Author="NPOI Team";
最后一步就是把批注赋给某个单元格:
HSSFCell cell= sheet.CreateRow(1).CreateCell(1);
cell.CellComment= comment1;
对于批注,你有两种选择,一种是隐藏(默认),一种是显示(即表单一打开就显示该批注),可以通过comment1.Visible属性来控制。
看了上面这张图大家就应该明白了,这里有2个批注,下面那个是显示的,上面那个是隐藏的。
很多人并不知道Excel的页眉和页脚功能,因为在界面上是显示不了页眉和页脚的,必须在打印页面中才能看到,这也直接导致了其设置界面也显得更隐秘,你必须进入页面设置 –>页眉和页脚才能设置。以下是Office 2007中的设置界面。
当你按“自定义页眉”或“自定义页脚”时,你会看到以下界面,Excel把页眉、页脚分成了左中右三部分,这一点绝非单纯体现在界面上,在底层的存储中也是如此。如果你设置的是“左”的内容,底层的存储字符串就会在开头加上&L,如果是“右”的内容则会加上&R,所以HeaderRecord中的字符串看上去是这样的:"&C&LFooter A&R”,这个字符串的意思是仅设置了“左”的内容,内容是Footer A。
看了这些我想你应该对页眉和页脚有所了解了,回过头来说NPOI,NPOI中主要是靠HSSFSheet.Header和HSSFSheet.Footer来设置的,这两个属性分别是HSSFHeader和HSSFFooter类型的。
参考代码如下:
HSSFSheet s1= hssfworkbook.CreateSheet("Sheet1");
s1.CreateRow(0).CreateCell(1).SetCellValue(123);
//set headertext
s1.Header.Center="This is a test sheet";
//set footertext
s1.Footer.Left="Copyright NPOI Team";
s1.Footer.Right="created by Tony Qu(瞿杰)";
以上代码中我添加了页眉的Center内容,Footer的Left和Right内容,在打印预览中看到的效果大概是这样的:
页眉
页脚
至于一些Excel特殊字符,比如说页码可以用&P,当前日期可以用&D,其他的东西你就自己研究吧。
在Excel中我们经常要设置格式,比如说日期格式(yyyymmdd)、小数点格式(1.20)、货币格式($2000)、百分比格式(99.99%)等等,这些东西在过去我们恐怕只能在服务器端生成好,不但增加了服务器端的代码量,还造成了不必要的字符串替换操作,如今NPOI将让服务器从这种完全没有必要的操作中解放出来,一切都将由Excel在客户端处理。
使用NPOI时要注意,所有的格式都是通过CellStyle.DataFormat赋给单元格的,而不是直接赋给单元格。
案例一 日期格式
假设我们现在需要显示的日期的格式为2008年5月5日,可以用下面的代码生成:
HSSFSheetsheet = hssfworkbook.CreateSheet("newsheet");
HSSFCell cell= sheet.CreateRow(0).CreateCell(0);
cell.SetCellValue(newDateTime(2008,5,5));
//set dateformat
HSSFCellStylecellStyle = hssfworkbook.CreateCellStyle();
HSSFDataFormatformat = hssfworkbook.CreateDataFormat();
cellStyle.DataFormat= format.GetFormat("yyyy年m月d日");
cell.CellStyle=cellStyle;
由于这里的“yyyy年m月d日”属于自定义格式(区别于Excel内嵌的格式),所以必须用hssfworkbook.CreateDataFormat()创建一个HSSFDataFormat实例,然后使用format.GetFormat来获取相应的格式,只要是Excel支持的格式表示方式,这种方式都能够实现。
案例二保留2位小数
假设我们有个单元格的值为1.2,怎么显示成1.20呢?在Excel中可以用“0.00”来表示,所以下面的代码就能完成:
// Create arow and put some cells in it. Rows are 0 based.
HSSFCell cell= sheet.CreateRow(0).CreateCell(0);
//set valuefor the cell
cell.SetCellValue(1.2);
//numberformat with 2 digits after the decimal point - "1.20"
HSSFCellStylecellStyle = hssfworkbook.CreateCellStyle();
cellStyle.DataFormat= HSSFDataFormat.GetBuiltinFormat("0.00");
cell.CellStyle= cellStyle;
这里与上面有所不同,用的是HSSFDataFormat.GetBuiltinFormat()方法,之所以用这个,是因为0.00是Excel内嵌的格式,完整的Excel内嵌格式列表大家可以看这个窗口中的自定义列表:
这里就不一一列出了。
案例三货币格式
货币格式在金融的项目中经常用到,比如说人民币符号¥,美元符号$等,这里可以用下面的代码表示:
HSSFCellcell2 = sheet.CreateRow(1).CreateCell(0);
cell2.SetCellValue(20000);
HSSFCellStylecellStyle2 = hssfworkbook.CreateCellStyle();
HSSFDataFormatformat = hssfworkbook.CreateDataFormat();
cellStyle2.DataFormat= format.GetFormat("¥#,##0");
cell2.CellStyle= cellStyle2;
注意,这里还加入了千分位分隔符,所以是#,##,至于为什么这么写,你得去问微软,呵呵。
案例四百分比
百分比在报表中也很常用,其实基本上和上面一样,只是格式表示是0.00%,代码如下:
cellStyle4.DataFormat= HSSFDataFormat.GetBuiltinFormat("0.00%");
由于这里是内嵌格式,所以直接用HSSFDataFormat.GetBuiltinFormat即可。
案例五中文大写
在表示金额时,我们时常会用到,我也见过不少兄弟实现了数字转中文大小写的工具类,以后你可以尝试让Excel去处理这一切,代码和刚才差不多,也是改格式的表示:
HSSFDataFormatformat = hssfworkbook.CreateDataFormat();
cellStyle6.DataFormat= format.GetFormat("[DbNum2][$-804]0");
由于是自定义格式,所以用了HSSFDataFormat.GetFormat,相信你对这两种获取格式的形式的区别越来越熟悉了。
案例六科学计数法
这东西数学课上我们都学过,虽然用的不多,但是既然Excel支持,这里也提一下:
cellStyle3.DataFormat= HSSFDataFormat.GetBuiltinFormat("0.00E+00");
下面展示下以上这些例子的显示效果:
最后总结一下HSSFDataFormat.GetFormat和HSSFDataFormat.GetBuiltinFormat的区别:
当使用Excel内嵌的(或者说预定义)的格式时,直接用HSSFDataFormat.GetBuiltinFormat静态方法即可。
当使用自己定义的格式时,必须先调用HSSFWorkbook.CreateDataFormat(),因为这时在底层会先找有没有匹配的内嵌FormatRecord,如果没有就会新建一个FormatRecord,所以必须先调用这个方法,然后你就可以用获得的HSSFDataFormat实例的GetFormat方法了,当然相对而言这种方式比较麻烦,所以内嵌格式还是用HSSFDataFormat.GetBuiltinFormat静态方法更加直接一些。不过自定义的格式也不是天马行空随便定义,还是要参照Excel的格式表示来定义,具体请看相关的Excel教程。
注意:自定义的FormatRecord是嵌入xls文件内部的,所以不用担心对方Excel中有没有定义过这种格式,都是能够正常使用的。
合并单元格在制作表格时很有用,比如说表格的标题就经常是把第一行的单元格合并居中。那么在NPOI中应该如何实现单元格的合并呢?
为了实现这一功能,NPOI引入了新的概念,即Region,因为合并单元格,其实就是设定一个区域。下面说一下Region类的参数,Region总共有4个参数,如下所示
Region的参数
说明
FirstRow
区域中第一个单元格的行号
FirstColumn
区域中第一个单元格的列号
LastRow
区域中最后一个单元格的行号
LastColumn
区域中最后一个单元格的列号
由于单元格的合并都是在表的基础上建立的,所以我们得先建Sheet:
HSSFWorkbookhssfworkbook =newHSSFWorkbook();
HSSFSheetsheet = hssfworkbook.CreateSheet("newsheet");
接下来我们根据实际场景来做一些演示。
场景一标题行的合并
这种场景是最常见的,比如说我们要建立一张销售情况表,英文叫Sales Report
我们先设置居中和字体样式,这里我们采用20号字体,代码如下:
HSSFRow row =sheet.CreateRow(0);
HSSFCell cell= row.CreateCell(0);
cell.SetCellValue("Sales Report");
HSSFCellStylestyle = hssfworkbook.CreateCellStyle();
style.Alignment= HSSFCellStyle.ALIGN_CENTER;
HSSFFont font= hssfworkbook.CreateFont();
font.FontHeight= 20*20;
style.SetFont(font);
cell.CellStyle= style;
要产生图中的效果,即把A1:F1这6个单元格合并,然后添加合并区域:
sheet.AddMergedRegion(new Region(0, 0, 0,5));
场景二多行合并
看完场景一,你可不要认为多行合并就需要一行一行做,其实也只需要一行代码,比如说我们要把C3:E5合并为一个单元格,那么就可以用下面的代码:
sheet.AddMergedRegion(new Region(2, 2, 4,4));
提示即使你没有用CreateRow和CreateCell创建过行或单元格,也完全可以直接创建区域然后把这一区域合并,Excel的区域合并信息是单独存储的,和RowRecord、ColumnInfoRecord不存在直接关系。
本节将围绕“对齐”选项卡中的设置展开,虽然实际上你会发现该选项卡中的很多设置和对齐没有什么关系。合并单元格已经在2.2.2节讲过了,这里就不提了。
首先我们用代码创建必要的单元格,代码如下:
HSSFWorkbookhssfworkbook =new HSSFWorkbook();
HSSFSheetsheet1 = hssfworkbook.CreateSheet("Sheet1");
HSSFRow row =sheet1.CreateRow(0);
row.CreateCell(0).SetCellValue("Test");
这里我们假设在A0单元格中加入了文本Test。
请注意接下来我们要做的所有操作都是在CellStyle的基础上完成的,所以我们创建一个HSSFCellStyle:
HSSFCellStylestyle=hssfworkbook.CreateCellStyle();
水平对齐
这里用的是HSSFCellStyle.Alignment,默认值自然是常规,即HSSFCellStyle.ALIGN_GENERAL。
如果是左侧对齐就是
style.Alignment= HSSFCellStyle.ALIGN_LEFT;
如果是居中对齐就是
style.Alignment= HSSFCellStyle.ALIGN_CENTER;
如果是右侧对齐就是
style.Alignment= HSSFCellStyle.ALIGN_RIGHT;
如果是跨列举中就是
style.Alignment= HSSFCellStyle.ALIGN_CENTER_SELECTION;
如果是两端对齐就是
style.Alignment= HSSFCellStyle.ALIGN_JUSTIFY;
如果是填充就是
style.Alignment= HSSFCellStyle.ALIGN_FILL;
注意:以上选项仅当有足够的宽度时才能产生效果,不设置宽度恐怕看不出区别。
垂直对齐
这里用的是HSSFCellStyle.VerticalAlignment,默认值为居中,即HSSFCellStyle.VERTICAL_CENTER
如果是靠上就是
style.VerticalAlignment=HSSFCellStyle.VERTICAL_TOP
如果是居中就是
style.VerticalAlignment=HSSFCellStyle.VERTICAL_CENTER
如果是靠下就是
style.VerticalAlignment=HSSFCellStyle.VERTICAL_BOTTOM
如果是两端对齐就是
style.VerticalAlignment=HSSFCellStyle.VERTICAL_JUSTIFY
注意:以上选项仅当有足够的高度时才能产生效果,不设置高度恐怕看不出区别。
自动换行
自动换行翻译成英文其实就是Wrap的意思,所以这里我们应该用WrapText属性,这是一个布尔属性
style.WrapText=true;
效果如下所示:
文本缩进
这是一个不太引人注意的选项,所以这里给张图出来,让大家知道是什么,缩进说白了就是文本前面的空白,我们同样可以用属性来设置,这个属性叫做Indention。
style.Indention= 3;
文本旋转
文本方向大家一定在Excel中设置过,上图中就是调整界面,主要参数是度数,那么我们如何在NPOI中设置呢?
style.Rotation=(short)90;
以上代码是把单元格A1中的文本逆时针旋转90度,等同于下图中的设置:
重点讲解NPOI中边框的设置和使用。
边框和其他单元格设置一样也是在HSSFCellStyle上操作的,HSSFCellStyle有2种和边框相关的属性,分别是:
边框相关属性
说明
范例
Border+方向
边框类型
BorderTop, BorderBottom,BorderLeft, BorderRight
方向+BorderColor
边框颜色
TopBorderColor,BottomBorderColor, LeftBorderColor, RightBorderColor
其中边框类型分为以下几种:
边框范例图
对应的静态值
HSSFCellStyle.BORDER_DOTTED
HSSFCellStyle.BORDER_HAIR
HSSFCellStyle.BORDER_DASH_DOT_DOT
HSSFCellStyle.BORDER_DASH_DOT
HSSFCellStyle.BORDER_DASHED
HSSFCellStyle.BORDER_THIN
HSSFCellStyle.BORDER_MEDIUM_DASH_DOT_DOT
HSSFCellStyle.BORDER_SLANTED_DASH_DOT
HSSFCellStyle.BORDER_MEDIUM_DASH_DOT
HSSFCellStyle.BORDER_MEDIUM_DASHED
HSSFCellStyle.BORDER_MEDIUM
HSSFCellStyle.BORDER_THICK
HSSFCellStyle.BORDER_DOUBLE
至于颜色那就很多了,全部在HSSFColor下面,如HSSFColor.GREEN,HSSFColor.RED,都是静态实例,可以直接引用。
下面我们假设我们要把一个单元格的四周边框都设置上,可以用下面的代码:
HSSFSheetsheet = hssfworkbook.CreateSheet("newsheet");
// Create arow and put some cells in it. Rows are 0 based.
HSSFRow row =sheet.CreateRow(1);
// Create acell and put a value in it.
HSSFCell cell= row.CreateCell(1);
// Style thecell with borders all around.
HSSFCellStylestyle = hssfworkbook.CreateCellStyle();
style.BorderBottom=HSSFCellStyle.BORDER_THIN;
style.BorderLeft=HSSFCellStyle.BORDER_THIN;
style.BorderRight=HSSFCellStyle.BORDER_THIN;
style.BorderTop= HSSFCellStyle.BORDER_THIN ;
cell.CellStyle=style;
这段代码使用了最普通的细边框,使得这个单元格看上去像块空心砖头。
注意:这里我们没有设置边框的颜色,但这不会影响最终的效果,因为Excel会用默认的黑色给边框上色。
如果要设置颜色的话,也很简单,如下:
style.BottomBorderColor=HSSFColor.GREEN.index;
以上代码将底部边框设置为绿色,要注意,不是直接把HSSFColor.GREEN赋给XXXXBorderColor属性,而是把index的值赋给它。
我们主要讲如何设置“字体”。
在设置字体之前,我们首先要做的就是创建字体对象,这和创建数字格式很相似。
HSSFFont font= hssfworkbook.CreateFont();
这句话会在Excel文件内部创建相应的FontRecord,所以你不用客户因为自己机器上的Excel没有相应的字体设置而导致设置丢失。
字体在设置完成后,我们就可以把它赋给单元格样式,代码如下:
HSSFCellStylestyle1 = hssfworkbook.CreateCellStyle();
style1.SetFont(font);
cell1.CellStyle=style1;
这里的cell1是HSSFCell的一个实例。
好了,下面我们就开始对字体进行设置。
字体名称
这里的字体名称是通过HSSFFont.FontName进行设置的,至于具体的名称,只要是常用字体都可以,比如说Arial, Verdana等,当然也可以是中文字体名,如宋体、黑体等。不过设置字体名称有个前提,那就是假设打开这个xls文件的客户机上有这种字体,如果没有,Excel将使用默认字体。
下面就是设置字体名称为“宋体”的代码:
font.FontName="宋体";
字号
与字号有关的属性有两个,一个是FontHeight,一个是FontHeightInPoints。区别在于,FontHeight的值是FontHeightInPoints的20倍,通常我们在Excel界面中看到的字号,比如说12,对应的是FontHeightInPoints的值,而FontHeight要产生12号字体的大小,值应该是240。所以通常建议你用FontHeightInPoint属性。
如果要设置字号为12,代码就是
font.FontHeightInPoints= 12;
字体颜色
这里可能会与CellStyle上的ForegroundColor和BackgroundColor产生混淆,其实所有的字体颜色都是在HSSFFont的实例上设置的,CellStyle的ForegroundColor和BackgroundColor分别指背景填充色和填充图案的颜色,和文本颜色无关。
要设置字体颜色,我们可以用HSSFFont.Color属性,颜色可以通过HSSFColor获得,代码如下所示:
font.Color =HSSFColor.RED.index;
这行代码把文本设置为红色。
下划线
通常我们所说的下划线都是单线条的,其实Excel支持好几种下划线,如下所示:
类型
对应的值
单下划线
HSSFFont.U_SINGLE
双下划线
HSSFFont.U_DOUBLE
会计用单下划线
HSSFFont.U_SINGLE_ACCOUNTING
会计用双下划线
HSSFFont.U_DOUBLE_ACCOUNTING
无下划线
HSSFFont.U_NONE
当你要设置下划线时,可以用HSSFFont.Underline属性,这是一个byte类型的值,例如
font.Underline=HSSFFont.U_SINGLE
这行代码就是设置单下划线的代码。
上标下标
设置这东西可以用HSSFFont.TypeOffset属性,值有以下几种:
TypeOffset的值
说明
HSSFFont.SS_SUPER
上标
HSSFFont.SS_SUB
下标
HSSFFont.SS_NONE
普通,默认值
所以如果你要上标的话,可以用下面的代码:
font.TypeOffset=HSSFFont.SS_SUPER;
删除线
设置这东西可以用HSSFFont.IsStrikeout属性,当为true时,表示有删除线;为false则表示没有删除线。
相关范例请参考NPOI 1.2正式版中的ApplyFontInXls的项目。
NPOI 1.2教程 - 2.2.6设置单元格的背景和图案
作者:Tony Qu
NPOI官方网站:http://npoi.codeplex.com/
本节我们将用NPOI来为单元格添加背景和图案。
在之前的教程中,我们已经提到HSSFCellStyle有两个背景颜色属性,一个叫FillBackgroundColor,另一个叫FillForegroundColor,但其实这指的都是背景颜色,那为什么还有ForegroundColor呢?为了能够帮助大家理解,我们举一个实际的例子,下面这个图案是Excel的一个单元格:
线是白色的,背景是红色的。这里的线其实就是下面的Excel界面中的图案:
至于线的颜色则是图案颜色,即白色。
所以以上单元格如果要用NPOI来设置就可以用以下代码完成:
//fillbackground
HSSFCellStylestyle8 = hssfworkbook.CreateCellStyle();
style8.FillForegroundColor= NPOI.HSSF.Util.HSSFColor.WHITE.index;
style8.FillPattern= HSSFCellStyle.SQUARES;
style8.FillBackgroundColor= NPOI.HSSF.Util.HSSFColor.RED.index;
sheet1.CreateRow(7).CreateCell(0).CellStyle= style8;
现在是不是清楚一些了,这里的FillPattern就图案样式,所有的枚举值都是HSSFCellStyle的常量;FillForegroundColor就是图案的颜色,而FillBackgroundColor则是背景的颜色,即红色。
下面罗列一下图案样式及其对应的值:
图案样式
常量
HSSFCellStyle.NO_FILL
HSSFCellStyle.ALT_BARS
HSSFCellStyle.FINE_DOTS
HSSFCellStyle.SPARSE_DOTS
HSSFCellStyle.LESS_DOTS
HSSFCellStyle.LEAST_DOTS
HSSFCellStyle.BRICKS
HSSFCellStyle.BIG_SPOTS
HSSFCellStyle.THICK_FORWARD_DIAG
HSSFCellStyle.THICK_BACKWARD_DIAG
HSSFCellStyle.THICK_VERT_BANDS
HSSFCellStyle.THICK_HORZ_BANDS
HSSFCellStyle.THIN_HORZ_BANDS
HSSFCellStyle.THIN_VERT_BANDS
HSSFCellStyle.THIN_BACKWARD_DIAG
HSSFCellStyle.THIN_FORWARD_DIAG
HSSFCellStyle.SQUARES
HSSFCellStyle.DIAMONDS
通过这张表,你将很容易找到自己需要的样式,不用再去一个一个猜测了。
相关范例请参考NPOI 1.2正式版中的ColorfullMatrixTable和FillBackgroundInXls。
NPOI 1.2教程 - 2.2.7 设置单元格的宽度和高度
作者:Tony Qu
NPOI官方网站:http://npoi.codeplex.com/
在Excel中,单元格的宽度其实就是列的宽度,因为Excel假设这一列的单元格的宽度肯定一致。所以要设置单元格的宽度,我们就得从列的宽度下手,HSSFSheet有个方法叫SetColumnWidth,共有两个参数:一个是列的索引(从0开始),一个是宽度。
现在假设你要设置B列的宽度,就可以用下面的代码:
HSSFWorkbookhssfworkbook =newHSSFWorkbook();
HSSFSheetsheet1 = hssfworkbook.CreateSheet("Sheet1");
sheet1.SetColumnWidth(1,100 * 256);
这里你会发现一个有趣的现象,SetColumnWidth的第二个参数要乘以256,这是怎么回事呢?其实,这个参数的单位是1/256个字符宽度,也就是说,这里是把B列的宽度设置为了100个字符。
刚才说的是如何设置,那如何去读取一个列的宽度呢?直接用GetColumnWidth方法,这个方法只有一个参数,那就是列的索引号。如下所示:
int col1width = sheet1.GetColumnWidth(1);
说完宽度,我们来说高度,在Excel中,每一行的高度也是要求一致的,所以设置单元格的高度,其实就是设置行的高度,所以相关的属性也应该在HSSFRow上,它就是HSSFRow.Height和HeightInPoints,这两个属性的区别在于HeightInPoints的单位是点,而Height的单位是1/20个点,所以Height的值永远是HeightInPoints的20倍。
要设置第一行的高度,可以用如下代码:
sheet1.CreateRow(0).Height= 200*20;
或者
sheet1.CreateRow(0).HeightInPoints= 200;
如果要获得某一行的行高,可以直接拿HSSFRow.Height属性的返回值。
你可能觉得一行一行设置行高或者一列一列设置列宽很麻烦,那你可以考虑使用HSSFSheet.DefaultColumnWidth、HSSFSheet.DefaultRowHeight和HSSFSheet.DefaultRowHeightInPoints属性。
一旦设置了这些属性,如果某一行或者某一列没有设置宽度,就会使用默认宽度或高度。代码如下:
sheet1.DefaultColumnWidth=100*256;
sheet1.DefaultRowHeight=30*20;
基本计算
从这节开始,我们将开始学习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函数
这节我们开始学习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如下图所示:
日期函数
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):比较两个字符串是否相等,区分大小写。
执行效果如下:
在此只简单的讲了一下常用的函数,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分以上为合格,否则为不合格。
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
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)。还是以例子加以说明更容易理解:
Code
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能不能获取公式的返回值呢?”,答案是可以!
一、获取模板文件中公式的返回值
如在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);
画线
之所有说NPOI强大,是因为常用的Excel操作她都可以通过编程的方式完成。这节开始,我们开始学习NPOI的画图功能。先从最简单的开始,画一条直线:
对应的代码为:
HSSFSheet sheet1 = hssfworkbook.CreateSheet("Sheet1");
HSSFPatriarch patriarch = sheet1.CreateDrawingPatriarch();
HSSFClientAnchor a1 = new HSSFClientAnchor(255, 125, 1023, 150, 0, 0,2, 2);
HSSFSimpleShape line1 = patriarch.CreateSimpleShape(a1);
line1.ShapeType = HSSFSimpleShape.OBJECT_TYPE_LINE;
line1.LineStyle = HSSFShape.LINESTYLE_SOLID;
//在NPOI中线的宽度12700表示1pt,所以这里是0.5pt粗的线条。
line1.LineWidth = 6350;
通常,利用NPOI画图主要有以下几个步骤:
1. 创建一个Patriarch;
2. 创建一个Anchor,以确定图形的位置;
3. 调用Patriarch创建图形;
4. 设置图形类型(直线,矩形,圆形等)及样式(颜色,粗细等)。
关于HSSFClientAnchor(dx1,dy1,dx2,dy2,col1,row1,col2,row2)的参数,有必要在这里说明一下:
dx1:起始单元格的x偏移量,如例子中的255表示直线起始位置距A1单元格左侧的距离;
dy1:起始单元格的y偏移量,如例子中的125表示直线起始位置距A1单元格上侧的距离;
dx2:终止单元格的x偏移量,如例子中的1023表示直线起始位置距C3单元格左侧的距离;
dy2:终止单元格的y偏移量,如例子中的150表示直线起始位置距C3单元格上侧的距离;
col1:起始单元格列序号,从0开始计算;
row1:起始单元格行序号,从0开始计算,如例子中col1=0,row1=0就表示起始单元格为A1;
col2:终止单元格列序号,从0开始计算;
row2:终止单元格行序号,从0开始计算,如例子中col2=2,row2=2就表示起始单元格为C3;
最后,关于LineStyle属性,有如下一些可选值,对应的效果分别如图所示:
2.4.2用NPOI操作EXCEL--画矩形
上一节我们讲了NPOI中画图的基本步骤:
1. 创建一个Patriarch;
2. 创建一个Anchor,以确定图形的位置;
3. 调用Patriarch创建图形;
4. 设置图形类型(直线,矩形,圆形等)及样式(颜色,粗细等)。
这一节我们将按照这个步骤创建一个矩形。废话少说,上代码:
HSSFSheet sheet1 = hssfworkbook.CreateSheet("Sheet1");
HSSFPatriarch patriarch = sheet1.CreateDrawingPatriarch();
HSSFClientAnchor a1 = new HSSFClientAnchor(255, 125, 1023, 150, 0, 0, 2, 2);
HSSFSimpleShape rec1 = patriarch.CreateSimpleShape(a1);
//此处设置图形类型为矩形
rec1.ShapeType = HSSFSimpleShape.OBJECT_TYPE_RECTANGLE;
//设置填充色
rec1.SetFillColor(125, 125, 125);
//设置边框样式
rec1.LineStyle = HSSFShape.LINESTYLE_DASHGEL;
//设置边框宽度
rec1.LineWidth = 25400;
//设置边框颜色
rec1.SetLineStyleColor(100, 0, 100);
代码执行效果:
其中SetFillColor和SetLineStyleColor函数的三个参数分别是RGB三色值,具体表示什么颜色,找个Photoshop试试:)
关于HSSFClientAnchor参数说明、边框样式,边框宽度的说明可以参见前一篇博文:
http://www.cnblogs.com/atao/archive/2009/09/13/1565645.html
2.4.3用NPOI操作EXCEL--画圆形
前面我们学习了NPOI中的画简单直线和矩形的功能,今天我们一起学习一下它支持的另一种简单图形--圆形。同样,按照前面所讲的绘图“四步曲”:
1. 创建一个Patriarch;
2. 创建一个Anchor,以确定图形的位置;
3. 调用Patriarch创建图形;
4. 设置图形类型(直线,矩形,圆形等)及样式(颜色,粗细等)。
还是以例子加以说明:
HSSFSheet sheet1 = hssfworkbook.CreateSheet("Sheet1");
HSSFPatriarch patriarch = sheet1.CreateDrawingPatriarch();
HSSFClientAnchor a1 = new HSSFClientAnchor(0, 0, 1023, 0, 0, 0, 1, 3);
HSSFSimpleShape rec1 = patriarch.CreateSimpleShape(a1);
rec1.ShapeType = HSSFSimpleShape.OBJECT_TYPE_OVAL;
rec1.SetFillColor(125, 125, 125);
rec1.LineStyle = HSSFShape.LINESTYLE_DASHGEL;
rec1.LineWidth = 12700;
rec1.SetLineStyleColor(100, 0, 100);
WriteToFile();
这里rec1.ShapeType =HSSFSimpleShape.OBJECT_TYPE_OVAL;表示图形为椭圆。适当调整HSSFClientAnchor的各参数可以得到圆形。
关于HSSFClientAnchor构造函数和边框、填充色等前两节都有介绍,这里不再重述。详情情见:画矩形和画线。
上面代码执行生成的Excel如下:
2.4.4用NPOI操作EXCEL--画Grid
在NPOI中,本身没有画Grid的方法。但我们知道Grid其实就是由横线和竖线构成的,所在我们可以通过画线的方式来模拟画Grid。
HSSFSheet sheet1 = hssfworkbook.CreateSheet("Sheet1");
HSSFRow row = sheet1.CreateRow(2);
row.CreateCell(1);
row.HeightInPoints = 240;
sheet1.SetColumnWidth(2, 9000);
int linesCount = 20;
HSSFPatriarch patriarch = sheet1.CreateDrawingPatriarch();
//因为HSSFClientAnchor中dx只能在0-1023之间,dy只能在0-255之间,所以这里采用比例的方式
double xRatio = 1023.0 / (linesCount*10);
double yRatio = 255.0 / (linesCount*10);
//画竖线
int x1 = 0;
int y1 = 0;
int x2 = 0;
int y2 = 200;
for (int i = 0; i < linesCount; i++)
{
HSSFClientAnchor a2 = new HSSFClientAnchor();
a2.SetAnchor((short)2, 2, (int)(x1 * xRatio), (int)(y1 * yRatio),
(short)2, 2, (int)(x2 * xRatio), (int)(y2 * yRatio));
HSSFSimpleShape shape2 = patriarch.CreateSimpleShape(a2);
shape2.ShapeType = (HSSFSimpleShape.OBJECT_TYPE_LINE);
x1 += 10;
x2 += 10;
}
//画横线
x1 = 0;
y1 = 0;
x2 = 200;
y2 = 0;
for (int i = 0; i < linesCount; i++)
{
HSSFClientAnchor a2 = new HSSFClientAnchor();
a2.SetAnchor((short)2, 2, (int)(x1 * xRatio), (int)(y1 * yRatio),
(short)2, 2, (int)(x2 * xRatio), (int)(y2 * yRatio));
HSSFSimpleShape shape2 = patriarch.CreateSimpleShape(a2);
shape2.ShapeType = (HSSFSimpleShape.OBJECT_TYPE_LINE);
y1 += 10;
y2 += 10;
}
请注意HSSFClientAnchor对象中的dx只能取0-1023之间的数,dy只能取0-255之间的数。我们可以理解为是将单元格的宽和高平分成了1023和255份,设置dx和dy时相当于按比例取对应的座标。最终生成的Excel如下:
2.4.5用NPOI操作EXCEL--插入图片
我们知道,在Excel中是可以插入图片的。操作菜单是“插入->图片”,然后选择要插入图片,可以很容易地在Excel插入图片。同样,在NPOI中,利用代码也可以实现同样的效果。在NPOI中插入图片的方法与画图的方法有点类似:
//add picture data to this workbook.
byte[] bytes = System.IO.File.ReadAllBytes(@"D:\MyProject\NPOIDemo\ShapeImage\image1.jpg");
int pictureIdx = hssfworkbook.AddPicture(bytes, HSSFWorkbook.PICTURE_TYPE_JPEG);
//create sheet
HSSFSheet sheet = hssfworkbook.CreateSheet("Sheet1");
// Create the drawing patriarch. This is the top level container for all shapes.
HSSFPatriarch patriarch = sheet.CreateDrawingPatriarch();
//add a picture
HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 1023, 0, 0, 0, 1, 3);
HSSFPicture pict = patriarch.CreatePicture(anchor, pictureIdx);
与画简单图形不同的是,首先要将图片读入到byte数组,然后添加到workbook中;最后调用的是patriarch.CreatePicture(anchor, pictureIdx)方法显示图片,而不是patriarch.CreateSimpleShape(anchor)方法。上面这段代码执行后生成的Excel文件样式如下:
我们发现,插入的图片被拉伸填充在HSSFClientAnchor指定的区域。有时可能我们并不需要拉伸的效果,怎么办呢?很简单,在最后加上这样一句用来自动调节图片大小:
pict.Resize();
添加代码后再执行上述代码,生成的Excel样式如下:
图片已经自动伸缩到原始大小了。
设置密码
有时,我们可能需要某些单元格只读,如在做模板时,模板中的数据是不能随意让别人改的。在Excel中,可以通过“审阅->保护工作表”来完成,如下图:
那么,在NPOI中有没有办法通过编码的方式达到这一效果呢?答案是肯定的。
HSSFSheet sheet1 = hssfworkbook.CreateSheet("Sheet1");
HSSFRow row1 = sheet1.CreateRow(0);
HSSFCell cel1 = row1.CreateCell(0);
HSSFCell cel2 = row1.CreateCell(1);
HSSFCellStyle unlocked = hssfworkbook.CreateCellStyle();
unlocked.IsLocked = false;
HSSFCellStyle locked = hssfworkbook.CreateCellStyle();
locked.IsLocked = true;
cel1.SetCellValue("没被锁定");
cel1.CellStyle = unlocked;
cel2.SetCellValue("被锁定");
cel2.CellStyle = locked;
sheet1.ProtectSheet("password");
正如代码中所看到的,我们通过设置CellStype的ISLocked为True,表示此单元格将被锁定。相当于在Excel中执行了如下操作:
然后通过ProtectSheet设置密码。
锁定列
在Excel中,有时可能会出现列数太多或是行数太多的情况,这时可以通过锁定列来冻结部分列,不随滚动条滑动,方便查看。在Excel中设置冻结列的方法如下:
同样,利用NPOI,通过代码也能实现上面的效果:
HSSFSheet sheet1 = hssfworkbook.CreateSheet("Sheet1");
HSSFRow row1 = sheet1.CreateRow(0);
row1.CreateCell(0).SetCellValue("冻结列");
sheet1.CreateFreezePane(1, 0, 1, 0);
代码执行结果如下:
下面对CreateFreezePane的参数作一下说明:
第一个参数表示要冻结的列数;
第二个参数表示要冻结的行数,这里只冻结列所以为0;
第三个参数表示右边区域可见的首列序号,从1开始计算;
第四个参数表示下边区域可见的首行序号,也是从1开始计算,这里是冻结列,所以为0;
举例说明也许更好理解,将各参数设置为如下:
sheet1.CreateFreezePane(2,0,5,0);