实体类
package com.springbootemaildemo.excel.a; import javax.persistence.Column; import io.swagger.annotations.ApiModelProperty; public class DataBean { @ApiModelProperty(value = "设备编码") @Column(name = "equip_code") private String equipCode; @ApiModelProperty(value = "设备英文名称") @Column(name = "equip_en") private String equipEn; @ApiModelProperty(value = "设备中文名称") @Column(name = "equip_cn") private String equipCn; public String getEquipCode() { return equipCode; } public void setEquipCode(String equipCode) { this.equipCode = equipCode; } public String getEquipEn() { return equipEn; } public void setEquipEn(String equipEn) { this.equipEn = equipEn; } public String getEquipCn() { return equipCn; } public void setEquipCn(String equipCn) { this.equipCn = equipCn; } }
控制层
package com.springbootemaildemo.excel.a; import io.swagger.annotations.ApiOperation; import lombok.extern.slf4j.Slf4j; import org.slf4j.Logger; import org.slf4j.LoggerFactory; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.web.bind.annotation.PostMapping; import org.springframework.web.bind.annotation.RequestMapping; import org.springframework.web.bind.annotation.RestController; import javax.servlet.http.HttpServletResponse; @Slf4j @RestController @RequestMapping("/excel") public class ExcelController { private static final Logger log = LoggerFactory.getLogger(ExcelController.class); @Autowired ExcelService excelServiceImpl; @ApiOperation(value = "导出数据", notes = "导出数据到excel文件", httpMethod = "POST") @PostMapping("/exportData") public void exportEquipmentsAlarmInfo(HttpServletResponse response) { log.info("到处数据开始---------------"); excelServiceImpl.exportInfo(response); log.info("到处数据结束---------------"); } }
业务层
package com.springbootemaildemo.excel.a; import com.springbootemaildemo.util.ApplicationException; import org.apache.poi.ss.usermodel.Workbook; import org.slf4j.Logger; import org.slf4j.LoggerFactory; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.stereotype.Service; import javax.servlet.http.HttpServletResponse; import java.io.IOException; import java.io.OutputStream; import java.util.*; @Service public class ExcelService { private static final Logger log = LoggerFactory.getLogger(ExcelService.class); @Autowired EhcacheInstance ehcacheInstance; /** * 导出方法 * * @param response */ public void exportInfo(HttpServletResponse response) { // 数据为空时可以导出标题行 List<DataBean> result = getData(); // 设置标题行 Map<String, String> map = ehcacheInstance.getBeanPropertyDesCollection(DataBean.class); map.remove("创建人"); map.remove("创建时间"); map.remove("修改人"); map.remove("修改时间"); Set<Map.Entry<String, String>> titileSet = map.entrySet(); String[] titlesCN = new String[map.size()]; String[] titlesEN = new String[map.size()]; int i = 0; for ( Map.Entry<String, String> entry : titileSet) { titlesCN[i] = entry.getKey(); titlesEN[i] = entry.getValue(); i++; } Workbook workbook = ExcelUtils.exportDataToWorkBook(result, titlesEN, titlesCN); // 将文件流输出到response 提供下载 if (workbook != null) { try { String fileName = UUID.randomUUID().toString().replace("-", "").concat(".xlsx"); // 设置响应内容为文件流 response.setContentType("application/octet-stream;charset=utf-8"); // 强制下载 response.setHeader("Content-Disposition", "attachment;filename=" + fileName); response.addHeader("Cache-Control", "no-cache"); OutputStream out = response.getOutputStream(); workbook.write(out); } catch (Exception e) { log.error("workbook write stream to response fail", e); throw new ApplicationException("500", "workbook write stream to response fail"); } finally { try { workbook.close(); } catch (IOException ex) { log.error("workbook close fail ", ex); } } } } /** * 造数据 * * @return */ private List<DataBean> getData() { List<DataBean> list = new ArrayList<>(); DataBean entity = new DataBean(); entity.setEquipCn("电视"); entity.setEquipEn("TV"); entity.setEquipCode("TV001"); DataBean entity2 = new DataBean(); entity2.setEquipCn("电视"); entity2.setEquipEn("TV"); entity2.setEquipCode("TV001"); list.add(entity); list.add(entity2); return list; } }
package com.springbootemaildemo.excel.a; import com.alibaba.fastjson.JSON; import com.springbootemaildemo.service.impl.MailServiceImpl; import io.swagger.annotations.ApiModelProperty; import org.slf4j.Logger; import org.slf4j.LoggerFactory; import org.springframework.stereotype.Component; import java.lang.reflect.Field; import java.util.LinkedHashMap; import java.util.Map; import java.util.Set; @Component public class EhcacheInstance { private static final Logger log = LoggerFactory.getLogger(EhcacheInstance.class); /** * 返回entity类bean属性名称和中文描述的有序键值集合 key是中文描述 value是字段名称 方便excel标题行转义 * 增加@Cacheable缓存策略 * * @param clazzType * @return */ public Map<String, String> getBeanPropertyDesCollection(Class clazzType) { // 查找本类中定义的属性 Map<String, String> titiles = new LinkedHashMap<String, String>(); Field[] fields = clazzType.getDeclaredFields(); for (Field field : fields) { ApiModelProperty prop = field.getDeclaredAnnotation(ApiModelProperty.class); // 未定义中文注解则默认取字段名称 titiles.put((null != prop ? prop.value() : field.getName()), field.getName()); } // 往上继承类获取属性 Class clazz = clazzType.getSuperclass(); while (!clazz.equals(Object.class)) { Field[] extralFileds = clazz.getDeclaredFields(); for (Field field : extralFileds) { ApiModelProperty prop = field.getDeclaredAnnotation(ApiModelProperty.class); titiles.put((null != prop ? prop.value() : field.getName()), field.getName()); } clazz = clazz.getSuperclass(); } // 移除不需要的属性 titiles.remove("serialVersionUID"); // 底包中审计字段未加swagger注解，此处需手动转义 后续修改底包可移除此段代码 titiles.remove("createdBy"); titiles.put("创建人", "createdBy"); titiles.remove("createDate"); titiles.put("创建时间", "createDate"); titiles.remove("lastUpdatedBy"); titiles.put("修改人", "lastUpdatedBy"); titiles.remove("lastUpdateDate"); titiles.put("修改时间", "lastUpdateDate"); log.info("excel title:{}", JSON.toJSONString(titiles)); return titiles; } }
excel工具类
package com.springbootemaildemo.excel.a; import java.io.ByteArrayInputStream; import java.io.ByteArrayOutputStream; import java.io.IOException; import java.io.InputStream; import java.lang.reflect.InvocationTargetException; import java.text.SimpleDateFormat; import java.util.ArrayList; import java.util.Iterator; import java.util.List; import java.util.Map; import com.springbootemaildemo.util.ApplicationException; import org.apache.commons.beanutils.BeanUtils; import org.apache.poi.ss.usermodel.*; import org.apache.poi.xssf.usermodel.XSSFCellStyle; import org.apache.poi.xssf.usermodel.XSSFFont; import org.apache.poi.xssf.usermodel.XSSFSheet; import org.apache.poi.xssf.usermodel.XSSFWorkbook; import org.slf4j.Logger; import org.slf4j.LoggerFactory; /** * @author Administrator * @date 2020-5-16 * excel导入导出工具类 */ public class ExcelUtils { private static final Logger log = LoggerFactory.getLogger(ExcelUtils.class); private static final int MAXROWCOUNT = 10000; private static SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); /** * 导出数据到excel文件 * * @param result 待存入的bean结果集 * @param titlesEN 英文标题 * @param titlesCN 中文标题 */ public static <T> Workbook exportDataToWorkBook(List<T> result, String[] titlesEN, String[] titlesCN) { // 内存中创建一个07版以上格式的excel XSSFWorkbook workbook = new XSSFWorkbook(); // 重命名工作簿 XSSFSheet sheet = workbook.createSheet("data"); //检查标题行元素是否合法 且一 一对应 if (null == titlesEN || null == titlesCN || titlesEN.length < 1 || titlesCN.length < 1 || titlesEN.length != titlesCN.length) { return null; } // 创建标题行 设置样式 Row row = sheet.createRow(0); CellStyle style = freeStyle(workbook); Cell cell = null; for (int i = 0; i < titlesCN.length; i++) { cell = row.createCell(i); cell.setCellValue(titlesCN[i]); cell.setCellStyle(style); } // 数据检查 不能超过MaxRowCount防止内存溢出 if (null == result || result.size() < 1 || result.size() > MAXROWCOUNT) { return workbook; } // 填充内容 Cell content = null; String cellValue = null; for (int i = 0; i < result.size(); i++) { row = sheet.createRow(i + 1); for (int j = 0; j < titlesEN.length; j++) { // 将内容按顺序赋给对应的列对象 try { cellValue = BeanUtils.getProperty(result.get(i), titlesEN[j]); } catch (IllegalAccessException e) { e.printStackTrace(); } catch (InvocationTargetException e) { e.printStackTrace(); } catch (NoSuchMethodException e) { e.printStackTrace(); } content = row.createCell(j); content.setCellType(CellType.STRING); content.setCellValue(cellValue); } } // 宽度自适应 rejustColumnLength(sheet); return workbook; } /** * 工作簿列宽自适应 * * @param sheet */ private static void rejustColumnLength(XSSFSheet sheet) { if (null == sheet) { return; } Row lastRow = sheet.getRow(sheet.getLastRowNum()); Iterator<Cell> iterator = lastRow.iterator(); int i = 0; while (iterator.hasNext()) { iterator.next(); // 宽度自适应 sheet.autoSizeColumn(i, true); i++; } } public static CellStyle freeStyle(XSSFWorkbook workbook) { XSSFCellStyle style = workbook.createCellStyle(); XSSFFont font = workbook.createFont(); // 设置字体样式 font.setFontName("宋体"); // 设置字体大小 font.setFontHeightInPoints((short) 12); font.setBold(true); style.setFont(font); // 设置标题行背景颜色 style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex()); // style.setFillPattern(CellStyle.SOLID_FOREGROUND); style.setFillPattern(FillPatternType.SOLID_FOREGROUND); // 水平对齐方式 style.setAlignment(HorizontalAlignment.CENTER); style.setBorderBottom(BorderStyle.THIN); style.setBorderRight(BorderStyle.THIN); style.setBorderTop(BorderStyle.THIN); style.setBorderLeft(BorderStyle.THIN); return style; } }
结果：