POI操作Excel常用方法總結(jié)及對(duì)excel的讀寫(xiě)舉例

1、目錄1. 一 POI簡(jiǎn)介2. 二 HSSF概況 3. 三 POI EXCEL文檔結(jié)構(gòu)類(lèi)4. 四 EXCEL常用操作方法 1. 取得sheet的數(shù)目 c-sharp view plaincopyprint wbgetNumberOfSheets wbgetNumberOfSheets 2. 取得一行的有效單元格個(gè)數(shù) c-sharp view plaincopyprint rowgetLastCellNum rowgetLastCellNum 一、 POI簡(jiǎn)介Apache POI是Apache軟件基金會(huì)的開(kāi)放源碼函式庫(kù)，POI提供API給Java程序?qū)icrosoft Office格式檔案讀和寫(xiě)

2、的功能。二、 HSSF概況 HSSF 是Horrible SpreadSheet Format的縮寫(xiě)，通過(guò)HSSF，你可以用純Java代碼來(lái)讀取、寫(xiě)入、修改Excel文件。HSSF 為讀取操作提供了兩類(lèi)API：usermodel和eventusermodel，即“用戶(hù)模型”和“事件-用戶(hù)模型”。三、 POI EXCEL文檔結(jié)構(gòu)類(lèi)HSSFWorkbook excel文檔對(duì)象 HSSFSheet excel的sheet HSSFRow excel的行 HSSFCell excel的單元格 HSSFFont excel字體 HSSFName 名稱(chēng) HSSFDataFormat 日期格式 HSSFHe

3、ader sheet頭 HSSFFooter sheet尾 HSSFCellStyle cell樣式 HSSFDateUtil 日期 HSSFPrintSetup 打印 HSSFErrorConstants 錯(cuò)誤信息表四、 EXCEL常用操作方法 1、 得到Excel常用對(duì)象 c-sharp view plaincopyprint?1. POIFSFileSystem fs=newPOIFSFileSystem(new FileInputStream(d:/test.xls); 2. /得到Excel工作簿對(duì)象 3. HSSFWorkbook wb = new HSSFWorkbook(fs)

4、; 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();/得到單元格樣式 2、建立Excel常用對(duì)象 c-sharp view plaincopyprint?HSSFWorkbook wb = new HSSFWorkbook();/創(chuàng)建

5、Excel工作簿對(duì)象 HSSFSheet sheet = wb.createSheet(new sheet);/創(chuàng)建Excel工作表對(duì)象 HSSFRow row = sheet.createRow(short)0); /創(chuàng)建Excel工作表的行 cellStyle = wb.createCellStyle();/創(chuàng)建單元格樣式 row.createCell(short)0).setCellStyle(cellStyle); /創(chuàng)建Excel工作表指定行的單元格 row.createCell(short)0).setCellValue(1); /設(shè)置Excel工作表的值 3、設(shè)置sheet名稱(chēng)和

6、單元格內(nèi)容 c-sharp view plaincopyprint?wb.setSheetName(1, 第一張工作表,HSSFCell.ENCODING_UTF_16); cell.setEncoding(short) 1); cell.setCellValue(單元格內(nèi)容); 4、取得sheet的數(shù)目 c-sharp view plaincopyprint?wb.getNumberOfSheets() 5、 根據(jù)index取得sheet對(duì)象c-sharp view plaincopyprint?1. HSSFSheet sheet = wb.getSheetAt(0); 6、取得有效的行數(shù)

7、c-sharp view plaincopyprint?1. int rowcount = sheet.getLastRowNum(); 7、取得一行的有效單元格個(gè)數(shù) c-sharp view plaincopyprint?1. row.getLastCellNum(); 8、單元格值類(lèi)型讀寫(xiě)c-sharp view plaincopyprint?1. cell.setCellType(HSSFCell.CELL_TYPE_STRING); /設(shè)置單元格為STRING類(lèi)型 2. cell.getNumericCellValue();/讀取為數(shù)值類(lèi)型的單元格內(nèi)容 9、設(shè)置列寬、行高c-sharp

8、 view plaincopyprint?1. sheet.setColumnWidth(short)column,(short)width); 2. row.setHeight(short)height); 10、添加區(qū)域，合并單元格c-sharp view plaincopyprint?1. Region region = new Region(short)rowFrom,(short)columnFrom,(short)rowTo 2. ,(short)columnTo);/合并從第rowFrom行columnFrom列 3. sheet.addMergedRegion(region);

9、/ 到rowTo行columnTo的區(qū)域 4. /得到所有區(qū)域 5. sheet.getNumMergedRegions() 11、保存Excel文件c-sharp view plaincopyprint?1. FileOutputStream fileOut = new FileOutputStream(path); 2. wb.write(fileOut); 12、根據(jù)單元格不同屬性返回字符串?dāng)?shù)值c-sharp view plaincopyprint?1. public String getCellStringValue(HSSFCell cell) 2. String cellValue

10、 = ; 3. switch (cell.getCellType() 4. case HSSFCell.CELL_TYPE_STRING:/字符串類(lèi)型 5. cellValue = cell.getStringCellValue(); 6. if(cellValue.trim().equals()|cellValue.trim().length()=0) 7. cellValue= ; 8. break; 9. case HSSFCell.CELL_TYPE_NUMERIC: /數(shù)值類(lèi)型 10. cellValue = String.valueOf(cell.getNumericCellVal

11、ue(); 11. break; 12. case HSSFCell.CELL_TYPE_FORMULA: /公式 13. cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC); 14. cellValue = String.valueOf(cell.getNumericCellValue(); 15. break; 16. case HSSFCell.CELL_TYPE_BLANK: 17. cellValue= ; 18. break; 19. case HSSFCell.CELL_TYPE_BOOLEAN: 20. break; 21. case HS

12、SFCell.CELL_TYPE_ERROR: 22. break; 23. default: 24. break; 25. 26. return cellValue; 27. 13、常用單元格邊框格式c-sharp view plaincopyprint?1. HSSFCellStyle style = wb.createCellStyle(); 2. style.setBorderBottom(HSSFCellStyle.BORDER_DOTTED);/下邊框 3. style.setBorderLeft(HSSFCellStyle.BORDER_DOTTED);/左邊框 4. style

13、.setBorderRight(HSSFCellStyle.BORDER_THIN);/右邊框 5. style.setBorderTop(HSSFCellStyle.BORDER_THIN);/上邊框 14、設(shè)置字體和內(nèi)容位置c-sharp view plaincopyprint?1. HSSFFont f = wb.createFont(); 2. f.setFontHeightInPoints(short) 11);/字號(hào) 3. f.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);/加粗 4. style.setFont(f); 5. style.se

14、tAlignment(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(string);/給單元格

15、設(shè)公式 11. style.setRotation(short rotation);/單元格內(nèi)容的旋轉(zhuǎn)的角度 15、插入圖片c-sharp view plaincopyprint?1. /先把讀進(jìn)來(lái)的圖片放到一個(gè)ByteArrayOutputStream中，以便產(chǎn)生ByteArray 2. ByteArrayOutputStream byteArrayOut = new ByteArrayOutputStream(); 3. BufferedImage bufferImg = ImageIO.read(new File(ok.jpg); 4. ImageIO.write(bufferImg,j

16、pg,byteArrayOut); 5. /讀進(jìn)一個(gè)excel模版 6. FileInputStream fos = new FileInputStream(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

17、. HSSFClientAnchor anchor = new HSSFClientAnchor(0,0,1023,255,(short) 0,0,(short)10,10); 13. patriarch.createPicture(anchor , wb.addPicture(byteArrayOut.toByteArray(),HSSFWorkbook.PICTURE_TYPE_JPEG); 16、調(diào)整工作表位置c-sharp view plaincopyprint?1. HSSFWorkbook wb = new HSSFWorkbook(); 2. HSSFSheet sheet =

18、wb.createSheet(format sheet); 3. HSSFPrintSetup ps = sheet.getPrintSetup(); 4. sheet.setAutobreaks(true); 5. ps.setFitHeight(short)1); 6. ps.setFitWidth(short)1); 17、設(shè)置打印區(qū)域c-sharp view plaincopyprint?1. HSSFSheet sheet = wb.createSheet(Sheet1); 2. wb.setPrintArea(0, $A$1:$C$2); 18、標(biāo)注腳注c-sharp view p

19、laincopyprint?1. HSSFSheet sheet = wb.createSheet(format sheet); 2. HSSFFooter footer = sheet.getFooter() 3. footer.setRight( Page + HSSFFooter.page() + of + HSSFFooter.numPages() ); 19、在工作單中清空行數(shù)據(jù)，調(diào)整行位置c-sharp view plaincopyprint?1. HSSFWorkbook wb = new HSSFWorkbook(); 2. HSSFSheet sheet = wb.creat

20、eSheet(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); 20、選中指定的工作表c-sharp view plaincopyprint?1. HSSFSheet sheet = wb.createSheet(row sheet); 2. heet.setSelected(true); 21、工作表的放大縮小c-sharp

21、 view plaincopyprint?1. HSSFSheet sheet1 = wb.createSheet(new sheet); 2. sheet1.setZoom(1,2); / 50 percent magnification 22、頭注和腳注c-sharp view plaincopyprint?1. HSSFSheet sheet = wb.createSheet(new sheet); 2. HSSFHeader header = sheet.getHeader(); 3. header.setCenter(Center Header); 4. header.setLeft

22、(Left Header); 5. header.setRight(HSSFHeader.font(Stencil-Normal, Italic) + 6. HSSFHeader.fontSize(short) 16) + Right w/ Stencil-Normal Italic font and size 16); 23、自定義顏色c-sharp view plaincopyprint?1. HSSFCellStyle style = wb.createCellStyle(); 2. style.setFillForegroundColor(HSSFColor.LIME.index);

23、3. style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); 4. HSSFFont font = wb.createFont(); 5. font.setColor(HSSFColor.RED.index); 6. style.setFont(font); 7. cell.setCellStyle(style); 24、填充和顏色設(shè)置c-sharp view plaincopyprint?1. HSSFCellStyle style = wb.createCellStyle(); 2. style.setFillBackgroundColo

24、r(HSSFColor.AQUA.index); 3. style.setFillPattern(HSSFCellStyle.BIG_SPOTS); 4. HSSFCell 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.

25、setCellStyle(style); 25、強(qiáng)行刷新單元格公式c-sharp view plaincopyprint?1. HSSFFormulaEvaluator eval=new HSSFFormulaEvaluator(HSSFWorkbook) wb); 2. private static void updateFormula(Workbook wb,Sheet s,int row) 3. Row r=s.getRow(row); 4. Cell c=null; 5. FormulaEcaluator eval=null; 6. if(wb instanceof HSSFWorkb

26、ook) 7. eval=new HSSFFormulaEvaluator(HSSFWorkbook) wb); 8. else if(wb instanceof XSSFWorkbook) 9. eval=new XSSFFormulaEvaluator(XSSFWorkbook) wb); 10. for(int i=r.getFirstCellNum();i工作表-行-單元格。這樣一分析就很簡(jiǎn)單了。我們定義兩個(gè)Excel文件，內(nèi)容相同，只是版本不同，分2003和2007來(lái)處理。 創(chuàng)建工作簿時(shí)可以接收一個(gè)輸入流對(duì)象，那么輸入流對(duì)象可以從文件對(duì)象來(lái)生成，這樣就可以繼續(xù)進(jìn)行了。取出工作表，取出

27、行，遍歷單元格，數(shù)據(jù)就拿到了。代碼如下：Java代碼1. packageorg.ourpioneer.excel;2. importjava.io.File;3. importjava.io.FileInputStream;4. importjava.io.IOException;5. importjava.io.InputStream;6. importjava.util.ArrayList;7. importjava.util.List;8. importorg.apache.poi.hssf.usermodel.HSSFCell;9. importorg.apache.poi.hssf.

28、usermodel.HSSFRow;10. importorg.apache.poi.hssf.usermodel.HSSFSheet;11. importorg.apache.poi.hssf.usermodel.HSSFWorkbook;12. importorg.ourpioneer.excel.bean.Student;13. /*14. *POI讀取Excel示例，分2003和200715. *16. *authorNanlei17. *18. */19. publicclassReadExcel20. privatestaticStringxls2003=C:student.xls

29、;21. privatestaticStringxlsx2007=C:student.xlsx;22. /*23. *讀取Excel2003的示例方法24. *25. *paramfilePath26. *return27. */28. privatestaticListreadFromXLS2003(StringfilePath)29. FileexcelFile=null;/Excel文件對(duì)象30. InputStreamis=null;/輸入流對(duì)象31. StringcellStr=null;/單元格，最終按字符串處理32. ListstudentList=newArrayList();

30、/返回封裝數(shù)據(jù)的List33. Studentstudent=null;/每一個(gè)學(xué)生信息對(duì)象34. try35. excelFile=newFile(filePath);36. is=newFileInputStream(excelFile);/獲取文件輸入流37. HSSFWorkbookworkbook2003=newHSSFWorkbook(is);/創(chuàng)建Excel2003文件對(duì)象38. HSSFSheetsheet=workbook2003.getSheetAt(0);/取出第一個(gè)工作表，索引是039. /開(kāi)始循環(huán)遍歷行，表頭不處理，從1開(kāi)始40. for(inti=1;i=sheet

31、.getLastRowNum();i+)41. student=newStudent();/實(shí)例化Student對(duì)象42. HSSFRowrow=sheet.getRow(i);/獲取行對(duì)象43. if(row=null)/如果為空，不處理44. continue;45. 46. /循環(huán)遍歷單元格47. for(intj=0;jrow.getLastCellNum();j+)48. HSSFCellcell=row.getCell(j);/獲取單元格對(duì)象49. if(cell=null)/單元格為空設(shè)置cellStr為空串50. cellStr=;51. elseif(cell.getCell

32、Type()=HSSFCell.CELL_TYPE_BOOLEAN)/對(duì)布爾值的處理52. cellStr=String.valueOf(cell.getBooleanCellValue();53. elseif(cell.getCellType()=HSSFCell.CELL_TYPE_NUMERIC)/對(duì)數(shù)字值的處理54. cellStr=cell.getNumericCellValue()+;55. else/其余按照字符串處理56. cellStr=cell.getStringCellValue();57. 58. /下面按照數(shù)據(jù)出現(xiàn)位置封裝到bean中59. if(j=0)60. s

33、tudent.setName(cellStr);61. elseif(j=1)62. student.setGender(cellStr);63. elseif(j=2)64. student.setAge(newDouble(cellStr).intValue();65. elseif(j=3)66. student.setSclass(cellStr);67. else68. student.setScore(newDouble(cellStr).intValue();69. 70. 71. studentList.add(student);/數(shù)據(jù)裝入List72. 73. catch(I

34、OExceptione)74. e.printStackTrace();75. finally/關(guān)閉文件流76. if(is!=null)77. try78. is.close();79. catch(IOExceptione)80. e.printStackTrace();81. 82. 83. 84. returnstudentList;85. 86. /*87. *主函數(shù)88. *89. *paramargs90. */91. publicstaticvoidmain(Stringargs)92. longstart=System.currentTimeMillis();93. List

35、list=readFromXLS2003(xls2003);94. for(Studentstudent:list)95. System.out.println(student);96. 97. longend=System.currentTimeMillis();98. System.out.println(end-start)+msdone!);99. 100. 做幾點(diǎn)說(shuō)明，如果不處理表頭，那么就從準(zhǔn)備處理的行開(kāi)始，而整個(gè)sheet對(duì)行的索引是從0開(kāi)始的，而Excel中是1，這點(diǎn)和數(shù)組/集合類(lèi)似。對(duì)于單元格中的數(shù)字，默認(rèn)按double類(lèi)型處理，所以只能字符串轉(zhuǎn)double，再取出int值。

36、最后執(zhí)行主函數(shù)，得到如下內(nèi)容： 這樣就拿到對(duì)象的List了，之后要持久到數(shù)據(jù)庫(kù)或者直接做業(yè)務(wù)邏輯就隨心所欲了。下面來(lái)看2007的處理，處理流程和2003是類(lèi)似的，區(qū)別就是使用的對(duì)象，2003中對(duì)象是HSSF*格式的，而2007是XSSF*格式的。方法如下：Java代碼1. publicstaticListreadFromXLSX2007(StringfilePath)2. FileexcelFile=null;/Excel文件對(duì)象3. InputStreamis=null;/輸入流對(duì)象4. StringcellStr=null;/單元格，最終按字符串處理5. ListstudentList=n

37、ewArrayList();/返回封裝數(shù)據(jù)的List6. Studentstudent=null;/每一個(gè)學(xué)生信息對(duì)象7. try8. excelFile=newFile(filePath);9. is=newFileInputStream(excelFile);/獲取文件輸入流10. XSSFWorkbookworkbook2007=newXSSFWorkbook(is);/創(chuàng)建Excel2003文件對(duì)象11. XSSFSheetsheet=workbook2007.getSheetAt(0);/取出第一個(gè)工作表，索引是012. /開(kāi)始循環(huán)遍歷行，表頭不處理，從1開(kāi)始13. for(inti=1;i=sheet.getLastRowNum();i+)14. student=newStudent();/實(shí)例化Student對(duì)象15. XSSFRowrow=sheet.getRow(i);/獲取行對(duì)象16. if(row=null)/如果為空，不處理17. continue;18. 19. /循環(huán)遍歷單元格20. for(intj=0;jrow.getLastCellNum();j+)21. XSSFCellcell=row.getCell(j);/獲取單元格對(duì)象22. if(cell=null)/單元格為空設(shè)置