POI操作常用方法

1、POI簡(jiǎn)介Apache POI是Apache軟件基金會(huì)的開放源碼函式庫(kù),POI提供API給Java程序?qū)icrosoft Office格式檔案讀和寫的功能.二、HSSF概況HSSF是Horrible SpreadSheet Format 的縮寫,通過HSSF,你可以用純 Java代碼來 讀取、寫入、修改 Excel文件.HSSF為讀取操作提供了兩類API： usermodel和eventusermodel ,即用戶模型和事件-用戶模型.POI EXCEL文檔結(jié)構(gòu)類HSSFWorkbook excel 文檔對(duì)象HSSFSheet excel 的 sheet HSSFRow excel 的行HS

2、SFCell excel 的單元格 HSSFFont excel 字體HSSFName 名稱 HSSFDataFormat 日期格式HSSFHeader sheet 頭HSSFFooter sheet 尾HSSFCellStyle cell 樣式HSSFDateUtil 日期HSSFPrintSetup 打印HSSFErrorConstants 錯(cuò)誤信息表四、EXCEL常用操作方法1、 得到Excel常用對(duì)象Java代碼1. POIFSFileSystem fs=newPOIFSFileSystem( new FileInputStream( "d:/test.xls");

3、2. 得到Excel工作簿對(duì)象3. HSSFWorkbook wb = new HSSFWorkbook(fs);4. 得到Excel工作表對(duì)象5. HSSFSheet sheet = wb.getSheetAt( 0);6. 得到Excel工作表的行7. HSSFRow row = sheet.getRow(i);8. /得到Excel工作表指定行的單元格9. HSSFCell cell = row.getCell( short ) j);10. cellStyle = cell.getCellStyle(); 得到單元格樣式11. POIFSFileSystem fs=newPOIFSFi

4、leSystem( new FileInputStream( "d:/test.xls");12. 得到Excel工作簿對(duì)象13. HSSFWorkbook wb = new HSSFWorkbook(fs);14. 得到Excel工作表對(duì)象15. HSSFSheet sheet = wb.getSheetAt( 0);16. 得到Excel工作表的行17. HSSFRow row = sheet.getRow(i);18. /得到Excel工作表指定行的單元格19. HSSFCell cell = row.getCell( short ) j);20. cellStyle

5、 = cell.getCellStyle(); 得到單元格樣式2、建立Excel常用對(duì)象Java代碼1. HSSFWorkbook wb = new HSSFWorkbook();/創(chuàng)立 Excel 工作簿對(duì)象2. HSSFSheet sheet = wb.createSheet( "new sheet");/ 創(chuàng)立 Excel工作表對(duì)象3. HSSFRow row = sheet.createRow( short )0); /創(chuàng)立 Excel 工作表的行4. cellStyle = wb.createCellStyle(); / 創(chuàng)立單元格樣式5. row.createC

6、ell( short )0).setCellStyle(cellStyle); 創(chuàng)立 Excel 工作表指定行的單元格6. row.createCell( short )0).setCellValue(1); / 設(shè)置 Excel 工作表的值7. HSSFWorkbook wb = new HSSFWorkbook();/創(chuàng)立 Excel 工作簿對(duì)象8. HSSFSheet sheet = wb.createSheet( "new sheet");/ 創(chuàng)立 Excel工作表對(duì)象9. HSSFRow row = sheet.createRow( short )0); /創(chuàng)立

7、Excel 工作表的行10. cellStyle = wb.createCellStyle(); / 創(chuàng)立單元格樣式11. row.createCell( short )0).setCellStyle(cellStyle); 創(chuàng)立 Excel 工作表指定行的單元格12. row.createCell( short )0).setCellValue( 1); / 設(shè)置 Excel 工作表的值3、設(shè)置sheet名稱和單元格內(nèi)容Java代碼1. wb.setSheetName( 1,"第一張工作表",HSSFCell.ENCODING_UTF_16);2. cell.setEnc

8、oding( short) 1);3. cell.setCellValue("單元格內(nèi)容");4. wb.setSheetName( 1,"第一張工作表",HSSFCell.ENCODING_UTF_16);5. cell.setEncoding( short) 1);6. cell.setCellValue("單元格內(nèi)容");4、取得sheet的數(shù)目Java代碼1. wb.getNumberOfSheets()2. wb.getNumberOfSheets()5、 根據(jù)index取得sheet對(duì)象Java代碼1. HSSFSheet

9、 sheet = wb.getSheetAt( 0);2. HSSFSheet sheet = wb.getSheetAt( 0);6、取得有效的行數(shù)Java代碼1. int rowcount = sheet.getLastRowNum();2. int rowcount = sheet.getLastRowNum();7、取得一行的有效單元格個(gè)數(shù)Java代碼1. row.getLastCellNum();2. row.getLastCellNum();8、單元格值類型讀寫Java代碼1. cell.setCellType(HSSFCell.CELL_TYPE_STRING)/設(shè)置單元格為 S

10、TRING類型2. cell.getNumericCellValue(); 讀取為數(shù)值類型的單元格內(nèi)容3. cell.setCellType(HSSFCell.CELL_TYPE_STRING)/設(shè)置單元格為 STRING類型4. cell.getNumericCellValue(); 讀取為數(shù)值類型的單元格內(nèi)容9、設(shè)置列寬、行高Java代碼1. sheet.setColumnWidth( short)column,(short)width);2. row.setHeight( short )height);3. sheet.setColumnWidth( short)column,(shor

11、t)width);4. row.setHeight( short )height);10、添加區(qū)域,合并單元格Java代碼1. Region region = new Region(short )rowFrom,( short )columnFrom,( short )rowTo2. ,(short )columnTo); / 合并從第 rowFrom 行 columnFrom 歹U3. sheet.addMergedRegion(region); / 至U rowTo 行 columnTo 的區(qū)域4. 得到所有區(qū)域5. sheet.getNumMergedRegions()6. Region

12、 region = new Region(short )rowFrom,( short )columnFrom,( short )rowTo7. ,(short )columnTo); / 合并從第 rowFrom 行 columnFrom 歹U8. sheet.addMergedRegion(region); / 至U rowTo 行 columnTo 的區(qū)域9. 得到所有區(qū)域10. sheet.getNumMergedRegions()11、保存 Excel文件Java代碼1. FileOutputStream fileOut = new FileOutputStream(path);2.

13、 wb.write(fileOut);3. FileOutputStream fileOut = new FileOutputStream(path);4. wb.write(fileOut);12、根據(jù)單元格不同屬性返回字符串?dāng)?shù)值Java代碼1. public2.3.4.5.6.7.8.9.10.11.12.13.14.15.16.17.18.19.20.21.22.23.24.25.String getCellStringValue(HSSFCell cell) String cellValue =""switch (cell.getCellType() case HS

14、SFCell.CELL_TYPE_STRING字符串類型cellValue = cell.getStringCellValue();if(cellValue.trim().equals( "")|cellValue.trim().length()<= 0) cellValue=""break ;case HSSFCell.CELL_TYPE_NUMERIC/ 數(shù)值類型cellValue = String.valueOf(cell.getNumericCellValue();break ;case HSSFCell.CELL_TYPE_FORMULA

15、/ 公式cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);cellValue = String.valueOf(cell.getNumericCellValue(); break ;case HSSFCell.CELL_TYPE_BLANK:cellValue=""break ;case HSSFCell.CELL_TYPE_BOOLEAN:break ;case HSSFCell.CELL_TYPE_ERROR:break ;default :break ;26. return cellValue;27. )28. public S

16、tring getCellStringValue(HSSFCell cell) 29. String cellValue =""30. switch (cell.getCellType() 31. case HSSFCell.CELL_TYPE_STRING字符串類型32. cellValue = cell.getStringCellValue();33. if(cellValue.trim().equals( "")|cellValue.trim().length()<= 0)34. cellValue=""35. break

17、;36. case HSSFCell.CELL_TYPE_NUMERIC/ 數(shù)值類型37. cellValue=String.valueOf(cell.getNumericCellValue();38. break;39. case HSSFCell.CELL_TYPE_FORMULA/ 公式40. cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);41. cellValue=String.valueOf(cell.getNumericCellValue();42. break;43. caseHSSFCell.CELL_TYPE_BLANK:44. c

18、ellValue=""45. break;46. caseHSSFCell.CELL_TYPE_BOOLEAN:47. break;48. caseHSSFCell.CELL_TYPE_ERROR:49. break;50. default :51. break;52. )53. return cellValue;54. 55. 常用單元格邊框格式Java代碼1. HSSFCellStyle style = wb.createCellStyle();2. style.setBorderBottom(HSSFCellStyle.BORDER_DOTTED); / 下邊框3.

19、style.setBorderLeft(HSSFCellStyle.BORDER_DOTTED) 左邊框4. style.setBorderRight(HSSFCellStyle.BORDER_THIN); 右邊框5. style.setBorderTop(HSSFCellStyle.BORDER_THIN);/ 上邊框6. HSSFCellStyle style = wb.createCellStyle();7. style.setBorderBottom(HSSFCellStyle.BORDER_DOTTED); / 下邊框8. style.setBorderLeft(HSSFCellSt

20、yle.BORDER_DOTTED) 左邊框9. style.setBorderRight(HSSFCellStyle.BORDER_THIN); 右邊框10. style.setBorderTop(HSSFCellStyle.BORDER_THIN);/ 上邊框14、設(shè)置字體和內(nèi)容位置Java代碼1. HSSFFont f = wb.createFont();2. f.setFontHeightInPoints( short) 11);/字號(hào)3. f.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL); 力口粗4. style.setFont(f);5. st

21、yle.setAlignment(HSSFCellStyle.ALIGN_CENTER); 左右居中6. style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);/上下居中7. style.setRotation( short rotation); 單元格內(nèi)容的旋轉(zhuǎn)的角度8. HSSFDataFormat df = wb.createDataFormat();9. style1.setDataFormat(df.getFormat( "0.00%); 設(shè)置單元格數(shù)據(jù)格式10. cell.setCellFormula(stri

22、ng); /給單元格設(shè)公式11. style.setRotation( short rotation); 單元格內(nèi)容的旋轉(zhuǎn)的角度12. HSSFFont f = wb.createFont();13. f.setFontHeightInPoints( short) 11);/字號(hào)14. f.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL); 力口粗15. style.setFont(f);16. style.setAlignment(HSSFCellStyle.ALIGN_CENTER); 左右居中17. style.setVerticalAlignment(

23、HSSFCellStyle.VERTICAL_CENTER); 上下居中18. style.setRotation( short rotation); 單元格內(nèi)容的旋轉(zhuǎn)的角度19. HSSFDataFormat df = wb.createDataFormat();20. style1.setDataFormat(df.getFormat( "0.00%); 設(shè)置單元格數(shù)據(jù)格式21. cell.setCellFormula(string); /給單元格設(shè)公式22. style.setRotation( short rotation); 單元格內(nèi)容的旋轉(zhuǎn)的角度15、插入圖片Java代碼

24、1. 先把讀進(jìn)來的圖片放到一個(gè)ByteArrayOutputStream 中,以便產(chǎn)生 ByteArray2. ByteArrayOutputStreambyteArrayOut = new ByteArrayOutputStream();3. BufferedImage bufferImg = ImageIO.read( new File("ok.jpg");4. ImageIO.write(bufferImg, "jpg" ,byteArrayOut);5. /讀進(jìn)一個(gè)excel模版6. FileInputStream fos = new FileI

25、nputStream(filePathName+ "/stencil.xlt");7. fs = new POIFSFileSystem(fos);8. 創(chuàng)立一個(gè)工作薄9. HSSFWorkbook wb = new HSSFWorkbook(fs);10. HSSFSheet sheet = wb.getSheetAt( 0);11. HSSFPatriarch patriarch = sheet.createDrawingPatriarch();12. HSSFClientAnchor anchor = new HSSFClientAnchor( 0,0,1023,25

26、5,(short) 0,0,(short )10,10)；13. patriarch.createPicture(anchor , wb.addPicture(byteArrayOut.toByteArray(),HSSFWorkbook.PICTU RE_TYPE_JPEG);14. 先把讀進(jìn)來的圖片放到一個(gè)ByteArrayOutputStream 中,以便產(chǎn)生 ByteArray15. ByteArrayOutputStreambyteArrayOut = new ByteArrayOutputStream();16. BufferedImage bufferImg = ImageIO.

27、read( new File("ok.jpg");17. ImageIO.write(bufferImg, "jpg" ,byteArrayOut);18. 讀進(jìn)一個(gè)excel模版19. FileInputStream fos = new FileInputStream(filePathName+ "/stencil.xlt");20. fs = new POIFSFileSystem(fos);21. 創(chuàng)立一個(gè)工作薄22. HSSFWorkbook wb = new HSSFWorkbook(fs);23. HSSFSheet sh

28、eet = wb.getSheetAt( 0);24. HSSFPatriarch patriarch = sheet.createDrawingPatriarch();25. HSSFClientAnchor anchor = new HSSFClientAnchor( 0,0,1023,255,(short) 0,0,(short )10,10);26. patriarch.createPicture(anchor , wb.addPicture(byteArrayOut.toByteArray(),HSSFWorkbook.PICTU RE_TYPE_JPEG);16、調(diào)整工作表位置Ja

29、va代碼1. HSSFWorkbook wb = new HSSFWorkbook();2. HSSFSheet sheet = wb.createSheet( "format sheet");3. HSSFPrintSetup ps = sheet.getPrintSetup();4. sheet.setAutobreaks( true );5. ps.setFitHeight( short )1);6. ps.setFitWidth( short )1);7. HSSFWorkbook wb = new HSSFWorkbook();8. HSSFSheet sheet

30、 = wb.createSheet( "format sheet");9. HSSFPrintSetup ps = sheet.getPrintSetup();10. sheet.setAutobreaks( true );11. ps.setFitHeight( short )1);12. ps.setFitWidth( short )1);II17、設(shè)置打印區(qū)域Java代碼1. HSSFSheet sheet = wb.createSheet( "Sheet1");2. wb.setPrintArea( 0, "$A$1:$C$2"

31、;);3. HSSFSheet sheet = wb.createSheet( "Sheetl");4. wb.setPrintArea( 0, "$A$1:$C$2");18、標(biāo)注腳注Java代碼1. HSSFSheet sheet = wb.createSheet( "format sheet");2. HSSFFooter footer = sheet.getFooter()3. footer.setRight( "Page " + HSSFFooter.page() + " of " +

32、 HSSFFooter.numPages();4. HSSFSheet sheet = wb.createSheet( "format sheet");5. HSSFFooter footer = sheet.getFooter()6. footer.setRight( "Page " + HSSFFooter.page() + " of " + HSSFFooter.numPages();19、在工作單中清空行數(shù)據(jù),調(diào)整行位置Java代碼1. HSSFWorkbook wb = new HSSFWorkbook();2. HSSFS

33、heet sheet = wb.createSheet( "row sheet");3. / Create various cells and rows for spreadsheet.4. / Shift rows 6 - 11 on the spreadsheet to the top (rows 0 - 5)5. sheet.shiftRows( 5, 10, - 5);6. HSSFWorkbook wb = new HSSFWorkbook();7. HSSFSheet sheet = wb.createSheet( "row sheet");

34、8. / Create various cells and rows for spreadsheet.9. / Shift rows 6 - 11 on the spreadsheet to the top (rows 0 - 5)10. sheet.shiftRows( 5, 10, - 5);20、選中指定的工作表Java代碼1. HSSFSheet sheet = wb.createSheet( "row sheet");2. heet.setSelected( true );3. HSSFSheet sheet = wb.createSheet( "row

35、 sheet");4. heet.setSelected( true );21、工作表的放大縮小Java代碼1. HSSFSheet sheet1 = wb.createSheet( "new sheet");2. sheet1.setZoom( 1,2);/ 50 percent magnification3. HSSFSheet sheet1 = wb.createSheet( "new sheet");4. sheet1.setZoom( 1,2);/ 50 percent magnification22、頭注和腳注Java代碼1. HS

36、SFSheet sheet = wb.createSheet( "new sheet");2. HSSFHeader header = sheet.getHeader();3. header.setCenter( "Center Header");4. header.setLeft( "Left Header");5. header.setRight(HSSFHeader.font( "Stencil-Normal" , "Italic") +6. HSSFHeader.fontSize( sh

37、ort) 16) + "Right w/ Stencil-Normal Italic font and size 16");7. HSSFSheet sheet = wb.createSheet( "new sheet");8. HSSFHeader header = sheet.getHeader();9. header.setCenter( "Center Header");10. header.setLeft( "Left Header");11. header.setRight(HSSFHeader.fon

38、t( "Stencil-Normal" , "Italic") +12. HSSFHeader.fontSize( short) 16) + "Right w/ Stencil-Normal Italic font and size 16");23、自定義顏色Java代碼1. HSSFCellStyle style = wb.createCellStyle();2. style.setFillForegroundC010r(HSSFColor.LIME.index);3. style.setFillPattern(HSSFCellSt

39、yle.SOLID_FOREGROUND);4. HSSFFont font = wb.createFont();5. font.setC010r(HSSFColor.RED.index);6. style.setFont(font);7. cell.setCellStyle(style);8. HSSFCellStyle style = wb.createCellStyle();9. style.setFillForegroundC010r(HSSFColor.LIME.index);10. style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUN

40、D);11. HSSFFont font = wb.createFont();12. font.setColor(HSSFColor.RED.index);13. style.setFont(font);14. cell.setCellStyle(style);24、填充和顏色設(shè)置Java代碼1. HSSFCellStyle style = wb.createCellStyle();2. style.setFillBackgroundC010r(HSSFColor.AQUA.index);3. style.setFillPattern(HSSFCellStyle.BIG_SPOTS);4. H

41、SSFCell cell = row.createCell( short) 1);5. cell.setCellValue("X");6. style = wb.createCellStyle();7. style.setFillForegroundColor(HSSFColor.ORANGE.index);8. style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);9. cell.setCellStyle(style);10. HSSFCellStyle style = wb.createCellStyle();11.

42、style.setFillBackgroundColor(HSSFColor.AQUA.index);12. style.setFillPattern(HSSFCellStyle.BIG_SPOTS);13. HSSFCell cell = row.createCell( short) 1);14. cell.setCellValue("X");15. style = wb.createCellStyle();16. style.setFillForegroundColor(HSSFColor.ORANGE.index);17. style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);18. cell.setCellStyl