基于Java手寫一個通用的Excel導(dǎo)入和導(dǎo)出工具類
更新時間：2026年05月28日 09:24:13 作者：xiaoyu?
在日常開發(fā)中,Excel導(dǎo)入導(dǎo)出是一個非常常見的需求,雖然市面上有Apache POI、EasyExcel等優(yōu)秀的開源庫,但在實際使用中,我們經(jīng)常遇到一些問題,下面我們就用Java手寫一個通用的Excel導(dǎo)入和導(dǎo)出工具類吧
前言
在日常開發(fā)中，Excel導(dǎo)入導(dǎo)出是一個非常常見的需求。雖然市面上有Apache POI、EasyExcel等優(yōu)秀的開源庫，但在實際使用中，我們經(jīng)常遇到以下痛點：
一、工具類設(shè)計思路
1.1 核心設(shè)計原則
- 簡單易用：通過注解和配置，最小化使用復(fù)雜度
- 功能強大：支持復(fù)雜表頭、動態(tài)列、數(shù)據(jù)驗證等高級功能
- 性能優(yōu)異：基于流式處理，支持大數(shù)據(jù)量導(dǎo)入導(dǎo)出
- 擴展性強：支持自定義轉(zhuǎn)換器、驗證器、樣式等
- 類型安全：基于泛型設(shè)計，編譯時類型檢查
1.2 技術(shù)選型
- 核心引擎：Apache POI（成熟穩(wěn)定）
- 注解驅(qū)動：自定義注解簡化配置
- 流式處理：SXSSFWorkbook/SXSSFSheet處理大數(shù)據(jù)量
- 模板引擎：支持Excel模板導(dǎo)出
- 數(shù)據(jù)驗證：內(nèi)置常用驗證規(guī)則
二、核心注解定義
2.1 字段級別注解 @ExcelField
package com.example.excel.annotation;
import java.lang.annotation.*;
/**
* Excel字段注解
*/
@Target({ElementType.FIELD})
@Retention(RetentionPolicy.RUNTIME)
@Documented
public @interface ExcelField {
/**
* 列名（支持多級表頭，用"|"分隔）
* 示例：@ExcelField(name = "基本信息|姓名")
*/
String name();
/**
* 列索引（從0開始）
*/
int index() default -1;
/**
* 列寬（單位：字符寬度）
*/
int width() default 15;
/**
* 數(shù)據(jù)格式
*/
String format() default "";
/**
* 轉(zhuǎn)換器
*/
Class<? extends ExcelConverter> converter() default ExcelConverter.class;
/**
* 驗證器
*/
Class<? extends ExcelValidator> validator() default ExcelValidator.class;
/**
* 是否必填
*/
boolean required() default false;
/**
* 必填錯誤提示
*/
String requiredMessage() default "該字段不能為空";
/**
* 是否導(dǎo)出
*/
boolean export() default true;
/**
* 是否導(dǎo)入
*/
boolean import() default true;
/**
* 下拉選項（用于數(shù)據(jù)驗證）
*/
String[] options() default {};
/**
* 數(shù)據(jù)類型
*/
DataType dataType() default DataType.STRING;
/**
* 數(shù)據(jù)類型枚舉
*/
enum DataType {
STRING, // 字符串
NUMBER, // 數(shù)字
DATE, // 日期
BOOLEAN, // 布爾
FORMULA, // 公式
IMAGE // 圖片
}
}
2.2 工作表級別注解 @ExcelSheet
package com.example.excel.annotation;
import java.lang.annotation.*;
/**
* Excel工作表注解
*/
@Target({ElementType.TYPE})
@Retention(RetentionPolicy.RUNTIME)
@Documented
public @interface ExcelSheet {
/**
* 工作表名稱
*/
String name() default "Sheet1";
/**
* 起始行（從0開始，默認為0）
*/
int startRow() default 0;
/**
* 標題行數(shù)
*/
int titleRows() default 1;
/**
* 是否創(chuàng)建表頭
*/
boolean createHeader() default true;
/**
* 是否自動調(diào)整列寬
*/
boolean autoSizeColumn() default true;
/**
* 表頭樣式
*/
String headerStyle() default "HEADER";
/**
* 數(shù)據(jù)樣式
*/
String dataStyle() default "DATA";
/**
* 是否啟用數(shù)據(jù)驗證
*/
boolean enableValidation() default true;
}
2.3 忽略注解 @ExcelIgnore
package com.example.excel.annotation;
import java.lang.annotation.*;
/**
* Excel忽略注解
*/
@Target({ElementType.FIELD})
@Retention(RetentionPolicy.RUNTIME)
@Documented
public @interface ExcelIgnore {
}
三、核心功能實現(xiàn)
3.1 轉(zhuǎn)換器接口與實現(xiàn)
package com.example.excel.converter;
/**
* Excel數(shù)據(jù)轉(zhuǎn)換器接口
*/
public interface ExcelConverter {
/**
* 導(dǎo)出時轉(zhuǎn)換：Java對象 -> Excel單元格值
*/
Object exportValue(Object value);
/**
* 導(dǎo)入時轉(zhuǎn)換：Excel單元格值 -> Java對象
*/
Object importValue(Object value);
}
內(nèi)置轉(zhuǎn)換器實現(xiàn)：
package com.example.excel.converter.impl;
import com.example.excel.converter.ExcelConverter;
import java.text.SimpleDateFormat;
import java.util.Date;
/**
* 日期轉(zhuǎn)換器
*/
public class DateConverter implements ExcelConverter {
private String pattern;
public DateConverter() {
this("yyyy-MM-dd");
}
public DateConverter(String pattern) {
this.pattern = pattern;
}
@Override
public Object exportValue(Object value) {
if (value == null) {
return null;
}
if (value instanceof Date) {
SimpleDateFormat sdf = new SimpleDateFormat(pattern);
return sdf.format((Date) value);
}
return value.toString();
}
@Override
public Object importValue(Object value) {
if (value == null) {
return null;
}
if (value instanceof Date) {
return value;
}
try {
SimpleDateFormat sdf = new SimpleDateFormat(pattern);
return sdf.parse(value.toString());
} catch (Exception e) {
throw new RuntimeException("日期格式錯誤，期望格式：" + pattern);
}
}
}
/**
* 枚舉轉(zhuǎn)換器
*/
public class EnumConverter implements ExcelConverter {
private Class<? extends Enum> enumClass;
public EnumConverter(Class<? extends Enum> enumClass) {
this.enumClass = enumClass;
}
@Override
public Object exportValue(Object value) {
if (value == null) {
return null;
}
if (value instanceof Enum) {
return value.toString();
}
return value;
}
@Override
public Object importValue(Object value) {
if (value == null) {
return null;
}
try {
return Enum.valueOf(enumClass, value.toString());
} catch (Exception e) {
throw new RuntimeException("枚舉值錯誤：" + value);
}
}
}
/**
* 字典轉(zhuǎn)換器
*/
public class DictConverter implements ExcelConverter {
private DictService dictService;
private String dictType;
public DictConverter(DictService dictService, String dictType) {
this.dictService = dictService;
this.dictType = dictType;
}
@Override
public Object exportValue(Object value) {
if (value == null) {
return null;
}
// 字典值 -> 字典標簽
return dictService.getLabel(dictType, value.toString());
}
@Override
public Object importValue(Object value) {
if (value == null) {
return null;
}
// 字典標簽 -> 字典值
return dictService.getValue(dictType, value.toString());
}
}
3.2 驗證器接口與實現(xiàn)
package com.example.excel.validator;
/**
* Excel數(shù)據(jù)驗證器接口
*/
public interface ExcelValidator {
/**
* 驗證數(shù)據(jù)
* @param value 待驗證的值
* @param fieldName 字段名
* @param rowIndex 行號
* @return 驗證結(jié)果
*/
ValidationResult validate(Object value, String fieldName, int rowIndex);
/**
* 驗證結(jié)果
*/
class ValidationResult {
private boolean valid;
private String message;
public ValidationResult(boolean valid, String message) {
this.valid = valid;
this.message = message;
}
public static ValidationResult success() {
return new ValidationResult(true, null);
}
public static ValidationResult fail(String message) {
return new ValidationResult(false, message);
}
public boolean isValid() {
return valid;
}
public String getMessage() {
return message;
}
}
}
內(nèi)置驗證器實現(xiàn)：
package com.example.excel.validator.impl;
import com.example.excel.validator.ExcelValidator;
import java.util.regex.Pattern;
/**
* 手機號驗證器
*/
public class PhoneValidator implements ExcelValidator {
private static final Pattern PHONE_PATTERN = Pattern.compile("^1[3-9]\\d{9}$");
@Override
public ValidationResult validate(Object value, String fieldName, int rowIndex) {
if (value == null || value.toString().trim().isEmpty()) {
return ValidationResult.success(); // 非空驗證由required字段控制
}
String phone = value.toString().trim();
if (!PHONE_PATTERN.matcher(phone).matches()) {
return ValidationResult.fail(
String.format("第%d行，%s格式錯誤：必須是11位手機號", rowIndex + 1, fieldName)
);
}
return ValidationResult.success();
}
}
/**
* 郵箱驗證器
*/
public class EmailValidator implements ExcelValidator {
private static final Pattern EMAIL_PATTERN =
Pattern.compile("^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\\.[a-zA-Z]{2,}$");
@Override
public ValidationResult validate(Object value, String fieldName, int rowIndex) {
if (value == null || value.toString().trim().isEmpty()) {
return ValidationResult.success();
}
String email = value.toString().trim();
if (!EMAIL_PATTERN.matcher(email).matches()) {
return ValidationResult.fail(
String.format("第%d行，%s格式錯誤：郵箱格式不正確", rowIndex + 1, fieldName)
);
}
return ValidationResult.success();
}
}
/**
* 身份證驗證器
*/
public class IdCardValidator implements ExcelValidator {
@Override
public ValidationResult validate(Object value, String fieldName, int rowIndex) {
if (value == null || value.toString().trim().isEmpty()) {
return ValidationResult.success();
}
String idCard = value.toString().trim();
if (!isValidIdCard(idCard)) {
return ValidationResult.fail(
String.format("第%d行，%s格式錯誤：身份證號格式不正確", rowIndex + 1, fieldName)
);
}
return ValidationResult.success();
}
private boolean isValidIdCard(String idCard) {
// 簡化驗證，實際應(yīng)該更嚴格
return idCard.length() == 18 && idCard.matches("^[1-9]\\d{5}(18|19|20)\\d{2}(0[1-9]|1[0-2])(0[1-9]|[12]\\d|3[01])\\d{3}[0-9Xx]$");
}
}
/**
* 數(shù)值范圍驗證器
*/
public class NumberRangeValidator implements ExcelValidator {
private double min;
private double max;
public NumberRangeValidator(double min, double max) {
this.min = min;
this.max = max;
}
@Override
public ValidationResult validate(Object value, String fieldName, int rowIndex) {
if (value == null || value.toString().trim().isEmpty()) {
return ValidationResult.success();
}
try {
double number = Double.parseDouble(value.toString());
if (number < min || number > max) {
return ValidationResult.fail(
String.format("第%d行，%s數(shù)值超出范圍：必須在 %.2f 到 %.2f 之間",
rowIndex + 1, fieldName, min, max)
);
}
} catch (NumberFormatException e) {
return ValidationResult.fail(
String.format("第%d行，%s格式錯誤：必須是數(shù)字", rowIndex + 1, fieldName)
);
}
return ValidationResult.success();
}
}
/**
* 自定義正則驗證器
*/
public class RegexValidator implements ExcelValidator {
private Pattern pattern;
private String errorMessage;
public RegexValidator(String regex, String errorMessage) {
this.pattern = Pattern.compile(regex);
this.errorMessage = errorMessage;
}
@Override
public ValidationResult validate(Object value, String fieldName, int rowIndex) {
if (value == null || value.toString().trim().isEmpty()) {
return ValidationResult.success();
}
if (!pattern.matcher(value.toString()).matches()) {
return ValidationResult.fail(
String.format("第%d行，%s格式錯誤：%s", rowIndex + 1, fieldName, errorMessage)
);
}
return ValidationResult.success();
}
}
3.3 樣式管理
package com.example.excel.style;
import org.apache.poi.ss.usermodel.*;
/**
* Excel樣式管理器
*/
public class ExcelStyleManager {
private Workbook workbook;
public ExcelStyleManager(Workbook workbook) {
this.workbook = workbook;
}
/**
* 獲取表頭樣式
*/
public CellStyle getHeaderStyle() {
CellStyle style = workbook.createCellStyle();
// 字體
Font font = workbook.createFont();
font.setFontName("微軟雅黑");
font.setFontHeightInPoints((short) 11);
font.setBold(true);
font.setColor(IndexedColors.WHITE.getIndex());
style.setFont(font);
// 背景色
style.setFillForegroundColor(IndexedColors.GREY_50_PERCENT.getIndex());
style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
// 邊框
style.setBorderTop(BorderStyle.THIN);
style.setBorderBottom(BorderStyle.THIN);
style.setBorderLeft(BorderStyle.THIN);
style.setBorderRight(BorderStyle.THIN);
// 對齊方式
style.setAlignment(HorizontalAlignment.CENTER);
style.setVerticalAlignment(VerticalAlignment.CENTER);
// 自動換行
style.setWrapText(true);
return style;
}
/**
* 獲取數(shù)據(jù)樣式
*/
public CellStyle getDataStyle() {
CellStyle style = workbook.createCellStyle();
// 字體
Font font = workbook.createFont();
font.setFontName("微軟雅黑");
font.setFontHeightInPoints((short) 10);
style.setFont(font);
// 邊框
style.setBorderTop(BorderStyle.THIN);
style.setBorderBottom(BorderStyle.THIN);
style.setBorderLeft(BorderStyle.THIN);
style.setBorderRight(BorderStyle.THIN);
// 對齊方式
style.setAlignment(HorizontalAlignment.LEFT);
style.setVerticalAlignment(VerticalAlignment.CENTER);
return style;
}
/**
* 獲取數(shù)字樣式
*/
public CellStyle getNumberStyle() {
CellStyle style = getDataStyle();
style.setAlignment(HorizontalAlignment.RIGHT);
DataFormat format = workbook.createDataFormat();
style.setDataFormat(format.getFormat("#,##0.00"));
return style;
}
/**
* 獲取日期樣式
*/
public CellStyle getDateStyle() {
CellStyle style = getDataStyle();
style.setAlignment(HorizontalAlignment.CENTER);
DataFormat format = workbook.createDataFormat();
style.setDataFormat(format.getFormat("yyyy-mm-dd"));
return style;
}
/**
* 獲取錯誤樣式
*/
public CellStyle getErrorStyle() {
CellStyle style = getDataStyle();
// 字體
Font font = workbook.createFont();
font.setColor(IndexedColors.RED.getIndex());
style.setFont(font);
return style;
}
/**
* 創(chuàng)建自定義樣式
*/
public CellStyle createCustomStyle(StyleConfig config) {
CellStyle style = workbook.createCellStyle();
// 字體
if (config.getFontName() != null) {
Font font = workbook.createFont();
font.setFontName(config.getFontName());
font.setFontHeightInPoints(config.getFontSize());
font.setBold(config.isBold());
if (config.getFontColor() != null) {
font.setColor(config.getFontColor());
}
style.setFont(font);
}
// 背景色
if (config.getBackgroundColor() != null) {
style.setFillForegroundColor(config.getBackgroundColor());
style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
}
// 邊框
if (config.getBorderType() != null) {
style.setBorderTop(config.getBorderType());
style.setBorderBottom(config.getBorderType());
style.setBorderLeft(config.getBorderType());
style.setBorderRight(config.getBorderType());
}
// 對齊方式
if (config.getHorizontalAlignment() != null) {
style.setAlignment(config.getHorizontalAlignment());
}
if (config.getVerticalAlignment() != null) {
style.setVerticalAlignment(config.getVerticalAlignment());
}
// 自動換行
style.setWrapText(config.isWrapText());
return style;
}
/**
* 樣式配置
*/
public static class StyleConfig {
private String fontName;
private short fontSize = 10;
private boolean bold;
private Short fontColor;
private Short backgroundColor;
private BorderStyle borderType;
private HorizontalAlignment horizontalAlignment;
private VerticalAlignment verticalAlignment;
private boolean wrapText;
// Builder模式
public static StyleConfig builder() {
return new StyleConfig();
}
public StyleConfig fontName(String fontName) {
this.fontName = fontName;
return this;
}
public StyleConfig fontSize(short fontSize) {
this.fontSize = fontSize;
return this;
}
public StyleConfig bold(boolean bold) {
this.bold = bold;
return this;
}
public StyleConfig fontColor(Short fontColor) {
this.fontColor = fontColor;
return this;
}
public StyleConfig backgroundColor(Short backgroundColor) {
this.backgroundColor = backgroundColor;
return this;
}
public StyleConfig borderType(BorderStyle borderType) {
this.borderType = borderType;
return this;
}
public StyleConfig horizontalAlignment(HorizontalAlignment horizontalAlignment) {
this.horizontalAlignment = horizontalAlignment;
return this;
}
public StyleConfig verticalAlignment(VerticalAlignment verticalAlignment) {
this.verticalAlignment = verticalAlignment;
return this;
}
public StyleConfig wrapText(boolean wrapText) {
this.wrapText = wrapText;
return this;
}
// Getters
public String getFontName() { return fontName; }
public short getFontSize() { return fontSize; }
public boolean isBold() { return bold; }
public Short getFontColor() { return fontColor; }
public Short getBackgroundColor() { return backgroundColor; }
public BorderStyle getBorderType() { return borderType; }
public HorizontalAlignment getHorizontalAlignment() { return horizontalAlignment; }
public VerticalAlignment getVerticalAlignment() { return verticalAlignment; }
public boolean isWrapText() { return wrapText; }
}
}
四、核心工具類實現(xiàn)
4.1 Excel導(dǎo)出工具類
package com.example.excel.util;
import com.example.excel.annotation.ExcelField;
import com.example.excel.annotation.ExcelIgnore;
import com.example.excel.annotation.ExcelSheet;
import com.example.excel.converter.ExcelConverter;
import com.example.excel.style.ExcelStyleManager;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import java.io.OutputStream;
import java.lang.reflect.Field;
import java.util.ArrayList;
import java.util.List;
/**
* Excel導(dǎo)出工具類
*/
public class ExcelExportUtil {
/**
* 導(dǎo)出Excel
* @param dataList 數(shù)據(jù)列表
* @param outputStream 輸出流
* @param <T> 數(shù)據(jù)類型
*/
public static <T> void export(List<T> dataList, OutputStream outputStream) {
if (dataList == null || dataList.isEmpty()) {
throw new IllegalArgumentException("數(shù)據(jù)列表不能為空");
}
Class<?> clazz = dataList.get(0).getClass();
ExcelSheet sheetAnnotation = clazz.getAnnotation(ExcelSheet.class);
// 創(chuàng)建工作簿
try (SXSSFWorkbook workbook = new SXSSFWorkbook()) {
// 創(chuàng)建工作表
String sheetName = sheetAnnotation != null ? sheetAnnotation.name() : "Sheet1";
Sheet sheet = workbook.createSheet(sheetName);
// 樣式管理器
ExcelStyleManager styleManager = new ExcelStyleManager(workbook);
// 獲取字段信息
List<FieldInfo> fieldInfos = getFieldInfos(clazz);
// 創(chuàng)建表頭
if (sheetAnnotation == null || sheetAnnotation.createHeader()) {
createHeader(sheet, fieldInfos, styleManager, sheetAnnotation);
}
// 填充數(shù)據(jù)
fillData(sheet, dataList, fieldInfos, styleManager, sheetAnnotation);
// 自動調(diào)整列寬
if (sheetAnnotation == null || sheetAnnotation.autoSizeColumn()) {
autoSizeColumn(sheet, fieldInfos.size());
}
// 寫入輸出流
workbook.write(outputStream);
} catch (Exception e) {
throw new RuntimeException("導(dǎo)出Excel失敗", e);
}
}
/**
* 獲取字段信息
*/
private static List<FieldInfo> getFieldInfos(Class<?> clazz) {
List<FieldInfo> fieldInfos = new ArrayList<>();
Field[] fields = clazz.getDeclaredFields();
for (Field field : fields) {
// 跳過忽略的字段
if (field.isAnnotationPresent(ExcelIgnore.class)) {
continue;
}
ExcelField fieldAnnotation = field.getAnnotation(ExcelField.class);
if (fieldAnnotation == null || !fieldAnnotation.export()) {
continue;
}
field.setAccessible(true);
fieldInfos.add(new FieldInfo(field, fieldAnnotation));
}
// 按index排序，未設(shè)置index的按字段順序
fieldInfos.sort((a, b) -> {
int indexA = a.annotation.index();
int indexB = b.annotation.index();
if (indexA >= 0 && indexB >= 0) {
return Integer.compare(indexA, indexB);
} else if (indexA >= 0) {
return -1;
} else if (indexB >= 0) {
return 1;
}
return 0;
});
return fieldInfos;
}
/**
* 創(chuàng)建表頭
*/
private static void createHeader(Sheet sheet, List<FieldInfo> fieldInfos,
ExcelStyleManager styleManager, ExcelSheet sheetAnnotation) {
CellStyle headerStyle = styleManager.getHeaderStyle();
// 計算最大層級
int maxLevel = 1;
for (FieldInfo fieldInfo : fieldInfos) {
String name = fieldInfo.annotation.name();
int level = name.split("\\|").length;
if (level > maxLevel) {
maxLevel = level;
}
}
// 創(chuàng)建多級表頭
for (int level = 0; level < maxLevel; level++) {
Row row = sheet.createRow(level);
int colIndex = 0;
for (FieldInfo fieldInfo : fieldInfos) {
String[] levels = fieldInfo.annotation.name().split("\\|");
if (level < levels.length) {
Cell cell = row.createCell(colIndex);
cell.setCellValue(levels[level]);
cell.setCellStyle(headerStyle);
// 檢查是否需要合并單元格
int rowspan = maxLevel - level - (levels.length - level - 1);
if (rowspan > 1) {
sheet.addMergedRegion(new CellRangeAddress(
level, level + rowspan - 1, colIndex, colIndex
));
}
// 檢查是否有子級需要橫向合并
if (level < levels.length - 1) {
// 計算該層級有多少個子列
int childCount = countChildren(fieldInfos, colIndex, level, levels.length);
if (childCount > 1) {
sheet.addMergedRegion(new CellRangeAddress(
level, level, colIndex, colIndex + childCount - 1
));
}
}
// 設(shè)置列寬
sheet.setColumnWidth(colIndex, fieldInfo.annotation.width() * 256);
colIndex++;
}
}
}
}
/**
* 計算子列數(shù)量
*/
private static int countChildren(List<FieldInfo> fieldInfos, int startIndex,
int level, int totalLevels) {
int count = 0;
for (int i = startIndex; i < fieldInfos.size(); i++) {
FieldInfo fieldInfo = fieldInfos.get(i);
String[] levels = fieldInfo.annotation.name().split("\\|");
if (levels.length > level && levels[level].equals(
fieldInfos.get(startIndex).annotation.name().split("\\|")[level])) {
count++;
} else {
break;
}
}
return count;
}
/**
* 填充數(shù)據(jù)
*/
private static <T> void fillData(Sheet sheet, List<T> dataList, List<FieldInfo> fieldInfos,
ExcelStyleManager styleManager, ExcelSheet sheetAnnotation) {
int startRow = sheetAnnotation != null ? sheetAnnotation.titleRows() : 1;
for (int i = 0; i < dataList.size(); i++) {
T data = dataList.get(i);
Row row = sheet.createRow(startRow + i);
for (int j = 0; j < fieldInfos.size(); j++) {
FieldInfo fieldInfo = fieldInfos.get(j);
Cell cell = row.createCell(j);
try {
Object value = fieldInfo.field.get(data);
// 應(yīng)用轉(zhuǎn)換器
if (fieldInfo.converter != null) {
value = fieldInfo.converter.exportValue(value);
}
// 設(shè)置單元格值
setCellValue(cell, value, fieldInfo.annotation.dataType(), styleManager);
// 設(shè)置樣式
setCellStyle(cell, fieldInfo.annotation.dataType(), styleManager);
} catch (IllegalAccessException e) {
cell.setCellValue("獲取數(shù)據(jù)失敗");
}
}
}
}
/**
* 設(shè)置單元格值
*/
private static void setCellValue(Cell cell, Object value, ExcelField.DataType dataType,
ExcelStyleManager styleManager) {
if (value == null) {
return;
}
switch (dataType) {
case STRING:
cell.setCellValue(value.toString());
break;
case NUMBER:
if (value instanceof Number) {
cell.setCellValue(((Number) value).doubleValue());
} else {
cell.setCellValue(Double.parseDouble(value.toString()));
}
break;
case DATE:
if (value instanceof java.util.Date) {
cell.setCellValue((java.util.Date) value);
} else {
cell.setCellValue(value.toString());
}
break;
case BOOLEAN:
if (value instanceof Boolean) {
cell.setCellValue((Boolean) value);
} else {
cell.setCellValue(Boolean.parseBoolean(value.toString()));
}
break;
case FORMULA:
cell.setCellFormula(value.toString());
break;
default:
cell.setCellValue(value.toString());
}
}
/**
* 設(shè)置單元格樣式
*/
private static void setCellStyle(Cell cell, ExcelField.DataType dataType,
ExcelStyleManager styleManager) {
CellStyle style;
switch (dataType) {
case NUMBER:
style = styleManager.getNumberStyle();
break;
case DATE:
style = styleManager.getDateStyle();
break;
default:
style = styleManager.getDataStyle();
}
cell.setCellStyle(style);
}
/**
* 自動調(diào)整列寬
*/
private static void autoSizeColumn(Sheet sheet, int columnCount) {
for (int i = 0; i < columnCount; i++) {
sheet.autoSizeColumn(i);
// 設(shè)置最大寬度，防止列過寬
int maxWidth = sheet.getColumnWidth(i);
if (maxWidth > 10000) {
sheet.setColumnWidth(i, 10000);
}
}
}
/**
* 字段信息內(nèi)部類
*/
private static class FieldInfo {
Field field;
ExcelField annotation;
ExcelConverter converter;
FieldInfo(Field field, ExcelField annotation) {
this.field = field;
this.annotation = annotation;
// 初始化轉(zhuǎn)換器
if (annotation.converter() != ExcelConverter.class) {
try {
this.converter = annotation.converter().newInstance();
} catch (Exception e) {
throw new RuntimeException("初始化轉(zhuǎn)換器失敗", e);
}
}
}
}
}
4.2 Excel導(dǎo)入工具類
package com.example.excel.util;
import com.example.excel.annotation.ExcelField;
import com.example.excel.annotation.ExcelIgnore;
import com.example.excel.annotation.ExcelSheet;
import com.example.excel.converter.ExcelConverter;
import com.example.excel.validator.ExcelValidator;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.util.ArrayList;
import java.util.List;
/**
* Excel導(dǎo)入工具類
*/
public class ExcelImportUtil {
/**
* 導(dǎo)入Excel
* @param inputStream 輸入流
* @param clazz 目標類
* @param <T> 數(shù)據(jù)類型
* @return 導(dǎo)入結(jié)果
*/
public static <T> ImportResult<T> importExcel(InputStream inputStream, Class<T> clazz) {
List<T> dataList = new ArrayList<>();
List<ImportError> errors = new ArrayList<>();
try (Workbook workbook = new XSSFWorkbook(inputStream)) {
Sheet sheet = workbook.getSheetAt(0);
ExcelSheet sheetAnnotation = clazz.getAnnotation(ExcelSheet.class);
int startRow = sheetAnnotation != null ? sheetAnnotation.titleRows() : 1;
int totalRows = sheet.getLastRowNum();
// 獲取字段信息
List<FieldInfo> fieldInfos = getFieldInfos(clazz);
// 讀取數(shù)據(jù)
for (int i = startRow; i <= totalRows; i++) {
Row row = sheet.getRow(i);
if (row == null) {
continue;
}
try {
T instance = clazz.newInstance();
// 填充字段值
for (FieldInfo fieldInfo : fieldInfos) {
int colIndex = getFieldColumnIndex(fieldInfos, fieldInfo);
Cell cell = row.getCell(colIndex);
if (cell != null) {
Object value = getCellValue(cell);
// 應(yīng)用轉(zhuǎn)換器
if (fieldInfo.converter != null) {
value = fieldInfo.converter.importValue(value);
}
// 驗證數(shù)據(jù)
ValidationResult validationResult = validateData(
value, fieldInfo, i, fieldInfo.annotation
);
if (!validationResult.isValid()) {
errors.add(new ImportError(
i + 1,
fieldInfo.annotation.name(),
validationResult.getMessage()
));
continue;
}
// 設(shè)置字段值
setFieldValue(instance, fieldInfo.field, value);
} else if (fieldInfo.annotation.required()) {
errors.add(new ImportError(
i + 1,
fieldInfo.annotation.name(),
fieldInfo.annotation.requiredMessage()
));
}
}
dataList.add(instance);
} catch (Exception e) {
errors.add(new ImportError(
i + 1,
"整行",
"解析失敗：" + e.getMessage()
));
}
}
return new ImportResult<>(dataList, errors);
} catch (Exception e) {
throw new RuntimeException("導(dǎo)入Excel失敗", e);
}
}
/**
* 獲取字段信息
*/
private static List<FieldInfo> getFieldInfos(Class<?> clazz) {
List<FieldInfo> fieldInfos = new ArrayList<>();
Field[] fields = clazz.getDeclaredFields();
for (Field field : fields) {
if (field.isAnnotationPresent(ExcelIgnore.class)) {
continue;
}
ExcelField fieldAnnotation = field.getAnnotation(ExcelField.class);
if (fieldAnnotation == null || !fieldAnnotation.import()) {
continue;
}
field.setAccessible(true);
fieldInfos.add(new FieldInfo(field, fieldAnnotation));
}
// 按index排序
fieldInfos.sort((a, b) -> {
int indexA = a.annotation.index();
int indexB = b.annotation.index();
if (indexA >= 0 && indexB >= 0) {
return Integer.compare(indexA, indexB);
} else if (indexA >= 0) {
return -1;
} else if (indexB >= 0) {
return 1;
}
return 0;
});
return fieldInfos;
}
/**
* 獲取字段列索引
*/
private static int getFieldColumnIndex(List<FieldInfo> fieldInfos, FieldInfo target) {
for (int i = 0; i < fieldInfos.size(); i++) {
if (fieldInfos.get(i) == target) {
return i;
}
}
return -1;
}
/**
* 獲取單元格值
*/
private static Object getCellValue(Cell cell) {
switch (cell.getCellType()) {
case STRING:
return cell.getStringCellValue().trim();
case NUMERIC:
if (DateUtil.isCellDateFormatted(cell)) {
return cell.getDateCellValue();
}
return cell.getNumericCellValue();
case BOOLEAN:
return cell.getBooleanCellValue();
case FORMULA:
return cell.getCellFormula();
case BLANK:
return null;
default:
return null;
}
}
/**
* 驗證數(shù)據(jù)
*/
private static ValidationResult validateData(Object value, FieldInfo fieldInfo,
int rowIndex, ExcelField annotation) {
// 必填驗證
if (annotation.required() && (value == null || value.toString().trim().isEmpty())) {
return ValidationResult.fail(annotation.requiredMessage());
}
// 應(yīng)用驗證器
if (fieldInfo.validator != null) {
return fieldInfo.validator.validate(value, annotation.name(), rowIndex);
}
return ValidationResult.success();
}
/**
* 設(shè)置字段值
*/
private static void setFieldValue(Object instance, Field field, Object value)
throws IllegalAccessException {
if (value == null) {
return;
}
Class<?> fieldType = field.getType();
// 類型轉(zhuǎn)換
if (fieldType == String.class) {
field.set(instance, value.toString());
} else if (fieldType == Integer.class || fieldType == int.class) {
if (value instanceof Number) {
field.set(instance, ((Number) value).intValue());
} else {
field.set(instance, Integer.parseInt(value.toString()));
}
} else if (fieldType == Long.class || fieldType == long.class) {
if (value instanceof Number) {
field.set(instance, ((Number) value).longValue());
} else {
field.set(instance, Long.parseLong(value.toString()));
}
} else if (fieldType == Double.class || fieldType == double.class) {
if (value instanceof Number) {
field.set(instance, ((Number) value).doubleValue());
} else {
field.set(instance, Double.parseDouble(value.toString()));
}
} else if (fieldType == Boolean.class || fieldType == boolean.class) {
if (value instanceof Boolean) {
field.set(instance, value);
} else {
field.set(instance, Boolean.parseBoolean(value.toString()));
}
} else if (fieldType == java.util.Date.class) {
if (value instanceof java.util.Date) {
field.set(instance, value);
} else {
// 需要根據(jù)format轉(zhuǎn)換
throw new RuntimeException("日期類型需要配置轉(zhuǎn)換器");
}
} else {
field.set(instance, value);
}
}
/**
* 字段信息內(nèi)部類
*/
private static class FieldInfo {
Field field;
ExcelField annotation;
ExcelConverter converter;
ExcelValidator validator;
FieldInfo(Field field, ExcelField annotation) {
this.field = field;
this.annotation = annotation;
// 初始化轉(zhuǎn)換器
if (annotation.converter() != ExcelConverter.class) {
try {
this.converter = annotation.converter().newInstance();
} catch (Exception e) {
throw new RuntimeException("初始化轉(zhuǎn)換器失敗", e);
}
}
// 初始化驗證器
if (annotation.validator() != ExcelValidator.class) {
try {
this.validator = annotation.validator().newInstance();
} catch (Exception e) {
throw new RuntimeException("初始化驗證器失敗", e);
}
}
}
}
/**
* 導(dǎo)入結(jié)果
*/
public static class ImportResult<T> {
private List<T> dataList;
private List<ImportError> errors;
public ImportResult(List<T> dataList, List<ImportError> errors) {
this.dataList = dataList;
this.errors = errors;
}
public List<T> getDataList() {
return dataList;
}
public List<ImportError> getErrors() {
return errors;
}
public boolean hasErrors() {
return !errors.isEmpty();
}
}
/**
* 導(dǎo)入錯誤
*/
public static class ImportError {
private int rowIndex;
private String fieldName;
private String message;
public ImportError(int rowIndex, String fieldName, String message) {
this.rowIndex = rowIndex;
this.fieldName = fieldName;
this.message = message;
}
public int getRowIndex() {
return rowIndex;
}
public String getFieldName() {
return fieldName;
}
public String getMessage() {
return message;
}
@Override
public String toString() {
return String.format("第%d行，%s：%s", rowIndex, fieldName, message);
}
}
/**
* 驗證結(jié)果
*/
private static class ValidationResult {
private boolean valid;
private String message;
public ValidationResult(boolean valid, String message) {
this.valid = valid;
this.message = message;
}
public static ValidationResult success() {
return new ValidationResult(true, null);
}
public static ValidationResult fail(String message) {
return new ValidationResult(false, message);
}
public boolean isValid() {
return valid;
}
public String getMessage() {
return message;
}
}
}
五、使用示例
5.1 定義實體類
package com.example.entity;
import com.example.excel.annotation.*;
import com.example.excel.converter.impl.*;
import com.example.excel.validator.impl.*;
import lombok.Data;
import java.util.Date;
/**
* 用戶實體
*/
@Data
@ExcelSheet(name = "用戶信息", titleRows = 2, autoSizeColumn = true)
public class UserExcel {
@ExcelField(name = "基本信息|工號", index = 0, width = 15)
private String employeeNo;
@ExcelField(
name = "基本信息|姓名",
index = 1,
width = 12,
required = true,
requiredMessage = "姓名不能為空"
)
private String name;
@ExcelField(
name = "基本信息|性別",
index = 2,
width = 8,
options = {"男", "女"}
)
private String gender;
@ExcelField(
name = "基本信息|年齡",
index = 3,
width = 8,
dataType = ExcelField.DataType.NUMBER
)
private Integer age;
@ExcelField(
name = "聯(lián)系方式|手機號",
index = 4,
width = 15,
validator = PhoneValidator.class
)
private String phone;
@ExcelField(
name = "聯(lián)系方式|郵箱",
index = 5,
width = 20,
validator = EmailValidator.class
)
private String email;
@ExcelField(
name = "工作信息|部門",
index = 6,
width = 15
)
private String department;
@ExcelField(
name = "工作信息|職位",
index = 7,
width = 15
)
private String position;
@ExcelField(
name = "工作信息|入職日期",
index = 8,
width = 15,
dataType = ExcelField.DataType.DATE,
converter = DateConverter.class,
format = "yyyy-MM-dd"
)
private Date entryDate;
@ExcelField(
name = "工作信息|薪資",
index = 9,
width = 12,
dataType = ExcelField.DataType.NUMBER,
converter = DateConverter.class
)
private Double salary;
@ExcelField(
name = "其他|狀態(tài)",
index = 10,
width = 10,
converter = StatusConverter.class
)
private UserStatus status;
@ExcelField(
name = "其他|備注",
index = 11,
width = 30
)
private String remark;
// 忽略導(dǎo)入導(dǎo)出的字段
@ExcelIgnore
private String password;
/**
* 用戶狀態(tài)枚舉
*/
public enum UserStatus {
ACTIVE("在職"),
INACTIVE("離職"),
SUSPENDED("停職");
private String desc;
UserStatus(String desc) {
this.desc = desc;
}
public String getDesc() {
return desc;
}
}
/**
* 狀態(tài)轉(zhuǎn)換器
*/
public static class StatusConverter implements ExcelConverter {
@Override
public Object exportValue(Object value) {
if (value == null) {
return null;
}
return ((UserStatus) value).getDesc();
}
@Override
public Object importValue(Object value) {
if (value == null) {
return null;
}
String desc = value.toString();
for (UserStatus status : UserStatus.values()) {
if (status.getDesc().equals(desc)) {
return status;
}
}
throw new RuntimeException("無效的狀態(tài)值：" + desc);
}
}
}
5.2 導(dǎo)出示例
package com.example.controller;
import com.example.entity.UserExcel;
import com.example.excel.util.ExcelExportUtil;
import org.springframework.web.bind.annotation.*;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.util.*;
@RestController
@RequestMapping("/api/excel")
public class ExcelExportController {
/**
* 導(dǎo)出用戶數(shù)據(jù)
*/
@GetMapping("/export/users")
public void exportUsers(HttpServletResponse response) throws IOException {
// 準備數(shù)據(jù)
List<UserExcel> userList = new ArrayList<>();
UserExcel user1 = new UserExcel();
user1.setEmployeeNo("E001");
user1.setName("張三");
user1.setGender("男");
user1.setAge(28);
user1.setPhone("13800138000");
user1.setEmail("zhangsan@example.com");
user1.setDepartment("技術(shù)部");
user1.setPosition("Java開發(fā)工程師");
user1.setEntryDate(new Date());
user1.setSalary(15000.0);
user1.setStatus(UserExcel.UserStatus.ACTIVE);
user1.setRemark("優(yōu)秀員工");
userList.add(user1);
UserExcel user2 = new UserExcel();
user2.setEmployeeNo("E002");
user2.setName("李四");
user2.setGender("女");
user2.setAge(26);
user2.setPhone("13900139000");
user2.setEmail("lisi@example.com");
user2.setDepartment("產(chǎn)品部");
user2.setPosition("產(chǎn)品經(jīng)理");
user2.setEntryDate(new Date());
user2.setSalary(18000.0);
user2.setStatus(UserExcel.UserStatus.ACTIVE);
user2.setRemark("工作認真");
userList.add(user2);
// 設(shè)置響應(yīng)頭
response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
response.setCharacterEncoding("utf-8");
String fileName = "用戶信息_" + System.currentTimeMillis() + ".xlsx";
response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName);
// 導(dǎo)出Excel
ExcelExportUtil.export(userList, response.getOutputStream());
}
}
5.3 導(dǎo)入示例
package com.example.controller;
import com.example.entity.UserExcel;
import com.example.excel.util.ExcelImportUtil;
import org.springframework.web.bind.annotation.*;
import org.springframework.web.multipart.MultipartFile;
import java.io.IOException;
import java.util.HashMap;
import java.util.Map;
@RestController
@RequestMapping("/api/excel")
public class ExcelImportController {
/**
* 導(dǎo)入用戶數(shù)據(jù)
*/
@PostMapping("/import/users")
public Map<String, Object> importUsers(@RequestParam("file") MultipartFile file) {
Map<String, Object> result = new HashMap<>();
try {
// 導(dǎo)入Excel
ExcelImportUtil.ImportResult<UserExcel> importResult =
ExcelImportUtil.importExcel(file.getInputStream(), UserExcel.class);
// 檢查是否有錯誤
if (importResult.hasErrors()) {
result.put("success", false);
result.put("message", "導(dǎo)入失敗，存在錯誤數(shù)據(jù)");
result.put("errors", importResult.getErrors());
return result;
}
// 保存數(shù)據(jù)
List<UserExcel> userList = importResult.getDataList();
userService.batchSave(userList);
result.put("success", true);
result.put("message", "導(dǎo)入成功，共導(dǎo)入 " + userList.size() + " 條數(shù)據(jù)");
result.put("data", userList);
} catch (IOException e) {
result.put("success", false);
result.put("message", "文件讀取失敗：" + e.getMessage());
} catch (Exception e) {
result.put("success", false);
result.put("message", "導(dǎo)入失?。? + e.getMessage());
}
return result;
}
}
六、高級功能
6.1 動態(tài)列配置
package com.example.excel.config;
import com.example.excel.annotation.ExcelField;
import lombok.Data;
/**
* 動態(tài)列配置
*/
@Data
public class DynamicColumnConfig {
/**
* 列名
*/
private String name;
/**
* 字段名
*/
private String fieldName;
/**
* 列寬
*/
private int width = 15;
/**
* 數(shù)據(jù)類型
*/
private ExcelField.DataType dataType = ExcelField.DataType.STRING;
/**
* 格式
*/
private String format;
/**
* 是否顯示
*/
private boolean visible = true;
/**
* 排序
*/
private int order;
}
6.2 模板導(dǎo)出
package com.example.excel.util;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import java.io.InputStream;
import java.io.OutputStream;
import java.util.List;
import java.util.Map;
/**
* Excel模板導(dǎo)出工具
*/
public class ExcelTemplateUtil {
/**
* 基于模板導(dǎo)出
* @param templateStream 模板文件流
* @param dataMap 數(shù)據(jù)映射
* @param outputStream 輸出流
*/
public static void exportByTemplate(InputStream templateStream,
Map<String, Object> dataMap,
OutputStream outputStream) {
try (Workbook workbook = new XSSFWorkbook(templateStream)) {
Sheet sheet = workbook.getSheetAt(0);
// 替換模板中的占位符
replacePlaceholders(sheet, dataMap);
// 寫入輸出流
workbook.write(outputStream);
} catch (Exception e) {
throw new RuntimeException("模板導(dǎo)出失敗", e);
}
}
/**
* 替換占位符
*/
private static void replacePlaceholders(Sheet sheet, Map<String, Object> dataMap) {
for (Row row : sheet) {
for (Cell cell : row) {
if (cell.getCellType() == CellType.STRING) {
String value = cell.getStringCellValue();
if (value != null && value.startsWith("${") && value.endsWith("}")) {
String key = value.substring(2, value.length() - 1);
Object data = dataMap.get(key);
if (data != null) {
cell.setCellValue(data.toString());
}
}
}
}
}
}
}
6.3 數(shù)據(jù)驗證配置
package com.example.excel.validation;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddressList;
/**
* Excel數(shù)據(jù)驗證工具
*/
public class ExcelDataValidationUtil {
/**
* 添加下拉驗證
* @param sheet 工作表
* @param firstRow 起始行
* @param lastRow 結(jié)束行
* @param firstCol 起始列
* @param lastCol 結(jié)束列
* @param options 選項列表
*/
public static void addDropdownValidation(Sheet sheet, int firstRow, int lastRow,
int firstCol, int lastCol, String[] options) {
DataValidationHelper validationHelper = sheet.getDataValidationHelper();
// 創(chuàng)建下拉列表
DataValidationConstraint constraint = validationHelper.createExplicitListConstraint(options);
// 設(shè)置驗證區(qū)域
CellRangeAddressList addressList = new CellRangeAddressList(
firstRow, lastRow, firstCol, lastCol
);
// 創(chuàng)建數(shù)據(jù)驗證
DataValidation validation = validationHelper.createValidation(constraint, addressList);
// 設(shè)置錯誤提示
validation.setErrorStyle(DataValidation.ErrorStyle.STOP);
validation.createErrorBox("輸入錯誤", "請從下拉列表中選擇");
// 添加到工作表
sheet.addValidationData(validation);
}
/**
* 添加數(shù)字范圍驗證
* @param sheet 工作表
* @param firstRow 起始行
* @param lastRow 結(jié)束行
* @param firstCol 起始列
* @param lastCol 結(jié)束列
* @param min 最小值
* @param max 最大值
*/
public static void addNumberRangeValidation(Sheet sheet, int firstRow, int lastRow,
int firstCol, int lastCol, double min, double max) {
DataValidationHelper validationHelper = sheet.getDataValidationHelper();
// 創(chuàng)建數(shù)字約束
DataValidationConstraint constraint = validationHelper.createNumericConstraint(
DataValidationConstraint.ValidationType.DECIMAL,
DataValidationConstraint.OperatorType.BETWEEN,
String.valueOf(min),
String.valueOf(max)
);
// 設(shè)置驗證區(qū)域
CellRangeAddressList addressList = new CellRangeAddressList(
firstRow, lastRow, firstCol, lastCol
);
// 創(chuàng)建數(shù)據(jù)驗證
DataValidation validation = validationHelper.createValidation(constraint, addressList);
// 設(shè)置錯誤提示
validation.setErrorStyle(DataValidation.ErrorStyle.STOP);
validation.createErrorBox("輸入錯誤",
String.format("請輸入 %.2f 到 %.2f 之間的數(shù)字", min, max));
// 添加到工作表
sheet.addValidationData(validation);
}
}
七、性能優(yōu)化
7.1 大數(shù)據(jù)量處理
package com.example.excel.util;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import java.io.OutputStream;
import java.util.List;
/**
* 大數(shù)據(jù)量Excel導(dǎo)出工具
*/
public class BigDataExcelExportUtil {
/**
* 分批導(dǎo)出大數(shù)據(jù)量
* @param dataProvider 數(shù)據(jù)提供者
* @param outputStream 輸出流
* @param batchSize 批次大小
*/
public static <T> void exportBatch(DataProvider<T> dataProvider,
OutputStream outputStream,
int batchSize) {
try (SXSSFWorkbook workbook = new SXSSFWorkbook(1000)) { // 保留1000行在內(nèi)存中
Sheet sheet = workbook.createSheet("數(shù)據(jù)");
int rowNum = 0;
List<T> batch;
while ((batch = dataProvider.getNextBatch(batchSize)) != null && !batch.isEmpty()) {
for (T data : batch) {
Row row = sheet.createRow(rowNum++);
// 填充數(shù)據(jù)
fillRow(row, data);
}
// 定期刷新到磁盤
if (rowNum % 1000 == 0) {
((SXSSFSheet) sheet).flushRows();
}
}
workbook.write(outputStream);
} catch (Exception e) {
throw new RuntimeException("大數(shù)據(jù)量導(dǎo)出失敗", e);
}
}
/**
* 數(shù)據(jù)提供者接口
*/
public interface DataProvider<T> {
/**
* 獲取下一批數(shù)據(jù)
*/
List<T> getNextBatch(int batchSize);
}
private static <T> void fillRow(Row row, T data) {
// 實現(xiàn)數(shù)據(jù)填充邏輯
}
}
7.2 并行處理
package com.example.excel.util;
import java.util.List;
import java.util.concurrent.*;
import java.util.stream.Collectors;
/**
* 并行Excel處理工具
*/
public class ParallelExcelUtil {
private static final ExecutorService executor =
Executors.newFixedThreadPool(Runtime.getRuntime().availableProcessors());
/**
* 并行處理數(shù)據(jù)
* @param dataList 數(shù)據(jù)列表
* @param processor 處理器
* @return 處理結(jié)果列表
*/
public static <T, R> List<R> processParallel(List<T> dataList,
Processor<T, R> processor) {
List<CompletableFuture<R>> futures = dataList.stream()
.map(data -> CompletableFuture.supplyAsync(
() -> processor.process(data),
executor
))
.collect(Collectors.toList());
return futures.stream()
.map(CompletableFuture::join)
.collect(Collectors.toList());
}
/**
* 處理器接口
*/
public interface Processor<T, R> {
R process(T data);
}
/**
* 關(guān)閉線程池
*/
public static void shutdown() {
executor.shutdown();
}
}
八、最佳實踐與注意事項
8.1 使用建議
- 注解優(yōu)先：優(yōu)先使用注解配置，減少代碼侵入性
- 合理分批：大數(shù)據(jù)量導(dǎo)入導(dǎo)出時，使用分批處理
- 異常處理：完善的異常處理和錯誤提示
- 性能監(jiān)控：監(jiān)控導(dǎo)入導(dǎo)出性能，及時優(yōu)化
- 資源管理：使用try-with-resources確保資源釋放
8.2 常見問題
Q1：如何處理復(fù)雜的嵌套對象？
A：使用轉(zhuǎn)換器在導(dǎo)入導(dǎo)出時進行對象轉(zhuǎn)換，或者在實體類中增加扁平化字段。
Q2：如何處理動態(tài)列？
A：使用DynamicColumnConfig配置動態(tài)列，或者使用模板導(dǎo)出方式。
Q3：大數(shù)據(jù)量導(dǎo)出內(nèi)存占用過高？
A：使用SXSSFWorkbook的流式處理，設(shè)置合適的內(nèi)存緩存行數(shù)。
Q4：如何自定義樣式？
A：通過ExcelStyleManager創(chuàng)建自定義樣式，或者在注解中指定樣式配置。
8.3 團隊規(guī)范建議
- 統(tǒng)一注解規(guī)范：團隊內(nèi)統(tǒng)一使用相同的注解配置
- 命名規(guī)范：導(dǎo)出文件名包含業(yè)務(wù)和時間戳
- 數(shù)據(jù)驗證：所有導(dǎo)入數(shù)據(jù)必須經(jīng)過驗證
- 日志記錄：記錄導(dǎo)入導(dǎo)出的關(guān)鍵操作和錯誤
- 版本管理：Excel模板版本化管理
九、總結(jié)
本文實現(xiàn)了一個功能完善的通用Excel導(dǎo)入導(dǎo)出工具類，核心功能：
- 注解驅(qū)動，使用簡單
- 支持復(fù)雜多級表頭
- 支持動態(tài)列配置
- 內(nèi)置常用轉(zhuǎn)換器和驗證器
- 支持自定義轉(zhuǎn)換器和驗證器
- 完善的錯誤提示機制
- 大數(shù)據(jù)量性能優(yōu)化
- 模板導(dǎo)出支持
- 數(shù)據(jù)驗證支持
以上就是基于Java手寫一個通用的Excel導(dǎo)入和導(dǎo)出工具類的詳細內(nèi)容，更多關(guān)于JavaExcel導(dǎo)入和導(dǎo)出的資料請關(guān)注腳本之家其它相關(guān)文章！
您可能感興趣的文章:
- Java中大量數(shù)據(jù)Excel導(dǎo)入導(dǎo)出的完整實現(xiàn)過程
- Java基于POI實現(xiàn)Excel文件的導(dǎo)入導(dǎo)出
- Java POI實現(xiàn)Excel導(dǎo)入導(dǎo)出通用方法與樣式處理
- Java導(dǎo)入、導(dǎo)出excel用法步驟保姆級教程(附封裝好的工具類)
- java如何在項目中實現(xiàn)excel導(dǎo)入導(dǎo)出功能
- Java如何使用poi導(dǎo)入導(dǎo)出excel工具類
- Java使用POI實現(xiàn)excel文件的導(dǎo)入和導(dǎo)出
- Java實現(xiàn)Excel導(dǎo)入導(dǎo)出的步驟詳解
相關(guān)文章
SpringBoot3實現(xiàn)統(tǒng)一結(jié)果封裝的示例代碼
Spring Boot進行統(tǒng)一結(jié)果封裝的主要目的是提高開發(fā)效率、降低代碼重復(fù)率,并且提供一致的API響應(yīng)格式,從而簡化前后端交互和錯誤處理,所以本文給大家介紹了SpringBoot3實現(xiàn)統(tǒng)一結(jié)果封裝的方法,需要的朋友可以參考下2024-03-03
這篇文章主要介紹了Redisson分布式鎖的原理和代碼實例,在分布式系統(tǒng)中,鎖機制是非常重要的,Redisson是一個基于Redis的Java應(yīng)用程序,常常被應(yīng)用作為分布式鎖的解決方案,需要的朋友可以參考下2024-01-01
Java實現(xiàn)并發(fā)執(zhí)行定時任務(wù)并手動控制開始結(jié)束
這篇文章主要介紹了Java實現(xiàn)并發(fā)執(zhí)行定時任務(wù)并手動控制開始結(jié)束,具有很好的參考價值,希望對大家有所幫助,如有錯誤或未考慮完全的地方,望不吝賜教2024-05-05
這篇文章主要介紹了restTemplate遇到的編碼問題及解決方案，具有很好的參考價值，希望對大家有所幫助。如有錯誤或未考慮完全的地方，望不吝賜教2021-10-10
SpringBoot整合Graylog做日志收集實現(xiàn)過程
這篇文章主要為大家介紹了SpringBoot整合Graylog做日志收集實現(xiàn)過程詳解，有需要的朋友可以借鑒參考下，希望能夠有所幫助，祝大家多多進步，早日升職加薪2022-12-12
由于最近的項目需求，需要在把配置類導(dǎo)入到容器中，通過查詢，使用@Import注解就能實現(xiàn)這個功能，@Import注解能夠幫我們吧普通配置類（定義為Bean的類）導(dǎo)入到IOC容器中2023-01-01
java操作elasticsearch詳細方法總結(jié)
elasticsearch是使用Java編寫的一種開源搜索引擎，也是一種分布式的搜索引擎架構(gòu),這篇文章主要給大家介紹了關(guān)于java操作elasticsearch的相關(guān)資料,文中通過示例代碼介紹的非常詳細,需要的朋友可以參考下2022-12-12
Java中的Lambda表達式及其應(yīng)用小結(jié)
Java中的Lambda表達式是一項極具創(chuàng)新性的特性,它使得Java代碼更加簡潔和高效,尤其是在集合操作和并行處理方面,這篇文章主要介紹了Java中的Lambda表達式及其應(yīng)用,需要的朋友可以參考下2025-04-04
Java利用Apache POI實現(xiàn)Excel文件讀寫操作
Apache POI是一個用于讀寫Microsoft Office文件格式的Java庫,可以用來讀取或?qū)懭隕xcel文件,下面小編就和大家詳細講講Java借助Apache POI讀寫Excel的詳細代碼吧2026-02-02
詳解SpringBoot使用RedisTemplate操作Redis的5種數(shù)據(jù)類型
本文主要介紹了SpringBoot使用RedisTemplate操作Redis的5種數(shù)據(jù)類型，文中通過示例代碼介紹的非常詳細，具有一定的參考價值，感興趣的小伙伴們可以參考一下2022-03-03