layui+SpringBoot导出excel，根据excel模板导出

按步骤走

模板存在地址（很重要）你可以放在你自己的任何位置

1、在pom.xml中添加以下maven包

<!--excel工具--> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi</artifactId> <version>4.0.1</version> </dependency> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi-ooxml</artifactId> <version>4.0.1</version> </dependency>

2、创建ExportUtils工具类

package com.soa.ump.server.message; import org.apache.poi.xssf.usermodel.XSSFCell; import org.apache.poi.xssf.usermodel.XSSFRow; import org.apache.poi.xssf.usermodel.XSSFSheet; import org.apache.poi.xssf.usermodel.XSSFWorkbook; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.stereotype.Component; import javax.servlet.http.HttpServletResponse; import java.io.*; import java.math.BigDecimal; import java.net.URLEncoder; import java.util.Arrays; import java.util.List; import java.util.Map; /** * <p> * 导出管理控制器 * </p> * * @author Xing * @since 2018-07-12 */ @Component public class ExportUtils { @Autowired public ExportConfig exportConfig; /** * 导出通用方法 * * @param title 表格第一行标题 * @param templateName 模板文件名称 * @param newFileName 新生成的文件名 * @param mapList 数据集合 list中的map 必须是LinkedHashMap,按照put的先后排序 防止数据与标题头不对应 * @param digitPosition 数字索引集合，用于处理导出的表格格式为数字类型 * @param h 从表格第几行开始 0开始 * @author Xing * @date 2019-07-23 */ public void exportExcel(HttpServletResponse response, String title, String templateName, String newFileName, List<Map<String, Object>> mapList, Integer[] digitPosition, Integer h) { FileInputStream in = null; try { in = new FileInputStream(new File(this.exportConfig.getCatalogue() + templateName)); XSSFWorkbook workbook = new XSSFWorkbook(in); // 设置字体 CellStyle redStyle = workbook.createCellStyle(); HSSFFont redFont = workbook.createFont(); //颜色 redFont.setColor(Font.COLOR_RED); //设置字体大小 redFont.setFontHeightInPoints((short) 10); //字体 //redFont.setFontName("宋体"); redStyle.setFont(redFont); XSSFSheet sheet = workbook.getSheetAt(0); if (sheet != null) { XSSFRow row = sheet.getRow(0); if (row == null) { row = sheet.createRow(0); } XSSFCell cell = row.getCell(0); if (cell == null) { cell = row.createCell(0); } cell.setCellValue(title); List<Integer> integers = Arrays.asList(digitPosition);//数字格式索引 for (int i = 0; i < mapList.size(); i++) { Map<String, Object> map = mapList.get(i); Object[] values = map.values().toArray(); row = sheet.createRow(i + h); //从第三行开始 //根据excel模板格式写入数据.... for (int k = 0; k < values.length; k++) { double height= 400 ; row.setHeight((short)height); cell = row.getCell(k) == null ? row.createCell(k) : row.getCell(k); cell.setCellStyle(redStyle);//数据写入字体 if (integers.contains(k)) {//特殊标记索引特殊处理 BigDecimal money = values[k] == null ? BigDecimal.ZERO : ((BigDecimal) values[k]); cell.setCellValue(money.doubleValue()); } else { cell.setCellValue(values[k] == null ? "" : values[k].toString()); } } } } String pageName = newFileName + System.currentTimeMillis() + ".xlsx"; setResponseHeader(response, pageName); workbook.write(response.getOutputStream()); response.getOutputStream().flush(); response.getOutputStream().close(); } catch (Exception e) { e.printStackTrace(); } finally { try { in.close(); } catch (IOException e) { e.printStackTrace(); } } } protected void setResponseHeader(HttpServletResponse response, String fileName) throws Exception { response.setContentType("application/vnd.ms-excel"); response.setHeader("Content-Disposition", "attachment;filename=\"" + URLEncoder.encode(fileName, "UTF-8") + "\""); response.setHeader("Cache-Control", "must-revalidate, post-check=0, pre-check=0"); response.setHeader("Pragma", "public"); response.setDateHeader("Expires", 0); } /** * 根据当前row行，来创建index标记的列数,并赋值数据 * * @author Xing * @date 2019-07-23 */ private void createRowAndCell(Object obj, XSSFRow row, XSSFCell cell, int index) { cell = row.getCell(index) == null ? row.createCell(index) : row.getCell(index); cell.setCellValue(obj == null ? "" : obj.toString()); } }

3、创建ExportConfig（和第二步是同目录）

package com.soa.ump.server.message; import org.springframework.boot.context.properties.ConfigurationProperties; import org.springframework.stereotype.Component; /** * 导出配置类 * * @author Xing * @date 2019-07-11 */ @Component @ConfigurationProperties(prefix = "export") public class ExportConfig { /** * 模版目录 */ private String catalogue; public String getCatalogue() { return catalogue; } public void setCatalogue(String catalogue) { this.catalogue = catalogue; } }

4、html页面 写按钮

<permission value="sys:client:export"> <button type="button" lay-submit="" class="layui-btn layui-btn-warm" id="exportExcel" name="exportExcel"> <i class="layui-icon"></i>导出Excel </button> </permission>

5、js

$("#exportExcel").bind("click", function () { excel("searchId", "back/soaexport/userInfoExcel"); }); //导出通用方法 Xing function excel(searchId, urls) { urls = requestLink + urls; if (searchId != "") { $("#" + searchId).find("input,select").each(function () { var id = this.id; var value = this.value; if (id.length > 0) { if (value != "" && value != null) { if (urls.indexOf("?") > -1) { urls += "&" + id + "=" + value; } else { urls += "?" + id + "=" + value; } } } }); } window.location.href = urls; }

6、控制器调用

package com.soa.ump.server.controllerBack; import com.soa.bus.rpc.api.*; import com.soa.common.web.BaseController; import com.soa.ump.server.message.ExportUtils; import io.swagger.annotations.Api; import io.swagger.annotations.ApiImplicitParam; import io.swagger.annotations.ApiImplicitParams; import io.swagger.annotations.ApiOperation; import org.apache.shiro.authz.annotation.RequiresPermissions; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.web.bind.annotation.GetMapping; import org.springframework.web.bind.annotation.RequestMapping; import org.springframework.web.bind.annotation.RestController; import javax.servlet.http.HttpServletRequest; import javax.servlet.http.HttpServletResponse; import java.util.*; /** * 导出管理 * * @author Xing * @return * @date 2019-12-05 */ @RestController @RequestMapping("/back/soaexport") @Api(value = "ExportController", description = "导出管理API") public class ExportController extends BaseController { //用户信息模版 private final String USER_INFO_TEMPLATE = "user_info_template.xlsx"; /** * 导出下载工具类 */ @Autowired private ExportUtils exportUtils; @Autowired private IHjClientService hjClientService; /** * 客户信息导出 * * @return java.lang.String * @author Xing * @date 2019-12-04 */ @ApiOperation(value = "客户信息导出", notes = "客户信息导出") @GetMapping("/userInfoExcel") @RequiresPermissions("sys:client:export") public String userInfoExcel(HttpServletRequest request, HttpServletResponse response) { String templateName = this.USER_INFO_TEMPLATE;//模板名称 String title = "客户信息";//标题 String newFileName = "客户信息";//文件名 Integer[] digitPosition = new Integer[]{}; Map cmap = new HashMap(); List<Map<String, Object>> mapList = this.hjClientService.findExcelList(cmap);//数据源 LinkedList this.exportUtils.exportExcel(response, title, templateName, newFileName, mapList, digitPosition, 2); return "succeed"; } }

7、业务层处理数据

/** * 导出客户所有数据 * 务必要和模板顺序相对应 * @return */ @Override public List<Map<String, Object>> findExcelList(Map cmap) { cmap.put("isDel", "N");//是否删除 List<HjClient> rb = this.hjClientMapper.find(cmap);//根据条件查询数据库 List<Map<String, Object>> relist = new ArrayList<>(); for (HjClient hjClient : rb) { Map map = new LinkedHashMap(); map.put("name", hjClient.getName());//客户名称 map.put("unifyCode", hjClient.getUnifyCode());//社会信用代码 map.put("registeredCapital", hjClient.getRegisteredCapital());//注册资本(元) // map.put("setupTime", hjClient.getSetupTime());//成立日期 map.put("firmType", DicParamUtil.getDicCodeByType("FIRM_TYPE", hjClient.getFirmType()));//企业类型 数据字典转换 map.put("address", hjClient.getAddress());//注册地址 map.put("representative", hjClient.getRepresentative());//法定代表人 map.put("startTime", hjClient.getStartTime());//经营期限起 map.put("stupTime", hjClient.getStupTime());//经营期限止 map.put("scope", hjClient.getScope());//经营范围 map.put("rating", hjClient.getRating());//公开评级 map.put("agent", hjClient.getAgent());//经办人 map.put("remark", hjClient.getRemark());//备注 relist.add(map); } return relist; }

8、成品