POI操作Excel文件
POI操作Word文件
作者:李志伟
时间:2014-05-28
Apache POI是Apache软件基金会的开源项目,POI提供API给Java程序对Microsoft Office格式档案读和写的功能。 .NET的开发人员则可以利用NPOI (POI for .NET) 来存取 Microsoft Office文档的功能。
包名称 | 说明 |
HSSF | 提供读写Microsoft Excel XLS格式档案的功能。 |
XSSF | 提供读写Microsoft Excel OOXML XLSX格式档案的功能。 |
HWPF | 提供读写Microsoft Word DOC格式档案的功能。 |
HSLF | 提供读写Microsoft PowerPoint格式档案的功能。 |
HDGF | 提供读Microsoft Visio格式档案的功能。 |
HPBF | 提供读Microsoft Publisher格式档案的功能。 |
HSMF | 提供读Microsoft Outlook格式档案的功能。 |
类名 | 说明 |
HSSFWorkbook | Excel的文档对象 |
HSSFSheet | Excel的表单 |
HSSFRow | Excel的行 |
HSSFCell | Excel的格子单元 |
HSSFFont | Excel字体 |
HSSFDataFormat | 格子单元的日期格式 |
HSSFHeader | Excel文档Sheet的页眉 |
HSSFFooter | Excel文档Sheet的页脚 |
HSSFCellStyle | 格子单元样式 |
HSSFDateUtil | 日期 |
HSSFPrintSetup | 打印 |
HSSFErrorConstants | 错误信息表 |
public class Test00
{
public static void main(String[] args) throws IOException
{
String filePath="d:\\users\\lizw\\桌面\\POI\\sample.xls";//文件路径
HSSFWorkbook workbook = new HSSFWorkbook();//创建Excel文件(Workbook)
HSSFSheet sheet = workbook.createSheet();//创建工作表(Sheet)
sheet = workbook.createSheet("Test");//创建工作表(Sheet)
FileOutputStream out = new FileOutputStream(filePath);
workbook.write(out);//保存Excel文件
out.close();//关闭文件流
System.out.println("OK!");
}
}
HSSFSheet sheet = workbook.createSheet("Test");// 创建工作表(Sheet)
HSSFRow row = sheet.createRow(0);// 创建行,从0开始
HSSFCell cell = row.createCell(0);// 创建行的单元格,也是从0开始
cell.setCellValue("李志伟");// 设置单元格内容
row.createCell(1).setCellValue(false);// 设置单元格内容,重载
row.createCell(2).setCellValue(new Date());// 设置单元格内容,重载
row.createCell(3).setCellValue(12.345);// 设置单元格内容,重载
workbook.createInformationProperties();//创建文档信息
DocumentSummaryInformation dsi= workbook.getDocumentSummaryInformation();//摘要信息
dsi.setCategory("类别:Excel文件");//类别
dsi.setManager("管理者:李志伟");//管理者
dsi.setCompany("公司:--");//公司
SummaryInformation si = workbook.getSummaryInformation();//摘要信息
si.setSubject("主题:--");//主题
si.setTitle("标题:测试文档");//标题
si.setAuthor("作者:李志伟");//作者
si.setComments("备注:POI测试文档");//备注
HSSFSheet sheet = workbook.createSheet("Test");// 创建工作表(Sheet)
HSSFPatriarch patr = sheet.createDrawingPatriarch();
HSSFClientAnchor anchor = patr.createAnchor(0, 0, 0, 0, 5, 1, 8, 3);//创建批注位置
HSSFComment comment = patr.createCellComment(anchor);//创建批注
comment.setString(new HSSFRichTextString("这是一个批注段落!"));//设置批注内容
comment.setAuthor("李志伟");//设置批注作者
comment.setVisible(true);//设置批注默认显示
HSSFCell cell = sheet.createRow(2).createCell(1);
cell.setCellValue("测试");
cell.setCellComment(comment);//把批注赋值给单元格
创建批注位置HSSFPatriarch.createAnchor(dx1, dy1, dx2, dy2, col1, row1, col2, row2)方法参数说明:
dx1 第1个单元格中x轴的偏移量
dy1 第1个单元格中y轴的偏移量
dx2 第2个单元格中x轴的偏移量
dy2 第2个单元格中y轴的偏移量
col1 第1个单元格的列号
row1 第1个单元格的行号
col2 第2个单元格的列号
row2 第2个单元格的行号
HSSFSheet sheet = workbook.createSheet("Test");// 创建工作表(Sheet)
HSSFHeader header =sheet.getHeader();//得到页眉
header.setLeft("页眉左边");
header.setRight("页眉右边");
header.setCenter("页眉中间");
HSSFFooter footer =sheet.getFooter();//得到页脚
footer.setLeft("页脚左边");
footer.setRight("页脚右边");
footer.setCenter("页脚中间");
也可以使用Office自带的标签定义,你可以通过HSSFHeader或HSSFFooter访问到它们,都是静态属性,列表如下:
HSSFHeader.tab &A 表名
HSSFHeader.file &F 文件名
HSSFHeader.startBold &B 粗体开始
HSSFHeader.endBold &B 粗体结束
HSSFHeader.startUnderline &U 下划线开始
HSSFHeader.endUnderline &U 下划线结束
HSSFHeader.startDoubleUnderline &E 双下划线开始
HSSFHeader.endDoubleUnderline &E 双下划线结束
HSSFHeader.time &T 时间
HSSFHeader.date &D 日期
HSSFHeader.numPages &N 总页面数
HSSFHeader.page &P 当前页号
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静态方法更加直接一些。
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不存在直接关系。
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;
边框和其他单元格设置一样也是调用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 |
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
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 |
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方法设置默认的列宽或行高。
判断单元格是否为日期类型,使用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
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");//设置公式
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"
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
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
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时的返回值。
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条件的对应的项。
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用于在表格或数值数组的首行查找指定的数值,并由此返回表格或数组当前列中指定行处的数值。读者可以自已去尝试。
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函数不会四舍五入,无论小数点后是多少都会被舍去。
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 = new HSSFFormulaEvaluator(workbook);
cell = e.evaluateInCell(cell);//若Excel文件不是POI创建的,则不必调用此方法
System.out.println("公式计算结果:"+cell.getNumericCellValue());
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属性,有如下一些可选值,对应的效果分别如图所示:
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);//设置边框颜色
更改上例的代码如下:
rec.setShapeType(HSSFSimpleShape.OBJECT_TYPE_OVAL);//设置图片类型
在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 < 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.setShapeType(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.setShapeType(HSSFSimpleShape.OBJECT_TYPE_LINE);
y1 += 10;
y2 += 10;
}
HSSFSheet sheet = workbook.createSheet("Test");// 创建工作表(Sheet)
FileInputStream stream=new FileInputStream("d:\\POI\\Apache.gif");
byte[] bytes=new byte[(int)stream.getChannel().size()];
stream.read(bytes);//读取图片到二进制数组
int pictureIdx = workbook.addPicture(bytes, HSSFWorkbook.PICTURE_TYPE_JPEG);
HSSFPatriarch patriarch = sheet.createDrawingPatriarch();
HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 0, 0, (short)0, 0, (short)5, 5);
HSSFPicture pict = patriarch.createPicture(anchor, pictureIdx);
//pict.resize();//自动调节图片大小,图片位置信息可能丢失
InputStream inp = new FileInputStream(filePath);
HSSFWorkbook workbook = new HSSFWorkbook(inp);//读取现有的Excel文件
List<HSSFPictureData> pictures = workbook.getAllPictures();
for(int i=0;i<pictures.size();i++)
{
HSSFPictureData pic=pictures.get(i);
String ext = pic.suggestFileExtension();
if (ext.equals("png"))//判断文件格式
{
FileOutputStream png=new FileOutputStream("d:\\POI\\Apache.png");
png.write(pic.getData());
png.close();//保存图片
}
}
HSSFWorkbook workbook = new HSSFWorkbook();// 创建Excel文件(Workbook)
workbook.createSheet("Test0");// 创建工作表(Sheet)
workbook.createSheet("Test1");// 创建工作表(Sheet)
workbook.createSheet("Test2");// 创建工作表(Sheet)
workbook.createSheet("Test3");// 创建工作表(Sheet)
workbook.setActiveSheet(2);//设置默认工作表
HSSFWorkbook workbook = new HSSFWorkbook();// 创建Excel文件(Workbook)
workbook.createSheet("Test0");// 创建工作表(Sheet)
workbook.createSheet("Test1");// 创建工作表(Sheet)
workbook.createSheet("Test2");// 创建工作表(Sheet)
workbook.createSheet("Test3");// 创建工作表(Sheet)
workbook.setSheetName(2, "1234");//重命名工作表
HSSFWorkbook workbook = new HSSFWorkbook();// 创建Excel文件(Workbook)
HSSFSheet sheet1= workbook.createSheet("Test0");// 创建工作表(Sheet)
HSSFSheet sheet2=workbook.createSheet("Test1");// 创建工作表(Sheet)
HSSFSheet sheet3=workbook.createSheet("Test2");// 创建工作表(Sheet)
sheet1.setZoom(1,2);//50%显示比例
sheet2.setZoom(2,1);//200%显示比例
sheet3.setZoom(1,10);//10%显示比例
HSSFWorkbook workbook = new HSSFWorkbook();// 创建Excel文件(Workbook)
HSSFSheet sheet1= workbook.createSheet("Test0");// 创建工作表(Sheet)
HSSFSheet sheet2=workbook.createSheet("Test1");// 创建工作表(Sheet)
sheet1.setDisplayGridlines(false);//隐藏Excel网格线,默认值为true
sheet2.setGridsPrinted(true);//打印时显示网格线,默认值为false
String filePath = "d:\\users\\lizw\\桌面\\POI\\sample.xls";
FileInputStream stream = new FileInputStream(filePath);
HSSFWorkbook workbook = new HSSFWorkbook(stream);//读取现有的Excel
HSSFSheet sheet= workbook.getSheet("Test0");//得到指定名称的Sheet
for (Row row : sheet)
{
for (Cell cell : row)
{
System.out.print(cell + "\t");
}
System.out.println();
}
HSSFSheet sheet= workbook.createSheet("Test0");// 创建工作表(Sheet)
sheet.groupRow(1, 3);//组合行
sheet.groupRow(2, 4);//组合行
sheet.groupColumn(2, 7);//组合列
这里简单的介绍一下什么叫做组合:组合分为行组合和列组合,所谓行组合,就是让n行组合成一个集合,能够进行展开和合拢操作。
使用POI也可以取消组合,例如:sheet.ungroupColumn(1, 3);//取消列组合
在Excel中,有时可能会出现列数太多或是行数太多的情况,这时可以通过锁定列来冻结部分列,不随滚动条滑动,方便查看。
HSSFSheet sheet= workbook.createSheet("Test0");// 创建工作表(Sheet)
sheet.createFreezePane(2, 3, 15, 25);//冻结行列
下面对CreateFreezePane的参数作一下说明:
第一个参数表示要冻结的列数;
第二个参数表示要冻结的行数,这里只冻结列所以为0;
第三个参数表示右边区域可见的首列序号,从1开始计算;
第四个参数表示下边区域可见的首行序号,也是从1开始计算,这里是冻结列,所以为0;
FileInputStream stream = new FileInputStream(filePath);
HSSFWorkbook workbook = new HSSFWorkbook(stream);
HSSFSheet sheet = workbook.getSheet("Test0");
sheet.shiftRows(2, 4, 2);//把第3行到第4行向下移动两行
HSSFSheet.shiftRows(startRow, endRow, n)参数说明
startRow:需要移动的起始行;
endRow:需要移动的结束行;
n:移动的位置,正数表示向下移动,负数表示向上移动;
HSSFSheet sheet= workbook.createSheet("Test0");// 创建工作表(Sheet)
HSSFRow row=sheet.createRow(1);
HSSFCell cell=row.createCell(1);
cell.setCellValue("已锁定");
HSSFCellStyle locked = workbook.createCellStyle();
locked.setLocked(true);//设置锁定
cell.setCellStyle(locked);
cell=row.createCell(2);
cell.setCellValue("未锁定");
HSSFCellStyle unlocked = workbook.createCellStyle();
unlocked.setLocked(false);//设置不锁定
cell.setCellStyle(unlocked);
sheet.protectSheet("password");//设置保护密码
HSSFSheet sheet= workbook.createSheet("Test0");// 创建工作表(Sheet)
HSSFRow row=sheet.createRow(0);
HSSFCell cell=row.createCell(0);
cell.setCellValue("日期列");
CellRangeAddressList regions = new CellRangeAddressList(1, 65535, 0, 0);//选定一个区域
DVConstraint constraint = DVConstraint.createDateConstraint(
DVConstraint.OperatorType.BETWEEN, "1993-01-01", "2014-12-31", "yyyy-MM-dd");
HSSFDataValidation dataValidate = new HSSFDataValidation(regions, constraint);
dataValidate.createErrorBox("错误", "你必须输入一个时间!");
sheet.addValidationData(dataValidate);
CellRangeAddressList类表示一个区域,构造函数中的四个参数分别表示起始行序号,终止行序号,起始列序号,终止列序号。65535是一个Sheet的最大行数。
另外,CreateDateConstraint的第一个参数除了设置成DVConstraint.OperatorType.BETWEEN外,还可以设置成如下一些值,大家可以自己一个个去试看看效果:
验证的数据类型也有几种选择,如下:
CellRangeAddressList regions = new CellRangeAddressList(0, 65535, 0, 0);
DVConstraint constraint = DVConstraint.createExplicitListConstraint(
new String[] { "C++", "Java", "C#" });
HSSFDataValidation dataValidate = new HSSFDataValidation(regions, constraint);
sheet.addValidationData(dataValidate);
HSSFSheet sheet= workbook.createSheet("Test0");// 创建工作表(Sheet)
HSSFPrintSetup print = sheet.getPrintSetup();//得到打印对象
print.setLandscape(false);//true,则表示页面方向为横向;否则为纵向
print.setScale((short)80);//缩放比例80%(设置为0-100之间的值)
print.setFitWidth((short)2);//设置页宽
print.setFitHeight((short)4);//设置页高
print.setPaperSize(HSSFPrintSetup.A4_PAPERSIZE);//纸张设置
print.setUsePage(true);//设置打印起始页码不使用"自动"
print.setPageStart((short)6);//设置打印起始页码
sheet.setPrintGridlines(true);//设置打印网格线
print.setNoColor(true);//值为true时,表示单色打印
print.setDraft(true);//值为true时,表示用草稿品质打印
print.setLeftToRight(true);//true表示“先行后列”;false表示“先列后行”
print.setNotes(true);//设置打印批注
sheet.setAutobreaks(false);//Sheet页自适应页面大小
更详细的打印设置请参考:http://tonyqus.sinaapp.com/archives/271
HSSFSheet sheet = workbook.createSheet("Test0");
CreationHelper createHelper = workbook.getCreationHelper();
// 关联到网站
Hyperlink link = createHelper.createHyperlink(Hyperlink.LINK_URL);
link.setAddress("http://poi.apache.org/");
sheet.createRow(0).createCell(0).setHyperlink(link);
// 关联到当前目录的文件
link = createHelper.createHyperlink(Hyperlink.LINK_FILE);
link.setAddress("sample.xls");
sheet.createRow(0).createCell(1).setHyperlink(link);
// e-mail 关联
link = createHelper.createHyperlink(Hyperlink.LINK_EMAIL);
link.setAddress("mailto:poi@apache.org?subject=Hyperlinks");
sheet.createRow(0).createCell(2).setHyperlink(link);
//关联到工作簿中的位置
link = createHelper.createHyperlink(Hyperlink.LINK_DOCUMENT);
link.setAddress("'Test0'!C3");//Sheet名为Test0的C3位置
sheet.createRow(0).createCell(3).setHyperlink(link);
POI读写Excel功能强大、操作简单。但是POI操作时,一般只用它读取word文档,POI只能能够创建简单的word文档,相对而言POI操作时的功能太少。
XWPFDocument doc = new XWPFDocument();// 创建Word文件
XWPFParagraph p = doc.createParagraph();// 新建一个段落
p.setAlignment(ParagraphAlignment.CENTER);// 设置段落的对齐方式
p.setBorderBottom(Borders.DOUBLE);//设置下边框
p.setBorderTop(Borders.DOUBLE);//设置上边框
p.setBorderRight(Borders.DOUBLE);//设置右边框
p.setBorderLeft(Borders.DOUBLE);//设置左边框
XWPFRun r = p.createRun();//创建段落文本
r.setText("POI创建的Word段落文本");
r.setBold(true);//设置为粗体
r.setColor("FF0000");//设置颜色
p = doc.createParagraph();// 新建一个段落
r = p.createRun();
r.setText("POI读写Excel功能强大、操作简单。");
XWPFTable table= doc.createTable(3, 3);//创建一个表格
table.getRow(0).getCell(0).setText("表格1");
table.getRow(1).getCell(1).setText("表格2");
table.getRow(2).getCell(2).setText("表格3");
FileOutputStream out = new FileOutputStream("d:\\POI\\sample.doc");
doc.write(out);
out.close();
FileInputStream stream = new FileInputStream("d:\\POI\\sample.doc");
XWPFDocument doc = new XWPFDocument(stream);// 创建Word文件
for(XWPFParagraph p : doc.getParagraphs())//遍历段落
{
System.out.print(p.getParagraphText());
}
for(XWPFTable table : doc.getTables())//遍历表格
{
for(XWPFTableRow row : table.getRows())
{
for(XWPFTableCell cell : row.getTableCells())
{
System.out.print(cell.getText());
}
}
}