| POI使用详解 | 您所在的位置：网站首页 › poi3官网注册 › POI使用详解 |
POI使用详解
|
3.Excel的单元格操作
(1)设置格式 HSSFSheet sheet = workbook.createSheet("Test");// 创建工作表(Sheet) HSSFRow row=sheet.createRow(0); //设置日期格式--使用Excel内嵌的格式 HSSFCell cell=row.createCell(0); cell.setCellValue(new Date()); HSSFCellStyle style=workbook.createCellStyle(); style.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy h:mm")); cell.setCellStyle(style); //设置保留2位小数--使用Excel内嵌的格式 cell=row.createCell(1); cell.setCellValue(12.3456789); style=workbook.createCellStyle(); style.setDataFormat(HSSFDataFormat.getBuiltinFormat("0.00")); cell.setCellStyle(style); //设置货币格式--使用自定义的格式 cell=row.createCell(2); cell.setCellValue(12345.6789); style=workbook.createCellStyle(); style.setDataFormat(workbook.createDataFormat().getFormat("￥#,##0")); cell.setCellStyle(style); //设置百分比格式--使用自定义的格式 cell=row.createCell(3); cell.setCellValue(0.123456789); style=workbook.createCellStyle(); style.setDataFormat(workbook.createDataFormat().getFormat("0.00%")); cell.setCellStyle(style); //设置中文大写格式--使用自定义的格式 cell=row.createCell(4); cell.setCellValue(12345); style=workbook.createCellStyle(); style.setDataFormat(workbook.createDataFormat().getFormat("[DbNum2][$-804]0")); cell.setCellStyle(style); //设置科学计数法格式--使用自定义的格式 cell=row.createCell(5); cell.setCellValue(12345); style=workbook.createCellStyle(); style.setDataFormat(workbook.createDataFormat().getFormat("0.00E+00")); cell.setCellStyle(style);
HSSFDataFormat.getFormat和HSSFDataFormat.getBuiltinFormat的区别： 当使用Excel内嵌的（或者说预定义）的格式时，直接用HSSFDataFormat.getBuiltinFormat静态方法即可。当使用自己定义的格式时，必须先调用HSSFWorkbook.createDataFormat()，因为这时在底层会先找有没有匹配的内嵌FormatRecord，如果没有就会新建一个FormatRecord，所以必须先调用这个方法，然后你就可以用获得的HSSFDataFormat实例的getFormat方法了，当然相对而言这种方式比较麻烦，所以内嵌格式还是用HSSFDataFormat.getBuiltinFormat静态方法更加直接一些。 (2)合并单元格 HSSFSheet sheet = workbook.createSheet("Test");// 创建工作表(Sheet) HSSFRow row=sheet.createRow(0); //合并列 HSSFCell cell=row.createCell(0); cell.setCellValue("合并列"); CellRangeAddress region=new CellRangeAddress(0, 0, 0, 5); sheet.addMergedRegion(region); //合并行 cell=row.createCell(6); cell.setCellValue("合并行"); region=new CellRangeAddress(0, 5, 6, 6); sheet.addMergedRegion(region);
CellRangeAddress对象其实就是表示一个区域，其构造方法如下：CellRangeAddress(firstRow, lastRow, firstCol, lastCol)，参数的说明： firstRow 区域中第一个单元格的行号 lastRow 区域中最后一个单元格的行号 firstCol 区域中第一个单元格的列号 lastCol 区域中最后一个单元格的列号提示： 即使你没有用CreateRow和CreateCell创建过行或单元格，也完全可以直接创建区域然后把这一区域合并，Excel的区域合并信息是单独存储的，和RowRecord、ColumnInfoRecord不存在直接关系。 (3)单元格对齐 HSSFCell cell=row.createCell(0); cell.setCellValue("单元格对齐"); HSSFCellStyle style=workbook.createCellStyle(); style.setAlignment(HSSFCellStyle.ALIGN_CENTER);//水平居中 style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);//垂直居中 style.setWrapText(true);//自动换行 style.setIndention((short)5);//缩进 style.setRotation((short)60);//文本旋转，这里的取值是从-90到90，而不是0-180度。 cell.setCellStyle(style);
水平对齐相关参数 如果是左侧对齐就是 HSSFCellStyle.ALIGN_FILL; 如果是居中对齐就是 HSSFCellStyle.ALIGN_CENTER; 如果是右侧对齐就是 HSSFCellStyle.ALIGN_RIGHT; 如果是跨列举中就是 HSSFCellStyle.ALIGN_CENTER_SELECTION; 如果是两端对齐就是 HSSFCellStyle.ALIGN_JUSTIFY; 如果是填充就是 HSSFCellStyle.ALIGN_FILL;垂直对齐相关参数 如果是靠上就是 HSSFCellStyle.VERTICAL_TOP; 如果是居中就是 HSSFCellStyle.VERTICAL_CENTER; 如果是靠下就是 HSSFCellStyle.VERTICAL_BOTTOM; 如果是两端对齐就是 HSSFCellStyle.VERTICAL_JUSTIFY;(4)使用边框 边框和其他单元格设置一样也是调用CellStyle接口，CellStyle有2种和边框相关的属性，分别是: 边框相关属性 说明 范例 Border+ 方向 边框类型 BorderLeft, BorderRight 等 方向 +BorderColor 边框颜色 TopBorderColor,BottomBorderColor 等 HSSFCell cell=row.createCell(1); cell.setCellValue("设置边框"); HSSFCellStyle style=workbook.createCellStyle(); style.setBorderTop(HSSFCellStyle.BORDER_DOTTED);//上边框 style.setBorderBottom(HSSFCellStyle.BORDER_THICK);//下边框 style.setBorderLeft(HSSFCellStyle.BORDER_DOUBLE);//左边框 style.setBorderRight(HSSFCellStyle.BORDER_SLANTED_DASH_DOT);//右边框 style.setTopBorderColor(HSSFColor.RED.index);//上边框颜色 style.setBottomBorderColor(HSSFColor.BLUE.index);//下边框颜色 style.setLeftBorderColor(HSSFColor.GREEN.index);//左边框颜色 style.setRightBorderColor(HSSFColor.PINK.index);//右边框颜色 cell.setCellStyle(style);
其中边框类型分为以下几种： 边框范例图 对应的静态值
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 (5)设置字体 HSSFCell cell = row.createCell(1); cell.setCellValue("设置字体"); HSSFCellStyle style = workbook.createCellStyle(); HSSFFont font = workbook.createFont(); font.setFontName("华文行楷");//设置字体名称 font.setFontHeightInPoints((short)28);//设置字号 font.setColor(HSSFColor.RED.index);//设置字体颜色 font.setUnderline(FontFormatting.U_SINGLE);//设置下划线 font.setTypeOffset(FontFormatting.SS_SUPER);//设置上标下标 font.setStrikeout(true);//设置删除线 style.setFont(font); cell.setCellStyle(style);
下划线选项值： 单下划线 FontFormatting.U_SINGLE 双下划线 FontFormatting.U_DOUBLE 会计用单下划线 FontFormatting.U_SINGLE_ACCOUNTING 会计用双下划线 FontFormatting.U_DOUBLE_ACCOUNTING 无下划线 FontFormatting.U_NONE 上标下标选项值： 上标 FontFormatting.SS_SUPER 下标 FontFormatting.SS_SUB 普通，默认值 FontFormatting.SS_NONE (6)背景和纹理 HSSFCellStyle style = workbook.createCellStyle(); style.setFillForegroundColor(HSSFColor.GREEN.index);//设置图案颜色 style.setFillBackgroundColor(HSSFColor.RED.index);//设置图案背景色 style.setFillPattern(HSSFCellStyle.SQUARES);//设置图案样式 cell.setCellStyle(style);
图案样式及其对应的值： 图案样式 常量
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 (7)设置宽度和高度 HSSFSheet sheet = workbook.createSheet("Test");// 创建工作表(Sheet) HSSFRow row = sheet.createRow(1); HSSFCell cell = row.createCell(1); cell.setCellValue("123456789012345678901234567890"); sheet.setColumnWidth(1, 31 * 256);//设置第一列的宽度是31个字符宽度 row.setHeightInPoints(50);//设置行的高度是50个点
这里你会发现一个有趣的现象，setColumnWidth的第二个参数要乘以256，这是怎么回事呢？其实，这个参数的单位是1/256个字符宽度，也就是说，这里是把B列的宽度设置为了31个字符。 设置行高使用HSSFRow对象的setHeight和setHeightInPoints方法，这两个方法的区别在于setHeightInPoints的单位是点，而setHeight的单位是1/20个点，所以setHeight的值永远是setHeightInPoints的20倍。 你也可以使用HSSFSheet.setDefaultColumnWidth、HSSFSheet.setDefaultRowHeight和HSSFSheet.setDefaultRowHeightInPoints方法设置默认的列宽或行高。 (8)判断单元格是否为日期 判断单元格是否为日期类型，使用DateUtil.isCellDateFormatted(cell)方法，例如： HSSFCell cell = row.createCell(1); cell.setCellValue(new Date());//设置日期数据 System.out.println(DateUtil.isCellDateFormatted(cell));//输出：false HSSFCellStyle style =workbook.createCellStyle(); style.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy h:mm")); cell.setCellStyle(style);//设置日期样式 System.out.println(DateUtil.isCellDateFormatted(cell));//输出：true 4.使用Excel公式(1)基本计算 HSSFSheet sheet = workbook.createSheet("Test");// 创建工作表(Sheet) HSSFRow row = sheet.createRow(0); HSSFCell cell = row.createCell(0); cell.setCellFormula("2+3*4");//设置公式 cell = row.createCell(1); cell.setCellValue(10); cell = row.createCell(2); cell.setCellFormula("A1*B1");//设置公式
(2)SUM函数 HSSFSheet sheet = workbook.createSheet("Test");// 创建工作表(Sheet) HSSFRow row = sheet.createRow(0); row.createCell(0).setCellValue(1); row.createCell(1).setCellValue(2); row.createCell(2).setCellValue(3); row.createCell(3).setCellValue(4); row.createCell(4).setCellValue(5); row = sheet.createRow(1); row.createCell(0).setCellFormula("sum(A1,C1)");//等价于"A1+C1" row.createCell(1).setCellFormula("sum(B1:D1)");//等价于"B1+C1+D1"
(3)日期函数 HSSFSheet sheet = workbook.createSheet("Test");// 创建工作表(Sheet) HSSFCellStyle style=workbook.createCellStyle(); style.setDataFormat(workbook.createDataFormat().getFormat("yyyy-mm-dd")); HSSFRow row = sheet.createRow(0); Calendar date=Calendar.getInstance();//日历对象 HSSFCell cell=row.createCell(0); date.set(2011,2, 7); cell.setCellValue(date.getTime()); cell.setCellStyle(style);//第一个单元格开始时间设置完成 cell=row.createCell(1); date.set(2014,4, 25); cell.setCellValue(date.getTime()); cell.setCellStyle(style);//第一个单元格结束时间设置完成 cell=row.createCell(3); cell.setCellFormula("CONCATENATE(DATEDIF(A1,B1,\"y\"),\"年\")"); cell=row.createCell(4); cell.setCellFormula("CONCATENATE(DATEDIF(A1,B1,\"m\"),\"月\")"); cell=row.createCell(5); cell.setCellFormula("CONCATENATE(DATEDIF(A1,B1,\"d\"),\"日\")");
以上代码中的公式说明： DATEDIF(A1,B1,\"y\") ：取得 A1 单元格的日期与 B1 单元格的日期的时间间隔。 ( “ y ” : 表示以年为单位 , ” m ”表示以月为单位 ; ” d ”表示以天为单位 ) 。
CONCATENATE( str1,str2, … ) ：连接字符串。 更多 Excel 的日期函数可参考：http://tonyqus.sinaapp.com/archives/286 (4)字符串相关函数 HSSFSheet sheet = workbook.createSheet("Test");// 创建工作表(Sheet) HSSFRow row = sheet.createRow(0); row.createCell(0).setCellValue("abcdefg"); row.createCell(1).setCellValue("aa bb cc dd ee fF GG"); row.createCell(3).setCellFormula("UPPER(A1)"); row.createCell(4).setCellFormula("PROPER(B1)");
以上代码中的公式说明： UPPER( String ) ：将文本转换成大写形式。
PROPER( String ) ：将文字串的首字母及任何非字母字符之后的首字母转换成大写。将其余的字母转换成小写。 更多 Excel 的字符串函数可参考：http://tonyqus.sinaapp.com/archives/289 (5)IF函数 HSSFSheet sheet = workbook.createSheet("Test");// 创建工作表(Sheet) HSSFRow row = sheet.createRow(0); row.createCell(0).setCellValue(12); row.createCell(1).setCellValue(23); row.createCell(3).setCellFormula("IF(A1>B1,\"A1大于B1\",\"A1小于等于B1\")");
以上代码中的公式说明： 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时的返回值。 (6)CountIf和SumIf函数 HSSFSheet sheet = workbook.createSheet("Test");// 创建工作表(Sheet) HSSFRow row = sheet.createRow(0); row.createCell(0).setCellValue(57); row.createCell(1).setCellValue(89); row.createCell(2).setCellValue(56); row.createCell(3).setCellValue(67); row.createCell(4).setCellValue(60); row.createCell(5).setCellValue(73); row.createCell(7).setCellFormula("COUNTIF(A1:F1,\">=60\")"); row.createCell(8).setCellFormula("SUMIF(A1:F1,\">=60\",A1:F1)");
以上代码中的公式说明： COUNTIF(range,criteria)：满足某条件的计数的函数。参数range：需要进行读数的计数；参数criteria：条件表达式，只有当满足此条件时才进行计数。 SumIF(criteria_range, criteria,sum_range)：用于统计某区域内满足某条件的值的求和。参数criteria_range：条件测试区域，第二个参数Criteria中的条件将与此区域中的值进行比较；参数criteria：条件测试值，满足条件的对应的sum_range项将进行求和计算；参数sum_range：汇总数据所在区域，求和时会排除掉不满足Criteria条件的对应的项。 (7)Lookup函数 HSSFSheet sheet = workbook.createSheet("Test");// 创建工作表(Sheet) HSSFRow row = sheet.createRow(0); row.createCell(0).setCellValue(0); row.createCell(1).setCellValue(59); row.createCell(2).setCellValue("不及格"); row = sheet.createRow(1); row.createCell(0).setCellValue(60); row.createCell(1).setCellValue(69); row.createCell(2).setCellValue("及格"); row = sheet.createRow(2); row.createCell(0).setCellValue(70); row.createCell(1).setCellValue(79); row.createCell(2).setCellValue("良好"); row = sheet.createRow(3); row.createCell(0).setCellValue(80); row.createCell(1).setCellValue(100); row.createCell(2).setCellValue("优秀"); row = sheet.createRow(4); row.createCell(0).setCellValue(75); row.createCell(1).setCellFormula("LOOKUP(A5,$A$1:$A$4,$C$1:$C$4)"); row.createCell(2).setCellFormula("VLOOKUP(A5,$A$1:$C$4,3,true)");
以上代码中的公式说明： LOOKUP(lookup_value,lookup_vector,result_vector) ，第一个参数：需要查找的内容，本例中指向 A5 单元格，也就是 75 ；第二个参数：比较对象区域，本例中的成绩需要与 $A$1:$A$4 中的各单元格中的值进行比较；第三个参数：查找结果区域，如果匹配到会将此区域中对应的数据返回。如本例中返回$C$1:$C$4 中对应的值。
可能有人会问，字典中没有 75 对应的成绩啊，那么 Excel 中怎么匹配的呢？答案是模糊匹配，并且 LOOKUP 函数只支持模糊匹配。 Excel 会在 $A$1:$A$4 中找小于 75 的最大值，也就是 A3 对应的 70 ，然后将对应的 $C$1:$C$4 区域中的 C3 中的值返回，这就是最终结果“良好”的由来。 VLOOKUP(lookup_value,lookup_area,result_col,is_fuzzy ) ，第一个参数：需要查找的内容，这里是 A5 单元格；第二个参数：需要比较的表，这里是 $A$1:$C$4 ，注意 VLOOKUP 匹配时只与表中的第一列进行匹配。第三个参数：匹配结果对应的列序号。这里要对应的是成绩列，所以为 3 。第四个参数：指明是否模糊匹配。例子中的 TRUE 表示模糊匹配，与上例中一样。匹配到的是第三行。如果将此参数改为 FALSE ，因为在表中的第 1 列中找不到 75 ，所以会报“#N/A ”的计算错误。
另外，还有与 VLOKUP 类似的 HLOOKUP 。不同的是 VLOOKUP 用于在表格或数值数组的首列查找指定的数值，并由此返回表格或数组当前行中指定列处的数值。而HLOOKUP 用于在表格或数值数组的首行查找指定的数值，并由此返回表格或数组当前列中指定行处的数值。读者可以自已去尝试。 (8)随机数函数 HSSFSheet sheet = workbook.createSheet("Test");// 创建工作表(Sheet) HSSFRow row = sheet.createRow(0); row.createCell(0).setCellFormula("RAND()");//取0-1之间的随机数 row.createCell(1).setCellFormula("int(RAND()*100)");//取0-100之间的随机整数 row.createCell(2).setCellFormula("rand()*10+10");//取10-20之间的随机实数 row.createCell(3).setCellFormula("CHAR(INT(RAND()*26)+97)");//随机小写字母 row.createCell(4).setCellFormula("CHAR(INT(RAND()*26)+65)");//随机大写字母 //随机大小写字母 row.createCell(5).setCellFormula("CHAR(INT(RAND()*26)+if(INT(RAND()*2)=0,97,65))");
以上代码中的公式说明： 上面几例中除了用到RAND函数以外，还用到了CHAR函数用来将ASCII码换为字母，INT函数用来取整。值得注意的是INT函数不会四舍五入，无论小数点后是多少都会被舍去。 (9)获得公式的返回值 HSSFSheet sheet = workbook.createSheet("Test");// 创建工作表(Sheet) HSSFRow row = sheet.createRow(0); row.createCell(0).setCellValue(7);//A1 row.createCell(1).setCellValue(8);//B1 HSSFCell cell=row.createCell(2); cell.setCellFormula("A1*B1+14"); HSSFFormulaEvaluator e = newHSSFFormulaEvaluator(workbook); cell = e.evaluateInCell(cell);//若Excel文件不是POI创建的，则不必调用此方法 System.out.println("公式计算结果："+cell.getNumericCellValue());
5.使用图形
(1)画线 HSSFSheet sheet = workbook.createSheet("Test");// 创建工作表(Sheet) HSSFPatriarch patriarch=sheet.createDrawingPatriarch(); HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 0, 0,(short)1, 0,(short)4, 4); HSSFSimpleShape line = patriarch.createSimpleShape(anchor); line.setShapeType(HSSFSimpleShape.OBJECT_TYPE_LINE);//设置图形类型 line.setLineStyle(HSSFShape.LINESTYLE_SOLID);//设置图形样式 line.setLineWidth(6350);//在POI中线的宽度12700表示1pt,所以这里是0.5pt粗的线条。
通常，利用POI画图主要有以下几个步骤： 1. 创建一个Patriarch（注意，一个sheet中通常只创建一个Patriarch对象）； 2. 创建一个Anchor，以确定图形的位置； 3. 调用Patriarch创建图形； 4. 设置图形类型(直线，矩形，圆形等)及样式（颜色，粗细等）。 关于HSSFClientAnchor(dx1,dy1,dx2,dy2,col1,row1,col2,row2)的参数，有必要在这里说明一下： dx1：起始单元格的x偏移量，如例子中的0表示直线起始位置距B1单元格左侧的距离； dy1：起始单元格的y偏移量，如例子中的0表示直线起始位置距B1单元格上侧的距离； dx2：终止单元格的x偏移量，如例子中的0表示直线起始位置距E5单元格左侧的距离； dy2：终止单元格的y偏移量，如例子中的0表示直线起始位置距E5单元格上侧的距离； col1：起始单元格列序号，从0开始计算； row1：起始单元格行序号，从0开始计算，如例子中col1=1,row1=0就表示起始单元格为B1； col2：终止单元格列序号，从0开始计算； row2：终止单元格行序号，从0开始计算，如例子中col2=4,row2=4就表示起始单元格为E5； 最后，关于LineStyle属性，有如下一些可选值，对应的效果分别如图所示： 
(2)画矩形 HSSFSheet sheet = workbook.createSheet("Test");// 创建工作表(Sheet) HSSFPatriarch patriarch=sheet.createDrawingPatriarch(); HSSFClientAnchor anchor = new HSSFClientAnchor(255,122,255, 122, (short)1, 0,(short)4, 3); HSSFSimpleShape rec = patriarch.createSimpleShape(anchor); rec.setShapeType(HSSFSimpleShape.OBJECT_TYPE_RECTANGLE); rec.setLineStyle(HSSFShape.LINESTYLE_DASHGEL);//设置边框样式 rec.setFillColor(255, 0, 0);//设置填充色 rec.setLineWidth(25400);//设置边框宽度 rec.setLineStyleColor(0, 0, 255);//设置边框颜色
(3)画圆形 更改上例的代码如下： rec.setShapeType(HSSFSimpleShape.OBJECT_TYPE_OVAL);//设置图片类型
(4)画Grid 在POI中，本身没有画Grid(网格)的方法。但我们知道Grid其实就是由横线和竖线构成的，所在我们可以通过画线的方式来模拟画Grid。代码如下： HSSFSheet sheet = workbook.createSheet("Test");// 创建工作表(Sheet) HSSFRow row = sheet.createRow(2); row.createCell(1); row.setHeightInPoints(240); sheet.setColumnWidth(2, 9000); int linesCount = 20; HSSFPatriarch patriarch = sheet.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 |
【本文地址】