easyexcel导出Excel表格

easyexcel相比较poi不是一套成熟的框架，但是开发快捷方便，不耗内存，速度快，是alibaba的开源框架，用起来十分简单：

整个excel表是一个workbook，下面有多个sheet，一个sheet有多行row，一行有多个cell

效果

第一步 引入maven依赖

<dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>easyexcel</artifactId>
            <version>1.1.1</version>
</dependency>

非正式版本目前更新有1.1.5beat版，生产中还是选正式版1.1.1，api 没有那么丰富。

第二步 封装对应实体

a、 需要继承BaseRowModel类，此类map字段可以自定义excel样式

@Data
public class UserVO extends BaseRowModel implements Serializable {

    @ExcelProperty(value = "姓名", index = 0)
    private String name;

    @ExcelProperty(value = "昵称", index = 1)
    private String nickName;

    @ExcelProperty(value = "幸运数字", index = 2)
    private String password;

    @ExcelProperty(value = "生日", index = 3, format = "yyyy/MM/dd")
    private Date birthday;
}

index 代表第几列，value表头

========================================================

b、封装一个sheet表数据对应实体：

@Data
public class MultipleSheelPropety implements Serializable {

    private List<? extends BaseRowModel> data;

    private Sheet sheet;
   }

data字段 为sheet表数据，一行一行的，sheet 字段可以设置顺序，名字等

========================================================

c 、编写excel工具类

@Slf4j
public class ExcelUtil {
    private static Sheet initSheet;

    /**
     * 如果 sheet为空，默认创建一个
     */
    static {
        initSheet = new Sheet(1, 0);
        initSheet.setSheetName("mysheet");
        initSheet.setAutoWidth(Boolean.TRUE);
    }

    /**
     * 生成多Sheet的excle
     *
     * @param response              response
     * @param multipleSheelPropetys multipleSheelPropetys
     */
    public static void writeWithMultipleSheel(HttpServletResponse response, List<MultipleSheelPropety> multipleSheelPropetys) {
        if (CollectionUtils.isEmpty(multipleSheelPropetys)) {
            log.error("export data is null");
            return;
        }
        OutputStream outputStream = null;
        ExcelWriter writer = null;
        try {
            outputStream = response.getOutputStream();
            writer = new ExcelWriter(outputStream, ExcelTypeEnum.XLSX, true);
            for (MultipleSheelPropety multipleSheelPropety : multipleSheelPropetys) {
                Sheet sheet = multipleSheelPropety.getSheet() != null ? multipleSheelPropety.getSheet() : initSheet;
                if (!CollectionUtils.isEmpty(multipleSheelPropety.getData())) {
                    sheet.setClazz(multipleSheelPropety.getData().get(0).getClass());
                }
                writer.write(multipleSheelPropety.getData(), sheet);
            }
        } catch (IOException e) {
            log.error("error la");
        } finally {
            try {
                Objects.requireNonNull(writer).finish();
                Objects.requireNonNull(outputStream).close();
            } catch (IOException e) {
                log.error("excel文件导出失败, 失败原因：{}", e);
            }
        }

    }
}

编写controller，service

controller：

@RestController
public class ExcelController {

    @Autowired
    private ExcelService excelService;

    @RequestMapping(value = "/export", method = RequestMethod.GET)
    public void export(HttpServletResponse response){
        excelService.export(response);
    }
}

service：

public interface ExcelService {

    /**
     * export
     *
     * @param response res
     */
    void export(HttpServletResponse response);
}

@Service
public class ExcelServiceImpl implements ExcelService {

@Autowired
private UserMapper userMapper;

@Override
public void export(HttpServletResponse response) {

    List list = getExportData();
    response.setCharacterEncoding("utf-8");
    response.setContentType("application/vnd.ms-excel;charset=utf-8");
    try {
        response.setHeader("Content-Disposition", "attachment;filename=" + new String(("test.xlsx").getBytes(), "ISO8859-1"));
    } catch (UnsupportedEncodingException e) {
        e.printStackTrace();
    }

    ExcelUtil.writeWithMultipleSheel(response, list);
}

private List getExportData() {
    List list = Lists.newArrayList();

    List<UserDto> allUser = userMapper.getAllUser();

    MultipleSheelPropety multipleSheelPropety = new MultipleSheelPropety();
    multipleSheelPropety.setData(allUser);
    Sheet sheet = new Sheet(1, 0);
    sheet.setSheetName("sheet1");
    multipleSheelPropety.setSheet(sheet);

    list.add(multipleSheelPropety);

    MultipleSheelPropety multipleSheelPropety1 = new MultipleSheelPropety();
    Sheet sheet1 = new Sheet(2, 0);
    sheet1.setSheetName("sheet2");
    multipleSheelPropety1.setSheet(sheet1);
    multipleSheelPropety1.setData(allUser);

    list.add(multipleSheelPropety1);
    return list;
}

}

数据随意填写

测试导出接口