POI操作Excel常用方法总结及对excel的读写举例

1、目录1. 一 POI简介2. 二 HSSF概况 3. 三 POI EXCEL文档结构类4. 四 EXCEL常用操作方法 1. 取得sheet的数目 c-sharp view plaincopyprint wbgetNumberOfSheets wbgetNumberOfSheets 2. 取得一行的有效单元格个数 c-sharp view plaincopyprint rowgetLastCellNum rowgetLastCellNum 一、 POI简介Apache POI是Apache软件基金会的开放源码函式库，POI提供API给Java程序对Microsoft Office格式档案读和写

2、的功能。二、 HSSF概况 HSSF 是Horrible SpreadSheet Format的缩写，通过HSSF，你可以用纯Java代码来读取、写入、修改Excel文件。HSSF 为读取操作提供了两类API：usermodel和eventusermodel，即“用户模型”和“事件-用户模型”。三、 POI EXCEL文档结构类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();/创建

5、Excel工作簿对象 HSSFSheet sheet = wb.createSheet(new sheet);/创建Excel工作表对象 HSSFRow row = sheet.createRow(short)0); /创建Excel工作表的行 cellStyle = wb.createCellStyle();/创建单元格样式 row.createCell(short)0).setCellStyle(cellStyle); /创建Excel工作表指定行的单元格 row.createCell(short)0).setCellValue(1); /设置Excel工作表的值 3、设置sheet名称和

6、单元格内容 c-sharp view plaincopyprint?wb.setSheetName(1, 第一张工作表,HSSFCell.ENCODING_UTF_16); cell.setEncoding(short) 1); cell.setCellValue(单元格内容); 4、取得sheet的数目 c-sharp view plaincopyprint?wb.getNumberOfSheets() 5、 根据index取得sheet对象c-sharp view plaincopyprint?1. HSSFSheet sheet = wb.getSheetAt(0); 6、取得有效的行数

7、c-sharp view plaincopyprint?1. int rowcount = sheet.getLastRowNum(); 7、取得一行的有效单元格个数 c-sharp view plaincopyprint?1. row.getLastCellNum(); 8、单元格值类型读写c-sharp view plaincopyprint?1. cell.setCellType(HSSFCell.CELL_TYPE_STRING); /设置单元格为STRING类型 2. cell.getNumericCellValue();/读取为数值类型的单元格内容 9、设置列宽、行高c-sharp

8、 view plaincopyprint?1. sheet.setColumnWidth(short)column,(short)width); 2. row.setHeight(short)height); 10、添加区域，合并单元格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的区域 4. /得到所有区域 5. sheet.getNumMergedRegions() 11、保存Excel文件c-sharp view plaincopyprint?1. FileOutputStream fileOut = new FileOutputStream(path); 2. wb.write(fileOut); 12、根据单元格不同属性返回字符串数值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: /数值类型 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、设置字体和内容位置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);/单元格内容的旋转的角度 8. HSSFDataFormat df = wb.createDataFormat(); 9. style1.setDataFormat(df.getFormat(0.00%);/设置单元格数据格式 10. cell.setCellFormula(string);/给单元格

15、设公式 11. style.setRotation(short rotation);/单元格内容的旋转的角度 15、插入图片c-sharp view plaincopyprint?1. /先把读进来的图片放到一个ByteArrayOutputStream中，以便产生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. /创建一个工作薄 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、调整工作表位置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、设置打印区域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、在工作单中清空行数据，调整行位置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、填充和颜色设置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文件，内容相同，只是版本不同，分2003和2007来处理。 创建工作簿时可以接收一个输入流对象，那么输入流对象可以从文件对象来生成，这样就可以继续进行了。取出工作表，取出

27、行，遍历单元格，数据就拿到了。代码如下：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、/返回封装数据的List33. Studentstudent=null;/每一个学生信息对象34. try35. excelFile=newFile(filePath);36. is=newFileInputStream(excelFile);/获取文件输入流37. HSSFWorkbookworkbook2003=newHSSFWorkbook(is);/创建Excel2003文件对象38. HSSFSheetsheet=workbook2003.getSheetAt(0);/取出第一个工作表，索引是039. /开始循环遍历行，表头不处理，从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. /循环遍历单元格47. for(intj=0;jrow.getLastCellNum();j+)48. HSSFCellcell=row.getCell(j);/获取单元格对象49. if(cell=null)/单元格为空设置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)/对数字值的处理54. cellStr=cell.getNumericCellValue()+;55. else/其余按照字符串处理56. cellStr=cell.getStringCellValue();57. 58. /下面按照数据出现位置封装到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);/数据装入List72. 73. catch(I

34、OExceptione)74. e.printStackTrace();75. finally/关闭文件流76. if(is!=null)77. try78. is.close();79. catch(IOExceptione)80. e.printStackTrace();81. 82. 83. 84. returnstudentList;85. 86. /*87. *主函数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，这点和数组/集合类似。对于单元格中的数字，默认按double类型处理，所以只能字符串转double，再取出int值。

36、最后执行主函数，得到如下内容： 这样就拿到对象的List了，之后要持久到数据库或者直接做业务逻辑就随心所欲了。下面来看2007的处理，处理流程和2003是类似的，区别就是使用的对象，2003中对象是HSSF*格式的，而2007是XSSF*格式的。方法如下：Java代码1. publicstaticListreadFromXLSX2007(StringfilePath)2. FileexcelFile=null;/Excel文件对象3. InputStreamis=null;/输入流对象4. StringcellStr=null;/单元格，最终按字符串处理5. ListstudentList=n

37、ewArrayList();/返回封装数据的List6. Studentstudent=null;/每一个学生信息对象7. try8. excelFile=newFile(filePath);9. is=newFileInputStream(excelFile);/获取文件输入流10. XSSFWorkbookworkbook2007=newXSSFWorkbook(is);/创建Excel2003文件对象11. XSSFSheetsheet=workbook2007.getSheetAt(0);/取出第一个工作表，索引是012. /开始循环遍历行，表头不处理，从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. /循环遍历单元格20. for(intj=0;jrow.getLastCellNum();j+)21. XSSFCellcell=row.getCell(j);/获取单元格对象22. if(cell=null)/单元格为空设置