最近要实现一个数据从Excel文件导入与导出功能,使用到了POI组件,从网上找到的代码(感谢原作者),给大家参考下。
以下功能实现的是从Excel文件中读取数据。
Excel:
Bean:
复制代码
[/code]
下面是操作类:ReadExcel
复制代码
结果:
Student [age= 23,gender= 男,name= 张三,sclass= 一班,score= 94]
Student [age= 20,gender= 女,name= 李四,sclass= 二班,score= 92]
Student [age= 21,gender= 男,name= 王五,sclass= 五班,score= 87]
Student [age= 22,gender= 女,name= 赵六,sclass= 三班,score= 83]
耗时:380
以下功能实现的是从Excel文件中读取数据。
Excel:
姓名 | 性别 | 年龄 | 班级 | 成绩 |
张三 | 男 | 23 | 一班 | 94 |
李四 | 女 | 20 | 二班 | 92 |
王五 | 男 | 21 | 五班 | 87 |
赵六 | 女 | 22 | 三班 | 83 |
Bean:
- [code]package com.way.poi.bean;
- public class Student {
- private String name;
- private String gender;
- private int age;
- private String sclass;
- private int score;
- public Student(){
- super();
- }
- public Student(String name, String gender, int age, String sclass, int score) {
- super();
- this.name = name;
- this.gender = gender;
- this.age = age;
- this.sclass = sclass;
- this.score = score;
- }
- public String toString(){
- return "Student [age= " + age +",gender= " + gender +",name= " + name +",sclass= " + sclass +",score= " + score +"]";
- }
- public String getName() {
- return name;
- }
- public void setName(String name) {
- this.name = name;
- }
- public String getGender() {
- return gender;
- }
- public void setGender(String gender) {
- this.gender = gender;
- }
- public int getAge() {
- return age;
- }
- public void setAge(int age) {
- this.age = age;
- }
- public String getSclass() {
- return sclass;
- }
- public void setSclass(String sclass) {
- this.sclass = sclass;
- }
- public int getScore() {
- return score;
- }
- public void setScore(int score) {
- this.score = score;
- }
- }
下面是操作类:ReadExcel
- package com.way.poi.readexcel;
- import java.io.File;
- import java.io.FileInputStream;
- import java.io.IOException;
- import java.io.InputStream;
- import java.util.ArrayList;
- 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;
- import org.apache.poi.hssf.usermodel.HSSFWorkbook;
- import com.way.poi.bean.Student;
- public class ReadExcel {
- private static String path = "student.xls";
- //主函数
- public static void main(String[] args) {
- long start = System.currentTimeMillis();
- List<Student> list = readFromExcel(path);
- for(Student student : list){
- System.out.println(student);
- }
- long end = System.currentTimeMillis();
- System.out.println("耗时:" + (end - start));
- }
- public static List<Student> readFromExcel(String filePath){
- //Excel文件对象
- File excelFile = null;
- //输入流对象
- InputStream is = null;
- //单元格,最终按字符串处理
- String cellStr = null;
- //返回封装对象
- List<Student> studentList = new ArrayList<Student>();
- //每一个学生对象
- Student student = null;
- try {
- excelFile = new File(filePath);
- //获取文件输入流
- is = new FileInputStream(excelFile);
- //创建Excel文件对象
- HSSFWorkbook workBook = new HSSFWorkbook(is);
- //取出第一个工作表
- HSSFSheet sheet = workBook.getSheetAt(0);
- //开始循环遍历行,表头不处理,从1开始
- for(int i = 1; i <= sheet.getLastRowNum(); i++){
- //实例化学生对象
- student = new Student();
- //获取行对象
- HSSFRow row = sheet.getRow(i);
- //如果行为空,则不处理
- if(row == null){
- continue;
- }
- //循环遍历单元格
- for(int j = 0; j < row.getLastCellNum(); j++){
- //获取单元格对象
- HSSFCell cell = row.getCell(j);
- //单元格为空,设置空串
- if(cell == null){
- cellStr = "";
- }else if(cell.getCellType() == HSSFCell.CELL_TYPE_BOOLEAN){ //对布尔值处理
- cellStr = String.valueOf(cell.getBooleanCellValue());
- }else if(cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC){ //对数字值处理
- cellStr = cell.getNumericCellValue() + "";
- }else{ //其他按字符串处理
- cellStr = cell.getStringCellValue();
- }
- //按照数据出现的位置将数据封装到Bean中
- if(j == 0){
- student.setName(cellStr);
- }else if(j == 1){
- student.setGender(cellStr);
- }else if(j == 2){
- student.setAge(new Double(cellStr).intValue());
- }else if(j == 3){
- student.setSclass(cellStr);
- }else{
- student.setScore(new Double(cellStr).intValue());
- }
- }
- studentList.add(student);
- }
- } catch (Exception e) {
- e.printStackTrace();
- }finally{
- if(is != null){
- try {
- is.close();
- } catch (IOException e) {
- // TODO Auto-generated catch block
- e.printStackTrace();
- }
- }
- }
- return studentList;
- }
- }
结果:
Student [age= 23,gender= 男,name= 张三,sclass= 一班,score= 94]
Student [age= 20,gender= 女,name= 李四,sclass= 二班,score= 92]
Student [age= 21,gender= 男,name= 王五,sclass= 五班,score= 87]
Student [age= 22,gender= 女,name= 赵六,sclass= 三班,score= 83]
耗时:380