java的poi技术读取Excel[2003-2007,2010]

转载

IT祖师爷 2021-08-18 00:34:55

文章标签 java apache 读取excel 数据 mysql 文章分类 Java 后端开发

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

读取excel和MySQL相关： java的poi技术读取Excel数据到MySQL

你也可以在： java的poi技术读取和导入Excel 了解到写入Excel的方法信息

使用JXL技术： java的jxl技术导入Excel

下面是本文的项目结构：

java的poi技术读取Excel[2003-2007,2010]_mysql

项目中所需要的jar文件：

java的poi技术读取Excel[2003-2007,2010]_读取excel_02

所用的Excel数据（2003-2007,2010都是一样的数据）

java的poi技术读取Excel[2003-2007,2010]_apache_03

运行效果：

java的poi技术读取Excel[2003-2007,2010]_mysql_04

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

源码部分：

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

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

java的poi技术读取Excel[2003-2007,2010]_apache_05

 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      * 学号
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 }