《POI操作常用方法.doc》由会员分享，可在线阅读，更多相关《POI操作常用方法.doc（14页珍藏版）》请在三一文库上搜索。
1、一、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
2、 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:
3、/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. POIFSFileS
4、ystem 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.
5、 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
6、(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. cells
7、tyle = 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
8、$ 元格容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代玛
9、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(HSSF
10、Cell.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 =
11、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);
12、/ 合并从第 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);1
13、2、根据单元格不字符串数値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()|cellValue.trim().length()_ignm2.(HSSFCe=s_y_eAL_G
14、NCENTER)w EmslI-&6 sw_ese_vemca_A_fmes(HSSFCe_sty_e_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
15、=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(bufferlm
16、gvNjpgN,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. HS
17、SFCIientAnchor 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 ByteArra
18、yOutputStreamO;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.
19、 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、调整
20、工作表位置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.createS
21、heet(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.s
22、etPrintArea(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()
23、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-
24、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 =
25、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.createSheetCnew sheet);4. sheet1.s
26、etZoom(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)
27、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(s
28、hort) 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.create
29、CellStyleO;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.cr
30、eateCell(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.c
31、reateCellStyle();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.Formu
32、laEcaluator 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();ir.getLastCellNum();i+)11.c=r.getCell(i);12.lf(c.getCellType()=Cell.CELL_TYPEJO
33、RMULA)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 HSSFFormula
34、Evaluator(HSSFWorkbook) wb);23.else if(wb instanceof XSSFWorkbook)24.evaknew XSSFFormulaEvaluator(XSSFWorkbook) wb);25.for(int i=r.getFirstCellNum();ir.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文件的所有公式,用起来很方便。