Apache POI 是用Java编写的免费开源的跨平台的 Java API,Apache POI提供API给Java程式对Microsoft Office格式档案读和写的功能。 

Apache POI 是创建和维护操作各种符合Office Open XML(OOXML)标准和微软的OLE 2复合文档格式(OLE2)的Java API。用它可以使用Java读取和创建,修改MS Excel文件.而且,还可以使用Java读取和创建MS Word和MSPowerPoint文件。Apache POI 提供Java操作Excel解决方案(适用于Excel97-2008)。 

如果处理.xlsx、docx、pptx的话可以试试Docx4j 。 
Docx4j is a Java library for creating and manipulating Microsoft Open XML (Word docx, Powerpoint pptx, and Excel xlsx) files. 

  • 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格式档案的功能。

http://poi.apache.org/ 

Busy Developers' Guide to HSSF and XSSF Features 

版本:poi-3.8-20120326.jar 

1、生成Workbook 

Java代码 

1. //生成Workbook  
2. HSSFWorkbook wb = new HSSFWorkbook();  
3.   
4. //添加Worksheet(不添加sheet时生成的xls文件打开时会报错)  
5. @SuppressWarnings("unused")  
6. Sheet sheet1 = wb.createSheet();  
7. @SuppressWarnings("unused")  
8. Sheet sheet2 = wb.createSheet();  
9. @SuppressWarnings("unused")  
10. Sheet sheet3 = wb.createSheet("new sheet");  
11. @SuppressWarnings("unused")  
12. Sheet sheet4 = wb.createSheet("rensanning");  
13.   
14. //保存为Excel文件  
15. FileOutputStream out = null;  
16.   
17. try {  
18. new FileOutputStream("c:\\text.xls");  
19.     wb.write(out);        
20. } catch (IOException e) {  
21.     System.out.println(e.toString());  
22. } finally {  
23. try {  
24.         out.close();  
25. catch (IOException e) {  
26.         System.out.println(e.toString());  
27.     }  
28. }


2、生成Workbook OOXML形式(.xlsx) 

Java代码  

1. //生成Workbook  
2. XSSFWorkbook wb = new XSSFWorkbook();  
3.   
4. //......


3、打开Workbook 

Java代码  

1. //方法一:使用WorkbookFactory  
2. FileInputStream in = null;  
3. Workbook wb = null;  
4.   
5. try {  
6. new FileInputStream(TEST_WORKBOOK_NAME);  
7.     wb = WorkbookFactory.create(in);  
8. } catch (IOException e) {  
9.     System.out.println(e.toString());  
10. } catch (InvalidFormatException e) {  
11.     System.out.println(e.toString());  
12. } finally {  
13. try {  
14.         in.close();  
15. catch (IOException e) {  
16.         System.out.println(e.toString());  
17.     }  
18. }  
19.   
20. System.out.println("====================Workbook====================");  
21. System.out.println("Number of Sheets:" + wb.getNumberOfSheets());  
22. System.out.println("Sheet3's name:" + wb.getSheetName(3));  
23. System.out.println();  
24.   
25. //方法二:使用POIFSFileSystem  
26. try {  
27. new FileInputStream(TEST_WORKBOOK_NAME);  
28. new POIFSFileSystem(in);  
29. new HSSFWorkbook(fs);  
30. } catch (IOException e) {  
31.     System.out.println(e.toString());  
32. } finally {  
33. try {  
34.         in.close();  
35. catch (IOException e) {  
36.         System.out.println(e.toString());  
37.     }  
38. }  
39.   
40. System.out.println("====================Workbook====================");  
41. System.out.println("Number of Sheets:" + wb.getNumberOfSheets());  
42. System.out.println("Sheet3's name:" + wb.getSheetName(3));  
43. System.out.println();


4、打开加密的Workbook(读加密) 

Java代码  

1. FileInputStream input = new FileInputStream(TEST_WORKBOOK_NAME_ENCRYPTED);  
2. BufferedInputStream binput = new BufferedInputStream(input);  
3. POIFSFileSystem poifs = new POIFSFileSystem(binput);  
4.   
5. Biff8EncryptionKey.setCurrentUserPassword(TEST_WORKBOOK_PASSWORD);  
6.   
7. HSSFWorkbook wb = new HSSFWorkbook(poifs);  
8.   
9. System.out.println("====================EncryptedWorkbook====================");  
10. System.out.println("Number of Sheets:" + wb.getNumberOfSheets());  
11. System.out.println("Sheet0's name:" + wb.getSheetName(0));  
12. System.out.println();


5、追加Sheet 

Java代码 

1. Sheet sheet = wb.createSheet("append sheet");


6、复制Sheet 

Java代码  

1. wb.cloneSheet(1);


7、修改Sheet名称 

Java代码 

1. wb.setSheetName(i, "SheetName new");


8、删除Sheet 

Java代码

1. wb.removeSheetAt(1);


9、设置下部Sheet名的Tab的第一个可见Tab 

Java代码  

1. //设置下部Sheet名的Tab的第一个可见Tab(以左的Sheet看不见)  
2. wb.setFirstVisibleTab(2);


10、调整Sheet顺序 

Java代码  

1. wb.setSheetOrder("SheetName3", 1);  
2. wb.setSheetOrder(wb.getSheetName(4), 0);


11、设置当前Sheet 

t.setActiveSheet();

Java代码  

1. //设置当前Sheet  
2. wb.setActiveSheet(wb.getNumberOfSheets() - 1);  
3. //(Excel的当前Sheet被设置,需要结合setSelected使用,不然下部Sheet名的Tab还是默认为第一个)  
4. //(需要选择多个Sheet的话,每个Sheet调用setSelected(true)即可)  
5. wb.getSheetAt(wb.getNumberOfSheets() - 1).setSelected(true);


12、固定窗口 

Java代码  

1. wb.getSheet("SheetName4").createFreezePane(2, 2);

 


13、分割窗口 Java代码  

1. wb.getSheet("SheetName5").createSplitPane(2000, 2000, 0, 0, HSSFSheet.PANE_LOWER_LEFT);

 


14、Sheet缩放 Java代码  

1. //setZoom(int numerator, int denominator)  
2. //"numerator"÷"denominator"  例如: 3÷1=3 那就是设置为300%  
3.   
4. //扩大(200%)  
5. wb.getSheet("sheetname1").setZoom(2, 1);  
6. //缩小(50%)  
7. wb.getSheet("sheetname2").setZoom(1, 2);

 


15、行列分组 Java代码  

1. wb.getSheet("sheetname3").groupColumn(4, 7);  
2. wb.getSheet("sheetname3").groupColumn(9, 12);  
3. wb.getSheet("sheetname3").groupColumn(10, 11);  
4.   
5. wb.getSheet("sheetname3").groupRow(5, 14);  
6. wb.getSheet("sheetname3").groupRow(7, 13);  
7. wb.getSheet("sheetname3").groupRow(16, 19);

 




16、关闭分组 Java代码  

1. wb.getSheet("sheetname3").setColumnGroupCollapsed(10, true);  
2. wb.getSheet("sheetname3").setRowGroupCollapsed(7, true);


17、插入行 

Java代码  

1. Row row1 = wb.getSheet("sheetname4").createRow(1);  
2. Cell cell1_1 = row1.createCell(1);  
3. cell1_1.setCellValue(123);  
4.   
5. Row row4 = wb.getSheet("sheetname4").createRow(4);  
6. Cell cell4_3 = row4.createCell(3);  
7. cell4_3.setCellValue("中国");


18、删除行 

Java代码  

1. Row row = wb.getSheet("sheetname4").getRow(1);  
2. wb.getSheet("sheetname4").removeRow(row);


19、移动行 

Java代码  

1. //******移动行只移动内容,不牵扯行的删除和插入  
2.   
3. //移动行(把第1行和第2行移到第5行之后)  
4. wb.getSheet("sheetname5").shiftRows(0, 1, 5);  
5.   
6. //移动行(把第3行和第4行往上移动1行)  
7. wb.getSheet("sheetname5").shiftRows(2, 3, -1);


20、修改行高 

Java代码

1. //设置默认行高  
2. wb.getSheet("sheetname6").setDefaultRowHeight((short)100);  
3.   
4. //设置行高  
5. wb.getSheet("sheetname6").getRow(2).setHeight((short)(100 * 20));


21、修改列宽 

Java代码  

1. //设置默认列宽  
2. wb.getSheet("sheetname7").setDefaultColumnWidth(12);  
3.   
4. //设置列宽  
5. wb.getSheet("sheetname7").setColumnWidth(0, 5 * 256);


22、不显示网格线 

Java代码  

1. //不显示网格线  
2. wb.getSheet("sheetname8").setDisplayGridlines(false);

 


23、设置分页 Java代码  

1. //设置第一页:3行2列 (可以多次设置)  
2. wb.getSheet("sheetname9").setRowBreak(2);  
3. wb.getSheet("sheetname9").setColumnBreak(1);

 


24、添加,删除,合并单元格 Java代码  

1. //追加行  
2. for (int i = 0; i < 10; i++) {  
3. "sheetname10").createRow(i);  
4. for (int j = 0; j < 10; j++) {  
5. //添加单元格  
6.         Cell cell = row.createCell(j);  
7. 1);  
8.     }  
9.       
10. //删除单元格  
11. 5));  
12. }  
13.           
14. //合并单元格  
15. //CellRangeAddress(int firstRow, int lastRow, int firstCol, int lastCol)  
16. wb.getSheet("sheetname10").addMergedRegion(new CellRangeAddress(1, 4, 2, 3));

 


25、设置Header,Footer Java代码  

1. //Header  
2. Header header = wb.getSheet("sheetname11").getHeader();  
3. header.setLeft(HSSFHeader.startUnderline() +  
4. "宋体", "Italic") +  
5. "文字文字" +  
6.         HSSFHeader.endUnderline());  
7. header.setCenter(HSSFHeader.fontSize((short)16) +  
8.         HSSFHeader.startDoubleUnderline() +  
9.         HSSFHeader.startBold() +  
10. "汉字汉字" +  
11.         HSSFHeader.endBold() +  
12.         HSSFHeader.endDoubleUnderline());  
13. header.setRight("打印时间:" + HSSFHeader.date() +  " " + HSSFHeader.time());  
14.   
15. //Footer  
16. Footer footer = wb.getSheet("sheetname11").getFooter();  
17. footer.setLeft("Copyright @ rensanning");  
18. footer.setCenter("Page:" + HSSFFooter.page() + " / " + HSSFFooter.numPages());  
19. footer.setRight("File:" + HSSFFooter.file());

 

 


26、设置单元格值 Java代码  

1. //boolean  
2. Cell cell00 = rows[0].createCell(0);  
3. boolean val00 = true;  
4. cell00.setCellValue(val00);  
5.   
6. //Calendar 格式化  
7. CellStyle styleCalendar = wb.createCellStyle();  
8. DataFormat formatCalendar = wb.createDataFormat();  
9. styleCalendar.setDataFormat(formatCalendar.getFormat("yyyy/mm/dd"));  
10. Cell cell11 = rows[1].createCell(0);  
11. Calendar val11 = Calendar.getInstance();  
12. cell11.setCellStyle(styleCalendar);  
13. cell11.setCellValue(val11);  
14.   
15. //Date 格式化  
16. CellStyle styleDate = wb.createCellStyle();  
17. DataFormat formatDate = wb.createDataFormat();  
18. styleDate.setDataFormat(formatDate.getFormat("yyyy/mm/dd hh:mm"));  
19. Cell cell21 = rows[2].createCell(0);  
20. Date val21 = new Date();  
21. cell21.setCellStyle(styleDate);  
22. cell21.setCellValue(val21);  
23.   
24. //double  
25. Cell cell30 = rows[3].createCell(0);  
26. double val30 = 1234.56;  
27. cell30.setCellValue(val30);  
28.   
29. //double 格式化  
30. CellStyle styleDouble = wb.createCellStyle();  
31. DataFormat formatDouble = wb.createDataFormat();  
32. styleDouble.setDataFormat(formatDouble.getFormat("#,##0.00"));  
33. Cell cell31 = rows[3].createCell(1);  
34. double val31 = 1234.56;  
35. cell31.setCellStyle(styleDouble);  
36. cell31.setCellValue(val31);  
37.   
38. //String  
39. Cell cell40 = rows[4].createCell(0);  
40. HSSFRichTextString val40 = new HSSFRichTextString("Test汉字");  
41. cell40.setCellValue(val40);

 


 


27、设置单元格边线 Java代码  

1. wb.getSheet("sheetname2").setColumnWidth(1, 4096);  
2.   
3. Row row1 = wb.getSheet("sheetname2").createRow(1);  
4. row1.setHeightInPoints(70);  
5.   
6. Cell cell1_1 = row1.createCell(1);  
7. cell1_1.setCellValue("Sample");  
8.   
9. CellStyle style = wb.createCellStyle();  
10.   
11. style.setBorderTop(CellStyle.BORDER_DASHED);  
12. style.setBorderBottom(CellStyle.BORDER_DOUBLE);  
13. style.setBorderLeft(CellStyle.BORDER_MEDIUM_DASH_DOT);  
14. style.setBorderRight(CellStyle.BORDER_MEDIUM);  
15.   
16. style.setTopBorderColor(IndexedColors.MAROON.getIndex());  
17. style.setBottomBorderColor(IndexedColors.SKY_BLUE.getIndex());  
18. style.setLeftBorderColor(IndexedColors.ORANGE.getIndex());  
19. style.setRightBorderColor(IndexedColors.BLUE_GREY.getIndex());  
20.   
21. cell1_1.setCellStyle(style);

 


28、设置单元格背景填充 Java代码  

1. wb.getSheet("sheetname3").setColumnWidth(0, 4096);  
2. wb.getSheet("sheetname3").setColumnWidth(1, 4096);  
3. wb.getSheet("sheetname3").setColumnWidth(2, 4096);  
4.   
5. Row row1 = wb.getSheet("sheetname3").createRow(1);  
6. row1.setHeightInPoints(70);  
7.   
8. Cell cell1_0 = row1.createCell(0);  
9. Cell cell1_1 = row1.createCell(1);  
10. Cell cell1_2 = row1.createCell(2);  
11.   
12. cell1_0.setCellValue("THIN_VERT_BANDS");  
13. cell1_1.setCellValue("BIG_SPOTS");  
14. cell1_2.setCellValue("THICK_HORZ_BANDS");  
15.   
16. CellStyle style1 = wb.createCellStyle();  
17. style1.setFillPattern(CellStyle.THIN_VERT_BANDS);  
18. style1.setFillForegroundColor(IndexedColors.WHITE.getIndex());  
19. style1.setFillBackgroundColor(IndexedColors.BLUE.getIndex());  
20.   
21. CellStyle style2 = wb.createCellStyle();  
22. style2.setFillPattern(CellStyle.BIG_SPOTS);  
23. style2.setFillForegroundColor(IndexedColors.RED.getIndex());  
24. style2.setFillBackgroundColor(IndexedColors.WHITE.getIndex());  
25.   
26. CellStyle style3 = wb.createCellStyle();  
27. style3.setFillPattern(CellStyle.THICK_HORZ_BANDS);  
28. style3.setFillForegroundColor(IndexedColors.PINK.getIndex());  
29. style3.setFillBackgroundColor(IndexedColors.BROWN.getIndex());  
30.   
31. cell1_0.setCellStyle(style1);  
32. cell1_1.setCellStyle(style2);  
33. cell1_2.setCellStyle(style3);

 


29、设置单元格注释 Java代码

1. HSSFCreationHelper createHelper =  
2.     (HSSFCreationHelper)wb.getCreationHelper();  
3. Drawing patriarch = wb.getSheet("sheetname4").createDrawingPatriarch();  
4.   
5. //注释  
6. Row row = wb.getSheet("sheetname4").createRow(1);  
7. Cell cell = row.createCell(1);  
8.   
9. HSSFClientAnchor clientAnchor = new HSSFClientAnchor(0, 0, 0, 0,  
10. short) 4, 2, (short) 6, 5);  
11.   
12. Comment comment = patriarch.createCellComment(clientAnchor);  
13. comment.setString(createHelper.createRichTextString("注释注释111"));  
14. comment.setAuthor("rensanning");  
15.   
16. cell.setCellComment(comment);  
17.   
18. //带字体的注释  
19. Row row2 = wb.getSheet("sheetname4").createRow(2);  
20. Cell cell2 = row2.createCell(1);  
21.   
22. Font font = wb.createFont();  
23. font.setFontName("宋体");  
24. font.setFontHeightInPoints((short)10);  
25. font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);  
26. font.setColor(HSSFColor.RED.index);  
27.   
28. Comment comment2 = patriarch.createCellComment(clientAnchor);  
29. HSSFRichTextString text = new HSSFRichTextString("注释注释222");  
30. text.applyFont(font);  
31. comment2.setString(text);  
32. comment2.setAuthor("rensanning");  
33.   
34. cell2.setCellComment(comment2);


30、设置单元格字体(斜体,粗体,下线,取消线,字体,大小,背景色) 

Java代码  

1. Font font = null;  
2. CellStyle style = null;  
3.   
4. //斜体  
5. font = wb.createFont();  
6. font.setItalic(true);  
7. style = wb.createCellStyle();  
8. style.setFont(font);  
9.   
10. wb.getSheet("sheetname5").getRow(1).getCell(1).setCellStyle(style);  
11.   
12. //粗体  
13. font = wb.createFont();  
14. font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);  
15. style = wb.createCellStyle();  
16. style.setFont(font);  
17.   
18. wb.getSheet("sheetname5").getRow(2).getCell(1).setCellStyle(style);  
19.   
20. //字体名  
21. font = wb.createFont();  
22. font.setFontName("Courier New");  
23. style = wb.createCellStyle();  
24. style.setFont(font);  
25.   
26. wb.getSheet("sheetname5").getRow(3).getCell(1).setCellStyle(style);  
27.   
28. //字体大小  
29. font = wb.createFont();  
30. font.setFontHeightInPoints((short)20);  
31. style = wb.createCellStyle();  
32. style.setFont(font);  
33.   
34. wb.getSheet("sheetname5").getRow(4).getCell(1).setCellStyle(style);  
35.   
36. //文字颜色  
37. font = wb.createFont();  
38. font.setColor(HSSFColor.YELLOW.index);  
39. style = wb.createCellStyle();  
40. style.setFont(font);  
41.   
42. wb.getSheet("sheetname5").getRow(5).getCell(1).setCellStyle(style);  
43.           
44. //上标  
45. font = wb.createFont();  
46. font.setTypeOffset(HSSFFont.SS_SUPER);  
47. style = wb.createCellStyle();  
48. style.setFont(font);  
49.   
50. wb.getSheet("sheetname5").getRow(6).getCell(1).setCellStyle(style);  
51.   
52. //下标  
53. font = wb.createFont();  
54. font.setTypeOffset(HSSFFont.SS_SUB);  
55. style = wb.createCellStyle();  
56. style.setFont(font);  
57.   
58. wb.getSheet("sheetname5").getRow(7).getCell(1).setCellStyle(style);  
59.   
60. //删除线  
61. font = wb.createFont();  
62. font.setStrikeout(true);  
63. style = wb.createCellStyle();  
64. style.setFont(font);  
65.   
66. wb.getSheet("sheetname5").getRow(8).getCell(1).setCellStyle(style);  
67.   
68. //下划线  
69. font = wb.createFont();  
70. font.setUnderline(HSSFFont.U_SINGLE);  
71. style = wb.createCellStyle();  
72. style.setFont(font);  
73.   
74. wb.getSheet("sheetname5").getRow(9).getCell(1).setCellStyle(style);  
75.   
76. //背景色  
77. style = wb.createCellStyle();  
78. style.setFillForegroundColor(HSSFColor.SEA_GREEN.index);  
79. style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);  
80.   
81. style.setFont(font);  
82.   
83. wb.getSheet("sheetname5").getRow(10).getCell(1).setCellStyle(style);

 


31、设置超链接 Java代码  

1. HSSFCreationHelper createHelper =  
2.     (HSSFCreationHelper)wb.getCreationHelper();  
3.   
4. CellStyle style = wb.createCellStyle();  
5. Font font = wb.createFont();  
6. font.setUnderline(HSSFFont.U_SINGLE);  
7. font.setColor(HSSFColor.BLUE.index);  
8. style.setFont(font);  
9.           
10. //追加行  
11. Row[] rows = new Row[10];  
12. for (int i = 0; i < 10; i++) {  
13. "sheetname6").createRow(i);  
14. }  
15.   
16. //URL  
17. rows[0].createCell(0).setCellValue("URL Link");  
18.   
19. HSSFHyperlink link1 = createHelper.createHyperlink(HSSFHyperlink.LINK_URL);  
20. link1.setAddress("http://poi.apache.org/");  
21. rows[0].getCell(0).setHyperlink(link1);  
22. rows[0].getCell(0).setCellStyle(style);  
23.   
24. //Mail  
25. rows[1].createCell(0).setCellValue("Email Link");  
26.   
27. HSSFHyperlink link2 = createHelper.createHyperlink(HSSFHyperlink.LINK_EMAIL);  
28. link2.setAddress("mailto:poi@apache.org?subject=Hyperlinks");  
29. rows[1].getCell(0).setHyperlink(link2);  
30. rows[1].getCell(0).setCellStyle(style);  
31.   
32. //File  
33. rows[2].createCell(0).setCellValue("File Link");  
34.   
35. HSSFHyperlink link3 = createHelper.createHyperlink(HSSFHyperlink.LINK_FILE);  
36. link3.setAddress("link.xls");  
37. rows[2].getCell(0).setHyperlink(link3);  
38. rows[2].getCell(0).setCellStyle(style);  
39.   
40. //Workbook内  
41. rows[3].createCell(0).setCellValue("Worksheet Link");  
42.   
43. HSSFHyperlink link4 = createHelper.createHyperlink(HSSFHyperlink.LINK_DOCUMENT);  
44. link4.setAddress("sheetname1!A1");  
45. rows[3].getCell(0).setHyperlink(link4);  
46. rows[3].getCell(0).setCellStyle(style);

 


32、设置单元格横向对齐,纵向对齐 

Java代码  

1. //横向对齐  
2. wb.getSheet("sheetname7").setColumnWidth(2, 3072);  
3.   
4. Row[] row = new Row[7];  
5. Cell[] cell = new Cell[7];  
6.   
7. for (int i = 0 ; i < 7 ; i++){  
8. "sheetname7").createRow(i + 1);  
9. 2);  
10. "Please give me a receipt");  
11. }  
12.   
13. CellStyle style0 = wb.createCellStyle();  
14. style0.setAlignment(CellStyle.ALIGN_GENERAL);  
15. cell[0].setCellStyle(style0);  
16.   
17. CellStyle style1 = wb.createCellStyle();  
18. style1.setAlignment(CellStyle.ALIGN_LEFT);  
19. cell[1].setCellStyle(style1);  
20.   
21. CellStyle style2 = wb.createCellStyle();  
22. style2.setAlignment(CellStyle.ALIGN_CENTER);  
23. cell[2].setCellStyle(style2);  
24.   
25. CellStyle style3 = wb.createCellStyle();  
26. style3.setAlignment(CellStyle.ALIGN_RIGHT);  
27. cell[3].setCellStyle(style3);  
28.   
29. CellStyle style4 = wb.createCellStyle();  
30. style4.setAlignment(CellStyle.ALIGN_FILL);  
31. cell[4].setCellStyle(style4);  
32.   
33. CellStyle style5 = wb.createCellStyle();  
34. style5.setAlignment(CellStyle.ALIGN_JUSTIFY);  
35. cell[5].setCellStyle(style5);  
36.   
37. CellStyle style6 = wb.createCellStyle();  
38. style6.setAlignment(CellStyle.ALIGN_CENTER_SELECTION);  
39. cell[6].setCellStyle(style6);  
40.   
41. //纵向对齐  
42. Row row2 = wb.getSheet("sheetname8").createRow(1);  
43. row2.setHeightInPoints(70);  
44. Cell[] cell2 = new Cell[4];  
45.   
46. for (int i = 0 ; i < 4 ; i++){  
47. 1);  
48. "Please give me a receipt");  
49. }  
50.   
51. CellStyle style02 = wb.createCellStyle();  
52. style02.setVerticalAlignment(CellStyle.VERTICAL_TOP);  
53. cell2[0].setCellStyle(style02);  
54.   
55. CellStyle style12 = wb.createCellStyle();  
56. style12.setVerticalAlignment(CellStyle.VERTICAL_CENTER);  
57. cell2[1].setCellStyle(style12);  
58.   
59. CellStyle style22 = wb.createCellStyle();  
60. style22.setVerticalAlignment(CellStyle.VERTICAL_BOTTOM);  
61. cell2[2].setCellStyle(style22);  
62.   
63. CellStyle style32 = wb.createCellStyle();  
64. style32.setVerticalAlignment(CellStyle.VERTICAL_JUSTIFY);  
65. cell2[3].setCellStyle(style32);

 


33、设置单元格旋转角度 Java代码  

1. Row[] row = new Row[4];  
2. Cell[] cell = new Cell[4];  
3.   
4. for (int i = 0 ; i < 4 ; i++){  
5. "sheetname9").createRow(i + 1);  
6. 2);  
7. "Coffee");  
8. }  
9.   
10. CellStyle style0 = wb.createCellStyle();  
11. style0.setRotation((short)45);  
12. cell[0].setCellStyle(style0);  
13.   
14. CellStyle style1 = wb.createCellStyle();  
15. style1.setRotation((short)0);  
16. cell[1].setCellStyle(style1);  
17.   
18. CellStyle style2 = wb.createCellStyle();  
19. style2.setRotation((short)-45);  
20. cell[2].setCellStyle(style2);  
21.   
22. CellStyle style3 = wb.createCellStyle();  
23. style3.setRotation((short)-90);  
24. cell[3].setCellStyle(style3);

 


34、设置单元格自动折行 Java代码  

1. Row[] row = new Row[2];  
2. Cell[] cell = new Cell[2];  
3.   
4. for (int i = 0 ; i < 2 ; i++){  
5. "sheetname10").createRow(i + 1);  
6. 2);  
7. "Thank you very much.");  
8. }  
9.   
10. CellStyle style0 = wb.createCellStyle();  
11. style0.setWrapText(true);  
12. cell[0].setCellStyle(style0);  
13.   
14. CellStyle style1 = wb.createCellStyle();  
15. style1.setWrapText(false);  
16. cell[1].setCellStyle(style1);

 


35、设置单元格文字缩进 Java代码  

1. Row[] row = new Row[4];  
2. Cell[] cell = new Cell[4];  
3.   
4. for (int i = 0 ; i < 4 ; i++){  
5. "sheetname11").createRow(i + 1);  
6. 2);  
7. "Coffee");  
8. }  
9.   
10. CellStyle style1 = wb.createCellStyle();  
11. style1.setIndention((short)1);  
12. style1.setAlignment(CellStyle.ALIGN_LEFT);  
13. cell[1].setCellStyle(style1);  
14.   
15. CellStyle style2 = wb.createCellStyle();  
16. style2.setIndention((short)2);  
17. style2.setAlignment(CellStyle.ALIGN_LEFT);  
18. cell[2].setCellStyle(style2);  
19.   
20. CellStyle style3 = wb.createCellStyle();  
21. style3.setIndention((short)3);  
22. style3.setAlignment(CellStyle.ALIGN_LEFT);  
23. cell[3].setCellStyle(style3);

 


36、自定义格式 Java代码  

 

1. Row[] rows = new Row[2];  
2. for (int i = 0; i < rows.length; i++) {  
3. "sheetname12").createRow(i + 1);  
4. }  
5. DataFormat format = wb.createDataFormat();  
6.   
7. CellStyle[] styles = new CellStyle[2];  
8. for (int i = 0; i < styles.length; i++) {  
9.     styles[i] = wb.createCellStyle();  
10. }  
11. styles[0].setDataFormat(format.getFormat("0.0"));  
12. styles[1].setDataFormat(format.getFormat("#,##0.000"));  
13.   
14. Cell[] cells = new Cell[2];  
15. for (int i = 0; i < cells.length; i++)  {  
16. 1);  
17. 1111.25);  
18.   
19.     cells[i].setCellStyle(styles[i]);  
20. }


37、设置公式 Java代码 

1. Row row1 = wb.getSheet("sheetname13").createRow(1);  
2. Row row2 = wb.getSheet("sheetname13").createRow(2);  
3.   
4. Cell cell1_1 = row1.createCell(1);  
5. Cell cell1_2 = row1.createCell(2);  
6. Cell cell1_3 = row1.createCell(3);  
7. Cell cell2_3 = row2.createCell(3);  
8.   
9. cell1_1.setCellValue(30);  
10. cell1_2.setCellValue(25);  
11. cell1_3.setCellFormula("B2+C2");  
12. cell2_3.setCellFormula("MOD(B2,C2)");

 


38、画直线,圆圈(椭圆),正方形(长方形),Textbox Java代码  

1. HSSFPatriarch patriarch = ((HSSFSheet)wb.getSheet("sheetname14")).createDrawingPatriarch();  
2.   
3. //直线  
4. HSSFClientAnchor clientAnchor1 = new HSSFClientAnchor(0, 0, 0, 0,  
5. short) 4, 2, (short) 6, 5);  
6. HSSFSimpleShape shape1 = patriarch.createSimpleShape(clientAnchor1);  
7. shape1.setShapeType(HSSFSimpleShape.OBJECT_TYPE_LINE);  
8.   
9. //圆圈(椭圆)  
10. HSSFClientAnchor clientAnchor2 = new HSSFClientAnchor(0, 0, 0, 0,  
11. short) 8, 4, (short) 6, 5);  
12. HSSFSimpleShape shape2 = patriarch.createSimpleShape(clientAnchor2);  
13. shape2.setShapeType(HSSFSimpleShape.OBJECT_TYPE_OVAL);  
14.   
15. //正方形(长方形)  
16. HSSFClientAnchor clientAnchor3 = new HSSFClientAnchor(0, 0, 0, 0,  
17. short) 12, 6, (short) 6, 5);  
18. HSSFSimpleShape shape3 = patriarch.createSimpleShape(clientAnchor3);  
19. shape3.setShapeType(HSSFSimpleShape.OBJECT_TYPE_RECTANGLE);  
20.   
21. //Textbox  
22. HSSFClientAnchor clientAnchor4 = new HSSFClientAnchor(0, 0, 0, 0,  
23. short) 14, 8, (short) 6, 5);  
24. HSSFTextbox textbox = patriarch.createTextbox(clientAnchor4);  
25. textbox.setString(new HSSFRichTextString("This is a test"));


39、插入图片 

Java代码  

1. //需要commons-codec-1.6.jar  
2. FileInputStream jpeg = new FileInputStream("resource/test.jpg");  
3. byte[] bytes = IOUtils.toByteArray(jpeg);  
4. int pictureIndex = wb.addPicture(bytes, HSSFWorkbook.PICTURE_TYPE_JPEG);  
5. jpeg.close();  
6.   
7. HSSFCreationHelper helper = (HSSFCreationHelper) wb.getCreationHelper();  
8.   
9. HSSFPatriarch patriarch = ((HSSFSheet)wb.getSheet("sheetname15")).createDrawingPatriarch();  
10.   
11. HSSFClientAnchor clientAnchor = helper.createClientAnchor();  
12.   
13. clientAnchor.setCol1(3);  
14. clientAnchor.setRow1(2);  
15.   
16. HSSFPicture picture = patriarch.createPicture(clientAnchor, pictureIndex);  
17. picture.resize();

 


40、设置可输入List Java代码  

1. CellRangeAddressList addressList = new CellRangeAddressList(  
2. 0,  
3. 0,  
4. 0,  
5. 0);  
6.   
7. final String[] DATA_LIST = new String[] {  
8. "10",  
9. "20",  
10. "30",  
11. };  
12. DVConstraint dvConstraint =  
13.     DVConstraint.createExplicitListConstraint(DATA_LIST);  
14.   
15. HSSFDataValidation dataValidation = new HSSFDataValidation(addressList, dvConstraint);  
16. dataValidation.setSuppressDropDownArrow(false);  
17.   
18. wb.getSheet("sheetname16").addValidationData(dataValidation);

 


41、设置输入提示信息 Java代码  

1. CellRangeAddressList addressList = new CellRangeAddressList(  
2. 0,  
3. 0,  
4. 0,  
5. 0);  
6.   
7. final String[] DATA_LIST = new String[] {  
8. "10",  
9. "20",  
10. "30",  
11. };  
12. DVConstraint dvConstraint = DVConstraint.createExplicitListConstraint(DATA_LIST);  
13.   
14. HSSFDataValidation dataValidation =  
15. new HSSFDataValidation(addressList, dvConstraint);  
16. dataValidation.setSuppressDropDownArrow(false);  
17. dataValidation.createPromptBox("输入提示", "请从下拉列表中选择!");  
18. dataValidation.setShowPromptBox(true);  
19.   
20. wb.getSheet("sheetname17").addValidationData(dataValidation);