locked
ListObject - DataColumn - Caption instead of ColumnName RRS feed

  • Question

  • Is there any way to force ListObject binded to DataTable, to show DataTable.Caption instead of ColumnName property? 

    Thanks

    Wednesday, August 16, 2017 11:54 AM

Answers

  • Hello Dawid,

    Yes, it uses default name like "Column1" "Column2" if we don't set AutoSetDataBoundColumnHeaders.

    You could use the following code to reset the column name into caption of DataColumn

                for (int i = 0; i < listObj.ListColumns.Count; i++)
                {
                    listObj.ListColumns[i + 1].Name = dt.Columns[i].Caption;
                }

    Regards,

    Celeste


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by Dawid Sadlik Thursday, August 17, 2017 8:39 AM
    Thursday, August 17, 2017 7:34 AM
  • Hello,

    You may create each table for each month or you could create a summary table including a date field.

    If you create a summary table, you could use PivotTable to get data of each month. Please visit Create a PivotTable to analyze worksheet data

    Regards,

    Celeste


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by Dawid Sadlik Thursday, August 17, 2017 10:45 AM
    Thursday, August 17, 2017 10:08 AM

All replies

  • Hello,

    According to my test result, it is bound to caption instead of column name. What code do you use now?

    Here is my code and result:

    using Excel = Microsoft.Office.Interop.Excel;
    using Microsoft.Office.Tools.Excel;
               DataTable dt = new DataTable("dt");
                DataColumn c1 = new DataColumn();
                c1.ColumnName = "C1";
                c1.Caption = "Column1";
                dt.Columns.Add(c1);
                DataColumn c2 =new DataColumn();
                c1.ColumnName = "C2";
                c2.Caption = "Column2";
                dt.Columns.Add(c2);
                dt.Rows.Add("1", "2");
                dt.Rows.Add("3", "4");
    
                Worksheet worksheet = Globals.Factory.GetVstoObject(
           Globals.ThisAddIn.Application.ActiveWorkbook.Worksheets[1]);
                Excel.Range lstObjRange = worksheet.Range["B2", "C4"] as Excel.Range; 
                lstObjRange.Clear();
                string listobjectname = "ListObject";
                ListObject lo = worksheet.Controls.AddListObject(lstObjRange, listobjectname);
                lo.DataSource = dt;
    

    Regards,

    Celeste


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, August 17, 2017 2:28 AM
  • Hi

    Thank you for reply.

    This is my code

    Excel.Worksheet worksheet = (Excel.Worksheet)this.Application.ActiveWorkbook.Worksheets[1];
                Worksheet extendedWorksheet = Globals.Factory.GetVstoObject(worksheet);
                Excel.Range cell = extendedWorksheet.Range["$A$1","$B$1"];
                ListObject listObj = extendedWorksheet.Controls.AddListObject(cell, "list1");
                listObj.AutoSetDataBoundColumnHeaders = true;
    
                DataTable dt = new DataTable("dt");
                DataColumn c1 = new DataColumn();
                c1.ColumnName = "C1";
                c1.Caption = "Column A";
                dt.Columns.Add(c1);
                DataColumn c2 = new DataColumn();
                c2.ColumnName = "C2";
                c2.Caption = "Column B";
                dt.Columns.Add(c2);
    
                //BindingSource bindingSource = new BindingSource();
                //bindingSource.DataSource = dt;
                //listObj.SetDataBinding(bindingSource);
                //listObj.AutoSetDataBoundColumnHeaders = true;
    
                dt.Rows.Add("1", "2");
                dt.Rows.Add("3", "4");
    
                listObj.DataSource = dt;

    Without AutoSetDataBoundColumnHeaders result looks like this

    I think you named columns just like default names are, that is why it is working for you

    Regards,

    Dawid

    Thursday, August 17, 2017 6:57 AM
  • Hello Dawid,

    Yes, it uses default name like "Column1" "Column2" if we don't set AutoSetDataBoundColumnHeaders.

    You could use the following code to reset the column name into caption of DataColumn

                for (int i = 0; i < listObj.ListColumns.Count; i++)
                {
                    listObj.ListColumns[i + 1].Name = dt.Columns[i].Caption;
                }

    Regards,

    Celeste


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by Dawid Sadlik Thursday, August 17, 2017 8:39 AM
    Thursday, August 17, 2017 7:34 AM
  • Hi

    It's working, but I should been describing my whole scenario. I'm trying to set same caption to multiple headers in this way:

    Excel.Worksheet worksheet = (Excel.Worksheet)this.Application.ActiveWorkbook.Worksheets[1];
                Worksheet extendedWorksheet = Globals.Factory.GetVstoObject(worksheet);
                Excel.Range cell = extendedWorksheet.Range["$A$1","$B$1"];
                ListObject listObj = extendedWorksheet.Controls.AddListObject(cell, "list1");
                //listObj.AutoSetDataBoundColumnHeaders = true;
    
                DataTable dt = new DataTable("dt");
                DataColumn c1 = new DataColumn();
                c1.ColumnName = "C1";
                c1.Caption = "Column A";
                dt.Columns.Add(c1);
                DataColumn c2 = new DataColumn();
                c2.ColumnName = "C2";
                c2.Caption = "Column A";
                dt.Columns.Add(c2);
    
                //BindingSource bindingSource = new BindingSource();
                //bindingSource.DataSource = dt;
                //listObj.SetDataBinding(bindingSource);
                //listObj.AutoSetDataBoundColumnHeaders = true;
    
                dt.Rows.Add("1", "2");
                dt.Rows.Add("3", "4");
    
                listObj.DataSource = dt;
    
                for (int i = 0; i < listObj.ListColumns.Count; i++)
                {
                    listObj.ListColumns[i + 1].Name = dt.Columns[i].Caption;
                }

    but I get result

    Regards

    Dawid


    Thursday, August 17, 2017 7:55 AM
  • Hello,

    Excel doesn't allow same column names. It should be unique, so that we could refer the column using it name. For example, we could use ListColumns("ColumnName") to represent a column in the table. In formula, we could also use Table1["ColumnName"].

    Why do you want to set same caption?

    Regards,

    Celeste


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, August 17, 2017 9:06 AM
  • Because of design I've to programme

    Thursday, August 17, 2017 9:52 AM
  • Hello,

    You may create each table for each month or you could create a summary table including a date field.

    If you create a summary table, you could use PivotTable to get data of each month. Please visit Create a PivotTable to analyze worksheet data

    Regards,

    Celeste


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by Dawid Sadlik Thursday, August 17, 2017 10:45 AM
    Thursday, August 17, 2017 10:08 AM
  • I was considering creating each month in separate table but I wanted to have default sorting capability with auto generated headers. Now i have neither of those functionality

    Can I create PivotTable programmicaly? 

    Regards

    Dawid

    Thursday, August 17, 2017 10:45 AM
  • Hello,

    If you create each in separate table, I think there should be default sorting capability. I suggest you create a new thread for that issue.

    To create PivotTable programmatically, you may get started from recording macros. You could also visit Creating PivotTable Reports and Charts with VBA in Excel 2010

    Regards,

    Celeste


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, August 18, 2017 6:52 AM