package com.ycl.common.utils.excel;
|
|
import cn.hutool.core.collection.CollUtil;
|
import cn.hutool.core.util.IdUtil;
|
import cn.hutool.core.util.ObjectUtil;
|
import cn.hutool.core.util.ZipUtil;
|
import com.alibaba.excel.EasyExcel;
|
import com.alibaba.excel.annotation.ExcelProperty;
|
import com.alibaba.excel.write.builder.ExcelWriterSheetBuilder;
|
import com.alibaba.excel.write.style.column.LongestMatchColumnWidthStyleStrategy;
|
import com.ycl.common.utils.excel.convert.ExcelBigNumberConvert;
|
import com.ycl.common.utils.excel.core.CellMergeStrategy;
|
import com.ycl.common.utils.excel.core.DropDownOptions;
|
import com.ycl.common.utils.excel.core.ExcelDownHandler;
|
import com.ycl.common.utils.file.FileUtils;
|
import org.apache.commons.codec.Charsets;
|
|
import javax.servlet.ServletOutputStream;
|
import javax.servlet.http.HttpServletResponse;
|
import javax.validation.constraints.NotNull;
|
import java.io.*;
|
import java.lang.reflect.Field;
|
import java.net.URLEncoder;
|
import java.nio.file.Files;
|
import java.util.Arrays;
|
import java.util.HashSet;
|
import java.util.List;
|
import java.util.Set;
|
|
/**
|
* @Author: ljx
|
* @CreateTime: 2024-10-18 10:13
|
*/
|
|
public class OutputExcelUtils {
|
|
/**
|
* 忽略部分导出字段方法
|
* @param response
|
* @param fileName 文件名称
|
* @param sheetName sheet名称
|
* @param dataList 需要导出的数据
|
* @param clazz 类
|
* @param
|
* @param <T>
|
* @throws IOException
|
*/
|
public static <T> void export(HttpServletResponse response, String fileName, String sheetName, List<T> dataList, Class<T> clazz, List<String> fieldNames) throws IOException {
|
response.setContentType("application/zip");
|
response.setCharacterEncoding(Charsets.UTF_8.name());
|
fileName = URLEncoder.encode(fileName, Charsets.UTF_8);
|
response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".zip");
|
Set<Integer> selectedIndexes = getSelectFields(fieldNames, clazz);
|
|
//下载模板
|
downloadTemplate(response, sheetName, dataList, clazz, selectedIndexes);
|
|
|
// 临时向资源文件夹写 名为template的文件夹
|
// ClassPathResource classPathResource = new ClassPathResource("/template/test.xls");
|
// File file = classPathResource.getFile();
|
// try(FileOutputStream fileOutputStream = new FileOutputStream(file,false)) {
|
// EasyExcel.write(fileOutputStream, clazz).sheet(sheetName).doWrite(dataList);
|
// }
|
//
|
// ClassPathResource classPathResource1 = new ClassPathResource("/template");
|
// File zip = ZipUtil.zip(classPathResource1.getFile());
|
// byte[] bytes;
|
// try (FileInputStream fileInputStream = new FileInputStream(zip)) {
|
// bytes = fileInputStream.readAllBytes();
|
// }
|
// response.getOutputStream().write(bytes);
|
|
// if (selectedIndexes.size() > 0) {
|
// EasyExcel.write(response.getOutputStream(), clazz).excludeColumnIndexes(selectedIndexes).sheet(sheetName).doWrite(dataList);
|
// } else {
|
// EasyExcel.write(response.getOutputStream(), clazz).sheet(sheetName).doWrite(dataList);
|
// }
|
}
|
|
private synchronized static <T> void downloadTemplate(HttpServletResponse response, String sheetName, List<T> dataList, Class<T> clazz, Set<Integer> columnIndex) throws IOException {
|
|
File tempDir = null;
|
try {
|
// 创建临时目录
|
tempDir = Files.createTempDirectory("temp").toFile();
|
|
File templateDir = new File(tempDir, "template");
|
if (!templateDir.exists()) {
|
templateDir.mkdirs();
|
}
|
|
// 创建 Excel 文件
|
File excelFile = new File(templateDir, "excel.xlsx");
|
if (!excelFile.exists()) {
|
excelFile.createNewFile();
|
}
|
|
// 写入 Excel 模板数据
|
try (FileOutputStream fileOutputStream = new FileOutputStream(excelFile, false)) {
|
EasyExcel.write(fileOutputStream, clazz).includeColumnIndexes(columnIndex).sheet(sheetName).doWrite(dataList);
|
}
|
|
// 创建附件目录
|
File attachmentDir = new File(templateDir, "attachment");
|
if (!attachmentDir.exists()) {
|
attachmentDir.mkdirs();
|
}
|
|
// 打包 ZIP 文件
|
File zipFile = ZipUtil.zip(templateDir);
|
byte[] zipBytes = Files.readAllBytes(zipFile.toPath());
|
|
// 将 ZIP 文件写入响应
|
try(ServletOutputStream outputStream = response.getOutputStream()) {
|
outputStream.write(zipBytes);
|
}
|
} finally {
|
deleteDirectoryOrFile(tempDir);
|
}
|
}
|
|
public static void deleteDirectoryOrFile(File file) {
|
if (ObjectUtil.isNull(file)) {
|
return;
|
}
|
|
if (file.isDirectory()) {
|
File[] files = file.listFiles();
|
if (files != null) {
|
for (File f : files) {
|
deleteDirectoryOrFile(f);
|
}
|
}
|
}
|
file.delete();
|
|
}
|
|
/**
|
* 导出模板
|
* @param response
|
* @param fileName
|
* @param sheetName
|
* @param dataList
|
* @param clazz
|
* @param fieldNames
|
*/
|
public static <T> void exportTemplate(HttpServletResponse response,String fileName, String sheetName, List<T> dataList, Class<T> clazz, List<String> fieldNames) throws IOException {
|
Set<Integer> selectedIndexes = getSelectFields(fieldNames, clazz);
|
|
resetResponse(fileName, response);
|
|
exportExcel(dataList, sheetName, clazz, false, response.getOutputStream(), null, selectedIndexes);
|
}
|
|
public static <T> @NotNull Set<Integer> getSelectFields(List<String> fieldNames, Class<T> clazz) {
|
Set<Integer> selectedIndexes = new HashSet<>();
|
if (CollUtil.isNotEmpty(fieldNames)) {
|
// 反射获取字段属性,这里只获取easyExcel注解的字段
|
Field[] declaredFields = Arrays.stream(clazz.getDeclaredFields())
|
.filter(field -> field.isAnnotationPresent(ExcelProperty.class))
|
.toArray(Field[]::new);
|
// // 匹配需要导入的字段
|
for (int i = 0; i < declaredFields.length; i++) {
|
if (fieldNames.contains(declaredFields[i].getName())) {
|
// 获取需要导入的字段下标
|
selectedIndexes.add(i);
|
}
|
}
|
}
|
return selectedIndexes;
|
|
}
|
|
/**
|
* 导出excel
|
*
|
* @param list 导出数据集合
|
* @param sheetName 工作表的名称
|
* @param clazz 实体类
|
* @param merge 是否合并单元格
|
* @param os 输出流
|
*/
|
public static <T> void exportExcel(List<T> list, String sheetName, Class<T> clazz, boolean merge,
|
OutputStream os, List<DropDownOptions> options, Set<Integer> selectedIndexes) {
|
ExcelWriterSheetBuilder builder = EasyExcel.write(os, clazz)
|
.autoCloseStream(false)
|
.includeColumnIndexes(selectedIndexes)
|
// 自动适配
|
.registerWriteHandler(new LongestMatchColumnWidthStyleStrategy())
|
// 大数值自动转换 防止失真
|
.registerConverter(new ExcelBigNumberConvert())
|
.sheet(sheetName);
|
if (merge) {
|
// 合并处理器
|
builder.registerWriteHandler(new CellMergeStrategy(list, true));
|
}
|
// 添加下拉框操作
|
builder.registerWriteHandler(new ExcelDownHandler(options));
|
builder.doWrite(list);
|
}
|
|
/**
|
* 重置响应体
|
*/
|
private static void resetResponse(String sheetName, HttpServletResponse response) throws UnsupportedEncodingException {
|
String filename = encodingFilename(sheetName);
|
FileUtils.setAttachmentResponseHeader(response, filename);
|
response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=UTF-8");
|
}
|
|
/**
|
* 编码文件名
|
*/
|
public static String encodingFilename(String filename) {
|
return IdUtil.fastSimpleUUID() + "_" + filename + ".xlsx";
|
}
|
|
|
}
|
