SpringBoot中使用POI实现自定义Excel布局式导出

曾经终败给现在 2023-10-18 17:37 127阅读 0赞

## 场景 ##

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][https_blog.csdn.net_BADAO_LIUMANG_QIZHI_article_details_89499051]

## 实现 ##

打开pom.xml，导入poi依赖。

<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);
                //行数加2
                x+=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 {
            }
        }

## 效果 ##

![Image 1][]![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0JBREFPX0xJVU1BTkdfUUlaSEk_size_16_color_FFFFFF_t_70][]

![Image 1][]

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0JBREFPX0xJVU1BTkdfUUlaSEk_size_16_color_FFFFFF_t_70 1][]

[https_blog.csdn.net_BADAO_LIUMANG_QIZHI_article_details_89499051]: https://blog.csdn.net/BADAO_LIUMANG_QIZHI/article/details/89499051
[Image 1]: 
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0JBREFPX0xJVU1BTkdfUUlaSEk_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/20190424170129489.jpg?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0JBREFPX0xJVU1BTkdfUUlaSEk=,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0JBREFPX0xJVU1BTkdfUUlaSEk_size_16_color_FFFFFF_t_70 1]: https://img-blog.csdnimg.cn/2019042417014480.jpg?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0JBREFPX0xJVU1BTkdfUUlaSEk=,size_16,color_FFFFFF,t_70