How do I check which cells belong to a merged cell and get the merged cell's value using c# Exc-CodePudding

How do I make a List of 3 column data as List<List> data = new() where some of cells are merged? For example, I want to extract the data as follows:

List<List<string>> data = new();

data.Add(new() List<string> {"something", "418", "The value"});
data.Add(new() List<string> {"something1", "400", "The value"});
...

and so on. The problem arises with merged cells - how can I get the count of merged cells and its value? How do I get the items that are in the merged cells range (something, 418, something1, 400 etc.?

If there are better techniques to extract the data (there for sure are), it would be really helpful to receive a good tip for that.

Any answer is highly appreciated.

CodePudding user response：

To get Row Number merged:

int iRow, iCol;
iRow=1;
iCol=3;
int Num=oSheet.Cells(iRow, iCol).MergeArea.Rows.Count;

To get value of Cell merged:

string val=oSheet.Cells(iRow, iCol).MergeArea.Cells(1,1).Value;

To get first row of Cell Merged:

 int iRow1=oSheet.Cells(iRow, iCol).MergeArea.Cells(1,1).row;

if iRow != iRow1 , it is row merged to iRow1

To read your excel: ex:

For int iRow = 1 To oSheet.UsedRange.Rows.Count
   string val1=oSheet.Cells(iRow, 1).Value;
   string val2=oSheet.Cells(iRow, 2).Value;
   string val3=oSheet.Cells(iRow, 3).MergeArea.Cells(1,1).Value;   
   //Add value to List
   
Next