Monday, June 7, 2010

Merging and Centering Excel cells from C# using Office Interop

Many a times, we need to generate Excel reports as output from Windows applications. There are now 2 ways to do this:

To be clear, Open XML is the recommended approach for working with Office applications using .Net. However, there are still many systems that rely heavily on Interop for this so in this post, I will be talking using the Interop approach to work with Excel. Specifically, let’s look at how we can merge a collection of cells and center them through .Net.

To accomplish this, we need to perform 3 steps, and they are as easy as they come – each step requires a single line of code in C#. So let’s take a look at the steps involved:

  • Get an Excel range – For folks familiar with Excel interop, they would be aware that for most operations we would need to get our hands on an Excel Range object and then perform the intended operation on the range. Here is how we can create a Range object

Microsoft.Office.Interop.Excel.Range range = excelApp.get_Range(sheet.Cells[startRowIndex, startColumnIndex], sheet.Cells[endRowIndex, endColumnIndex]);

Here, excelApp refers to an instance of Microsoft.Office.Interop.Excel.Application class. The parameters that we need to pass to the get_Range() specify where we want the range to start and where it should end.

  • Merge the cells – Once we have the handle to the range, we can call the Merge() on the range to do just that – merge the cells that form the range into a single cell

(Warning – there might be data loss if multiple cells within the range have different values)

range.Merge(Type.Missing);

  • Center the cells – Finally, we need to set the alignment of the data in the merged cells so that data appears centered horizontally. This can be done again with a single statement as shown below

range.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;

So there you have it, we can put these 3 steps into a method and use it whenever we want to merge and center cells. One more thing we need to do is add a reference to Microsoft.Office.Interop.Excel.dll and import the same DLL to be able to use the Excel Interop API.

8 comments:

  1. Microsoft.Office.Interop.Excel.Range range = excelApp.get_Range(sheet.Cells[startRowIndex, startColumnIndex], sheet.Cells[endRowIndex, endColumnIndex]);

    this doesnt work, only if I do like that:

    Microsoft.Office.Interop.Excel.Range range = excelApp.get_Range("A1","A2");

    also, how can I do the following?

    1 big cell over 4 other cells:

    [ ]
    [][][][]

    ReplyDelete
  2. New111, when you say this code:
    Microsoft.Office.Interop.Excel.Range range = excelApp.get_Range(sheet.Cells[startRowIndex, startColumnIndex], sheet.Cells[endRowIndex, endColumnIndex]);
    does not work, do you get an error or it doesn't return the range you are expecting? What values did you pass to the method?

    Regarding the second part of your comment, to do that, you would need to merge the cells. Step 2 in my post explains how to do that.

    ReplyDelete
  3. when I start the application it says:

    "'object' does not contain a definition for 'get_Range'" when I use this:

    Microsoft.Office.Interop.Excel.Range range = excelApp.get_Range(sheet.Cells[startRowIndex, startColumnIndex], sheet.Cells[endRowIndex, endColumnIndex]);


    but it works perfectly fine if I use:

    Microsoft.Office.Interop.Excel.Range range = sheet.get_Range("A1","B1");
    range.Merge();

    so basically if I use "A1" instead of [1,1] it works


    your description still helped a lot, thx for that ! :)

    ReplyDelete
  4. That's really very helpfull.. Thanks

    ReplyDelete
  5. Thanks for the post!

    Just to correct something, this line is wrong:

    Microsoft.Office.Interop.Excel.Range range = excelApp.get_Range(sheet.Cells[startRowIndex, startColumnIndex], sheet.Cells[endRowIndex, endColumnIndex]);

    The right way to do it should be:
    Microsoft.Office.Interop.Excel.Range range = osheet.Range(osheet.Cells[startRowIndex, startColumnIndex], osheet.Cells[endRowIndex, endColumnIndex]);

    Where osheet is the Workbook.ActiveSheet of type Microsoft.Office.Interop.Excel._Worksheet.

    According to Microsoft's documentation the function get_Range() should not be used:

    Worksheet.get_Range Method
    Gets a Microsoft.Office.Interop.Excel.Range object that represents a cell or a range of cells. Use the Range property instead of this method. From here: http://msdn.microsoft.com/en-us/library/microsoft.office.tools.excel.worksheet.get_range.aspx

    Other objects as the Workbook and the ExcelApplication has the Range property but doing it with the Worksheet worked for me.

    Hope this helps!

    ReplyDelete
  6. In the last comment, osheet.Range -> All the signs must be "[" or "]" I missed that, sorry!

    ReplyDelete
  7. thanks for share.

    ReplyDelete