文件下载(导出excel文件)
(学习笔记仅供参考)
方式一:
controller层:
@GetMapping("/download")
@ApiOperation(value = "下载")
public void download(HttpServletResponse response){
xiaZaiService.loading(response);
}
service层:
@Override
public void loading(HttpServletResponse response) {
//查找企业数据
list<EntApplyInfoListVo> list = xiaZaiMapper.selectEntApplyListByPage();
if(list.size <= 0){
return;
}
try(OutputStream out = response.getOutputStream()){
//创建excel工作表对象
XFFSheet sheet = ExcelUtils.create();
//导出Excel文件名
String fileName = "企业申办明细表".concat("(")
.concat("yyy-MM-dd")
.concat(")")
.concat(".xlsx");
//创建表头
List<CoumnDTO> datelist = this.createExcelHeader(list);
ExcrlUtils.createTitle(sheet,list);
//创建内容按照参数合并
ExcrlUtils.createContent(sheet,list);
response.setContentType("application/octet-stream");
response.setHeader("Content-Disposition","attachment;filename="+ java.net.URLEncoder.encode(fileName,"UTF-8"));
sheet.getWorkbook().write(out);
out.flush();
}catch (IOException ex){
log.error("构建文件失败",ex);
}
}
/**
* 创建表头
*/
private List<ColumDTO> createExcelHeader(list<EntApplyInfoListVo> list){
List<ColumnDTO> dateList = new ArrayList<>(7);
String titles = "办事编码,企业名称,服务主题,服务事项,实施主体,服务状态";
for (String title : titles.split(",")) {
ColnmnDTO column = new ColumnDTO;
column.setTitle(title);
column.setValueList(new ArrayList<>());
dateList.add(column);
}
//设置企业名称合并单元格
dateList.get(0).setMerge(true);
list.forEach(e->{
dateList.get(0).getValueList().add(Optional.ofNullable(e.getProcessCode()).orElse(""));//Optional.ofNullable
dateList.get(0).getValueList().add(e.getEntName());
dateList.get(0).getValueList().add(e.getItemName());
dateList.get(0).getValueList().add(e.getIndName());
dateList.get(0).getValueList().add(e.getOrgName());
dateList.get(0).getValueList().add(e.getWarnState());
});
return dateList;
}
实体类:
@Date
public class ColimnDTO {
@ApiModelProperty(value = "表格标题")
private String title;
@ApiModelProperty(value = "是否加粗",example = "true")
private boolean bold = true;
@ApiModelProperty(value = "字体颜色",example = "#fff")
private String color;
@ApiModelProperty(value = "合并单元格",example = "false")
private boolean merge;
@ApiModelProperty(value = "列值")
List<String> valueList;
}
方式二(没有封装方法):
@ApiOperation(value = "导出合同", notes = "导出选中的合同列表")
@PostMapping(value = "export")
void exportContract(HttpServletResponse response, @Validated @RequestBody ContractExportReqDTO reqDTO) {
final List<ContractRspDTO> dtoList = contractService.queryByContractUuids(reqDTO.getContractUuids());
try (final Workbook workbook = WorkbookFactory.create(false); OutputStream os = response.getOutputStream()) {
//第一张表
final Sheet sheet0 = workbook.createSheet("服务信息表");
final Row row0 = sheet0.createRow(0);
final Font font = workbook.createFont();
final CellStyle style = workbook.createCellStyle();
font.setFontHeightInPoints((short) 12);
style.setFont(font);
CellUtil.createCell(row0, 0, "流水号", style);
CellUtil.createCell(row0, 1, "预约用户", style);
CellUtil.createCell(row0, 2, "预约服务编号", style);
CellUtil.createCell(row0, 3, "合同编号", style);
CellUtil.createCell(row0, 4, "合同金额", style);
CellUtil.createCell(row0, 5, "实际服务编号", style);
final Font requireFont = workbook.createFont();
requireFont.setColor(Font.COLOR_RED);
requireFont.setFontHeightInPoints((short) 12);
CellUtil.createCell(row0, 6, "*实际服务时间段/服务人员");
RichTextString richTextString1 = new HSSFRichTextString("*实际服务时间段/服务人员");
richTextString1.applyFont(0, 1, requireFont);
richTextString1.applyFont(1, 12, font);
final Cell cell6 = row0.createCell(6);
cell6.setCellValue(richTextString1);
RichTextString richTextString2 = new HSSFRichTextString("*仪器清单与实际机时(小时)");
richTextString2.applyFont(0, 1, requireFont);
richTextString2.applyFont(1, 14, font);
final Cell cell7 = row0.createCell(7);
cell7.setCellValue(richTextString2);
final Row row1 = sheet0.createRow(1);
CellUtil.createCell(row1, 0, "填写说明:", style);
CellUtil.createCell(row1, 3, "最大支持50位", style);
CellUtil.createCell(row1, 4, "最大输入10位,单位元", style);
CellUtil.createCell(row1, 7, "不可增删改仪器编号,机时支持2位小数,未使用到的仪器填0", style);
ContractRspDTO dto;
List<ContractRspDTO> collect =
dtoList.stream().sorted(Comparator.comparing(ContractRspDTO::getSerialNumber).reversed())
.collect(Collectors.toList());
for (int i = 0, len = collect.size(); i < len; i++) {
StringBuilder realTimeSb = new StringBuilder();
StringBuilder instructAndTimeSb = new StringBuilder();
StringBuilder servicePerson = new StringBuilder();
dto = collect.get(i);
final Row row = sheet0.createRow(i + 2);
CellUtil.createCell(row, 0, dto.getSerialNumber(), style);
CellUtil.createCell(row, 1, dto.getAppointPerson(), style);
CellUtil.createCell(row, 2, dto.getAppointIdentifier(), style);
CellUtil.createCell(row, 3, dto.getIdentifier(), style);
CellUtil.createCell(row, 4, dto.getContractAmount(), style);
CellUtil.createCell(row, 5, dto.getServiceName(), style);
// 实际服务时间
dto.getRealTimeList().forEach(tmp -> {
realTimeSb.append(Objects.nonNull(tmp.getStartTime()) ?
LocalDateTime.ofInstant(tmp.getStartTime().toInstant(), ZoneId.systemDefault())
.format(DateTimeFormatter.ofPattern(DateTimeConstants.DATETIME_PATTEN)) : "").append("~")
.append(Objects.nonNull(tmp.getEndTime()) ?
LocalDateTime.ofInstant(tmp.getEndTime().toInstant(), ZoneId.systemDefault())
.format(DateTimeFormatter.ofPattern(DateTimeConstants.DATETIME_PATTEN)) : "")
.append("/").append(Objects.nonNull(tmp.getPersonInfo()) ? tmp.getPersonInfo() : "")
.append("\n");
});
CellUtil.createCell(row, 6,
realTimeSb.length() > 0 ? realTimeSb.substring(0, realTimeSb.length() - 1) : realTimeSb.toString(), style);
// 仪器清单与实际机时
dto.getInstructmentAndTimeList().forEach(tmp -> {
instructAndTimeSb.append(Objects.nonNull(tmp.getIdentifier()) ? tmp.getIdentifier() : "")
.append("/").append(Objects.nonNull(tmp.getMeasurementUnit()) ? tmp.getMeasurementUnit() : "").
append("/").append(Objects.nonNull(tmp.getRealTime()) ? tmp.getRealTime() : "")
.append("\n");
});
CellUtil.createCell(row, 7,
instructAndTimeSb.length() > 0 ? instructAndTimeSb.substring(0, instructAndTimeSb.length() - 1) :
instructAndTimeSb.toString(), style);
}
//第一张表
final Sheet sheet1 = workbook.createSheet("服务和仪器编号");
final Row row2 = sheet1.createRow(0);
final Font font2 = workbook.createFont();
final CellStyle style2 = workbook.createCellStyle();
font2.setFontHeightInPoints((short) 12);
style.setFont(font2);
CellUtil.createCell(row2, 0, "服务名称", style2);
CellUtil.createCell(row2, 1, "服务编号", style2);
CellUtil.createCell(row2, 2, "服务关联仪器", style2);
CellUtil.createCell(row2, 3, "仪器编号", style2);
List<ContractServiceDTO> contractServiceDTOList=new ArrayList<>();
for (ContractRspDTO contractRspDTO : dtoList) {
List<ContractServiceDTO> contractServiceDTOList1 = contractRspDTO.getContractServiceDTOList();
for (ContractServiceDTO contractServiceDTO : contractServiceDTOList1) {
ContractServiceDTO contractServiceDTO1=new ContractServiceDTO();
contractServiceDTO1.setServiceName(contractServiceDTO.getServiceName());
contractServiceDTO1.setServiceIdentifier(contractServiceDTO.getServiceIdentifier());
contractServiceDTO1.setConstractIntrucmentDTOS(contractServiceDTO.getConstractIntrucmentDTOS());
contractServiceDTOList.add(contractServiceDTO1);
}
}
ContractServiceDTO dto1;
for (int i = 0, len = contractServiceDTOList.size(); i < len; i++) {
StringBuilder realTimeSb = new StringBuilder();
StringBuilder instructAndTimeSb = new StringBuilder();
StringBuilder servicePerson = new StringBuilder();
dto1 = contractServiceDTOList.get(i);
final Row row3 = sheet1.createRow(i + 2);
CellUtil.createCell(row3, 0, dto1.getServiceName(), style);
CellUtil.createCell(row3, 1, dto1.getServiceIdentifier(), style);
for (constractIntrucmentDTO constractIntrucmentDTO : dto1.getConstractIntrucmentDTOS()) {
CellUtil.createCell(row3, 2, constractIntrucmentDTO.getName(), style);
CellUtil.createCell(row3, 3, constractIntrucmentDTO.getIdentifier(), style);
}
}
String fileTempName = URLEncoder.encode("服务信息数据模板.xls", "UTF-8");
//处理空格
fileTempName = fileTempName.replaceAll("\\+", "%20");
response.setContentType("application/x-www-form-urlencoded;charset=UTF-8");
response.setHeader("Content-Disposition", "attachment;filename=" + fileTempName);
workbook.write(os);
os.flush();
} catch (IOException e) {
// ignore
}
}
方式三:数据导出excel,可自定义行宽高,字体大小,颜色等,简单易懂。
@Override
public void logExport(HttpServletResponse response) throws IOException {
List<ChangeLogVO> changeLogList = rotatePlanConfigDetailHistoryMapper.selectLists();
// Excel表开始
String sheetname = "变更日志记录";
//创建一个HSSFWorkbook,对应一个Excel文件
HSSFWorkbook wb = new HSSFWorkbook();
//添加一个sheet,对应Excel文件中的sheet
HSSFSheet sheet = wb.createSheet(sheetname);
//设置宽度(我这里只有8列)
int[] width = {30, 30, 30, 30, 30, 30, 30,70};
for (int columnIndex = 0; columnIndex < 8; columnIndex++) {
sheet.setColumnWidth(columnIndex, width[columnIndex] * 256);
}
//设置样式集合
Map<String, HSSFCellStyle> styles = addStyle(wb);
int rowIndex;
int colIndex;
HSSFRow row0 = sheet.createRow(0);
for (colIndex = 0; colIndex < 9; colIndex++) {
row0.createCell(colIndex);
row0.getCell(colIndex).setCellStyle(styles.get("header_center"));
}
for (int i = 0; i < logHeade.size(); i++) {
row0.getCell(i).setCellValue(logHeade.get(i));
row0.getCell(i).setCellStyle(styles.get("vicetitle"));
}
//塞入数据
for (int i = 0; i < changeLogList.size(); i++) {
HSSFRow rowi = sheet.createRow(i + 1);
for (int j = 0; j < logHeade.size(); j++) {
rowi.createCell(j);
rowi.getCell(j).setCellStyle(styles.get("header_center"));
}
rowi.getCell(0).setCellValue(changeLogList.get(i).getClmc());
rowi.getCell(1).setCellValue(changeLogList.get(i).getClms());
rowi.getCell(2).setCellValue(changeLogList.get(i).getQymc());
rowi.getCell(3).setCellValue(changeLogList.get(i).getKdmc());
rowi.getCell(4).setCellValue(changeLogList.get(i).getBgyy());
rowi.getCell(5).setCellValue(changeLogList.get(i).getBgr());
rowi.getCell(6).setCellValue(DateUtil.format(changeLogList.get(i).getBgsj(),"yyyy-MM-dd HH:mm:ss"));
rowi.getCell(7).setCellValue(changeLogList.get(i).getBgnr());
}
//生成文件
String fileName = "变更日志记录.xlsx";
response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=utf-8");
response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8"));
OutputStream outputStream = response.getOutputStream();
wb.write(outputStream);
outputStream.flush();
outputStream.close();
}
private Map<String, HSSFCellStyle> addStyle(HSSFWorkbook wb) {
@SuppressWarnings({"unchecked", "rawtypes"})
Map<String, HSSFCellStyle> styles = new HashMap();
//设置字体
HSSFFont headFont = wb.createFont();
// headFont.setFontName("微软雅黑");
headFont.setFontHeightInPoints((short) 16);
headFont.setBold(true);
HSSFFont bodyFont = wb.createFont();
// bodyFont.setFontName("微软雅黑");
bodyFont.setFontHeightInPoints((short) 11);
HSSFFont headFont1 = wb.createFont();
// headFont1.setFontName("微软雅黑");
headFont1.setFontHeightInPoints((short) 16);
headFont1.setBold(true);
//标题行样式
HSSFCellStyle style = wb.createCellStyle();
style.setFont(headFont);
style.setWrapText(true);
style.setFillForegroundColor((short) 27);
// style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
// style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
styles.put("title", style);
//副标题行样式
HSSFCellStyle vicestyle = wb.createCellStyle();
vicestyle.setFont(headFont1);
vicestyle.setWrapText(true);
vicestyle.setFillForegroundColor((short) 27);
//居中对齐
vicestyle.setAlignment(HorizontalAlignment.CENTER);
//垂直居中
// style.setVerticalAlignment(VERTICAL_CENTER);
styles.put("vicetitle", vicestyle);
//数据头居中样式
style = wb.createCellStyle();
style.setFont(bodyFont);
style.setWrapText(true);
style.setFillForegroundColor((short) 27);
style.setAlignment(HorizontalAlignment.CENTER);
styles.put("header_center", style);
//数据行居中样式
style = wb.createCellStyle();
style.setFont(bodyFont);
styles.put("data_center", style);
//数据行居中底色样式
style = wb.createCellStyle();
style.setFont(bodyFont);
style.setWrapText(true);
style.setFillForegroundColor((short) 27);
styles.put("data_center_color", style);
//数据行居中底色样式2
style = wb.createCellStyle();
style.setFont(bodyFont);
style.setWrapText(true);
style.setFillForegroundColor((short) 27);
styles.put("data_center_color1", style);
//数据行居左样式
style = wb.createCellStyle();
style.setFont(bodyFont);
style.setWrapText(true);
style.setFillForegroundColor((short) 27);
// style.setAlignment(HSSFCellStyle.ALIGN_LEFT);
// style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
styles.put("data_left", style);
//数据行居右样式
style = wb.createCellStyle();
style.setFont(bodyFont);
// style.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
// style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
styles.put("data_right", style);
//无边框样式
style = wb.createCellStyle();
style.setFont(bodyFont);
style.setWrapText(true);
styles.put("data_noborder", style);
//无底边框样式
style = wb.createCellStyle();
style.setFont(bodyFont);
style.setWrapText(true);
styles.put("data_bottom", style);
return styles;
}