SpringBoot種如何使用?EasyExcel?實(shí)現(xiàn)自定義表頭導(dǎo)出并實(shí)現(xiàn)數(shù)據(jù)格式化轉(zhuǎn)換
更新時(shí)間：2024年11月12日 14:29:25 作者：魯子狄
本文詳細(xì)介紹了如何使用EasyExcel工具類實(shí)現(xiàn)自定義表頭導(dǎo)出,并實(shí)現(xiàn)數(shù)據(jù)格式化轉(zhuǎn)換與添加下拉框操作,通過示例和代碼,展示了如何處理不同數(shù)據(jù)結(jié)構(gòu)和注解,確保數(shù)據(jù)在導(dǎo)出時(shí)能夠正確顯示和格式化,此外,還介紹了如何解決特定數(shù)據(jù)類型的轉(zhuǎn)換問題,并提供了解決方案
SpringBoot3 使用 EasyExcel 封裝工具類實(shí)現(xiàn) 自定義表頭 導(dǎo)出并實(shí)現(xiàn) 數(shù)據(jù)格式化轉(zhuǎn)換 與 添加下拉框 操作
在現(xiàn)代企業(yè)應(yīng)用中，數(shù)據(jù)導(dǎo)出功能是非常常見的需求。特別是在處理大量數(shù)據(jù)時(shí)，將數(shù)據(jù)導(dǎo)出為 Excel 文件不僅方便用戶查看和分析，還能提高數(shù)據(jù)處理的效率。Apache POI 是一個(gè)常用的 Java Excel 處理庫(kù)，但它在處理大數(shù)據(jù)量時(shí)性能較差。為此，阿里巴巴開源了 EasyExcel，這是一個(gè)基于 Java 的簡(jiǎn)單、優(yōu)雅的 Excel 操作庫(kù)，它在處理大數(shù)據(jù)量時(shí)表現(xiàn)優(yōu)異。
本文將詳細(xì)介紹如何使用 EasyExcel 實(shí)現(xiàn)自定義表頭導(dǎo)出，并實(shí)現(xiàn)數(shù)據(jù)格式化轉(zhuǎn)換。
官網(wǎng) : EasyExcel
封裝了很多導(dǎo)出方法針對(duì)不同的業(yè)務(wù)場(chǎng)景,這里直接展示自定義表頭的代碼 .
1. 使用示例
Vo 示例(部分字段)
使用方法 export
用戶自定義表頭的 字段顯示名稱 , 需跟導(dǎo)出注解 @ExcelProperty 的 value 相對(duì)應(yīng) , 不然無法取到值
頁(yè)面顯示數(shù)據(jù)
導(dǎo)出 Excel 數(shù)據(jù)
2. 工具類
/**
* Excel相關(guān)處理
*
* @author 魯子狄
*/
@NoArgsConstructor(access = AccessLevel.PRIVATE)
public class ExcelUtil {
/**
* 導(dǎo)出 Excel 文件
*
* @param list 導(dǎo)出數(shù)據(jù)集合
* @param sheetName 工作表的名稱
* @param heads 表頭
* @param response 響應(yīng)體
*/
public static <T> void exportExcel(List<T> list, Class<T> clazz, String sheetName, List<List<String>> heads, HttpServletResponse response) {
try {
// 將對(duì)象列表轉(zhuǎn)換為自定義的數(shù)據(jù)列表
List<List<Object>> data = ExcelUtil.convertObjectsToExcelRows(list, heads);
// 重置響應(yīng)體，設(shè)置響應(yīng)頭
ExcelUtil.resetResponse(sheetName, response);
// 獲取響應(yīng)輸出流
ServletOutputStream os = response.getOutputStream();
// 導(dǎo)出 Excel 文件
ExcelUtil.exportExcel(data, clazz, heads, sheetName, false, os, null);
} catch (IOException e) {
throw new RuntimeException("導(dǎo)出 Excel 異常");
}
}
/**
* 重置響應(yīng)體
*/
private static void resetResponse(String sheetName, HttpServletResponse response) throws UnsupportedEncodingException {
String filename = ExcelUtil.encodingFilename(sheetName);
FileUtils.setAttachmentResponseHeader(response, filename);
response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=UTF-8");
}
/**
* 導(dǎo)出 Excel 文件
*
* @param data 導(dǎo)出數(shù)據(jù)集合
* @param heads 表頭
* @param sheetName 工作表的名稱
* @param merge 是否合并單元格
* @param os 輸出流
* @param options 級(jí)聯(lián)下拉選內(nèi)容
*/
public static <T> void exportExcel(List<List<Object>> data, Class<T> clazz, List<List<String>> heads, String sheetName, boolean merge,
OutputStream os, List<DropDownOptions> options) {
ExcelWriterSheetBuilder builder = EasyExcel.write(os, clazz)
.autoCloseStream(false)
// 自動(dòng)適配列寬
.registerWriteHandler(new LongestMatchColumnWidthStyleStrategy())
// 大數(shù)值自動(dòng)轉(zhuǎn)換，防止失真
.registerConverter(new ExcelBigNumberConvert())
.sheet(sheetName)
.head(heads);
if (merge) {
// 注冊(cè)合并單元格處理器
builder.registerWriteHandler(new CellMergeStrategy(data, true));
}
// 添加下拉框操作
builder.registerWriteHandler(new ExcelDownHandler(options, heads));
// 寫入數(shù)據(jù)
builder.doWrite(data);
}
/**
* convertObjectsToExcelRows 將對(duì)象列表轉(zhuǎn)換為 Excel 行數(shù)據(jù)
*
* @param list 對(duì)象集合
* @param heads 表頭
* @return {@link List<List<Object>>} Excel 行數(shù)據(jù)
*/
private static <T> List<List<Object>> convertObjectsToExcelRows(List<T> list, List<List<String>> heads) {
return list.stream()
// 將對(duì)象轉(zhuǎn)換為 Map
.map(ExcelUtil::convertObjectToMap)
.map(map -> {
List<Object> row = new ArrayList<>();
for (List<String> header : heads) {
// 假設(shè)表頭只有一個(gè)元素
String key = header.get(0);
// 根據(jù)表頭獲取對(duì)應(yīng)的值
row.add(map.get(key));
}
return row;
})
.toList();
}
/**
* convertObjectToMap 將對(duì)象轉(zhuǎn)換為 Map，鍵為 @ExcelProperty 注解中的列標(biāo)題，值為字段值。
*
* @param vo 對(duì)象
* @return {@link Map<String,Object>} 包含列標(biāo)題和字段值的 Map
*/
private static Map<String, Object> convertObjectToMap(Object vo) {
// 獲取對(duì)象的所有字段
return Arrays.stream(vo.getClass().getDeclaredFields())
// 過濾帶有 @ExcelProperty 注解的字段
.filter(field -> field.isAnnotationPresent(ExcelProperty.class))
.map(field -> {
try {
// 設(shè)置字段可訪問
field.setAccessible(true);
// 獲取字段名和值
String fieldName = field.getDeclaredAnnotation(ExcelProperty.class).value()[0];
Object fieldValue = field.get(vo);
// 檢查是否有 @ExcelDictFormat 注解
ExcelDictFormat dictFormat = field.getDeclaredAnnotation(ExcelDictFormat.class);
if (dictFormat != null && fieldValue != null) {
fieldValue = convertExcelDictFormat(fieldValue, dictFormat);
}
return new AbstractMap.SimpleEntry<>(fieldName, fieldValue);
} catch (IllegalAccessException e) {
throw new IllegalStateException("獲取字段值失敗: " + field.getName(), e);
}
})
// 過濾掉值為 null 的條目
.filter(entry -> entry.getValue() != null)
.collect(Collectors.toMap(
// 鍵為字段名
AbstractMap.SimpleEntry::getKey,
// 值為字段值
AbstractMap.SimpleEntry::getValue,
// 如果鍵重復(fù)，保留第一個(gè)值
(existing, replacement) -> existing,
// 使用 HashMap 存儲(chǔ)結(jié)果
HashMap::new
));
}
/**
* convertToExcelData 根據(jù) @ExcelDictFormat 注解轉(zhuǎn)換數(shù)據(jù)
*
* @param value 字段值
* @param dictFormat 注解信息
* @return 轉(zhuǎn)換后的值
*/
private static String convertExcelDictFormat(Object value, ExcelDictFormat dictFormat) {
String type = dictFormat.dictType();
String label;
if (StringUtils.isBlank(type)) {
label = convertByExp(value.toString(), dictFormat.readConverterExp(), dictFormat.separator());
} else {
label = SpringUtils.getBean(DictService.class).getDictLabel(type, value.toString(), dictFormat.separator());
}
return label;
}
}
/**
* 解析導(dǎo)出值 0=男,1=女,2=未知
*
* @param propertyValue 參數(shù)值
* @param converterExp 翻譯注解
* @param separator 分隔符
* @return 解析后值
*/
public static String convertByExp(String propertyValue, String converterExp, String separator) {
StringBuilder propertyString = new StringBuilder();
String[] convertSource = converterExp.split(StringUtils.SEPARATOR);
for (String item : convertSource) {
String[] itemArray = item.split("=");
if (StringUtils.containsAny(propertyValue, separator)) {
for (String value : propertyValue.split(separator)) {
if (itemArray[0].equals(value)) {
propertyString.append(itemArray[1] + separator);
break;
}
}
} else {
if (itemArray[0].equals(propertyValue)) {
return itemArray[1];
}
}
}
return StringUtils.stripEnd(propertyString.toString(), separator);
}注意
- 在使用
EasyExcel導(dǎo)出 Excel 文件時(shí)，convertToExcelData方法的調(diào)用與否取決于你如何傳遞數(shù)據(jù)以及數(shù)據(jù)的結(jié)構(gòu)。 - 傳入單個(gè)對(duì)象或?qū)ο罅斜頃r(shí)
- 當(dāng)你傳入一個(gè)對(duì)象列表（如
List<User>），EasyExcel會(huì)根據(jù)對(duì)象的屬性和字段上的注解來決定如何處理每個(gè)字段。如果某個(gè)字段上有@ExcelDictFormat注解，并且你已經(jīng)注冊(cè)了相應(yīng)的轉(zhuǎn)換器（如ExcelDictConvert），那么EasyExcel會(huì)在導(dǎo)出時(shí)調(diào)用convertToExcelData方法。- 傳入嵌套列表
List<List<Object>>時(shí) (自定義表頭使用) - 當(dāng)你傳入一個(gè)嵌套列表
List<List<Object>>時(shí)，EasyExcel不會(huì)自動(dòng)解析對(duì)象的屬性和字段注解。這是因?yàn)?List<List<Object>>是一個(gè)二維數(shù)組，每個(gè)元素都是一個(gè)Object，EasyExcel無法知道這些Object應(yīng)該如何轉(zhuǎn)換。 - 因?yàn)樽远x表頭使用
List<List<Object>>, 所以我的解決方案(convertObjectToMap方法)是:Map表示動(dòng)態(tài)數(shù)據(jù)：將List<List<Object>>轉(zhuǎn)換為List<Map<String, Object>>，其中鍵是字段名，值是字段值。- 手動(dòng)調(diào)用轉(zhuǎn)換器：在導(dǎo)出數(shù)據(jù)之前，手動(dòng)調(diào)用
convertToExcelData方法進(jìn)行數(shù)據(jù)轉(zhuǎn)換。
- 傳入嵌套列表
3. ExcelBigNumberConvert 大數(shù)值自動(dòng)轉(zhuǎn)換，防止失真
/**
* 大數(shù)值轉(zhuǎn)換
* Excel 數(shù)值長(zhǎng)度位15位 大于15位的數(shù)值轉(zhuǎn)換位字符串
*/
@Slf4j
public class ExcelBigNumberConvert implements Converter<Long> {
@Override
public Class<Long> supportJavaTypeKey() {
return Long.class;
}
@Override
public CellDataTypeEnum supportExcelTypeKey() {
return CellDataTypeEnum.STRING;
}
@Override
public Long convertToJavaData(ReadCellData<?> cellData, ExcelContentProperty contentProperty, GlobalConfiguration globalConfiguration) {
return Convert.toLong(cellData.getData());
}
@Override
public WriteCellData<Object> convertToExcelData(Long object, ExcelContentProperty contentProperty, GlobalConfiguration globalConfiguration) {
if (ObjectUtil.isNotNull(object)) {
String str = Convert.toStr(object);
if (str.length() > 15) {
return new WriteCellData<>(str);
}
}
WriteCellData<Object> cellData = new WriteCellData<>(new BigDecimal(object));
cellData.setType(CellDataTypeEnum.NUMBER);
return cellData;
}
}4. CellMergeStrategy 注冊(cè)合并單元格處理器
**
* 列值重復(fù)合并策略
*/
@Slf4j
public class CellMergeStrategy extends AbstractMergeStrategy implements WorkbookWriteHandler {
private final List<CellRangeAddress> cellList;
private final boolean hasTitle;
private int rowIndex;
public CellMergeStrategy(List<?> list, boolean hasTitle) {
this.hasTitle = hasTitle;
// 行合并開始下標(biāo)
this.rowIndex = hasTitle ? 1 : 0;
this.cellList = handle(list, hasTitle);
}
@Override
protected void merge(Sheet sheet, Cell cell, Head head, Integer relativeRowIndex) {
//單元格寫入了,遍歷合并區(qū)域,如果該Cell在區(qū)域內(nèi),但非首行,則清空
final int rowIndex = cell.getRowIndex();
if (CollUtil.isNotEmpty(cellList)){
for (CellRangeAddress cellAddresses : cellList) {
final int firstRow = cellAddresses.getFirstRow();
if (cellAddresses.isInRange(cell) && rowIndex != firstRow){
cell.setBlank();
}
}
}
}
@Override
public void afterWorkbookDispose(final WorkbookWriteHandlerContext context) {
//當(dāng)前表格寫完后，統(tǒng)一寫入
if (CollUtil.isNotEmpty(cellList)){
for (CellRangeAddress item : cellList) {
context.getWriteContext().writeSheetHolder().getSheet().addMergedRegion(item);
}
}
}
@SneakyThrows
private List<CellRangeAddress> handle(List<?> list, boolean hasTitle) {
List<CellRangeAddress> cellList = new ArrayList<>();
if (CollUtil.isEmpty(list)) {
return cellList;
}
Field[] fields = ReflectUtils.getFields(list.get(0).getClass(), field -> !"serialVersionUID".equals(field.getName()));
// 有注解的字段
List<Field> mergeFields = new ArrayList<>();
List<Integer> mergeFieldsIndex = new ArrayList<>();
for (int i = 0; i < fields.length; i++) {
Field field = fields[i];
if (field.isAnnotationPresent(CellMerge.class)) {
CellMerge cm = field.getAnnotation(CellMerge.class);
mergeFields.add(field);
mergeFieldsIndex.add(cm.index() == -1 ? i : cm.index());
if (hasTitle) {
ExcelProperty property = field.getAnnotation(ExcelProperty.class);
rowIndex = Math.max(rowIndex, property.value().length);
}
}
}
Map<Field, RepeatCell> map = new HashMap<>();
// 生成兩兩合并單元格
for (int i = 0; i < list.size(); i++) {
for (int j = 0; j < mergeFields.size(); j++) {
Field field = mergeFields.get(j);
Object val = ReflectUtils.invokeGetter(list.get(i), field.getName());
int colNum = mergeFieldsIndex.get(j);
if (!map.containsKey(field)) {
map.put(field, new RepeatCell(val, i));
} else {
RepeatCell repeatCell = map.get(field);
Object cellValue = repeatCell.getValue();
if (cellValue == null || "".equals(cellValue)) {
// 空值跳過不合并
continue;
}
if (!cellValue.equals(val)) {
if ((i - repeatCell.getCurrent() > 1)) {
cellList.add(new CellRangeAddress(repeatCell.getCurrent() + rowIndex, i + rowIndex - 1, colNum, colNum));
}
map.put(field, new RepeatCell(val, i));
} else if (i == list.size() - 1) {
if (i > repeatCell.getCurrent() && isMerge(list, i, field)) {
cellList.add(new CellRangeAddress(repeatCell.getCurrent() + rowIndex, i + rowIndex, colNum, colNum));
}
} else if (!isMerge(list, i, field)) {
if ((i - repeatCell.getCurrent() > 1)) {
cellList.add(new CellRangeAddress(repeatCell.getCurrent() + rowIndex, i + rowIndex - 1, colNum, colNum));
}
map.put(field, new RepeatCell(val, i));
}
}
}
}
return cellList;
}
private boolean isMerge(List<?> list, int i, Field field) {
boolean isMerge = true;
CellMerge cm = field.getAnnotation(CellMerge.class);
final String[] mergeBy = cm.mergeBy();
if (StrUtil.isAllNotBlank(mergeBy)) {
//比對(duì)當(dāng)前l(fā)ist(i)和list(i - 1)的各個(gè)屬性值一一比對(duì) 如果全為真 則為真
for (String fieldName : mergeBy) {
final Object valCurrent = ReflectUtil.getFieldValue(list.get(i), fieldName);
final Object valPre = ReflectUtil.getFieldValue(list.get(i - 1), fieldName);
if (!Objects.equals(valPre, valCurrent)) {
//依賴字段如有任一不等值,則標(biāo)記為不可合并
isMerge = false;
}
}
}
return isMerge;
}
@Data
@AllArgsConstructor
static class RepeatCell {
private Object value;
private int current;
}
}5. ExcelDownHandler 添加下拉框操作
/**
* <h1>Excel表格下拉選操作</h1>
* 考慮到下拉選過多可能導(dǎo)致Excel打開緩慢的問題，只校驗(yàn)前1000行
* <p>
* 即只有前1000行的數(shù)據(jù)可以用下拉框，超出的自行通過限制數(shù)據(jù)量的形式，第二次輸出
*/
@Slf4j
public class ExcelDownHandler implements SheetWriteHandler {
/**
* Excel表格中的列名英文
* 僅為了解析列英文，禁止修改
*/
private static final String EXCEL_COLUMN_NAME = "ABCDEFGHIJKLMNOPQRSTUVWXYZ";
/**
* 單選數(shù)據(jù)Sheet名
*/
private static final String OPTIONS_SHEET_NAME = "options";
/**
* 聯(lián)動(dòng)選擇數(shù)據(jù)Sheet名的頭
*/
private static final String LINKED_OPTIONS_SHEET_NAME = "linkedOptions";
/**
* 下拉可選項(xiàng)
*/
private final List<DropDownOptions> dropDownOptions;
/**
* 表頭
*/
private final List<List<String>> heads;
/**
* 當(dāng)前單選進(jìn)度
*/
private int currentOptionsColumnIndex;
/**
* 當(dāng)前聯(lián)動(dòng)選擇進(jìn)度
*/
private int currentLinkedOptionsSheetIndex;
private final DictService dictService;
public ExcelDownHandler(List<DropDownOptions> options, List<List<String>> heads) {
dropDownOptions = options;
this.heads = heads;
currentOptionsColumnIndex = 0;
currentLinkedOptionsSheetIndex = 0;
dictService = SpringUtils.getBean(DictService.class);
}
/**
* <h2>開始創(chuàng)建下拉數(shù)據(jù)</h2>
* 1.通過解析傳入的@ExcelProperty同級(jí)是否標(biāo)注有@DropDown選項(xiàng)
* 如果有且設(shè)置了value值，則將其直接置為下拉可選項(xiàng)
* <p>
* 2.或者在調(diào)用ExcelUtil時(shí)指定了可選項(xiàng)，將依據(jù)傳入的可選項(xiàng)做下拉
* <p>
* 3.二者并存，注意調(diào)用方式
*/
@Override
public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
Sheet sheet = writeSheetHolder.getSheet();
// 開始設(shè)置下拉框 HSSFWorkbook
DataValidationHelper helper = sheet.getDataValidationHelper();
Workbook workbook = writeWorkbookHolder.getWorkbook();
FieldCache fieldCache = ClassUtils.declaredFields(writeWorkbookHolder.getClazz(), writeWorkbookHolder);
for (Map.Entry<Integer, FieldWrapper> entry : fieldCache.getSortedFieldMap().entrySet()) {
Integer index = entry.getKey();
FieldWrapper wrapper = entry.getValue();
Field field = wrapper.getField();
// 循環(huán)實(shí)體中的每個(gè)屬性
// 可選的下拉值
List<String> options = new ArrayList<>();
if (field.isAnnotationPresent(ExcelDictFormat.class)) {
// 如果指定了@ExcelDictFormat，則使用字典的邏輯
ExcelDictFormat format = field.getDeclaredAnnotation(ExcelDictFormat.class);
String dictType = format.dictType();
String converterExp = format.readConverterExp();
if (StringUtils.isNotBlank(dictType)) {
// 如果傳遞了字典名，則依據(jù)字典建立下拉
Collection<String> values = Optional.ofNullable(dictService.getAllDictByDictType(dictType))
.orElseThrow(() -> new ServiceException(String.format("字典 %s 不存在", dictType)))
.values();
options = new ArrayList<>(values);
} else if (StringUtils.isNotBlank(converterExp)) {
// 如果指定了確切的值，則直接解析確切的值
List<String> strList = StringUtils.splitList(converterExp, format.separator());
options = StreamUtils.toList(strList, s -> StringUtils.split(s, "=")[1]);
}
} else if (field.isAnnotationPresent(ExcelEnumFormat.class)) {
// 否則如果指定了@ExcelEnumFormat，則使用枚舉的邏輯
ExcelEnumFormat format = field.getDeclaredAnnotation(ExcelEnumFormat.class);
List<Object> values = EnumUtil.getFieldValues(format.enumClass(), format.textField());
options = StreamUtils.toList(values, String::valueOf);
}
if (ObjectUtil.isNotEmpty(options)) {
// 如過自定義表頭不為空
if (CollUtil.isNotEmpty(heads)) {
// 獲取對(duì)應(yīng)的下拉表頭的下標(biāo)
index = IntStream.range(0, heads.size())
.filter(i -> wrapper.getHeads()[0].equals(heads.get(i).get(0)))
.findFirst().orElse(0);
}
// 僅當(dāng)下拉可選項(xiàng)不為空時(shí)執(zhí)行
if (options.size() > 20) {
// 這里限制如果可選項(xiàng)大于20，則使用額外表形式
dropDownWithSheet(helper, workbook, sheet, index, options);
} else {
// 否則使用固定值形式
dropDownWithSimple(helper, sheet, index, options);
}
}
}
if (CollUtil.isEmpty(dropDownOptions)) {
return;
}
dropDownOptions.forEach(everyOptions -> {
// 如果傳遞了下拉框選擇器參數(shù)
if (!everyOptions.getNextOptions().isEmpty()) {
// 當(dāng)二級(jí)選項(xiàng)不為空時(shí)，使用額外關(guān)聯(lián)表的形式
dropDownLinkedOptions(helper, workbook, sheet, everyOptions);
} else if (everyOptions.getOptions().size() > 10) {
// 當(dāng)一級(jí)選項(xiàng)參數(shù)個(gè)數(shù)大于10，使用額外表的形式
dropDownWithSheet(helper, workbook, sheet, everyOptions.getIndex(), everyOptions.getOptions());
} else if (!everyOptions.getOptions().isEmpty()) {
// 當(dāng)一級(jí)選項(xiàng)個(gè)數(shù)不為空，使用默認(rèn)形式
dropDownWithSimple(helper, sheet, everyOptions.getIndex(), everyOptions.getOptions());
}
});
}
/**
* <h2>簡(jiǎn)單下拉框</h2>
* 直接將可選項(xiàng)拼接為指定列的數(shù)據(jù)校驗(yàn)值
*
* @param celIndex 列index
* @param value 下拉選可選值
*/
private void dropDownWithSimple(DataValidationHelper helper, Sheet sheet, Integer celIndex, List<String> value) {
if (ObjectUtil.isEmpty(value)) {
return;
}
markOptionsToSheet(helper, sheet, celIndex, helper.createExplicitListConstraint(ArrayUtil.toArray(value, String.class)));
}
/**
* <h2>額外表格形式的級(jí)聯(lián)下拉框</h2>
*
* @param options 額外表格形式存儲(chǔ)的下拉可選項(xiàng)
*/
private void dropDownLinkedOptions(DataValidationHelper helper, Workbook workbook, Sheet sheet, DropDownOptions options) {
String linkedOptionsSheetName = String.format("%s_%d", ExcelDownHandler.LINKED_OPTIONS_SHEET_NAME, currentLinkedOptionsSheetIndex);
// 創(chuàng)建聯(lián)動(dòng)下拉數(shù)據(jù)表
Sheet linkedOptionsDataSheet = workbook.createSheet(WorkbookUtil.createSafeSheetName(linkedOptionsSheetName));
// 將下拉表隱藏
workbook.setSheetHidden(workbook.getSheetIndex(linkedOptionsDataSheet), true);
// 完善橫向的一級(jí)選項(xiàng)數(shù)據(jù)表
List<String> firstOptions = options.getOptions();
Map<String, List<String>> secoundOptionsMap = options.getNextOptions();
// 創(chuàng)建名稱管理器
Name name = workbook.createName();
// 設(shè)置名稱管理器的別名
name.setNameName(linkedOptionsSheetName);
// 以橫向第一行創(chuàng)建一級(jí)下拉拼接引用位置
String firstOptionsFunction = String.format("%s!$%s$1:$%s$1",
linkedOptionsSheetName,
ExcelDownHandler.getExcelColumnName(0),
ExcelDownHandler.getExcelColumnName(firstOptions.size())
);
// 設(shè)置名稱管理器的引用位置
name.setRefersToFormula(firstOptionsFunction);
// 設(shè)置數(shù)據(jù)校驗(yàn)為序列模式，引用的是名稱管理器中的別名
markOptionsToSheet(helper, sheet, options.getIndex(), helper.createFormulaListConstraint(linkedOptionsSheetName));
for (int columIndex = 0; columIndex < firstOptions.size(); columIndex++) {
// 先提取主表中一級(jí)下拉的列名
String firstOptionsColumnName = ExcelDownHandler.getExcelColumnName(columIndex);
// 一次循環(huán)是每一個(gè)一級(jí)選項(xiàng)
int finalI = columIndex;
// 本次循環(huán)的一級(jí)選項(xiàng)值
String thisFirstOptionsValue = firstOptions.get(columIndex);
// 創(chuàng)建第一行的數(shù)據(jù)
Optional.ofNullable(linkedOptionsDataSheet.getRow(0))
// 如果不存在則創(chuàng)建第一行
.orElseGet(() -> linkedOptionsDataSheet.createRow(finalI))
// 第一行當(dāng)前列
.createCell(columIndex)
// 設(shè)置值為當(dāng)前一級(jí)選項(xiàng)值
.setCellValue(thisFirstOptionsValue);
// 第二行開始，設(shè)置第二級(jí)別選項(xiàng)參數(shù)
List<String> secondOptions = secoundOptionsMap.get(thisFirstOptionsValue);
if (CollUtil.isEmpty(secondOptions)) {
// 必須保證至少有一個(gè)關(guān)聯(lián)選項(xiàng)，否則將導(dǎo)致Excel解析錯(cuò)誤
secondOptions = Collections.singletonList("暫無_0");
}
// 以該一級(jí)選項(xiàng)值創(chuàng)建子名稱管理器
Name sonName = workbook.createName();
// 設(shè)置名稱管理器的別名
sonName.setNameName(thisFirstOptionsValue);
// 以第二行該列數(shù)據(jù)拼接引用位置
String sonFunction = String.format("%s!$%s$2:$%s$%d",
linkedOptionsSheetName,
firstOptionsColumnName,
firstOptionsColumnName,
secondOptions.size() + 1
);
// 設(shè)置名稱管理器的引用位置
sonName.setRefersToFormula(sonFunction);
// 數(shù)據(jù)驗(yàn)證為序列模式，引用到每一個(gè)主表中的二級(jí)選項(xiàng)位置
// 創(chuàng)建子項(xiàng)的名稱管理器，只是為了使得Excel可以識(shí)別到數(shù)據(jù)
String mainSheetFirstOptionsColumnName = ExcelDownHandler.getExcelColumnName(options.getIndex());
for (int i = 0; i < 100; i++) {
// 以一級(jí)選項(xiàng)對(duì)應(yīng)的主體所在位置創(chuàng)建二級(jí)下拉
String secondOptionsFunction = String.format("=INDIRECT(%s%d)", mainSheetFirstOptionsColumnName, i + 1);
// 二級(jí)只能主表每一行的每一列添加二級(jí)校驗(yàn)
markLinkedOptionsToSheet(helper, sheet, i, options.getNextIndex(), helper.createFormulaListConstraint(secondOptionsFunction));
}
for (int rowIndex = 0; rowIndex < secondOptions.size(); rowIndex++) {
// 從第二行開始填充二級(jí)選項(xiàng)
int finalRowIndex = rowIndex + 1;
int finalColumIndex = columIndex;
Row row = Optional.ofNullable(linkedOptionsDataSheet.getRow(finalRowIndex))
// 沒有則創(chuàng)建
.orElseGet(() -> linkedOptionsDataSheet.createRow(finalRowIndex));
Optional
// 在本級(jí)一級(jí)選項(xiàng)所在的列
.ofNullable(row.getCell(finalColumIndex))
// 不存在則創(chuàng)建
.orElseGet(() -> row.createCell(finalColumIndex))
// 設(shè)置二級(jí)選項(xiàng)值
.setCellValue(secondOptions.get(rowIndex));
}
}
currentLinkedOptionsSheetIndex++;
}
/**
* <h2>額外表格形式的普通下拉框</h2>
* 由于下拉框可選值數(shù)量過多，為提升Excel打開效率，使用額外表格形式做下拉
*
* @param celIndex 下拉選
* @param value 下拉選可選值
*/
private void dropDownWithSheet(DataValidationHelper helper, Workbook workbook, Sheet sheet, Integer celIndex, List<String> value) {
// 創(chuàng)建下拉數(shù)據(jù)表
Sheet simpleDataSheet = Optional.ofNullable(workbook.getSheet(WorkbookUtil.createSafeSheetName(ExcelDownHandler.OPTIONS_SHEET_NAME)))
.orElseGet(() -> workbook.createSheet(WorkbookUtil.createSafeSheetName(ExcelDownHandler.OPTIONS_SHEET_NAME)));
// 將下拉表隱藏
workbook.setSheetHidden(workbook.getSheetIndex(simpleDataSheet), true);
// 完善縱向的一級(jí)選項(xiàng)數(shù)據(jù)表
for (int i = 0; i < value.size(); i++) {
int finalI = i;
// 獲取每一選項(xiàng)行，如果沒有則創(chuàng)建
Row row = Optional.ofNullable(simpleDataSheet.getRow(i))
.orElseGet(() -> simpleDataSheet.createRow(finalI));
// 獲取本級(jí)選項(xiàng)對(duì)應(yīng)的選項(xiàng)列，如果沒有則創(chuàng)建
Cell cell = Optional.ofNullable(row.getCell(currentOptionsColumnIndex))
.orElseGet(() -> row.createCell(currentOptionsColumnIndex));
// 設(shè)置值
cell.setCellValue(value.get(i));
}
// 創(chuàng)建名稱管理器
Name name = workbook.createName();
// 設(shè)置名稱管理器的別名
String nameName = String.format("%s_%d", ExcelDownHandler.OPTIONS_SHEET_NAME, celIndex);
name.setNameName(nameName);
// 以縱向第一列創(chuàng)建一級(jí)下拉拼接引用位置
String function = String.format("%s!$%s$1:$%s$%d",
ExcelDownHandler.OPTIONS_SHEET_NAME,
ExcelDownHandler.getExcelColumnName(currentOptionsColumnIndex),
ExcelDownHandler.getExcelColumnName(currentOptionsColumnIndex),
value.size());
// 設(shè)置名稱管理器的引用位置
name.setRefersToFormula(function);
// 設(shè)置數(shù)據(jù)校驗(yàn)為序列模式，引用的是名稱管理器中的別名
markOptionsToSheet(helper, sheet, celIndex, helper.createFormulaListConstraint(nameName));
currentOptionsColumnIndex++;
}
/**
* 掛載下拉的列，僅限一級(jí)選項(xiàng)
*/
private void markOptionsToSheet(DataValidationHelper helper, Sheet sheet, Integer celIndex,
DataValidationConstraint constraint) {
// 設(shè)置數(shù)據(jù)有效性加載在哪個(gè)單元格上,四個(gè)參數(shù)分別是：起始行、終止行、起始列、終止列
CellRangeAddressList addressList = new CellRangeAddressList(1, 1000, celIndex, celIndex);
markDataValidationToSheet(helper, sheet, constraint, addressList);
}
/**
* 掛載下拉的列，僅限二級(jí)選項(xiàng)
*/
private void markLinkedOptionsToSheet(DataValidationHelper helper, Sheet sheet, Integer rowIndex,
Integer celIndex, DataValidationConstraint constraint) {
// 設(shè)置數(shù)據(jù)有效性加載在哪個(gè)單元格上,四個(gè)參數(shù)分別是：起始行、終止行、起始列、終止列
CellRangeAddressList addressList = new CellRangeAddressList(rowIndex, rowIndex, celIndex, celIndex);
markDataValidationToSheet(helper, sheet, constraint, addressList);
}
/**
* 應(yīng)用數(shù)據(jù)校驗(yàn)
*/
private void markDataValidationToSheet(DataValidationHelper helper, Sheet sheet,
DataValidationConstraint constraint, CellRangeAddressList addressList) {
// 數(shù)據(jù)有效性對(duì)象
DataValidation dataValidation = helper.createValidation(constraint, addressList);
// 處理Excel兼容性問題
if (dataValidation instanceof XSSFDataValidation) {
//數(shù)據(jù)校驗(yàn)
dataValidation.setSuppressDropDownArrow(true);
//錯(cuò)誤提示
dataValidation.setErrorStyle(DataValidation.ErrorStyle.STOP);
dataValidation.createErrorBox("提示", "此值與單元格定義數(shù)據(jù)不一致");
dataValidation.setShowErrorBox(true);
//選定提示
dataValidation.createPromptBox("填寫說明：", "填寫內(nèi)容只能為下拉中數(shù)據(jù)，其他數(shù)據(jù)將導(dǎo)致導(dǎo)入失敗");
dataValidation.setShowPromptBox(true);
sheet.addValidationData(dataValidation);
} else {
dataValidation.setSuppressDropDownArrow(false);
}
sheet.addValidationData(dataValidation);
}
/**
* <h2>依據(jù)列index獲取列名英文</h2>
* 依據(jù)列index轉(zhuǎn)換為Excel中的列名英文
* <p>例如第1列，index為0，解析出來為A列</p>
* 第27列，index為26，解析為AA列
* <p>第28列，index為27，解析為AB列</p>
*
* @param columnIndex 列index
* @return 列index所在得英文名
*/
private static String getExcelColumnName(int columnIndex) {
// 26一循環(huán)的次數(shù)
int columnCircleCount = columnIndex / 26;
// 26一循環(huán)內(nèi)的位置
int thisCircleColumnIndex = columnIndex % 26;
// 26一循環(huán)的次數(shù)大于0，則視為欄名至少兩位
String columnPrefix = columnCircleCount == 0
? StrUtil.EMPTY
: StrUtil.subWithLength(ExcelDownHandler.EXCEL_COLUMN_NAME, columnCircleCount - 1, 1);
// 從26一循環(huán)內(nèi)取對(duì)應(yīng)的欄位名
String columnNext = StrUtil.subWithLength(ExcelDownHandler.EXCEL_COLUMN_NAME, thisCircleColumnIndex, 1);
// 將二者拼接即為最終的欄位名
return columnPrefix + columnNext;
}
}6. ExcelDictFormat 字典格式化注解
/**
* 字典格式化
*/
@Target({ElementType.FIELD})
@Retention(RetentionPolicy.RUNTIME)
@Inherited
public @interface ExcelDictFormat {
/**
* 如果是字典類型，請(qǐng)?jiān)O(shè)置字典的type值 (如: sys_user_sex)
*/
String dictType() default "";
/**
* 讀取內(nèi)容轉(zhuǎn)表達(dá)式 (如: 0=男,1=女,2=未知)
*/
String readConverterExp() default "";
/**
* 分隔符，讀取字符串組內(nèi)容
*/
String separator() default StringUtils.SEPARATOR;
}7. ExcelDictConvert 字典格式化轉(zhuǎn)換處理 (普通導(dǎo)出方法調(diào)用)
/**
* 字典格式化轉(zhuǎn)換處理
*/
@Slf4j
public class ExcelDictConvert implements Converter<Object> {
@Override
public Class<Object> supportJavaTypeKey() {
return Object.class;
}
@Override
public CellDataTypeEnum supportExcelTypeKey() {
return null;
}
@Override
public Object convertToJavaData(ReadCellData<?> cellData, ExcelContentProperty contentProperty, GlobalConfiguration globalConfiguration) {
ExcelDictFormat anno = getAnnotation(contentProperty.getField());
String type = anno.dictType();
String label = cellData.getStringValue();
String value;
if (StringUtils.isBlank(type)) {
value = ExcelUtil.reverseByExp(label, anno.readConverterExp(), anno.separator());
} else {
value = SpringUtils.getBean(DictService.class).getDictValue(type, label, anno.separator());
}
return Convert.convert(contentProperty.getField().getType(), value);
}
@Override
public WriteCellData<String> convertToExcelData(Object object, ExcelContentProperty contentProperty, GlobalConfiguration globalConfiguration) {
if (ObjectUtil.isNull(object)) {
return new WriteCellData<>("");
}
ExcelDictFormat anno = getAnnotation(contentProperty.getField());
String type = anno.dictType();
String value = Convert.toStr(object);
String label;
if (StringUtils.isBlank(type)) {
label = ExcelUtil.convertByExp(value, anno.readConverterExp(), anno.separator());
} else {
label = SpringUtils.getBean(DictService.class).getDictLabel(type, value, anno.separator());
}
return new WriteCellData<>(label);
}
private ExcelDictFormat getAnnotation(Field field) {
return AnnotationUtil.getAnnotation(field, ExcelDictFormat.class);
}
}8. 后續(xù)修改
1. 添加大標(biāo)題,自動(dòng)合并單元格
a. 添加大標(biāo)題
b. 修改取數(shù)據(jù)方法
修改取下標(biāo)為1的元素
/**
* convertObjectsToExcelRows 將對(duì)象列表轉(zhuǎn)換為 Excel 行數(shù)據(jù)
*
* @param list 對(duì)象集合
* @param heads 表頭
* @return {@link List<List<Object>>} Excel 行數(shù)據(jù)
*/
private static <T> List<List<Object>> convertObjectsToExcelRows(List<T> list, List<List<String>> heads) {
return list.stream()
// 將對(duì)象轉(zhuǎn)換為 Map
.map(ExcelUtil::convertObjectToMap)
.map(map -> {
List<Object> row = new ArrayList<>();
for (List<String> header : heads) {
String key = header.get(1);
// 根據(jù)表頭獲取對(duì)應(yīng)的值
row.add(map.get(key));
}
return row;
})
.toList();
}c. 導(dǎo)出方法
d. 導(dǎo)出內(nèi)容
2. 解決 Can not find ‘Converter’ support class LocalTime.
a. 原因
EasyExcel 默認(rèn)支持一些常見的數(shù)據(jù)類型，但對(duì)于 LocalTime 這樣的類型，可能需要自定義一個(gè)轉(zhuǎn)換器。
b. 解決方案
- 自定義
LocalTime轉(zhuǎn)換器 - 直接添加個(gè)數(shù)據(jù)轉(zhuǎn)換 (因?yàn)槭亲远x表頭,所以走此方法)
c.直接數(shù)據(jù)轉(zhuǎn)換(因不同的java版本寫法會(huì)有不同,我用的java17)
/**
* convertToExcelData 數(shù)據(jù)轉(zhuǎn)換
*
* @param value 值
* @return {@link java.lang.Object}
*/
private static Object convertToExcelData(Object value) {
if (ObjectUtil.isEmpty(value)) {
return value;
}
if (value instanceof LocalTime localTime) {
return localTime.format(DateTimeFormatter.ofPattern("HH:mm:ss"));
} else if (value instanceof LocalDateTime localDateTime) {
return localDateTime.format(DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss"));
} else if (value instanceof LocalDate localDate) {
return localDate.format(DateTimeFormatter.ofPattern("yyyy-MM-dd"));
} else {
return value;
}
}d. 修改 convertObjectToMap 方法
相關(guān)文章
Springboot創(chuàng)建項(xiàng)目的圖文教程（idea版本）
這篇文章主要介紹了Springboot創(chuàng)建項(xiàng)目的圖文教程（idea版本）,本文通過圖文并茂的形式給大家介紹的非常詳細(xì)，對(duì)大家的學(xué)習(xí)或工作具有一定的參考借鑒價(jià)值，需要的朋友可以參考下2020-06-06
如何在IDEA啟動(dòng)多個(gè)Spring Boot工程實(shí)例(圖文)
這篇文章主要介紹了如何在IDEA啟動(dòng)多個(gè)Spring Boot工程實(shí)例(圖文)，文中通過示例代碼介紹的非常詳細(xì)，對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值，需要的朋友們下面隨著小編來一起學(xué)習(xí)學(xué)習(xí)吧2019-09-09
Java注解如何基于Redission實(shí)現(xiàn)分布式鎖
這篇文章主要介紹了Java注解如何基于Redission實(shí)現(xiàn)分布式鎖,文中通過示例代碼介紹的非常詳細(xì)，對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值,需要的朋友可以參考下2020-01-01
Mybatis #foreach中相同的變量名導(dǎo)致值覆蓋的問題解決
本文主要介紹了Mybatis #foreach中相同的變量名導(dǎo)致值覆蓋的問題解決，具有一定的參考價(jià)值，感興趣的小伙伴們可以參考一下2021-07-07
Java視頻格式轉(zhuǎn)化的實(shí)現(xiàn)方法
這篇文章主要為大家詳細(xì)介紹了Java視頻格式轉(zhuǎn)化的實(shí)現(xiàn)方法，具有一定的參考價(jià)值，感興趣的小伙伴們可以參考一下2018-02-02
這篇文章介紹了java獲取日期的方法，有需要的朋友可以參考一下2013-10-10
Java實(shí)現(xiàn)文件檢索系統(tǒng)的示例代碼
這篇文章主要為大家詳細(xì)介紹了如何劉Java語(yǔ)言實(shí)現(xiàn)簡(jiǎn)易的文件檢索系統(tǒng)，文中的示例代碼講解詳細(xì)，對(duì)我們學(xué)習(xí)Java開發(fā)有一定的幫助，需要的可以參考一下2022-07-07
這篇文章主要介紹了Java圖片中顯示當(dāng)前時(shí)間的方法,需要的朋友可以參考下2017-09-09
關(guān)于Poi讀取Excel引發(fā)內(nèi)存溢出問題的解決方法
這篇文章主要給大家介紹了關(guān)于Poi讀取Excel引發(fā)內(nèi)存溢出問題的解決方法，文中通過示例代碼介紹的非常詳細(xì)，對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值，需要的朋友們下面跟著小編來一起學(xué)習(xí)學(xué)習(xí)吧。2017-08-08
這篇文章主要介紹了如何教你完全理解ReentrantLock重入鎖，文中通過示例代碼介紹的非常詳細(xì)，對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值，下面我們來一起學(xué)習(xí)一下吧2021-10-10