如何将示例格式的多行插入excel格式文件

Question

我有一个像这样的excel格式：

我有两个函数来填充数据。这个是填充细节。

    public XSSFWorkbook getDetail(List<ResultDTO> ResultDTOList, XSSFWorkbook workBook,
        String sheetName) throws DaoException {

    XSSFSheet sheet = workBook.getSheet(sheetName);

    // first row at 9
    rowIdx = 9;

    int count = 0;
    for (ResultDTO dto : ResultDTOList) {

        colIdx = 0;
        count++;
        // get row 
        row = sheet.getRow(rowIdx);
        if (row == null) {
            row = sheet.createRow(rowIdx);
        }
        row.setHeightInPoints(16.5F);
        // No
        callCreateCell(row, colIdx++).setCellValue(count);
        // ItemNo
        callCreateCell(row, colIdx++).setCellValue(castRichTextString(dto.getItemNo()));
        // ArtistName
        callCreateCell(row, colIdx++).setCellValue(castRichTextString(dto.getArtistName()));
        colIdx++;
        // ItemTitle
        callCreateCell(row, colIdx++).setCellValue(castRichTextString(dto.getItemTitle()));
        colIdx++;

        // Size
        callCreateCell(row, colIdx++).setCellValue(dto.getSize());
        colIdx++;

        // Classification
        callCreateCell(row, colIdx++).setCellValue(dto.getClassification());
        colIdx++;

        // Detail
        callCreateCell(row, colIdx++).setCellValue(StringUtil.cutNewLineCharacter(dto.getPayDetail()));

        // Technique
        callCreateCell(row, colIdx++).setCellValue(dto.getTechnique());

        // Uriage
        callCreateCell(row, colIdx++).setCellValue(dto.getUriageWithoutTaxPrice());

        row = null;
        rowIdx++;
        rowIdx++;
    }
    return workBook;
}

这个用来填充总数

    public HSSFWorkbook getSum(UriageForm uriageForm, HSSFWorkbook workBook, String sheetName) {
    HSSFSheet sheet = workBook.getSheet(sheetName);

     CellStyle style1 = workBook.createCellStyle();

    style1.setAlignment(CellStyle.ALIGN_RIGHT);

    //set font size 14
    Font font1 = workBook.createFont();
    font1.setFontHeightInPoints((short)14);
    style1.setFont(font1);

    style1.setBorderLeft(CellStyle.BORDER_THIN);
    style1.setBorderBottom(CellStyle.BORDER_MEDIUM);

    Row row = sheet.getRow(20);
    //SUM1
    Cell cell2 = row.getCell(8);
    cell2.setCellValue(uriageForm.getPrintNestedSize());
    cell2.setCellStyle(style1);

    //SUM2
    Cell cell3 = row.getCell(9);
    cell3.setCellValue(uriageForm.getPrintTxtShouhi());
    cell3.setCellStyle(style1);

    //SUM3
    Cell cell4 = row.getCell(10);
    cell4.setCellValue(uriageForm.getPrintTxtSum());
    cell4.setCellStyle(style1);

    //SUM4
    Cell cell5 = row.getCell(12);
    cell5.setCellValue(uriageForm.getPrintTxtSumAll());
    cell5.setCellStyle(style1);

    return workBook;
}

问题是，如果我有超过5条记录，数据将被填充到第20行，我的格式被破坏。我的预期结果是，如果有超过5条记录，那么它将为每个添加的记录添加2行（作为格式），并且我的SUM始终是工作表的最后2行。

我正在考虑将 SUM 性别删除到另一个工作表中，并将其复制到最后一个工作表中，直到详细信息完成。但是我遇到了一些问题：

1. 哪个函数可以帮助我将行从一个工作表复制到另一个工作表？
2. 我只为5条记录设置了格式，那么如何为6th中的每个添加记录插入2行，以保持与旧记录相同的格式？（例如：第10行的复制格式

如果有点混乱，我很抱歉。这是我第一次使用Java和POI，所以我非常期待你的帮助！谢谢大家！

Answer 1

添加CopRow函数

    private void copyRow(XSSFWorkbook workbook, XSSFSheet worksheet, int sourceRowNum, int destinationRowNum) {
        XSSFRow newRow = worksheet.getRow(destinationRowNum);
        XSSFRow sourceRow = worksheet.getRow(sourceRowNum);

          if (newRow != null) {
            worksheet.shiftRows(destinationRowNum, worksheet.getLastRowNum(), 1);
            newRow = worksheet.createRow(destinationRowNum);
          } else {
            newRow = worksheet.createRow(destinationRowNum);
          }

          // copy process
          for (int i = 0; i < sourceRow.getLastCellNum(); i++) {
            Cell oldCell = sourceRow.getCell(i);
            Cell newCell = newRow.createCell(i);

            // if source cell is blank -> stop
            if (oldCell == null) {
              newCell = null;
              continue;
            }

            //copy style
            CellStyle newCellStyle = workbook.createCellStyle();
            newCellStyle.cloneStyleFrom(oldCell.getCellStyle());
            newCell.setCellStyle(newCellStyle);

            //copy cell type
            newCell.setCellType(oldCell.getCellType());

            //copy cell value
            switch (oldCell.getCellType()) {
            case Cell.CELL_TYPE_BLANK:
              newCell.setCellValue(oldCell.getStringCellValue());
              break;
            case Cell.CELL_TYPE_BOOLEAN:
              newCell.setCellValue(oldCell.getBooleanCellValue());
              break;
            case Cell.CELL_TYPE_ERROR:
              newCell.setCellErrorValue(oldCell.getErrorCellValue());
              break;
            case Cell.CELL_TYPE_FORMULA:
              newCell.setCellFormula(oldCell.getCellFormula());
              break;
            case Cell.CELL_TYPE_NUMERIC:
              newCell.setCellValue(oldCell.getNumericCellValue());
              break;
            case Cell.CELL_TYPE_STRING:
              newCell.setCellValue(oldCell.getRichStringCellValue());
              break;
            }
          }

          //copy column merge
          for (int i = 0; i < worksheet.getNumMergedRegions(); i++) {
            CellRangeAddress cellRangeAddress = worksheet.getMergedRegion(i);
            if (cellRangeAddress.getFirstRow() == sourceRow.getRowNum()) {
              CellRangeAddress newCellRangeAddress = new CellRangeAddress(newRow.getRowNum(),
                  (newRow.getRowNum() + (cellRangeAddress.getLastRow() - cellRangeAddress.getFirstRow())),
                  cellRangeAddress.getFirstColumn(), cellRangeAddress.getLastColumn());
              worksheet.addMergedRegion(newCellRangeAddress);
            }
          }
        }

在< code>getDetail函数中，添加以下代码:

int listSize = resultUriageWorkDTOList.size();
//add 2 rows for each record from 5th record 
for (int  i = 1; i <= (listSize - 5); i++) {
    this.copyRow(workBook, sheet, 10, 12);
    this.copyRow(workBook, sheet, 11, 13);
    sheet.addMergedRegion(new CellRangeAddress(11,12,4,5));
}

资料来源:https://www.sukerou.com/2019/03/poi-apache-poi-tips.html