java读取excel2010文件_java的poi技术读取Excel[2003-2007,2010]

王若琳

于 2021-02-13 03:22:11 发布

阅读量101

点赞数

文章标签： java读取excel2010文件

本文链接：https://blog.csdn.net/weixin_42347426/article/details/114099952

版权

这篇blog主要是讲述java中poi读取excel，而excel的版本包括：2003-2007和2010两个版本，即excel的后缀名为：xls和xlsx。

你也可以在：

下面是本文的项目结构：

项目中所需要的jar文件：

所用的Excel数据(2003-2007,2010都是一样的数据)

运行效果：

=================================================

源码部分：

=================================================

/Excel2010/src/com/b510/common/Common.java

1 /**

2 *3 */

4 packagecom.b510.common;5

6 /**

7 *@authorHongten8 * @created 2014-5-219 */

10 public classCommon {11

12 public static final String OFFICE_EXCEL_2003_POSTFIX = "xls";13 public static final String OFFICE_EXCEL_2010_POSTFIX = "xlsx";14

15 public static final String EMPTY = "";16 public static final String POINT = ".";17 public static final String LIB_PATH = "lib";18 public static final String STUDENT_INFO_XLS_PATH = LIB_PATH + "/student_info" + POINT +OFFICE_EXCEL_2003_POSTFIX;19 public static final String STUDENT_INFO_XLSX_PATH = LIB_PATH + "/student_info" + POINT +OFFICE_EXCEL_2010_POSTFIX;20 public static final String NOT_EXCEL_FILE = " : Not the Excel file!";21 public static final String PROCESSING = "Processing...";22

23 }

/Excel2010/src/com/b510/excel/ReadExcel.java

1 /**

2 *3 */

4 packagecom.b510.excel;5

6 importjava.io.FileInputStream;7 importjava.io.IOException;8 importjava.io.InputStream;9 importjava.util.ArrayList;10 importjava.util.List;11

12 importorg.apache.poi.hssf.usermodel.HSSFCell;13 importorg.apache.poi.hssf.usermodel.HSSFRow;14 importorg.apache.poi.hssf.usermodel.HSSFSheet;15 importorg.apache.poi.hssf.usermodel.HSSFWorkbook;16 importorg.apache.poi.xssf.usermodel.XSSFCell;17 importorg.apache.poi.xssf.usermodel.XSSFRow;18 importorg.apache.poi.xssf.usermodel.XSSFSheet;19 importorg.apache.poi.xssf.usermodel.XSSFWorkbook;20

21 importcom.b510.common.Common;22 importcom.b510.excel.util.Util;23 importcom.b510.excel.vo.Student;24

25 /**

26 *@authorHongten27 * @created 2014-5-2028 */

29 public classReadExcel {30

31 /**

32 * read the Excel file33 *@parampath the path of the Excel file34 *@return

35 *@throwsIOException36 */

37 public List readExcel(String path) throwsIOException {38 if (path == null ||Common.EMPTY.equals(path)) {39 return null;40 } else{41 String postfix =Util.getPostfix(path);42 if (!Common.EMPTY.equals(postfix)) {43 if(Common.OFFICE_EXCEL_2003_POSTFIX.equals(postfix)) {44 returnreadXls(path);45 } else if(Common.OFFICE_EXCEL_2010_POSTFIX.equals(postfix)) {46 returnreadXlsx(path);47 }48 } else{49 System.out.println(path +Common.NOT_EXCEL_FILE);50 }51 }52 return null;53 }54

55 /**

56 * Read the Excel 201057 *@parampath the path of the excel file58 *@return

59 *@throwsIOException60 */

61 public List readXlsx(String path) throwsIOException {62 System.out.println(Common.PROCESSING +path);63 InputStream is = newFileInputStream(path);64 XSSFWorkbook xssfWorkbook = newXSSFWorkbook(is);65 Student student = null;66 List list = new ArrayList();67 //Read the Sheet

68 for (int numSheet = 0; numSheet < xssfWorkbook.getNumberOfSheets(); numSheet++) {69 XSSFSheet xssfSheet =xssfWorkbook.getSheetAt(numSheet);70 if (xssfSheet == null) {71 continue;72 }73 //Read the Row

74 for (int rowNum = 1; rowNum <= xssfSheet.getLastRowNum(); rowNum++) {75 XSSFRow xssfRow =xssfSheet.getRow(rowNum);76 if (xssfRow != null) {77 student = newStudent();78 XSSFCell no = xssfRow.getCell(0);79 XSSFCell name = xssfRow.getCell(1);80 XSSFCell age = xssfRow.getCell(2);81 XSSFCell score = xssfRow.getCell(3);82 student.setNo(getValue(no));83 student.setName(getValue(name));84 student.setAge(getValue(age));85 student.setScore(Float.valueOf(getValue(score)));86 list.add(student);87 }88 }89 }90 returnlist;91 }92

93 /**

94 * Read the Excel 2003-200795 *@parampath the path of the Excel96 *@return

97 *@throwsIOException98 */

99 public List readXls(String path) throwsIOException {100 System.out.println(Common.PROCESSING +path);101 InputStream is = newFileInputStream(path);102 HSSFWorkbook hssfWorkbook = newHSSFWorkbook(is);103 Student student = null;104 List list = new ArrayList();105 //Read the Sheet

106 for (int numSheet = 0; numSheet < hssfWorkbook.getNumberOfSheets(); numSheet++) {107 HSSFSheet hssfSheet =hssfWorkbook.getSheetAt(numSheet);108 if (hssfSheet == null) {109 continue;110 }111 //Read the Row

112 for (int rowNum = 1; rowNum <= hssfSheet.getLastRowNum(); rowNum++) {113 HSSFRow hssfRow =hssfSheet.getRow(rowNum);114 if (hssfRow != null) {115 student = newStudent();116 HSSFCell no = hssfRow.getCell(0);117 HSSFCell name = hssfRow.getCell(1);118 HSSFCell age = hssfRow.getCell(2);119 HSSFCell score = hssfRow.getCell(3);120 student.setNo(getValue(no));121 student.setName(getValue(name));122 student.setAge(getValue(age));123 student.setScore(Float.valueOf(getValue(score)));124 list.add(student);125 }126 }127 }128 returnlist;129 }130

131 @SuppressWarnings("static-access")132 privateString getValue(XSSFCell xssfRow) {133 if (xssfRow.getCellType() ==xssfRow.CELL_TYPE_BOOLEAN) {134 returnString.valueOf(xssfRow.getBooleanCellValue());135 } else if (xssfRow.getCellType() ==xssfRow.CELL_TYPE_NUMERIC) {136 returnString.valueOf(xssfRow.getNumericCellValue());137 } else{138 returnString.valueOf(xssfRow.getStringCellValue());139 }140 }141

142 @SuppressWarnings("static-access")143 privateString getValue(HSSFCell hssfCell) {144 if (hssfCell.getCellType() ==hssfCell.CELL_TYPE_BOOLEAN) {145 returnString.valueOf(hssfCell.getBooleanCellValue());146 } else if (hssfCell.getCellType() ==hssfCell.CELL_TYPE_NUMERIC) {147 returnString.valueOf(hssfCell.getNumericCellValue());148 } else{149 returnString.valueOf(hssfCell.getStringCellValue());150 }151 }152 }

/Excel2010/src/com/b510/excel/client/Client.java

1 /**

2 *3 */

4 packagecom.b510.excel.client;5

6 importjava.io.IOException;7 importjava.util.List;8

9 importcom.b510.common.Common;10 importcom.b510.excel.ReadExcel;11 importcom.b510.excel.vo.Student;12

13 /**

14 *@authorHongten15 * @created 2014-5-2116 */

17 public classClient {18

19 public static void main(String[] args) throwsIOException {20 String excel2003_2007 =Common.STUDENT_INFO_XLS_PATH;21 String excel2010 =Common.STUDENT_INFO_XLSX_PATH;22 //read the 2003-2007 excel

23 List list = newReadExcel().readExcel(excel2003_2007);24 if (list != null) {25 for(Student student : list) {26 System.out.println("No. : " + student.getNo() + ", name : " + student.getName() + ", age : " + student.getAge() + ", score : " +student.getScore());27 }28 }29 System.out.println("======================================");30 //read the 2010 excel

31 List list1 = newReadExcel().readExcel(excel2010);32 if (list1 != null) {33 for(Student student : list1) {34 System.out.println("No. : " + student.getNo() + ", name : " + student.getName() + ", age : " + student.getAge() + ", score : " +student.getScore());35 }36 }37 }38 }

/Excel2010/src/com/b510/excel/util/Util.java

1 /**

2 *3 */

4 packagecom.b510.excel.util;5

6 importcom.b510.common.Common;7

8 /**

9 *@authorHongten10 * @created 2014-5-2111 */

12 public classUtil {13

14 /**

15 * get postfix of the path16 *@parampath17 *@return

18 */

19 public staticString getPostfix(String path) {20 if (path == null ||Common.EMPTY.equals(path.trim())) {21 returnCommon.EMPTY;22 }23 if(path.contains(Common.POINT)) {24 return path.substring(path.lastIndexOf(Common.POINT) + 1, path.length());25 }26 returnCommon.EMPTY;27 }28 }

/Excel2010/src/com/b510/excel/vo/Student.java

1 /**

2 *3 */

4 packagecom.b510.excel.vo;5

6 /**

7 * Student8 *9 *@authorHongten10 * @created 2014-5-1811 */

12 public classStudent {13 /**

14 * id15 */

16 privateInteger id;17 /**

18 * 学号19 */

20 privateString no;21 /**

22 * 姓名23 */