今天工作中又遇到了excel导出数据的功能，在这里我将自己自定义的Excel模板和相关的代码拿出来，解释一下如何进行操作，以便日后能够方便的进行功能开发，提高效率。
导出表单数据
一、引入maven依赖
<!-- easypoi的支持 --> <dependency> <groupId>cn.afterturn</groupId> <artifactId>easypoi-base</artifactId> <version>4.2.0</version> </dependency> <dependency> <groupId>cn.afterturn</groupId> <artifactId>easypoi-web</artifactId> <version>4.2.0</version> </dependency> <dependency> <groupId>cn.afterturn</groupId> <artifactId>easypoi-annotation</artifactId> <version>4.2.0</version> </dependency>
二、编写业务逻辑代码
package com.platform.modules.yc.service.impl; import cn.afterturn.easypoi.cache.manager.FileLoaderImpl; import cn.afterturn.easypoi.cache.manager.POICacheManager; import cn.afterturn.easypoi.excel.ExcelExportUtil; import cn.afterturn.easypoi.excel.entity.TemplateExportParams; import cn.afterturn.easypoi.view.EasypoiTemplateExcelView; import com.baomidou.mybatisplus.extension.plugins.pagination.Page; import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl; import com.platform.modules.yc.dao.Dao; import com.platform.modules.yc.entity.Entity; import com.platform.modules.yc.service.ExcelService; import org.apache.poi.ss.usermodel.*; import org.apache.poi.ss.util.CellRangeAddress; import org.apache.poi.ss.util.CellUtil; import org.apache.poi.ss.usermodel.CellStyle; import org.apache.poi.ss.usermodel.Row; import org.springframework.stereotype.Service; import javax.servlet.http.HttpServletRequest; import javax.servlet.http.HttpServletResponse; import java.io.FileOutputStream; import java.text.SimpleDateFormat; import java.time.LocalDate; import java.time.format.DateTimeFormatter; import java.util.ArrayList; import java.util.HashMap; import java.util.List; import java.util.Map; /** * @author ：zjc * @ProjectName: platform-plus * @Package: com.platform.modules.yc.service.impl * @ClassName: ExcelServiceImpl * @date ：Created in 2021/4/12 15:48 * @description：excel导出 * @modified By： * @version: 1.0.0$ */ @Service("ExcelService") public class ExcelServiceImpl implements ExcelService { private void setRegionStyle(Sheet sheet, CellRangeAddress region, CellStyle cs) { for (int i = region.getFirstRow(); i <= region.getLastRow(); i++) { Row row = CellUtil.getRow(i, sheet); for (int j = region.getFirstColumn(); j <= region.getLastColumn(); j++) { Cell cell = CellUtil.getCell(row, (short) j); cell.setCellStyle(cs); } } } private void addMergeRow(Sheet sheet, int rowIndex, String content, CellStyle cellStyle) { cellStyle.setLeftBorderColor((short) 8); cellStyle.setRightBorderColor((short) 8); cellStyle.setTopBorderColor((short) 8); cellStyle.setBottomBorderColor((short) 8); cellStyle.setBorderLeft(BorderStyle.THIN); cellStyle.setBorderRight(BorderStyle.THIN); cellStyle.setBorderTop(BorderStyle.THIN); cellStyle.setBorderBottom(BorderStyle.THIN); Row newRow = sheet.createRow(rowIndex); newRow.setHeight((short) 955); Cell cell = newRow.createCell(0); cell.setCellValue(content); cell.setCellStyle(cellStyle); int lastCellNum = 55; for (int i = 1; i < lastCellNum; i++) { newRow.createCell(i).setCellStyle(cellStyle); } // 合并单元格 CellRangeAddress cellAddresses = new CellRangeAddress(rowIndex, rowIndex, 0, lastCellNum); sheet.addMergedRegion(cellAddresses); setRegionStyle(sheet, cellAddresses, cellStyle); } @Override public void remoteSensAmountExcel(List<Entity> params,HttpServletRequest request, HttpServletResponse response) { // String type = (String)params.getMap().getOrDefault("type", ExcelType.XSSF.name()); String sheetName = "数量查询";// (String)params.getMap().getOrDefault("sheetName", "SheetName"); String fileName = "数量查询.xls"; Map<String, Object> excelValue = new HashMap<String, Object>(); DateTimeFormatter dtf = DateTimeFormatter.ofPattern("yyyy-MM-dd"); SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); excelValue.put("title", "数量查询"); excelValue.put("siteName", "监测系统"); excelValue.put("date", LocalDate.parse(dtf.format(LocalDate.now()), dtf)); List<Map<String, Object>> valList = new ArrayList<Map<String, Object>>(); List<Map<String, Object>> valListData = new ArrayList<>(); Map<String, Object> mapv = new HashMap<>(); Entity mapvalue = new Entity(); int rowIndex = 5; for (int i = 0; i < params.size(); i++) { mapvalue = params.get(i); mapv = new HashMap<>(); mapv.put("id", mapvalue.getId()); mapv.put("tstime", simpleDateFormat.format(mapvalue.getTstime())); mapv.put("ycz", "xxx"); mapv.put("cphm", mapvalue.getHphm()); mapv.put("cdh", mapvalue.getCdxh()); mapv.put("cpys", mapvalue.getCpys()); mapv.put("rlzl", mapvalue.getRlzl()); mapv.put("pdjg", mapvalue.getJudge()); mapv.put("cojg", mapvalue.getCojg()); mapv.put("co2jg", mapvalue.getCo2jg()); mapv.put("hcjg", mapvalue.getHcjg()); mapv.put("nojg", mapvalue.getNojg()); valListData.add(mapv); rowIndex++; } excelValue.put("valList", valListData); //excelValue.put("valList", valList); // 模板路径 String templateFile = ExcelServiceImpl.class.getClassLoader().getResource("doc/hour001.xls").getPath(); TemplateExportParams exportTemplateParams = new TemplateExportParams( templateFile, sheetName); exportTemplateParams.setColForEach(true); POICacheManager.setFileLoader(new FileLoaderImpl()); /* 采用另一种导出方式 注意模板格式和导出的文件格式为xlsx Map<String, Object> map = new HashMap<>(5); map.put("map", excelValue); map.put(NormalExcelConstants.PARAMS, exportTemplateParams); map.put(NormalExcelConstants.FILE_NAME, fileName); PoiBaseView.render(map, request, response, "easypoiTemplateExcelView");*/ Workbook book = ExcelExportUtil.exportExcel(exportTemplateParams, excelValue); Sheet sheet = book.getSheetAt(0); Row head = sheet.getRow(1); Cell headOne = head.getCell(0); CellStyle cellStyle = headOne.getCellStyle(); //以下代码是自定义单元格所显示的样式设置 String content="上传数据总数:15 缺失数据总数:2 数据完整率86.67%\t"; addMergeRow(sheet, rowIndex, content, cellStyle); rowIndex++; content="制表人: 审核人: 审核时间: "; addMergeRow( sheet, rowIndex, content, cellStyle); /* 以下该部分代码将文件路径指定到了本地磁盘 FileOutputStream fos = new FileOutputStream("D:\\down-info\\easypoi-test\\src\\test\\resources\\doc\\bb.xls"); book.write(fos); fos.close();*/ /* *以下该代码可以采用postman或者是直接在浏览器请求，直接以一个文件的形式下载下来 * */ EasypoiTemplateExcelView easypoiTemplateExcelView = new EasypoiTemplateExcelView(); try { easypoiTemplateExcelView.out( book, fileName, request, response ); } catch (Exception ex) { } } }
三、写适合自己的模板
因为指定的模板路径为实现类文件夹下，所以在实现类所对应模块的resources文件夹里新建了一个doc,然后里面放我们的模板。模板的定义如下：
一定要注意红色标出来的花括号，否则显示不出来对应的数据值。
四、 打jar包后找不到导出模板的解决方法
Jar包导出无法读取Excel模板，在本地运行可以，但是到了服务器就会报workbook的空指针异常。
主要原因有两点：
1.Linux和Window文件系统环境不一致。
2.springBoot项目打jar包后文件资源加载方式不同，打包后Spring试图访问文件系统路径，但无法访问jar中的路径。
解决方案如下：
采用 resource.getInputStream() 获取模板的文档流，重写到tomcat容器中并生成新的模板路径，按新的路径，导出excel即可。
//Workbook book = ExcelExportUtil.exportExcel(exportTemplateParams, excelValue); //改成如下： String templateFile = ExcelExportUtils.convertTemplatePath("doc/hour001.xls"); //InputStream templateFile = EmergencyMaterialInputServiceImpl.class.getClassLoader().getResourceAsStream("doc/hour001.xls"); TemplateExportParams exportTemplateParams = new TemplateExportParams( templateFile, sheetName); exportTemplateParams.setColForEach(true); POICacheManager.setFileLoader(new FileLoaderImpl()); Workbook book = ExcelExportUtil.exportExcel(exportTemplateParams, excelValue);
附代码：
import org.springframework.core.io.ClassPathResource; import org.springframework.core.io.Resource; import org.springframework.stereotype.Component; import java.io.BufferedInputStream; import java.io.File; import java.io.FileOutputStream; import java.io.IOException; /** * @Author zjc * @Description Excel导出方法 */ @Component public class ExcelExportUtils { /** * 模板路径处理，避免出现NPE * windows和linux系统不同，且SpringBoot的文件加载方式已有不同，不能简单的使用相对路径 * * @param path excel模板的相对路径 * @return 重写后的模板路径 */ public static String convertTemplatePath(String path) { // 如果是windows则直接返回 if (System.getProperties().getProperty("os.name").contains("Windows")) { return path; } Resource resource = new ClassPathResource(path); FileOutputStream fileOutputStream = null; // 将模版文件写入到tomcat临时目录 String folder = System.getProperty("catalina.home"); File tempFile = new File(folder + File.separator + path); // 文件存在时不再写入 if (tempFile.exists()) { return tempFile.getPath(); } File parentFile = tempFile.getParentFile(); // 判断父文件夹是否存在 if (!parentFile.exists()) { parentFile.mkdirs(); } try { BufferedInputStream inputStream = new BufferedInputStream(resource.getInputStream()); fileOutputStream = new FileOutputStream(tempFile); byte[] buffer = new byte[10240]; int len = 0; while ((len = inputStream.read(buffer)) != -1) { fileOutputStream.write(buffer, 0, len); } } catch (IOException e) { e.printStackTrace(); } finally { if (fileOutputStream != null) { try { fileOutputStream.close(); } catch (IOException e) { e.printStackTrace(); } } } return tempFile.getPath(); } }
导入表单数据
一、导入Controller
@ApiOperation(value = "批量导入应急物资数据") @RequestMapping(value = "/upload", method = RequestMethod.POST) // @PreAuth("hasPermit('{}view')") public R upload(@RequestParam("file") MultipartFile multipartFile, @ApiIgnore @LoginUser SysUser user) throws Exception { ImportParams params = new ImportParams(); params.setHeadRows(1); params.setTitleRows(2); List<EmergencyMaterialInputEntity> factors = ExcelImportUtil.importExcel(multipartFile.getInputStream(), EmergencyMaterialInputEntity.class, params); EmergencyMaterialInputSaveDTO dto = new EmergencyMaterialInputSaveDTO(); // this.setDTO(dto, user); emergencyMaterialInputService.upload(factors, dto); return R.success(); }
二、ServiceImpl层
public void upload(List<EmergencyMaterialInputEntity> factors, EmergencyMaterialInputSaveDTO dto) { List<EmergencyMaterialInput> emergencyMaterialInputs = new ArrayList<>(); List<Map<String, Object>> stringList = baseMapper.selectManagerCode(); for (EmergencyMaterialInputEntity factor : factors) { if (factor.getMaterialName() != null && !"".equals(factor.getMaterialName()) && factor.getMaterialType() != null && !"".equals(factor.getMaterialType())) { dto.setMaterialName(factor.getMaterialName() != null ? factor.getMaterialName() : ""); dto.setMaterialType(factor.getMaterialType() != null ? factor.getMaterialType() : ""); dto.setMaterialCount(factor.getMaterialCount() != null ? factor.getMaterialCount() : 0); dto.setMaterialUnit(factor.getMaterialUnit() != null ? factor.getMaterialUnit() : ""); //根据用户名查询对应负责人编号 for (Map<String, Object> map : stringList) { if (factor.getManager() != null) { if (factor.getManager().equals(map.get("manager"))) { dto.setManagerCode(map.get("manager_code").toString()); } } } dto.setInputTime(factor.getInputTime() != null ? factor.getInputTime() : LocalDateTime.now()); dto.setValidityDate(factor.getValidityDate() != null ? factor.getValidityDate() : LocalDate.now()); EmergencyMaterialInput model = BeanUtil.toBean(dto, getEntityClass()); emergencyMaterialInputs.add(model); } } this.saveBatch(emergencyMaterialInputs); }
下载模板
public void downloadModel(Map params, HttpServletRequest request, HttpServletResponse response) { String model = params.get("model").toString(); // String templateFile = EnvironmentJsonServiceImpl.class.getClassLoader().getResource("doc/"+model+".xlsx") // .getPath(); try { Resource resource = new ClassPathResource("doc/" + model + ".xls"); // File file = resource.getFile(); String filename = resource.getFilename(); InputStream inputStream = resource.getInputStream(); response.setHeader("content-type", "application/octet-stream;charset=UTF-8"); response.setContentType("application/octet-stream;charset=UTF-8"); // response.addHeader("Content-Length", String.valueOf(file.length())); OutputStream out = response.getOutputStream(); //使用URLEncoder来防止文件名乱码或者读取错误 response.setHeader("Content-Disposition", "attachment; filename=" + URLEncoder.encode(filename, "UTF-8")); int b = 0; byte[] buffer = new byte[1000000]; while (b != -1) { b = inputStream.read(buffer); if (b != -1) { out.write(buffer, 0, b); } } inputStream.close(); out.close(); out.flush(); } catch (Exception e) { e.printStackTrace(); } }
传入的map为json形式的模板名称，对应的doc地址在项目的resource/doc目录下。
