Give a tableStyle to my excel sheet in excel

Question

I'm displaying the result of a stored procedure in excel file using excel dna,i want to give a table style to the header of my excel sheet but i didn't find a way to do this, examples i found are not simila to my case, this is my code, it may explain more:

   public static object loadViewData(string date)
    {
        DateTime dt = DateTime.ParseExact(date, "dd/MM/yyyy", CultureInfo.InvariantCulture);
        object[,] resultFromDB = getViewDataFromDB(dt);           
        object res= ArrayResizer.displayResultInExcelFile(resultFromDB);

        return res;
    }

getViewDataFromDB returns data from sql Server, and displayResultInExcelFile displays data into excel file and this is it's code:

            public static object displayResultInExcelFile(object[,]array)
    {
        var caller = Excel(xlfCaller) as ExcelReference;
        if (caller == null)
            return array;

        int rows = array.GetLength(0);
        int columns = array.GetLength(1);
        if (rows == 0 || columns == 0)
            return array;

        var rowLast = caller.RowFirst + rows - 1;
        var columnLast = caller.ColumnFirst + columns - 1;
        if (rowLast > ExcelDnaUtil.ExcelLimits.MaxRows - 1 ||
         columnLast > ExcelDnaUtil.ExcelLimits.MaxColumns - 1)
        {
            return ExcelError.ExcelErrorValue;
        }

        ExcelAsyncUtil.QueueAsMacro(
            delegate
            {
                var newTarget = new ExcelReference(caller.RowFirst + 1, rowLast + 1, caller.ColumnFirst, columnLast, caller.SheetId);
                newTarget.SetValue(array);
            } );
        return array;
    }

Can I add a tableStyle to my excel file please ? i want something like

TableStyle = "TableStyleMedium9";

Thanks

Answer 1

finally i found how to add a table style to my excel sheet,

 public static object displayResultInExcelFile(object[,]array)
{
    var caller = Excel(xlfCaller) as ExcelReference;
    if (caller == null)
        return array;

    int rows = array.GetLength(0);
    int columns = array.GetLength(1);
    if (rows == 0 || columns == 0)
        return array;

    var rowLast = caller.RowFirst + rows - 1;
    var columnLast = caller.ColumnFirst + columns - 1;
    if (rowLast > ExcelDnaUtil.ExcelLimits.MaxRows - 1 ||
     columnLast > ExcelDnaUtil.ExcelLimits.MaxColumns - 1)
    {
        return ExcelError.ExcelErrorValue;
    }

    ExcelAsyncUtil.QueueAsMacro(
        delegate
        {
            var newTarget = new ExcelReference(caller.RowFirst + 1, rowLast + 1, caller.ColumnFirst, columnLast, caller.SheetId);
Microsoft.Office.Interop.Excel.Application oxl = (Microsoft.Office.Interop.Excel.Application)Marshal.GetActiveObject("Excel.Application");
            oxl.Visible = true;               
            Workbook wrkbk = oxl.ActiveWorkbook;
            string wbkname =wrkbk.Name;
            Worksheet wrksht = wrkbk.ActiveSheet ;
            string wrksheetname = oxl.ActiveSheet.Name;               
            object cell1 = wrksht.Cells[newTarget.RowFirst+1, newTarget.ColumnFirst];

            object cell2 = wrksht.Cells[newTarget.RowFirst+1
                , newTarget.ColumnLast];
            Range headerRange = wrksht.get_Range(cell1, cell2);
            wrksht.ListObjects.AddEx(XlListObjectSourceType.xlSrcRange, headerRange, Type.Missing,XlYesNoGuess.xlYes,Type.Missing).Name= "MyTableStyle";
            wrksht.ListObjects.get_Item("MyTableStyle").TableStyle = "TableStyleMedium9";

            newTarget.SetValue(array);
        } );
    return array;
}