自用小工具
word工具类
- 支持段落、表格的文本占位符、图片占位符替换
- 支持设置文本颜色
import org.apache.poi.xwpf.usermodel.*;
import org.openxmlformats.schemas.wordprocessingml.x2006.main.CTRow;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.nio.file.Files;
import java.util.*;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
/**
* word工具类
*/
public class WordBuilder {
private static final String TEXT = "${";
private static final String IMAGE = "${image:";
private static final Pattern TEXT_PLACEHOLDER = Pattern.compile("\\$\\{([^}]+)}");
private static final Pattern IMAGE_PLACEHOLDER = Pattern.compile("\\$\\{image:([^}]+)}");
private final XWPFDocument doc;
public WordBuilder(InputStream tpl) throws IOException {
this.doc = new XWPFDocument(tpl);
}
public WordBuilder(byte[] tpl) throws IOException {
this(new ByteArrayInputStream(tpl));
}
/**
* 普通占位符
*/
public WordBuilder replace(Map<String, Object> vars) {
replaceAll(vars);
return this;
}
/**
* 图片
*
* @param images 字段名：base68字符串
*/
public WordBuilder image(Map<String, Object> images) {
return image(images, -1, -1);
}
/**
* 图片
*
* @param key 占位符名称
* @param base68Image 图片：base68字符串
* @param widthEmus 宽度
* @param heightEmus 高度
*/
public WordBuilder image(String key, String base68Image, int widthEmus, int heightEmus) {
Map<String, Object> map = new HashMap<>();
map.put(key, base68Image);
replaceAllImages(map, widthEmus * 9525, heightEmus * 9525);
return this;
}
/**
* 图片
*
* @param key 占位符名称
* @param bytes 图片：bytes[]
* @param widthEmus 宽度
* @param heightEmus 高度
*/
public WordBuilder image(String key, byte[] bytes, int widthEmus, int heightEmus) {
Map<String, Object> map = new HashMap<>();
map.put(key, bytes);
replaceAllImages(map, widthEmus * 9525, heightEmus * 9525);
return this;
}
/**
* 图片
*
* @param images 字段名：base68字符串 或 bytes[]
* @param widthEmus 图片宽度
* @param heightEmus 图片高度
*/
public WordBuilder image(Map<String, Object> images, int widthEmus, int heightEmus) {
replaceAllImages(images, widthEmus * 9525, heightEmus * 9525);
return this;
}
/**
* 表格
*
* @param data 数据列表
*/
public WordBuilder table(List<Map<String, Object>> data) {
return table(data, 0, 1);
}
/**
* 表格
*
* @param data 数据列表
* @param tableIndex 文档中第几个表格（0 开始）
* @param tempRowIndex 模板行号（0 开始）
*/
public WordBuilder table(List<Map<String, Object>> data, int tableIndex, int tempRowIndex) {
if (data == null || data.isEmpty() || tableIndex < 0 || tempRowIndex < 0) {
return this;
}
List<XWPFTable> tables = doc.getTables();
if (tableIndex >= tables.size()) {
return this;
}
XWPFTable table = tables.get(tableIndex);
if (tempRowIndex >= table.getRows().size()) {
return this;
}
XWPFTableRow templateRow = table.getRow(tempRowIndex);
for (int i = 0; i < data.size(); i++) {
Map<String, Object> rowData = data.get(i);
// 复制模板行
XWPFTableRow newRow = new XWPFTableRow((CTRow) templateRow.getCtRow().copy(), table);
for (XWPFTableCell newCell : newRow.getTableCells()) {
for (XWPFParagraph newPara : newCell.getParagraphs()) {
// 替换文本占位符
replaceParagraph(newPara, rowData);
}
}
int newRowIndex = tempRowIndex + 1 + i;
table.addRow(newRow, newRowIndex);
}
// 删除模板行
table.removeRow(tempRowIndex);
return this;
}
/**
* 设置文本颜色(全局)
*
* @param textColor 文本颜色
*/
public WordBuilder textColor(String textColor) {
return textColorParagraph(textColor, -1);
}
/**
* 设置段落文本颜色
*
* @param textColor 文本颜色
* @param index 文档中第几个段落（0 开始）
*/
public WordBuilder textColorParagraph(String textColor, int index) {
// TODO
return this;
}
/**
* 设置表格文本颜色
*
* @param textColor 文本颜色
* @param index 文档中第几个表格（0 开始）
*/
public WordBuilder textColorTable(String textColor, int index) {
return textColorTable(textColor, index, -1, -1);
}
/**
* 设置表格文本颜色
*
* @param textColor 文本颜色
* @param index 文档中第几个表格（0 开始）
* @param rowIndex 行下标
*/
public WordBuilder textColorTableRow(String textColor, int index, int rowIndex) {
return textColorTable(textColor, index, rowIndex, -1);
}
/**
* 设置表格文本颜色
*
* @param textColor 文本颜色
* @param index 文档中第几个表格（0 开始）
* @param colIndex 列下标
*/
public WordBuilder textColorTableCol(String textColor, int index, int colIndex) {
return textColorTable(textColor, index, -1, colIndex);
}
/**
* 设置表格文本颜色
*
* @param textColor 文本颜色
* @param index 文档中第几个表格（0 开始）
* @param rowIndex 行下标
* @param colIndex 列下标
*/
public WordBuilder textColorTable(String textColor, int index, int rowIndex, int colIndex) {
// TODO
return this;
}
public void write(HttpServletResponse resp) throws IOException {
resp.setContentType("application/vnd.openxmlformats-officedocument.wordprocessingml.document");
resp.setHeader("Content-Disposition", "attachment;filename=file.docx");
try (ServletOutputStream sos = resp.getOutputStream()) {
doc.write(sos);
} finally {
if (doc != null) {
doc.close();
}
}
}
public void write(File file) throws IOException {
try (FileOutputStream out = new FileOutputStream(file)) {
doc.write(out);
} finally {
if (doc != null) {
doc.close();
}
}
}
public byte[] toBytes() throws IOException {
try (ByteArrayOutputStream bos = new ByteArrayOutputStream()) {
doc.write(bos);
return bos.toByteArray();
} finally {
if (doc != null) {
doc.close();
}
}
}
private void replaceAll(Map<String, Object> map) {
// 段落
for (XWPFParagraph p : doc.getParagraphs()) {
replaceParagraph(p, map);
}
// 表格
for (XWPFTable table : doc.getTables()) {
for (XWPFTableRow row : table.getRows()) {
for (XWPFTableCell cell : row.getTableCells()) {
for (XWPFParagraph p : cell.getParagraphs()) {
replaceParagraph(p, map);
}
}
}
}
// 页眉
for (XWPFHeader header : doc.getHeaderList()) {
for (XWPFParagraph paragraph : header.getParagraphs()) {
replaceParagraph(paragraph, map);
}
}
// 页脚
List<XWPFFooter> footerList = doc.getFooterList();
for (XWPFFooter footer : footerList) {
for (XWPFParagraph paragraph : footer.getParagraphs()) {
replaceParagraph(paragraph, map);
}
}
}
private void replaceParagraph(XWPFParagraph p, Map<String, Object> data) {
if (!p.getText().contains(TEXT)) {
return;
}
// 合并 run，防止占位符被拆
joinRuns(p);
for (XWPFRun run : p.getRuns()) {
String txt = run.text();
if (txt.contains(TEXT) && !txt.contains(IMAGE)) {
Matcher m = TEXT_PLACEHOLDER.matcher(txt);
StringBuffer sb = new StringBuffer();
while (m.find()) {
String key = m.group(1);
Object val = data.get(key);
if (val != null) {
m.appendReplacement(sb, val.toString());
}
}
m.appendTail(sb);
txt = sb.toString();
// 换行处理
if (txt.contains("\n")) {
String[] lines = txt.split("\n");
run.setText(lines[0], 0);
for (int i = 1; i < lines.length; i++) {
run.addBreak();
run.setText(lines[i]);
}
} else {
run.setText(txt, 0);
}
}
}
}
private void replaceAllImages(Map<String, Object> images, int widthEmus, int heightEmus) {
// 段落
for (XWPFParagraph p : doc.getParagraphs()) {
replaceParagraphWithImage(p, images, widthEmus, heightEmus);
}
// 表格
for (XWPFTable table : doc.getTables()) {
for (XWPFTableRow row : table.getRows()) {
for (XWPFTableCell cell : row.getTableCells()) {
for (XWPFParagraph p : cell.getParagraphs()) {
replaceParagraphWithImage(p, images, widthEmus, heightEmus);
}
}
}
}
}
private void replaceParagraphWithImage(XWPFParagraph p, Map<String, Object> images, int widthEmus, int heightEmus) {
if (!p.getText().contains(IMAGE)) {
return;
}
joinRuns(p);
for (XWPFRun run : p.getRuns()) {
String txt = run.text();
Matcher m = IMAGE_PLACEHOLDER.matcher(txt);
while (m.find()) {
String key = m.group(1);
Object val = images.get(key);
if (val == null) {
continue;
}
byte[] bytes = toBytes(val);
if (bytes == null || bytes.length == 0) {
continue;
}
String ext = getFormat(bytes);
int pictureType = getPictureType(ext);
try {
int width = widthEmus > 0 ? widthEmus : 200 * 9525;
int height = heightEmus > 0 ? heightEmus : 200 * 9525;
run.addPicture(new ByteArrayInputStream(bytes), pictureType, "image." + ext, width, height);
run.setText("", 0);
} catch (Exception e) {
throw new RuntimeException("插入图片失败: " + key, e);
}
}
}
}
/**
* 合并 run，防止占位符被拆开
*/
private void joinRuns(XWPFParagraph p) {
if (p.getRuns().size() <= 1) {
return;
}
StringBuilder sb = new StringBuilder();
for (XWPFRun r : p.getRuns()) {
sb.append(r.text());
}
p.getRuns().get(0).setText(sb.toString(), 0);
while (p.getRuns().size() > 1) {
p.removeRun(1);
}
}
private byte[] toBytes(Object val) {
try {
if (val instanceof byte[]) {
return (byte[]) val;
}
if (val instanceof String) {
String s = (String) val;
// 去掉 data URI 头（如果有）
if (s.contains(",")) {
s = s.split(",")[1];
}
return Base64.getDecoder().decode(s.trim());
}
if (val instanceof File) {
return org.apache.poi.util.IOUtils.toByteArray(Files.newInputStream(((File) val).toPath()));
}
if (val instanceof InputStream) {
return org.apache.poi.util.IOUtils.toByteArray((InputStream) val);
}
} catch (IOException e) {
throw new RuntimeException("读取图片失败", e);
}
return null;
}
private String getFormat(byte[] bs) {
// 简单魔数
if (bs[0] == (byte) 0x89 && bs[1] == (byte) 0x50) {
return "png";
}
if (bs[0] == (byte) 0xFF && bs[1] == (byte) 0xD8) {
return "jpg";
}
if (bs[0] == (byte) 0x47 && bs[1] == (byte) 0x49) {
return "gif";
}
return "png";
}
private int getPictureType(String ext) {
switch (ext.toLowerCase(Locale.ENGLISH)) {
case "jpg":
return Document.PICTURE_TYPE_JPEG;
case "jpeg":
return Document.PICTURE_TYPE_JPEG;
case "gif":
return Document.PICTURE_TYPE_GIF;
case "png":
return Document.PICTURE_TYPE_PNG;
default:
throw new IllegalArgumentException("不支持的图片类型: " + ext);
}
}
}
PDF工具类
- word转pdf
import com.spire.doc.Document;
import com.spire.doc.FileFormat;
import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.File;
/**
* PDF工具类
*/
public class PdfUtil {
public static byte[] convertWordToPdf(byte[] docBytes) {
Document doc = new Document();
try (ByteArrayOutputStream pdfOut = new ByteArrayOutputStream()) {
doc.loadFromStream(new ByteArrayInputStream(docBytes), FileFormat.Docx);
doc.saveToStream(pdfOut, FileFormat.PDF);
return pdfOut.toByteArray();
} catch (IOException e) {
throw new MyWzcgglException("Word 转 PDF 失败");
} finally {
doc.dispose();
}
}
}
集合工具
- 差集
- 交集
- 去重
- 切片
- 自适应切片
- 切片操作数据库查询（串行、并行）
- 拷贝
import cn.hutool.core.collection.CollectionUtil;
import org.springframework.beans.BeanUtils;
import java.util.*;
import java.util.function.Function;
import java.util.function.Supplier;
import java.util.stream.Collectors;
/**
* 集合工具
*/
public class ListUtil {
/**
* 求差集
*
* @param comparator 比较器
* @param keyExtractors 比较字段
* @return list1 与 list2 的差集
*/
@SafeVarargs
public static <T> List<T> difference(List<T> list1, List<T> list2, Comparator<T> comparator, Function<T, ?>... keyExtractors) {
if (CollectionUtil.isEmpty(list1)) {
return new ArrayList<>();
}
if (CollectionUtil.isEmpty(list2)) {
return new ArrayList<>(list1);
}
// 用 Wrapper 包装 list2，建立哈希索引
Set<Wrapper<T>> set2 = new HashSet<>(list2.size() * 2);
for (T t : list2) {
set2.add(new Wrapper<>(t, comparator, keyExtractors));
}
List<T> result = new ArrayList<>(list1.size());
for (T t : list1) {
if (!set2.contains(new Wrapper<>(t, comparator, keyExtractors))) {
result.add(t);
}
}
return result;
}
/**
* 求差集
*
* @param keyExtractors 比较字段
* @return list1 与 list2 的差集
*/
@SafeVarargs
public static <T> List<T> difference(List<T> list1, List<T> list2, Function<T, ?>... keyExtractors) {
Comparator<T> comparator = createComparator(keyExtractors);
return difference(list1, list2, comparator, keyExtractors);
}
/**
* 求交集
*
* @param objectComparator 比较器
* @param keyExtractors 比较字段
* @return list1 与 list2 的交集（以list1为原型）
*/
@SafeVarargs
public static <T> List<T> intersection(List<T> list1, List<T> list2, Comparator<T> objectComparator, Function<T, ?>... keyExtractors) {
if (CollectionUtil.isEmpty(list1) || CollectionUtil.isEmpty(list2)) {
return new ArrayList<>();
}
Set<Wrapper<T>> index = new HashSet<>(Math.min(list1.size(), list2.size()) * 2);
// 用较小列表建索引，减少内存
List<T> small = list1.size() <= list2.size() ? list1 : list2;
for (T t : small) {
index.add(new Wrapper<>(t, objectComparator, keyExtractors));
}
// 遍历较大列表，命中且未输出过的元素加入结果
List<T> large = small == list1 ? list2 : list1;
Set<Wrapper<T>> seen = new HashSet<>(index.size() * 2);
List<T> result = new ArrayList<>(index.size());
for (T t : large) {
Wrapper<T> w = new Wrapper<>(t, objectComparator, keyExtractors);
if (index.contains(w) && seen.add(w)) {
result.add(t);
}
}
return result;
}
/**
* 求交集
*
* @param keyExtractors 比较字段
* @return list1 与 list2 的交集
*/
@SafeVarargs
public static <T> List<T> intersection(List<T> list1, List<T> list2, Function<T, ?>... keyExtractors) {
Comparator<T> comparator = createComparator(keyExtractors);
return intersection(list1, list2, comparator, keyExtractors);
}
/**
* 去重
*
* @param comparator 比较器
* @param keyExtractors 比较字段
*/
@SafeVarargs
public static <T> List<T> distinct(List<T> list, Comparator<T> comparator, Function<T, ?>... keyExtractors) {
if (CollectionUtil.isEmpty(list)) {
return new ArrayList<>();
}
Set<Wrapper<T>> wrapperSet = new LinkedHashSet<>(list.size() * 2);
for (T element : list) {
wrapperSet.add(new Wrapper<>(element, comparator, keyExtractors));
}
// 提取原始元素
List<T> result = new ArrayList<>(wrapperSet.size());
for (Wrapper<T> wrapper : wrapperSet) {
result.add(wrapper.target);
}
return result;
}
/**
* 去重
*
* @param keyExtractors 比较字段
*/
@SafeVarargs
public static <T> List<T> distinct(List<T> list, Function<T, ?>... keyExtractors) {
Comparator<T> comparator = createComparator(keyExtractors);
return distinct(list, comparator, keyExtractors);
}
/**
* 比较器
*
* @return 0:相等，-1:不相等
*/
@SafeVarargs
public static <T> Comparator<T> createComparator(Function<T, ?>... keyExtractors) {
return (o1, o2) -> {
for (Function<T, ?> keyExtractor : keyExtractors) {
Object key1 = keyExtractor.apply(o1);
Object key2 = keyExtractor.apply(o2);
if (!Objects.equals(key1, key2)) {
return -1;
}
}
return 0;
};
}
/**
* 切片
*
* @param num 每组分片数量
*/
public static <T> List<List<T>> sharding(List<T> list, int num) {
if (list == null || list.isEmpty() || num <= 0) {
return Collections.emptyList();
}
List<List<T>> result = new ArrayList<>((list.size() + num - 1) / num);
for (int i = 0; i < list.size(); i += num) {
result.add(list.subList(i, Math.min(i + num, list.size())));
}
return result;
}
/**
* 切片
* 默认500一组
*/
public static <T> List<List<T>> sharding(List<T> list) {
return sharding(list, 500);
}
/**
* 自适应切片
* 数据量级 | 策略 | 单组条数 | 总组数
* < 500 | 不切 | 500 | 1
* 501-20k | 自适应 | 500-2000 | 1-20
* >20k | 固定2000 | 2000 | >=10
*/
public static <T> List<List<T>> autoSharding(List<T> list) {
if (list == null || list.isEmpty()) {
return Collections.emptyList();
}
int size = list.size();
// 如果长度小于等于500，直接返回
if (size <= 500) {
return Collections.singletonList(list);
}
// 计算“理想组大小”：目标组数 10~20
int targetGroups = Math.min(20, Math.max(10, (int) Math.sqrt(size)));
int groupSize = (size + targetGroups - 1) / targetGroups;
// 把组大小钳在 500~2000
groupSize = Math.max(500, Math.min(2000, groupSize));
return sharding(list, groupSize);
}
/**
* 自适应切片，并单线程串行执行查询数据库
*
* @param list 参数列表
* @param queryFunction 查询函数，接收一个批次参数，返回该批次查询结果
* @param <T> 参数类型
* @param <V> 结果类型
* @return 合并后的查询结果集合
*/
public static <T, V> List<V> autoShardingToSelectDb(List<T> list, Function<List<T>, List<V>> queryFunction) {
if (list == null || list.isEmpty()) {
return Collections.emptyList();
}
// 自适应切片
List<List<T>> shards = autoSharding(list);
// 单线程串行执行
List<V> result = new ArrayList<>();
for (List<T> shard : shards) {
List<V> shardResult = queryFunction.apply(shard);
if (shardResult != null && !shardResult.isEmpty()) {
result.addAll(shardResult);
}
}
return result;
}
/**
* 自适应切片，并并行执行查询数据库
*
* @param list 参数列表
* @param queryFunction 查询函数
* @param executor 线程池执行器
* @param <T> 参数类型
* @param <V> 结果类型
* @return 合并后的查询结果集合
*/
public static <T, V> List<V> autoShardingToSelectDbParallel(
List<T> list,
Function<List<T>, List<V>> queryFunction,
Executor executor) {
if (list == null || list.isEmpty()) {
return Collections.emptyList();
}
// 自适应切片
List<List<T>> shards = autoSharding(list);
// 只有1个分片时，无需走线程池
if (shards.size() == 1) {
List<V> result = queryFunction.apply(shards.get(0));
return result != null ? result : Collections.emptyList();
}
// 并行执行
List<CompletableFuture<List<V>>> futures = shards.stream()
.map(shard -> CompletableFuture.supplyAsync(() -> {
try {
List<V> shardResult = queryFunction.apply(shard);
return shardResult != null ? shardResult : Collections.<V>emptyList();
} catch (Exception e) {
throw new RuntimeException("查询批次失败，批次大小: " + shard.size(), e);
}
}, executor))
.collect(Collectors.toList());
// 等待所有任务完成并合并结果
return futures.stream()
.map(CompletableFuture::join)
.flatMap(List::stream)
.collect(Collectors.toList());
}
/**
* 拷贝集合对象属性
*
* @param data 原始对象集合
* @param targetFactory 目标对象工厂（不能为 null）
* @param result 接收结果的集合（允许为 null）
* @param <T> 源类型
* @param <V> 目标类型
* @return 填充后的 result（不会返回 null）
*/
public static <T, V> List<V> copyProperties(List<T> data, Supplier<V> targetFactory, List<V> result) {
if (data == null || data.isEmpty()) {
return result == null ? Collections.emptyList() : result;
}
if (result == null) {
result = new ArrayList<>(data.size());
}
for (T src : data) {
V tgt = targetFactory.get();
BeanUtils.copyProperties(src, tgt);
result.add(tgt);
}
return result;
}
/**
* 包装器：equals/hashCode 都服从 comparator + keyExtractors
*/
private static final class Wrapper<T> {
private final T target;
private final Comparator<T> cmp;
private final Function<T, ?>[] extractors;
@SafeVarargs
Wrapper(T target, Comparator<T> cmp, Function<T, ?>... extractors) {
this.target = target;
this.cmp = cmp;
this.extractors = extractors;
}
@Override
public boolean equals(Object o) {
if (this == o) {
return true;
}
if (!(o instanceof Wrapper)) {
return false;
}
Wrapper<?> other = (Wrapper<?>) o;
return cmp.compare(this.target, (T) other.target) == 0;
}
@Override
public int hashCode() {
// 只用 comparator 实际比较的字段计算哈希
int h = 1;
for (Function<T, ?> ex : extractors) {
h = 31 * h + Objects.hashCode(ex.apply(target));
}
return h;
}
}
}
map工具类
- list转map
import io.seata.common.util.CollectionUtils;
import java.util.Collections;
import java.util.List;
import java.util.Map;
import java.util.function.Function;
import java.util.stream.Collectors;
/**
* map工具类
*/
public class MapUtil {
/**
* List 转成 Map
*
* @param list list数据
* @param keyMapper 取主键的 Lambda
* @param <T> 原始元素类型
* @param <K> 主键类型
* @return Map<主键, VO>
*/
public static <T, K> Map<K, T> listToMap(List<T> list, Function<T, K> keyMapper) {
if (CollectionUtils.isEmpty(list)) {
return Collections.emptyMap();
}
return list.stream().collect(Collectors.toMap(
keyMapper,
Function.identity(),
(oldVal, newVal) -> oldVal));
}
/**
* List 转成 Map
*
* @param list list数据
* @param keyMapper 取主键的 Lambda
* @param valueMapper 取值的 Lambda
* @param <T> 原始元素类型shi
* @param <K> 主键类型
* @param <V> 值类型
* @return Map<主键, 值>
*/
public static <T, K, V> Map<K, V> listToMap(List<T> list, Function<T, K> keyMapper, Function<T, V> valueMapper) {
if (CollectionUtils.isEmpty(list)) {
return Collections.emptyMap();
}
return list.stream().collect(Collectors.toMap(
keyMapper,
valueMapper,
(oldVal, newVal) -> oldVal));
}
}
文件工具类
- 下载
- 判断文件类型
import com.sinopec.sicii.wzcggl.config.MyWzcgglException;
import lombok.extern.slf4j.Slf4j;
import org.springframework.http.MediaType;
import org.springframework.util.MimeTypeUtils;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.net.URLEncoder;
import java.util.Locale;
/**
* 文件工具类
*/
@Slf4j
public class FileUtil {
public static void download(HttpServletResponse response, byte[] bytes, String fileName) {
if (bytes == null || bytes.length == 0) {
throw new MyWzcgglException("文件为空!");
}
try {
MediaType mediaType = resolveMediaType(fileName);
response.reset();
response.setContentType(mediaType.toString());
response.setContentLength(bytes.length);
response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8"));
try (ServletOutputStream outputStream = response.getOutputStream()) {
outputStream.write(bytes);
outputStream.flush();
}
} catch (IOException e) {
log.error("文件下载失败，fileName：{}，错误:{}", fileName, e.getMessage());
throw new MyWzcgglException("文件下载失败!");
}
}
private static MediaType resolveMediaType(String fileName) {
if (fileName == null) {
return MediaType.APPLICATION_OCTET_STREAM;
}
String ext = fileName
.substring(fileName.lastIndexOf('.') + 1)
.toLowerCase(Locale.ENGLISH);
switch (ext) {
case "pdf":
return MediaType.APPLICATION_PDF;
case "xls":
return MediaType.valueOf("application/vnd.ms-excel");
case "xlsx":
return MediaType.valueOf(
"application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
case "doc":
return MediaType.valueOf("application/msword");
case "docx":
return MediaType.valueOf(
"application/vnd.openxmlformats-officedocument.wordprocessingml.document");
case "ppt":
return MediaType.valueOf("application/vnd.ms-powerpoint");
case "pptx":
return MediaType.valueOf(
"application/vnd.openxmlformats-officedocument.presentationml.presentation");
case "txt":
return MediaType.TEXT_PLAIN;
case "png":
return MediaType.IMAGE_PNG;
case "jpg":
case "jpeg":
return MediaType.IMAGE_JPEG;
case "gif":
return MediaType.IMAGE_GIF;
case "zip":
return MediaType.valueOf("application/zip");
default:
String mime = MimeTypeUtils.parseMimeType(ext).toString();
return MediaType.parseMediaType(mime);
}
}
}
反射工具
import java.lang.reflect.Field;
import java.util.HashMap;
import java.util.Map;
/**
* 反射工具
*/
public class FieldUtil {
/**
* 获取包括父类在内的所有字段
*/
public static Map<String, Object> getFieldMap(Object obj) {
Map<String, Object> fieldMap = new HashMap<>();
if (obj == null) {
return fieldMap;
}
try {
Class<?> clazz = obj.getClass();
while (clazz != null && clazz != Object.class) {
Field[] fields = clazz.getDeclaredFields();
for (Field field : fields) {
field.setAccessible(true);
String fieldName = field.getName();
Object fieldValue = field.get(obj);
// 如果值为null，设为空字符串
if (fieldValue == null){
fieldValue = "";
}
// 处理Date类型且带有@JsonFormat注解的字段
if (fieldValue instanceof Date) {
JsonFormat jsonFormat = field.getAnnotation(JsonFormat.class);
if (jsonFormat != null) {
fieldValue = formatDate((Date) fieldValue, jsonFormat);
}
}
fieldMap.put(fieldName, fieldValue);
}
clazz = clazz.getSuperclass();
}
} catch (IllegalAccessException e) {
throw new RuntimeException(e);
}
return fieldMap;
}
/**
* 根据@JsonFormat注解格式化日期
*/
private static String formatDate(Date date, JsonFormat jsonFormat) {
if (date == null) {
return "";
}
String pattern = jsonFormat.pattern();
if (pattern.isEmpty()) {
// 默认格式
pattern = "yyyy-MM-dd HH:mm:ss";
}
SimpleDateFormat sdf = new SimpleDateFormat(pattern);
// 设置时区
String timezone = jsonFormat.timezone();
if (!timezone.isEmpty() && !timezone.equals(JsonFormat.DEFAULT_TIMEZONE)) {
sdf.setTimeZone(TimeZone.getTimeZone(timezone));
}
return sdf.format(date);
}
}
Validator 校验框架工具
import javax.validation.ConstraintViolation;
import javax.validation.ConstraintViolationException;
import javax.validation.Validator;
import cn.hutool.extra.spring.SpringUtil;
import lombok.AccessLevel;
import lombok.NoArgsConstructor;
import java.util.Set;
/**
* Validator 校验框架工具
*/
@NoArgsConstructor(access = AccessLevel.PRIVATE)
public class ValidatorUtil {
private static final Validator VALID = SpringUtil.getBean(Validator.class);
/**
* 对给定对象进行参数校验，并根据指定的校验组进行校验
*
* @param object 要进行校验的对象
* @param groups 校验组
* @throws ConstraintViolationException 如果校验不通过，则抛出参数校验异常
*/
public static <T> void validate(T object, Class<?>... groups) {
Set<ConstraintViolation<T>> validate = VALID.validate(object, groups);
if (!validate.isEmpty()) {
throw new ConstraintViolationException("参数校验异常", validate);
}
}
}
阿里excel工具类
import com.alibaba.excel.EasyExcel;
import com.esse.projects.inm.common.excel.DefaultExcelListener;
import com.esse.projects.inm.common.excel.ExcelListener;
import com.esse.projects.inm.common.excel.ExcelResult;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.InputStream;
import java.net.URLEncoder;
import java.util.List;
/**
* 阿里excel工具类
*/
public class EasyExcelUtil {
/**
* 同步导入(适用于小数据量)
*
* @param is 输入流
* @return 转换后集合
*/
public static <T> List<T> importExcel(InputStream is, Class<T> clazz) {
return EasyExcel.read(is).head(clazz).autoCloseStream(false).sheet().doReadSync();
}
/**
* 使用校验监听器 异步导入 同步返回
*
* @param is 输入流
* @param clazz 对象类型
* @param isValidate 是否 Validator 检验 默认为是
* @return 转换后集合
*/
public static <T> ExcelResult<T> importExcel(InputStream is, Class<T> clazz, boolean isValidate) {
DefaultExcelListener<T> listener = new DefaultExcelListener<>(isValidate);
EasyExcel.read(is, clazz, listener).sheet().doRead();
return listener.getExcelResult();
}
/**
* 使用自定义监听器 异步导入 自定义返回
*
* @param is 输入流
* @param clazz 对象类型
* @param listener 自定义监听器
* @return 转换后集合
*/
public static <T> ExcelResult<T> importExcel(InputStream is, Class<T> clazz, ExcelListener<T> listener) {
EasyExcel.read(is, clazz, listener).sheet().doRead();
return listener.getExcelResult();
}
/**
* 导出excel
*
* @param list 导出数据集合
* @param sheetName 工作表的名称
* @param clazz 实体类
*/
public static <T> void exportExcel(List<T> list, String sheetName, Class<T> clazz, HttpServletResponse response) throws IOException {
response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
response.setCharacterEncoding("utf-8");
// 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系
String fileName = URLEncoder.encode(sheetName, "UTF-8").replaceAll("\\+", "%20");
response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
EasyExcel.write(response.getOutputStream(), clazz)
.sheet(sheetName)
.doWrite(list);
}
}
import java.util.List;
/**
* excel返回对象
*/
public interface ExcelResult<T> {
/**
* 对象列表
*/
List<T> getList();
/**
* 错误列表
*/
List<String> getErrorList();
/**
* 导入回执
*/
String getAnalysis();
}
import com.alibaba.excel.read.listener.ReadListener;
/**
* Excel 导入监听
*/
public interface ExcelListener<T> extends ReadListener<T> {
ExcelResult<T> getExcelResult();
void addError(String error);
}
import lombok.Setter;
import java.util.ArrayList;
import java.util.List;
/**
* 默认excel返回对象
*/
public class DefaultExcelResult<T> implements ExcelResult<T> {
/**
* 数据对象list
*/
@Setter
private List<T> list;
/**
* 错误信息列表
*/
@Setter
private List<String> errorList;
public DefaultExcelResult() {
this.list = new ArrayList<>();
this.errorList = new ArrayList<>();
}
public DefaultExcelResult(List<T> list, List<String> errorList) {
this.list = list;
this.errorList = errorList;
}
public DefaultExcelResult(ExcelResult<T> excelResult) {
this.list = excelResult.getList();
this.errorList = excelResult.getErrorList();
}
@Override
public List<T> getList() {
return list;
}
@Override
public List<String> getErrorList() {
return errorList;
}
/**
* 获取导入回执
*
* @return 导入回执
*/
@Override
public String getAnalysis() {
int successCount = list.size();
int errorCount = errorList.size();
if (errorCount == 0) {
return "全部导入成功!";
} else {
if (successCount == 0) {
return "全部导入失败<br/>" + String.join("<br/>", errorList);
} else {
return "部分导入失败<br/>" + String.join("<br/>", errorList);
}
}
}
}
import cn.hutool.core.util.StrUtil;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.alibaba.excel.exception.ExcelDataConvertException;
import com.alibaba.fastjson.JSON;
import com.esse.projects.inm.common.util.ValidatorUtil;
import lombok.NoArgsConstructor;
import lombok.extern.slf4j.Slf4j;
import javax.validation.ConstraintViolation;
import javax.validation.ConstraintViolationException;
import java.util.Map;
import java.util.Set;
import java.util.stream.Collectors;
/**
* Excel 默认导入监听
*/
@Slf4j
@NoArgsConstructor
public class DefaultExcelListener<T> extends AnalysisEventListener<T> implements ExcelListener<T> {
/**
* 是否Validator检验，默认为是
*/
private Boolean isValidate = Boolean.TRUE;
/**
* excel 表头数据
*/
private Map<Integer, String> headMap;
/**
* 导入回执
*/
private ExcelResult<T> excelResult;
private static final String FORMAT = "第{}行数据校验异常: {}";
private static final String BUSINESS = "第{}行业务逻辑异常: {}";
public DefaultExcelListener(boolean isValidate) {
this.excelResult = new DefaultExcelResult<>();
this.isValidate = isValidate;
}
/**
* 处理异常
*
* @param exception ExcelDataConvertException
* @param context Excel 上下文
*/
@Override
public void onException(Exception exception, AnalysisContext context) {
if (exception instanceof ExcelDataConvertException) {
ExcelDataConvertException excelDataConvertException = (ExcelDataConvertException) exception;
// 如果是某一个单元格的转换异常 能获取到具体行号
Integer rowIndex = excelDataConvertException.getRowIndex();
Integer columnIndex = excelDataConvertException.getColumnIndex();
addError(StrUtil.format("第{}行-第{}列-表头{}: 解析异常<br/>",
rowIndex + 1, columnIndex + 1, headMap.get(columnIndex)));
} else if (exception instanceof ConstraintViolationException) {
ConstraintViolationException constraintViolationException = (ConstraintViolationException) exception;
Set<ConstraintViolation<?>> constraintViolations = constraintViolationException.getConstraintViolations();
String constraintViolationsMsg = constraintViolations.stream().map(ConstraintViolation::getMessage)
.collect(Collectors.joining(", "));
addFormatError(context.readRowHolder().getRowIndex() + 1, constraintViolationsMsg);
}
}
/**
* 处理表头
*/
@Override
public void invokeHeadMap(Map<Integer, String> headMap, AnalysisContext context) {
this.headMap = headMap;
log.debug("解析到一条表头数据: {}", JSON.toJSONString(headMap));
}
/**
* 处理数据
*/
@Override
public void invoke(T data, AnalysisContext context) {
if (isValidate) {
ValidatorUtil.validate(data);
}
excelResult.getList().add(data);
}
/**
* 所有数据分析完成后的回调
*/
@Override
public void doAfterAllAnalysed(AnalysisContext context) {
log.debug("所有数据解析完成！");
}
@Override
public ExcelResult<T> getExcelResult() {
return excelResult;
}
@Override
public void addError(String error) {
excelResult.getErrorList().add(error);
}
public void addFormatError(Integer rowIndex, String error) {
addError(StrUtil.format(FORMAT, rowIndex, error));
}
public void addBusinessError(Integer rowIndex, String error) {
addError(StrUtil.format(BUSINESS, rowIndex, error));
}
}
使用示例
import cn.afterturn.easypoi.excel.annotation.ExcelTarget;
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.write.style.ColumnWidth;
import com.fasterxml.jackson.annotation.JsonFormat;
import lombok.Data;
import lombok.EqualsAndHashCode;
import javax.validation.constraints.NotBlank;
import javax.validation.constraints.Pattern;
import javax.validation.constraints.Size;
import java.io.Serializable;
@EqualsAndHashCode(callSuper = true)
@Data
@ExcelTarget("***")
public class StableUnitExcel extends BaseExcel {
@ExcelProperty("类型*")
@ColumnWidth(20)
@NotBlank(message = "类型不能为空")
private String typeName;
@ExcelProperty("社会统一信用代码*")
@ColumnWidth(30)
@NotBlank(message = "社会统一信用代码不能为空")
private String unitIntegrityCode;
@ExcelProperty("单位名称")
@ColumnWidth(25)
private String unitName;
@ExcelProperty("项目编号*")
@ColumnWidth(25)
@NotBlank(message = "项目编号不能为空")
private String projectCode;
@ExcelProperty("项目名称")
@ColumnWidth(30)
private String projectName;
@ExcelProperty("禁用到期时间（不填为永久）")
@ColumnWidth(25)
@Pattern(message = "禁用到期时间格式错误，正确示例：2025-10-01", regexp = "^[0-9]{4}-(0[1-9]|1[0-2])-(0[1-9]|[12][0-9]|3[01])$")
private String forbiddenEndTime;
@ExcelProperty("事由*")
@ColumnWidth(50)
@NotBlank(message = "事由不能为空")
@Size(message = "事由长度不能大于2000", max = 2000)
private String remark;
}
import cn.hutool.core.util.StrUtil;
import cn.hutool.extra.spring.SpringUtil;
import com.alibaba.excel.context.AnalysisContext;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.collections4.CollectionUtils;
import org.springframework.beans.BeanUtils;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.Set;
import java.util.stream.Collectors;
/**
* 监听
*/
@Slf4j
public class StableGreyExcelListener extends DefaultExcelListener<StableGreyExcel> {
private final IStableGreyService stableGreyService;
private final RemoteEmployeeServer remoteEmployeeServer;
private final CmService cmService;
private final ProjectInfoService projectInfoService;
private final ProjectSystemDetailService projectSystemDetailService;
public StableGreyExcelListener() {
super(false);
this.remoteEmployeeServer = SpringUtil.getBean(RemoteEmployeeServer.class);
this.stableGreyService = SpringUtil.getBean(IStableGreyService.class);
this.cmService = SpringUtil.getBean(CmService.class);
this.projectInfoService = SpringUtil.getBean(ProjectInfoService.class);
this.projectSystemDetailService = SpringUtil.getBean(ProjectSystemDetailService.class);
}
@Override
public void invoke(StableGreyExcel data, AnalysisContext context) {
// 保存行号信息
data.setRowIndex(context.readRowHolder().getRowIndex() + 1);
// 格式校验
ValidatorUtil.validate(data);
getExcelResult().getList().add(data);
}
@Override
public void doAfterAllAnalysed(AnalysisContext context) {
List<StableGreyExcel> list = getExcelResult().getList();
// 批量查询单位信息、项目信息
Set<String> unitIntegrityCodeSet = list.stream().map(StableGreyExcel::getUnitIntegrityCode).collect(Collectors.toSet());
Set<String> projectCodeSet = list.stream().map(StableGreyExcel::getProjectCode).collect(Collectors.toSet());
Map<String, ContractorUnitVO> unitMap = MapUtil.listToMap(
cmService.getUnitListByIntegrityCodeList(new ArrayList<>(unitIntegrityCodeSet)).getData(),
ContractorUnitVO::getIntegrityCode
);
Map<String, ProjectInfoVO> projectMap = MapUtil.listToMap(
projectInfoService.getProjectByCodes2(String.join(",", projectCodeSet)),
ProjectInfoVO::getProjectInfoCode
);
Long tenantId = SecurityUtils.getUser().getSysTenantUserVO().getTenantId();
List<StableGreyEntity> insertList = new ArrayList<>();
for (StableGreyExcel stableGreyExcel : list) {
Integer rowIndex = stableGreyExcel.getRowIndex();
StableGreyEntity entity = new StableGreyEntity();
BeanUtils.copyProperties(stableGreyExcel, entity);
// 禁用到期时间
if (StrUtil.isNotBlank(stableGreyExcel.getForbiddenEndTime())) {
entity.setForbiddenEndTime(DateUtil.stringToDate(stableGreyExcel.getForbiddenEndTime()));
}
// 人员信息
List<IdNameOrgVO> userList = remoteEmployeeServer.listByidNo(stableGreyExcel.getIdentityCard(), tenantId).getData();
if (CollectionUtils.isEmpty(userList)) {
addBusinessError(rowIndex, "未找到相关人员信息");
continue;
}
IdNameOrgVO user = userList.get(0);
if (!stableGreyExcel.getGreyUserName().equals(user.getName())) {
addBusinessError(rowIndex, "人员姓名与身份证号不匹配");
continue;
}
entity.setGreyUserId(Long.parseLong(user.getId()));
// 单位信息
ContractorUnitVO unit = unitMap.get(stableGreyExcel.getUnitIntegrityCode());
if (unit == null) {
addBusinessError(rowIndex, "未找到该统一信用社会代码的单位");
continue;
}
if (StrUtil.isNotEmpty(stableGreyExcel.getUnitName())
&& !stableGreyExcel.getUnitName().equals(unit.getName())) {
addBusinessError(rowIndex, "统一信用社会代码与单位名称不匹配");
continue;
}
entity.setUnitId(unit.getId());
entity.setUnitName(unit.getName());
// 项目信息
if (StrUtil.isNotBlank(stableGreyExcel.getProjectCode())) {
ProjectInfoVO projectInfoVO = projectMap.get(stableGreyExcel.getProjectCode());
if (projectInfoVO == null) {
addBusinessError(rowIndex, "未找到该项目编码的项目信息");
continue;
}
if (StrUtil.isNotBlank(stableGreyExcel.getProjectName())
&& !stableGreyExcel.getProjectName().equals(projectInfoVO.getProjectInfoName())) {
addBusinessError(rowIndex, "项目编码和项目名称不匹配");
continue;
}
entity.setProjectId(projectInfoVO.getProjectInfoId());
entity.setProjectName(projectInfoVO.getProjectInfoName());
}
// 职务信息 如果未匹配到则不处理
if (StrUtil.isNotBlank(stableGreyExcel.getPostName()) && entity.getUnitId() != null) {
List<SimpleBeanVO> simpleBeanVOList = projectSystemDetailService.personListOfPerson(Long.parseLong(user.getId()), entity.getUnitId()).getData();
for (SimpleBeanVO simpleBeanVO : simpleBeanVOList) {
if (stableGreyExcel.getPostName().equals(simpleBeanVO.getName())) {
entity.setPostId(simpleBeanVO.getId());
}
}
}
insertList.add(entity);
}
if (CollectionUtils.isNotEmpty(insertList)) {
stableGreyService.saveBatch(insertList);
}
log.info("所有数据解析完成！");
}
}
993
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
