package com.chinaztt.mes.common.util.easyexcel;
|
|
|
import cn.hutool.core.date.DateUtil;
|
import com.alibaba.excel.EasyExcelFactory;
|
import com.alibaba.excel.ExcelReader;
|
import com.alibaba.excel.ExcelWriter;
|
import com.alibaba.excel.metadata.Font;
|
import com.alibaba.excel.metadata.Sheet;
|
import com.alibaba.excel.metadata.Table;
|
import com.alibaba.excel.metadata.TableStyle;
|
import com.alibaba.excel.metadata.*;
|
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 com.chinaztt.mes.common.util.easyexcel.exception.ExcelException;
|
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
|
import org.apache.poi.ss.usermodel.Cell;
|
import org.apache.poi.ss.usermodel.*;
|
import org.apache.poi.xssf.usermodel.XSSFSheet;
|
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
|
import org.springframework.web.multipart.MultipartFile;
|
|
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 {
|
|
public static HorizontalCellStyleStrategy getStyleStrategy() {
|
// 头的策略
|
WriteCellStyle headWriteCellStyle = new WriteCellStyle();
|
// 背景设置为灰色
|
headWriteCellStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
|
WriteFont headWriteFont = new WriteFont();
|
headWriteFont.setFontHeightInPoints((short) 12);
|
// 字体样式
|
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) 11);
|
// 字体样式
|
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) 12);
|
// 字体样式
|
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) 10);
|
// 字体样式
|
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 HttpServletResponse getResponse(HttpServletResponse response, String targetName){
|
String fileName;
|
try {
|
fileName = String.valueOf(new StringBuilder()
|
.append(targetName)
|
.append("_")
|
.append(DateUtil.today())
|
.append(ExcelTypeEnum.XLS.getValue()));
|
fileName = URLEncoder.encode(fileName, StandardCharsets.UTF_8.toString());
|
response.setContentType("application/vnd.ms-excel");
|
response.setHeader("Cache-Control", "no-cache");
|
response.setHeader("Content-Disposition", "attachment;filename=" + fileName);
|
response.setHeader("Access-Control-Expose-Headers", "Content-Disposition");
|
return response;
|
} catch (UnsupportedEncodingException e) {
|
throw new RuntimeException(e);
|
}
|
}
|
|
/**
|
* excel文件读取
|
*
|
* @param clazz model类型Class对象
|
* @param excel excel文件对象
|
* @param <T> model类型泛型
|
* @return model类型数据集合
|
* @throws IOException IO异常
|
*/
|
public static <T extends BaseRowModel> List<T> readModelExcel(Class<T> clazz, MultipartFile excel) throws IOException {
|
ExcelListener<T> excelListener = new ExcelListener<>();
|
ExcelReader reader = getReader(excel, excelListener);
|
try {
|
reader.getSheets().forEach(sheet -> {
|
sheet.setClazz(clazz);
|
reader.read(sheet);
|
});
|
} catch (Exception e) {
|
e.printStackTrace();
|
throw new IOException("请检查导入模板！");
|
}
|
return excelListener.getDataList();
|
}
|
|
/**
|
*
|
* @param clazz model类型Class对象
|
* @param excel excel文件对象
|
* @param sheetNumber 指定sheet页
|
* @param <T> model类型泛型
|
* @return model类型数据集合
|
* @throws IOException
|
*/
|
public static <T extends BaseRowModel> List<T> readModelSheetExcel(Class<T> clazz, MultipartFile excel, int sheetNumber) throws IOException {
|
ExcelListener<T> excelListener = new ExcelListener<>();
|
ExcelReader reader = getReader(excel, excelListener);
|
try {
|
Sheet sheet = reader.getSheets().get(sheetNumber);
|
sheet.setClazz(clazz);
|
reader.read(sheet);
|
} catch (Exception e) {
|
throw new IOException("请检查导入模板！");
|
}
|
return excelListener.getDataList();
|
}
|
|
/**
|
* 获取excel文件读取对象
|
*
|
* @param excel excel文件对象
|
* @param excelListener excel事件处理
|
* @return excel 读取对象
|
* @throws IOException IO异常
|
*/
|
private static ExcelReader getReader(MultipartFile excel, ExcelListener excelListener) throws IOException {
|
String filename = excel.getOriginalFilename();
|
if (filename == null || (!filename.toLowerCase().endsWith(".xls") && !filename.toLowerCase().endsWith(".xlsx") && !filename.toLowerCase().endsWith(".xltm") && !filename.toLowerCase().endsWith(".xlsm"))) {
|
throw new ExcelException(ResponseResultEnum.EXCEL_FILE_EXT_ERROR);
|
}
|
InputStream inputStream = new BufferedInputStream(excel.getInputStream());
|
return new ExcelReader(inputStream, null, excelListener, false);
|
}
|
|
/**
|
* 写入数据到导出文件
|
*
|
* @param clazz model类型Class对象
|
* @param sheetNo sheet序号
|
* @param headLineMun head行数
|
* @param startRow 开始写入行数
|
* @param tableNo table序号
|
* @param modelList model数据集合
|
* @param temp 临时文件对象
|
* @param export 导出文件对象
|
* @param <T> model泛型
|
*/
|
public static <T extends BaseRowModel> void writeModelExport(Class<T> clazz, Integer sheetNo, Integer headLineMun, Integer startRow, Integer tableNo, List<T> modelList, File temp, File export) {
|
try {
|
InputStream inputStream = new FileInputStream(temp);
|
FileOutputStream OutputStream = new FileOutputStream(export);
|
ExcelWriter writer = EasyExcelFactory.getWriterWithTemp(inputStream, OutputStream, ExcelTypeEnum.XLSX, false);
|
writer.write(modelList, initSheet(sheetNo, headLineMun, startRow), initTable(tableNo, clazz));
|
writer.finish();
|
OutputStream.close();
|
} catch (IOException e) {
|
e.printStackTrace();
|
}
|
}
|
|
/**
|
* Sheet初始化
|
*
|
* @param sheetNo sheet序号
|
* @param headLineMun head行数
|
* @param startRow 开始写入行数
|
* @return Sheet对象
|
*/
|
public static Sheet initSheet(Integer sheetNo, Integer headLineMun, Integer startRow) {
|
Sheet sheet = new Sheet(sheetNo, headLineMun);
|
sheet.setStartRow(startRow);
|
return sheet;
|
}
|
|
/**
|
* Table初始化
|
*
|
* @param tableNo table序号
|
* @param clazz model类型Class对象
|
* @param <T> model泛型
|
* @return Table对象
|
*/
|
public static <T extends BaseRowModel> Table initTable(Integer tableNo, Class<T> clazz) {
|
Table table = new Table(tableNo);
|
table.setClazz(clazz);
|
TableStyle tableStyle = new TableStyle();
|
tableStyle.setTableContentBackGroundColor(IndexedColors.WHITE);
|
tableStyle.setTableHeadBackGroundColor(IndexedColors.WHITE);
|
Font font = new Font();
|
font.setBold(false);
|
font.setFontName("宋体");
|
font.setFontHeightInPoints((short) 11);
|
tableStyle.setTableContentFont(font);
|
table.setTableStyle(tableStyle);
|
return table;
|
}
|
|
/**
|
* 替换模板标记
|
*
|
* @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);
|
}
|
|
}
|
