Reading Excel sheet with merged cells

[eric.marciano]

Hello experts,
I have read many topics on this forum about merged cells in Excel, including this one, but none helped me in answering my tricky and specific question.

For a specific need, I need to read an Excel sheet that has keys and values. To make it simple to understand, I created a simple Excel sheet to illustrate my need, it is attached.
MergeTest.xlsx (9.1 KB)

Depending on the key in column A, I must read the corresponding low and high values (in columns B and C). If you look at row #7 (no merge), you can read values in B7 and C7, easy.
For row #4 (key 18G), I used the trick explained on the forum: if the cell is empty, I read the cell above until I find a value, it works.
The real problem is for row #6 (key 20Y), the B and C columns are merged. To read the high value, I read cell C6. It is empty so I look above in C5 and it is still empty, because the value for all cell B5, C5, B6, C6 is stored in cell B5.
I couldn’t find an algorithm to read the low and high value in all the possible cases shown in this sample sheet.
Can you help me?

[Yoichi]

Hi,

Probably, it’s necessary to check if what cells are merged and get top-left value from it, in these complex cases.
For now, can you try the following sample? This sample fills data in blank cells from information for merged cell using ClosedXML

Using wb As New ClosedXML.Excel.XLWorkbook(filename)
Dim sheet As ClosedXML.Excel.IXLWorksheet = wb.Worksheet(sheetname)
For r As Int32=0  To dt.Rows.Count-1
For c As Int32 = 0 To dt.Columns.Count-1
        If (dt.Rows(r)(c).ToString="") Then
			   Dim cell As  ClosedXML.Excel.IXLCell  = sheet.Cell(row+r, col+c)
               Dim range As ClosedXML.Excel.IXLRangeAddress = cell.MergedRange().RangeAddress
               If (range.ColumnSpan > 1 OrElse range.RowSpan > 1) Then
				   dt.Rows(r)(c) = dt.Rows(range.FirstAddress.RowNumber-row)(range.FirstAddress.ColumnNumber-col)
			   End If
	    End If
Next
Next
End Using

Sample
Sample20240324-1a.zip (9.9 KB)

Regards,

[eric.marciano]

I don’t like to include pure code in my automations, but I must say this works perfectly. I regret I don’t have access to the merged cells through activity.
Anyway, I thank you very much, problem solved