Springboot导出excel,合并单元格示例
以下用一个示例来说明springboot如何导出数据到excel。
首先引入Maven依赖:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
<dependency> <groupId>org.apache.poi</groupId> <artifactId>poi</artifactId> <version>3.17</version> </dependency> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi-ooxml</artifactId> <version>3.17</version> </dependency> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi-ooxml-schemas</artifactId> <version>3.17</version> </dependency> |
导出接口方法:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 |
@RequestMapping("/exportExcel") public void exportExcel(HttpServletResponse response, HttpServletRequest request) throws Exception { //接收参数 String ids = request.getParameter("ids"); List<ProjectInfoVo> result = projectInfoService.getProByIds(ids); //查询数据,实际可通过传过来的参数当条件去数据库查询,在此我就用空集合(数据)来替代 List<ProjectInfoVo> list = new ArrayList<>(); //创建poi导出数据对象 SXSSFWorkbook sxssfWorkbook = new SXSSFWorkbook(); //创建sheet页 SXSSFSheet sheet = sxssfWorkbook.createSheet("开复工项目"); CellRangeAddress region1 = new CellRangeAddress(0, 1, (short) 0, (short) 12); //参数1:起始行 参数2:终止行 参数3:起始列 参数4:终止列 sheet.addMergedRegion(region1); SXSSFRow headTitle = sheet.createRow(0); headTitle.createCell(0).setCellValue("重点工程项目计划表"); //创建表头 SXSSFRow headRow = sheet.createRow(4); //设置表头信息 headRow.createCell(0).setCellValue("序号"); headRow.createCell(1).setCellValue("项目分类"); headRow.createCell(2).setCellValue("投资分类"); headRow.createCell(3).setCellValue("牵头单位"); headRow.createCell(4).setCellValue("工程名称"); headRow.createCell(5).setCellValue("建设单位"); headRow.createCell(6).setCellValue("建设地点"); headRow.createCell(7).setCellValue("建设内容"); headRow.createCell(8).setCellValue("总投资"); headRow.createCell(9).setCellValue("手续进展及工程现状"); headRow.createCell(10).setCellValue("是否XXXX项目"); headRow.createCell(11).setCellValue("开工时间"); headRow.createCell(12).setCellValue("完工时间"); String yihui = null; // 遍历上面数据库查到的数据 for (ProjectInfoVo pm : result) { if (pm.getPrjType3() == 0) { yihui = "是"; } else { yihui = "否"; } SXSSFRow dataRow = sheet.createRow(sheet.getLastRowNum() + 1); dataRow.createCell(0).setCellValue(pm.getPrjId()); dataRow.createCell(1).setCellValue(pm.getProjectTypeName()); dataRow.createCell(2).setCellValue(pm.getPrjType4Name()); dataRow.createCell(3).setCellValue(pm.getLeadOrgId()); dataRow.createCell(4).setCellValue(pm.getPrjName()); dataRow.createCell(5).setCellValue(pm.getDevOrgId()); dataRow.createCell(6).setCellValue(pm.getDetailAddr()); dataRow.createCell(7).setCellValue(pm.getPrjIntro()); dataRow.createCell(8).setCellValue(pm.getPlanTotalInvest() + ""); dataRow.createCell(9).setCellValue("XXXXX"); dataRow.createCell(10).setCellValue(yihui); dataRow.createCell(11).setCellValue(pm.getRealStartDate()); dataRow.createCell(12).setCellValue(pm.getRealEndDate()); } // 下载导出 String filename = "XXXXXXX平台工程信息表"; // 设置头信息 response.setCharacterEncoding("UTF-8"); response.setContentType("application/vnd.ms-excel"); //一定要设置成xlsx格式 response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(filename + ".xlsx", "UTF-8")); //创建一个输出流 ServletOutputStream outputStream = response.getOutputStream(); //写入数据 sxssfWorkbook.write(outputStream); // 关闭 outputStream.close(); sxssfWorkbook.close(); } |
//查询数据,实际可通过传过来的参数当条件去数据库查询,在此我就用空集合(数据)来替代
List<ProjectInfoVo> list = new ArrayList<>();
//创建poi导出数据对象
SXSSFWorkbook sxssfWorkbook = new SXSSFWorkbook();
//创建sheet页
SXSSFSheet sheet = sxssfWorkbook.createSheet("开复工项目");
CellRangeAddress region1 = new CellRangeAddress(0, 1, (short) 0, (short) 12);
//参数1:起始行 参数2:终止行 参数3:起始列 参数4:终止列
sheet.addMergedRegion(region1);
SXSSFRow headTitle = sheet.createRow(0);
headTitle.createCell(0).setCellValue("重点工程项目计划表");
//创建表头
SXSSFRow headRow = sheet.createRow(4);
//设置表头信息
headRow.createCell(0).setCellValue("序号");
headRow.createCell(1).setCellValue("项目分类");
headRow.createCell(2).setCellValue("投资分类");
headRow.createCell(3).setCellValue("牵头单位");
headRow.createCell(4).setCellValue("工程名称");
headRow.createCell(5).setCellValue("建设单位");
headRow.createCell(6).setCellValue("建设地点");
headRow.createCell(7).setCellValue("建设内容");
headRow.createCell(8).setCellValue("总投资");
headRow.createCell(9).setCellValue("手续进展及工程现状");
headRow.createCell(10).setCellValue("是否XXXX项目");
headRow.createCell(11).setCellValue("开工时间");
headRow.createCell(12).setCellValue("完工时间");
String yihui = null;
// 遍历上面数据库查到的数据
for (ProjectInfoVo pm : result) {
if (pm.getPrjType3() == 0) {
yihui = "是";
} else {
yihui = "否";
}
SXSSFRow dataRow = sheet.createRow(sheet.getLastRowNum() + 1);
dataRow.createCell(0).setCellValue(pm.getPrjId());
dataRow.createCell(1).setCellValue(pm.getProjectTypeName());
dataRow.createCell(2).setCellValue(pm.getPrjType4Name());
dataRow.createCell(3).setCellValue(pm.getLeadOrgId());
dataRow.createCell(4).setCellValue(pm.getPrjName());
dataRow.createCell(5).setCellValue(pm.getDevOrgId());
dataRow.createCell(6).setCellValue(pm.getDetailAddr());
dataRow.createCell(7).setCellValue(pm.getPrjIntro());
dataRow.createCell(8).setCellValue(pm.getPlanTotalInvest() + "");
dataRow.createCell(9).setCellValue("XXXXX");
dataRow.createCell(10).setCellValue(yihui);
dataRow.createCell(11).setCellValue(pm.getRealStartDate());
dataRow.createCell(12).setCellValue(pm.getRealEndDate());
}
// 下载导出
String filename = "XXXXXXX平台工程信息表";
// 设置头信息
response.setCharacterEncoding("UTF-8");
response.setContentType("application/vnd.ms-excel");
//一定要设置成xlsx格式
response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(filename + ".xlsx", "UTF-8"));
//创建一个输出流
ServletOutputStream outputStream = response.getOutputStream();
//写入数据
sxssfWorkbook.write(outputStream);
// 关闭
outputStream.close();
sxssfWorkbook.close();
}
前台请求接口方法:
1 2 3 |
function bbdc() { window.location.href= 'xxxxxxxx/front/exportExcel'; } |
合并Excel单元格
方法:
1 2 3 |
CellRangeAddress region1 = new CellRangeAddress(rowNumber, rowNumber, (short) 0, (short) 11); //参数1:起始行 参数2:终止行 参数3:起始列 参数4:终止列 sheet.addMergedRegion(region1); |
设置样式
演示文字居中方法
1 2 3 4 5 6 7 |
//合并的单元格样式 HSSFCellStyle boderStyle = wb.createCellStyle(); //垂直居中 boderStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); boderStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 创建一个居中格式 //设置一个边框 boderStyle.setBorderTop(HSSFBorderFormatting.BORDER_THICK); |
更具体、更详细的操作,当然是官方文档啦:http://poi.apache.org/apidocs/dev/org/apache/poi