场景
Apache POI 是创建和维护操作各种符合Office Open XML(OOXML)标准和微软的OLE 2复合文档格式(OLE2)的Java API。用它可以使用Java读取和创建,修改MS Excel文件.而且,还可以使用Java读取和创建MS Word和MSPowerPoint文件。Apache POI 提供Java操作Excel解决方案(适用于Excel97-)。
在SpringBoot中要实现指定样式的Excel的导出,即规定了每个Cell要显示什么内容。
POI实现Excel导出时常用方法说明:
/BADAO_LIUMANG_QIZHI/article/details/89499051
实现
打开pom.xml,导入poi依赖。
<!--excel导入导出--><dependency><groupId>org.apache.poi</groupId><artifactId>poi</artifactId><version>3.9</version></dependency><dependency><groupId>org.apache.poi</groupId><artifactId>poi-ooxml</artifactId><version>3.9</version></dependency><dependency><groupId>org.apache.poi</groupId><artifactId>poi-ooxml-schemas</artifactId><version>3.9</version></dependency>
html页面代码:
<button id="printBtn" class="btn btn-info " type="button"><i class="fa fa-trash-o"></i> excel打印</button>
js代码:
//打印按钮点击事件$("#printBtn").click(function () {var data = t.rows(['.selected']).data()[0];if(undefined===data){swal({type: 'warning',title: '提示:',text: '请首先选择一行数据!',confirmButtonColor: "#1ab394",})}else{exportExcel(data.id);}});
导出excel具体执行方法。
function exportExcel(id){$.post("/wmsInOrder/isExport.do",{id:id}).done(function (res) {if(res.status){if(res.data= =true){window.location.href="/wmsInOrder/exportExcel.html?id="+id;}else{Swal.fire('请选择已完成的入库单',res.data,'warning')}}else{Swal.fire('导出失败!',res.data,res.msg)}}).fail(function (err) {Swal.fire('异常提示','执行导出操作失败','error')});}
前面一大堆验证逻辑,就是为了:
window.location.href="/wmsInOrder/exportExcel.html?id= "+id;
来发送post请求。
来到后台Controller
@Description("excel导出")@RequestMapping("/exportExcel.html")public void exportExcel(Long id, HttpServletRequest request, HttpServletResponse response) throws Exception {this.inOrderService.exportExcel(id,request,response);}
传递到service层
void exportExcel(Long id, HttpServletRequest request, HttpServletResponse response) throws Exception ;
然后到serviceImpl
public void exportExcel(Long id, HttpServletRequest request, HttpServletResponse response) throws Exception {Sheet tempSheet = null;try {XSSFWorkbook book=new XSSFWorkbook();// 创建单元格样式对象XSSFCellStyle alignStyle = book.createCellStyle();alignStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER);alignStyle.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);alignStyle.setBorderBottom(BorderStyle.THIN);alignStyle.setBottomBorderColor(IndexedColors.BLACK.getIndex());alignStyle.setBorderLeft(BorderStyle.THIN);alignStyle.setLeftBorderColor(IndexedColors.BLACK.getIndex());alignStyle.setBorderRight(BorderStyle.THIN);alignStyle.setRightBorderColor(IndexedColors.BLACK.getIndex());alignStyle.setBorderTop(BorderStyle.THIN);alignStyle.setTopBorderColor(IndexedColors.BLACK.getIndex());XSSFCellStyle alignStyle3 = book.createCellStyle();alignStyle3.setAlignment(XSSFCellStyle.ALIGN_CENTER);alignStyle3.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);alignStyle3.setBorderBottom(BorderStyle.THIN);alignStyle3.setBottomBorderColor(IndexedColors.BLACK.getIndex());alignStyle3.setBorderLeft(BorderStyle.THIN);alignStyle3.setLeftBorderColor(IndexedColors.BLACK.getIndex());alignStyle3.setBorderRight(BorderStyle.THIN);alignStyle3.setRightBorderColor(IndexedColors.BLACK.getIndex());alignStyle3.setBorderTop(BorderStyle.THIN);alignStyle3.setTopBorderColor(IndexedColors.BLACK.getIndex());org.apache.poi.ss.usermodel.Font ztFont3 = book.createFont();ztFont3.setBoldweight(org.apache.poi.ss.usermodel.Font.BOLDWEIGHT_BOLD);alignStyle3.setFont(ztFont3);XSSFCellStyle alignStyle11 = book.createCellStyle();alignStyle11.setAlignment(XSSFCellStyle.ALIGN_LEFT);alignStyle11.setVerticalAlignment(XSSFCellStyle.VERTICAL_TOP);XSSFCellStyle alignStyle22 = book.createCellStyle();alignStyle22.setAlignment(XSSFCellStyle.ALIGN_CENTER);alignStyle22.setVerticalAlignment(XSSFCellStyle.VERTICAL_BOTTOM);XSSFCellStyle alignStyle1 = book.createCellStyle();// 设置单元格内容水平对其方式// XSSFCellStyle.ALIGN_CENTER 居中对齐// XSSFCellStyle.ALIGN_LEFT 左对齐// XSSFCellStyle.ALIGN_RIGHT 右对齐alignStyle1.setAlignment(XSSFCellStyle.ALIGN_LEFT);// 设置单元格内容垂直对其方式// XSSFCellStyle.VERTICAL_TOP 上对齐// XSSFCellStyle.VERTICAL_CENTER 中对齐// XSSFCellStyle.VERTICAL_BOTTOM 下对齐alignStyle1.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);alignStyle1.setBorderBottom(BorderStyle.THIN);alignStyle1.setBottomBorderColor(IndexedColors.BLACK.getIndex());org.apache.poi.ss.usermodel.Font ztFont = book.createFont();alignStyle1.setFont(ztFont);XSSFCellStyle alignStyle2 = book.createCellStyle();alignStyle2.setAlignment(XSSFCellStyle.ALIGN_LEFT);alignStyle2.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);org.apache.poi.ss.usermodel.Font ztFont1 = book.createFont();ztFont1.setBoldweight(org.apache.poi.ss.usermodel.Font.BOLDWEIGHT_BOLD);alignStyle2.setFont(ztFont1);XSSFCellStyle alignStyle5 = book.createCellStyle();alignStyle5.setAlignment(XSSFCellStyle.ALIGN_CENTER);alignStyle5.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);org.apache.poi.ss.usermodel.Font ztFont5 = book.createFont();ztFont5.setBoldweight(org.apache.poi.ss.usermodel.Font.BOLDWEIGHT_BOLD);alignStyle5.setFont(ztFont5);//样式结束,根据具体业务查询数据// x行数 y列数int x=1;int y=0;tempSheet = book.createSheet();book.setSheetName(0, "霸道流氓");//第一行第三列tempSheet.createRow(0).createCell(y+2).setCellValue("第一行第三列");tempSheet.getRow(0).createCell(y+3).setCellValue("");tempSheet.getRow(0).createCell(y+4).setCellValue("");tempSheet.getRow(0).createCell(y+5).setCellValue("");tempSheet.getRow(0).createCell(y+6).setCellValue("");tempSheet.getRow(0).createCell(y+7).setCellValue("");tempSheet.getRow(0).createCell(y+8).setCellValue("");tempSheet.getRow(0).createCell(y+9).setCellValue("");//设置cell样式tempSheet.getRow(0).getCell(y+2).setCellStyle(alignStyle5);tempSheet.getRow(0).getCell(y+3).setCellStyle(alignStyle5);tempSheet.getRow(0).getCell(y+4).setCellStyle(alignStyle5);tempSheet.getRow(0).getCell(y+5).setCellStyle(alignStyle5);tempSheet.getRow(0).getCell(y+6).setCellStyle(alignStyle5);tempSheet.getRow(0).getCell(y+7).setCellStyle(alignStyle5);tempSheet.getRow(0).getCell(y+8).setCellStyle(alignStyle5);tempSheet.getRow(0).getCell(y+9).setCellStyle(alignStyle5);//合并单元格Cell 第1行到第1行 第3列到第10列CellRangeAddress region = new CellRangeAddress(0,0,y+2,y+9);tempSheet.addMergedRegion(region);//创建一行tempSheet.createRow(x).createCell(y).setCellValue("姓名:");tempSheet.getRow(x).createCell(y+1).setCellValue("霸道");tempSheet.getRow(x).createCell(y+2).setCellValue("");tempSheet.getRow(x).createCell(y+3).setCellValue("年龄:");tempSheet.getRow(x).createCell(y+4).setCellValue("23");tempSheet.getRow(x).createCell(y+5).setCellValue("");tempSheet.getRow(x).getCell(y).setCellStyle(alignStyle2);tempSheet.getRow(x).getCell(y+1).setCellStyle(alignStyle1);tempSheet.getRow(x).getCell(y+2).setCellStyle(alignStyle1);tempSheet.getRow(x).getCell(y+3).setCellStyle(alignStyle2);tempSheet.getRow(x).getCell(y+4).setCellStyle(alignStyle1);tempSheet.getRow(x).getCell(y+5).setCellStyle(alignStyle1);region = new CellRangeAddress(x,x,y+1,y+2);tempSheet.addMergedRegion(region);region = new CellRangeAddress(x,x,y+4,y+5);tempSheet.addMergedRegion(region);//行数加2x+=2;//固定表头字段tempSheet.createRow(x).createCell(y+2).setCellValue("字段1");tempSheet.getRow(x).createCell(y+3).setCellValue("字段2");tempSheet.getRow(x).createCell(y+4).setCellValue("字段3");tempSheet.getRow(x).createCell(y+5).setCellValue("字段4");tempSheet.getRow(x).createCell(y+6).setCellValue("字段5");tempSheet.getRow(x).createCell(y+7).setCellValue("字段6");tempSheet.getRow(x).createCell(y+8).setCellValue("字段7");tempSheet.getRow(x).getCell(y+2).setCellStyle(alignStyle3);tempSheet.getRow(x).getCell(y+3).setCellStyle(alignStyle3);tempSheet.getRow(x).getCell(y+4).setCellStyle(alignStyle3);tempSheet.getRow(x).getCell(y+5).setCellStyle(alignStyle3);tempSheet.getRow(x).getCell(y+6).setCellStyle(alignStyle3);tempSheet.getRow(x).getCell(y+7).setCellStyle(alignStyle3);tempSheet.getRow(x).getCell(y+8).setCellStyle(alignStyle3);// 自定义输出文件名String title = "霸道流氓气质";response.reset();response.setHeader("Content-Disposition", "attachment;filename=".concat(String.valueOf(URLEncoder.encode(title + ".xlsx", "UTF-8"))));response.setContentType("application/vnd.ms-excel;charset=UTF-8");OutputStream out = response.getOutputStream();book.write(out);out.flush();out.close();}catch (Exception e) {e.printStackTrace();} finally {}}