EasyExcel之多行表头+自定义表头样式+一行样例+自动列宽+多个sheet页+多行冻结+下拉框+web中的写_kingo谢

http://blog.sina.com.cn/u/3821593180

首页博文目录关于我

个人资料

微博

加好友发纸条

写留言加关注

博客等级：
博客积分：

博客访问：
关注人气：
获赠金笔：0支
赠出金笔：0支
荣誉徽章：

正文字体大小：大中小

EasyExcel之多行表头+自定义表头样式+一行样例+自动列宽+多个sheet页+多行冻结+下拉框+web中的写

(2021-02-19 14:30:08)

标签：

java

excel

分类： EasyExcel

功能需求：根据不同业务的Dto导出统一表头样式且带有下拉框和一行样例的excel模板，让用户在excel中填写数据做批量添加功能。

因为部分数据是需要从数据库实时选取载入下拉框，所以需要在导出excel时在对应的单元格列生成动态下拉框，同时校验用户在下拉框单元格中是否输入有效数据，非下拉框内容弹出提示框。

特别注意：Excel下拉框内容限制255字符内，解决办法是下拉框的数据写入一个隐藏的sheet页某列，再引入隐藏的sheet页某列数据。

导入maven依赖

com.alibaba
easyexcel
2.2.7

创建excel对应的实体对象，多行表头

@Data

@ApiModel(description = "用户数据模板dto")

public class SysUserDataTempDto {

@ApiModelProperty(value = "*员工姓名")

@ExcelProperty(value = {"用户信息", "*员工姓名"}, index = 1)

private String name;

......

@ApiModelProperty(value = "备注")

@ExcelProperty(value = {"用户信息", "备注"}, index = 10)

private String note;

@ApiModelProperty(value = "行号")

@ExcelIgnore

private Integer rowIndex;

}

自定义表头样式

@Slf4j

public class HeadStyleHandler extends AbstractCellStyleStrategy {

public HeadStyleHandler() {

}

@Override

protected void initCellStyle(Workbook workbook) {

}

@Override

protected void setHeadCellStyle(Cell cell, Head head, Integer integer) {

Workbook workbook = cell.getSheet().getWorkbook();

CellStyle headCellStyle = workbook.createCellStyle();

headCellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);

headCellStyle.setFillForegroundColor(IndexedColors.YELLOW1.getIndex());

Font cellFont = workbook.createFont();

cellFont.setBold(true);

cellFont.setColor((short) 2);

cellFont.setFontHeightInPoints((short) 15);

headCellStyle.setFont(cellFont);

headCellStyle.setBorderBottom(BorderStyle.THIN);

headCellStyle.setBorderLeft(BorderStyle.THIN);

headCellStyle.setBorderRight(BorderStyle.THIN);

headCellStyle.setBorderTop(BorderStyle.THIN);

headCellStyle.setAlignment(HorizontalAlignment.CENTER);

headCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);

cell.setCellStyle(headCellStyle);

}

@Override

protected void setContentCellStyle(Cell cell, Head head, Integer integer) {

Sheet sheet = cell.getSheet();

Workbook workbook = sheet.getWorkbook();

CellStyle cellStyle = workbook.createCellStyle();

cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);

cellStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());

cellStyle.setBorderBottom(BorderStyle.THIN);

cellStyle.setBorderLeft(BorderStyle.THIN);

cellStyle.setBorderRight(BorderStyle.THIN);

cellStyle.setBorderTop(BorderStyle.THIN);

cell.setCellStyle(cellStyle);

}

自定义下拉框拦截器

public class AutoDropDownBoxHandler implements SheetWriteHandler {

private static final Logger LOGGER = LoggerFactory.getLogger(AutoDropDownBoxHandler.class);

private Map mapDropDown;

public AutoDropDownBoxHandler(Map mapDropDown) {

this.mapDropDown = mapDropDown;

}

@Override

public void beforeSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {

}

@Override

public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {

int count = 0;

//获取工作簿

Sheet sheet = writeSheetHolder.getSheet();

//设置冻结前三行

sheet.createFreezePane(0,3);

//设置下拉框

DataValidationHelper helper = sheet.getDataValidationHelper();

for (Map.Entry entry : mapDropDown.entrySet()) {

//起始行、终止行、起始列、终止列

CellRangeAddressList addressList = new CellRangeAddressList(3, 65535, entry.getKey(), entry.getKey());

//获取一个workbook

Workbook workbook = writeWorkbookHolder.getWorkbook();

//定义sheet的名称

String hiddenName = writeSheetHolder.getSheetName() + count;

//1.创建一个隐藏的sheet 名称为 hidden

Sheet hidden = workbook.createSheet(hiddenName);

//2.循环赋值（为了防止下拉框的行数与隐藏域的行数相对应，将隐藏域加到结束行之后）

for (int j = 0, length = entry.getValue().length; j < length; j++) {

// 3:表示你开始的行数 3表示你开始的列数

hidden.createRow(65535 + j).createCell(0).setCellValue(entry.getValue()[j]);

}

Name category1Name = workbook.createName();

category1Name.setNameName(hiddenName);

//4 A1:A代表隐藏域创建第N列createCell(N)时。以A1列开始A行数据获取下拉数组

category1Name.setRefersToFormula(hiddenName + "!A" + entry.getKey() + 1 + ":A" + (65535 * 2));

//5 将刚才设置的sheet引用到你的下拉列表中

DataValidationConstraint constraint = helper.createFormulaListConstraint(hiddenName + "!A" + entry.getKey() + 1 + ":A" + (65535 * 2));

DataValidation dataValidation = helper.createValidation(constraint, addressList);

//错误提示信息

dataValidation.createErrorBox("输入有误", "请输入下拉框中的数据");

//设置是否显示错误窗口

dataValidation.setShowErrorBox(true);

sheet.addValidationData(dataValidation);

count++;

// 隐藏sheet

int hiddenSheetNum = workbook.getNumberOfSheets() - 1;

workbook.setSheetHidden(hiddenSheetNum, true);

}

定义一个map,key是需要添加下拉框的列的index,value是下拉框数据

Map mapDropDown = new HashMap<>(1);

Map bankMapDropDown = new HashMap<>(1);

String[] sexArray = this.getSexEnumForDropDownBox();

String[] deptArray = this.getDeptEnumForDropDownBox();

String[] employmentFormArray = this.getEmploymentFormEnumForDropDownBox();

String[] isMainArray = this.getBooleanEnumForDropDownBox();

mapDropDown.put(7, sexArray);

mapDropDown.put(3, deptArray);

mapDropDown.put(4, deptArray);

mapDropDown.put(5, employmentFormArray);

bankMapDropDown.put(3, isMainArray);

模板写入一行样例

private List createUserExampleData() {

List sysUserDataTempList = new ArrayList();

SysUserDataTempDto exampleData = new SysUserDataTempDto();

exampleData.setName("手动填写,例:张XX");

...

exampleData.setUsername("手动填写,例:wx_zhangsan");

exampleData.setSex("下拉框");

exampleData.setPhone("手动填写,例:189XXXXXXXX");

exampleData.setEmail("手动填写,例:XXX@qq.com");

exampleData.setNote("手动填写,例:XXX");

sysUserDataTempList.add(exampleData);

return sysUserDataTempList;

}

web中的写

*sheet页的名称必须是字母、数字、句点或下划线且第一个字符必须是下划线或字母

response.setContentType("application/vnd.ms-excel");

response.setCharacterEncoding("utf-8");

String fileName = URLEncoder.encode("用户数据导入模板", "UTF-8").replaceAll("\\+", " ");

response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");

ExcelWriter excelWriter = EasyExcel.write(response.getOutputStream()).build();

// 第1个sheet页

WriteSheet writeSheet1 = EasyExcel.writerSheet(0, "用户信息").head(SysUserDataTempDto.class)

.registerWriteHandler(new HeadStyleHandler())

// 自动列宽

.registerWriteHandler(new LongestMatchColumnWidthStyleStrategy())

.registerWriteHandler(new AutoDropDownBoxHandler(mapDropDown)).build();

excelWriter.write(createUserExampleData(), writeSheet1);

// 第2个sheet页

WriteSheet writeSheet2 = EasyExcel.writerSheet(1, "银行卡信息").head(SysUserBankDataTempDto.class)

.registerWriteHandler(new HeadStyleHandler())

.registerWriteHandler(new LongestMatchColumnWidthStyleStrategy())

.registerWriteHandler(new AutoDropDownBoxHandler(bankMapDropDown)).build();

excelWriter.write(createUserBankExampleData(), writeSheet2);

//关闭流

excelWriter.finish();

参考链接：

https://www.yuque.com/easyexcel/doc/write#591ee418

https://www.yuque.com/easyexcel/doc/write#1bea3540

https://www.yuque.com/easyexcel/doc/write#afb7324a

https://www.yuque.com/easyexcel/doc/write#b8e4013d

https://www.yuque.com/easyexcel/doc/write#b94382ae

https://blog.csdn.net/h1192341606/article/details/106378030

https://blog.csdn.net/qq_32495261/article/details/108482413

阅读┊ 收藏 ┊ 喜欢 ▼ ┊打印┊举报/Report

前一篇：Linux之使用scp+密钥+echo，远程无密码传输文件

后一篇：IDEA打开RunDashboard面板

新浪BLOG意见反馈留言板　欢迎批评指正