com.ly.mybatis.mapperservice.util.MPSExcelUtil Maven / Gradle / Ivy
The newest version!
package com.ly.mybatis.mapperservice.util;
import cn.afterturn.easypoi.excel.ExcelExportUtil;
import cn.afterturn.easypoi.excel.ExcelImportUtil;
import cn.afterturn.easypoi.excel.annotation.Excel;
import cn.afterturn.easypoi.excel.entity.ExportParams;
import cn.afterturn.easypoi.excel.entity.ImportParams;
import cn.hutool.core.io.IoUtil;
import cn.hutool.crypto.digest.DigestUtil;
import com.ly.mybatis.mapperservice.service.ExcelFieldSelector;
import org.apache.poi.hssf.usermodel.DVConstraint;
import org.apache.poi.hssf.usermodel.HSSFDataValidation;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.springframework.util.StringUtils;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.lang.reflect.Field;
import java.net.URLEncoder;
import java.util.*;
import java.util.function.Consumer;
import java.util.stream.Collectors;
import java.util.stream.Stream;
@SuppressWarnings("unused")
public class MPSExcelUtil {
public static void importExcel(
ImportParams params,
InputStream inputStream,
Class pojoClass,
Consumer> consumer
) {
if (inputStream == null) {
return;
}
ByteArrayOutputStream baos = new ByteArrayOutputStream();
IoUtil.copy(inputStream, baos);
ByteArrayInputStream userIs = new ByteArrayInputStream(baos.toByteArray());
try {
Workbook workbook = WorkbookFactory.create(userIs);
Sheet digest = workbook.getSheet("digest");
if (digest == null) {
throw new RuntimeException("请下载模板再编辑上传");
}
Row row = digest.getRow(0);
if (row == null) {
throw new RuntimeException("请下载模板再编辑上传");
}
Cell cell = row.getCell(0);
if (cell == null) {
throw new RuntimeException("请下载模板再编辑上传");
}
String d = cell.getStringCellValue();
String d2 = DigestUtil.md5Hex(getDigest(workbook));
if (!Objects.equals(d, d2)) {
throw new RuntimeException("请下载模板再编辑上传");
}
userIs.reset();
List list = ExcelImportUtil.importExcel(userIs, pojoClass, params);
consumer.accept(list);
} catch (IOException e) {
e.printStackTrace();
throw new RuntimeException(e);
} catch (NoSuchElementException e) {
throw new RuntimeException("模板不能为空");
} catch (Exception e) {
e.printStackTrace();
throw new RuntimeException(e.getMessage());
} finally {
IoUtil.close(inputStream);
IoUtil.close(baos);
IoUtil.close(userIs);
}
}
public static void exportExcel(
String fileName,
String sheetName,
Class pojoClass,
List selectors,
List list,
HttpServletResponse response
) {
ExportParams exportParams = new ExportParams(null, sheetName);
if (list == null) {
list = new ArrayList<>();
}
Workbook workbook = ExcelExportUtil.exportExcel(exportParams, pojoClass, list);
if (workbook != null) {
if (selectors != null) {
List fields = new ArrayList<>();
Class itoClass = pojoClass;
while (itoClass != Object.class) {
Field[] fs = itoClass.getDeclaredFields();
fields.addAll(Arrays.asList(fs));
itoClass = itoClass.getSuperclass();
}
Map nameMap = fields.parallelStream()
.map(field -> {
String fieldName = field.getName();
try {
Excel excel = field.getAnnotation(Excel.class);
String excelName = excel.name();
if (StringUtils.hasText(excelName)) {
return new String[]{fieldName, excelName};
}
} catch (NullPointerException ignored) {
}
return null;
})
.filter(Objects::nonNull)
.collect(Collectors.toMap(ss -> ss[0], ss -> ss[1], (a, b) -> a));
Map> selectMap = selectors.parallelStream()
.map(selector -> {
if (selector != null &&
selector.name() != null &&
nameMap.containsKey(selector.name())) {
List values = new ArrayList<>();
if (selector.defaultValue() != null) {
values.add(selector.defaultValue());
}
if (selector.supplier() != null) {
Collection v;
try {
v = selector.supplier().get();
} catch (Exception e) {
e.printStackTrace();
v = null;
}
if (v != null) {
values.addAll(v);
}
}
if (!values.isEmpty()) {
return new Object[]{
nameMap.get(selector.name()),
values
};
}
}
return null;
})
.filter(Objects::nonNull)
.collect(Collectors.toMap(
os -> (String) os[0],
os -> (List) os[1],
(a, b) -> a
));
Sheet sheet = workbook.getSheetAt(0);
Row titleRow = sheet.getRow(0);
Map> indexSelectors = new HashMap<>();
for (int i = 0; i < titleRow.getLastCellNum(); i++) {
try {
String title = titleRow.getCell(i).getStringCellValue();
List strings = selectMap.get(title);
if (Objects.nonNull(strings)) {
indexSelectors.put(i, strings);
}
} catch (Exception ignored) {
}
}
indexSelectors.forEach((k, v) -> {
Sheet sheetHelper = workbook.createSheet("hidden" + k);
Cell cell;
for (int i = 0; i < v.size(); i++) {
Row row = sheetHelper.createRow(i);
cell = row.createCell(0);
cell.setCellValue(v.get(i));
}
Name name = workbook.createName();
name.setNameName("hidden" + k);
name.setRefersToFormula("hidden" + k + "!$A$1:$A$" + v.size());
DVConstraint dvConstraint = DVConstraint.createFormulaListConstraint("hidden" + k);
CellRangeAddressList regions = new CellRangeAddressList(1, 99999, k, k);
HSSFDataValidation dataValidation = new HSSFDataValidation(regions, dvConstraint);
// 对sheet页生效
sheet.addValidationData(dataValidation);
workbook.setSheetHidden(workbook.getSheetIndex(sheetHelper), true);
});
}
Sheet digest = workbook.createSheet("digest");
Row digestRow = digest.createRow(0);
Cell digestCell = digestRow.createCell(0);
digestCell.setCellValue(DigestUtil.md5Hex(getDigest(workbook)));
workbook.setSheetHidden(workbook.getSheetIndex(digest), true);
downLoadExcel(fileName, response, workbook);
}
}
public static void downLoadExcel(String fileName, HttpServletResponse response, Workbook workbook) {
try {
response.setCharacterEncoding("UTF-8");
response.setHeader("content-Type", "application/vnd.ms-excel");
response.setHeader(
"Content-Disposition",
"attachment;filename=" + URLEncoder.encode(fileName + ".xls", "UTF-8")
);
workbook.write(response.getOutputStream());
} catch (Exception e) {
throw new RuntimeException(e.getMessage());
}
}
public static String getDigest(Workbook workbook) {
int sheetNum = workbook.getNumberOfSheets();
String title = "";
String constraint = "";
Map map = new HashMap<>();
for (int s = 0; s < sheetNum; s++) {
Sheet sheet = workbook.getSheetAt(s);
if (!Objects.equals("digest", sheet.getSheetName())) {
if (s == 0) {
StringJoiner joiner = new StringJoiner(",", "[", "]");
Row row = sheet.getRow(0);
short cellNum = row.getLastCellNum();
for (int c = row.getFirstCellNum(); c < cellNum; c++) {
Cell cell = row.getCell(c);
String content;
try {
content = cell.getStringCellValue();
} catch (Exception e) {
content = cell.getNumericCellValue() + "";
}
joiner.add(content);
}
title = joiner.toString();
constraint = sheet.getDataValidations().parallelStream()
.map(dataValidation -> {
CellRangeAddressList regions = dataValidation.getRegions();
return Stream.concat(Stream.of(regions.getCellRangeAddresses())
.map(CellRangeAddress::formatAsString),
Stream.of(dataValidation.getValidationConstraint()
.getFormula1(),
dataValidation.getValidationConstraint()
.getFormula2()
))
.filter(Objects::nonNull)
.collect(Collectors.joining(",", "[", "]"));
})
.collect(Collectors.joining(",", "[", "]"));
} else {
StringJoiner joiner = new StringJoiner(",", sheet.getSheetName() + "[", "]");
int rowNum = sheet.getLastRowNum();
for (int r = sheet.getFirstRowNum(); r < rowNum; r++) {
Row row = sheet.getRow(r);
short cellNum = row.getLastCellNum();
for (int c = row.getFirstCellNum(); c < cellNum; c++) {
Cell cell = row.getCell(c);
String content;
try {
content = cell.getStringCellValue();
} catch (Exception e) {
content = cell.getNumericCellValue() + "";
}
joiner.add(content);
}
}
map.put(sheet.getSheetName(), joiner.toString());
}
}
}
return Stream.concat(Stream.of(title, constraint), map.entrySet().parallelStream()
.sorted(Map.Entry.comparingByKey())
.map(Map.Entry::getValue))
.collect(Collectors.joining(",", "{", "}"));
}
}
© 2015 - 2025 Weber Informatics LLC | Privacy Policy