Java将一张excel数据填充到另一张excel表

需求：根据数据库中excel保存地址url，获取到Excel表格，将其中数据填充到一张新生成的excel表格中

1.设置读取excel和要填充excel的起始行列

public void setExcelData(String fileUrl, String downloadPath, String reportFilePath, String endTime) {
        // 读取excel开始行 14  结束行 322  
        int readStartRow = 14;
        int readEndRow = 322;
		// 读取excel开始列 9 结束列 12
        int readStartCell = 9;
        int readEndCell = 12;
		// 写入excel开始行 1， 开始列 2
        int writeStartRow = 1;
        int writeStartCell = 2;
		// 读取excel的第1个sheet，写入excel的第2个sheet
        int sheetIndex = 0;
        int outSheetIndex=2;
        
        //获取读取的excel，以及要填充的excel
        File file = HttpUtil.downloadFileFromUrl(cmmFileUrl, downloadPath);
        File reportFile = FileUtil.file(reportFilePath);
        InputStream inputStream = null;
        InputStream reportInputStream = null;

		try {
                inputStream = FileUtil.getInputStream(file);
                reportInputStream = FileUtil.getInputStream(reportFile);
                setData(inputStream, reportInputStream, reportFilePath, sheetIndex, readStartRow, readEndRow, readStartCell, readEndCell, writeStartRow, writeStartCell, outSheetIndex, endTime);
            } catch (Exception e) {
                log.error("写入excel数据失败", e);
            } finally {
                try {
                    assert inputStream != null;
                    inputStream.close();
                } catch (IOException e) {
                    log.error("关闭excel文件流失败");
                }
                try {
                    assert reportInputStream != null;
                    reportInputStream.close();
                } catch (IOException e) {
                    log.error("关闭excel文件流失败");
                }
                FileUtil.del(file);
            }
            
    }

2.读取表格的文件流，进行数据填充

private static void setData(InputStream inputStream, InputStream reportInputStream, String reportFilePath, int sheetIndex, int readStartRow, int readEndRow, int readStartCell, int readEndCell,
                                int writeStartRow, int writeStartCell, int outSheetIndex, String endTime) {
        Workbook wb = null;
        Workbook reportWb = null;
        OutputStream os = null;
        try {
           // 1、获取要读取的文件工作簿对象
           ZipSecureFile.setMinInflateRatio(-1.0d);
           wb= WorkbookFactory.create(inputStream);
           reportWb= WorkbookFactory.create(reportInputStream);

            // 2、获取工作表
            Sheet s = wb.getSheetAt(sheetIndex);
            Sheet rs = reportWb.getSheetAt(outSheetIndex);
            setValue(s, rs, readStartRow, readEndRow, readStartCell, readEndCell, writeStartRow, writeStartCell);

            // 2.1、工作表填入测量完成时间
            Sheet firstSheet = reportWb.getSheetAt(0);

            // 生成一个样式
            CellStyle style = reportWb.createCellStyle();
            // 设置字体
            Font font = reportWb.createFont();
            font.setFontHeightInPoints((short) 9);
            // 把字体应用到当前的样式
            style.setFont(font);

            //完成时间, 开始行 8, 开始列 112
            Row row2 = firstSheet.getRow(8);
            Cell cell2 = row2.createCell(112);
            if(StrUtil.isNotEmpty(endTime)){
                cell2.setCellValue(endTime);
                cell2.setCellStyle(style);
            }

            // 输出时通过流的形式对外输出，包装对应的目标文件
            os = Files.newOutputStream(Paths.get(reportFilePath));
            // 将内存中的workbook数据写入到流中
            reportWb.write(os);

        } catch (Exception e) {
            throw new RuntimeException(e);
        } finally {
            try {
                assert reportWb != null;
                reportWb.close();
            } catch (Exception e) {
                log.error("关闭ReportWb失败");
            }
            try {
                assert os != null;
                os.close();
            } catch (Exception e) {
                log.error("关闭输出流失败");
            }
            try {
                wb.close();
            } catch (Exception e) {
                log.error("关闭Wb失败");
            }
        }
    }

3.将读取excel数据填充到当前excel中

private static void setValue(Sheet s, Sheet rs, int readStartRow, int readEndRow, int readStartCell, int readEndCell, int writeStartRow, int writeStartCell) {
        for (int i = readStartRow; i < readEndRow; i++) {
            int startCell = writeStartCell;
            for (int j = readStartCell; j < readEndCell; j++) {
                // 3、获取行  开始行 14  结束行 284  开始列 9 结束列
                Row row = s.getRow(i);
                // 4、获取列
                Cell cell = row.getCell(j);
                // 5、根据数据的类型获取数据
                Double data = null;
                String data1 = null;
                try {
                    data = cell.getNumericCellValue();
                }catch (IllegalStateException e) {
                    data1 = cell.getStringCellValue();
                }catch (NullPointerException e) {
                    break;
                }

                // 报告开始行 1, 开始列 2
                Row rRow = rs.getRow(writeStartRow);
                if (rRow == null) {
                    rRow = rs.createRow(writeStartRow);
                }
                Cell rCell = rRow.createCell(startCell);
                // 5、在列中写数据
                if(data != null) {
                    rCell.setCellValue(data);
                }

                if(StringUtils.isNotBlank(data1)) {
                    rCell.setCellValue(data1);
                }
                startCell++;
            }
            writeStartRow++;
        }
    }