转载

springboot+easypoi,快速搞定excel导入导出

发中经常会遇到excel的处理，导入导出解析等等，java中比较流行的用poi，但是每次都要写大段工具类来搞定这事儿，此处推荐一个别人造好的轮子【easypoi】，下面介绍下“轮子”的使用。

1.pom引入

[XML] 纯文本查看复制代码

<groupId>cn.afterturn</groupId>
        <artifactId>easypoi-base</artifactId>
        <version>3.0.3</version>
    </dependency>
    <dependency>
        <groupId>cn.afterturn</groupId>
        <artifactId>easypoi-web</artifactId>
        <version>3.0.3</version>
    </dependency>
    <dependency>
        <groupId>cn.afterturn</groupId>
        <artifactId>easypoi-annotation</artifactId>
        <version>3.0.3</version>
    </dependency>

编写实体类

此处注意必须要有空构造函数，否则会报错“对象创建错误”

关于注解@Excel，其他还有@ExcelCollection，@ExcelEntity ，@ExcelIgnore，@ExcelTarget等，此处我们用不到，可以去官方查看更多

[Java] 纯文本查看复制代码

import cn.afterturn.easypoi.excel.annotation.Excel;

import java.util.Date;

public class Person {

@Excel(name = "姓名", orderNum = "0")
private String name;

@Excel(name = "性别", replace = {"男_1", "女_2"}, orderNum = "1")
private String sex;

@Excel(name = "生日", exportFormat = "yyyy-MM-dd", orderNum = "2")
private Date birthday;

public Person(String name, String sex, Date birthday) {
    this.name = name;
    this.sex = sex;
    this.birthday = birthday;
}

public String getName() {
    return name;
}

public void setName(String name) {
    this.name = name;
}

public String getSex() {
    return sex;
}

public void setSex(String sex) {
    this.sex = sex;
}

public Date getBirthday() {
    return birthday;
}

public void setBirthday(Date birthday) {
    this.birthday = birthday;
}

}

[Java] 纯文本查看复制代码

public static void exportExcel(List<?> list, String title, String sheetName, Class<?> pojoClass,String fileName,boolean isCreateHeader, HttpServletResponse response){

ExportParams exportParams = new ExportParams(title, sheetName);
    exportParams.setCreateHeadRows(isCreateHeader);
    defaultExport(list, pojoClass, fileName, response, exportParams);

}
public static void exportExcel(List<?> list, String title, String sheetName, Class<?> pojoClass,String fileName, HttpServletResponse response){
    defaultExport(list, pojoClass, fileName, response, new ExportParams(title, sheetName));
}

public static void exportExcel(List<Map<String, Object>> list, String fileName, HttpServletResponse response){

defaultExport(list, fileName, response);
}

private static void defaultExport(List<?> list, Class<?> pojoClass, String fileName, HttpServletResponse response, ExportParams exportParams) {
    Workbook workbook = ExcelExportUtil.exportExcel(exportParams,pojoClass,list);
    if (workbook != null);
    downLoadExcel(fileName, response, workbook);
}

private static void downLoadExcel(String fileName, HttpServletResponse response, Workbook workbook) {
    try {
        response.setCharacterEncoding("UTF-8");
        response.setHeader("content-Type", "application/vnd.ms-excel");
        response.setHeader("Content-Disposition",
                "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8"));
        workbook.write(response.getOutputStream());
    } catch (IOException e) {
        throw new NormalException(e.getMessage());
    }
}

private static void defaultExport(List<Map<String, Object>> list, String fileName, HttpServletResponse response) {

Workbook workbook = ExcelExportUtil.exportExcel(list, ExcelType.HSSF);
    if (workbook != null);
    downLoadExcel(fileName, response, workbook);
}

public static <T> List<T> importExcel(String filePath,Integer titleRows,Integer headerRows, Class<T> pojoClass){
    if (StringUtils.isBlank(filePath)){
        return null;
    }
    ImportParams params = new ImportParams();
    params.setTitleRows(titleRows);
    params.setHeadRows(headerRows);
    List<T> list = null;
    try {
        list = ExcelImportUtil.importExcel(new File(filePath), pojoClass, params);
    }catch (NoSuchElementException e){
        throw new NormalException("模板不能为空");
    } catch (Exception e) {
        e.printStackTrace();
        throw new NormalException(e.getMessage());
    }
    return list;
}
public static <T> List<T> importExcel(MultipartFile file, Integer titleRows, Integer headerRows, Class<T> pojoClass){
    if (file == null){
        return null;
    }
    ImportParams params = new ImportParams();
    params.setTitleRows(titleRows);
    params.setHeadRows(headerRows);
    List<T> list = null;
    try {
        list = ExcelImportUtil.importExcel(file.getInputStream(), pojoClass, params);
    }catch (NoSuchElementException e){
        throw new NormalException("excel文件不能为空");
    } catch (Exception e) {
        throw new NormalException(e.getMessage());
    }
    return list;
}

测试

[Java] 纯文本查看复制代码

@RequestMapping("export")

public void export(HttpServletResponse response){

    //模拟从数据库获取需要导出的数据
    List<Person> personList = new ArrayList<>();
    Person person1 = new Person("路飞","1",new Date());
    Person person2 = new Person("娜美","2", DateUtils.addDate(new Date(),3));
    Person person3 = new Person("索隆","1", DateUtils.addDate(new Date(),10));
    Person person4 = new Person("小狸猫","1", DateUtils.addDate(new Date(),-10));
    personList.add(person1);
    personList.add(person2);
    personList.add(person3);
    personList.add(person4);

    //导出操作
    FileUtil.exportExcel(personList,"花名册","草帽一伙",Person.class,"海贼王.xls",response);
}

@RequestMapping("importExcel")
public void importExcel(){
    String filePath = "F://海贼王.xls";
    //解析excel，
    List<Person> personList = FileUtil.importExcel(filePath,1,1,Person.class);
    //也可以使用MultipartFile,使用 FileUtil.importExcel(MultipartFile file, Integer titleRows, Integer headerRows, Class<T> pojoClass)导入
    System.out.println("导入数据一共【"+personList.size()+"】行");

    //TODO 保存数据库

原文 https://segmentfault.com/a/1190000021129318

正文到此结束