如何利用POI将Excel合并Key列对应Sentence列单元格分组为键值对？

Alright, let's work through this problem since you already have POI set up to read your Excel file—you just need to nail down the grouping logic for those merged Key cells. The core idea is to track the last valid Key you encountered, and use that for any rows where the Key cell is empty (from merged cells). Here's a step-by-step solution:

Step 1: Track the active Key and use a LinkedHashMap to preserve order #

Since you want to maintain the original order of Keys from your Excel sheet, a LinkedHashMap is perfect here (unlike a regular HashMap which doesn't guarantee order). We'll map each Key to a List<String> to collect all related Sentences, then merge them into a single value later if needed.

Step 2: Iterate through rows with merged cell logic #

When POI reads merged cells, only the first cell in the merged range has the Key value—all subsequent cells in the merge are empty. So we'll check if the current Key cell has a value; if not, we reuse the last valid Key we stored.

Here's the code implementation:

import org.apache.poi.ss.usermodel.*;
import java.util.*;

// Assume you've already loaded your Workbook and Sheet
Workbook workbook = WorkbookFactory.create(new File("your-excel-file.xlsx"));
Sheet sheet = workbook.getSheetAt(0);

// Use LinkedHashMap to keep the original order of Keys from Excel
LinkedHashMap<String, List<String>> keyToSentences = new LinkedHashMap<>();
String currentActiveKey = null;

// Skip the header row (start at row index 1; adjust if your header is at a different row)
for (int rowNum = 1; rowNum <= sheet.getLastRowNum(); rowNum++) {
    Row row = sheet.getRow(rowNum);
    if (row == null) continue; // Skip empty rows

    // Process Key cell
    Cell keyCell = row.getCell(0);
    String key = null;
    if (keyCell != null) {
        keyCell.setCellType(CellType.STRING);
        key = keyCell.getStringCellValue().trim();
    }

    // Process Sentence cell
    Cell sentenceCell = row.getCell(1);
    String sentence = null;
    if (sentenceCell != null) {
        sentenceCell.setCellType(CellType.STRING);
        sentence = sentenceCell.getStringCellValue().trim();
    }

    // Update active Key if we found a non-empty Key
    if (key != null && !key.isEmpty()) {
        currentActiveKey = key;
        // Initialize a new list for this Key if it doesn't exist yet
        keyToSentences.putIfAbsent(currentActiveKey, new ArrayList<>());
    }

    // Add the Sentence to the active Key's list (only if both are valid)
    if (currentActiveKey != null && sentence != null && !sentence.isEmpty()) {
        keyToSentences.get(currentActiveKey).add(sentence);
    }
}

Step 3: Merge the Sentences into a single value for XML #

Once you have the map of Keys to Sentence lists, you can merge the lists into a single string (use whatever separator makes sense for your XML—like semicolons, newlines, etc.):

LinkedHashMap<String, String> xmlReadyMap = new LinkedHashMap<>();
for (Map.Entry<String, List<String>> entry : keyToSentences.entrySet()) {
    // Customize the separator here (e.g., "\n" for line breaks, "; " for delimited text)
    String mergedSentences = String.join("; ", entry.getValue());
    xmlReadyMap.put(entry.getKey(), mergedSentences);
}

Quick Notes: #

• If your Key or Sentence cells have non-string types (like numbers), adjust the cell type handling to avoid errors.
• Add extra checks if your Excel might have blank Sentence rows that you want to skip.
• This logic works for both horizontal and vertical merged cells in the Key column, since we're only checking if the current Key cell is empty.

内容的提问来源于stack exchange，提问作者alfrosz