Poiexcel导出

1.无模板导出

exportGzp() {
      this.$confirm(
        '可能占用较长一段时间，确定要导出吗?',
        {},
        {
          confirmButtonText: '确定',
          cancelButtonText: '取消',
          type: 'success',
          center: true
        }
      )
        .then(() => {
          exportExcel('/a/b', {
            params: JSON.stringify(
              this.query
            )
          })
            .then(res => {
              const link = document.createElement('a');
              let blob = new Blob([res.data], {
                type: 'application/vnd.ms-excel'
              });
              link.style.display = 'none';
              link.href = URL.createObjectURL(blob);
              // eslint-disable-next-line no-unused-vars
              let num = '';
              for (let i = 0; i < 10; i++) {
                num += Math.ceil(Math.random() * 10);
              }
              link.setAttribute('download', '信息' + '.xls');
              document.body.appendChild(link);
              link.click();
              document.body.removeChild(link);
            })
            .catch(error => {
              console.log(error);
            });
        })
        .catch(() => {});
    },

    @GetMapping(value ="/a")
    @ApiOperation(value ="导出")
    @ApiImplicitParams({
            @ApiImplicitParam(name = "params",value = "",dataType = "String"),
    })
    public void a(String params,HttpServletResponse response){
        service.a(params,response);
    }

     OutputStream out = null;
        try { 
　　　　　　　HSSFWorkbook workbook = new HSSFWorkbook();//创建Excel文件(Workbook)
            HSSFSheet sheet = workbook.createSheet();//创建工作表(Sheet)
            sheet.setDefaultColumnWidth(30);//设置工作表的默认行宽

            String[] exportTitleArray = {"序号", "类型.", "编号", "运号", "内容",
                    "人", "状态", "计划", "结束"};//得到标题行

            HSSFCellStyle titleStype = titleStype(workbook);
            //创建标题行
            HSSFRow row = sheet.createRow(0);
            for (int i = 0; i < exportTitleArray.length; i++) {
                HSSFCell cell = row.createCell(i);
                cell.setCellValue(exportTitleArray[i]);
                cell.setCellStyle(titleStype);
            }

            //创建值行
            HSSFCellStyle listStype = ListStype(workbook);
            for (int i = 0; i < entityList.size(); i++) {
                HSSFRow listRow = sheet.createRow(i + 1);
                Map<String, Object> m = entityList.get(i);

                //序号
                HSSFCell cell = listRow.createCell(0);
                cell.setCellStyle(listStype);
                cell.setCellValue(i + 1);

                //类型
                HSSFCell cell1 = listRow.createCell(1);
                cell1.setCellStyle(listStype);
                if (m.get("") != null) {
                    cell1.setCellValue(m.get("").toString());
                }
                //编号
                HSSFCell cell2 = listRow.createCell(2);
                cell2.setCellStyle(listStype);
                if (m.get("") != null) {
                    cell2.setCellValue(m.get("").toString());
                }
                //运
                HSSFCell cell3 = listRow.createCell(3);
                cell3.setCellStyle(listStype);
                if (m.get("") != null) {
                    cell3.setCellValue(m.get("").toString());
                }
                //工
                HSSFCell cell4 = listRow.createCell(4);
                cell4.setCellStyle(listStype);
                if (m.get("") != null) {
                    cell4.setCellValue(m.get("").toString());
                }
                //工
                HSSFCell cell5 = listRow.createCell(5);
                cell5.setCellStyle(listStype);
                if (m.get("") != null) {
                    cell5.setCellValue(m.get("").toString());
                }
                //状态
                HSSFCell cell6 = listRow.createCell(6);
                cell6.setCellStyle(listStype);
                if (m.get("") != null) {
                    cell6.setCellValue(m.get("").toString());
                }
                //开始
                HSSFCell cell10 = listRow.createCell(7);
                cell10.setCellStyle(listStype);
                if (m.get("") != null) {
                    cell10.setCellValue(m.get("").toString());
                }
                //结束
                HSSFCell cell11 = listRow.createCell(8);
                cell11.setCellStyle(listStype);
                if (m.get("") != null) {
                    cell11.setCellValue(m.get("").toString());
                }
            }

            response.setContentType("application/ms-excel;charset=UTF-8");
            response.setHeader("Content-Disposition", "attachment;filename=".concat(String.valueOf(URLEncoder.encode("信息.xls", "UTF-8"))));//设置选择导出地址的

            out = response.getOutputStream();
            workbook.write(out);// 将数据写出去
            out.flush();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if (out != null) {
                    out.close();
                }
            } catch (IOException e) {
                e.printStackTrace();
            }
        }

 //导出标题样式
    private HSSFCellStyle titleStype(HSSFWorkbook workbook) {
        HSSFCellStyle cellStyle = workbook.createCellStyle();

        cellStyle.setBorderBottom(BorderStyle.THIN); //下边框
        cellStyle.setBorderLeft(BorderStyle.THIN); //左边框
        cellStyle.setBorderRight(BorderStyle.THIN); //右边框
        cellStyle.setBorderTop(BorderStyle.THIN); //上边框

        //水平居中
        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        // 设置单元格内容垂直居中
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);

        // 创建字体
        HSSFFont font = workbook.createFont();
        //设置为粗体
        font.setBold(true);
        cellStyle.setFont(font);
        //设置自动换行
        cellStyle.setWrapText(true);

        //设置背景颜色
        cellStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
        //solid 填充 foreground 前景色
        cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);

        return cellStyle;
    }

    //导出list样式
    private HSSFCellStyle ListStype(HSSFWorkbook workbook) {
        HSSFCellStyle cellStyle = workbook.createCellStyle();
        //水平居中
        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        // 设置单元格内容垂直居中
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        //设置自动换行
        cellStyle.setWrapText(false);

        cellStyle.setBorderBottom(BorderStyle.THIN); //下边框
        cellStyle.setBorderLeft(BorderStyle.THIN); //左边框
        cellStyle.setBorderRight(BorderStyle.THIN); //右边框
        cellStyle.setBorderTop(BorderStyle.THIN); //上边框

        return cellStyle;
    }

2.模板导出

private void ex(List<TSbtzFjzz> list, HttpServletResponse response){
        Workbook wbs =null;
        OutputStream out =null;
        FileInputStream inputStream =null;
        try {
            // 将文档模板转化流
            inputStream = new FileInputStream(templateFilePath+"/sb.xlsx");

            //创建工作簿
            wbs = new XSSFWorkbook(inputStream);

            // 使用的是模板，所以已经创建了表格的，获取就行。
            Sheet sheet = wbs.getSheetAt(0);

            CellStyle style = ListStype(wbs);
for (TSbtzFjzz jbxx:list) {

                Row row = sheet.createRow(rowNumber);
                row.createCell(0).setCellValue(jbxx());
                row.createCell(1).setCellValue(jbxx());
            }

            response.setContentType("application/ms-excel;charset=UTF-8");

            response.setHeader("Content-Disposition", "attachment;filename=".concat(String.valueOf(URLEncoder.encode("设备.xlsx", "UTF-8"))));

            out=response.getOutputStream();

            wbs.write(out);// 将数据写出去

            out.flush();

        } catch (IOException e) {
            e.printStackTrace();
        }finally {
            try {
                if(inputStream!=null){
                    inputStream.close(); // 关闭流
                }
                if(wbs!=null){
                    wbs.close(); // 关闭流
                }
                if(out!=null){
                    out.close(); // 关闭流
                }
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }