POI 的API大全二

1. HSSFSheet sheet = workbook.createSheet("Test");// 创建工作表(Sheet)
2. HSSFRow row = sheet.createRow(0);
3. HSSFCell cell = row.createCell(0);
4. cell.setCellFormula("2+3*4");//设置公式
5. cell = row.createCell(1);
6. cell.setCellValue(10);
7. cell = row.createCell(2);
8. cell.setCellFormula("A1*B1");//设置公式

1. HSSFSheet sheet = workbook.createSheet("Test");// 创建工作表(Sheet)
2. HSSFRow row = sheet.createRow(0);
3. row.createCell(0).setCellValue(1);
4. row.createCell(1).setCellValue(2);
5. row.createCell(2).setCellValue(3);
6. row.createCell(3).setCellValue(4);
7. row.createCell(4).setCellValue(5);
8. row = sheet.createRow(1);
9. row.createCell(0).setCellFormula("sum(A1,C1)");//等价于"A1+C1"
10. row.createCell(1).setCellFormula("sum(B1:D1)");//等价于"B1+C1+D1"

1. HSSFSheet sheet = workbook.createSheet("Test");// 创建工作表(Sheet)
2. HSSFCellStyle style=workbook.createCellStyle();
3. style.setDataFormat(workbook.createDataFormat().getFormat("yyyy-mm-dd"));
4. HSSFRow row = sheet.createRow(0);
5. Calendar date=Calendar.getInstance();//日历对象
6. HSSFCell cell=row.createCell(0);
7. date.set(2011,2, 7);
8. cell.setCellValue(date.getTime());
9. cell.setCellStyle(style);//第一个单元格开始时间设置完成
10. cell=row.createCell(1);
11. date.set(2014,4, 25);
12. cell.setCellValue(date.getTime());
13. cell.setCellStyle(style);//第一个单元格结束时间设置完成
14. cell=row.createCell(3);
15. cell.setCellFormula("CONCATENATE(DATEDIF(A1,B1,\"y\"),\"年\")");
16. cell=row.createCell(4);
17. cell.setCellFormula("CONCATENATE(DATEDIF(A1,B1,\"m\"),\"月\")");
18. cell=row.createCell(5);
19. 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

1. HSSFSheet sheet = workbook.createSheet("Test");// 创建工作表(Sheet)
2. HSSFRow row = sheet.createRow(0);
3. row.createCell(0).setCellValue("abcdefg");
4. row.createCell(1).setCellValue("aa bb cc dd ee fF GG");
5. row.createCell(3).setCellFormula("UPPER(A1)");
6. row.createCell(4).setCellFormula("PROPER(B1)");

以上代码中的公式说明：

UPPER( String ) ：将文本转换成大写形式。

PROPER( String ) ：将文字串的首字母及任何非字母字符之后的首字母转换成大写。将其余的字母转换成小写。

更多 Excel 的字符串函数可参考：http://tonyqus.sinaapp.com/archives/289

1. HSSFSheet sheet = workbook.createSheet("Test");// 创建工作表(Sheet)
2. HSSFRow row = sheet.createRow(0);
3. row.createCell(0).setCellValue(12);
4. row.createCell(1).setCellValue(23);
5. row.createCell(3).setCellFormula("IF(A1>B1,\"A1大于B1\",\"A1小于等于B1\")");

1. HSSFSheet sheet = workbook.createSheet("Test");// 创建工作表(Sheet)
2. HSSFRow row = sheet.createRow(0);
3. row.createCell(0).setCellValue(57);
4. row.createCell(1).setCellValue(89);
5. row.createCell(2).setCellValue(56);
6. row.createCell(3).setCellValue(67);
7. row.createCell(4).setCellValue(60);
8. row.createCell(5).setCellValue(73);
9. row.createCell(7).setCellFormula("COUNTIF(A1:F1,\">=60\")");
10. row.createCell(8).setCellFormula("SUMIF(A1:F1,\">=60\",A1:F1)");

1. HSSFSheet sheet = workbook.createSheet("Test");// 创建工作表(Sheet)
2. HSSFRow row = sheet.createRow(0);
3. row.createCell(0).setCellValue(0);
4. row.createCell(1).setCellValue(59);
5. row.createCell(2).setCellValue("不及格");
6. row = sheet.createRow(1);
7. row.createCell(0).setCellValue(60);
8. row.createCell(1).setCellValue(69);
9. row.createCell(2).setCellValue("及格");
10. row = sheet.createRow(2);
11. row.createCell(0).setCellValue(70);
12. row.createCell(1).setCellValue(79);
13. row.createCell(2).setCellValue("良好");
14. row = sheet.createRow(3);
15. row.createCell(0).setCellValue(80);
16. row.createCell(1).setCellValue(100);
17. row.createCell(2).setCellValue("优秀");
18. row = sheet.createRow(4);
19. row.createCell(0).setCellValue(75);
20. row.createCell(1).setCellFormula("LOOKUP(A5,$A$1:$A$4,$C$1:$C$4)");
21. 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 用于在表格或数值数组的首行查找指定的数值，并由此返回表格或数组当前列中指定行处的数值。读者可以自已去尝试。

1. HSSFSheet sheet = workbook.createSheet("Test");// 创建工作表(Sheet)
2. HSSFRow row = sheet.createRow(0);
3. row.createCell(0).setCellFormula("RAND()");//取0-1之间的随机数
4. row.createCell(1).setCellFormula("int(RAND()*100)");//取0-100之间的随机整数
5. row.createCell(2).setCellFormula("rand()*10+10");//取10-20之间的随机实数
6. row.createCell(3).setCellFormula("CHAR(INT(RAND()*26)+97)");//随机小写字母
7. row.createCell(4).setCellFormula("CHAR(INT(RAND()*26)+65)");//随机大写字母
8. //随机大小写字母
9. row.createCell(5).setCellFormula("CHAR(INT(RAND()*26)+if(INT(RAND()*2)=0,97,65))");

1. HSSFSheet sheet = workbook.createSheet("Test");// 创建工作表(Sheet)
2. HSSFRow row = sheet.createRow(0);
3. row.createCell(0).setCellValue(7);//A1
4. row.createCell(1).setCellValue(8);//B1
5. HSSFCell cell=row.createCell(2);
6. cell.setCellFormula("A1*B1+14");
7. HSSFFormulaEvaluator e = newHSSFFormulaEvaluator(workbook);
8. cell = e.evaluateInCell(cell);//若Excel文件不是POI创建的，则不必调用此方法
9. System.out.println("公式计算结果："+cell.getNumericCellValue());

5.使用图形

(1)画线

1. HSSFSheet sheet = workbook.createSheet("Test");// 创建工作表(Sheet)
2. HSSFPatriarch patriarch=sheet.createDrawingPatriarch();
3. HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 0, 0,(short)1, 0,(short)4, 4);
4. HSSFSimpleShape line = patriarch.createSimpleShape(anchor);
5. line.setShapeType(HSSFSimpleShape.OBJECT_TYPE_LINE);//设置图形类型
6. line.setLineStyle(HSSFShape.LINESTYLE_SOLID);//设置图形样式
7. 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)画矩形

1. HSSFSheet sheet = workbook.createSheet("Test");// 创建工作表(Sheet)
2. HSSFPatriarch patriarch=sheet.createDrawingPatriarch();
3. HSSFClientAnchor anchor = new HSSFClientAnchor(255,122,255, 122, (short)1, 0,(short)4, 3);
4. HSSFSimpleShape rec = patriarch.createSimpleShape(anchor);
5. rec.setShapeType(HSSFSimpleShape.OBJECT_TYPE_RECTANGLE);
6. rec.setLineStyle(HSSFShape.LINESTYLE_DASHGEL);//设置边框样式
7. rec.setFillColor(255, 0, 0);//设置填充色
8. rec.setLineWidth(25400);//设置边框宽度
9. rec.setLineStyleColor(0, 0, 255);//设置边框颜色

(3)画圆形

更改上例的代码如下：

rec.setShapeType(HSSFSimpleShape.OBJECT_TYPE_OVAL);//设置图片类型

(4)画Grid

在POI中，本身没有画Grid(网格)的方法。但我们知道Grid其实就是由横线和竖线构成的，所在我们可以通过画线的方式来模拟画Grid。代码如下：

1. HSSFSheet sheet = workbook.createSheet("Test");// 创建工作表(Sheet)
2. HSSFRow row = sheet.createRow(2);
3. row.createCell(1);
4. row.setHeightInPoints(240);
5. sheet.setColumnWidth(2, 9000);
6. int linesCount = 20;
7. HSSFPatriarch patriarch = sheet.createDrawingPatriarch();
8. //因为HSSFClientAnchor中dx只能在0-1023之间,dy只能在0-255之间，这里采用比例的方式
9. double xRatio = 1023.0 / (linesCount * 10);
10. double yRatio = 255.0 / (linesCount * 10);
11. // 画竖线
12. int x1 = 0;
13. int y1 = 0;
14. int x2 = 0;
15. int y2 = 200;
16. for (int i = 0; i < linesCount; i++)
17. {
18.  HSSFClientAnchor a2 = new HSSFClientAnchor();
19. a2.setAnchor((short) 2, 2, (int) (x1 * xRatio),
20.  (int) (y1 * yRatio), (short) 2, 2, (int) (x2 * xRatio),
21.  (int) (y2 * yRatio));
22.  HSSFSimpleShape shape2 = patriarch.createSimpleShape(a2);
23. shape2.setShapeType(HSSFSimpleShape.OBJECT_TYPE_LINE);
24. x1 += 10;
25. x2 += 10;
26. }
27. // 画横线
28. x1 = 0;
29. y1 = 0;
30. x2 = 200;
31. y2 = 0;
32. for (int i = 0; i < linesCount; i++)
33. {
34.  HSSFClientAnchor a2 = new HSSFClientAnchor();
35. a2.setAnchor((short) 2, 2, (int) (x1 * xRatio),
36.  (int) (y1 * yRatio), (short) 2, 2, (int) (x2 * xRatio),
37.  (int) (y2 * yRatio));
38.  HSSFSimpleShape shape2 = patriarch.createSimpleShape(a2);
39. shape2.setShapeType(HSSFSimpleShape.OBJECT_TYPE_LINE);
40. y1 += 10;
41. y2 += 10;
42. }

(5)插入图片

1. HSSFSheet sheet = workbook.createSheet("Test");// 创建工作表(Sheet)
2. FileInputStream stream=newFileInputStream("d:\\POI\\Apache.gif");
3. byte[] bytes=new byte[(int)stream.getChannel().size()];
4. stream.read(bytes);//读取图片到二进制数组
5. int pictureIdx = workbook.addPicture(bytes,HSSFWorkbook.PICTURE_TYPE_JPEG);
6. HSSFPatriarch patriarch = sheet.createDrawingPatriarch();
7. HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 0, 0,(short)0, 0, (short)5, 5);
8. HSSFPicture pict = patriarch.createPicture(anchor,pictureIdx);
9. //pict.resize();//自动调节图片大小,图片位置信息可能丢失

(6)从Excel文件提取图片

1. InputStream inp = new FileInputStream(filePath);
2. HSSFWorkbook workbook = new HSSFWorkbook(inp);//读取现有的Excel文件
3. List<HSSFPictureData> pictures = workbook.getAllPictures();
4. for(int i=0;i<pictures.size();i++)
5. {
6.  HSSFPictureData pic=pictures.get(i);
7.  String ext = pic.suggestFileExtension();
8.  if (ext.equals("png"))//判断文件格式
9.  {
10.  FileOutputStream png=newFileOutputStream("d:\\POI\\Apache.png");
11. png.write(pic.getData());
12. png.close();//保存图片
13.  }
14. }

6.Excel表操作

(1)设置默认工作表

1. HSSFWorkbook workbook = new HSSFWorkbook();// 创建Excel文件(Workbook)
2. workbook.createSheet("Test0");// 创建工作表(Sheet)
3. workbook.createSheet("Test1");// 创建工作表(Sheet)
4. workbook.createSheet("Test2");// 创建工作表(Sheet)
5. workbook.createSheet("Test3");// 创建工作表(Sheet)
6. workbook.setActiveSheet(2);//设置默认工作表

(2)重命名工作表

1. HSSFWorkbook workbook = new HSSFWorkbook();// 创建Excel文件(Workbook)
2. workbook.createSheet("Test0");// 创建工作表(Sheet)
3. workbook.createSheet("Test1");// 创建工作表(Sheet)
4. workbook.createSheet("Test2");// 创建工作表(Sheet)
5. workbook.createSheet("Test3");// 创建工作表(Sheet)
6. workbook.setSheetName(2, "1234");//重命名工作表

(3)调整表单显示比例

1. HSSFWorkbook workbook = new HSSFWorkbook();// 创建Excel文件(Workbook)
2. HSSFSheet sheet1= workbook.createSheet("Test0");// 创建工作表(Sheet)
3. HSSFSheet sheet2=workbook.createSheet("Test1");// 创建工作表(Sheet)
4. HSSFSheet sheet3=workbook.createSheet("Test2");// 创建工作表(Sheet)
5. sheet1.setZoom(1,2);//50%显示比例
6. sheet2.setZoom(2,1);//200%显示比例
7. sheet3.setZoom(1,10);//10%显示比例

(4)显示/隐藏网格线

1. HSSFWorkbook workbook = new HSSFWorkbook();// 创建Excel文件(Workbook)
2. HSSFSheet sheet1= workbook.createSheet("Test0");// 创建工作表(Sheet)
3. HSSFSheet sheet2=workbook.createSheet("Test1");// 创建工作表(Sheet)
4. sheet1.setDisplayGridlines(false);//隐藏Excel网格线,默认值为true
5. sheet2.setGridsPrinted(true);//打印时显示网格线,默认值为false

(5)遍历Sheet

1. String filePath = "d:\\users\\lizw\\桌面\\POI\\sample.xls";
2. FileInputStream stream = new FileInputStream(filePath);
3. HSSFWorkbook workbook = new HSSFWorkbook(stream);//读取现有的Excel
4. HSSFSheet sheet= workbook.getSheet("Test0");//得到指定名称的Sheet
5. for (Row row : sheet)
6. {
7.  for (Cell cell : row)
8.  {
9.  System.out.print(cell + "\t");
10.  }
11.  System.out.println();
12. }

7.Excel行列操作

(1)组合行、列

1. HSSFSheet sheet= workbook.createSheet("Test0");// 创建工作表(Sheet)
2. sheet.groupRow(1, 3);//组合行
3. sheet.groupRow(2, 4);//组合行
4. sheet.groupColumn(2, 7);//组合列

这里简单的介绍一下什么叫做组合：组合分为行组合和列组合，所谓行组合，就是让n行组合成一个集合，能够进行展开和合拢操作。

使用POI也可以取消组合，例如：sheet.ungroupColumn(1, 3);//取消列组合

(2)锁定列

在Excel中，有时可能会出现列数太多或是行数太多的情况，这时可以通过锁定列来冻结部分列，不随滚动条滑动，方便查看。

1. HSSFSheet sheet= workbook.createSheet("Test0");// 创建工作表(Sheet)
2. sheet.createFreezePane(2, 3, 15, 25);//冻结行列

下面对CreateFreezePane的参数作一下说明：

第一个参数表示要冻结的列数；

第二个参数表示要冻结的行数，这里只冻结列所以为0；

第三个参数表示右边区域可见的首列序号，从1开始计算；

第四个参数表示下边区域可见的首行序号，也是从1开始计算，这里是冻结列，所以为0；

(3)上下移动行

1. FileInputStream stream = new FileInputStream(filePath);
2. HSSFWorkbook workbook = new HSSFWorkbook(stream);
3. HSSFSheet sheet = workbook.getSheet("Test0");
4. sheet.shiftRows(2, 4, 2);//把第3行到第4行向下移动两行

HSSFSheet.shiftRows(startRow, endRow, n)参数说明

startRow：需要移动的起始行；

endRow：需要移动的结束行；

n：移动的位置，正数表示向下移动，负数表示向上移动；