This blog is mainly about Java in the POI read Excel, and Excel version includes: 2003-2007 and 20,102 versions, that is, Excel suffix named: xls and xlsx.
Read Excel and MySQL Related: Java POI Technology read Excel data to MySQL
You can also read and import excel in: Java's POI technology to learn how to write Excel information
Importing Excel using JXL technology: Java's JXL technology
The following is the project structure of this article:
The jar files required in the project:
The Excel data used (2003-2007,2010 are the same data )
Operating effect:
=================================================
SOURCE section:
=================================================
/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 {One page public static final String office_excel_2003_postfix = "xls"; public static Fin Al string office_excel_2010_postfix = "xlsx"; all public static final String EMPTY = ""; String point = "."; n Static final string lib_path = "LIB"; 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 Exce L file! "; 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; Ten import java.util.List; Import Org.apache.poi.hssf.usermodel.HSSFCell; Import Org.apache.poi.hssf.usermodel.HSSFRow; Import Org.apache.poi.hssf.usermodel.HSSFSheet; Org.apache.poi.hssf.usermodel.HSSFWorkbook import; Org.apache.poi.xssf.usermodel.XSSFCell import; + Import Org.apache.poi.xssf.usermodel.XSSFRow; Import Org.apache.poi.xssf.usermodel.XSSFSheet; Import Org.apache.poi.xssf.usermodel.XSSFWorkbook; Import Com.b510.common.Common; Import Com.b510.excel.util.Util; Com.b510.excel.vo.Student import; /** * @author Hongten * @created 2014-5-20 * * (public class Readexcel {30 31/** 32 * Read the Excel file * @param path the path of the Excel file @return * @throws IOException 36 * * Notoginseng public LIST< Student> readexcel (String path) throws IOException {# if (path = = NULL | | Common.EMPTY.equals (path)) {n = return null; +} else {postfix String = util.getpostf IX (PATH); if (! Common.EMPTY.equals (Postfix)) {Common.OFFICE_EXCEL_2003_POSTFIX.equals (postfix)) {44 return Readxls (path); Common.OFFICE_EXCEL_2010_POSTFIX.equals} else if (POSTFIX) {readxlsx return (p ATH); System.out.println} else {(path + common.not_excel_file); 50 }, and return null; /** * Read the Excel * * @param path The path of the Excel file @return 5 9 * @throws IOException */list<student> readxlsx (String path) throws IOException {62 SYSTEM.OUT.PRINTLN (common.processing + path); InpuTStream is = new FileInputStream (path); Xssfworkbook Xssfworkbook = new Xssfworkbook (IS); Student Student = null; list<student> List = new arraylist<student> (); *//Read the Sheet (int numsheet = 0; Numsheet < xssfworkbook.getnumberofsheets (); numsheet++) {Xssfsheet Xssfsheet = Xssfworkbook.getsheetat (Numsheet); (Xssfsheet = = null) {71 Continue *//Read the Row rowNum for (int = 1; rowNum <= Xssfsheet.getlastrownum () ; rownum++) {Xssfrow Xssfrow = Xssfsheet.getrow (RowNum); if (xssfrow! = null) {77 Student = new Student (); Xssfcell no = Xssfrow.getcell (0); Xssfcell name = Xssfrow.getcell (1); Xssfcell age = Xssfrow.getcell (2); Bayi Xssfcell score = Xssfrow.getcell (3); 82 Student.setno (GetValue (no)); Student.setname (GetValue (name)); Student.setage (GetValue (age)); Student.setscore (Float.valueof (GetValue (score))); List.add (student); (a) A. /** 94 * Read the Excel 2003-2007 * @param path The path of the Excel * @return 9 7 * @throws IOException 98 * * * Public list<student> Readxls (String path) throws IOException {100 SYSTEM.OUT.PRINTLN (common.processing + path); 101 InputStream is = new FileInputStream (path); 102 HSSFW Orkbook Hssfworkbook = new Hssfworkbook (IS); 103 Student Student = null;104 list<student> List = new Arraylist<student> ();//Read the Sheet106 for (int numsheet = 0; Numsheet < HSSFWORKBOOK.GETN Umberofsheets (); numsheet++) {107 Hssfsheet Hssfsheet = Hssfworkbook.getsheetat (numsheet); 108 if (Hssfsheet = = null) {109 con tinue;110}111//Read the Row112 for (int rowNum = 1; rowNum <= hssfsheet.getlastr Ownum (); rownum++) {113 Hssfrow Hssfrow = Hssfsheet.getrow (RowNum); if (hssfrow! = null) {115 Student = new Student (); Hssfcell no = Hssfrow.getcell (0); 117 Hssfcell name = Hssfrow.getcell (1), 118 Hssfcell age = Hssfrow.getcell (2); 119 HSSF Cell score = Hssfrow.getcell (3); Student.setno (GetValue (No)); 121 Student.setnam E (GetValue (name)); 122 Student.setage (GetValue (age)); 123 Student.setscore (Float.val Ueof (GetValue (Score)); 124 List.add (student); 125}126}127}128 Return List;129}130 131 @SuppressWarnings ("static-access") Private String GetValue (Xssfcell xssfrow) {133 I F (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 (XSSFR Ow.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.getbooleancell Value ()); 146} else if (hssfcell.getcelltype () = = Hssfcell.cell_type_numeric) {147 return String.value Of (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 {$ public static void main (string[] args) throw s IOException {string excel2003_2007 = common.student_info_xls_path;21 string excel2010 = Common.studen T_INFO_XLSX_PATH;22//Read the 2003-2007 excel23 list<student> List = new Readexcel (). Readexcel (exc el2003_2007); (list = null) {Student student:list) {System.out.prin TLN ("No.:" + student.getno () + ", Name:" + student.getname () + ", Age:" + student.getage () + ", Score:" + student. Getscore ());}28}29 System.out.println ("======================================"); 30 Read the excel31 LiSt<student> List1 = new Readexcel (). Readexcel (excel2010); + if (list1! = null) {A-for (Student Student:list1) {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 * @auth or Hongten10 * @created 2014-5-2111 */12 public class Util { /**15 * Get postfix of the path16
* @param path17 * @return18 */19 public static string Getpostfix (string path) { (Path = = NULL | | Common.EMPTY.equals (Path.trim ())) { return common.empty;22 }23 if (Path.contains (Common.point)) { return path.substring (Path.lastindexof (common.point) + 1, path.length ()), }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 */16 Private Integer id;17/**18 * study number */20 private String no;21/**22 * Name 2 3 */24 private String name;25/**26 * College */28 private String age;29/**30 * Score 31 */32 Private float score;33 public Integer getId () {}37 id;36 setId ( Integer id) {this.id = id;40}41-Public String Getno () {"Return no;44}45" Publi c void Setno (String no) {this.no = no;48}49 public String getName () {return name;52 }53 public void SetName (string name) {this.name = name;56}57-Public String getage () {59 Return age;60}61-public void Setage (String age) {this.age = age;64}65-Public Float Getscore () {SetScore return score;68}69 (float score) {71 This.score = score;72}73 74}
Java's POI technology reads Excel data