With openpyxl how can I find out how many cells are merged in .xlsx

Question

I have sheet where the "heading" are in merged cells and the value is in the cells under the "heading", see example.

How can I count how many cells the heading spans? I need this to know where to start and stop reading the cells that belong to the "heading".

+-----------+-----------+
|  Heading1 | Heading2  |
+-----------+-----------+
| 1 | 2 | 3 | 3 | 3 | 4 |
+---+---+---+---+---+---+
| 4 | 5 | 6 | 3 | 3 | 3 |
+---+---+---+---+---+---+

Do the cells in the header row have any sort of a "span" attribute? — martineau, Jul 13 '16 at 07:44
Not quite sure what you mean by span attribute. I think their just manually merged. — The Dude, Jul 13 '16 at 07:48
There must be something in the Excel file that indicates that a header cell spans more than one cell of the row. Print a `dir()` of the cell object that openpyxl provides and see what its attributes are. — martineau, Jul 13 '16 at 07:54
There is no span attribute. Merged cells are kept as `A1:E2` ranges in Excel so client code has to implement it's own spanning calculation. — Charlie Clark, Jul 13 '16 at 08:40

score -1 · Answer 1 · answered Jul 13 '16 at 08:04

-1

You could use a function in VBA in Excel, like below

Public Function COLUMNSINMERGED(r As Excel.Range)

If r.MergeCells Then
    COLUMNSINMERGED=r.MergeArea.Columns.Count
    Debug.Print r.MergeArea.Address, "Cols : " & r.MergeArea.Columns.Count, "Rows : " & r.MergeArea.Rows.Count
else
   COLUMNSINMERGED=r.cells.count      
End If

End Function

answered Jul 13 '16 at 08:04

Nathan_Sav

8,466
2
13
20

The question is specifically about openpyxl. – Charlie Clark Jul 13 '16 at 08:41
The question is "How can I find out how many cells are merged in .xlsx" and you have an Excel tag, bit misleading then, not mentioning python except in a tag. – Nathan_Sav Jul 13 '16 at 08:42

Boikem · Answer 2 · 2018-01-25T03:35:04.160

You can get the convertLetterToColumnNum function from Excel Column Number to Text

#calculates the span given a merged cell
#first checks to see if cell is merged and then calculates the span
def calculateSpan(sheet, cell):
    idx = cell.coordinate
    for range_ in sheet.merged_cell_ranges:
        merged_cells = list(openpyxl.utils.rows_from_range(range_))
        for row in merged_cells:
            if idx in row:
                # If this is a merged cell
                first_col=convertLetterToColumnNum(str(merged_cells[0][0][0]))
                second_col = convertLetterToColumnNum(str(merged_cells[0][1][0]))
                span=abs(second_col-first_col)+1 #remove +1 if you want to count from zero
                return span

#usage
print(calculateSpan(mysheet,mysheet['D11']))

The rest is a modified version of How do i get value present in a merged cell

With openpyxl how can I find out how many cells are merged in .xlsx

2 Answers2