Excel导入导出工具
设计理念
在我们平时的软件开发过程中会遇到许多数据的导入与导出，大批量的数据导入导出就会牵扯到数据的校验问题。所以怎样能完美而优雅的返回错误数据并且能让用户一目了然就成了在开发过程中不断完善的技术。
实现效果
代码实现
- 引入jar包
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.17</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.17</version>
</dependency>
<dependency>
<groupId>org.hibernate</groupId>
<artifactId>hibernate-validator</artifactId>
<version>5.3.6.Final</version>
</dependency>
<dependency>
<groupId>org.glassfish.web</groupId>
<artifactId>javax.el</artifactId>
<version>2.2.4</version>
</dependency>
引入公共java类
1.ExcelColumn 类 用户封装excel表头
@Retention(RUNTIME)
@Target({ FIELD, METHOD })
public @interface ExcelColumn {
/**
* 列名称，显示在列头上
*/
String name() default "";
/**
* 备注信息，显示在列头上
*/
String notes() default "";
/**
* 列宽（大致按英文字数计算），等于0时为自动宽度
*/
int width() default 0;
/**
* 水平对齐方式
*/
ExcelColumnHorizontalAlignment horizontalAlignment() default ExcelColumnHorizontalAlignment.CENTER;
/**
* 提供可选项值的类，如果有值，这个列会有下拉列表
*/
Class<? extends ExcelColumnSelectionProvider> selectionProvider() default NullExcelColumnSelectionProvider.class;
/**
* 可选项类型，会作为参数传给{{@link #selectionProvider()}指定的类
*/
String selectionType() default "";
/**
* 当可选项是级联选择的时候，指定上级的字段名称。
*/
String selectionRefField() default "";
/**
* 日期格式
*/
String dateFormat() default "";
}
2.ExcelColumnHorizontalAlignment Excel列水平对齐方式
public enum ExcelColumnHorizontalAlignment {
LEFT, CENTER, RIGHT
}
3.ExcelColumnSelectionItem Excel模型某个列的可选项值
public class ExcelColumnSelectionItem {
/** 实际值 */
private String value;
/** 显示名称 */
private String name;
/** 联动上级实际值，用于级联选择的场景 */
private String refValue;
/** 联动上级显示名称，用于级联选择的场景 */
private String refName;
public ExcelColumnSelectionItem() {
}
public ExcelColumnSelectionItem(String value, String name) {
this.value = value;
this.name = name;
}
public ExcelColumnSelectionItem(String value, String name, String refValue, String refName) {
this.value = value;
this.name = name;
this.refValue = refValue;
this.refName = refName;
}
protected String getValue() {
return value;
}
protected void setValue(String value) {
this.value = value;
}
protected String getName() {
return name;
}
protected void setName(String name) {
this.name = name;
}
protected String getRefValue() {
return refValue;
}
protected void setRefValue(String refValue) {
this.refValue = refValue;
}
protected String getRefName() {
return refName;
}
protected void setRefName(String refName) {
this.refName = refName;
}
}
4.ExcelColumnSelectionProvider exce列可选项提供者接口
public interface ExcelColumnSelectionProvider {
/**
* 获取所有可选值
* <p>
* 对于非级联选择，返回的可选值对象包含value和name即可。
* <p>
* 对于级联选择，要返回全部的可选值对象，每个对象要包含value, name, refValue, refName。
*
* @param type 可选值类型
* @return 所有可选值列表
*/
List<ExcelColumnSelectionItem> selectionItems(String type);
}
5.ExcelImportExportTools Excel导入导出工具
package com.adps.common.excel;
import cn.hutool.core.util.ObjectUtil;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.hibernate.validator.HibernateValidator;
import org.springframework.web.multipart.MultipartFile;
import javax.servlet.http.HttpServletResponse;
import javax.validation.ConstraintViolation;
import javax.validation.Validation;
import javax.validation.Validator;
import javax.validation.ValidatorFactory;
import java.io.*;
import java.lang.reflect.Field;
import java.math.BigDecimal;
import java.net.URLEncoder;
import java.text.ParseException;
import java.util.*;
/**
* @package: com.adps.common.excel
* @program: mtws-boot
* @author: fyh
* @date: 2023/8/9
* @description: Excel导入导出工具
**/
public class ExcelImportExportTools {
/**
* 导入模板填入多少行
*/
private static final int TEMPLATE_DATA_ROWS = 100;
private static Map<Class<?>, ExcelColumnSelectionProvider> selectionProviderMap = new HashMap<Class<?>, ExcelColumnSelectionProvider>();
private static ValidatorFactory validatorFactory = Validation.byProvider(HibernateValidator.class).configure()
.buildValidatorFactory();
/**
* 生成导入模板文件
*
* @param modelClasses 导入模型信息，每个模型针对一个sheet页
* @return 导入模板文件内容
*/
public static byte[] impTemplate(Class<?>... modelClasses) {
// 导入模板实际就是导出若干个空对象
List<List<Object>> data = new ArrayList<List<Object>>(modelClasses.length);
for (int i = 0; i < modelClasses.length; i++) {
List<Object> sheetData = Arrays.asList(new Object[TEMPLATE_DATA_ROWS]);
data.add(sheetData);
}
return exp(data, modelClasses);
}
/**
* 生成导入模板文件（指定行数）
*
* @param modelClasses 导入模型信息，每个模型针对一个sheet页
* @return 导入模板文件内容
*/
public static byte[] impTemplateAppointRow(int row, Class<?>... modelClasses) {
// 导入模板实际就是导出若干个空对象
List<List<Object>> data = new ArrayList<List<Object>>(modelClasses.length);
for (int i = 0; i < modelClasses.length; i++) {
List<Object> sheetData = Arrays.asList(new Object[row]);
data.add(sheetData);
}
return exp(data, modelClasses);
}
/**
* 将导入的Excel转换为模型对象
*
* @param excelInput Excel文件输入流
* @param modelClasses 导入模型信息，每个模型针对一个sheet页，如果有某个sheet页无需导入，可以传一个null值表示跳过
* @return 转换后的模型对象，外侧List每个元素针对一个sheet页（无需导入的也会有个null值），内侧元素的List表示每个sheet页转换成的模型数据
*/
public static List<List<Object>> imp(InputStream excelInput, Class<?>... modelClasses) {
try {
List<List<Object>> result = new ArrayList<List<Object>>();
Workbook wb = new XSSFWorkbook(excelInput);
for (int i = 0; i < modelClasses.length; i++) {
if (modelClasses[i] == null) {
result.add(null);
} else {
Sheet sheet = wb.getSheetAt(i);
List<Object> list = importSheet(wb, sheet, modelClasses[i]);
result.add(list);
}
}
return result;
} catch (RuntimeException e) {
throw e;
} catch (Exception e) {
throw new RuntimeException(e);
}
}
/**
* 将导入的Excel转换为模型对象
*
* @param excelFile Excel文件内容
* @param modelClasses 导入模型信息，每个模型针对一个sheet页，如果有某个sheet页无需导入，可以传一个null值表示跳过
* @return 转换后的模型对象，外侧List每个元素针对一个sheet页（无需导入的也会有个null值），内侧元素的List表示每个sheet页转换成的模型数据
*/
public static List<List<Object>> imp(byte[] excelFile, Class<?>... modelClasses) {
return imp(new ByteArrayInputStream(excelFile), modelClasses);
}
/**
* 将模型对象导出成Excel文件
*
* @param data 模型对象，外侧List每个元素针对一个sheet页，内侧元素的List表示每个sheet页中的模型数据
* @param modelClasses 导出的模型信息，每个模型针对一个sheet页
* @return 导出的Excel文件内容
*/
public static byte[] exp(List<List<Object>> data, Class<?>... modelClasses) {
try {
if (data == null) {
throw new IllegalArgumentException("data不能为null");
}
if (modelClasses == null) {
throw new IllegalArgumentException("modelClasses不能为null");
}
if (data.size() != modelClasses.length) {
throw new IllegalArgumentException("modelClasses个数必须与data对应");
}
Workbook wb = new XSSFWorkbook();
for (Class<?> modelClass : modelClasses) {
makeExportSheet(wb, modelClass);
}
int i = 0;
for (Class<?> modelClass : modelClasses) {
exportSheet(wb, wb.getSheetAt(i), modelClass, data.get(i));
i++;
}
ByteArrayOutputStream baos = new ByteArrayOutputStream();
wb.write(baos);
return baos.toByteArray();
} catch (RuntimeException e) {
throw e;
} catch (Exception e) {
throw new RuntimeException(e);
}
}
/**
* 创建导出sheet页
*/
private static Sheet makeExportSheet(Workbook wb, Class<?> modelClass) throws Exception {
ExcelSheet sheetInfo = modelClass.getAnnotation(ExcelSheet.class);
if (sheetInfo == null) {
throw new IllegalArgumentException("Excel模型类" + modelClass.getName() + "未包含@ExcelSheet标注信息");
}
String sheetName = sheetInfo.name();
return wb.createSheet(sheetName);
}
/**
* 导入模板单sheet处理
*/
private static void exportSheet(Workbook wb, Sheet sheet, Class<?> modelClass, List<Object> data) throws Exception {
// data不能为null或空数组，如果是这种情况插入一个元素（元素可以为null）作为样例行
if (data == null || data.isEmpty()) {
data = new ArrayList<>();
data.add(null);
}
List<ExcelColumnInfo> columnInfoList = getColumnInfos(modelClass);
// 样式
CellStyle[] headerCellStyles = new CellStyle[columnInfoList.size()];
CellStyle[] dataCellStyles = new CellStyle[columnInfoList.size()];
CellStyle[] errorRowNormalCellStyles = new CellStyle[columnInfoList.size()];
CellStyle[] errorRowErrorCellStyles = new CellStyle[columnInfoList.size()];
CellStyle errorMessageCellStyle = getErrorMessageCellStyle(wb);
int columnNum = 0;
for (ExcelColumnInfo columnInfo : columnInfoList) {
headerCellStyles[columnNum] = getHeaderCellStyle(wb, columnInfo);
dataCellStyles[columnNum] = getCellStyle(wb, columnInfo);
errorRowNormalCellStyles[columnNum] = getErrorRowNormalCellStyle(wb, columnInfo);
errorRowErrorCellStyles[columnNum] = getErrorRowErrorCellStyle(wb, columnInfo);
columnNum++;
}
// 表头
Drawing<?> drawing = sheet.createDrawingPatriarch();
CreationHelper creationHelper = wb.getCreationHelper();
Row headerRow = sheet.createRow(0);
columnNum = 0;
for (ExcelColumnInfo columnInfo : columnInfoList) {
Cell cell = headerRow.createCell(columnNum, CellType.STRING);
cell.setCellValue(columnInfo.annotation.name());
cell.setCellStyle(headerCellStyles[columnNum]);
if (Utils.isNotBlank(columnInfo.annotation.notes())) {
ClientAnchor anchor = creationHelper.createClientAnchor();
anchor.setCol1(cell.getColumnIndex());
anchor.setCol2(cell.getColumnIndex() + 3);
anchor.setRow1(cell.getRowIndex() + 1);
anchor.setRow2(cell.getRowIndex() + 4);
Comment comment = drawing.createCellComment(anchor);
comment.setString(creationHelper.createRichTextString(columnInfo.annotation.notes()));
comment.setVisible(false);
cell.setCellComment(comment);
}
columnNum++;
}
// 数据行
for (int i = 0; i < data.size(); i++) {
Object item = data.get(i);
boolean hasError = item != null && item instanceof ExcelModelBase && ((ExcelModelBase) item).hasError();
Row row = sheet.createRow(i + 1);
columnNum = 0;
for (ExcelColumnInfo columnInfo : columnInfoList) {
String fieldName = columnInfo.field.getName();
Class<?> fieldType = columnInfo.field.getType();
Cell cell = null;
if (hasError && ((ExcelModelBase) item).hasFieldError(columnInfo.field.getName())) {
cell = row.createCell(columnNum, CellType.STRING);
cell.setCellStyle(errorRowErrorCellStyles[columnNum]);
cell.setCellValue(((ExcelModelBase) item).getOriginalValue(fieldName));
} else {
if (Number.class.isAssignableFrom(fieldType) || Date.class.isAssignableFrom(fieldType)) {
cell = row.createCell(columnNum, CellType.NUMERIC);
} else {
cell = row.createCell(columnNum, CellType.STRING);
}
if (hasError) {
cell.setCellStyle(errorRowNormalCellStyles[columnNum]);
} else {
cell.setCellStyle(dataCellStyles[columnNum]);
}
// 设值
if (item != null) {
Object cellValue = columnInfo.field.get(item);
if (cellValue != null) {
if (cellValue instanceof Date) {
cell.setCellValue((Date) cellValue);
} else if (cellValue instanceof Number) {
cell.setCellValue(((Number) cellValue).doubleValue());
} else {
List<ExcelColumnSelectionItem> selectionItems = columnInfo.getSelectionItems();
if (selectionItems != null) {
for (ExcelColumnSelectionItem selectionItem : selectionItems) {
if (Utils.equals(selectionItem.getValue(), cellValue.toString())) {
cellValue = selectionItem.getName();
break;
}
}
}
cell.setCellValue(cellValue.toString());
}
}
}
}
columnNum++;
}
if (hasError) {
ExcelModelBase modelBase = (ExcelModelBase) item;
Cell cell = row.createCell(columnNum, CellType.STRING);
cell.setCellStyle(errorMessageCellStyle);
String allErrorMessages = Utils.join(modelBase.getAllErrors(), ";");
cell.setCellValue(allErrorMessages);
}
}
// 可选项
columnNum = 0;
for (ExcelColumnInfo columnInfo : columnInfoList) {
if (columnInfo.annotation.selectionProvider() != null) {
ExcelColumnSelectionProvider provider = getExcelColumnSelectionProvider(
columnInfo.annotation.selectionProvider());
List<ExcelColumnSelectionItem> items = provider.selectionItems(columnInfo.annotation.selectionType());
if (items != null) {
if (Utils.isBlank(columnInfo.annotation.selectionRefField())) {
// 非级联下拉
String baseName = createRefSheetWithNames(wb, columnInfo);
DataValidationConstraint dvc = sheet.getDataValidationHelper()
.createFormulaListConstraint(baseName);
CellRangeAddressList range = new CellRangeAddressList(1, data.size(), columnNum, columnNum);
sheet.addValidationData(sheet.getDataValidationHelper().createValidation(dvc, range));
} else {
// 级联查询，需要创建参照sheet
String baseName = createRefSheetWithNames(wb, columnInfo);
int refColumnIndex = -1;
for (int i = 0; i < columnInfoList.size(); i++) {
if (Utils.equals(columnInfo.annotation.selectionRefField(),
columnInfoList.get(i).field.getName())) {
refColumnIndex = i;
break;
}
}
if (refColumnIndex < 0) {
throw new RuntimeException(
"selectionRefField指定的字段" + columnInfo.annotation.selectionRefField() + "不存在");
}
String refColumnName = numberToColumnHead(refColumnIndex + 1);
String formula = "INDIRECT(CONCATENATE(\"" + baseName + "_\",$" + refColumnName + "2))";
DataValidationConstraint dvc = sheet.getDataValidationHelper()
.createFormulaListConstraint(formula);
CellRangeAddressList range = new CellRangeAddressList(1, data.size(), columnNum, columnNum);
sheet.addValidationData(sheet.getDataValidationHelper().createValidation(dvc, range));
}
}
}
columnNum++;
}
// 宽度设置
columnNum = 0;
for (ExcelColumnInfo columnInfo : columnInfoList) {
if (columnInfo.annotation.width() > 0) {
sheet.setColumnWidth(columnNum, columnInfo.annotation.width() * 256);
} else {
sheet.autoSizeColumn(columnNum);
}
columnNum++;
}
// 最后一列（错误信息）
sheet.setColumnWidth(columnNum, 25600);
}
/**
* 导入单sheet处理
*/
private static List<Object> importSheet(Workbook wb, Sheet sheet, Class<?> modelClass) throws Exception {
List<Object> result = new ArrayList<Object>();
List<ExcelColumnInfo> columnInfos = getColumnInfos(modelClass);
for (int r = 1; r <= sheet.getLastRowNum(); r++) {
Row row = sheet.getRow(r);
if (row != null) {
Object model = modelClass.newInstance();
boolean isAllNull = true;
for (int c = 0; c < columnInfos.size(); c++) {
Cell cell = row.getCell(c);
ExcelColumnInfo columnInfo = columnInfos.get(c);
if (readCellValueToModel(wb, cell, columnInfo, model)) {
isAllNull = false;
}
}
if (!isAllNull) {
// 集成Validator校验
if (model instanceof ExcelModelBase) {
ExcelModelBase modelBase = (ExcelModelBase) model;
Validator validator = validatorFactory.getValidator();
Set<ConstraintViolation<Object>> violations = validator.validate(model);
for (ConstraintViolation<Object> violation : violations) {
String fieldName = violation.getPropertyPath().iterator().next().getName();
String error = violation.getMessage();
// 如果已经有解析错误了，先不增加额外的错误信息
if (!modelBase.hasFieldError(fieldName)) {
modelBase.addFieldError(fieldName, error);
}
}
}
result.add(model);
}
}
}
return result;
}
/**
* 获取模型对象所有标注属性
*/
private static List<ExcelColumnInfo> getColumnInfos(Class<?> modelClass) throws Exception {
List<ExcelColumnInfo> infoList = new ArrayList<ExcelColumnInfo>();
Field[] fields = modelClass.getDeclaredFields();
for (Field field : fields) {
ExcelColumn annotation = field.getAnnotation(ExcelColumn.class);
if (annotation != null) {
ExcelColumnInfo info = new ExcelColumnInfo();
field.setAccessible(true);
info.annotation = annotation;
info.field = field;
infoList.add(info);
}
}
return infoList;
}
/**
* 导入模板及导出列头单元格样式
*/
private static CellStyle getHeaderCellStyle(Workbook wb, ExcelColumnInfo columnInfo) {
CellStyle style = wb.createCellStyle();
if (columnInfo.annotation.horizontalAlignment() == ExcelColumnHorizontalAlignment.CENTER) {
style.setAlignment(HorizontalAlignment.CENTER);
} else if (columnInfo.annotation.horizontalAlignment() == ExcelColumnHorizontalAlignment.LEFT) {
style.setAlignment(HorizontalAlignment.LEFT);
} else if (columnInfo.annotation.horizontalAlignment() == ExcelColumnHorizontalAlignment.RIGHT) {
style.setAlignment(HorizontalAlignment.RIGHT);
}
style.setBorderLeft(BorderStyle.THIN);
style.setBorderRight(BorderStyle.THIN);
style.setBorderTop(BorderStyle.THIN);
style.setBorderBottom(BorderStyle.THIN);
style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.index);
style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
Font font = wb.createFont();
font.setBold(true);
style.setFont(font);
return style;
}
/**
* 导入模板及导出数据单元格样式
*/
@SuppressWarnings("unchecked")
private static CellStyle getCellStyle(Workbook wb, ExcelColumnInfo columnInfo) {
CellStyle style = wb.createCellStyle();
if (columnInfo.annotation.horizontalAlignment() == ExcelColumnHorizontalAlignment.CENTER) {
style.setAlignment(HorizontalAlignment.CENTER);
} else if (columnInfo.annotation.horizontalAlignment() == ExcelColumnHorizontalAlignment.LEFT) {
style.setAlignment(HorizontalAlignment.LEFT);
} else if (columnInfo.annotation.horizontalAlignment() == ExcelColumnHorizontalAlignment.RIGHT) {
style.setAlignment(HorizontalAlignment.RIGHT);
}
style.setBorderLeft(BorderStyle.THIN);
style.setBorderRight(BorderStyle.THIN);
style.setBorderTop(BorderStyle.THIN);
style.setBorderBottom(BorderStyle.THIN);
if (Utils.isNotBlank(columnInfo.annotation.dateFormat())) {
// 自定义日期格式
style.setDataFormat(
wb.getCreationHelper().createDataFormat().getFormat(columnInfo.annotation.dateFormat())
);
} else if (String.class.equals(columnInfo.field.getType())) {
// 文本
style.setDataFormat((short) BuiltinFormats.getBuiltinFormat("@"));
} else if (Date.class.equals(columnInfo.field.getType())) {
// 日期
style.setDataFormat(wb.getCreationHelper().createDataFormat().getFormat("yyyy-MM-dd"));
} else if (Arrays.asList(Integer.class, Integer.TYPE, Long.class, Long.TYPE)
.contains(columnInfo.field.getType())) {
style.setDataFormat((short) BuiltinFormats.getBuiltinFormat("0"));
} else if (Arrays.asList(Double.class, Double.TYPE, Float.class, Float.TYPE)
.contains(columnInfo.field.getType())) {
style.setDataFormat((short) BuiltinFormats.getBuiltinFormat("0.00"));
} else {
// 常规
style.setDataFormat((short) 0);
}
return style;
}
/**
* 导出时错误行正常单元格样式
*/
private static CellStyle getErrorRowNormalCellStyle(Workbook wb, ExcelColumnInfo columnInfo) {
CellStyle style = getCellStyle(wb, columnInfo);
style.setFillForegroundColor(IndexedColors.LIGHT_ORANGE.index);
style.setFillPattern(FillPatternType.FINE_DOTS);
return style;
}
/**
* 导出时错误行错误单元格样式
*/
private static CellStyle getErrorRowErrorCellStyle(Workbook wb, ExcelColumnInfo columnInfo) {
CellStyle style = wb.createCellStyle();
if (columnInfo.annotation.horizontalAlignment() == ExcelColumnHorizontalAlignment.CENTER) {
style.setAlignment(HorizontalAlignment.CENTER);
} else if (columnInfo.annotation.horizontalAlignment() == ExcelColumnHorizontalAlignment.LEFT) {
style.setAlignment(HorizontalAlignment.LEFT);
} else if (columnInfo.annotation.horizontalAlignment() == ExcelColumnHorizontalAlignment.RIGHT) {
style.setAlignment(HorizontalAlignment.RIGHT);
}
style.setBorderLeft(BorderStyle.THIN);
style.setBorderRight(BorderStyle.THIN);
style.setBorderTop(BorderStyle.THIN);
style.setBorderBottom(BorderStyle.THIN);
Font font = wb.createFont();
font.setBold(true);
font.setColor(IndexedColors.RED.getIndex());
style.setFont(font);
style.setFillForegroundColor(IndexedColors.LIGHT_ORANGE.index);
style.setFillPattern(FillPatternType.FINE_DOTS);
return style;
}
/**
* 导出时显示错误信息单元格样式
*/
private static CellStyle getErrorMessageCellStyle(Workbook wb) {
CellStyle style = wb.createCellStyle();
style.setAlignment(HorizontalAlignment.LEFT);
style.setBorderLeft(BorderStyle.THIN);
style.setBorderRight(BorderStyle.THIN);
style.setBorderTop(BorderStyle.THIN);
style.setBorderBottom(BorderStyle.THIN);
Font font = wb.createFont();
font.setBold(true);
font.setColor(IndexedColors.RED.getIndex());
style.setFont(font);
return style;
}
/**
* 获取列可选项提供者
*/
private static ExcelColumnSelectionProvider getExcelColumnSelectionProvider(
Class<? extends ExcelColumnSelectionProvider> selectionProviderClass) {
ExcelColumnSelectionProvider selectionProvider = selectionProviderMap.get(selectionProviderClass);
if (selectionProvider == null) {
synchronized (selectionProviderMap) {
selectionProvider = selectionProviderMap.get(selectionProviderClass);
if (selectionProvider == null) {
// 如果获取不到，直接用new
if (selectionProvider == null) {
try {
selectionProvider = selectionProviderClass.newInstance();
} catch (Exception e) {
throw new RuntimeException(e);
}
}
selectionProviderMap.put(selectionProviderClass, selectionProvider);
}
}
}
return selectionProvider;
}
/**
* 创建关联sheet页（隐藏）及相关的“名称”（Excel名称管理器中的那种）
*/
private static String createRefSheetWithNames(Workbook wb, ExcelColumnInfo columnInfo) {
String baseName = "REF_" + columnInfo.annotation.selectionProvider().getSimpleName() + "_"
+ columnInfo.annotation.selectionType();
if (wb.getName(baseName) == null) {
// 创建REF sheet
Sheet sheet = wb.getSheet("REF");
if (sheet == null) {
sheet = wb.createSheet("REF");
wb.setSheetHidden(wb.getSheetIndex(sheet), true);
}
// 创建全集引用Name
int rowNum = sheet.getLastRowNum() + 1;
Row row = sheet.createRow(rowNum);
int cellNum = 0;
for (ExcelColumnSelectionItem item : columnInfo.getSelectionItems()) {
Cell cell = row.createCell(cellNum, CellType.STRING);
cell.setCellValue(item.getName());
cellNum++;
}
Name wbName = wb.createName();
wbName.setNameName(baseName);
wbName.setRefersToFormula(
"REF!" + "$A$" + (rowNum + 1) + ":$" + numberToColumnHead(cellNum) + "$" + (rowNum + 1));
rowNum++;
// 创建级联引用的Name
Map<String, List<ExcelColumnSelectionItem>> map = new LinkedHashMap<String, List<ExcelColumnSelectionItem>>();
for (ExcelColumnSelectionItem selectionItem : columnInfo.getSelectionItems()) {
String refName = selectionItem.getRefName();
if (Utils.isNotBlank(refName)) {
List<ExcelColumnSelectionItem> list = map.get(refName);
if (ObjectUtil.isEmpty(list)) {
list = new ArrayList<>();
map.put(refName, list);
}
list.add(selectionItem);
}
}
for (Map.Entry<String, List<ExcelColumnSelectionItem>> entry : map.entrySet()) {
String refName = entry.getKey();
List<ExcelColumnSelectionItem> list = entry.getValue();
row = sheet.createRow(rowNum);
cellNum = 0;
for (ExcelColumnSelectionItem item : list) {
Cell cell = row.createCell(cellNum, CellType.STRING);
cell.setCellValue(item.getName());
cellNum++;
}
wbName = wb.createName();
wbName.setNameName(baseName + "_" + refName);
wbName.setRefersToFormula(
"REF!" + "$A$" + (rowNum + 1) + ":$" + numberToColumnHead(cellNum) + "$" + (rowNum + 1));
rowNum++;
}
}
return baseName;
}
/**
* 数字转换为Excel列头格式，如1转换为A，2转换为B，27转换为AA等
*/
private static String numberToColumnHead(int num) {
if (num <= 26) {
return String.valueOf((char) ('A' + (num - 1)));
} else {
int h = (num - 1) / 26;
int l = (num - 1) % 26 + 1;
return numberToColumnHead(h) + numberToColumnHead(l);
}
}
/**
* 读取单元格的值，返回是否读到非空值
*/
private static boolean readCellValueToModel(Workbook wb, Cell cell, ExcelColumnInfo columnInfo, Object model) {
Class<?> fieldType = columnInfo.field.getType();
ExcelModelBase modelBase = new ExcelModelBase();
if (model instanceof ExcelModelBase) {
modelBase = (ExcelModelBase) model;
}
String strValue = Utils.trimToEmpty(getCellValueAsString(wb, cell));
modelBase.setOriginalValue(columnInfo.field.getName(), strValue);
Object value = null;
if (Date.class.equals(fieldType)) {
if (Utils.isNotBlank(strValue)) {
String dateFormat = columnInfo.annotation.dateFormat();
if (Utils.isBlank(dateFormat)) {
dateFormat = "yyyy-MM-dd";
}
try {
value = Utils.parseDate(strValue, dateFormat);
} catch (ParseException e) {
modelBase.addFieldError(columnInfo.field.getName(),
columnInfo.annotation.name() + "日期格式无法解析，应该为" + dateFormat + "格式");
}
}
} else if (Integer.class.equals(fieldType) || Integer.TYPE.equals(fieldType)) {
if (Utils.isNotBlank(strValue)) {
try {
value = Integer.valueOf(strValue, 10);
} catch (Exception e) {
modelBase.addFieldError(columnInfo.field.getName(), columnInfo.annotation.name() + "数字格式无法解析");
}
}
} else if (Long.class.equals(fieldType) || Long.TYPE.equals(fieldType)) {
if (Utils.isNotBlank(strValue)) {
try {
value = Long.valueOf(strValue, 10);
} catch (Exception e) {
modelBase.addFieldError(columnInfo.field.getName(), columnInfo.annotation.name() + "数字格式无法解析");
}
}
} else if (Float.class.equals(fieldType) || Float.TYPE.equals(fieldType)) {
if (Utils.isNotBlank(strValue)) {
try {
value = Float.valueOf(strValue);
} catch (Exception e) {
modelBase.addFieldError(columnInfo.field.getName(), columnInfo.annotation.name() + "数字格式无法解析");
}
}
} else if (Double.class.equals(fieldType) || Double.TYPE.equals(fieldType)) {
if (Utils.isNotBlank(strValue)) {
try {
value = Double.valueOf(strValue);
} catch (Exception e) {
modelBase.addFieldError(columnInfo.field.getName(), columnInfo.annotation.name() + "数字格式无法解析");
}
}
} else if (BigDecimal.class.equals(fieldType)) {
if (Utils.isNotBlank(strValue)) {
try {
value = new BigDecimal(strValue);
} catch (Exception e) {
modelBase.addFieldError(columnInfo.field.getName(), columnInfo.annotation.name() + "数字格式无法解析");
}
}
} else {
value = strValue;
}
if (Utils.isNotBlank(strValue)) {
List<ExcelColumnSelectionItem> selectionItems = columnInfo.getSelectionItems();
if (selectionItems != null) {
String realValue = null;
for (ExcelColumnSelectionItem selectionItem : selectionItems) {
if (selectionItem.getName().equals(value)) {
realValue = selectionItem.getValue();
}
}
if (realValue == null) {
modelBase.addFieldError(columnInfo.field.getName(), columnInfo.annotation.name() + "值不在可选范围内");
value = null;
} else {
value = realValue;
}
}
}
if (value != null) {
try {
columnInfo.field.set(model, value);
} catch (Exception e) {
throw new RuntimeException(e);
}
}
return Utils.isNotBlank(strValue);
}
/**
* 获取单元格字符串值
*/
private static String getCellValueAsString(Workbook wb, Cell cell) {
if (cell == null) {
return null;
}
FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator();
DataFormatter dataFormatter = new DataFormatter();
return dataFormatter.formatCellValue(cell, evaluator);
}
/**
* 根据对象返回excel
*
* @作者 Pj
* @创建时间 2023/8/14 14:45
**/
public static void toExcel(HttpServletResponse response, String name, Class<?>... modelClasses) throws IOException {
response.setContentType("application/vnd.ms-excel");
response.setCharacterEncoding("utf-8");
String fileName = URLEncoder.encode(name, "UTF-8");
response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx");
byte[] data = ExcelImportExportTools.impTemplateAppointRow(1, modelClasses);
OutputStream fout = response.getOutputStream();
try {
fout.write(data);
} finally {
fout.close();
}
}
/**
* 根据excel返回list
**/
public static List<Object> excelToList(MultipartFile file, HttpServletResponse response, String name, int sheetI, Class<?>... modelClasses) throws IOException {
response.setContentType("application/vnd.ms-excel");
response.setCharacterEncoding("utf-8");
String fileName = URLEncoder.encode(name, "UTF-8");
response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx");
List<List<Object>> models = ExcelImportExportTools.imp(file.getInputStream(), modelClasses);
List<Object> sheetList = models.get(sheetI);
return sheetList;
}
/**
* 根据对象返回excel
* @创建时间 2023/8/14 14:45
**/
public static void listToExcel(HttpServletResponse response, List<Object> list, Class<?>... modelClasses) throws IOException {
List<List<Object>> o = new ArrayList<>();
o.add(list);
byte[] exp = ExcelImportExportTools.exp(o, modelClasses);
OutputStream fout = response.getOutputStream();
try {
fout.write(exp);
} finally {
fout.close();
}
}
private static class ExcelColumnInfo {
ExcelColumn annotation;
Field field;
boolean selectionItemsInitialized;
List<ExcelColumnSelectionItem> selectionItems;
public List<ExcelColumnSelectionItem> getSelectionItems() {
if (!selectionItemsInitialized) {
try {
ExcelColumnSelectionProvider selectionProvider = getExcelColumnSelectionProvider(
annotation.selectionProvider());
selectionItems = selectionProvider.selectionItems(annotation.selectionType());
selectionItemsInitialized = true;
} catch (Exception e) {
throw new RuntimeException(e);
}
}
return selectionItems;
}
}
private ExcelImportExportTools() {
}
}
6.ExcelModelBase Excel模型对象的基类，包含一个特殊的错误信息字段，如果该错误信息有值，导出的时候最后会有一个错误列显示错误信息
public class ExcelModelBase implements Serializable {
/** 其他错误信息 */
private List<String> otherErrors = new ArrayList<String>();
/** 字段相关的错误信息 */
private Map<String, List<String>> fieldErrorMap = new LinkedHashMap<String, List<String>>();
/** 导入时读取的原始值 */
private Map<String, String> originalValueMap = new HashMap<String, String>();
/** 获取所有错误信息 */
public List<String> getAllErrors() {
List<String> allErrors = new ArrayList<String>();
for (List<String> fieldErrors : fieldErrorMap.values()) {
allErrors.addAll(fieldErrors);
}
allErrors.addAll(otherErrors);
return allErrors;
}
/** 增加字段相关错误 */
public void addFieldError(String fieldName, String error) {
List<String> fieldErrors = fieldErrorMap.get(fieldName);
if (fieldErrors == null) {
fieldErrors = new ArrayList<String>();
fieldErrorMap.put(fieldName, fieldErrors);
}
fieldErrors.add(error);
}
/** 增加一条错误信息 */
public void addOtherError(String error) {
this.otherErrors.add(error);
}
/** 某字段是否有错误 */
public boolean hasFieldError(String fieldName) {
return fieldErrorMap.containsKey(fieldName);
}
/** 是否有错误 */
public boolean hasError() {
return !this.fieldErrorMap.isEmpty() || !this.otherErrors.isEmpty();
}
/** 设置Excel导入时的原始值 */
public void setOriginalValue(String key, String value) {
originalValueMap.put(key, value);
}
/** 获取Excel导入时的原始值 */
public String getOriginalValue(String key) {
return originalValueMap.get(key);
}
}
7.ExcelSheet 标注在Excel模型类上，定义Sheet相关属性
@Retention(RUNTIME)
@Target(TYPE)
@Inherited
public @interface ExcelSheet {
/**
* Sheet名称
*/
String name() default "";
}
8.NullExcelColumnSelectionProvider 默认列表提供者，不提供可选列表
public class NullExcelColumnSelectionProvider implements ExcelColumnSelectionProvider{
@Override
public List<ExcelColumnSelectionItem> selectionItems(String type) {
return null;
}
}
9.Utils 公共处理类
public class Utils {
public static boolean isBlank(String str) {
return str == null || str.trim().length() == 0;
}
public static boolean isNotBlank(String str) {
return !isBlank(str);
}
public static boolean equals(Object obj1, Object obj2) {
if (obj1 == null && obj2 == null) {
return true;
} else if (obj1 != null) {
return obj1.equals(obj2);
} else {
return false;
}
}
public static String join(Iterable<String> strs, String separator) {
StringBuilder sb = new StringBuilder();
for (String str : strs) {
if (sb.length() == 0) {
sb.append(str);
} else {
sb.append(separator).append(str);
}
}
return sb.toString();
}
public static String trimToEmpty(String str) {
return str == null ? "" : str.trim();
}
public static Date parseDate(String dateStr, String format) throws ParseException {
return new SimpleDateFormat(format).parse(dateStr);
}
}
10.导入导出模板
@ExcelSheet(name = "员工信息")
@Data
public class SysUserExcel extends ExcelModelBase {
@ExcelColumn(name = "* 职务级别",notes = "职务级别不能为空,请在单元格下拉框中选择填写",width = 20,selectionProvider = SysUserExcelColumnSelectPro.class,selectionType = "jobLevel")
private String jobLevel;
@ExcelColumn(name = "* 姓名",notes = "姓名不能为空",width = 15)
private String nickName;
@ExcelColumn(name = "* 性别",notes = "性别不能为空,请在单元格下拉框中选择填写",width = 15,selectionProvider = SysUserExcelColumnSelectPro.class,selectionType = "sex")
private String sex;
@ExcelColumn(name = "出生日期",notes = "员工出生日期，请使用yyyy-MM-dd格式", width = 15, dateFormat = "yyyy-MM-dd")
private String birthday;
}
11.用户信息得下拉操作
public class SysUserExcelColumnSelectPro implements ExcelColumnSelectionProvider {
@Override
public List<ExcelColumnSelectionItem> selectionItems(String type) {
List<ExcelColumnSelectionItem> items = new ArrayList<ExcelColumnSelectionItem>();
if("jobLevel".equals(type)){
items.add(new ExcelColumnSelectionItem("team_leader","班组长"));
items.add(new ExcelColumnSelectionItem("clerk_level","办事员级别"));
items.add(new ExcelColumnSelectionItem("deputy_depart_level","副科级"));
items.add(new ExcelColumnSelectionItem("deputy_dapart_level_benefits","副科级待遇"));
items.add(new ExcelColumnSelectionItem("deputy_county_depart_level","副县处级"));
items.add(new ExcelColumnSelectionItem("office_level","科员级"));
items.add(new ExcelColumnSelectionItem("nothing_level","无职务级别"));
items.add(new ExcelColumnSelectionItem("county_level","县处级"));
items.add(new ExcelColumnSelectionItem("department_level","正科级"));
items.add(new ExcelColumnSelectionItem("department_level_benefits","正科级待遇"));
}else if("sex".equals(type)){
items.add(new ExcelColumnSelectionItem("1", "男"));
items.add(new ExcelColumnSelectionItem("2", "女"));
}
}
12 导入导出实现方法
@ApiOperation(value = "员工导入模板")
@GetMapping("/downUserExcel")
public void downUserExcel(HttpServletResponse response) throws Exception {
response.setContentType("application/vnd.ms-excel");
response.setCharacterEncoding("utf-8");
String fileName = URLEncoder.encode("员工信息导入模板", "UTF-8");
response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx");
byte[] data = ExcelImportExportTools.impTemplate(SysUserExcel.class);
OutputStream fout = response.getOutputStream();
try {
fout.write(data);
} finally {
fout.close();
}
}
@ApiOperation(value = "员工数据导入")
@PostMapping("/importUserExcel")
public AjaxResult importUserExcel(@RequestParam(name = "file") MultipartFile file, HttpServletResponse response) throws IOException {
response.setContentType("application/vnd.ms-excel");
response.setCharacterEncoding("utf-8");
String fileName = URLEncoder.encode("员工错误信息导出", "UTF-8");
response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx");
List<List<Object>> models = ExcelImportExportTools.imp(file.getInputStream(), SysUserExcel.class);
List<Object> sheet1Models = models.get(0);
//错误信息查询
List<Object> userSelector = userService.importUserSelector(sheet1Models);
if (userSelector.size() > 0) {
List<List<Object>> o = new ArrayList<>();
o.add(userSelector);
byte[] exp = ExcelImportExportTools.exp(o, SysUserExcel.class);
OutputStream fout = response.getOutputStream();
try {
fout.write(exp);
} finally {
fout.close();
}
}
return AjaxResult.success("成功");
}
12.service层
/**
* 用户导入错误信息收集
* @param sheet1Models 导入数据
* @return java.util.List<java.lang.Object>
* @date 2023/8/10 9:38
* @author fyh
**/
List<Object> importUserSelector(List<Object> sheet1Models);
13.serviceImpl实现类 错误信息返回，正确数据直接保存数据库
//手机号码校验
String telRegex = "^((13[0-9])|(14[5,7,9])|(15([0-3]|[5-9]))|(166)|(17[0,1,3,4,5,6,7,8])|(18[0-9])|(19[8|9]))\\d{8}$";
//身份证号码校验
String IDRe18 = "^([1-6][1-9]|50)\\d{4}(18|19|20)\\d{2}((0[1-9])|10|11|12)(([0-2][1-9])|10|20|30|31)\\d{3}[0-9Xx]$";
//n位数字校验
String number = "^[0-9]*$";
@Override
@Transactional
public List<Object> importUserSelector(List<Object> sheet1Models) {
List<Object> errorList = new ArrayList<>();
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
Map<String, String> postMap = new HashMap<>();
List<SysUser> list = new ArrayList<>();
List<SysUser> sysUsers = userMapper.selectUserList(new SysUser());
List<SysUserExcel> sysUserExcels = BeanUtil.copyToList(sheet1Models, SysUserExcel.class);
List<String> collect1 = sysUserExcels.stream().collect(Collectors.toMap(SysUserExcel::getUserName, e -> 1, Integer::sum))
.entrySet().stream().filter(e -> e.getValue() > 1).map(Map.Entry::getKey).collect(Collectors.toList());
for (Object model : sheet1Models) {
SysUserExcel user = (SysUserExcel) model;
Date graDate = null;
Date birthday1 = null;
Date entryTime1 = null;
Date workDate1 = null;
if (StringUtils.isBlank(user.getPostId())) {
user.addFieldError("postId", "员工所属岗位不能为空");
}
if (StringUtils.isBlank(user.getNickName())) {
user.addFieldError("nickName", "员工姓名不能为空");
}
if (StringUtils.isBlank(user.getSex())) {
user.addFieldError("sex", "员工性别不能为空");
}
//校验工号长度是否满足条件
String userName = String.valueOf(user.getUserName());
if (StringUtils.isBlank(userName)) {
user.addFieldError("userName", "员工工号不能为空");
} else {
Pattern p = Pattern.compile(number);
Matcher m = p.matcher(userName);
if (!m.matches()) {
user.addFieldError("userName", "员工只能是纯数字");
}
if (userName.length() < 6 || userName.length() > 20) {
user.addFieldError("userName", "员工工号填写有误,工号长度介于6-20位之间，且必须为数字");
}
}
if (StringUtils.isBlank(user.getNation())) {
user.addFieldError("nation", "员工所属民族不能为空");
}
if (StringUtils.isBlank(user.getPoliticalStatus())) {
user.addFieldError("politicalStatus", "员工政治面貌不能为空");
}
//校验工号是否已存在
if (ObjectUtil.isNotEmpty(sysUsers)) {
List<SysUser> collect = sysUsers.stream().filter(res -> res.getUserName().equals(user.getUserName())).collect(Collectors.toList());
if (collect.size() > 0) {
user.addFieldError("userName", "员工编号重复,该工号已存在于员工管理模块中，不能重复导入");
}
}
if (collect1.size() > 0) {
List<String> collect = collect1.stream().filter(res -> res.equals(user.getUserName())).collect(Collectors.toList());
if (collect.size() > 0) {
user.addFieldError("userName", "导入的Excel员工信息中工号: " + collect.get(0) + " 存在重复数据");
}
}
//校验员工联系方式格式是否正确
if (StringUtils.isNotBlank(user.getPhonenumber())) {
Pattern p = Pattern.compile(telRegex);
Matcher m = p.matcher(user.getPhonenumber());
if (!m.matches()) {
user.addFieldError("phonenumber", "员工手机号码格式有误");
}
}
//身份证号码正则校验
String idCard = user.getIdCard();
if (StringUtils.isNotBlank(idCard)) {
Pattern p = Pattern.compile(IDRe18);
Matcher m = p.matcher(idCard);
if (!m.matches()) {
user.addFieldError("idCard", "员工身份证号码格式有误");
}
}
boolean b = user.hasError();
if (b) {
errorList.add(user);
} else {
postMap.put(userName, user.getPostId());
SysUser sysUser = new SysUser();
BeanUtil.copyProperties(user, sysUser);
if (ObjectUtil.isNotEmpty(graDate)) {
sysUser.setGraduateDate(graDate);
}
if (ObjectUtil.isNotEmpty(birthday1)) {
sysUser.setBirthday(birthday1);
}
if (ObjectUtil.isNotEmpty(workDate1)) {
sysUser.setWorkDate(workDate1);
}
if (ObjectUtil.isNotEmpty(entryTime1)) {
sysUser.setEntryTime(entryTime1);
}
String pass = userName.substring(userName.length() - 6);
String passWord = "wh" + pass;
sysUser.setPassword(SecurityUtils.encryptPassword(passWord));
//系统中导入员工默认在职状态
sysUser.setEmployeeStatus("1");
sysUser.setUserName(userName);
list.add(sysUser);
}
}
//用户信息保存
if (list.size() > 0) {
List<SysUserPost> sysUserPosts = new ArrayList<>();
//调用保存方法
boolean b1 = this.saveOrUpdateBatch(list);
//postMapper
List<SysPost> postList = postMapper.selectPostList(new SysPost());
if (b1) {
//新增用户角色关联关系
List<SysUserRole> sysUserRoleList = new ArrayList<>();
list.forEach(res -> {
SysUserPost sysUserPost = new SysUserPost();
SysUserRole sysUserRole = new SysUserRole();
sysUserRole.setUserId(res.getUserId());
sysUserRole.setRoleId(2L);
sysUserRoleList.add(sysUserRole);
String s = postMap.get(res.getUserName());
//岗位信息
sysUserPost.setUserId(res.getUserId());
sysUserPost.setPostId(Long.valueOf(s));
if (ObjectUtil.isNotEmpty(postList)) {
List<SysPost> collect = postList.stream().filter(result -> result.getPostId().equals(Long.valueOf(s))).collect(Collectors.toList());
if (collect.size() > 0) {
sysUserPost.setDictCode(collect.get(0).getDictCode());
}
}
sysUserPosts.add(sysUserPost);
});
int i = userRoleMapper.batchUserRole(sysUserRoleList);
if (i <= 0) {
throw new ServiceException("导入失败，请重新上传");
}
//新增用户岗位关联关系
int i1 = userPostMapper.batchUserPost(sysUserPosts);
if (i1 <= 0) {
throw new ServiceException("导入失败，请重新上传");
}
} else {
throw new ServiceException("导入失败，请重新上传");
}
}
return errorList;
}
写在最后的话
题目中校验为空时可以直接在 实体类上添加注解实现 @NotBlank 实现 poi版本可以根据自己项目中的poi版本自己确定
611
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
