ApachePOI

使用ApachePOI进行Excel文件的操作

事前准备 

public class ApachePOITest {
    //文件路径
    public static final String PATH = "D:\\1.xlsx";
    //模拟数据
    private final List<Student> list;
    //打印日志
    private final Logger logger = Logger.getLogger(ApachePOITest.class.getName());

    public ApachePOITest() {
        list = new ArrayList<>();
        list.add(new Student("瑟夫", 25));
        list.add(new Student("稣法", 26));
        list.add(new Student("维斯", 22));
        list.add(new Student("捷特", 19));
    }
    //内部类
    static class Student {
        String name;
        int age;

        public Student(String name, int age) {
            this.name = name;
            this.age = age;
        }

        @Override
        public String toString() {
            return "Student{" +
                    "name='" + name + '\'' +
                    ", age=" + age +
                    '}';
        }
    }
}

文件写入 

    void write() {
        //创建一个excel
        try (XSSFWorkbook work = new XSSFWorkbook();) {
            //创建一个sheet
            XSSFSheet sheet = work.createSheet();
            //创建一个row
            XSSFRow rowHead = sheet.createRow(0);
            //设置第一行
            rowHead.createCell(0).setCellValue("姓名");
            rowHead.createCell(1).setCellValue("年龄");
            for (int i = 1; i < list.size() + 1; i++) {
                XSSFRow row = sheet.createRow(i);
                row.createCell(0).setCellValue(list.get(i - 1).name);
                row.createCell(1).setCellValue(list.get(i - 1).age);
            }
            work.write(Files.newOutputStream(Paths.get(PATH)));
            logger.info("文件写入完成");
        } catch (IOException e) {
            throw new RuntimeException(e);
        }
    }

 文件读取

    void read() {
        ArrayList<Student> students = new ArrayList<>();
        try (FileInputStream fileInputStream = new FileInputStream(PATH);) {
            XSSFWorkbook excel = new XSSFWorkbook(fileInputStream);
            //获得第一个工作表
            XSSFSheet sheetAt = excel.getSheetAt(0);
            //获得最后一行的行号
            int lastRowNum = sheetAt.getLastRowNum();
            for (int i = 1; i <= lastRowNum; i++) {
                //获得当前行
                XSSFRow row = sheetAt.getRow(i);
                //获取最后一列的了列号 short lastCellNum = row.getLastCellNum();
                Student student = new Student(row.getCell(0).getStringCellValue(), (int) row.getCell(1).getNumericCellValue());
                students.add(student);
            }
            excel.close();
            logger.info("文件读取完成: " + students);
        } catch (IOException e) {
            throw new RuntimeException(e);
        }
    }