如何使用 Apache POI 为相邻列设置单元格值？

Question

我的目标是遍历一个已有的 2 列 Excel 电子表格。一个称为制造商，另一个称为 DNC 或请勿联系。

我想遍历我拥有的制造商列表，并将那些不应联系的标记为红色，并在制造商名称列表旁边的相邻空白栏中标记一些为什么无法联系他们的注释。我在下面附上了我的代码。

我将每个制造商对象的字段存储在一个名为“mu”的链表中，它们是“name”和“DNC_Reason”。

Iterator<Row> rowIterator2 = spreadsheet.iterator();

while (rowIterator2.hasNext()) {

    Row row2 = rowIterator2.next();

    Cell DNC_Reason = row2.getCell(1);

    if(row2.getCell(1) == null) {

        row2.createCell(1);

    }

    Iterator<Cell> cellIterator2 = row2.cellIterator();

    while (cellIterator2.hasNext()) {

        Cell cell = cellIterator2.next();

        Pattern p = Pattern.compile("[\\.$|,|;|'|\\s|-]|\\b(LLC|Company|Incorporated|Co|Manufacturer|The|Limited|Ltd|Inc)\\b", Pattern.CASE_INSENSITIVE);

        Matcher m = p.matcher(cell.getStringCellValue());

        String s = m.replaceAll("");

        for (Manufacturer mu : mfgs) {

            if (cell.getColumnIndex() == 0 && mu.getName().equals(s)) {

                cell.setCellStyle(style);

                DNC_Reason.setCellValue(mu.getDNCReason());

            }

        }

    }

}

Answer 1

您应该为您创建一个地图，not-to-be-contacted-manufacturers名称作为键，制造商作为值。然后您可以使用containsKey而不是不断迭代mfgs-list.您应该使用 的结果遍历行rowIterator。您不需要另一个迭代器。局部变量不应该以大写字母开头（DNC_Reason-更好的名字是dncReasonCell）假设制造商单元格已填充的示例代码（getStringValue()可能会导致NullPointerException未给出适当的值），样式变量已初始化并且您有一个不可联系的制造商地图：Iterator<Row> rowIterator = spreadsheet.rowIterator();while (rowIterator.hasNext()) {&nbsp; &nbsp; Row row = rowIterator.next();&nbsp; &nbsp; Cell dncReasonCell = row.getCell(1);&nbsp; &nbsp; if (dncReasonCell == null) {&nbsp; &nbsp; &nbsp; &nbsp; dncReasonCell = row.createCell(1, CellType.STRING);&nbsp; &nbsp; }&nbsp; &nbsp; Cell manufacturerCell = row.getCell(0);&nbsp; &nbsp; String manufacturerNameForDncTest = Pattern&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; .compile("[\\.$|,|;|'|\\s|-]|\\b(LLC|Company|Incorporated|Co|Manufacturer|The|Limited|Ltd|Inc)\\b", Pattern.CASE_INSENSITIVE)&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; .matcher(manufacturerCell.getStringCellValue()).replaceAll("");&nbsp; &nbsp; if (notToBeContactedManufacturers.containsKey(manufacturerNameForDncTest)) {&nbsp; &nbsp; &nbsp; &nbsp; manufacturerCell.setCellStyle(style);&nbsp; &nbsp; &nbsp; &nbsp; dncReasonCell.setCellValue(notToBeContactedManufacturers.get(manufacturerNameForDncTest).getDNCReason());&nbsp; &nbsp; } else {&nbsp; &nbsp; &nbsp; &nbsp; dncReasonCell.setCellValue("");&nbsp; &nbsp; }}