CODEWITHSUNDEEP
c#excelexcel-interop
Nadia Hristova
Nadia Hristova

Reputation: 23

Merge excel cells with C# using Interop.Excel

I would like to merge several sequences of cells in order to form table header groupings.
I keep the information about the merged cell's value and it's column span in 

List<Tuple<string, int>>

Here is what I have so far: 

      appExcel.DisplayAlerts = false;

      int columnCellIndex = 1;
      foreach(var captionGroupInfo in extraCaptionGroupings)
      {
        Microsoft.Office.Interop.Excel.Range currRange = newWorksheet.Range[newWorksheet.Cells[1, columnCellIndex], 
        newWorksheet.Cells[1, columnCellIndex + captionGroupInfo.Item2]];
        currRange.Value2 = captionGroupInfo.Item1;
        currRange.Select();
        currRange.Merge(Missing.Value);

        columnCellIndex += captionGroupInfo.Item2;
       }
      }

So instead of: 

|    ||     Dog 1    ||     Dog 2    ||      Cat     ||  Hamster 1  ||   Hamster 2  ||  
--------------------------------------------------------------------------------------
| Id ||Name|Owner|Age||Name|Owner|Age||Name|Owner|Age||Nam|Owner|Age||Name|Owner|Age||

I get 

|                                         Dogs                                     |  
| Id ||Name|Owner|Age|Name|Owner|Age||Name|Owner|Age||Nam|Owner|Age|Name|Owner|Age||

It seems to merge all cells in 1.
Any ideas what I am doing wrong? Even if I increase columnCellIndex only by 1, still the result is not what I expect.

Upvotes: 0

Views: 6681

Answers (1)

JohnG
JohnG

Reputation: 9469

Since I can not see what the values are from captionGroupInfo.Item2 I am kind of guessing here. From your displayed output, I am guessing that you do not mean to merge the first column. It looks like a single non-merged column. So when you set columnCellIndex = 1; then you are starting from the first column. So I changed the starting column index to 2 because this appears to be where you want to start merging cells. Then I subtracted the ending column index value by 1 when getting the range for a merge. Otherwise the range will be ONE to many because we started at column 2 not column 1. With these changes it worked fine when I tested the returned values from captionGroupInfo.Item2 where they were 3 cells merge, 3 cells merge, 5 cells merge, 5, etc. It worked as expected with these changes. Hope this helps.

int columnCellIndex = 2;
foreach (var captionGroupInfo in extraCaptionGroupings)
{
  Microsoft.Office.Interop.Excel.Range currRange = newWorksheet.Range[newWorksheet.Cells[1, columnCellIndex], 
                                                                      newWorksheet.Cells[1, columnCellIndex + captionGroupInfo.Item2 - 1]];
  currRange.Value2 = captionGroupInfo.Item1;
  currRange.Select();
  currRange.Merge(Missing.Value);
  columnCellIndex += captionGroupInfo.Item2;
}

Upvotes: 2

Related Questions