Java导出分类到Excel

需求

在一般需求中点击导出按钮可以把所有的分类导出到Excel文件中。

技术方案

使用EasyExcel实现Excel的导出操作。
https://github.com/alibaba/easyexcel
https://easyexcel.opensource.alibaba.com/docs/current/quickstart/write#%E7%A4%BA%E4%BE%8B%E4%BB%A3%E7%A0%81-1

web中的写并且失败的时候返回json

    /*** 文件下载并且失败的时候返回json（默认失败了会返回一个有部分数据的Excel）** @since 2.1.1*/@GetMapping("downloadFailedUsingJson")public void downloadFailedUsingJson(HttpServletResponse response) throws IOException {// 这里注意 有同学反应使用swagger 会导致各种问题，请直接用浏览器或者用postmantry {response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");response.setCharacterEncoding("utf-8");// 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系String fileName = URLEncoder.encode("测试", "UTF-8").replaceAll("\\+", "%20");response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");// 这里需要设置不关闭流EasyExcel.write(response.getOutputStream(), DownloadData.class).autoCloseStream(Boolean.FALSE).sheet("模板").doWrite(data());} catch (Exception e) {// 重置responseresponse.reset();response.setContentType("application/json");response.setCharacterEncoding("utf-8");Map<String, String> map = MapUtils.newHashMap();map.put("status", "failure");map.put("message", "下载文件失败" + e.getMessage());response.getWriter().println(JSON.toJSONString(map));}}

接口设计

请求方式 请求地址 请求头
GET /content/category/export 需要token请求头
CategoryController

  @GetMapping("/export")public void export(HttpServletResponse response){try {//设置下载文件的请求头WebUtils.setDownLoadHeader("分类.xlsx",response);//获取需要导出的数据List<Category> categoryVOs = categoryService.list();List<ExcelCategoryVO> excelCategoryVOs = BeanCopyUtils.copyBeanList(categoryVOs, ExcelCategoryVO.class);//把数据写入到Excel中EasyExcel.write(response.getOutputStream(), ExcelCategoryVO.class).autoCloseStream(Boolean.FALSE).sheet("分类导出").doWrite(excelCategoryVOs);} catch (Exception e) {e.printStackTrace();//如果出现异常也要响应jsonResponseResult result = ResponseResult.errorResult(AppHttpCodeEnum.SYSTEM_ERROR);WebUtils.renderString(response, JSON.toJSONString(result));}

WebUtils

public class WebUtils
{/*** 将字符串渲染到客户端* * @param response 渲染对象* @param string 待渲染的字符串* @return null*/public static void renderString(HttpServletResponse response, String string) {try{response.setStatus(200);response.setContentType("application/json");response.setCharacterEncoding("utf-8");response.getWriter().print(string);}catch (IOException e){e.printStackTrace();}}public static void setDownLoadHeader(String filename, HttpServletResponseresponse) throws UnsupportedEncodingException {response.setContentType("application/vnd.openxmlformatsofficedocument.spreadsheetml.sheet");response.setCharacterEncoding("utf-8");String fname= URLEncoder.encode(filename,"UTF-8").replaceAll("\\+","%20");response.setHeader("Content-disposition","attachment; filename="+fname);}
}

ExcelCategoryVO

@Data
@NoArgsConstructor
@AllArgsConstructor
public class ExcelCategoryVO {@ExcelProperty("分类名")private String name;//描述@ExcelProperty("描述")private String description;//状态0:正常,1禁用@ExcelProperty("状态0:正常,1禁用")private String status;
}

CategoryVO

@Data
@NoArgsConstructor
@AllArgsConstructor
public class CategoryVO {private Long id;private String name;//描述private String description;}

效果展示