@Override
public void downloadWorkerZip(Integer hospitalId, String searchStr, Date entryDate, Integer serviceAge, Integer state, Integer sex, Integer photo) {
Page page = this.workerRecordList(hospitalId, searchStr, entryDate, serviceAge, state, sex, photo, 0, 0);
List<WorkerRecordVo> data = page.getData(); //所有护工
if(org.apache.commons.collections.CollectionUtils.isEmpty(data)){
throw new ServiceException(ExtendedErrorCode.E201.code(),"无可导出数据");
}
String nursingIds = data.stream().map(WorkerDO::getNursingId).map(String::valueOf).collect(Collectors.joining(","));
String sql="select id,attendant_id,cert_name,cert_type,acquisition_time,expire_time,pictures from ns_attendant_cert " +
"where attendant_id in (" + nursingIds +") and is_delete =0";
//所有护工的职业信息
List<AttendantCertDO> allCertDOs = extendDaoSupport.queryForList(sql, AttendantCertDO.class);
//所有护工的工作经历
sql="select id,attendant_id,company_name,entry_date,leave_date,leave_reason,labels " +
"from ns_attendant_work_history where attendant_id in (" + nursingIds +") and is_delete=0";
List<AttendantWorkHistoryVO> allWorkHistory = extendDaoSupport.queryForList(sql, AttendantWorkHistoryVO.class);
data.forEach(vo->{
if (!CollectionUtils.isEmpty(allCertDOs)) {
List<AttendantCertDO> attendantCertDOs = allCertDOs.stream().filter(item -> item.getAttendantId().equals(vo.getNursingId())).collect(Collectors.toList());
vo.setAttendantCertDOList(attendantCertDOs);
}
if (!CollectionUtils.isEmpty(allWorkHistory)) {
List<AttendantWorkHistoryVO> historyVOS = allWorkHistory.stream().filter(item -> item.getAttendantId().equals(vo.getNursingId())).collect(Collectors.toList());
vo.setWorkHistoryVOList(historyVOS);
}
});
this.exportWorkerZip(data);
}
private void exportWorkerZip(List<WorkerRecordVo> data){
HttpServletResponse response = ThreadContextHolder.getHttpResponse();
String[] baseColumns = {"护工姓名", "护工编号",
"所属医院", "手机号码",
"身份证号", "民族",
"性别", "出生日期",
"籍贯", "户籍地址",
"家庭地址", "星级","是否有保险",
"文化程度", "婚姻状况",
"语言能力", "入职日期",
"紧急联系人", "紧急联系电话",
"开户行名称", "开户行代码",
"银行卡号", "账户类型",
"开户证件类型", "账户名",
"证件号","银行预留号码","体检时间","有效期"};
List<String> list = Arrays.asList(baseColumns);
List<String> columnNames = new ArrayList<>();
columnNames.addAll(list);
//护理证书最多的记录
WorkerRecordVo certWorkerRecord = data.stream().filter(vo->vo.getAttendantCertDOList()!=null).max(Comparator.comparingInt(vo -> vo.getAttendantCertDOList().size())).orElse(null);
if (!ObjectUtils.isEmpty(certWorkerRecord)) {
List<AttendantCertDO> attendantCertDOList = certWorkerRecord.getAttendantCertDOList().stream().filter(vo->Integer.valueOf(0).equals(vo.getCertType())).collect(Collectors.toList());
for (int i = 1; i <= attendantCertDOList.size(); i++) {
columnNames.add("护理证书"+i);
columnNames.add("获取时间"+i);
}
}
//筛选出工作经历最多的护工
WorkerRecordVo workerRecordVo = data.stream().filter(vo->vo.getWorkHistoryVOList()!=null).max(Comparator.comparingInt(vo -> vo.getWorkHistoryVOList().size())).orElse(null);
if (!ObjectUtils.isEmpty(workerRecordVo)) {
List<AttendantWorkHistoryVO> workHistoryList = workerRecordVo.getWorkHistoryVOList();
for (int i = 1; i <= workHistoryList.size(); i++) {
columnNames.add("入职公司"+i);
columnNames.add("入职时间"+i);
columnNames.add("离职时间"+i);
columnNames.add("离职原因"+i);
}
}
String sheetName = "护工档案信息表";
String[] headers = columnNames.toArray(new String[columnNames.size()]);
// 创建excel工作簿
HSSFWorkbook workBook = CreateWorkerSheetUtil.createWorkerSheet(headers, sheetName);
//获取工作表sheet
HSSFSheet sheet = workBook.getSheet(sheetName);
//填充数据
for (int i = 0; i < data.size(); i++) {
WorkerRecordVo entity = data.get(i);
HSSFRow nextRow = sheet.createRow(i + 2);
Integer column = 0;
nextRow.createCell(column++).setCellValue(entity.getName());
nextRow.createCell(column++).setCellValue(entity.getEmployeeNumber());
nextRow.createCell(column++).setCellValue(entity.getHospitalName());
nextRow.createCell(column++).setCellValue(entity.getMobile());
nextRow.createCell(column++).setCellValue(entity.getIdCardNumber());
nextRow.createCell(column++).setCellValue(entity.getNation());
nextRow.createCell(column++).setCellValue(entity.getSex()==null?"":entity.getSex()==1?"男":"女");
nextRow.createCell(column++).setCellValue(entity.getBirthday()==null?"":DateUtil.toString(entity.getBirthday(),"yyyy-MM-dd"));
nextRow.createCell(column++).setCellValue(entity.getNativePlace());// "籍贯
nextRow.createCell(column++).setCellValue(entity.getPermanentAddress());//户籍地址
nextRow.createCell(column++).setCellValue(entity.getResidentialAddress());//家庭地址
nextRow.createCell(column++).setCellValue(entity.getStarLevel()==null?"":getStartLevel(entity.getStarLevel())); //星级 0:普通护工;4:四星护工;5:五星护工
nextRow.createCell(column++).setCellValue(entity.getInsurance()==null?"":entity.getInsurance()==1?"有":"无");//是否有保险
nextRow.createCell(column++).setCellValue(getEducation(entity.getEducation()==null?0:entity.getEducation()));//文化程度 0:无;1:小学,2:初中,3:高中,4:大学',
nextRow.createCell(column++).setCellValue(entity.getMaritalStatus()==null?"":getMaritalStatus(entity.getMaritalStatus()));//婚姻状况 0:未婚 1:已婚 2:离异
nextRow.createCell(column++).setCellValue(entity.getLanguageAbility());
nextRow.createCell(column++).setCellValue(entity.getEntryDate()==null?"":DateUtil.toString(entity.getEntryDate(),"yyyy-MM-dd"));
nextRow.createCell(column++).setCellValue(entity.getEmergencyContact());
nextRow.createCell(column++).setCellValue(entity.getEmergencyPhone());
nextRow.createCell(column++).setCellValue(entity.getBankName());//开户行名称
nextRow.createCell(column++).setCellValue(entity.getBankCode());
nextRow.createCell(column++).setCellValue(entity.getAccountNo());//银行卡号
nextRow.createCell(column++).setCellValue(entity.getAccountType());//账户类型
nextRow.createCell(column++).setCellValue(entity.getIdType());
nextRow.createCell(column++).setCellValue(entity.getAccountName());//账户名
nextRow.createCell(column++).setCellValue(entity.getLicenseNum());//证件号
nextRow.createCell(column++).setCellValue(entity.getTelephone());//银行预留号码
List<AttendantCertDO> entityCertDOList = entity.getAttendantCertDOList(); //所有职业信息
if (!CollectionUtils.isEmpty(entityCertDOList)) {
//健康证
AttendantCertDO attendantCertDO = entityCertDOList.stream().filter(vo -> Integer.valueOf(1).equals(vo.getCertType())).findFirst().orElse(null);
if (!ObjectUtils.isEmpty(attendantCertDO)) {
nextRow.createCell(column++).setCellValue(attendantCertDO.getAcquisitionTime()==null?"":DateUtil.toString(attendantCertDO.getAcquisitionTime(),"yyyy-MM-dd"));//体检时间(时间戳)
nextRow.createCell(column++).setCellValue(attendantCertDO.getExpireTime()==null?"":DateUtil.toString(attendantCertDO.getExpireTime(),"yyyy-MM-dd"));//有效期(时间戳)
}
//职业证书
List<AttendantCertDO> certList = entityCertDOList.stream().filter(vo -> Integer.valueOf(0).equals(vo.getCertType())).collect(Collectors.toList());
if (!CollectionUtils.isEmpty(certList)) {
for (AttendantCertDO certDO : certList) {
nextRow.createCell(column++).setCellValue(certDO.getCertName());//证书名
nextRow.createCell(column++).setCellValue(certDO.getAcquisitionTime()==null?"":DateUtil.toString(certDO.getAcquisitionTime(),"yyyy-MM-dd"));//有效期(时间戳)
}
}
}
int maxCertSize = certWorkerRecord==null?0:certWorkerRecord.getAttendantCertDOList().size();
int certSize = entityCertDOList == null ? 0 : entityCertDOList.size();
int sub = maxCertSize * 2 - certSize * 2;
column=column+sub;
List<AttendantWorkHistoryVO> workHistoryVOList = entity.getWorkHistoryVOList();//所有工作经历
if (!CollectionUtils.isEmpty(workHistoryVOList)) {
for (AttendantWorkHistoryVO historyVO : workHistoryVOList) {
nextRow.createCell(column++).setCellValue(historyVO.getCompanyName());//公司名
nextRow.createCell(column++).setCellValue(historyVO.getEntryDate()==null?"":DateUtil.toString(historyVO.getEntryDate(),"yyyy-MM-dd"));//入职(时间戳)
nextRow.createCell(column++).setCellValue(historyVO.getLeaveDate()==null?"":DateUtil.toString(historyVO.getLeaveDate(),"yyyy-MM-dd"));//离职
nextRow.createCell(column++).setCellValue(historyVO.getLeaveReason());//原因
}
}
}
Map<String,String[]> workerPics = getWorkerPics(data);
response.setContentType("application/octet-stream");
response.setHeader("Access-Control-Expose-Headers", "downloadFileName, Content-Disposition");
try {
response.setHeader("downloadFileName", URLEncoder.encode("护工档案.zip", "UTF-8"));
response.setHeader("Content-Disposition", "attachment;filename*=UTF-8''" + URLEncoder.encode("护工档案.zip", "UTF-8"));
} catch (UnsupportedEncodingException e) {
e.printStackTrace();
}
try {
/* ServletOutputStream out = response.getOutputStream();
workBook.write(out);
out.close();*/这段代码可以单独下载一个表格使用
//将图片放入压缩包
ZipOutputStream zos = new ZipOutputStream(response.getOutputStream());
workerPics.forEach((key,value)->{
for (int i = 0; i < value.length; i++) {
try {
URL url = new URL(value[i]);
zos.putNextEntry(new ZipEntry(key+i+".jpg"));
InputStream fis = url.openConnection().getInputStream();
byte[] buffer = new byte[1024];
int r = 0;
while ((r = fis.read(buffer)) != -1) {
zos.write(buffer, 0, r);
}
fis.close();
} catch (IOException e) {
e.printStackTrace();
}
}
});
//将表格放入压缩包
ByteArrayOutputStream os = new ByteArrayOutputStream();
try {
workBook.write(os);
} catch (IOException e) {
e.printStackTrace();
}
byte[] content = os.toByteArray();
//写入输入流
InputStream is = new ByteArrayInputStream(content);
// 用于将数据压缩成Zip文件格式
zos.putNextEntry(new ZipEntry("护工档案信息表.xls"));
int bytesRead = 0;
while ((bytesRead = is.read()) != -1) {
zos.write(bytesRead);
}
zos.flush();
zos.close();
} catch (IOException e) {
e.printStackTrace();
}
}
package com.enation.app.javashop.framework.util;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
/**
* @program: javashop-b2b2c
* @description: 创建表头工具类
* @author: Xu.YanPing
* @create: 2020-08-03 16:30
**/
public class CreateWorkerSheetUtil {
public static HSSFWorkbook createWorkerSheet(String[] headers, String sheetName) {
HSSFWorkbook workBook = new HSSFWorkbook();// 创建excel工作簿
// 设置表头字体样式
HSSFFont headfont = workBook.createFont();
headfont.setFontName("黑体");
headfont.setFontHeightInPoints((short) 22);// 字体大小
headfont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);// 加粗
HSSFCellStyle headstyle = workBook.createCellStyle();
headstyle.setFont(headfont);
headstyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 左右居中
headstyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 上下居中
headstyle.setLocked(true);
headstyle.setWrapText(true);// 自动换行
// 设置列头字体样式
HSSFFont columnHeadFont = workBook.createFont();
columnHeadFont.setFontName("宋体");
columnHeadFont.setFontHeightInPoints((short) 11);
columnHeadFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
// 设置列头的样式
HSSFCellStyle columnHeadStyle = workBook.createCellStyle();
columnHeadStyle.setFont(columnHeadFont);
columnHeadStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 左右居中
columnHeadStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 上下居中
columnHeadStyle.setLocked(true);
columnHeadStyle.setWrapText(true);
HSSFFont font = workBook.createFont();
font.setFontName("Arial");
font.setFontHeightInPoints((short) 11);
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
// 设置普通单元格样式
HSSFCellStyle style = workBook.createCellStyle();
style.setFont(font);
style.setAlignment(HSSFCellStyle.ALIGN_LEFT);// 左右居中
style.setVerticalAlignment(HSSFCellStyle.VERTICAL_TOP);// 上下居中
style.setWrapText(true);
HSSFCellStyle centerstyle = workBook.createCellStyle();
centerstyle.setFont(font);
centerstyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 左右居中
centerstyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 上下居中
centerstyle.setWrapText(true);
HSSFSheet sheet = workBook.createSheet(sheetName);// 创建一个工作表sheet,名为sheetName
// 对第一行做处理,标题的创建
HSSFRow row = sheet.createRow(0);// 创建第一行
row.setHeight((short) 600);// 设置表格行的高度
HSSFCell cell0 = row.createCell(0);
cell0.setCellValue(new HSSFRichTextString(sheetName));// 设置表名
cell0.setCellStyle(headstyle);
//合并第一行单元格
CellRangeAddress range = new CellRangeAddress(0, 0, 0, 16);
sheet.addMergedRegion(range);
//第二行列名
HSSFRow row2 = sheet.createRow(1);
row2.setHeight((short) 500);
HSSFCell cell = null;
// 插入第一行数据标题
for (int i = 0; i < headers.length; i++) {
cell = row2.createCell(i);
cell.setCellValue(headers[i]);
cell.setCellStyle(columnHeadStyle);
sheet.setColumnWidth(i, 5500);
}
return workBook;
}
}
/**
* 获取护工的照片:获取一个护工对应的所有图片的url路径
* @param data
* @return
*/
private Map<String,String[]> getWorkerPics(List<WorkerRecordVo> data) {
Map<String,String[]> map = new HashMap<>();
for (WorkerRecordVo item : data) {
List<String> picUrls = new ArrayList<>();
if (null != item.getIdCardBack())
picUrls.add(item.getIdCardBack());
if (null != item.getIdCardFront())
picUrls.add(item.getIdCardFront());
if (null != item.getPhoto())
picUrls.add(item.getPhoto());
List<AttendantCertDO> attendantCertDOList = item.getAttendantCertDOList();
if (!CollectionUtils.isEmpty(attendantCertDOList)) {
String pictures = attendantCertDOList.stream().filter(vo -> vo.getPictures() != null).map(AttendantCertDO::getPictures).collect(Collectors.joining(","));
if (!StringUtil.isEmpty(pictures))
picUrls.addAll(Arrays.asList(pictures.split(",")));
}
map.put(item.getName()+"("+item.getEmployeeNumber()+")", picUrls.toArray(new String[picUrls.size()]));
}
return map;
}