前言
Java解析、生成Excel比较有名的框架有Apache poi、jxl。但他们都存在一个严重的问题就是非常的耗内存，poi有一套SAX模式的API可以一定程度的解决一些内存溢出的问题，但POI还是有一些缺陷，比如07版Excel解压缩以及解压后存储都是在内存中完成的，内存消耗依然很大。easyexcel重写了poi对07版Excel的解析，能够原本一个3M的excel用POI sax依然需要100M左右内存降低到几M，并且再大的excel不会出现内存溢出，03版依赖POI的sax模式。在上层做了模型转换的封装，让使用者更加简单方便
一、API读写操作
简单写(导出)
pox.xml
<dependency><groupId>com.alibaba</groupId><artifactId>easyexcel</artifactId><version>2.2.10</version></dependency>
EasyExcelUtil
/*** @author lanys* @Description: 导出工具类* @date 10/9/ 下午5:47*/@Slf4jpublic class EasyExcelUtil {/*** 简单导出** @param response 响应* @param data导出数据* @param fileName 文件名字* @param sheetName 工作栏名字* @param t 模板类* @param <T> 泛型* @return*/public static <T> void export(HttpServletResponse response, List<T> data, String fileName, String sheetName, Class<T> t) {try {// 这里注意 有同学反应使用swagger 会导致各种问题，请直接用浏览器或者用postmanresponse.sendRedirect("application/vnd.ms-excel");// 设置字符集response.setCharacterEncoding("utf-8");// 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系String name = URLEncoder.encode(fileName, "UTF-8");//文件名response.setHeader("Content-disposition", "attachment;filename=" + name + ".xlsx");//导出EasyExcel.write(response.getOutputStream(), t).autoCloseStream(Boolean.FALSE).sheet(sheetName).doWrite(data);}catch (Exception e){log.error("导出文件异常：{}", e);response.reset();response.setContentType("application/json");response.setCharacterEncoding("utf-8");Map<String, String> map = new HashMap<String, String>();map.put("status", "failure");map.put("message", "下载文件失败" + e.getMessage());try {response.getWriter().println(JSON.toJSONString(map));} catch (IOException ioException) {ioException.printStackTrace();}}}}
SysUser
/*** 系统用户** @author Mark sunlightcs@*/@Data@TableName("sys_user")public class SysUser implements Serializable {private static final long serialVersionUID = 1L;/*** 用户ID*/@TableId@ExcelProperty("用户id")private Long userId;/*** 用户名*/@NotBlank(message="用户名不能为空")@ExcelProperty("用户名")private String username;/*** 密码*/@NotBlank(message="密码不能为空")@ExcelProperty("密码")@JsonProperty(access = JsonProperty.Access.WRITE_ONLY)private String password;/*** 盐*/@ExcelProperty("盐")private String salt;/*** 邮箱*/@ExcelProperty("邮箱")@NotBlank(message="邮箱不能为空")@Email(message="邮箱格式不正确")private String email;/*** 手机号*/@ExcelProperty("手机号")private String mobile;/*** 状态 0：禁用 1：正常*/@ExcelProperty("状态")private Integer status;/*** 角色ID列表*/@ExcelProperty("角色ID列表")@TableField(exist=false)private List<Long> roleIdList;/*** 创建时间*/@ExcelProperty("创建时间")private Date createTime;/*** 部门ID*/@ExcelProperty("部门ID")@NotNull(message="部门不能为空")private Long deptId;/*** 部门名称*/@ExcelProperty("部门名称")@TableField(exist=false)private String deptName;}
testController
@ApiOperation(value = "导出用户列表数据")@GetMapping("/user_list_export")@ApiImplicitParams({@ApiImplicitParam(name = HttpHeaders.AUTHORIZATION, value = "token", required = true, paramType = "header"),@ApiImplicitParam(name = "path", value = "验证码验证", required = true, example = "E:/2.xlsx", paramType = "query")})public void userListExport(@NotEmpty(message = "path数据不能为空") String path) {userService.userListExport(path);}
userService
/*** @ClassName SysUserServerImpl* @Author lanys* @Data 1/4/ 下午6:17*/@Servicepublic class SysUserServiceImpl extends ServiceImpl<SysUserMapper, SysUser> implements SysUserService {/*** 文件导出** @param path 文件保存地址*/@Overridepublic void userListExport(String path) {//获取用户列表List<SysUser> sysUsers = this.userList();monExport(path,sysUsers,"用户列表",SysUser.class);}}
测试：
多sheet导出
使用场景（我返回是用Map展示，需要导出今日，昨日，上周的数据，多sheet）
EasyExcelUtil
/*** @author lanys* @Description: 导出工具类* @date 10/9/ 下午5:47*/@Slf4jpublic class EasyExcelUtil {/*** 多sheet导出** @param response 响应* @param data1数据列表1* @param data2数据列表2* @param data3数据列表3* @param fileName 文档名称* @param t 文档类型* @param <T> 泛型* @throws IOException*/public static <T> void multipleExport(HttpServletResponse response, List<T> data1, List<T> data2, List<T> data3, String fileName,Class<T> t) throws IOException {try {// 这里注意 有同学反应使用swagger 会导致各种问题，请直接用浏览器或者用postmanresponse.setContentType("application/vnd.ms-excel; charset=utf-8");// 设置字符集response.setCharacterEncoding("utf-8");// 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系String name = URLEncoder.encode(fileName, "UTF-8");response.setHeader("Content-disposition", "attachment;filename="+name+".xlsx");ExcelWriter excelWriter = EasyExcel.write(response.getOutputStream(),t).build();WriteSheet writeSheet = EasyExcel.writerSheet(0,"今日开播").head(t).build();WriteSheet writeSheet1 = EasyExcel.writerSheet(1,"昨日开播").head(t).build();WriteSheet writeSheet2 = EasyExcel.writerSheet(2,"上周开播").head(t).build();excelWriter.write(data1,writeSheet).write(data2,writeSheet1).write(data3,writeSheet2);//关闭excelWriter.finish();} catch (Exception e) {log.error("多文件导出异常：{}", e);response.reset();response.setContentType("application/json");response.setCharacterEncoding("utf-8");Map<String, String> map = new HashMap<String, String>();map.put("status", "failure");map.put("message", "下载文件失败" + e.getMessage());try {response.getWriter().println(JSON.toJSONString(map));} catch (IOException ioException) {ioException.printStackTrace();}}}}
TestController
@ApiOperation(value = "首页-实时分析-数据趋势-导出")@GetMapping("/data_trend_derive")@ApiImplicitParams({@ApiImplicitParam(name = "token", value = "token", paramType = "header", required = true),@ApiImplicitParam(paramType = "query", name = "type", value = "1.导出-音浪，2.导出-主播", required = true, dataType = "int",example = "1")})public void dataTrendDerive(HttpServletResponse response,Integer type) throws IOException {Assert.notNull(type,"type数据不能为空:{}",type);groupConsoleService.dataTrendDerive(response,type,super.userId);}
service
/*** 首页-实时分析-数据趋势-导出** @param response 响应* @param type1.导出-音浪，2.导出-主播* @param userId 用户id*/@Overridepublic void dataTrendDerive(HttpServletResponse response, Integer type, Long userId) {try {//获取列表DataTrendVO dataTrendVO = this.dataTrend(type, userId);//Map转ListList<DataTrendDeriveVO> todayList = publicDataTrendDeriveList(dataTrendVO.getToday());List<DataTrendDeriveVO> yesterdayList = publicDataTrendDeriveList(dataTrendVO.getYesterday());List<DataTrendDeriveVO> lastWeekList = publicDataTrendDeriveList(dataTrendVO.getLastWeek());if (type == 1) {EasyExcelUtil.multipleExport(response, todayList, yesterdayList, lastWeekList, "数据趋势音浪数据", DataTrendDeriveVO.class);}if (type == 2) {EasyExcelUtil.multipleExport(response, todayList, yesterdayList, lastWeekList, "数据趋势开播数", DataTrendDeriveVO.class);}} catch (IOException e) {log.error("首页-实时分析-数据趋势-导出数据异常:{}",e);}}/*** 公共的-前-导出数据-处理(map -> list)** @param linkedHashMap map集合* @return list<DataTrendDeriveVO>*/private List<DataTrendDeriveVO> publicDataTrendDeriveList(LinkedHashMap<String, Integer> linkedHashMap) {ArrayList<String> arrayKeyList = new ArrayList(linkedHashMap.keySet());ArrayList<Integer> arrayValueList = new ArrayList(linkedHashMap.values());List<DataTrendDeriveVO> deriveVOArrayList = new ArrayList<>();for (int i = 0; i < arrayKeyList.size(); i++) {deriveVOArrayList.add(new DataTrendDeriveVO(arrayKeyList.get(i), arrayValueList.get(i)));}return deriveVOArrayList;}
DataTrendDeriveVO
/*** @author lanys* @Description:* @date 9/9/ 下午4:23*/@Data@AllArgsConstructor@EqualsAndHashCode(callSuper = false)public class DataTrendDeriveVO implements Serializable {private static final long serialVersionUID = 1L;@ExcelProperty(value = "时间",index = 0)private String time;@ExcelProperty(value = "数据",index = 1)private Integer data;}
测试
简单读(读取)
ExcelListener（监听器）
/*** @Author: Lanys* @Description:* @Date: Create in 23:33 /9/11*/@Slf4jpublic class ExcelListener extends AnalysisEventListener {/*** 自定义用于暂时存储data* 可以通过实例获取该值*/private List<List<String>> datas = new ArrayList<>();/*** 每解析一行都会回调invoke()方法** @param object 读取后的数据对象* @param context 内容*/@Overridepublic void invoke(Object object, AnalysisContext context) {@SuppressWarnings("unchecked") Map<String, String> stringMap = (HashMap<String, String>) object;//数据存储到list，供批量处理，或后续自己业务逻辑处理。datas.add(new ArrayList<>(stringMap.values()));//根据自己业务做处理}@Overridepublic void doAfterAllAnalysed(AnalysisContext context) {//解析结束销毁不用的资源//注意不要调用datas.clear(),否则getDatas为null}/*** 返回数据** @return 返回读取的数据集合**/public List<List<String>> getDatas() {return datas;}/*** 设置读取的数据集合** @param datas 设置读取的数据集合**/public void setDatas(List<List<String>> datas) {this.datas = datas;}}
EasyExcelUtil
/*** @author lanys* @Description: 导出工具类* @date 10/9/ 下午5:47*/@Slf4jpublic class EasyExcelUtil {/*** 根据excel输入流，读取excel文件** @param inputStream exece表格的输入流* @return 返回双重list的集合**/public static List<List<String>> writeWithoutHead(InputStream inputStream) {ExcelListener listener = new ExcelListener();ExcelReader excelReader = EasyExcelFactory.read(inputStream, null, listener).headRowNumber(0).build();excelReader.read();List<List<String>> datas = listener.getDatas();excelReader.finish();return datas;}}
TestController
/*** @Author: Lanys* @Description:* @Date: Create in 23:46 /9/11*/public class TestController {public static void main(String[] args) {// 这里的excel文件可以 为xls或xlsx结尾File file = new File("D:\\2.xlsx");List<List<String>> result = new ArrayList<>();try {result = EasyExcelUtil.writeWithoutHead(new FileInputStream(file));} catch (FileNotFoundException e) {e.printStackTrace();}System.out.println("读取结果：" + result);}}
