none
Formatting an Excel Range as a Table Programatically leads to error: A table cannot overlap another table. RRS feed

  • Question

  • I am making an add-in and I am trying to format the output which my add-in generates,using Format as table table-styles provided by Excel.

    The one which you get on the 'home tab' --> 'Format as Table' button on the ribbon.

    I am using following code:

    SourceRange.Worksheet.ListObjects.Add(XlListObjectSourceType.xlSrcRange,
     
    SourceRange, System.Type.Missing, xlYesNo, System.Type.Missing).Name =
     
    TableName;

    SourceRange.Select();

    SourceRange.Worksheet.ListObjects[TableName].TableStyle = TableStyleName;

    TableStyleName is any style name like TableStyleMedium17, you get it if you just hover a particular style in Excel.

    My problem is that, even if I keep the SourceRange as 10 columns, all the columns right till the end get selected and are considered as one table. Because of that the table I populate right next to it is also considered as a part of the first table that was generated.Since, both the table have same column names excel automatically changes the column names in all the following tables that are generated. Also, because I am generating the tables in a loop after 2 tables are generated I get the error :

    A table cannot overlap another table.

    PS: I am clearly mentioning SourceRange as:

    var startCell = (Range)worksheet.Cells[startRow, startCol];
    var endCell = (Range)worksheet.Cells[endRow, endCol];
    var SourceRange = worksheet.get_Range(startCell, endCell);

    Kindly suggest a way out.


    sutharmonil

    Tuesday, September 25, 2012 6:09 AM

Answers

  • Hi Sutharmonil,

    Thank you for posting in the MSDN Forum.

    According to your description, I've written the below sample for you. It's an Console Application.

    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Text;
    using Excel = Microsoft.Office.Interop.Excel;
    using Office = Microsoft.Office.Core;
    
    namespace ConsoleApplication_FormattingAsTable
    {
        class Program
        {
            static void Main(string[] args)
            {
                //Suppose you have a workbook in D:\ named Tables.xlsx
                string wbkName = @"D:\Tables.xlsx";
    
                //Open Workbook.
                Excel._Application xlApp = new Excel.Application();
                xlApp.Visible = true;
                Excel.Workbook xlWbk= xlApp.Workbooks.Open(wbkName);
                Excel.Worksheet xlWsht = xlWbk.Sheets[1];
                
                Excel.ListObject xlTable;
                //Pre-defined Table Style list.
                string[] tableStyle = { "TableStyleLight1", "TableStyleLight2", 
                                        "TableStyleLight3", "TableStyleMedium1", 
                                        "TableStyleMedium2", "TableStyleMedium3", 
                                        "TableStyleDark1", "TableStyleDark2", 
                                        "TableStyleDark3", "TableStyleDark4" };
    
                int i; //Add 10 tables in via a for loop.
                for (i = 1; i <= 20; i = i + 2)
                {
                    var startCell = (Excel.Range)xlWsht.Cells[1, i];
                    var endCell = (Excel.Range)xlWsht.Cells[5, i + 1];
                    var sourceRange = (Excel.Range)xlWsht.get_Range(startCell, endCell);
    
                    xlTable = sourceRange.Worksheet.ListObjects.Add(
                        Excel.XlListObjectSourceType.xlSrcRange, sourceRange,
                        Type.Missing, Excel.XlYesNoGuess.xlNo, Type.Missing);
                    xlTable.TableStyle = tableStyle[i / 2];
                }
            }
        }
    }

    Effect of Code above, 10 tables in total.

    I've also met the error when I wrote 

    var endCell = (Excel.Range)xlWsht.Cells[5, i + 1];

    into 

    var endCell = (Excel.Range)xlWsht.Cells[5, i + 2];

    Hope it helps.

    Best regards,
    Quist


    Quist Zhang [MSFT]
    MSDN Community Support | Feedback to us


    Thursday, September 27, 2012 7:02 AM
    Moderator