POI操作常用方法.doc

一、POI简介Apache POI是Apache软件基金会的开赦源码函直库，POI S R API给Java程序对 Microsoft Office格式档案读和写的功能。二、HSSFIM 况HSSF 是 Horrible Spreadsheet Format 的缩写，通 il HSSF,你 nJ 以用纯 Java 代码来 读取、写入、修改Excel文件oHSSF为读取樣作提供了两类API： usermodel和eventusermodel, 即“用户模里"和“事件用户模型"。三、POI EXCEL文甘构造类HSSFWorkbook excel 文档对象HSSFSheet excel 的 sheet HSSFRow excel 的 fiHSSFCell excel 的单元格 HSSFFont excel 字 itHSSFName g f? HSSFDataFormat 日期格式HSSFHeader sheet 头HSSFFooter sheet 尾HSSFCellStyle cell 样 3HSSFDateUtil 日期HSSFPrintSetup 打印HSSFErrorConstants 錯误信息表四、EXCEL常用操作方法1、R3 Excel常用对象Java代玛1. POIFSFileSystem fs=newPOIFSFileSystem(new RlelnputStreamCd:/test.xls");2. /福到Excel I作涌对象3. HSSFWorkbook wb = new HSSFWorkbook(fs);4. 福到Excel I ft表对象5. HSSFSheet sheet = wb.getSheetAt(O);6. /福到Excel I ft表的行7. HSSFRow row = sheet.getRow(i);8. /用到Excel I ft表描定仃的单元陷9. HSSFCell cell = row.getCell($hort) j);10. cellstyle = cell.getCellStyleO;/ 到甲元格佯衣11. POIFSFileSystem fs=newPOIFSFileSystem(new FilelnputStreamCd:/test.xlsrt);12. /到Excel工作涌对象13. HSSFWorkbook wb = new HSSFWorkbook(fs);14. /到Excel工作表对象15. HSSFSheet sheet = wb.getSheetAt(O);16. /3 Excel in表的行17. HSSFRow row = sheet.getRow(i);18. / 3 Excel I ft表描定存的甲元洛19. HSSFCell cell = row.getCell(short) j);20. cellstyle = cell.getCellStyleO;/ 列甲元恪佯武2、建立Excel常用对象Java代首奇1. HSSFWorkbook wb = new HSSFWorkbook();创立 Excel 工作籀对浆2. HSSFSheet sheet = wb.createSheet("new sheet*);/立 Excel 工作表对象3. HSSFRow row = sheet.createRow($hort)0); /&l 立 Excel 工作表的斤4. cellstyle = wb.createCellStyle();/6| 立单元格样 K5. row.createCell(short)0).setCellStyle(cellStyle); /fi| 立 Excel 工作表指定行的单元幡6. row.createCell(short)0).setCellValue(1); /设罟 Excel 工作表的7. HSSFWorkbook wb = new HSSFWorkbook();创立 Excel 工作韓对象8. HSSFSheet sheet = wb.createSheet("new sheet");/立 Excel 工作表对象9. HSSFRow row = sheet.createRow(short)0); /&l 立 Excel 工作表的行10. cellstyle = wb.createCellStyle();/fl 立单元格徉式11. row.createCell(short)0).setCellStyle(cellStyle);砺立 Excel 工作表指定行的单元輻12. row.createCell(short)0).setCellValue(1); /i5 n Excel 工作表的慎3、设置sheet S 和单元格容Java代円1. wb.setSheetName(1/$ 一工作表：HSSFCell. ENCODING_UTF_16);2. cell.setEncoding(short) 1);3. cell.setCellValueC$ 元格容J;4. wb.setSheetNamed,期一工作表；HSSFCell.ENCODING_UTF_16);5. cell.setEncoding(short) 1);6. cell.setCellValueC$ 元陷容”)；4、取得sheet的数目Java代罔1. wb.getNumberOfSheetsO2. Wb.getNumberOfSheetsO5、根据index取得sheet对象Java代筍奇1. HSSFSheet sheet = wb.getSheetAt(O);2. HSSFSheet sheet = wb.getSheetAt(O);6、取得有效的行数Java代玛1. int rowcount = sheet.getLastRowNum();2. int rowcount = sheet.getLastRowNum();7、取得一行的有效单元松个数Java代関1. row.getLastCellNum();2. row.getLastCellNum();8、单元格値类里旗写Java代西1. cell.setCellType(HSSFCell.CELL_TYPE_STRING); 设冒单元格为 STRING 类塑2. cell.getNumericCellValue();/jt 取为数值类塑的单元 IS 容3. cell.setCellType(HSSFCell.CELL_TYPE_STRING); i殳冒单元格为 STRING 类塑4. cell.getNumericCellValue();/it取为数值类塑的单元格容9、设置列宽、行高Java代玛1.2.3.4.sheet.setColumnWidth($hort)columnf($hort)width); row.setHeight(short)height);sheet.setColumnWidth($hort)column?(short)width);row.setHeight($hort)height);10、海JB区域，合并单元格Java代码1. Region region = new Region(short)rowFrom,(short)columnFrom,(short)rowTo2. f($hort)columnTo);/合并 U 第 rowFrom fj columnFrom 列3. sheet.addMergedRegion(region);/ 到 rowTo fj columnTo 的区域4. /福到师有区撤5. sheet.getNumMergedRegions()6. Region region = new Region(short)rowFrom,(short)columnFrom,(short)rowTo7. f(short)columnTo);/ 合并从第 rowFrom columnFrom 列8. sheet.addMergedRegion(region);/ 到 rowTo fj columnTo 的区 M9. 福到除有区域10. sheet.getNumMergedRegionsO11、保存Excel文件Java代码白1 FileOutputStream fileOut = new FileOutputStream(path);2. wb.write(fileOut);3. FileOutputStream fileOut = new FileOutputStream(path);4. wb.write(fileOut);12、根据单元格不字符串数値Java代玛1. public String getCellStringValue(HSSFCell cell) 2. String cellValue =:switch (cell.getCellTypeO) case HSSFCell.CELL_TYPE_STRING/字符串艾里 cellValue = cell.getStringCellValueO;if(cellValue.trim().equals(-* )|cellValue.trim().length()<=0) cellValue=,:break:case HSSFCell.CELL_TYPE_NUMERIC: 数値娄里 cellValue = String.valueOf(cell.getNumericCellValueO); break:case HSSFCell.CELL_TYPE_FORMULA: 公艮 cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC); cellValue = String.valueOf(cell.getNumericCellValueO); break:case cellValuebreak:case break;case HSSFCell.CELL_TYPE_ERROR:break:default:break:return cellValue;public String getCellStringValue(HSSFCell cell) String cellValue =:switch (cell.getCellTypeO) case HSSFCell.CELL_TYPE_STRING/字符串艾里 cellValue = cell.getStringCellValueO;if(cellValue.trirn().equals(a>)|cellValue.trim().length()<-O) cellValue=,i:break:case HSSFCell.CELL_TYPE_NUMERIC: /数何棊里 cellValue = String.valueOf(cell.getNumericCellValueO); break:case HSSFCell.CELL_TYPE_FORMULA: 公 Jt cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC); cellValue = String.valueOf(cell.getNumericCellValueO); break:case cellValue：break:3.4.5.6.7.&9.10.11.12.13.14.15.16.17.18.19.20.21.22.23.24.25.26.27.2&29.30.31.32.33.34.35.36.37.3&39.40.41.42.43.44.45.46.case HSSFCell.CELL_TYPE_BOOLEAN:break-case HSSFCe=CELLTYPlmERROKbreak- defaultbreak-一return ce-_va-un13,西丑-B冃ss&tst&KJava注血為1 HSSFCe一一sty_e sty一e n wb.crea一eCe一一s_y一e(r2 sw_ese-BOderB2.o3HSSFCe一一SMe.BORDERIDOTTED 辽/T &fs 3 s_y_ese-BOs.erLef=HSSFCe=sty_eBORDERDOTTED)y/ 4 stypseooorderRigsHSSFCe一一SWPBORDERITH 一 N)wmia IS 5 s_y_ese-BOa.erTOP(HSSFCe_sty_eBORDERTH_N)y= 0 禹 6 HSSFCe一一s_y_e style n wb.creaiece一一s_y一e(r 7 一一s_y一eB0RDERID0TTEs5 & 禹 8 Rtjn 9 s_y一B0RDERTH3wmG 禹 o s_y一ese-BOs.erTOP(HSSFCe=sty一eBORDERTH_N)w - 0 禹 一严注M§乜li吉蜩侍碉1 HSSFFOaf u wbbrea 一 eFonp2 fse-FOsHefunposs(shom 二 YJ-也 43 fse650一dwef=HSSFFOnLBOLDWE_GHTNORMAL)w 8 曲4 s_y_ese_FOs35 s_y_ese>_ignm2.(HSSFCe=s_y_eAL_GNCENTER)w EmslI-&6 sw_ese_vemca_A_fmes(HSSFCe_sty_e<ERT_CALCENTER)y、r Tsn-e 7 s_y一ese-Raa5-n(shortota5-n)w-B耳声璃3希強3湖忍 8 HSSFDaEFOrmai df H wb.creaieDaErFormMK9 sty_els&Das-Forma_(df 昱 Fomlat(ooo%)w 津nsT 冲25洛辭京it10 ce_seoe_Form 三 a(smng)ys4Bm3$注步24二.s_y_ese_R£a5-n(shortotaa-n) W-BTHSM3鬲芽s丑河12 HSSFFon二=wb.crea一eFon=E13 fs2.FOsHef=nposs(short)二 YJ-占 m云 fsetBO_dweEh_(HSSFFO2.BOLDWE_GHTNORMAL)w 8 ffi15 stypss-Fonts16 s_y_ese>_ignmea(HSSFCe=sw_eAL_GNCENTER)WR 6Sn-B7 s_y_ese_vertica_A_fm2.(HSSFCe_sty_eVERT_CALCENTER)w.t Tsn-B 18 s_y 一es2.ROs5-n(short rotation?-®c=tlHS刖 2 帯苏sm 丽 19 HSSFDasFormai a n wb.crea 一 eDasFormao20 sty_£se6as,FormaafgaFormau000%)w 津nsTmss洛 IgssIt21 ce=seoe=Formula(s=f)；/s-B冲35津步！t22. style.setRotation(short rotation);/$ 元矗容的臨转的角 H15、捕入图片Java代码白1. /先出读i!来的图片赦到一个ByteArrayOutputStream中，以便产生ByteArray2. ByteArrayOutputStream byteArrayOut = new ByteArrayOutputStreamO;3. Bufferedlmage bufferlmg = lmagelO.read(new FileCok.jpg*);4. lmagelO.write(bufferlmgvNjpgN,byteArrayOut);5. /读泌一个excel模版6. Fileinputstream fos = new FilelnputStream(filePathName+7stencil.xir);7. fs = new POIFSFileSystem(fos);8. /剑立一个工作询9. HSSFWorkbook wb = new HSSFWorkbook(fs);10. HSSFSheet sheet = wb.getSheetAt(O);11. HSSFPatriarch patriarch = sheet.createDrawingPatriarchO;12. HSSFCIientAnchor anchor = new HSSFCIientAnchor(0,0,1023,255,(short) 0,0,(shoit)10,10);13. patriarch.createPicture(anchor, wb.addPicture(byteArrayOut.toByteArray(),HSSFWorkbook.PICTURE_TYP EJPEG);14. /先出读i!来的图片或到一个ByteArrayOutputStream中，以便产生ByteArray15. ByteArrayOutputStream byteArrayOut = new ByteArrayOutputStreamO;16. Bufferedlmage bufferlmg = lmagelO.read(new File(,ok.jpg,);17. lmagelO.write(bufferlmg/jpg",byteArrayOut);/读进一个excel模版19. Fileinputstream fos = new FilelnputStream(filePathName+7stencil.xlC);20. fs = new POIFSFileSystem(fos);21. /a立一个工作询22. HSSFWorkbook wb = new HSSFWorkbook(fs);23. HSSFSheet sheet = wb.getSheetAt(O);24. HSSFPatriarch patriarch = sheet.createDrawingPatriarchO;25. HSSFCIientAnchor anchor = new HSSFCIientAnchor(0,0,1023,255,(short) 0,0,(short)10,10);26. patriarch.createPicture(anchor, wb.addPicture(byteArrayOut.toByteArray(),HSSFWorkbook.PICTURE_TYP EJPEG);16、调整工作表位置Java代玛1. HSSFWorkbook wb = new HSSFWorkbookO;2. HSSFSheet sheet = wb.createSheet("format sheet1);3. HSSFPrintSetup ps = sheet.getPrintSetup();4. sheet.setAutobreaks(true);5. ps.setFitHeight(short)1)；6. ps.setFitWidth(short)1);7. HSSFWorkbook wb = new HSSFWorkbookO;8. HSSFSheet sheet = wb.createSheet(l,format sheet1);9. HSSFPrintSetup ps = sheet.getPrintSetup();10. sheet.setAutobreaks(true);11. ps.setFitHeight(short)1);12. ps.setFitWidth(short)1)；17、设置打印区域Java代玛奇1. HSSFSheet sheet = wb.createSheetCSheetl");2. wb.setPrintArea(O, "$A$1 :$C$2“)；3. HSSFSheet sheet = wb.createSheet('Sheetr);4. wb.setPrintArea(O, ,$A$1:$CS2,);18、ON8Java代晋1. HSSFSheet sheet = wb.createSheet(l,format sheet*);2. HSSfTooter footer = sheet.getFooter()3. footer.setRight( Tage" + HSSFFooter.pageO +" of “ + HSSFFooter.numPagesO );4. HSSFSheet sheet = wb.createSheet("format sheet1):5. HSSFFooter footer = sheet.getFooter()6. footer.setRight( Tage" + HSSFFooter.page() +" of" + HSSFFooter.numPagesO );T9i SI作单中清空行数据，调整行位置Java代码白1. HSSFWorkbook wb = new HSSFWorkbookO;2. HSSFSheet sheet = wb.createSheetCrov 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 HSSFWorkbookO;7. HSSFSheet sheet = wb.createSheet("row sheet");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.createSheetCrow sheet");4. heet.setSelected(true);21、工作表的赦大编不Java代晋1 HSSFSheet sheetl = wb.createSheet(Mnew sheet");2. sheetl .setZoom(1,2); / 50 percent magnification3. HSSFSheet sheetl = wb.createSheetC'new sheet');4. sheet1.setZoom(1,2); / 50 percent magnification22、头注和H注Java代码白1 HSSFSheet sheet = wb.createSheet(*new sheet*);2. HSSFHeader header = sheet.getHeader();3. header.setCenterfCenter Header")；4. header.setLeftCLeft Header1');5. header.setRight(HSSFHeader.font('Stencil-Normar, "Italic") +6. HSSFHeader.fontSize(short) 16) + 'Right w/ Stencil-Normal Italic font and size 16”);7. HSSFSheet sheet = wb.createSheet(-new sheet1);8. HSSFHeader header = sheet.getHeader();9. header.setCenterfCenter Header);10. header.setLeft(MLeft Header1);11. header.setRight(HSSFHeader.font('Stencil-Normal "Italic") +12. HSSFHeader.fontSize(short) 16) + 'Right w/ Stencil-Normal Italic font and size 16");23、自定义顾色Java代首1. HSSFCellStyle style = wb.createCellStyleO;2.3. style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);4. HSSFFont font = wb.createFont();5.6. style.setFont(font);7. cell.setCellStyle(style);8. HSSFCellStyle style = wb.createCellStyleO;9.10. style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);11. HSSFFont font = wb.createFont();12.13. style.setFont(font);14. cell.setCellStyle(style);24、填充和顾色设置Java代码白1. HSSFCellStyle style = wb.createCellStyleO;2.3. style.setFillPattern(HSSFCellStyle.BIG_SPOTS);4. HSSFCell cell = row.createCell(short) 1);5. cell.setCellValueCX*);6. style = wb.createCellStyle();7.8. style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);9. cell.setCellStyle(style);10. HSSFCellStyle style = wb.createCellStyleO;11.12.13. HSSFCell cell = row.createCell(short) 1);14. cell.setCellValue(X);15. style = wb.createCellStyle();16.17. style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);18. cell.setCellStyle(style);25、强行刷新单元格公式Java代首白1HSSFFormulaEvaluator eval=new HSSFFormulaEvaluator(HSSFWorkbook) wb);2private static void updateFormula(Workbook wb,Sheet sjnt row)3.Row r=s.getRow(row);4.Cell c二null;5.FormulaEcaluator eval=null;6if(wb instanceof HSSFWorkbook)7.evaknew HSSFFormulaEvaluator(HSSFWorkbook) wb);&else if(wb instanceof XSSFWorkbook)9.eval=new XSSFFormulaEvaluator(XSSFWorkbook) wb);10.for(int i=r.getFirstCellNum();i<r.getLastCellNum();i+)11.c=r.getCell(i);12.lf(c.getCellType()=Cell.CELL_TYPEJORMULA)13.eval.evaluateFormulaCell(c);14.I15.16.HSSFFormulaEvaluator eval=new HSSFFormulaEvaluator(HSSFWorkbook) wb);17.private static void updateFormula(Workbook wb,Sheet sjnt row)Row r=s.getRow(row);19.Cell c=null;20.FormulaEcaluator evaknull;21.if(wb instanceof HSSFWorkbook)22.eval=new HSSFFormulaEvaluator(HSSFWorkbook) wb);23.else if(wb instanceof XSSFWorkbook)24.evaknew XSSFFormulaEvaluator(XSSFWorkbook) wb);25.for(int i=r.getFirstCellNum();i<r.getLastCellNum();i+)26.c=r.getCell(i);27.lf(c.getCellType()=Cell.CELL_TYPE_FORMULA)28.eval.evaluateFormulaCell(c);29.30.说明:FormulaEvaluator 提供了 evaluateFormulaCell(Cell cell)方法,it 算公式保存结果，但不改 变公衣。而evaluatelnCell(Cell cell)方法是计算公沆,并将原公氏替換为it算结果,也就是说 该单元格的类塑不在是 Cell.CELL_TYPE_FORMULA 而是 Cell.CELL_TYPE_NUMBERIC。 HSSFFormulaEvaluator 提供了静态方法 evaluateAIIFormulaCells(HSSFWorkbook wb)，廿算一 f Excel文件的所有公式,用起来很方便。