前后端分离 poi使用

前端

axios({
        method: "get",
        url: process.env.VUE_APP_BASE_API + "/monthDistribution/exportOrgMonthForm",
        params: params,
        responseType: "blob"
      })
        .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);
            link.setAttribute("download", decodeURI(res.headers['filename']));
            document.body.appendChild(link);
            link.click();
        document.body.removeChild(link);
      
        })

 

后端

 @Override
    public HSSFWorkbook exportOrgMonthForm(CommonParam commonParam) throws IOException {
        // 查询数据
        HlzDistributionDto data = getMonthDistributionTable(commonParam.getSearchMonth(), commonParam.getSearchOrgId(), commonParam.getSearchGroupId());

        HSSFWorkbook wb = new HSSFWorkbook();
        HSSFSheet sheet = wb.createSheet();
        // 设置单元格样式
        CellStyle style = setCellStyle(wb);
        HSSFCellStyle style1 = setCellStyle1(wb);
        HSSFCellStyle style2 = setCellStyle2(wb);

        List<List<String>> rowList = new ArrayList<>();
    
     // 这里插入数据省略

        createRow(sheet, style, style1, style2, rowList);

        return wb;
    }

    private void createRow(HSSFSheet sheet, CellStyle style, CellStyle style1, CellStyle style2, List<List<String>> rowList) {

        if (CollectionUtils.isNotEmpty(rowList)) {

            for (int i = 0; i < rowList.size(); i++) {

                HSSFRow row = sheet.createRow(i);

                List<String> cellList = rowList.get(i);

                if (CollectionUtils.isNotEmpty(cellList)) {

                    for (int j = 0; j < cellList.size(); j++) {

                        HSSFCell cell = row.createCell(j);
                        cell.setCellValue(cellList.get(j));

                        // 设置列宽
                        if (i == 0) {
                            if (j == 1) {
                                sheet.setColumnWidth(j, 50 * 256);
                            } else {
                                sheet.setColumnWidth(j, 25 * 256);
                            }
                        }

                        if ((i == 4 || i == 11 || i == 13 || i == 15 || i == 18 || i == 22 || i == 23 || i == 31 || i == 34 || i == 36 || i == 39 || i == 41 || i == 45 || i == 50 || i == 53 || i == 56) && j > 1) {
                            style.setFillForegroundColor(IndexedColors.CORNFLOWER_BLUE.getIndex());
                            cell.setCellStyle(style1);
                        } else if (i == 0 || j == 0 || j == 1) {
                            cell.setCellStyle(style2);
                        } else {
                            cell.setCellStyle(style);
                        }
                    }
                }

                row.setHeight((short) (12 * 60));
            }


            sheet.addMergedRegion(new CellRangeAddress(2, 18, 0, 0));

            sheet.addMergedRegion(new CellRangeAddress(20, 23, 0, 0));

            sheet.addMergedRegion(new CellRangeAddress(25, 36, 0, 0));

            sheet.addMergedRegion(new CellRangeAddress(38, 42, 0, 0));

            sheet.addMergedRegion(new CellRangeAddress(44, 56, 0, 0));

            sheet.addMergedRegion(new CellRangeAddress(58, 61, 0, 0));
        }
    }

    private List<String> addRowData(String cell1, String cell2, List<String> data) {
        List<String> row = new ArrayList<>();
        row.add(cell1);
        row.add(cell2);
        row.addAll(data);
        return row;
    }

    private HSSFCellStyle setCellStyle(HSSFWorkbook wb) {
        HSSFCellStyle style = wb.createCellStyle();
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);//设置内容水平居中
        style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);//设置内容垂直居中
        style.setBorderBottom(HSSFCellStyle.BORDER_DOTTED);//下边框
        style.setBorderLeft(HSSFCellStyle.BORDER_DOTTED);//左边框
        style.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框
        style.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框

     // 设置边框颜色
     style.setBottomBorderColor(HSSFColor.WHITE.index);
     style.setTopBorderColor(HSSFColor.WHITE.index);
     style.setLeftBorderColor(HSSFColor.WHITE.index);
     style.setRightBorderColor(HSSFColor.WHITE.index);

return style; } private HSSFCellStyle setCellStyle1(HSSFWorkbook wb) { HSSFCellStyle style = setCellStyle2(wb); // style.setFillForegroundColor(IndexedColors.CORNFLOWER_BLUE.getIndex());// 设置背景色

    // 设置字体颜色
    HSSFFont font=wb.createFont();
    font.setColor(HSSFColor.WHITE.index);
    style.setFont(font);

    // 设置背景颜色
    HSSFPalette palette = wb.getCustomPalette();
    palette.setColorAtIndex(HSSFColor.LIME.index, (byte) 60, (byte) 141, (byte) 188);// 设置背景色
    style.setFillForegroundColor(palette.getColor(HSSFColor.LIME.index).getIndex());
        return style;
    }

    private HSSFCellStyle setCellStyle2(HSSFWorkbook wb) {
        HSSFCellStyle style = setCellStyle(wb);
        style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); //设置加粗

        HSSFFont font = wb.createFont();
        font.setFontName("黑体");
        font.setFontHeightInPoints((short) 12);//设置字体大小
        style.setFont(font);
        return style;
    }

 

posted @ 2020-11-17 09:33  渣男梦想  阅读(357)  评论(0编辑  收藏  举报