none
How to programmatically hide a column on the excel RRS feed

  • Question

  •  Hi,
         I'm writing an application level add-in with VSTO 2005 SE (C#) for Excel 2007. On the add-in, I had programmatically create a worksheet with data from the database. After the data is populated, how do I hide a particular column from the user. Please advice. Thanks.

    Microsoft.Office.Interop.Excel.Range rng = null;     
    DataTable dataTable = ldsetData.Tables[0];     
    Microsoft.Office.Interop.Excel.Worksheet sheetToAddTo = activeSheet;     
    //create the object to store the column names     
    object[,] columnNames;     
    columnNames = new object[1, dataTable.Columns.Count];     
    //add the columns names from the datatable     
    for (int i = 0; i < dataTable.Columns.Count; i++)     
    {     
    columnNames[0, i] = dataTable.Columns[i].ColumnName;     
    }     
    //get a range object that the columns will be added to     
    Microsoft.Office.Interop.Excel.Range columnsNamesRange = (Microsoft.Office.Interop.Excel.Range)sheetToAddTo.get_Range(sheetToAddTo.Cells[1, 1], sheetToAddTo.Cells[1, dataTable.Columns.Count]);     
    //a simple assignement allows the data to be transferred quickly     
    columnsNamesRange.Value2 = columnNames;     
    //release the columsn range object now it is finished with     
    columnsNamesRange = null;     
    //create the object to store the dataTable data     
        
    object[,] rowData;     
    rowData = new object[dataTable.Rows.Count, dataTable.Columns.Count];     
    //insert the data into the object[,]     
    for (int iRow = 0; iRow < dataTable.Rows.Count; iRow++)     
    {     
        for (int iCol = 0; iCol < dataTable.Columns.Count; iCol++)     
        {     
            rowData[iRow, iCol] = dataTable.Rows[iRow][iCol];     
        }     
    }     
    //get a range to add the table data into      
    //it is one row down to avoid the previously added columns     
    Microsoft.Office.Interop.Excel.Range dataCells = (Microsoft.Office.Interop.Excel.Range)sheetToAddTo.get_Range(sheetToAddTo.Cells[2, 1],sheetToAddTo.Cells[dataTable.Rows.Count + 1, dataTable.Columns.Count]);     
        
        
    //assign data to worksheet     
    dataCells.Value2 = rowData;     
        
    //release range     
    dataCells = null;    
    Wednesday, February 25, 2009 9:45 AM

Answers

  • Hi ,
    Please try following code to hide a column in Excel :
    Worksheet.get_Range("C:C", missing).EntireColumn.Hidden = true;

    Thanks
    We have published a VSTO FAQ recently, you can view them from the entry thread http://social.msdn.microsoft.com/Forums/en/vsto/thread/31b1ffbf-117b-4e8f-ad38-71614437df59. If you have any feedbacks or suggestions on this FAQ, please feel free to write us emails to colbertz@microsoft.com.
    • Marked as answer by Tim Li Wednesday, March 4, 2009 2:48 AM
    Wednesday, March 4, 2009 2:48 AM

All replies

  • Hi ,
    Please try following code to hide a column in Excel :
    Worksheet.get_Range("C:C", missing).EntireColumn.Hidden = true;

    Thanks
    We have published a VSTO FAQ recently, you can view them from the entry thread http://social.msdn.microsoft.com/Forums/en/vsto/thread/31b1ffbf-117b-4e8f-ad38-71614437df59. If you have any feedbacks or suggestions on this FAQ, please feel free to write us emails to colbertz@microsoft.com.
    • Marked as answer by Tim Li Wednesday, March 4, 2009 2:48 AM
    Wednesday, March 4, 2009 2:48 AM
  • Hello,

    i also try to hide some columns and "Range.EntireColumn.Hidden = true;" isn't working for me. Can someone please explain to me, why?

     

    worksheet.get_Range("A1", "A8").EntireRow.Hidden = true;
    worksheet.get_Range("E:E", Missing.Value).EntireColumn.Hidden = true;
    worksheet.get_Range("G:G", Missing.Value).EntireColumn.Hidden = true;
    worksheet.get_Range("H:H", Missing.Value).EntireColumn.Hidden = true;
    

     

    Hiding the rows works perfectly, but columns stay visible. I don't understand why.

     

    Greetings

    Thursday, August 5, 2010 11:42 AM
  • The issue is due to updating a field for the column after it was hidden. Try move the code at the end or your code just befor exiting.

    I hope it helps

    • Proposed as answer by Jan Handrich Friday, February 18, 2011 10:28 AM
    Thursday, January 13, 2011 10:12 PM
  • hello,

    workSheet.get_Range(workSheet.Rows.Cells[1, 8], workSheet.Rows.Cells[workSheet.Rows.Count, 8]).EntireColumn.Hidden = true;

    //8: column hide

    please test again!

    Tuan.le-Cadena

    Thursday, December 22, 2011 2:29 AM
  • "Worksheet.get_Range("C:C", missing).EntireColumn.Hidden = true"- This helps. Thanks

    Friday, July 26, 2013 6:44 PM