本文主要是介绍SpringBoot+EasyExcel实现自定义复杂样式导入导出,希望对大家解决编程问题提供一定的参考价值,需要的开发者们随着小编来一起学习吧!
《SpringBoot+EasyExcel实现自定义复杂样式导入导出》这篇文章主要为大家详细介绍了SpringBoot如何结果EasyExcel实现自定义复杂样式导入导出功能,文中的示例代码讲解详细,...
tips:能用模板就用模板,当模板不适用的情况下,再选择自定义生成 Excel。
官网:https://easyexcel.opensource.alibaba.com
安装
<dependency> <groupId>com.alibaba</groupId> <artifactId>easyexcel</artifactId> <version>3.1.1</version> </dependency>
处理自定义导出复杂场景
1、列不固定,动态列
2、动态下拉
3、自定义锁定行/列,添加密码
4、合并单元格
5、导入自定义统一注解统一校验
6、样式处理(字体,颜色,底色,富文本,列宽,行宽等)
7、冻结窗格
8、多Sheet处理
1、列不固定,动态列
首先定义一个公共实体,处理公共字段和动态列字段,具体实体则继承该类即可。
package com.example.springbootexcel.excel.base.model; import com.alibaba.excel.annotation.ExcelProperty; import lombok.Data; import Java.util.List; import java.util.Map; @Data public class BaseExcel { @ExcelProperty( value = "序号") private String num; /** * 动态字段python处理 */ private List<Map<String, Object>> dynamicList; }
2、动态下拉
封装一个公共类,构造入参Map,key为表头,value为下拉字符串数组。
.registerWriteHandler(new DropDownHandler(dropDownMap));
package com.example.springbootexcel.excel.base.style; import com.alibaba.excel.write.handler.SheetWriteHandler; import com.alibaba.excel.write.metadata.holder.WriteSheetHolder; import com.alibaba.excel.write.metadata.holder.WriteWorkbookHolder; import org.apache.poi.ss.usermodel.DataValidation; import org.apache.poi.ss.usermodel.DataValidationConstraint; import org.apache.poi.ss.usermodel.DataValidationHelper; import org.apache.poi.ss.usermodel.Sheet; import org.apache.poi.ss.util.CellRangeAddressList; import java.util.Map; /** * 添加下拉选单 * * @author jason */ public class DropDownHandler implements SheetWriteHandler { private final Map<Integer, String[]> dropDownMap; // key:列号(从0开始), value:下拉数据 public DropDownHandler(Map<Integer, String[]> dropDownMap) { this.dropDownMap = dropDownMap; } @Override public void beforeSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) { // 不需要实现 } @Override public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) { if (dropDownMap == null || dropDownMap.isEmpty()) { return; } Sheet sheet = writeSheetHolder.getSheet(); DataValidationHelper helper = sheet.getDataValidationHelper(); dropDownMap.forEach((columnIndex, dropDownData) -> { // 设置下拉框数据范围 (这里设置从第2行到第10000行) CellRangeAddressList addressList = new CellRangeAddressList(1, 9999, columnIndex, columnIndex); // 创建数据验证约束 DataValidationConstraint constraint = helper.createExplicitListConstraint(dropDownData); // 创建数据验证 DataValidation validation = helper.createValidation(constraint, addressList); // 阻止输入非下拉选项的值 validation.setErrorStyle(DataValidation.ErrorStyle.STOP); validation.setShowErrorBox(true); validation.setSuppressDropDownArrow(true); validation.createErrorBox("提示", "请从下拉选项中选择"); // 添加验证到sheet sheet.addValidationData(validation); }); } }
3、自定义锁定行/列,添加密码
@Override public void afterCellCreate(CellWriteHandlerContext context) { WriteSheetHolder writeSheetHolder = context.getWriteSheetHolder(); Sheet sheet = writeSheetHolder.getSheet(); Workbook workbook = sheet.getWorkbook(); Cell cell = context.getCell(); int columnIndex = cell.getColumnIndex(); Row row = cell.getRow(); // 设置工作表保护 if (!sheet.getProtect()) { XSSFSheet xssfSheet = (XSSFSheet) sheet; // 启用保护 xssfSheet.protectSheet("1234"); // 设置保护选项:允许删除未锁定行 xssfSheet.lockDeleteRows(false); // 设置保护选项:允许插入未锁定行 xssfSheet.lockInsertRows(false); } // 设置工作表的默认单元格样式为不锁定 CellStyle defaultStyle = workbook.createCellStyle(); defaultStyle.setLocked(false); sheet.setDefaultColumnStyle(columnIndex, defaultStyle); row.setRowStyle(defaultStyle); }
4、合并单元格
sheet.addMergedRegion(new CellRangeAddress(relativeRowIndex, relativeRowIndex, 0, 10));
5、导入自定义统一注解统一校验
package com.example.springbootexcel.excel.base.component; import java.lang.annotation.ElementType; import java.lang.annotation.Retention; import java.lang.annotation.RetentionPolicy; import java.lang.annotation.Target; /** * tips:非必填校验,填了就校验,不填不校验 */ @Retention(RetentionPolicy.RUNTIME) @Target({ElementType.FIELD}) public @interface ExcelValidation { /** * 日期校验 * * @return true表示必须为日期,false表示不限制 */ boolean date() default false; /** * 是否必须为数字 * * @return true表示必须为数字,false表示不限制 */ boolean numeric() default false; /** * 是否允许小数,且最多两位小数 * * @return true表示允许最多两位小数,false表示不允许小数 */ boolean decimal() default false; /** * 是否允许斜杠 * * @return true表示允许斜杠,false表示不允许 */ boolean allowSlash() default false; /** * 校验失败时的错误提示信息 * * @return 错误提示信息 */ String message() default "字段校验失败"; }
6、样式处理(字体,颜色,底色,富文本,列宽,行宽等)
// 基本样式设置 cellStyle.setBorderTop(BorderStyle.THIN); cellStyle.setBorderBottom(BorderStyle.THIN); cellStyle.setBorderLeft(BorderStyle.THIN); cellStyle.setBorderRight(BorderStyle.THIN); // 设置水平对齐为左对齐 cellStyle.setAlignment(HorizontalAlignment.LEFT); // 设置垂直对齐为垂直居中 cellStyle.setVerticalAlignment(VerticalAlignment.CENTER); // 设置自动换行 cellStyle.setWrapText(true); // 创建默认字体 Font defaultFont = workbook.createFont(); defaultFont.setFontName(DEFAULT_FONT_NAME); defaultFont.setFontHeightInPoints(DEFAULT_FONT_POINTS); defaultFont.setColor(IndexedColors.BLACK.getIndex()); defaultFont.setBold(false); // 创建红色字体 Font redFont = workbook.createFont(); redFont.setFontName(DEFAULT_FONT_NAME); redFont.setFontHeightInPoints(DEFAULT_FONT_POINTS); redFont.setColor(IndexedColors.RED.getIndex()); redFont.setBold(true); // 自定义列宽 String cellValue = cell.getStringCellValue(); Integer columnWidth = COLUMN_WIDTHS.get(cellValue); if (ObjectUtil.isNotNull(columnWidth) KjwdXm&& !CollectionUtil.contains(COLUMN_WIDTHS_EXIST, context.getColumnIndex())) { sheet.setColumnWidth(context.getColumnIndex(), columnWidth); COLUMN_WIDTHS_EXIST.add(context.getColumnIndex()); } // 设置默认宽度 if (!CollectionUtil.contains(COLUMN_WIDTHS_EXIST, context.getColumnIndex())) { sheet.setColumnWidth(context.getColumnIndex(), DEFAULT_COLUMN_WIDTH); } // 提示词 if (CollectionUtil.contains(TIPS_LIST, relativeRowIndex)) { defaultFont = redFont; // 合并单元格 sheet.addMergedRegion(new CellRangeAddress(relativeRowIndex, relativeRowIndex, 0, 10)); } // 表头 if (CollectionUtil.contains(HEAD_LIST, relativeRowIndex)) { defaultFont.setBold(true); // 背景色 cellStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex()); cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND); // 动态字段标红 if (CollectionUtil.contains(HEAD_READ_COLOR, cell.getColumnIndex())) { defaultFont = redFont; } else { // 星号标红 www.chinasem.cn RichTextString richText = cell.getRichStringCellValue(); if (StrUtil.startWith(cellValue, "*")) { richText.applyFont(0, 1, redFont); if (cellValue.length() > 1) { richText.applyFont(1, cellValue.length(), defaultFont); } cell.setCellValue(richText); } } } cellStyle.setFont(defaultFont);
7、冻结窗格
.registerWriteHandler(new FreezePaneHandler(2))
package com.example.springbootexcel.excel.base.style; import com.alibaba.excel.write.handler.SheetWriteHandler; import com.alibaba.excel.write.metadata.holder.WriteSheetHolder; import com.alibaba.excel.write.metadata.holder.WriteWorkbookHolder; import org.apache.poi.ss.usermodel.Sheet; /** * 冻结窗格 * * @author jason */ public class FreezePaneHandler implements SheetWriteHandler { private final int row; // 需要冻结的行 public FreezePaneHandler(int row) { this.row = row; } @Override public void beforeSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) { } @Override public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) { Sheet sheet = writeSheetHolder.getSheet(); // 冻结首行 // sheet.createFreezePane(0, 1, 0, 1); sheet.createFreezePane(0, row, 0, row); } }
8、多Sheet处理
// 创建 ExcelWriter 对象 ExcelWriter excelWriter = EasyExcel.write(filePath).inMemory(true).build(); // 写入第1个 Sheet WriteSheet sheet1 = EasyExcel.writerSheet("Sheet1") .registerWriteHandlerjavascript(new CommonStyleHandler(MockDataUtil.getHeadReadColor(headList, dynamicList))) .registerWriteHandler(new DropDownHandler(dropDownMap)) .registerW编程riteHandler(new FreezePaneHandler(2)) .build(); excelWriter.write(sheet1DataList, sheet1); // 写入第2个 Sheet WriteSheet sheet2 = EasyExcel.writerSheet("Sheet2") .head(BrandModelExcel.class) .registerWriteHandler(new FreezePaneHandler(1)) .build(); excelWriter.write(MockDataUtil.brandModelExcelList(), sheet2); // 写入第3个 Sheet WriteSheet sheet3 = EasyExcel.writerSheet("Sheet3") .head(VehicleNameExcel.class) .registerWriteHandler(new FreezePaneHandler(1)) .build(); excelWriter.write(MockDataUtil.vehicleNameExcelList(), sheet3); // 非常重要:最后一定要关闭 excelWriter excelWriter.finish(); log.info("导出成功:{}", filePath);
源码:https://gitee.com/zhaomingjian/workspace_dora/tree/master/spring-boot-excel
到此这篇关于SpringBoot+EasyExcel实现自定义复杂样式导入导出的文章就介绍到这了,更多相关SpringBoot EasyExcel自定义样式导入导出内容请搜索China编程(www.chinasem.cn)以前的文章或继续浏览下面的相关文章希望大家以后多多支持编程China编程(www.chinasem.cn)!
这篇关于SpringBoot+EasyExcel实现自定义复杂样式导入导出的文章就介绍到这儿,希望我们推荐的文章对编程师们有所帮助!