利用 Java 的 Jakarta POI HSSF API 组件实现多表数据导出为 Excel 文件
程序员文章站
2022-04-30 16:43:04
...
前言
根据业务需要，将多表关联查询的结果导出为 Excel 文件，经过多次尝试，以及多次失败，最终实现了这个业务。
导出文件的效果图
核心后端代码
```java
public ResponseEntity<byte[]> exportVillagesAndTownsExcel(String startDate, String endDate, int exportType, List<Area> list) throws Exception {
HttpHeaders headers = null;
ByteArrayOutputStream baos = null;
try {
//1.创建Excel文档
HSSFWorkbook workbook = new HSSFWorkbook();
CreationHelper createHelper = workbook.getCreationHelper();
//2.创建文档摘要
workbook.createInformationProperties();
//3.获取文档信息，并配置
DocumentSummaryInformation dsi = workbook.getDocumentSummaryInformation();
//3.1文档类别
dsi.setCategory("考勤统计");
//3.2设置文档管理员
// dsi.setManager(UserUtils.getCurrentUser().getName());
//3.3设置组织机构
dsi.setCompany("");
//4.获取摘要信息并配置
SummaryInformation si = workbook.getSummaryInformation();
//4.1设置文档主题
si.setSubject("考勤统计");
//4.2.设置文档标题
si.setTitle("");
//4.3 设置文档作者
si.setAuthor("");
//4.4设置文档备注
si.setComments("");
//创建标题的显示样式
HSSFCellStyle headerStyle = workbook.createCellStyle();
headerStyle.setFillForegroundColor(IndexedColors.WHITE.index);
headerStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
headerStyle.setAlignment(HorizontalAlignment.CENTER);
// 设置标题边框
headerStyle.setBottomBorderColor(HSSFColor.BLACK.index);
headerStyle.setBorderBottom(BorderStyle.THIN);
headerStyle.setBorderLeft(BorderStyle.THIN);
headerStyle.setBorderRight(BorderStyle.THIN);
headerStyle.setBorderTop(BorderStyle.THIN);
// 生成一个标题字体
HSSFFont headerFont = workbook.createFont();
headerFont.setFontHeightInPoints((short) 24);
headerFont.setColor(HSSFColor.BLACK.index);
headerFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
headerFont.setFontName("宋体");
//设置标题字体
headerStyle.setFont(headerFont);
//创建列的显示样式
HSSFCellStyle columnStyle = workbook.createCellStyle();
columnStyle.setFillForegroundColor(IndexedColors.WHITE.index);
columnStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
columnStyle.setAlignment(HorizontalAlignment.CENTER);
columnStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
//设置列的边框
columnStyle.setBottomBorderColor(HSSFColor.BLACK.index);
columnStyle.setBorderBottom(BorderStyle.THIN);
columnStyle.setBorderLeft(BorderStyle.THIN);
columnStyle.setBorderRight(BorderStyle.THIN);
columnStyle.setBorderTop(BorderStyle.THIN);
// 生成一个列字体
HSSFFont columnFont = workbook.createFont();
columnFont.setFontHeightInPoints((short) 12);
columnFont.setColor(HSSFColor.BLACK.index);
columnFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
columnFont.setFontName("宋体");
//设置列的字体
columnStyle.setFont(columnFont);
//设置数据的样式
HSSFCellStyle dataStyle = workbook.createCellStyle();
dataStyle.setFillForegroundColor(IndexedColors.WHITE.index);
dataStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
dataStyle.setAlignment(HorizontalAlignment.CENTER);
dataStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
//数据的边框
dataStyle.setBottomBorderColor(HSSFColor.BLACK.index);
dataStyle.setBorderBottom(BorderStyle.THIN);
dataStyle.setBorderLeft(BorderStyle.THIN);
dataStyle.setBorderRight(BorderStyle.THIN);
dataStyle.setBorderTop(BorderStyle.THIN);
/*链接样式*/
HSSFCellStyle linkStyle = workbook.createCellStyle();
linkStyle.setAlignment(HorizontalAlignment.CENTER);
linkStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
linkStyle.setBottomBorderColor(HSSFColor.BLACK.index);
linkStyle.setBorderBottom(BorderStyle.THIN);
linkStyle.setBorderLeft(BorderStyle.THIN);
linkStyle.setBorderRight(BorderStyle.THIN);
linkStyle.setBorderTop(BorderStyle.THIN);
HSSFFont cellFont= workbook.createFont();
cellFont.setUnderline((byte) 1);
cellFont.setColor(HSSFColor.BLUE.index);
linkStyle.setFont(cellFont);
for(Area area:list){
//创建Excel表单
HSSFSheet sheet = workbook.createSheet(area.getName());
// 合并第一行
CellRangeAddress region1 = new CellRangeAddress(0, 0, (short) 0, (short) 16);
//参数1：起始行 参数2：终止行 参数3：起始列 参数4：终止列
sheet.addMergedRegion(region1);
HSSFRow headerRow1 = sheet.createRow(0);
HSSFCell headerCell = headerRow1.createCell(0);
headerCell.setCellValue("考勤统计表(乡镇村统计汇总)"+DateUtils.getDate());
headerCell.setCellStyle(headerStyle);
//5.设置表头
HSSFRow headerRow = sheet.createRow(1);
HSSFCell cell0 = headerRow.createCell(0);
cell0.setCellValue("乡镇");
cell0.setCellStyle(columnStyle);
HSSFCell cell1 = headerRow.createCell(1);
cell1.setCellValue("村委会");
cell1.setCellStyle(columnStyle);
HSSFCell cell2 = headerRow.createCell(2);
cell2.setCellValue("姓名");
cell2.setCellStyle(columnStyle);
HSSFCell cell3 = headerRow.createCell(3);
cell3.setCellValue("身份证号码");
cell3.setCellStyle(columnStyle);
HSSFCell cell4 = headerRow.createCell(4);
cell4.setCellValue("电话号码");
cell4.setCellStyle(columnStyle);
HSSFCell cell5 = headerRow.createCell(5);
cell5.setCellValue("管护责任区");
cell5.setCellStyle(columnStyle);
HSSFCell cell6 = headerRow.createCell(6);
cell6.setCellValue("月份");
cell6.setCellStyle(columnStyle);
HSSFCell cell7 = headerRow.createCell(7);
cell7.setCellValue("日期");
cell7.setCellStyle(columnStyle);
HSSFCell cell8 = headerRow.createCell(8);
cell8.setCellValue("上报时间");
cell8.setCellStyle(columnStyle);
HSSFCell cell9 = headerRow.createCell(9);
cell9.setCellValue("上报经度");
cell9.setCellStyle(columnStyle);
HSSFCell cell10 = headerRow.createCell(10);
cell10.setCellValue("上报纬度");
cell10.setCellStyle(columnStyle);
HSSFCell cell11 = headerRow.createCell(11);
cell11.setCellValue("上报位置");
cell11.setCellStyle(columnStyle);
HSSFCell cell12 = headerRow.createCell(12);
cell12.setCellValue("上报内容");
cell12.setCellStyle(columnStyle);
HSSFCell cell13 = headerRow.createCell(13);
cell13.setCellValue("上报附件");
cell13.setCellStyle(columnStyle);
HSSFCell cell14 = headerRow.createCell(14);
cell14.setCellValue("批示情况");
cell14.setCellStyle(columnStyle);
HSSFCell cell15 = headerRow.createCell(15);
cell15.setCellValue("处置情况");
cell15.setCellStyle(columnStyle);
HSSFCell cell16 = headerRow.createCell(16);
cell16.setCellValue("其他");
cell16.setCellStyle(columnStyle);
// 根据乡镇获取 上报记录
List<ForesterReportInfo> reportList = foresterReportInfoService.exportList(null, startDate, endDate, area.getAreaCode());
//6.装数据
Integer rI = 2;
for (ForesterReportInfo item:reportList){
EntityWrapper<ForesterReportFile> attachmentWrapper = new EntityWrapper<ForesterReportFile>();
attachmentWrapper.eq("report_info_id", item.getId());
List<ForesterReportFile> attachmentList = foresterReportFileService.selectList(attachmentWrapper);
List<ForesterReportReply> replyList = foresterReportReplyService.getReplyList(item.getId());
HSSFRow row1 = sheet.createRow(rI);
HSSFCell c1 = row1.createCell(0);
c1.setCellValue(area.getName());
c1.setCellStyle(dataStyle);
HSSFCell c2 = row1.createCell(1);
c2.setCellValue(item.getCwh());
c2.setCellStyle(dataStyle);
HSSFCell c3 = row1.createCell(2);
c3.setCellValue(item.getRealname());
c3.setCellStyle(dataStyle);
HSSFCell c4 = row1.createCell(3);
c4.setCellValue(item.getIdcard());
c4.setCellStyle(dataStyle);
HSSFCell c5 = row1.createCell(4);
c5.setCellValue(item.getPhone());
c5.setCellStyle(dataStyle);
String ghq = "";
if(item.getSt() == null){
ghq = item.getTb();
}
if(item.getTb() == null){
ghq = item.getSt();
}
if(item.getSt() != null && item.getTb() != null){
ghq = item.getSt() + "," + item.getTb();
}
HSSFCell c6 = row1.createCell(5);
c6.setCellValue(ghq);
c6.setCellStyle(dataStyle);
SimpleDateFormat formatM = new SimpleDateFormat("MM");
SimpleDateFormat formatD = new SimpleDateFormat("dd");
SimpleDateFormat formatT = new SimpleDateFormat("HH:m:s");
HSSFCell c7 = row1.createCell(6);
c7.setCellValue(formatM.format(item.getReportedTime()));
c7.setCellStyle(dataStyle);
HSSFCell c8 = row1.createCell(7);
c8.setCellValue(formatD.format(item.getReportedTime()));
c8.setCellStyle(dataStyle);
HSSFCell c9 = row1.createCell(8);
c9.setCellValue(formatT.format(item.getReportedTime()));
c9.setCellStyle(dataStyle);
HSSFCell c10 = row1.createCell(9);
c10.setCellValue(item.getLongitude());
c10.setCellStyle(dataStyle);
HSSFCell c11 = row1.createCell(10);
c11.setCellValue(item.getLatitude());
c11.setCellStyle(dataStyle);
HSSFCell c12 = row1.createCell(11);
c12.setCellValue(item.getAddress());
c12.setCellStyle(dataStyle);
HSSFCell c13 = row1.createCell(12);
c13.setCellValue(item.getContent());
c13.setCellStyle(dataStyle);
String replyContent = "";
for(ForesterReportReply tem:replyList){
replyContent += tem.getReplyContent();
replyContent += ";";
replyContent += "\r\n";
}
HSSFCell c15 = row1.createCell(14);
c15.setCellValue(replyContent);
c15.setCellStyle(dataStyle);
String type = "";
if(item.getType() == 0){
type = "日常上报";
}
if(item.getType() == 1){
type = "异常上报";
}
HSSFCell c16 = row1.createCell(15);
c16.setCellValue(type);
c16.setCellStyle(dataStyle);
HSSFCell c17 = row1.createCell(16);
c17.setCellValue("");
c17.setCellStyle(dataStyle);
for(int i = 0; i < attachmentList.size(); i++){
String fileType = "附件";
/*String[] audio = {"acc"};
String[] video = {"mp4"};
String[] image = {"png"};*/
if(attachmentList.get(i).getFileExtension().toLowerCase().equals("aac")){
fileType = "音频附件";
}
if(attachmentList.get(i).getFileExtension().toLowerCase().equals("mp4")){
fileType = "视频附件";
}
if(attachmentList.get(i).getFileExtension().toLowerCase().equals("png")){
fileType = "图片附件";
}
if(sheet.getRow(rI + i) == null){
HSSFRow rows = sheet.createRow(rI + i);
rows.createCell(13).setCellType(HSSFCell.CELL_TYPE_FORMULA);
rows.getCell(13).setCellFormula("HYPERLINK(\"" +"http://" + attachmentList.get(i).getFilePath() + "\",\"" + fileType + (i+1) +"\")");
rows.getCell(13).setCellStyle(linkStyle);
}else {
HSSFRow rows = sheet.getRow(rI + i);
rows.createCell(13).setCellType(HSSFCell.CELL_TYPE_FORMULA);
rows.getCell(13).setCellFormula("HYPERLINK(\"" + "http://" + attachmentList.get(i).getFilePath() + "\",\"" + fileType + (i+1) +"\")");;
rows.getCell(13).setCellStyle(linkStyle);
}
}
if(attachmentList.size() > 1){
for(int i = 0; i < 17; i++){
if(i == 13){
continue;
}else{
sheet.addMergedRegion(new CellRangeAddress(rI, rI + attachmentList.size() - 1, (short) i, (short) i));
for (int m = rI; m <= rI + attachmentList.size() - 1; m ++) {
HSSFRow row = HSSFCellUtil.getRow(m, sheet);
for (int j = i; j <= i; j++) {
HSSFCell cell = HSSFCellUtil.getCell(row, (short)j);
cell.setCellStyle(dataStyle);
}
}
}
}
}
rI = rI + attachmentList.size();
}
for(int i = 0; i < 17; i++){
sheet.autoSizeColumn((short) i);
}
}
headers = new HttpHeaders();
headers.setContentDispositionFormData("attachment",
DateUtils.getDate()+new String("考勤统计.xls".getBytes("UTF-8"), "iso-8859-1"));
headers.setContentType(MediaType.APPLICATION_OCTET_STREAM);
baos = new ByteArrayOutputStream();
workbook.write(baos);
} catch (IOException e) {
e.printStackTrace();
}
return new ResponseEntity<byte[]>(baos.toByteArray(), headers, HttpStatus.CREATED);
}
个人总结
这次的功能开发，触及到了很多的知识盲点，开拓了自己的编程思路，感觉在开发的时侯，有想法，就应该动手敲代码，去测试自己的想法是否正确。
最后，希望通过这篇博文可以为大家提供帮助。祝大家早日成为大佬。
上一篇： ps大师高级恶搞作品,来来来评选出你心中的男神与女神
下一篇： 洗手,泡脚,洗头,拎马桶的爆糗图片