这篇blog主要是讲述java中poi读取excel，而excel的版本包括：2003-2007和2010两个版本， 即excel的后缀名为：xls和xlsx。
读取excel和MySQL相关：
你也可以在 ： 了解到写入Excel的方法信息
使用JXL技术 ：
下面是本文的项目结构：
项目中所需要的jar文件：
所用的Excel数据（2003-2007,2010都是一样的数据）
运行效果：
=================================================
源码部分：
=================================================
/Excel2010/src/com/b510/common/Common.java
1 /** 2 * 3 */ 4 package com.b510.common; 5 6 /** 7 * @author Hongten 8 * @created 2014-5-21 9 */10 public class Common {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 package com.b510.excel; 5 6 import java.io.FileInputStream; 7 import java.io.IOException; 8 import java.io.InputStream; 9 import java.util.ArrayList; 10 import java.util.List; 11 12 import org.apache.poi.hssf.usermodel.HSSFCell; 13 import org.apache.poi.hssf.usermodel.HSSFRow; 14 import org.apache.poi.hssf.usermodel.HSSFSheet; 15 import org.apache.poi.hssf.usermodel.HSSFWorkbook; 16 import org.apache.poi.xssf.usermodel.XSSFCell; 17 import org.apache.poi.xssf.usermodel.XSSFRow; 18 import org.apache.poi.xssf.usermodel.XSSFSheet; 19 import org.apache.poi.xssf.usermodel.XSSFWorkbook; 20 21 import com.b510.common.Common; 22 import com.b510.excel.util.Util; 23 import com.b510.excel.vo.Student; 24 25 /** 26 * @author Hongten 27 * @created 2014-5-20 28 */ 29 public class ReadExcel { 30 31 /** 32 * read the Excel file 33 * @param path the path of the Excel file 34 * @return 35 * @throws IOException 36 */ 37 public List readExcel(String path) throws IOException { 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 return readXls(path); 45 } else if (Common.OFFICE_EXCEL_2010_POSTFIX.equals(postfix)) { 46 return readXlsx(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 2010 57 * @param path the path of the excel file 58 * @return 59 * @throws IOException 60 */ 61 public List readXlsx(String path) throws IOException { 62 System.out.println(Common.PROCESSING + path); 63 InputStream is = new FileInputStream(path); 64 XSSFWorkbook xssfWorkbook = new XSSFWorkbook(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 = new Student(); 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 return list; 91 } 92 93 /** 94 * Read the Excel 2003-2007 95 * @param path the path of the Excel 96 * @return 97 * @throws IOException 98 */ 99 public List readXls(String path) throws IOException {100 System.out.println(Common.PROCESSING + path);101 InputStream is = new FileInputStream(path);102 HSSFWorkbook hssfWorkbook = new HSSFWorkbook(is);103 Student student = null;104 List list = new ArrayList ();105 // Read the Sheet106 for (int numSheet = 0; numSheet < hssfWorkbook.getNumberOfSheets(); numSheet++) {107 HSSFSheet hssfSheet = hssfWorkbook.getSheetAt(numSheet);108 if (hssfSheet == null) {109 continue;110 }111 // Read the Row112 for (int rowNum = 1; rowNum <= hssfSheet.getLastRowNum(); rowNum++) {113 HSSFRow hssfRow = hssfSheet.getRow(rowNum);114 if (hssfRow != null) {115 student = new Student();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 return list;129 }130 131 @SuppressWarnings("static-access")132 private String getValue(XSSFCell xssfRow) {133 if (xssfRow.getCellType() == xssfRow.CELL_TYPE_BOOLEAN) {134 return String.valueOf(xssfRow.getBooleanCellValue());135 } else if (xssfRow.getCellType() == xssfRow.CELL_TYPE_NUMERIC) {136 return String.valueOf(xssfRow.getNumericCellValue());137 } else {138 return String.valueOf(xssfRow.getStringCellValue());139 }140 }141 142 @SuppressWarnings("static-access")143 private String getValue(HSSFCell hssfCell) {144 if (hssfCell.getCellType() == hssfCell.CELL_TYPE_BOOLEAN) {145 return String.valueOf(hssfCell.getBooleanCellValue());146 } else if (hssfCell.getCellType() == hssfCell.CELL_TYPE_NUMERIC) {147 return String.valueOf(hssfCell.getNumericCellValue());148 } else {149 return String.valueOf(hssfCell.getStringCellValue());150 }151 }152 } /Excel2010/src/com/b510/excel/client/Client.java
1 /** 2 * 3 */ 4 package com.b510.excel.client; 5 6 import java.io.IOException; 7 import java.util.List; 8 9 import com.b510.common.Common;10 import com.b510.excel.ReadExcel;11 import com.b510.excel.vo.Student;12 13 /**14 * @author Hongten15 * @created 2014-5-2116 */17 public class Client {18 19 public static void main(String[] args) throws IOException {20 String excel2003_2007 = Common.STUDENT_INFO_XLS_PATH;21 String excel2010 = Common.STUDENT_INFO_XLSX_PATH;22 // read the 2003-2007 excel23 List list = new ReadExcel().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 excel31 List list1 = new ReadExcel().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 package com.b510.excel.util; 5 6 import com.b510.common.Common; 7 8 /** 9 * @author Hongten10 * @created 2014-5-2111 */12 public class Util {13 14 /**15 * get postfix of the path16 * @param path17 * @return18 */19 public static String getPostfix(String path) {20 if (path == null || Common.EMPTY.equals(path.trim())) {21 return Common.EMPTY;22 }23 if (path.contains(Common.POINT)) {24 return path.substring(path.lastIndexOf(Common.POINT) + 1, path.length());25 }26 return Common.EMPTY;27 }28 } /Excel2010/src/com/b510/excel/vo/Student.java
1 /** 2 * 3 */ 4 package com.b510.excel.vo; 5 6 /** 7 * Student 8 * 9 * @author Hongten10 * @created 2014-5-1811 */12 public class Student {13 /**14 * id 15 */16 private Integer id;17 /**18 * 学号19 */20 private String no;21 /**22 * 姓名23 */24 private String name;25 /**26 * 学院27 */28 private String age;29 /**30 * 成绩31 */32 private float score;33 34 public Integer getId() {35 return id;36 }37 38 public void setId(Integer id) {39 this.id = id;40 }41 42 public String getNo() {43 return no;44 }45 46 public void setNo(String no) {47 this.no = no;48 }49 50 public String getName() {51 return name;52 }53 54 public void setName(String name) {55 this.name = name;56 }57 58 public String getAge() {59 return age;60 }61 62 public void setAge(String age) {63 this.age = age;64 }65 66 public float getScore() {67 return score;68 }69 70 public void setScore(float score) {71 this.score = score;72 }73 74 } 相关Jar文件下载：
源码下载：
---------updated on 2018-08-21
源码下载：
链接:
密码: tri2
========================================================
More reading,and english is important.
I'm Hongten
大哥哥大姐姐，觉得有用打赏点哦！多多少少没关系，一分也是对我的支持和鼓励。谢谢。 Hongten博客排名在100名以内。粉丝过千。 Hongten出品，必是精品。
E | hongtenzone@foxmail.com B |
========================================================