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<String, String> 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<String, String> replaceMap) {
|
XSSFSheet sheet = workBook.getSheetAt(index);
|
Row row = sheet.getRow(rows);
|
Cell cell = row.getCell(columns);
|
String replaceString = cell.toString();
|
for (Map.Entry<String, String> 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<Map<String, String>> 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<Map<String, String>> replaceMapList) {
|
writeModelCloneSheet(temp, export, 0, 0, 0, replaceMapList);
|
}
|
|
}
|
