想要导出的样式
看需求要用那种方式的，使用注解一样可以导出杂表头，我的表头是动态的所以用的代码写
//Excel注解依赖
<dependency>
<groupId>cn.afterturn</groupId>
<artifactId>easypoi-annotation</artifactId>
<version>4.2.0</version>
</dependency>
//ExcelExportEntity操作类依赖
<dependency>
<groupId>cn.afterturn</groupId>
<artifactId>easypoi-base</artifactId>
<version>4.2.0</version>
</dependency>这是查找的资料(比我这全面些)：
基于easypoi的自定义excel表头导出-蒲公英云 (dandelioncloud.cn)
(49条消息) easypoi以最简单的方式实现多级表头导入导出_子非我鱼的博客-优快云博客_easypoi多级表头
数据结构:
{
"userId": 662,
"username": "wk0060",
"realName": "哈喽",
"employmentTime": "2022-05-14",
"phone": "18478343530",
"deptName": "采购组",
"mainPostName": "职务",
//工卡详情信息
"jobCardExtendDetailsVO": {
//性格维度
"characterExtendVOS": [
{
"characterTypeId": 1,
"selfAssessment": "自我评定：活泼开朗",
"leaderAssessment": "部门领导评定：活泼开朗",
"managerAssessment": "总经理评定：活泼开朗",
"jobCardId": 1,
"characterDesc": "领导者"
},
{
"characterTypeId": 2,
"selfAssessment": "自我评定：活泼开朗",
"leaderAssessment": "部门领导评定：活泼开朗",
"managerAssessment": "总经理评定：活泼开朗",
"jobCardId": 1,
"characterDesc": "组织者"
},
{
"characterTypeId": 3,
"selfAssessment": "自我评定：活泼开朗",
"leaderAssessment": "部门领导评定：活泼开朗",
"managerAssessment": "总经理评定：活泼开朗",
"jobCardId": 1,
"characterDesc": "沟通者"
},
{
"characterTypeId": 4,
"selfAssessment": "自我评定：活泼开朗",
"leaderAssessment": "部门领导评定：活泼开朗",
"managerAssessment": "总经理评定：活泼开朗",
"jobCardId": 1,
"characterDesc": "协调者"
}
],
//自驱动能力
"selfDrivingExtendVOS": [
{
"selfDrivingTypeId": 1,
"selfAssessment": "自我评定：初级",
"leaderAssessment": "部门领导评定：初级",
"managerAssessment": "总经理评定：初级",
"jobCardId": 1,
"selfDriving": "计划力",
"selfDrivingDesc": null
},
{
"selfDrivingTypeId": 2,
"selfAssessment": "自我评定：初级",
"leaderAssessment": "部门领导评定：初级",
"managerAssessment": "总经理评定：初级",
"jobCardId": 1,
"selfDriving": "决策力",
"selfDrivingDesc": null
},
{
"selfDrivingTypeId": 3,
"selfAssessment": "自我评定：初级",
"leaderAssessment": "部门领导评定：初级",
"managerAssessment": "总经理评定：初级",
"jobCardId": 1,
"selfDriving": "自我定位力",
"selfDrivingDesc": null
}
],
//技术能力
"skillExtendVOS": [
{
"skiilTypeId": 1,
"skillName": "C++",
"selfAssessment": "自我评定：高级",
"leaderAssessment": "部门领导评定：中级",
"managerAssessment": "总经理评定：中级",
"jobCardId": 1,
"skillType": "语言",
"skillDesc": "描述"
},
{
"skiilTypeId": 2,
"skillName": "PS",
"selfAssessment": "自我评定：高级",
"leaderAssessment": "部门领导评定：中级",
"managerAssessment": "总经理评定：中级",
"jobCardId": 1,
"skillType": "UI/UE",
"skillDesc": "描述"
},
{
"skiilTypeId": 3,
"skillName": "研发级产品",
"selfAssessment": "自我评定：高级",
"leaderAssessment": "部门领导评定：中级",
"managerAssessment": "总经理评定：中级",
"jobCardId": 1,
"skillType": "产品",
"skillDesc": "描述"
}
],
//管理类型
"managementAbilityExtendVOS": [
{
"managementTypeId": 1,
"selfAssessment": "自我评定：无",
"leaderAssessment": "部门领导评定：无",
"managerAssessment": "总经理评定：无",
"jobCardId": 1,
"managementType": "主管",
"managementDesc": "描述"
}
],
//思维类型
"thinkingAbilityExtendVOS": [
{
"thinkingAbilityTypeId": 1,
"selfAssessment": "自我评定：初",
"leaderAssessment": "部门领导评定：中",
"managerAssessment": "部门领导评定：中",
"jobCardId": 1,
"thinkingAbilityType": "初",
"thinkingAbilityDesc": "描述"
}
],
//HR推荐岗位
"jobRecommendationExtend": {
"jobRecommendation": "Java开发",
"jobCardId": 1
}
},
//一对多的职务
"postExtends": null
}现在所实现的：
代码：
public void exportJobCardExtendDetails(UserDTO dto, HttpServletResponse response) {
//查询数据
List<UserInfoVO> userDetails = findUserDetails(dto);
userDetails.forEach(userInfo -> {
userInfo.setJobCardExtendDetailsVO(findJobCardExtendDetailByUserId(userInfo.getUserId()));
});
// 表头定义 可以将表头配置在数据库中，然后在代码里动态生成表头
// 这里只是展示如何用代码生成表头
List<ExcelExportEntity> columnList = new ArrayList<ExcelExportEntity>();
ExcelExportEntity colEntity1 = new ExcelExportEntity("序号", "id");
colEntity1.setNeedMerge(true);//是否需要纵向合并单元格(用于含有list中,单个的单元格,合并list创建的多个row)
columnList.add(colEntity1);
ExcelExportEntity colEntity2 = new ExcelExportEntity("部门", "deptName");
colEntity2.setNeedMerge(true);
columnList.add(colEntity2);
ExcelExportEntity colEntity3 = new ExcelExportEntity("姓名", "realName");
colEntity3.setNeedMerge(true);
columnList.add(colEntity3);
ExcelExportEntity colEntity4 = new ExcelExportEntity("入职日期", "employmentTime");
colEntity4.setNeedMerge(true);
//导出时间设置,如果字段是Date类型则不需要设置 数据库如果是string 类型,这个需要设置这个数据库格式,用以转换时间格式输出
colEntity4.setDatabaseFormat("yyyy-MM-dd");
colEntity4.setFormat("yyyy-MM-dd");//时间格式,相当于同时设置了exportFormat 和 importFormat
columnList.add(colEntity4);
ExcelExportEntity evaluatePersonnel = new ExcelExportEntity("", "evaluatePersonnel");
List<ExcelExportEntity> personnelExcelList = new ArrayList<ExcelExportEntity>();
//evaluatePersonnel.setMergeVertical(true);//纵向合并内容相同的单元格
//evaluatePersonnel.setMergeRely(new int[]{0});//合并单元格依赖关系,比如第二列合并是基于第一列 则{0}就可以了
evaluatePersonnel.setList(personnelExcelList);
columnList.add(evaluatePersonnel);
ExcelExportEntity character = new ExcelExportEntity("性格力", "character");
List<ExcelExportEntity> characterExcelList = new ArrayList<ExcelExportEntity>();
List<CharacterTypeExtend> characterTypeExtendList = characterTypeExtendService.findCharacterTypeExtendList(new CharacterTypeExtend());
characterTypeExtendList.forEach(characterType -> {
characterExcelList.add(new ExcelExportEntity(characterType.getCharacters(), "C" + characterType.getId()));
});
character.setList(characterExcelList);
columnList.add(character);
ExcelExportEntity selfDriving = new ExcelExportEntity("自驱动能力", "selfDriving");
selfDriving.setGroupName("自驱动能力");
List<ExcelExportEntity> selfDrivingExcelList = new ArrayList<ExcelExportEntity>();
//自驱动能力
List<SelfDrivingTypeExtend> selfDrivingTypeExtendList = selfDrivingTypeExtendService.findSelfDrivingTypeExtendList(new SelfDrivingTypeExtend());
selfDrivingTypeExtendList.forEach(selfDrivingType2 -> {
selfDrivingExcelList.add(new ExcelExportEntity(selfDrivingType2.getSelfDriving(), "C" + selfDrivingType2.getId()));
});
selfDriving.setList(selfDrivingExcelList);
columnList.add(selfDriving);
//固定数据
List<Map<String, Object>> personnelList = new ArrayList<Map<String, Object>>();
Map<String, Object> self = new HashMap<String, Object>();
self.put("personnel", "自我评定");
personnelList.add(self);
Map<String, Object> leader1 = new HashMap<String, Object>();
leader1.put("personnel", "部门领导评定");
personnelList.add(leader1);
Map<String, Object> leader2 = new HashMap<String, Object>();
leader2.put("personnel", "部门领导评定");
personnelList.add(leader2);
// 数据拉取 一般需要从数据库中拉取
// 这里是手动模拟数据
List<Map<String, Object>> dataList = new ArrayList<Map<String, Object>>();
for (int i = 0; i < userDetails.size(); i++) {
Map<String, Object> values = new HashMap<>();
values.put("id", i + 1);
values.put("deptName", userDetails.get(i).getDeptName());
values.put("realName", userDetails.get(i).getRealName());
values.put("employmentTime", userDetails.get(i).getEmploymentTime());
values.put("evaluatePersonnel", personnelList);
List<Map<String, Object>> cList = new ArrayList<>();
Map<String, Object> cs = new HashMap<String, Object>();
Map<String, Object> cl = new HashMap<String, Object>();
Map<String, Object> cm = new HashMap<String, Object>();
for (CharacterTypeExtend ct : characterTypeExtendList) {
userDetails.get(i).getJobCardExtendDetailsVO().getCharacterExtendVOS().forEach(uct -> {
if (uct.getId().equals(ct.getId())) {
cs.put("C" + uct.getId(), uct.getSelfAssessment());
cl.put("C" + uct.getId(), uct.getLeaderAssessment());
cm.put("C" + uct.getId(), uct.getManagerAssessment());
}
});
}
cList.add(cs);
cList.add(cl);
cList.add(cm);
values.put("character", cList);
List<Map<String, Object>> sList = new ArrayList<>();
Map<String, Object> ss = new HashMap<String, Object>();
Map<String, Object> sl = new HashMap<String, Object>();
Map<String, Object> sm = new HashMap<String, Object>();
for (SelfDrivingTypeExtend st : selfDrivingTypeExtendList) {
userDetails.get(i).getJobCardExtendDetailsVO().getSelfDrivingExtendVOS().forEach(ust -> {
if (ust.getId().equals(st.getId())) {
ss.put("C" + ust.getId(), ust.getSelfAssessment());
sl.put("C" + ust.getId(), ust.getLeaderAssessment());
sm.put("C" + ust.getId(), ust.getManagerAssessment());
}
});
}
sList.add(ss);
sList.add(sl);
sList.add(sm);
values.put("selfDriving", sList);
dataList.add(values);
}
// 定义标题和sheet名称
ExportParams exportParams = new ExportParams("工卡信息表", "工卡信息表");
Workbook workbook = ExcelExportUtil.exportExcel(exportParams, columnList, dataList);
// 导入到本地目录，如果需要从浏览器导出，参看上一篇文章
ServletOutputStream outputStream = null;
try {
//用流的方式传出
response.setHeader("content-type", "application/octet-stream");
//防止中文乱码Content-disposition", "attachment;filename=
response.setHeader("content-disposition", "attachment;filename=" + URLEncoder.encode("工卡信息表.xlsx", "UTF-8"));
outputStream = response.getOutputStream();
workbook.write(outputStream);//导出数据
} catch (IOException e) {
e.printStackTrace();
} finally {
//关闭流
if (outputStream != null) {
try {
outputStream.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
}上面的三级表头尝试过使用ExcelExportEntity 的分组GroupName, 但是不可行, 有没有大佬来聊下解决方法
扫一扫
1万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
