简介:
为什么用 JXLS 主要是生成精美的Excel格式报表 ，并且可以处理复杂报表，只需要设计出 华丽的excel 渲染交给JXLS 谁用谁知道
工作流程:1.导入依赖
2.定义Excel模板 批注
3.模板放到项目中
4.controller控制层 导出 实现
5.controller 控制层 导入 实现
6. 定义导入数据的xml模板
1. maven依赖
<!-- JXLS导出导入读取excel报表 JXLS 核心--> <!-- https://mvnrepository.com/artifact/org.jxls/jxls --> <dependency> <groupId>org.jxls</groupId> <artifactId>jxls</artifactId> <version>2.8.1</version> </dependency> <!-- https://mvnrepository.com/artifact/org.jxls/jxls-poi --> <dependency> <groupId>org.jxls</groupId> <artifactId>jxls-poi</artifactId> <version>2.8.1</version> </dependency> <!-- https://mvnrepository.com/artifact/org.jxls/jxls-jexcel --> <dependency> <groupId>org.jxls</groupId> <artifactId>jxls-jexcel</artifactId> <version>1.0.9</version> </dependency> <!-- https://mvnrepository.com/artifact/org.jxls/jxls-reader --> <dependency> <groupId>org.jxls</groupId> <artifactId>jxls-reader</artifactId> <version>2.0.6</version> </dependency>
2. 定义Excel模板 批注
批注一
批注二
1. 模板函数: jx:area(lastCell="B3")
参数B3 就是我们单元格 循环 的边界范围
2. 模板函数: jx:each(items="statusQuoPlan" var="AftersalesFeedbackImage" lastCell="B3")
3. 表达式最基本的语法是使用${和}
AftersalesFeedbackImage 作为 key 加上数据库 对应字段 就是如下↓
${AftersalesFeedbackImage.feedbackImageurl}
3.模板放到项目中
4. controller 控制层 导出
/** * 导出Excel数据 * @param response * @throws Exception */ @ApiOperation(value = "导出Excel数据") @RequestMapping(value = "/export" , method = RequestMethod.POST, produces = "application/json") public void export(HttpServletResponse response) throws Exception{ ByteArrayOutputStream baos = null; OutputStream out = null; try { // excel数据 实际运用从数据库中查询 List<AftersalesFeedbackImage> statusQuoPlan = new ArrayList<AftersalesFeedbackImage>(); AftersalesFeedbackImage data = new AftersalesFeedbackImage(); data.setFeedbackImageurl("ssss"); data.setFeedbackRecordId("测试测试"); statusQuoPlan.add(data); if (null != statusQuoPlan && statusQuoPlan.size() > 0) { Map<String, Object> mapData = new HashMap<>(); // statusQuoPlan与批注表达式中的items=”statusQuoPlan”相同 mapData.put("statusQuoPlan", statusQuoPlan); //模板存放位置 baos = JXLSExcelUtil.export("template/Templates.xlsx", mapData); //下载的模板名称 DownloadUtil.generalDownload(response, baos, "TemplatesNew.xlsx"); } } catch (Exception e) { e.printStackTrace(); throw new Exception("导出失败：" + e.getMessage()); } finally { if(baos != null){ baos.close(); } if(out != null){ out.close(); } } }
下载工具类
/** * 下载工具类 */ public class DownloadUtil { /** * 通用下载设置 * @param response * @param outData 输出数据 ByteArrayOutputStream * @param fileName 文件名 * @throws Exception */ public static void generalDownload(HttpServletResponse response, ByteArrayOutputStream outData, String fileName) throws Exception{ response.setContentType( "application/x-msdownload"); response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8")); ServletOutputStream out = response.getOutputStream(); outData.writeTo(out); out.flush(); out.close(); } /** * 通用下载设置 * @param response * @param outData 输出数据 InputStream * @param fileName 文件名 * @throws Exception */ public static void generalDownload(HttpServletResponse response, InputStream outData, String fileName) throws Exception{ response.setContentType( "application/x-msdownload"); response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8")); ServletOutputStream out = response.getOutputStream(); byte[] bffer = new byte[1024]; int r = outData.read(bffer, 0, 1024); while (r != -1) { out.write(bffer); r = outData.read(bffer, 0, 1024); } out.flush(); out.close(); } /** * 通用下载设置 * @param response * @param outData 输出数据 byte[] * @param fileName 文件名 * @throws Exception */ public static void generalDownload(HttpServletResponse response, byte[] outData, String fileName) throws Exception{ response.setContentType( "application/x-msdownload"); response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8")); ServletOutputStream out = response.getOutputStream(); out.write(outData); out.flush(); out.close(); } }
导出Excel工具类
/** * JXLS Excel 工具类 */ public class JXLSExcelUtil { /** * 导出 Excle * @param templateRelativePathAndName 模板相对路径和文件名 * @param data Excle模板数据 * @return * @throws Exception */ public static ByteArrayOutputStream export(String templateRelativePathAndName, Map<String, Object> data) throws Exception { ByteArrayOutputStream out = new ByteArrayOutputStream(); InputStream in = JXLSExcelUtil.class.getClassLoader().getResourceAsStream(templateRelativePathAndName); if (in == null) { throw new Exception("模板文件未找到：" + templateRelativePathAndName); } Context context = new Context(); for (Entry<String, Object> d : data.entrySet()) { if (d.getKey() != null && d.getValue() != null) { context.putVar(d.getKey(), d.getValue()); } } JxlsHelper.getInstance().processTemplate(in, out, context); return out; } /** * 读取Excle数据到bean * @param readConfigXmlRelativePathAndName 读取配置文件 * @param inputExcelStream 输入的excle inputSream * @param beans 要封装数据的bean * @return * @throws Exception */ public static boolean readExcelData(String readConfigXmlRelativePathAndName, InputStream inputExcelStream, Map<String, Object> beans) throws Exception { InputStream in = JXLSExcelUtil.class.getClassLoader().getResourceAsStream(readConfigXmlRelativePathAndName); if (in == null) { throw new Exception("配置文件未找到：" + readConfigXmlRelativePathAndName); } InputStream inputXML = new BufferedInputStream(in); XLSReader reader = ReaderBuilder.buildFromXML(inputXML); XLSReadStatus readStatus = reader.read(inputExcelStream, beans); return readStatus.isStatusOK(); } /** * 读取Excle数据到bean * @param readConfigXmlRelativePathAndName 读取配置文件 * @param file 输入的excle文件 * @param beans 要封装数据的bean * @return * @throws Exception */ public static boolean readExcelData(String readConfigXmlRelativePathAndName, File file, Map<String, Object> beans) throws Exception { if (file == null) { throw new Exception("Excel文件为空"); } InputStream inputExcelStream = new FileInputStream(file); return readExcelData(readConfigXmlRelativePathAndName, inputExcelStream, beans); } }
5. controller 控制层 导入
/** * 导入Excel数据 * @param file */ @ApiOperation(value = "导入Excel数据") @RequestMapping(value = "/imports", method = RequestMethod.POST) public void importExcel(MultipartFile file) { File f = null; try { // MultipartFile 转 file f = FileUtil.multipartFileToFile(file); List<AftersalesFeedbackImage> statusQuoPlan = new ArrayList<>(); Map<String, Object> beans = new HashMap<>(); beans.put("statusQuoPlan", statusQuoPlan); // dataList 来自配置文件readExcelConfig.xml的items=”dataList” JXLSExcelUtil.readExcelData("jxls/texttemplate.xml", f , beans); for (AftersalesFeedbackImage data : statusQuoPlan) { System.out.println(data.toString()); } } catch (Exception e) { e.printStackTrace(); } finally { if (f != null) { f.delete(); } } }
6. 定义导入数据的xml模板
<?xml version="1.0" encoding="UTF-8"?> <workbook> <!-- ①指定读取哪一个sheet： name="测试" ② 如果只是读取第一个的话，可使用： idx="0" --> <worksheet name="Sheet1"> <!--表头开始至结束行--> <section startRow="0" endRow="0"> </section> <!--开始循环读取文件数据，配置开始行，items映射的list var映射的bean varType 类路径 startRow：开始循环的行数 endRow-startRow：循环体的大小，0代表一行，依次论推 每循环一次，判断是否结束，不结束继续循环，直至结束 --> <loop startRow="1" endRow="1" items="statusQuoPlan" var="AftersalesFeedbackImage" varType="com.lt.crm.entity.AftersalesFeedbackImage"> <!--循环开始行 如果: endRow = "1" 执行到空白行结束--> <section startRow="1" endRow="1"> <!--循环中每一次的节点属性配置--> <mapping cell="A3">AftersalesFeedbackImage.feedbackImageurl</mapping> <mapping cell="B3">AftersalesFeedbackImage.feedbackRecordId</mapping> </section> <!--结束条件配置--> <loopbreakcondition> <rowcheck offset="0"/> </loopbreakcondition> </loop> </worksheet> <worksheet name="Sheet2"> <section startRow="0" endRow="0"> </section> <loop startRow="1" endRow="1" items="statusQuoPlan" var="AftersalesFeedbackImage" varType="com.lt.crm.entity.AftersalesFeedbackImage"> <section startRow="1" endRow="1"> <mapping cell="A3">AftersalesFeedbackImage.feedbackImageurl</mapping> <mapping cell="B3">AftersalesFeedbackImage.feedbackRecordId</mapping> </section> <loopbreakcondition> <rowcheck offset="0"/> </loopbreakcondition> </loop> </worksheet> <worksheet name="3当前为第三个sheet页"> <section startRow="0" endRow="0"> </section> <loop startRow="1" endRow="1" items="statusQuoPlan" var="AftersalesFeedbackImage" varType="com.lt.crm.entity.AftersalesFeedbackImage"> <section startRow="1" endRow="1"> <mapping cell="A3">AftersalesFeedbackImage.feedbackImageurl</mapping> <mapping cell="B3">AftersalesFeedbackImage.feedbackRecordId</mapping> </section> <loopbreakcondition> <rowcheck offset="0"/> </loopbreakcondition> </loop> </worksheet> </workbook>
导入的数据模板 每个sheet也都有数据
wagger 接口文档测试
控制台结果如下:
