場景

?Apache POI 是創建和維護操作各種符合Office Open XML（OOXML）標準和微軟的OLE 2復合文檔格式（OLE2）的Java API。用它可以使用Java讀取和創建,修改MS Excel文件.而且,還可以使用Java讀取和創建MS Word和MSPowerPoint文件。Apache POI 提供Java操作Excel解決方案（適用于Excel97-2008）。

在SpringBoot中要實現指定樣式的Excel的導出，即規定了每個Cell要顯示什么內容。

POI實現Excel導出時常用方法說明：

https://blog.csdn.net/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 {}}

效果

?

總結

以上是生活随笔為你收集整理的SpringBoot中使用POI实现自定义Excel布局式导出的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。