package com.zbkj.common.utils; import cn.hutool.core.collection.CollectionUtil; import cn.hutool.core.collection.CollUtil; import cn.hutool.core.date.DateUtil; import cn.hutool.core.util.StrUtil; import cn.hutool.poi.excel.ExcelUtil; import cn.hutool.poi.excel.ExcelWriter; import cn.hutool.poi.excel.StyleSet; import com.zbkj.common.constants.Constants; import com.zbkj.common.constants.DateConstants; import com.zbkj.common.constants.UploadConstants; import com.zbkj.common.exception.CrmebException; import org.apache.commons.io.FilenameUtils; import org.apache.poi.ss.usermodel.CellStyle; import org.apache.poi.ss.usermodel.Font; import java.io.File; import java.util.LinkedHashMap; import com.alibaba.excel.EasyExcel; import com.alibaba.excel.support.ExcelTypeEnum; import com.alibaba.excel.write.metadata.style.WriteCellStyle; import com.alibaba.excel.write.style.HorizontalCellStyleStrategy; import lombok.extern.slf4j.Slf4j; import org.apache.poi.ss.usermodel.BorderStyle; import org.apache.poi.ss.usermodel.HorizontalAlignment; import javax.servlet.http.HttpServletResponse; import java.io.UnsupportedEncodingException; import java.net.URLEncoder; import java.util.List; /** * 导出工具类 */ @Slf4j public class ExportUtil { /** * 导出Excel文件 * * @param fileName 文件名 * @param title 文件标题 * @param voList 数据列表 * @param aliasMap 别名Map（别名需要与数据列表的数据对应） * @return 返回给前端的文件名（路径+文件名） */ public static String exportExcel(String fileName, String title, List voList, LinkedHashMap aliasMap) { if (StrUtil.isBlank(fileName)) { throw new CrmebException("文件名不能为空"); } if (StrUtil.isBlank(title)) { throw new CrmebException("标题不能为空"); } if (CollUtil.isEmpty(voList)) { throw new CrmebException("数据列表不能为空"); } if (CollUtil.isEmpty(aliasMap)) { throw new CrmebException("别名map不能为空"); } // 文件名部分 String hzwServerPath = UploadUtil.getHzwServerPath(); String filePath = StrUtil.format("{}/{}/{}/{}/", UploadConstants.UPLOAD_FILE_KEYWORD, UploadConstants.DOWNLOAD_FILE_KEYWORD, UploadConstants.UPLOAD_MODEL_PATH_EXCEL, CrmebDateUtil.nowDate(DateConstants.DATE_FORMAT_DATE).replace("-", "/")); String path = FilenameUtils.separatorsToSystem(hzwServerPath + filePath); String newFileName = filePath + fileName; // 判断是否存在当前目录，不存在则创建 File file = new File(path); if (!file.exists()) { file.mkdirs(); } fileName = path.concat(fileName); // 通过工具类创建writer ExcelWriter writer = ExcelUtil.getWriter(fileName); // ExcelWriter writer = ExcelUtil.getWriter("d:/writeMapTest.xlsx"); CellStyle headCellStyle = writer.getHeadCellStyle(); Font font = writer.createFont(); font.setBold(true); headCellStyle.setFont(font); // 解决操作excel时换行符（\n）只有鼠标双击才会生效 CellStyle styleSet = writer.getCellStyle(); styleSet.setWrapText(true); //自定义标题别名 aliasMap.forEach((key, value) -> writer.addHeaderAlias(key, value)); // 合并单元格后的标题行，使用默认标题样式 writer.merge(aliasMap.size() - 1, title); writer.merge(aliasMap.size() - 1, StrUtil.format("生成时间:{}", DateUtil.now())); //设置宽度自适应 writer.setColumnWidth(-1, 22); // 一次性写出内容，使用默认样式，强制输出标题 writer.write(voList, true); // 关闭writer，释放内存 writer.close(); return newFileName; } /** * 上传部分设置 */ public static void setUpload(String rootPath, String modelPath, String type) { if (StrUtil.isBlank(rootPath) || StrUtil.isBlank(modelPath) || StrUtil.isBlank(type)) { throw new CrmebException("请检查上传参数，上传参数不能为空"); } UploadUtil.setRootPath(rootPath); UploadUtil.setModelPath(UploadConstants.UPLOAD_FILE_KEYWORD + "/" + UploadConstants.UPLOAD_AFTER_FILE_KEYWORD + "/" + modelPath); // UploadUtil.setType(type); } /** * description：导出excel * author：linchunpeng * date：2023/8/28 */ public void export(String fileName, List resultList, HttpServletResponse response) { try { //设置导出excel的response setExcelResponse(fileName, response); if (CollectionUtil.isNotEmpty(resultList)) { //执行导出 EasyExcel.write(response.getOutputStream(), resultList.get(0).getClass()) .autoCloseStream(false) .excelType(ExcelTypeEnum.XLS) .sheet(fileName) .registerWriteHandler(getWriteCellStyle()) .doWrite(resultList); } } catch (Exception e) { e.printStackTrace(); log.error("[导出excel-{}]异常，{}", fileName, e.getMessage(), e); } } /** * description：设置导出excel的response * author：linchunpeng * date：2023/8/29 */ private void setExcelResponse(String name, HttpServletResponse response) throws UnsupportedEncodingException { String fileName = String.format("%s-%s", name, DateUtils.getDate("yyyyMMddHHmmss")); String disposition = String.format("attachment;filename=%s.xls", URLEncoder.encode(fileName, "UTF-8")); response.setHeader("Access-Control-Expose-Headers", "Content-Disposition"); response.setHeader("Content-Disposition", disposition); response.setContentType("application/vnd.ms-excel;charset=UTF-8");// 设置文本内省 response.setCharacterEncoding("UTF-8");// 设置字符编码 //设置跨域支持 这里如果不设置，swagger和postman可以正常访问，但是前端vue页面无法访问 response.setHeader("Access-Control-Allow-Origin","*"); response.setHeader("Access-Control-Allow-Method","POST,GET,OPTIONS"); response.setHeader("Access-Control-Allow-Headers","*"); } /** * 获取内容样式 */ private HorizontalCellStyleStrategy getWriteCellStyle() { //内容样式 WriteCellStyle contentWriteCellStyle = new WriteCellStyle(); contentWriteCellStyle.setBorderLeft(BorderStyle.THIN);//细实线 contentWriteCellStyle.setBorderTop(BorderStyle.THIN); contentWriteCellStyle.setBorderRight(BorderStyle.THIN); contentWriteCellStyle.setBorderBottom(BorderStyle.THIN); //设置头部样式 WriteCellStyle headWriteCellStyle = new WriteCellStyle(); //设置头部标题居中 headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER); //这个策略是 头是头的样式 内容是内容的样式 其他的策略可以自己实现 return new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle); } }