Poi 和 EasyPoi 的使用

一、POI

1.1  引入依赖

<!--poi导包-->
<dependency>
   <groupId>org.apache.poi</groupId>
   <artifactId>poi</artifactId>
   <version>4.1.0</version>
</dependency>

1.2  导出测试

    @Test
    public void testPOI() {
        List<User> users = userDAO.selectAll();
        //创建工作薄
        Workbook workbook = new HSSFWorkbook();
        //创建字体   楷体、加粗、倾斜 
        Font font = workbook.createFont();
        font.setBold(true);
        font.setFontName("楷体");
        font.setItalic(true);
        //创建样式
        CellStyle cellStyle = workbook.createCellStyle();
        cellStyle.setFont(font);
        //创建日期格式的样式
        CellStyle cellStyle1 = workbook.createCellStyle();
        //创建日期格式
        DataFormat dataFormat = workbook.createDataFormat();
        short format = dataFormat.getFormat("yyyy年MM月dd日");
        cellStyle1.setDataFormat(format);
        //创建工作表
        Sheet sheet = workbook.createSheet("user");
        //第一个参数 是要给那一列设置宽度 下标    第二个参数 宽度  *256
        sheet.setColumnWidth(3,20*256);
        //创建行  参数为下标
        Row row = sheet.createRow(0);
        String[] strings = {"编号","姓名","性别","生日"};
        for (int i = 0; i < strings.length; i++) {
            //创建单元格
            Cell cell = row.createCell(i);
            //给单元格设置样式
            cell.setCellStyle(cellStyle);
            //给单元格赋值
            cell.setCellValue(strings[i]);
        }
        for (int i = 0; i < users.size(); i++) {
            Row row1 = sheet.createRow(i + 1);
            row1.createCell(0).setCellValue(users.get(i).getId());
            row1.createCell(1).setCellValue(users.get(i).getUsername());
            row1.createCell(2).setCellValue(users.get(i).getSex());
            Cell cell = row1.createCell(3);
            cell.setCellStyle(cellStyle1);
            cell.setCellValue(users.get(i).getCreateDate());
        }
        try {
            workbook.write(new FileOutputStream(new File("F:/user.xls")));
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

1.3  导入测试

    @Test
    public void testPOIImport() {
        try {
            Workbook workbook = new HSSFWorkbook(new FileInputStream(new File("F:/user.xls")));
            Sheet sheet = workbook.getSheet("user");
            Row row = sheet.getRow(0);
            String id = row.getCell(0).getStringCellValue();
            String name = row.getCell(1).getStringCellValue();
            String sex = row.getCell(2).getStringCellValue();
            String bir = row.getCell(3).getStringCellValue();
            System.out.println(id+"====="+name+"======"+sex+"======"+bir);
            int lastRowNum = sheet.getLastRowNum();
            for (int i = 1; i <= lastRowNum; i++) {
                User user = new User();
                Row row1 = sheet.getRow(i);
                user.setId(row1.getCell(0).getStringCellValue());
                user.setUsername(row1.getCell(1).getStringCellValue());
                user.setSex(row1.getCell(2).getStringCellValue());
                user.setCreateDate(row1.getCell(3).getDateCellValue());
                System.out.println("user:             "+user);
				//导入数据库
				userService.insert(user);
            }
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

二、EasyPoi

官方文档 -->  EasyPoi教程

2.1  引入依赖

        <!-- easypoi的jar -->
        <dependency>
            <groupId>cn.afterturn</groupId>
            <artifactId>easypoi-base</artifactId>
            <version>3.2.0</version>
        </dependency>
        <dependency>
            <groupId>cn.afterturn</groupId>
            <artifactId>easypoi-web</artifactId>
            <version>3.2.0</version>
        </dependency>
        <dependency>
            <groupId>cn.afterturn</groupId>
            <artifactId>easypoi-annotation</artifactId>
            <version>3.2.0</version>
        </dependency>

注意：引入 easypoi 的jar后就不要引 poi 的jar了，否则会冲突

2.2  导出测试

• 实体类

//Student实体类
@Data
@AllArgsConstructor
@NoArgsConstructor
public class Student implements Serializable {

    @Excel(name="学生编号")
    private String id;

    @Excel(name="学生名字")
    private String name;

    @Excel(name="学生年龄")
    private Integer age;

    @Excel(name="出生年月",exportFormat = "yyyy年MM月dd日")
    private Date bir;

    @ExcelIgnore
    private Double salary;

}


//Teacher实体类
@Data
@NoArgsConstructor
@AllArgsConstructor
@ExcelTarget(value = "teacher")
public class Teacher implements Serializable {
    @ExcelIgnore
    private String id;
    @Excel(name = "教师姓名",needMerge=true)
    private String name;
    @Excel(name = "性别",needMerge=true)
    private String sex;
    @ExcelCollection(name = "1502班学生")
    private List<Student> children;
}

• 测试代码

    @Test
    public void myTest3() throws IOException {
        //学生集合
        List<Student> list = new ArrayList<>();
        for(int i=0;i<10;i++){
            Student student = new Student(i + "", "小黑" + i, 20 + i, new Date(), 10000.0);
            list.add(student);
        }

        //教师集合
        List<Teacher> teachers = new ArrayList<>();
        Teacher teacher1 = new Teacher("1", "教师1", "男", list);
        Teacher teacher2 = new Teacher("2", "教师2", "男", list);
        teachers.add(teacher1);
        teachers.add(teacher2);

        Workbook workbook = ExcelExportUtil.exportExcel(new ExportParams("计算机1502班学生","学生"),
                    Teacher.class,teachers);

        workbook.write(new FileOutputStream("G:\\用户.xls"));
        workbook.close();
    }

2.3  导入测试

配置上与导出是一样的,只是方式反过来而已，具体见官方文档。