Java中使用Apache POI实现数据的Excel导入和导出

一、首先引入Apache POI jar包（目前较新版本）
1、poi-ooxml-3.15.jar(推荐使用)
下载地址: http://central.maven.org/maven2/org/apache/poi/poi-ooxml/3.15/poi-ooxml-3.15.jar
maven 仓库地址 ：http://www.mvnrepository.com/artifact/org.apache.poi/poi-ooxml

2、poi-3.15.jar
下载地址: http://central.maven.org/maven2/org/apache/poi/poi/3.15/poi-3.15.jar
maven 仓库地址: http://www.mvnrepository.com/artifact/org.apache.poi/poi/3.15

二、Excel数据样例
测试实体类

package com.fxrh.iccmcp.test;

public class Student {

private Integer id;private String no;private String name;private Integer age;private Double score;public Student(){}public Student(String no, String name, Integer age, Double score) {    this.no = no;    this.name = name;    this.age = age;    this.score = score;}public Integer getId() {    return id;}public void setId(Integer id) {    this.id = id;}public String getNo() {    return no;}public void setNo(String no) {    this.no = no;}public String getName() {    return name;}public void setName(String name) {    this.name = name;}public Integer getAge() {    return age;}public void setAge(Integer age) {    this.age = age;}public Double getScore() {    return score;}public void setScore(Double score) {    this.score = score;}@Overridepublic String toString() {    return "Student [id=" + id + ", no=" + no + ", name=" + name + ", age=" + age + ", score=" + score + "]";}

}

三、测试程序
package com.fxrh.iccmcp.test;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.hibernate.SessionFactory;
import org.junit.Test;
//import org.junit.runner.RunWith;
//import org.springframework.beans.factory.annotation.Autowired;
//import org.springframework.test.context.ContextConfiguration;
//import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;
import org.springframework.util.StringUtils;

//@RunWith(SpringJUnit4ClassRunner.class)
//@ContextConfiguration(“classpath:applicationContext.xml”)
public class ExcelPOITest {

//@Autowired private SessionFactory sessionFactory;@Testpublic void test(){    System.out.println(new Date().toLocaleString());    ExcelPOITest ept = new ExcelPOITest();    List<Student> list = ept.readExcelData("C:\\Users\\dell\\Desktop\\sample.xlsx");    System.out.println(list.size());    for (Student stu : list) {        System.out.println(stu);    }    String[] titleRow = {"No","姓名","Age","分数"};    ept.writeExcelData("C:\\Users\\dell\\Desktop\\aa.xls", list, titleRow);}public List<Student> readExcelData(String fileName){    List<Student> list = new ArrayList<Student>();    Student student = null;    FileInputStream fis = null;    try {        //Create the input stream from the xlsx/xls file        fis = new FileInputStream(fileName);        //Create Workbook instance for xlsx/xls file input stream        Workbook workbook = null;        if(fileName.toLowerCase().endsWith(".xlsx")){            workbook = new XSSFWorkbook(fis);        }else if(fileName.toLowerCase().endsWith(".xls")){            workbook = new HSSFWorkbook(fis);        }        Sheet sheet = null;        Row row = null;        Integer succ = 0;        Integer errNum = 0;        Integer count = 0;        String errorInfo="";        if(workbook!=null){            for (int i = 0; i < workbook.getNumberOfSheets(); i++) {//循环表                sheet = workbook.getSheetAt(i);// 第i张表格                for(int j=1;j < sheet.getPhysicalNumberOfRows();j++){//循环行                     //row.getPhysicalNumberOfCells();  //列数                    if(errNum>=20){break;}                    row = sheet.getRow(j);                    if(row == null){break;};//判断行是否还有数据                    if(StringUtils.isEmpty(getCellVaule(row.getCell(0)))){break;};                    ++count;                    student  = new Student();                    int index = 0;//表  列索引                    student.setNo(getCellVaule(row.getCell(index)));                    student.setName(getCellVaule(row.getCell(++index)));                    student.setAge(Integer.valueOf(getCellVaule(row.getCell(++index))));                    student.setScore(Double.valueOf(getCellVaule(row.getCell(++index))));                    list.add(student);                }            }        }    }catch (Exception e){        e.printStackTrace();    } finally {        if(fis!=null){            try {                fis.close();            } catch (IOException e) {                e.printStackTrace();            }        }    }    return list;}public String getCellVaule(Cell cell){    String value = null;    if(cell == null) return value;    switch (cell.getCellType()) {        case Cell.CELL_TYPE_STRING:            value = cell.getRichStringCellValue().toString();            break;        case Cell.CELL_TYPE_NUMERIC:            value = new DecimalFormat("0.######").format(cell.getNumericCellValue());            break;        case Cell.CELL_TYPE_BOOLEAN:            value = String.valueOf(cell.getBooleanCellValue());            break;        case Cell.CELL_TYPE_FORMULA:            value = cell.getArrayFormulaRange().formatAsString();            break;        case Cell.CELL_TYPE_BLANK:            value = "";            break;        default:            value = cell.getRichStringCellValue().toString();            break;    }    if(value == null) return null;    return value;}public void writeExcelData(String excelPath,List<Student> list,String titleRow[]){    Workbook workbook = null;    Sheet sheet = null;    FileOutputStream out = null;    try {        File file = new File(excelPath);        if(!file.exists()){            file.createNewFile();        }        if(excelPath.toLowerCase().endsWith(".xls")){            workbook = new HSSFWorkbook();        }else if(excelPath.toLowerCase().endsWith(".xlsx")){            workbook = new XSSFWorkbook();        }        sheet = workbook.createSheet("Sheet1");//创建第一张表        //sheet.setDefaultColumnWidth((short)15);        int rowNumber = sheet.getLastRowNum();  // 第一行从0开始算        //System.out.println("原始数据总行数，除属性列：" + rowNumber);         for (int i = 1; i <= rowNumber; i++) {//删除原有Excel文件            Row row = sheet.getRow(i);              sheet.removeRow(row);          }        Row row = sheet.createRow(0);//创建第一行    列属性(表头)        for(int i=0;i<titleRow.length;i++){//写  第一行            row.createCell(i).setCellValue(titleRow[i]);        }        for (int i=0;i<list.size();i++) {//写数据            int lastRowNum = sheet.getLastRowNum();              Row currentRow = sheet.createRow(lastRowNum + 1);              int index = 0;//写  第i列            while(index<titleRow.length){                currentRow.createCell(index++).setCellValue(list.get(i).getNo());                currentRow.createCell(index++).setCellValue(list.get(i).getName());                currentRow.createCell(index++).setCellValue(list.get(i).getAge().toString());                currentRow.createCell(index++).setCellValue(list.get(i).getScore().toString());            }        }        out = new FileOutputStream(file);        workbook.write(out);    } catch (Exception e) {        e.printStackTrace();    } finally {          try {              if(out != null){                  out.flush();                  out.close();              }         } catch (Exception e) {              e.printStackTrace();          }      }      System.out.println("数据导出成功");  }

}