@Override
public Response exportPersonnelExcel(String startTime, String endTime, HttpServletRequest request) {
File file = null;
List<List<String>> headLists = new ArrayList<>();
List<List<String>> valueLists = new ArrayList<>();
List<String> yearList = new ArrayList<>();
String beginTime = "";
String stopTime = "";
if (!StringUtils.isEmpty(startTime)) {
beginTime = StringUtils.substring(startTime, 0, 4);
}
if (!StringUtils.isEmpty(endTime)) {
stopTime = StringUtils.substring(endTime, 0, 4);
}
if (!StringUtils.isEmpty(startTime) && !StringUtils.isEmpty(endTime)) {
for (int i = 0; i <= Integer.valueOf(stopTime) - Integer.valueOf(beginTime); i++) {
yearList.add(String.valueOf(Integer.valueOf(beginTime) + i));
}
} else {
List<String> list = applyInfoMapper.selectYear();
for (int i = 0; i <= Integer.valueOf(list.get(list.size() - 1)) - Integer.valueOf(list.get(0)); i++) {
yearList.add(String.valueOf(Integer.valueOf(list.get(0)) + i));
}
}
if (null == yearList || yearList.size() <= 0) {
throw new AppBusinessException(BaseErrorCode.INVALID_ARGUMENT);
}
try {
int i = 1;
beginTime= yearList.get(0);
stopTime= yearList.get(yearList.size()-1);
List<LinkedHashMap<String, Object>> personnelDataList = assistActivityInfoMapper.getExportPersonnelData(beginTime, stopTime);
for (Map<String, Object> personData : personnelDataList) {
String userId = personData.get("user_id").toString();
List<AssistEquApplyInfo> onePersonData = applyInfoMapper.selectPesonData(userId, yearList);
List<String> valueList = new ArrayList<>();
valueList.add(String.valueOf(i));
valueList.add(personData.get("user_name").toString());
if (personData.get("sex") != null) {
valueList.add(personData.get("sex").toString());
} else {
valueList.add("未知");
}
valueList.add(personData.get("id_card").toString());
valueList.add(personData.get("age").toString());
String present = personData.get("present").toString();
if (present.contains("市")) {
String address = StringUtils.substringAfter(present, "市");
valueList.add(address);
} else {
valueList.add(present);
}
if (personData.get("phone") != null) {
valueList.add(personData.get("phone").toString());
} else
valueList.add("未知");
if (CollectionUtils.isEmpty(onePersonData)) {
for (String year : yearList) {
valueList.add("");
}
} else {
Map<String, String> map = onePersonData.stream().collect(Collectors.toMap(k -> k.getYearStr(), v -> v.getApplyReason()));
for (String year : yearList) {
if (map.containsKey(year)) {
valueList.add(map.get(year));
} else {
valueList.add("");
}
}
}
valueLists.add(valueList);
i++;
}
i = i - 1;
String title = beginTime + "-" + stopTime + "年伊滨区残疾人精准康复适配名单";
String header = title + "(" + i + "名)";
//处理标题
String[] tableHeaders = {"序号", "残疾人的名字", "性别", "残疾证/身份证", "年龄", "现住址", "固定电话/手机"};
for (String tableHeader : tableHeaders) {
//处理标题
List<String> addHeadList = new ArrayList<>();
addHeadList.add(header);
addHeadList.add(tableHeader);
headLists.add(addHeadList);
}
List<Map<String, Object>> personnelHeadList = assistActivityInfoMapper.getExportPersonnelHead(beginTime, stopTime);
if (null == personnelHeadList || personnelHeadList.size() <= 0) {
throw new AppBusinessException(BaseErrorCode.INVALID_ARGUMENT);
}
personnelHeadList.forEach(r -> {
List<String> headList = new ArrayList<>();
headList.add(header);
headList.add(r.get("endTime") + "年适配");
headLists.add(headList);
});
WriteCellStyle headWriteCellStyle = new WriteCellStyle();
setWriteCellStyle(headWriteCellStyle);
WriteFont headWriteFont = new WriteFont();
headWriteFont.setFontHeightInPoints((short) 12);
headWriteFont.setFontName("仿宋");
headWriteCellStyle.setFillForegroundColor(IndexedColors.WHITE.getIndex());
headWriteCellStyle.setWriteFont(headWriteFont);
//处理样式
WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
setWriteCellStyle(contentWriteCellStyle);
WriteFont contentWriteFont = new WriteFont();
contentWriteFont.setFontHeightInPoints((short) 12);
contentWriteFont.setFontName("仿宋");
contentWriteCellStyle.setWriteFont(contentWriteFont);
// 这个策略是 头是头的样式 内容是内容的样式 其他的策略可以自己实现
HorizontalCellStyleStrategy horizontalCellStyleStrategy =
new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);
//写入文件夹当中
String fileName = System.currentTimeMillis() + "." + "xls";
EasyExcel.write(privatePath + "/" + fileName)
// registerWriteHandler(new LongestMatchColumnWidthStyleStrategy())
.registerWriteHandler(new SimpleColumnWidthStyleStrategy(20))// 简单的列宽策略,列宽20
.registerWriteHandler(new SimpleRowHeightStyleStrategy((short) 25, (short) 25)). // 简单的行高策略:头行高,内容行高
registerWriteHandler(horizontalCellStyleStrategy)
.head(headLists)
.sheet(beginTime + "-" + stopTime + "适配")
.doWrite(valueLists);
file = new File(privatePath + "/" + fileName);
InputStream inputStream = new FileInputStream(file);
//处理输出流,下载文件
String fileNameD = CommonTool.getFileName(request, title + DateUtils.dateToStringYYYYMMDD(new Date()));
return Response.ok(inputStream, "application/vnd.ms-excel;charset=utf-8;")
.header("Content-Length", FileUtils.sizeOf(file))
.header("Access-Control-Expose-Headers", "Content-Disposition")
.header("Content-Disposition", "attachment; filename=" + fileNameD + ".xls")
.build();
} catch (Exception e) {
log.error("获取文件异常", e);
return Response.ok().build();
}
}
private void setWriteCellStyle(WriteCellStyle writeCellStyle) {
//设置边框样式
writeCellStyle.setBorderLeft(BorderStyle.THIN);
writeCellStyle.setBorderTop(BorderStyle.THIN);
writeCellStyle.setBorderRight(BorderStyle.THIN);
writeCellStyle.setBorderBottom(BorderStyle.THIN);
//水平加垂直居中
writeCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
writeCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
//自动换行
writeCellStyle.setWrapped(true);
}
多sheet导出 (注意要有结束语句)
file = new File(privatePath + "/" + fileName);
ExcelWriter build = EasyExcel.write(file, OfficialRegisterFgw.class).registerWriteHandler(horizontalCellStyleStrategy).build();
for (ExcelDataBean excelDataBean : list) {
String sheetName = excelDataBean.getSheetName();
List<OfficialRegisterFgw> datalist = excelDataBean.getDatalist();
WriteSheet sheet = EasyExcel.writerSheet(sheetName).head(OfficialRegisterFgw.class).registerWriteHandler(horizontalCellStyleStrategy).build();
build.write(datalist, sheet);
}
build.finish();