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

来源：互联网发布：中异软件好用吗编辑：程序博客网时间：2024/05/16 06:23

这篇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

下面是本文的项目结构：

项目中所需要的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<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 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<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 excel31         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 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文件下载：http://pan.baidu.com/s/1mg7IG6c

源码下载：http://pan.baidu.com/s/1qWwfpDi

0 0