cool hit counter poi technology for java to read Excel [2003-2007,2010]_Intefrankly

poi technology for java to read Excel [2003-2007,2010]


This blog is about poi reading excel in java, and excel versions include: 2003-2007 and 2010 two versions, that is, the suffix name of excel: xls and xlsx.

Read excel and MySQL related. java's poi technique to read Excel data to MySQL

You can also find it at. java's poi technology to read and import Excel Get information on how to write to Excel

Use of JXL technology. java's jxl technique to import Excel

The following is the project structure for this paper.

Required jar files in the project.

The Excel data used ( Same data for 2003-2007, 2010

Running effect.

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

Source code 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 {
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<Student> 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<Student> 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<Student> list = new ArrayList<Student>();
 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<Student> 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<Student> list = new ArrayList<Student>();
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 = 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 Hongten
15  * @created 2014-5-21
16  */
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 excel
23         List<Student> 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 excel
31         List<Student> 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 Hongten
10  * @created 2014-5-21
11  */
12 public class Util {
13 
14     /**
15      * get postfix of the path
16      * @param path
17      * @return
18      */
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 Hongten
10  * @created 2014-5-18
11  */
12 public class Student {
13     /**
14      * id   
15      */
16     private Integer id;
17     /**
18      *  student number
19      */
20     private String no;
21     /**
22      *  name and surname
23      */
24     private String name;
25     /**
26      *  academy
27      */
28     private String age;
29     /**
30      *  performance records
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 }

Related Jar files to download.http://pan.baidu.com/s/1mg7IG6c

Source Code Download.http://pan.baidu.com/s/1qWwfpDi

---------updated on 2018-08-21

Source Code Download.

Link: https://pan.baidu.com/s/1Sktrm4gu3aBMB1o8n_ubtA

Password: tri2

E | hongtenzone@foxmail.com B |http://www.cnblogs.com/hongten


Recommended>>
1、Basic usage of Docker
2、Hereford Chain Solves Healthcare Industry Pain Points
3、CCTV naming the underaged pregnant mother Netflix Racer cleans up overnight removes all content
4、LeetCode Brush Up Notes 1
5、What are the features of US storage servers

    已推荐到看一看 和朋友分享想法
    最多200字,当前共 发送

    已发送

    朋友将在看一看看到

    确定
    分享你的想法...
    取消

    分享想法到看一看

    确定
    最多200字,当前共

    发送中

    网络异常,请稍后重试

    微信扫一扫
    关注该公众号