|
最近都是在写导出，那么为什么要用 easypoi呢，我用freemarker模板导出写好后，发现图片不行，word转Base64编码就行，excel不行，只能换一种了，一个导出弄一天。做个记录，你知道的越多，不知道的越多。
EasyPoi官网
展示效果（正面 sheet）
（反面 sheet）
1. EasyPoi模板导出
1.1 准备模板
用{{}}包裹变量就行，注意一点，图片所在的单元格不用提前合并。
这边有个遍历填充的 第一个单元格：{{$fe: maplist t.familyName
中间单元格直接 t.变量
最后一个单元格 t.变量 }}
虽然官网说的 {{$fe: maplist t t.familyName}}，但是不要这么写，不然第一列元素不填充。(t是默认)
1.2 准备依赖
cn.afterturn
easypoi-base
4.1.2
compile
cn.afterturn
easypoi-annotation
4.1.2
cn.afterturn
easypoi-web
4.1.2
com.google.guava
guava
20.0
org.apache.poi
poi
4.1.2
org.apache.poi
poi-ooxml
4.1.2
org.apache.poi
poi-ooxml-schemas
4.1.2
1.3 具体实现
大概就是首先查出相关数据，并根据要求处理好格式，拼数据什么的，将查出来的数据以map的key-value格式进行保存，再利用ExcelExportUtil生成Excel，到系统Excel文件的固定保存位置，然后再去读这个文件，以流的形式返回给前台，windows.open()，实现导出下载。一些路径我写死的，后续改成自己yml文件里的配置就行，不建议写死。
@GetMapping("/exportDisciplineLeader")
@ApiOperation("纪检干部导出")
@LogAnnotation("纪检干部导出")
public Result exportDisciplineLeader(HttpServletResponse response,String oid) {
try {
// 查询导出数据
HashMap map = disciplineLeaderInfoService.listDisciplineLeader(oid);
// 获取resource目录下的模板位置
String templateFileName = this.getClass().getResource("/").getPath() + "excelTemplate" + File.separator + "disciplineLeader.xls";
// 第二个参数true是为了开启多sheet扫描，就是同一个xls文件中有多个工作表的时候。
TemplateExportParams params = new TemplateExportParams(templateFileName, true);
// 数据载入，生成excel文件
Workbook book = ExcelExportUtil.exportExcel(params, map);
ArrayList maplist = (ArrayList) map.get("maplist");
// 纵向合并单元格
CellRangeAddress cellAddresses = new CellRangeAddress(1, maplist.size()+1, 0, 0);
book.getSheet("反面").addMergedRegion(cellAddresses);
try {
//设置导出文件名、创建输出流
FileOutputStream fos = new FileOutputStream("D:\\easypoi\\华侨城集团有限公司纪委书记(纪检干部)信息采集表.xls");
//导出excel文件
book.write(fos);
fos.close();
} catch (IOException e) {
e.printStackTrace();
}
// 以流的形式返回给前台
response.setContentType("application/force-download");// 设置强制下载不打开
response.addHeader("Content-Disposition", "attachment;fileName=" + URLEncoder.encode("华侨城集团有限公司纪委书记(纪检干部)信息采集表.xls", "UTF-8"));// 设置文件名
byte[] buffer = new byte[1024];
FileInputStream fis = null;
BufferedInputStream bis = null;
try {
fis = new FileInputStream("D:\\easypoi\\华侨城集团有限公司纪委书记(纪检干部)信息采集表.xls");
bis = new BufferedInputStream(fis);
OutputStream os = response.getOutputStream();
int i = bis.read(buffer);
while (i != -1) {
os.write(buffer, 0, i);
i = bis.read(buffer);
}
} catch (Exception e) {
e.printStackTrace();
} finally {
if (bis != null) {
try {
bis.close();
} catch (IOException e) {
e.printStackTrace();
}
}
if (fis != null) {
try {
fis.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
return null;
} catch (Exception e) {
log.error("纪检干部导出失败", e);
return Result.error().message("纪检干部导出失败");
}
}
大多数都是拼接数据的，看图片导出的设置直接到最后。
@Override
public HashMap listDisciplineLeader(String oid) throws IOException {
HashMap map = new HashMap();
DisciplineLeaderInfo disciplineLeaderInfo = disciplineLeaderInfoMapper.selectById(oid);
if (disciplineLeaderInfo != null) {
// 日期格式 yyyy年MM月dd日
SimpleDateFormat ymdFormat = new SimpleDateFormat("yyyy年MM月dd日");
SimpleDateFormat ymFormat = new SimpleDateFormat("yyyy年MM月");
// 姓名
map.put("personName", disciplineLeaderInfo.getPersonName());
// 性别
map.put("sexName", disciplineLeaderInfo.getSexName());
// 民族
map.put("nationName", disciplineLeaderInfo.getNationName());
// 出生日期
Date birthday = disciplineLeaderInfo.getBirthday();
String birthdayformat = ymdFormat.format(birthday);
map.put("birthday", birthdayformat);
// 籍贯
map.put("birthplace", disciplineLeaderInfo.getBirthplace());
// 政治面貌
map.put("politicStatuName", disciplineLeaderInfo.getPoliticStatuName());
// 入党时间
Date partyTime = disciplineLeaderInfo.getPartyTime();
String partFormat = ymFormat.format(partyTime);
map.put("partyTime", partFormat);
// 参加工作时间
Date workTime = disciplineLeaderInfo.getWorkTime();
String workTimeformat = ymFormat.format(workTime);
map.put("workTime", workTimeformat);
// 毕业时间
String[] graduationDate = disciplineLeaderInfo.getGraduationDate().split("-");
map.put("graduationDate", graduationDate[0] + "年" + graduationDate[1] + "月" + graduationDate[2] + "日");
// 户籍所在地
map.put("censusRegisterProvinceName", disciplineLeaderInfo.getCensusRegisterProvinceName());
map.put("censusRegisterCityName", disciplineLeaderInfo.getCensusRegisterCityName());
map.put("censusRegisterCountyName", disciplineLeaderInfo.getCensusRegisterCountyName());
// 身体状况
map.put("physicalCondition", disciplineLeaderInfo.getPhysicalCondition());
// 现任职务
map.put("presentOccupation", disciplineLeaderInfo.getPresentOccupation());
// 任现职时间
String[] lengthTenureDate = disciplineLeaderInfo.getLengthTenureDate().split("-");
map.put("lengthTenureDate", lengthTenureDate[0] + "年" + lengthTenureDate[1] + "月" + lengthTenureDate[2] + "日");
// 学历学位-全日制
map.put("allEducationBackgroundFirstName", disciplineLeaderInfo.getAllEducationBackgroundFirstName());
map.put("allEducationBackgroundSecondName", disciplineLeaderInfo.getAllEducationBackgroundSecondName());
// 毕业院校及专业
map.put("allEducationGraduateInstitutions", disciplineLeaderInfo.getAllEducationGraduateInstitutions());
// 学历学位-在职
map.put("inEducationBackgroundFirstName", disciplineLeaderInfo.getInEducationBackgroundFirstName());
map.put("inEducationBackgroundSecondName", disciplineLeaderInfo.getInEducationBackgroundSecondName());
// 毕业院校及专业
map.put("inEducationGraduateInstitutions", disciplineLeaderInfo.getInEducationGraduateInstitutions());
// 专业技术职称
map.put("professionalTechnical", disciplineLeaderInfo.getProfessionalTechnical());
// 熟悉专业有何专长
map.put("specialSkill", disciplineLeaderInfo.getSpecialSkill());
// 证件号码
map.put("cardId", disciplineLeaderInfo.getCardId());
// 婚姻状况
map.put("maritalStatusName", disciplineLeaderInfo.getMaritalStatusName());
// 通讯地址
map.put("mailingAddress", disciplineLeaderInfo.getMailingAddress());
// 联系方式
map.put("accusedTelOne", disciplineLeaderInfo.getAccusedTelOne());
// 工作履历
List lzdaRecordInformationInfos = lzdaRecordInformationInfoMapper.selectList(Wrappers.lambdaQuery()
.eq(LzdaRecordInformationInfo::getBusinessId, oid));
if (CollectionUtils.isNotEmpty(lzdaRecordInformationInfos)) {
StringBuffer workResume = new StringBuffer();
for (int i = 0; i < lzdaRecordInformationInfos.size(); i++) {
// 工作开始时间
String[] workStartDateSplit = lzdaRecordInformationInfos.get(i).getWorkStartDate().split("-");
String workStartDate = workStartDateSplit[0] + "年" + workStartDateSplit[1] + "月" + workStartDateSplit[2] + "日";
// 工作结束时间
String[] workEndDateSplit = lzdaRecordInformationInfos.get(i).getWorkEndDate().split("-");
String workEndDate = workEndDateSplit[0] + "年" + workEndDateSplit[1] + "月" + workEndDateSplit[2] + "日";
// 单位
String recordworkUnit = lzdaRecordInformationInfos.get(i).getRecordworkUnit();
// 职务
String positionName = lzdaRecordInformationInfos.get(i).getPositionName();
// 拼接数据+换行
workResume.append(i + 1).append("、").append(workStartDate + "-" + workEndDate + " " + recordworkUnit + positionName).append("\n");
}
map.put("workResume", workResume);
}
// 家庭成员及主要社会关系
List lzdaFamilyRelationInfos = lzdaFamilyRelationInfoMapper.selectList(Wrappers.lambdaQuery()
.eq(LzdaFamilyRelationInfo::getBusinessId, oid));
if (CollectionUtils.isNotEmpty(lzdaFamilyRelationInfos)) {
ArrayList listMap = new ArrayList();
for (LzdaFamilyRelationInfo lzdaFamilyRelationInfo : lzdaFamilyRelationInfos) {
HashMap famap = new HashMap();
// 家庭成员姓名
famap.put("familyName", lzdaFamilyRelationInfo.getFamilyName());
// 与本人关系
famap.put("familyRelationName", lzdaFamilyRelationInfo.getFamilyRelationName());
// 工作单位或从事事业
famap.put("familyWorkUnit", lzdaFamilyRelationInfo.getFamilyWorkUnit());
// 职务
famap.put("familyPositionName", lzdaFamilyRelationInfo.getFamilyPositionName());
listMap.add(famap);
}
// 不到6行添加空数据
if (lzdaFamilyRelationInfos.size() < 5) {
for (int i = 0; i < 6 - lzdaFamilyRelationInfos.size(); i++) {
HashMap temp = new HashMap();
// 家庭成员姓名
temp.put("familyName", "");
// 与本人关系
temp.put("familyRelationName", "");
// 工作单位或从事事业
temp.put("familyWorkUnit", "");
// 职务
temp.put("familyPositionName", "");
listMap.add(temp);
}
}
map.put("maplist", listMap);
}
// 执纪经历
List jjgbRulesSituations = jjgbRulesSituationMapper.selectList(Wrappers.lambdaQuery()
.eq(JjgbRulesSituation::getBusinessId, oid));
if (CollectionUtils.isNotEmpty(jjgbRulesSituations)) {
StringBuffer rulesSituation = new StringBuffer();
for (int i = 0; i < jjgbRulesSituations.size(); i++) {
// 执纪开始时间
String ruleStartDate = ymFormat.format(jjgbRulesSituations.get(i).getRuleStartDate());
// 执纪结束时间
String ruleEndDate = ymFormat.format(jjgbRulesSituations.get(i).getRuleEndDate());
// 执纪经历
String ruleExperience = jjgbRulesSituations.get(i).getRuleExperience();
// 拼接数据+换行
rulesSituation.append(i + 1).append("、").append(ruleStartDate + "-" + ruleEndDate + " " + ruleExperience).append("\n");
map.put("rulesSituation", rulesSituation);
}
}
// 参加培训学习情况
map.put("learningCondition", disciplineLeaderInfo.getLearningCondition());
// 奖励处分集合
List rewardPunishmentInfoList = disciplineLeaderInfo.getRewardPunishmentInfoList();
if (CollectionUtils.isNotEmpty(rewardPunishmentInfoList)) {
StringBuffer rewardPunishment = new StringBuffer();
for (int i = 0; i < rewardPunishmentInfoList.size(); i++) {
// 拼接数据+换行
rewardPunishment.append(i + 1).append("、").append(rewardPunishmentInfoList.get(i).getRewardPunishmentDesignation() + "," + rewardPunishmentInfoList.get(i).getRewardPunishmentTypeName()).append("\n");
}
map.put("rewardPunishment", rewardPunishment);
}
// 考核结果
List threeYearAssessmentResults = disciplineLeaderInfo.getThreeYearAssessmentResults();
if (CollectionUtils.isNotEmpty(threeYearAssessmentResults)) {
StringBuffer assessment = new StringBuffer();
for (int i = 0; i < threeYearAssessmentResults.size(); i++) {
String assessmentDate = ymFormat.format(threeYearAssessmentResults.get(i).getAssessmentDate());
String assessGradeName = threeYearAssessmentResults.get(i).getAssessGradeName();
// 拼接数据+换行
assessment.append(i + 1).append("、").append(assessmentDate + "年度考核等次为" + assessGradeName).append("\n");
}
map.put("assessment", assessment);
}
MultiMediaInfo multiMediaInfo = multiMediaInfoMapper.selectOne(Wrappers.lambdaQuery().eq(MultiMediaInfo::getBusinessOid, oid));
// 图片
ImageEntity image = new ImageEntity();
// 这里是设置合并单元格，但是千万不要再模板你提前合并单元格。合并了这里会报错。行合并多少个格子在这里设置。
image.setRowspan(4);//向下合并三行
//添加图片存放路径 D:\IMG_0783.JPG
image.setUrl(multiMediaInfo.getFilePath());
map.put("image", image);
}
return map;
}
|