java使用poi读写Excel

package com.demo.excel;

import com.demo.pojo.Student;

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 org.apache.poi.ss.usermodel.Cell;

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 java.io.File;

import java.io.FileInputStream;

import java.io.FileOutputStream;

import java.util.ArrayList;

import java.util.List;

import static javax.xml.bind.JAXBIntrospector.getValue;

/**

 * Created by xfma on 2017/1/19.

 */

public class ReadExcel {

    final static String excelFileName = "F:/Test.xls";

    public static void main(String[] args) {

        try {

            createExcel(createStudent());

            List<Student> list = readExcel(excelFileName);

            for (int i = 0; i < list.size(); i++) {

                System.out.println(list.get(i).getName() + "\t" + list.get(i).getAge() + "\t" + list.get(i).getSchool() + "\t" + list.get(i).getAddress());

            }

        } catch (Exception e) {

            e.getStackTrace();

        }

    }

    /**

     * 读Excel

     *

     * @param excelFileName 文件名

     * @return

     * @throws Exception

     */

    public static List<Student> readExcel(String excelFileName) throws Exception {

        boolean isExcel2003 = true;

        if (!excelFileName.endsWith("xls")) {

            isExcel2003 = false;

        }

        File file = new File(excelFileName);

        FileInputStream fis = new FileInputStream(file);

        Workbook workbook = null;

        /*excel2003和2007不是用同一个对象读取的*/

        if (isExcel2003) {

            workbook = new HSSFWorkbook(fis);

        } else {

            workbook = new XSSFWorkbook(fis);

        }

        Sheet sheet = workbook.getSheetAt(0);//得到第0个Sheet

        sheet.getLastRowNum();

        List<Student> list = new ArrayList<Student>();

        for (int r = 0; r < sheet.getLastRowNum(); r++) {

            Row row = sheet.getRow(r + 1);//越过标题，从第二行读

            if (row != null) {

                Student student = new Student();

                Cell name = row.getCell(0);

                Cell age = row.getCell(1);

                Cell address = row.getCell(2);

                Cell school = row.getCell(3);

                student.setName(getValue(name).toString());

                float f = Float.parseFloat(getValue(age).toString());

                student.setAge((int) f);

                student.setAddress(getValue(address).toString());

                student.setSchool(getValue(school).toString());

                list.add(student);

            }

        }

        return list;

    }

    /**

     * 生成Excel

     *

     * @param list 对象集合

     * @throws Exception

     */

    public static void createExcel(List<Student> list) throws Exception {

        //1.创建一个HSSFWorkbook对象，每个HSSFWorkbook对应一个新的Excel文件

        HSSFWorkbook workbook = new HSSFWorkbook();

        //2.在HSSFWorkbook中添加一个sheet，对应Excel中的一个sheet表

        HSSFSheet sheet = workbook.createSheet("学生信息表");

        String[] cells = new String[]{"姓名", "年龄", "地址", "学校"};//表头

        int rowSize = list.size() + 1;//从第二条开始读，去掉标题

        for (int r = 0; r < rowSize; r++) {

            HSSFRow row = sheet.createRow(r);

            for (int c = 0; c < cells.length; c++) {

                HSSFCell cell = row.createCell(c);

                if (r == 0) {

                    cell.setCellValue(cells[c]);//创建表头

                } else {

                      /*往表内写数据*/

                    int index = r - 1;//从第一条数据往里面写

                    switch (c) {

                        case 0:

                            cell.setCellValue(list.get(index).getName());

                            continue;

                        case 1:

                            cell.setCellValue(list.get(index).getAge());

                            continue;

                        case 2:

                            cell.setCellValue(list.get(index).getAddress());

                            continue;

                        case 3:

                            cell.setCellValue(list.get(index).getSchool());

                            continue;

                    }

                }

            }

        }

        FileOutputStream fileOut = new FileOutputStream(excelFileName);

        workbook.write(fileOut);

        fileOut.flush();

        fileOut.close();

    }

    /**

     * 创建示例

     *

     * @return

     */

    public static List<Student> createStudent() {

        List<Student> list = new ArrayList<Student>();

        Student s1 = new Student("小黑", 18, "上海浦东", "复旦大学");

        Student s2 = new Student("小白", 19, "上海普陀", "同济大学");

        Student s3 = new Student("小玉", 22, "上海黄埔", "上海交通大学");

        Student s4 = new Student("小红", 20, "上海静安", "上海财经大学");

        list.add(s1);

        list.add(s2);

        list.add(s3);

        list.add(s4);

        return list;

    }

}

pojo：

package com.demo.pojo;

import java.io.Serializable;

/**

 * Created by xfma on 2017/1/19.

 */

public class Student implements Serializable{

    private String name;

    private Integer age;

    private String address;

    private String school;

    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 String getAddress() {

        return address;

    }

    public void setAddress(String address) {

        this.address = address;

    }

    public String getSchool() {

        return school;

    }

    public void setSchool(String school) {

        this.school = school;

    }

    public Student(String name, Integer age, String address, String school) {

        this.name = name;

        this.age = age;

        this.address = address;

        this.school = school;

    }

    public Student() {

    }

}

pom.xml：

<!-- POI -->

    <dependency>

      <groupId>org.apache.poi</groupId>

      <artifactId>poi</artifactId>

      <version>3.15</version>

    </dependency>

    <!-- poi-ooxml -->

    <dependency>

      <groupId>org.apache.poi</groupId>

      <artifactId>poi-ooxml</artifactId>

      <version>3.15</version>

    </dependency>