这篇blog主要是讲述java中poi读取excel,而excel的版本包括:-和两个版本, 即excel的后缀名为:xls和xlsx。
你也可以在 :
下面是本文的项目结构:
项目中所需要的jar文件:
所用的Excel数据(-,都是一样的数据)
运行效果:
=================================================
源码部分:
=================================================
/Excel/src/com/b510/common/Common.java
1 /**
2 *3 */
4 mon;5
6 /**
7 *@authorHongten8 * @created -5-219 */
10 public classCommon {11
12 public static final String OFFICE_EXCEL__POSTFIX = "xls";13 public static final String OFFICE_EXCEL__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__POSTFIX;19 public static final String STUDENT_INFO_XLSX_PATH = LIB_PATH + "/student_info" + POINT +OFFICE_EXCEL__POSTFIX;20 public static final String NOT_EXCEL_FILE = " : Not the Excel file!";21 public static final String PROCESSING = "Processing...";22
23 }
/Excel/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 mon;22 importcom.b510.excel.util.Util;23 importcom.b510.excel.vo.Student;24
25 /**
26 *@authorHongten27 * @created -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__POSTFIX.equals(postfix)) {44 returnreadXls(path);45 } else if(Common.OFFICE_EXCEL__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 57 *@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 -95 *@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 }
/Excel/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 mon;10 importcom.b510.excel.ReadExcel;11 importcom.b510.excel.vo.Student;12
13 /**
14 *@authorHongten15 * @created -5-2116 */
17 public classClient {18
19 public static void main(String[] args) throwsIOException {20 String excel_ =Common.STUDENT_INFO_XLS_PATH;21 String excel =Common.STUDENT_INFO_XLSX_PATH;22 //read the - excel
23 List list = newReadExcel().readExcel(excel_);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 excel
31 List list1 = newReadExcel().readExcel(excel);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 }
/Excel/src/com/b510/excel/util/Util.java
1 /**
2 *3 */
4 packagecom.b510.excel.util;5
6 mon;7
8 /**
9 *@authorHongten10 * @created -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 }
/Excel/src/com/b510/excel/vo/Student.java
1 /**
2 *3 */
4 packagecom.b510.excel.vo;5
6 /**
7 * Student8 *9 *@authorHongten10 * @created -5-1811 */
12 public classStudent {13 /**
14 * id15 */
16 privateInteger id;17 /**
18 * 学号19 */
20 privateString no;21 /**
22 * 姓名23 */
24 privateString name;25 /**
26 * 学院27 */
28 privateString age;29 /**
30 * 成绩31 */
32 private floatscore;33
34 publicInteger getId() {35 returnid;36 }37
38 public voidsetId(Integer id) {39 this.id =id;40 }41
42 publicString getNo() {43 returnno;44 }45
46 public voidsetNo(String no) {47 this.no =no;48 }49
50 publicString getName() {51 returnname;52 }53
54 public voidsetName(String name) {55 this.name =name;56 }57
58 publicString getAge() {59 returnage;60 }61
62 public voidsetAge(String age) {63 this.age =age;64 }65
66 public floatgetScore() {67 returnscore;68 }69
70 public void setScore(floatscore) {71 this.score =score;72 }73
74 }
---------updated on -08-21
源码下载:
密码: tri2
========================================================
More reading,and english is important.
I'm Hongten
大哥哥大姐姐,觉得有用打赏点哦!多多少少没关系,一分也是对我的支持和鼓励。谢谢。
Hongten博客排名在100名以内。粉丝过千。
Hongten出品,必是精品。
E | hongtenzone@ B |/hongten
========================================================