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

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

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

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

4、; 4. /得到Excel工作表對象 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常用對象 c-sharp view plaincopyprint?HSSFWorkbook wb = new HSSFWorkbook();/創(chuàng)建

5、Excel工作簿對象 HSSFSheet sheet = wb.createSheet(new sheet);/創(chuàng)建Excel工作表對象 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名稱和

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對象c-sharp view plaincopyprint?1. HSSFSheet sheet = wb.getSheetAt(0); 6、取得有效的行數(shù)

7、c-sharp view plaincopyprint?1. int rowcount = sheet.getLastRowNum(); 7、取得一行的有效單元格個數(shù) c-sharp view plaincopyprint?1. row.getLastCellNum(); 8、單元格值類型讀寫c-sharp view plaincopyprint?1. cell.setCellType(HSSFCell.CELL_TYPE_STRING); /設(shè)置單元格為STRING類型 2. cell.getNumericCellValue();/讀取為數(shù)值類型的單元格內(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ù)單元格不同屬性返回字符串數(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:/字符串類型 5. cellValue = cell.getStringCellValue(); 6. if(cellValue.trim().equals()|cellValue.trim().length()=0) 7. cellValue= ; 8. break; 9. case HSSFCell.CELL_TYPE_NUMERIC: /數(shù)值類型 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);/字號 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. /先把讀進來的圖片放到一個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. /讀進一個excel模版 6. FileInputStream fos = new FileInputStream(filePathName+/stencil.xlt); 7. fs = new POIFSFileSystem(fos); 8. /創(chuàng)建一個工作薄 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、標注腳注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、強行刷新單元格公式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工作表-行-單元格。這樣一分析就很簡單了。我們定義兩個Excel文件，內(nèi)容相同，只是版本不同，分2003和2007來處理。 創(chuàng)建工作簿時可以接收一個輸入流對象，那么輸入流對象可以從文件對象來生成，這樣就可以繼續(xù)進行了。取出工作表，取出

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文件對象30. InputStreamis=null;/輸入流對象31. StringcellStr=null;/單元格，最終按字符串處理32. ListstudentList=newArrayList();

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

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

32、Type()=HSSFCell.CELL_TYPE_BOOLEAN)/對布爾值的處理52. cellStr=String.valueOf(cell.getBooleanCellValue();53. elseif(cell.getCellType()=HSSFCell.CELL_TYPE_NUMERIC)/對數(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. 做幾點說明，如果不處理表頭，那么就從準備處理的行開始，而整個sheet對行的索引是從0開始的，而Excel中是1，這點和數(shù)組/集合類似。對于單元格中的數(shù)字，默認按double類型處理，所以只能字符串轉(zhuǎn)double，再取出int值。

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

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