Get reference to merged range that cell belongs to

[sa2304]

merged-cells.xlsx

I've got an XLSX file with some merged cells. Each line in file describes an item.

I iterate over rows and want to get all the values in columns.

for (const auto& line : rows) {
    string group = line[0].to_string();
}

File contains cell A1="Group 1" that is merged: spans range A1:A3. When I read first line, I've got A1's value. But A2 doesn't have any value, only spreadsheet knows that it has mergeCells node.

<sheetData>
    <row r="1" customFormat="false" ht="12.8" hidden="false" customHeight="false" outlineLevel="0" collapsed="false">
        <c r="A1" s="1" t="s">
            <v>0</v>
        </c>
        <c r="C1" s="0" t="s">
            <v>1</v>
        </c>
    </row>
</sheetData>
<mergeCells count="3">
    <mergeCell ref="A1:A3"/>
    <mergeCell ref="A4:A6"/>
    <mergeCell ref="B4:B5"/>
</mergeCells>

I only see xlnt::cell has is_merged() flag, but does xlnt API have any way to get a reference to first cell in that range?

[sa2304]

Okay, I found a solution. Not optimized yet, but working.

bool InRange(const xlnt::cell &cell, const xlnt::range_reference &range) {
  return range.top_left().row() <= cell.row()
      && range.top_left().column() <= cell.column()
      && cell.row() <= range.bottom_right().row()
      && cell.column() <= range.bottom_right().column();
}

template <class T>
std::optional<T> GetValue(const xlnt::cell& cell) try {
  auto s = cell.to_string();
  return boost::lexical_cast<T>(s);
} catch (...) {
  return {};
}

template <class T>
std::optional<T> GetMergedCellValue(const xlnt::worksheet& sheet, const xlnt::cell& cell) {
  if (cell.is_merged()) {
    for (const auto& r : sheet.merged_ranges()) {
      if (InRange(cell, r)) {
        return GetValue<T>(sheet.cell(r.top_left()));
      }
    }
  } else return GetValue<T>(cell);
}

class TestUtils : public ::testing::Test {};

TEST_F(TestUtils, GetMergedCellValue) {
  {
    xlnt::workbook wb;
    auto sheet = wb.active_sheet();
    sheet.cell("A1"s).value("Test"s);
    ASSERT_FALSE(sheet.cell("A2"s).has_value());
    sheet.merge_cells("A1:A3"s);
    // value is not copied to other cells
    ASSERT_FALSE(sheet.cell("A2"s).has_value());
    // but our utility function could access it
    auto value = utils::GetMergedCellValue<string>(sheet, sheet.cell("A2"s));
    ASSERT_EQ("Test"s, value);
  }
}

[tfussell]

I could see how this would be useful. Glad that you found a workaround. I can take a look at the code to see if I can add a built-in interface for this.