none
OpenXml,Excel,C# RRS feed

  • Question

  • Hi,

    Please explain, how to hide particular column in the excelsheet using OpenXML in C#.

    thanks,

    Elangovan P

    Monday, December 19, 2011 10:38 AM

Answers

  • Hi Elangovan,

    The reason you got this exception because that you haven't done any customization for this column before, this column hasn't been added to Columns collection. So if you are unable to retrieve a certain column, you need to add this column manually and then hide it. Check the code below:

          Column col1  =null;
                //detect if the fifth column exists in columns collection
                 if (columns.Descendants<Column>().Any(c => c.Min == 5))
                 {
                     col1 = columns.Descendants<Column>().Where(c => c.Min == 5).First();
                 }
                  //if not, add this column
                 else
                 {
                     col1 = new Column();
                     col1.Min = 5;
                     col1.Max = 5;
                     col1.BestFit = true;
                     col1.CustomWidth = true;
                     columns.Append(col1);
                 }      
    
                //hide the column
                col1.Hidden = true;
    

    I hope this helps.


    Calvin Gao[MSFT]
    MSDN Community Support | Feedback to us
    Friday, December 23, 2011 3:38 AM
    Moderator

All replies

  • Hi Elangovan,

    Thanks for your post.

    You can use Hidden property of Column object:

              SpreadsheetDocument spreadDocument = SpreadsheetDocument.Open(fileName, true);
    
                //retrieve worksheetpart
                Sheet sheet1 = spreadDocument.WorkbookPart.Workbook.Descendants<Sheet>().Where(s => s.Name == "Sheet1").First();             
                WorksheetPart sheetPart = spreadDocument.WorkbookPart.GetPartById(sheet1.Id) as WorksheetPart;
                Worksheet worksheet1 = sheetPart.Worksheet;
    
                //retrieve columns collection
                Columns columns = worksheet1.GetFirstChild<Columns>();    
          
                //retieve the sec
                Column col1 = columns.Descendants<Column>().Where(c => c.Min == 2).First();
    
                //hide the column
                col1.Hidden = false;
    
                //save the workbook and close it
                spreadDocument.WorkbookPart.Workbook.Save();
                spreadDocument.Close();
    

    I hope this helps.


    Calvin Gao[MSFT]
    MSDN Community Support | Feedback to us
    Tuesday, December 20, 2011 8:19 AM
    Moderator
  • Hi  Calvin Gao,

    thanks for the reply. i will try the above and let you know shortly. thanks a lot.

     

    thanks,

    Elangovan P

     
    Tuesday, December 20, 2011 2:17 PM
  • Hi Calvin Gao,

     

    I got the exception when trying the above "sequence containce no elements"

     Column col1 = columns.Descendants<Column>().Where(c => c.Min == 2).First();

    Here..

    and please explain what do you mean by Min. how can i hide column 5.

     

    thanks,

    Elangovan P

     

    Tuesday, December 20, 2011 2:44 PM
  • Hi,

    Please explain, hoe to generate columns  and append it to the worksheet. and also please explain about hidding some columns Using openXML in C#.

     

    thanks,

    Elangovan P

     

    Thursday, December 22, 2011 1:46 PM
  • Hi Elangovan,

    The reason you got this exception because that you haven't done any customization for this column before, this column hasn't been added to Columns collection. So if you are unable to retrieve a certain column, you need to add this column manually and then hide it. Check the code below:

          Column col1  =null;
                //detect if the fifth column exists in columns collection
                 if (columns.Descendants<Column>().Any(c => c.Min == 5))
                 {
                     col1 = columns.Descendants<Column>().Where(c => c.Min == 5).First();
                 }
                  //if not, add this column
                 else
                 {
                     col1 = new Column();
                     col1.Min = 5;
                     col1.Max = 5;
                     col1.BestFit = true;
                     col1.CustomWidth = true;
                     columns.Append(col1);
                 }      
    
                //hide the column
                col1.Hidden = true;
    

    I hope this helps.


    Calvin Gao[MSFT]
    MSDN Community Support | Feedback to us
    Friday, December 23, 2011 3:38 AM
    Moderator