下载模板Excel、读取用户上传的Excel文件、生成Excel文件是常见的业务需求。针对以上需求,本文
- 定义一个通用方法,实现对Excel模板文件下载。
- 介绍了如何使用阿里的开源工具 easyexcel,解析Excel文件。
Excel模板的下载
系统中上传Excel的功能往往附带一个下载Excel模板功能。这种Excel模板往往是固定的,不随用户、操作等条件的变化而变。对于这种Excel模板的下载,可以通过以下两种方式实现:
- 每次调用下载的接口时,都生成一份Excel模板文件,然后供用户下载。
- 将Excel模板放在指定路径下,每次调用下载的接口时直接使用对应的Excel模板文件。
两种方式都能实现Excel的下载,但是第一种方式针对每种模板都要写大段的生成Excel代码,十分繁琐。如果采用第二种方式,只需要一个接口,就可以搞定所有固定不变的Excel模板下载。Excel文件放置在”WebContent\static\excel”路径下。相关代码如下:
- 下载Excel模板接口:
1 2 3 4 5 6 7 8 9 10 11 12 13 | /** * 下载Excel模板 * @param request request * @param response response * @throws IOException IOException */ @RequestMapping(value = "/template/download", method = RequestMethod.POST) public void downloadTemplate (HttpServletRequest request, HttpServletResponse response) throws IOException { String filename = request.getParameter("filename"); String path = request.getSession().getServletContext().getRealPath("/")+"static/excel/"; FileUtil.download(filename,path,request,response); } |
- 下载文件工具类:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 | import javax.servlet.ServletException; import javax.servlet.http.HttpServletRequest; import javax.servlet.http.HttpServletResponse; import java.io.*; /** * 下载文件工具类 * * @author lurunze * @date 2019/04/22 */ public class FileUtil { /** * 下载文件工具 * * @param fileName 文件名 * @param path 文件路径 * @param response 响应 * @param file 文件 * @throws IOException io异常 */ public static void download (String fileName, String path, HttpServletRequest request, HttpServletResponse response) throws IOException { File file = new File(path + fileName); if (file.exists()) { response.setContentType("application/x-msdownload"); response.setHeader("Content-disposition", "attachment; filename=" + new String(fileName.getBytes("utf-8"), "ISO8859-1")); InputStream inputStream = new FileInputStream(file); OutputStream outputStream = response.getOutputStream(); byte[] b = new byte[2014]; int n; while((n=inputStream.read(b)) != -1){ outputStream.write(b, 0, n); } outputStream.close(); inputStream.close(); } else { throw new RuntimeException("找不到文件:" + fileName); } } } |
解析Excel
easyexcel简介
Java解析、生成Excel比较有名的框架有Apache poi、jxl。但他们都存在一个严重的问题就是非常的耗内存,poi有一套SAX模式的API可以一定程度的解决一些内存溢出的问题,但POI还是有一些缺陷,比如07版Excel解压缩以及解压后存储都是在内存中完成的,内存消耗依然很大。easyexcel重写了poi对07版Excel的解析,能够原本一个3M的excel用POI sax依然需要100M左右内存降低到KB级别,并且再大的excel不会出现内存溢出,03版依赖POI的sax模式。在上层做了模型转换的封装,让使用者更加简单方便。
easyexcel1.0.2解析excel
目前项目里用的是easyexcel1.0.2,在这里介绍无模型读取Excel的用法。
编写监听类,该类用于返回读取到的数据
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 | import com.alibaba.excel.read.context.AnalysisContext; import com.alibaba.excel.read.event.AnalysisEventListener; import java.util.ArrayList; import java.util.List; /** * 解析监听器, * 每解析一行会回调invoke()方法。 * 整个excel解析结束会执行doAfterAllAnalysed()方法 * * @author lurunze * @date 2019/04/22 */ public class ExcelListener extends AnalysisEventListener { /** * 自定义用于暂时存储data。 * 可以通过实例获取该值 */ private List<Object> datas = new ArrayList<>(); private List<List<String>> excelDatas = new ArrayList<>(); @Override public void invoke(Object object, AnalysisContext context) { datas.add(object); } @Override public void doAfterAllAnalysed(AnalysisContext context) { } public List<List<String>> getDatas() { for (Object object : datas) { excelDatas.add((List<String>) object); } return excelDatas; } public void setDatas(List<Object> datas) { this.datas = datas; } } |
编写工具类
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 | import com.alibaba.excel.ExcelReader; import com.alibaba.excel.ExcelWriter; import com.alibaba.excel.metadata.Sheet; import com.alibaba.excel.support.ExcelTypeEnum; import org.springframework.util.Assert; import java.io.*; import java.util.List; /** * Excel工具类 * * @author lurunze * @date 2019/04/22 */ public class ExcelUtil { /** * 获取Excel文件类型 * * @param fileType 文件后缀 * @return ExcelTypeEnum */ public static ExcelTypeEnum getExcelTypeEnum(String fileType) { Assert.isTrue(ExcelTypeEnum.XLS.getValue().equals(fileType) || ExcelTypeEnum.XLSX.getValue().equals(fileType) , "文件格式不正确,请选择.xlxs或.xls格式文件!"); return ExcelTypeEnum.valueOf(fileType); } /** * 读取Excel文件数据 * * @param inputStream 文件输入流 * @return Excel文件数据 */ public static List<List<String>> readExcel(InputStream inputStream, ExcelTypeEnum excelTypeEnum) { List<List<String>> datas; try { ExcelListener listener = new ExcelListener(); ExcelReader excelReader = new ExcelReader(inputStream, excelTypeEnum, null, listener); excelReader.read(); datas = listener.getDatas(); } catch (Exception e) { throw new RuntimeException(e.getMessage()); } finally { try { inputStream.close(); } catch (IOException e) { throw new RuntimeException(e.getMessage()); } } return datas; } /** * 写Excel文件 * * @param excelTypeEnum excel版本 * @param fileName 包含文件路径的文件名 * @param sheetName sheet名 * @param datas 文件数据(包含表头) */ public static void writeExcel(ExcelTypeEnum excelTypeEnum, String fileName, String sheetName, List<List<String>> datas) throws FileNotFoundException { OutputStream out = new FileOutputStream(fileName); try { ExcelWriter writer = new ExcelWriter(out, excelTypeEnum, false); //写第一个sheet, sheet1 数据全是List<String> 无模型映射关系 Sheet sheet1 = new Sheet(1, 0); sheet1.setSheetName(sheetName); writer.write0(datas, sheet1); writer.finish(); } catch (Exception e) { e.printStackTrace(); } finally { try { out.close(); } catch (IOException e) { e.printStackTrace(); } } } } |
easyexcel1.1.2-beta4解析excel
新版的easyexcel新增了一些接口,可以不用写ExcelListener就能读取Excel。同时,写Excel也多了一些多样式设定的方法。
工具类
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 | /** * Excel读写工具类 * * @author lurunze * @date 2019/05/02 */ public class ExcelUtil { //ReadMethod(); //writeMethod{}; /** * Excel参数 */ @Data static class ExcelParameter { /** * 工作表编号 */ private Integer sheetNo; /** * 标题占用行数 */ private Integer headLineMun; /** * 工作表名称 */ private String sheetName; /** * 表头 */ private List<List<String>> listStringHead; /** * 宽度 */ private Map<Integer, Integer> columnWidth; /** * 合并单元格参数 */ private List<MergeParameter> mergeParameterList; } /** * 单元格合并参数 */ @Data private static class MergeParameter { /** * 起始行 */ private Integer firstRow; /** * 结束行 */ private Integer lastRow; /** * 起始列 */ private Integer firstCol; /** * 结束列 */ private Integer lastCol; } } |
无模型读取Excel
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 | /** * excel读数据量少于1千行数据,内部采用回调方法. * (经测试,大于1千行也是可以正常读取的) * * @param inputStream 输入流 * @param sheetNo 工作表编号 * @param headLineMun 标题占用行数 */ public static List<Object> simpleReadListString(InputStream inputStream, int sheetNo, int headLineMun) { List<Object> data = null; try { data = EasyExcelFactory.read(inputStream, new Sheet(sheetNo, headLineMun)); } catch (Exception e) { e.printStackTrace(); } finally { try { inputStream.close(); } catch (IOException e) { e.printStackTrace(); } } return data; } |
有模型读取Excel
有模型读取Excel的方式需要针对不同的Excel创建模型类。
Excel样式
创建模型类
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | @Data public class ReadUser extends BaseRowModel { @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; } |
读取Excel
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 | /** * excel读数据量少于1千行数据自动转成javamodel,内部采用回调方法. * (经测试,大于1千行也是可以正常读取的) * * @param inputStream 输入流 * @param sheetNo 工作表编号 * @param headLineMun 标题占用行数 */ public static List<Object> simpleReadJavaModel(InputStream inputStream, int sheetNo, int headLineMun, Class<? extends BaseRowModel> clazz) { List<Object> data = null; try { data = EasyExcelFactory.read(inputStream, new Sheet(sheetNo, headLineMun, clazz)); inputStream.close(); } catch (IOException e) { e.printStackTrace(); } finally { try { inputStream.close(); } catch (IOException e) { e.printStackTrace(); } } return data; } |
虽然官方文档中说这两种方法读数据量少于1千行,但是经测试,大于1千行也是可以正常读取的。
无模型写Excel
当前版本ExcelWriter.write1()方法有bug,表头会纵向排列。如果想用无模型写Excel,可以通过前文介绍的Excel.write0()方法。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 | /** * 无模板写excel(当前版本有bug) * * @param outputStream 输出流 * @param parameter Excel相关参数 * @param data Excel数据 */ public static void writeExcel(OutputStream outputStream, ExcelParameter parameter, List<List<Object>> data) { try { ExcelWriter writer = EasyExcelFactory.getWriter(outputStream); //写第一个sheet, sheet1 数据全是List<String> 无模型映射关系 Sheet sheet = new Sheet(parameter.sheetNo, parameter.headLineMun); sheet.setSheetName(parameter.sheetName); if (parameter.listStringHead != null && parameter.listStringHead.size() > 0) { sheet.setHead(parameter.listStringHead); } //设置列宽 设置每列的宽度 if (parameter.columnWidth != null && parameter.columnWidth.size() > 0) { sheet.setColumnWidthMap(parameter.columnWidth); } else { // 设置自适应宽度 sheet.setAutoWidth(Boolean.TRUE); } writer.write1(data, sheet); //合并单元格 if (parameter.getMergeParameterList() != null && parameter.getMergeParameterList().size() > 0) { for (MergeParameter mergeParameter : parameter.getMergeParameterList()) { writer.merge(mergeParameter.firstRow, mergeParameter.lastRow, mergeParameter.firstCol, mergeParameter.lastCol); } } writer.finish(); } finally { try { outputStream.close(); } catch (IOException e) { e.printStackTrace(); } } } |
有模型写Excel
模型类
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 | @Data public class WriteUser extends BaseRowModel { @ExcelProperty(value = "姓名" ,index = 0) private String name; @ExcelProperty(value = "年龄",index = 1) private String age; @ExcelProperty(value = "邮箱",index = 2) private String email; @ExcelProperty(value = "地址",index = 3) private String address; @ExcelProperty(value = "性别",index = 4) private String sax; @ExcelProperty(value = "高度",index = 5) private String heigh; @ExcelProperty(value = "备注",index = 6) private String last; } |
写Excel
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 | /** * 有模板写excel * * @param outputStream 输出流 * @param parameter Excel相关参数 * @param data Excel数据 */ public static void writeExcelWithTemplate(OutputStream outputStream, ExcelParameter parameter, List<? extends BaseRowModel> data) { try { ExcelWriter writer = EasyExcelFactory.getWriter(outputStream); Class t; if (data != null && data.size() > 0) { t = data.get(0).getClass(); } else { throw new RuntimeException("文件数据为空"); } Sheet sheet = new Sheet(parameter.sheetNo, parameter.headLineMun, t, parameter.sheetName, null); //设置列宽 设置每列的宽度 if (parameter.columnWidth != null && parameter.columnWidth.size() > 0) { sheet.setColumnWidthMap(parameter.columnWidth); } else { // 设置自适应宽度 sheet.setAutoWidth(Boolean.TRUE); } writer.write(data, sheet); //合并单元格 if (parameter.getMergeParameterList() != null && parameter.getMergeParameterList().size() > 0) { for (MergeParameter mergeParameter : parameter.getMergeParameterList()) { writer.merge(mergeParameter.firstRow, mergeParameter.lastRow, mergeParameter.firstCol, mergeParameter.lastCol); } } writer.finish(); } catch (RuntimeException e) { e.printStackTrace(); } finally { try { outputStream.close(); } catch (IOException e) { e.printStackTrace(); } } } |
生成的Excel
写多表头Excel
模型类
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 | public class MultiLineHeadExcelModel extends BaseRowModel { @ExcelProperty(value = {"表头1","表头1","表头31"},index = 0) private String p1; @ExcelProperty(value = {"表头1","表头1","表头32"},index = 1) private String p2; @ExcelProperty(value = {"表头3","表头3","表头3"},index = 2) private int p3; @ExcelProperty(value = {"表头4","表头4","表头4"},index = 3) private long p4; @ExcelProperty(value = {"表头5","表头51","表头52"},index = 4) private String p5; @ExcelProperty(value = {"表头6","表头61","表头611"},index = 5) private String p6; @ExcelProperty(value = {"表头6","表头61","表头612"},index = 6) private String p7; @ExcelProperty(value = {"表头6","表头62","表头621"},index = 7) private String p8; @ExcelProperty(value = {"表头6","表头62","表头622"},index = 8) private String p9; } |
生成的Excel