Java 页面表单数据导出Excel

Excel导出

1，代码，
Controllerc层

    @GetMapping("/exportExcel")
    @ResponseBody
    public Object bloodKinshipExportExcel(DataBloodQo qo) throws Exception {
//        excelService.bloodKinshipExportExcel(qo);
        String fileName = URLEncoder.encode("血缘关系.xlsx", "UTF-8");
        try {
            byte [] buff = excelService.bloodKinshipExportExcel(qo);
            response.setHeader("Content-Disposition", "attachment;filename="+new String(fileName.getBytes(StandardCharsets.UTF_8), StandardCharsets.ISO_8859_1));
            response.setHeader("Access-Control-Expose-Headers","Content-Disposition");
            response.setContentType("application/ms-excel");
            ResponseUtil.toDownload(buff, fileName);
            return null;
        } catch (Exception e) {
        	e.printStackTrace();
            return ResponseUtil.toWarningMessage("导出失败", e);
        }
    }

Service层

 byte[] bloodKinshipExportExcel(DataBloodQo qo) throws Exception ;

Service实现层

public byte[] bloodKinshipExportExcel(DataBloodQo qo) throws Exception {
        List<DataBloodRelationVo> list = service.list(qo);
//        Map<String,Object> map = new HashMap<String, Object>();
//        map.put("SrcDbName",list.get(0).getSrcDbName());
//        map.put("AccName",list.get(0).getTargetAccName());
//        map.put("TargetDbName",list.get(0).getTargetDbName());
//        map.put("TargetTableName",list.get(0).getTargetTableName());
        String dataSyncType = list.get(0).getDataSyncType();
        Map<String, Object> map1 = null;
        if (StringUtils.isNotEmpty(dataSyncType)){
             map1 = mapper.listETLTreeNanm(dataSyncType);
        }
        InputStream resourceAsStream = this.getClass().getClassLoader().getResourceAsStream("templates/exportxlxs/formwork.xlsx");
        assert resourceAsStream != null;
        XSSFWorkbook book = new XSSFWorkbook(resourceAsStream);
//        System.out.println("xssfWorkbook对象：" + book);
        //读取第一个工作表
        XSSFSheet sheet = book.getSheetAt(0);
//        System.out.println("sheet对象：" + sheet);
        int maxRow = sheet.getLastRowNum();
        int firstRowNum = sheet.getFirstRowNum();
//        System.out.println("s数： "+firstRowNum);
//        System.out.println("总行数为：" + maxRow);
        XSSFRow row1 = sheet.getRow(0);
        XSSFCell cell = row1.getCell(2);
        cell.setCellValue(list.get(0).getTargetDbName());
        XSSFRow row2 = sheet.getRow(0);
        XSSFCell cell1 = row2.getCell(6);
        cell1.setCellValue(list.get(0).getTargetAccName());
        XSSFRow row3 = sheet.getRow(0);
        XSSFCell cell2 = row3.getCell(10);
        cell2.setCellValue(list.get(0).getTargetTableName());
        XSSFRow sheetRow = sheet.getRow(1);
        XSSFCell xssfCell = sheetRow.getCell(3);
        assert map1 != null;
        xssfCell.setCellValue(ObjectUtils.toString(map1.get("SORT_NM")));
        Map<String, Object> objectMap = mapper.listMeaningName(ObjectUtils.toString(list.get(0).getImplType()));
        XSSFRow sheetRow1 = sheet.getRow(1);
        XSSFCell xssfCell1 = sheetRow1.getCell(6);
        xssfCell1.setCellValue(ObjectUtils.toString(objectMap.get("CODE_MEANING")));
        XSSFRow row4 = sheet.getRow(2);
        XSSFCell cell4 = row4.getCell(2);
        cell4.setCellValue(list.get(0).getTableDesc());
        List<ColBloodInfoVo> colBloodInfoList = list.get(0).getColBloodInfoList();
       List<SrcColumnVo> srcColumnVoList = colBloodInfoList.get(0).getSrcColumnVoList();
        for (int i = 0 ;i<colBloodInfoList.size();i++){

            XSSFRow xssfRow = sheet.createRow(5+i);
            copyRow(book, sheet.getRow(4+i), xssfRow, false);

                XSSFCell rowCell = xssfRow.getCell(1);
                rowCell.setCellValue(colBloodInfoList.get(i).getTargetColumnName());
                XSSFRow xssfRow1 = sheet.getRow(5+i);
                XSSFCell rowCell1 = xssfRow1.getCell(4);
                rowCell1.setCellValue(colBloodInfoList.get(i).getColumnDesc());
                XSSFRow xssfRow7 = sheet.getRow(5+i);
                XSSFCell row1Cell1 = xssfRow7.getCell(0);
                row1Cell1.setCellValue(i);
                XSSFRow xssfRow2 = sheet.getRow(5+i);
                XSSFCell row1Cell = xssfRow2.getCell(5);
                row1Cell.setCellValue(colBloodInfoList.get(i).getExpression());
                XSSFRow xssfRow3 = sheet.getRow(5+i);
                XSSFCell row2Cell = xssfRow3.getCell(6);
                row2Cell.setCellValue(StringUtils.strip(list.get(0).getSrcDbName(),","));
                XSSFRow xssfRow4 = sheet.getRow(5+i);
                XSSFCell row3Cell = xssfRow4.getCell(7);
                row3Cell.setCellValue(StringUtils.strip(list.get(0).getSrcAccName(),","));
                XSSFRow xssfRow5 = sheet.getRow(5+i);
                XSSFCell row4Cell = xssfRow5.getCell(8);
                row4Cell.setCellValue(StringUtils.strip(list.get(0).getSrcTableName(),","));
                XSSFRow xssfRow6 = sheet.getRow(5+i);
                XSSFCell row5Cell = xssfRow6.getCell(9);

                row5Cell.setCellValue(colBloodInfoList.get(i).getSrcColumnVoList().get(0).getSrcColumnName());
        }
        // 新建一个输出流把填充过的excel输出到d盘下
        ByteArrayOutputStream out = new ByteArrayOutputStream();
            book.write(out);
            out.close();
            book.close();
//        for (int row = 0; row <= maxRow; row++) {
//            //获取最后单元格num，即总单元格数 ***注意：此处从1开始计数***
//            int maxRol = sheet.getRow(row).getLastCellNum();
//
//            System.out.println("--------第" + row + "行的数据如下--------");
//            for (int rol = 0; rol < maxRol; rol++) {
                XSSFRow row1 = sheet.getRow(row).getCell(0).getRow();
                XSSFCell cell = row1.getCell(1);
                cell.setCellValue("测试机");
//                System.out.print(sheet.getRow(row).getCell(rol) + " ");
//            }
//            System.out.println();
//        }
        return out.toByteArray();
    }

Excel复制行工具

/**
     * 行复制功能
     *
     * @param wb            工作簿
     * @param fromRow       从哪行开始
     * @param toRow         目标行
     * @param copyValueFlag true则连同cell的内容一起复制
     */
    public static void copyRow(Workbook wb, Row fromRow, Row toRow, boolean copyValueFlag) {
        toRow.setHeight(fromRow.getHeight());

        for (Iterator<Cell> cellIt = fromRow.cellIterator(); cellIt.hasNext(); ) {
            Cell tmpCell = cellIt.next();
            Cell newCell = toRow.createCell(tmpCell.getColumnIndex());
            copyCell(wb, tmpCell, newCell, copyValueFlag);
        }

        Sheet worksheet = fromRow.getSheet();

        for (int i = 0; i < worksheet.getNumMergedRegions(); i++) {
            CellRangeAddress cellRangeAddress = worksheet.getMergedRegion(i);
            if (cellRangeAddress.getFirstRow() == fromRow.getRowNum()) {
                CellRangeAddress newCellRangeAddress = new CellRangeAddress(toRow.getRowNum(),
                        (toRow.getRowNum() + (cellRangeAddress.getLastRow() - cellRangeAddress.getFirstRow())),
                        cellRangeAddress.getFirstColumn(), cellRangeAddress.getLastColumn());
                worksheet.addMergedRegionUnsafe(newCellRangeAddress);
            }
        }
    }


    /**
     * 复制单元格
     *
     * @param srcCell
     * @param distCell
     * @param copyValueFlag true则连同cell的内容一起复制
     */
    public static void copyCell(Workbook wb, Cell srcCell, Cell distCell, boolean copyValueFlag) {
        CellStyle newStyle = wb.createCellStyle();
        CellStyle srcStyle = srcCell.getCellStyle();

        newStyle.cloneStyleFrom(srcStyle);
        newStyle.setFont(wb.getFontAt(srcStyle.getFontIndex()));

        // 样式
        distCell.setCellStyle(newStyle);

        // 内容
        if (srcCell.getCellComment() != null) {
            distCell.setCellComment(srcCell.getCellComment());
        }

        // 不同数据类型处理
        CellType srcCellType = srcCell.getCellTypeEnum();
        distCell.setCellType(srcCellType);

        if (copyValueFlag) {
            if (srcCellType == CellType.NUMERIC) {
                if (DateUtil.isCellDateFormatted(srcCell)) {
                    distCell.setCellValue(srcCell.getDateCellValue());
                } else {
                    distCell.setCellValue(srcCell.getNumericCellValue());
                }
            } else if (srcCellType == CellType.STRING) {
                distCell.setCellValue(srcCell.getRichStringCellValue());
            } else if (srcCellType == CellType.BLANK) {

            } else if (srcCellType == CellType.BOOLEAN) {
                distCell.setCellValue(srcCell.getBooleanCellValue());
            } else if (srcCellType == CellType.ERROR) {
                distCell.setCellErrorValue(srcCell.getErrorCellValue());
            } else if (srcCellType == CellType.FORMULA) {
                distCell.setCellFormula(srcCell.getCellFormula());
            }
        }

    }

public static void toDownload(byte[] bytes, String fileName) throws IOException {
        HttpServletResponse resp = ApplicationContextUtil.getServletActionContext().getResponse();
        boolean isIE = isIE();
        if (isIE) {
            fileName = URLEncoder.encode(fileName, "UTF8");
        } else {
            fileName = new String(fileName.getBytes("UTF-8"), "ISO-8859-1");
        }

        resp.addHeader("Content-Disposition", "attachment;filename=" + fileName);
        Cookie cookie = new Cookie(FILEDOWNLOAD, "true");
        cookie.setPath("/");
        resp.addCookie(cookie);
        OutputStream out = resp.getOutputStream();
        out.write(bytes);
        out.close();
    }

  public static ServletRequestAttributes getServletActionContext() {
    return ((ServletRequestAttributes) RequestContextHolder.getRequestAttributes());
  }