EasyPOI学习(Java导入导出Excel)

• easyPOI官方文档

快速入门

• 导入依赖

    <!-- https://mvnrepository.com/artifact/cn.afterturn/easypoi-base -->
        <dependency>
            <groupId>cn.afterturn</groupId>
            <artifactId>easypoi-base</artifactId>
            <version>4.1.0</version>
        </dependency>

Excel导出

• 实体
• 使用@ExcelTarget注解标注一个类,表示一个excel模型,一个实体对象表示excel的一行
• 使用@Excel注解标注字段,一个字段表示Excel中的一列
  • type属性为1时表示导出文本
  • type属性为2时表示导出图片
  • 默认为1
• 使用@ExcelIgnore注解标注表示导出时忽略的字段
• 使用 @ExcelEntity注解标注在字段上,表示一对一导出
• 使用``注解标注在字段上,表示一对多导出

@Data
@ExcelTarget("user")
public class User implements Serializable {
    @Excel(name = "编号",orderNum = "0",width = 20,height = 15)
    private String id;
    @Excel(name = "姓名",orderNum = "1",width = 20,height = 15)
    private  String name;
    @Excel(name = "年龄",orderNum = "3",width = 20,height = 15)
    private Integer age;
    @Excel(name = "生日",format = "yyyy-MM-dd HH:mm:ss",orderNum = "2",width = 40,height = 15)
    private Date birthday;
    @Excel(name = "爱好",orderNum = "4",width = 20,height = 15)
    @ExcelIgnore
    private List<String> hobbies;
    //重写getter方法格式化爱好的导出格式
    @Excel(name = "爱好",orderNum = "4",width = 20,height = 15)
    private String hobbiesToString;
    @Excel(name = "状态",orderNum = "5",width = 20,height = 15,replace = {"已激活_0","未激活_1"})
    private Integer state;

    //一对一
    @ExcelEntity
    private Card card;
    //一对多
    @ExcelCollection(name = "订单信息",orderNum = "8")
    private List<Order> orders;

    //导出图片,通过url导出
   //type = 2表示导出的是图片
    // imageType = 1表示通过url导出,imageType = 2表示通过二进制文件导出,默认是1
    @Excel(name = "头像",orderNum = "0",width = 20,height = 15,type = 2)
    private String photoUrl;

    public String getHobbiesToString() {
        StringBuilder builder = new StringBuilder();
        for (String hobby : hobbies) {
            builder.append(hobby).append("-");
        }
        return builder.toString().substring(0,builder.length()-1);
    }
}

• 一对一关联实体

@Data
@ExcelTarget("card")
public class Card implements Serializable {
    @Excel(name = "身份证号",width = 20,height = 15,orderNum = "6")
    private String no;
    @Excel(name = "地址",width = 20,height = 15,orderNum = "7")
    private String address;
}

• 一对多关联实体

@Data
@NoArgsConstructor
@AllArgsConstructor
@ExcelTarget("order")
public class Order {
    @Excel(name = "订单号", orderNum = "1", width = 20,height = 15)
    private String orderId;
    @Excel(name = "订单内容", orderNum = "2", width = 20,height = 15)
    private String orderContent;
}

• 案例

    /**
     * 导出excel
     * @throws IOException
     */
    public void exportExcel() throws IOException {
        //获取数据
        List<User> users = getAllUser();
        //使用EasyPoi工具类创建一个excel导出对象
        //第一个参数是excel表的配置信息,第二个参数是对应实体类的class,第三个参数是实际导出的实体列表
        Workbook workbook = ExcelExportUtil.exportExcel(new ExportParams("用户信息表", "用户信息表"), User.class,users);
        //创建一个文件输出流
        FileOutputStream fileOutputStream = new FileOutputStream("excelExportTest1.xls");
        //将excel写到输出流
        workbook.write(fileOutputStream);

        //关闭资源
        fileOutputStream.close();
        workbook.close();
    }

导入

• 实体类

@Data
@ExcelTarget(value = "emp")
public class Emp {
    @Excel(name = "编号")
    private String id;
    @Excel(name = "姓名")
    private  String name;
    @Excel(name = "年龄")
    private Integer age;
    @Excel(name = "生日",format = "yyyy-MM-dd HH:mm:ss")
    private Date birthday;
}

• 案例

/**
     * 导入excel
     * @throws Exception
     */
    public void importExcel() throws Exception {
        //excel文件的输入流
        InputStream inputStream = new FileInputStream("excelImportTest1.xls");

        //导入参数设置
        ImportParams params = new ImportParams();
        //设置标题占几行
        params.setTitleRows(1);
        //设置头部占几行
        params.setHeadRows(2);
        //设置从那个sheet开始读取,默认为0
        //params.setStartSheetIndex(0);
        //设置读取几个sheet,默认为1
        //params.setSheetNum(1);
        //验证excel是否包含对应的列
        params.setImportFields(new String[]{"编号"});
        List<Emp> emps = ExcelImportUtil.importExcel(inputStream, Emp.class, params);
        emps.forEach(System.out::println);
    }