package com.ruoyi.common.utils.excel; import cn.hutool.core.date.DateUtil; import com.alibaba.excel.support.ExcelTypeEnum; import com.alibaba.excel.write.metadata.style.WriteCellStyle; import com.alibaba.excel.write.metadata.style.WriteFont; import com.alibaba.excel.write.style.HorizontalCellStyleStrategy; import org.apache.poi.openxml4j.exceptions.InvalidFormatException; import org.apache.poi.ss.usermodel.*; import org.apache.poi.xssf.usermodel.XSSFSheet; import org.apache.poi.xssf.usermodel.XSSFWorkbook; import javax.servlet.http.HttpServletResponse; import java.io.*; import java.net.URLEncoder; import java.nio.charset.StandardCharsets; import java.util.Calendar; import java.util.List; import java.util.Map; /** *@author: Zou, Yu *@description: *@date: 2023/9/24 0024 13:18 **/ public class EasyExcelUtils { /** * color枚举: IndexedColors * @param color * @return */ public static HorizontalCellStyleStrategy getStyleStrategy(IndexedColors color) { // 头的策略 WriteCellStyle headWriteCellStyle = new WriteCellStyle(); // 背景设置为灰色 headWriteCellStyle.setFillForegroundColor(color.getIndex()); WriteFont headWriteFont = new WriteFont(); headWriteFont.setFontHeightInPoints((short) 14); // 字体样式 headWriteFont.setFontName("宋体"); headWriteCellStyle.setWriteFont(headWriteFont); // 自动换行 headWriteCellStyle.setWrapped(false); // 水平对齐方式 headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER); // 垂直对齐方式 headWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER); // 内容的策略 WriteCellStyle contentWriteCellStyle = new WriteCellStyle(); // 这里需要指定 FillPatternType 为FillPatternType.SOLID_FOREGROUND 不然无法显示背景颜色.头默认了 FillPatternType所以可以不指定 // contentWriteCellStyle.setFillPatternType(FillPatternType.SQUARES); // 背景白色 contentWriteCellStyle.setFillForegroundColor(IndexedColors.WHITE.getIndex()); WriteFont contentWriteFont = new WriteFont(); // 字体大小 contentWriteFont.setFontHeightInPoints((short) 12); // 字体样式 contentWriteFont.setFontName("宋体"); contentWriteCellStyle.setWriteFont(contentWriteFont); // 内容居中对齐 contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER); //内容垂直居中 contentWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER); //边框设置 contentWriteCellStyle.setBorderTop(BorderStyle.THIN); contentWriteCellStyle.setBorderRight(BorderStyle.THIN); contentWriteCellStyle.setBorderBottom(BorderStyle.THIN); contentWriteCellStyle.setBorderLeft(BorderStyle.THIN); // 这个策略是 头是头的样式 内容是内容的样式 其他的策略可以自己实现 return new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle); } public static HorizontalCellStyleStrategy getStyleFixedStrategy() { // 头的策略 WriteCellStyle headWriteCellStyle = new WriteCellStyle(); // 背景设置为灰色 headWriteCellStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex()); WriteFont headWriteFont = new WriteFont(); headWriteFont.setFontHeightInPoints((short) 14); // 字体样式 headWriteFont.setFontName("Calibri"); headWriteCellStyle.setWriteFont(headWriteFont); // 自动换行 headWriteCellStyle.setWrapped(false); // 水平对齐方式 headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER); // 垂直对齐方式 headWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER); // 内容的策略 WriteCellStyle contentWriteCellStyle = new WriteCellStyle(); // 这里需要指定 FillPatternType 为FillPatternType.SOLID_FOREGROUND 不然无法显示背景颜色.头默认了 FillPatternType所以可以不指定 // contentWriteCellStyle.setFillPatternType(FillPatternType.SQUARES); // 背景白色 contentWriteCellStyle.setFillForegroundColor(IndexedColors.WHITE.getIndex()); WriteFont contentWriteFont = new WriteFont(); // 字体大小 contentWriteFont.setFontHeightInPoints((short) 12); // 字体样式 contentWriteFont.setFontName("Calibri"); contentWriteCellStyle.setWriteFont(contentWriteFont); // 内容左对齐 contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.LEFT); // 这个策略是 头是头的样式 内容是内容的样式 其他的策略可以自己实现 return new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle); } public static HorizontalCellStyleStrategy getArveStyleStrategy() { // 头的策略 WriteCellStyle headWriteCellStyle = new WriteCellStyle(); // 背景设置为 headWriteCellStyle.setFillForegroundColor(IndexedColors.BLACK.getIndex()); WriteFont headWriteFont = new WriteFont(); headWriteFont.setFontHeightInPoints((short) 12); // 字体样式 headWriteFont.setFontName("Calibri"); headWriteFont.setColor(IndexedColors.WHITE.getIndex()); headWriteCellStyle.setWriteFont(headWriteFont); headWriteCellStyle.setBorderBottom(BorderStyle.MEDIUM); headWriteCellStyle.setLeftBorderColor(IndexedColors.WHITE.getIndex()); headWriteCellStyle.setRightBorderColor(IndexedColors.WHITE.getIndex()); // 自动换行 headWriteCellStyle.setWrapped(true); // 水平对齐方式 headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.LEFT); // 垂直对齐方式 headWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER); // 内容的策略 WriteCellStyle contentWriteCellStyle = new WriteCellStyle(); // 这里需要指定 FillPatternType 为FillPatternType.SOLID_FOREGROUND 不然无法显示背景颜色.头默认了 FillPatternType所以可以不指定 // contentWriteCellStyle.setFillPatternType(FillPatternType.SQUARES); // 背景白色 contentWriteCellStyle.setFillForegroundColor(IndexedColors.WHITE.getIndex()); WriteFont contentWriteFont = new WriteFont(); // 字体大小 contentWriteFont.setFontHeightInPoints((short) 10); // 字体样式 contentWriteFont.setFontName("Calibri"); contentWriteCellStyle.setWriteFont(contentWriteFont); // 这个策略是 头是头的样式 内容是内容的样式 其他的策略可以自己实现 return new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle); } /** * 设置response * @param response * @param targetName * @return */ public static OutputStream getResponse(HttpServletResponse response, String targetName) throws IOException { String fileName; try { fileName = String.valueOf(new StringBuilder() .append(targetName) .append("_") .append(DateUtil.today()) .append(ExcelTypeEnum.XLSX.getValue())); fileName = URLEncoder.encode(fileName, StandardCharsets.UTF_8.toString()); response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"); response.setHeader("Cache-Control", "no-cache"); response.setHeader("Content-Disposition", "attachment;filename=" + fileName); response.setHeader("Access-Control-Expose-Headers", "Content-Disposition"); return response.getOutputStream(); } catch (UnsupportedEncodingException e) { throw new RuntimeException(e); } } /** * 替换模板标记 * * @param export 导出文件对象 * @param index sheet序号 * @param rows 单元格行序 * @param columns 单元格列序 * @param replaceMap 多个标记集合 */ public static void editModelWorkBook(File export, Integer index, Integer rows, Integer columns, Map replaceMap) { try { XSSFWorkbook workBook = new XSSFWorkbook(export); // editModelWorkBook(workBook,index,0,0,replaceMap); editModelWorkBook(workBook, index, rows, columns, replaceMap); workBook.close(); } catch (IOException | InvalidFormatException e) { e.printStackTrace(); } } /** * 替换模板标记 * * @param workBook excel工作册对象 * @param index sheet序号 * @param rows 单元格行序 * @param columns 单元格列序 * @param replaceMap 多个标记集合 */ public static void editModelWorkBook(XSSFWorkbook workBook, Integer index, Integer rows, Integer columns, Map replaceMap) { XSSFSheet sheet = workBook.getSheetAt(index); Row row = sheet.getRow(rows); Cell cell = row.getCell(columns); String replaceString = cell.toString(); for (Map.Entry entry : replaceMap.entrySet()) { replaceString = replaceString.replace(entry.getKey(), entry.getValue()); } cell.setCellValue(replaceString); workBook.setSheetName(index, replaceString.replace(":", "")); } /** * 模板sheet克隆及命名 * * @param temp 临时文件对象 * @param export 导出文件对象 * @param cloneSheetNo 克隆sheet序列 * @param rows 单元格行序 * @param columns 单元格列序 * @param replaceMapList 多个标记集合 */ public static void writeModelCloneSheet(File temp, File export, Integer cloneSheetNo, Integer rows, Integer columns, List> replaceMapList) { try { XSSFWorkbook workBook = new XSSFWorkbook(temp); OutputStream outputStream = new FileOutputStream(export); /** 如果你需要6份相同模板的sheet 那么你只需要克隆5份即可*/ for (int index = 0; index < replaceMapList.size() - 1; index++) { /** 克隆模板文件 */ XSSFSheet sheet = workBook.cloneSheet(cloneSheetNo); } for (int index = 0; index < replaceMapList.size(); index++) { editModelWorkBook(workBook, index, rows, columns, replaceMapList.get(index)); } workBook.write(outputStream); outputStream.flush(); outputStream.close(); workBook.close(); } catch (IOException | InvalidFormatException e) { e.printStackTrace(); } } /** * 临时文件写入导出文件 * * @param temp 临时文件对象 * @param export 导出文件对象 * @param index sheet序号 * @param rows 单元格行序 * @param columns 单元格列序 * @param isTime 是否有时间标记 */ public static void writeModelWorkBook(File temp, File export, Integer index, Integer rows, Integer columns, Boolean isTime) { try { XSSFWorkbook workBook = new XSSFWorkbook(temp); OutputStream outputStream = new FileOutputStream(export); if (isTime) { writeModelWorkBook(workBook, index, rows, columns); } workBook.write(outputStream); outputStream.flush(); outputStream.close(); workBook.close(); } catch (IOException | InvalidFormatException e) { e.printStackTrace(); } } /** * 替换时间标记（year,month,day） * * @param workBook excel工作册对象 * @param index sheet序号 * @param rows 单元格行序 * @param columns 单元格列序 */ public static void writeModelWorkBook(XSSFWorkbook workBook, Integer index, Integer rows, Integer columns) { XSSFSheet xssfSheet = workBook.getSheetAt(index); Row row = xssfSheet.getRow(rows); Cell cell = row.getCell(columns); String cellString = cell.toString(); Calendar calendar = Calendar.getInstance(); int year = calendar.get(Calendar.YEAR); int month = calendar.get(Calendar.MONTH) + 1; int day = calendar.get(Calendar.DAY_OF_MONTH); String yearString = cellString.replace("year", String.valueOf(year)); String monthString = yearString.replace("month", String.valueOf(month)); String dayString = monthString.replace("day", String.valueOf(day)); cell.setCellValue(dayString); } /** * 针对UserModel导出方法 * * @param temp * @param export * @param isTime */ public static void writeModelWorkBook(File temp, File export, Boolean isTime) { writeModelWorkBook(temp, export, 0, 1, 9, isTime); } public static void writeModelCloneSheet(File temp, File export, List> replaceMapList) { writeModelCloneSheet(temp, export, 0, 0, 0, replaceMapList); } }