How to hide a sheet in Excel using OpenXML C#?

Question

I have an Excel Template with various sheets to which I am dumping data retrieved from SQL Server using OpenXML, C#. After I am done with dumping the data, I need to hide some of the sheets based on conditions. I couldn't find any piece of code to hide a particular sheet using C# OpenXML.

I tried the following but the sheets did not get hidden.

byte[] byteArray = File.ReadAllBytes("D:\\rptTemplate.xlsx");
using (MemoryStream mem = new MemoryStream())
{
mem.Write(byteArray, 0, (int)byteArray.Length);
using (SpreadsheetDocument rptTemplate = SpreadsheetDocument.Open(mem, true))
{
    foreach (OpenXmlElement oxe in (rptTemplate.WorkbookPart.Workbook.Sheets).ChildElements)
    {
     if(((DocumentFormat.OpenXml.Spreadsheet.Sheet)(oxe)).Name == "ABC")
        ((DocumentFormat.OpenXml.Spreadsheet.Sheet)(oxe)).State = SheetStateValues.Hidden;
    }
    rptTemplate.WorkbookPart.Workbook.Save();
}
}

Request help on this.

Thanks.

Lashawn

Answer 1

You have to set the ActiveTab property of the WorkbookView class to an index which is different from the index of the worksheet you would like to hide.

So, for example if you would like to hide the first worksheet (worksheet with index 0) in your excel file then set the ActiveTab property to the next visible worksheet index.

Here is a small code example (based on the code you provided):

static void Main(string[] args)
{
  byte[] byteArray = File.ReadAllBytes("D:\\rptTemplate.xlsx");

  using (MemoryStream mem = new MemoryStream())
  {
    mem.Write(byteArray, 0, (int)byteArray.Length);

    using (SpreadsheetDocument rptTemplate = SpreadsheetDocument.Open(mem, true))
    {
      foreach (OpenXmlElement oxe in (rptTemplate.WorkbookPart.Workbook.Sheets).ChildElements)
      {
        if(((DocumentFormat.OpenXml.Spreadsheet.Sheet)(oxe)).Name == "ABC")
        {
          ((DocumentFormat.OpenXml.Spreadsheet.Sheet)(oxe)).State = SheetStateValues.Hidden;

           WorkbookView wv = rptTemplate.WorkbookPart.Workbook.BookViews.ChildElements.First<WorkbookView>();

           if (wv != null)
           {
             wv.ActiveTab = GetIndexOfFirstVisibleSheet(rptTemplate.WorkbookPart.Workbook.Sheets);
           }                       
         }
      }
      rptTemplate.WorkbookPart.Workbook.Save();
    }
  }
}

private static uint GetIndexOfFirstVisibleSheet(Sheets sheets)
{
  uint index = 0;
  foreach (Sheet currentSheet in sheets.Descendants<Sheet>())
  {
    if (currentSheet.State == null || currentSheet.State.Value == SheetStateValues.Visible)
    {
      return index;
    }
    index++;
  }
  throw new Exception("No visible sheet found.");
}

Genovera

I tried the same code, Gone through debugging each line, everything is works good. But After execution of code. When i open Excel file sheet didn't hide. Can you help me? – Matthus

@NarendraKumar: It's very difficult to help without knowing the exact structure of your excel document and the names of your excel sheets. How many sheets do you have? – Genovera

@Genovera Document consist of 5 Sheet, Names like Priority, Task Status like that. And the document have "full control" permission for all users. – Matthus

@NarendraKumar: Did you change the name of the sheet in my code? – Genovera

@Genovera Yes i changed the sheet name in code. I gone through debugging the Excel file is readable and i can see Sheet name in if condition and setting property of hidden and saving document. But its not reflecting in document. – Matthus

@NarendraKumar: Could you provide your document (a simplified version with no confidential data). Then I can have a look. – Genovera

Did this ever get fixed? I have the same issue. It works for 1 of the tabs I am hiding but not both – Idleman

Answer 2

To hide sheet apply the "State" property of Sheet to all the sheets. By default "State" property of a Sheet is null. Following is the change I did and it hided Sheet2.

  Sheet sheet1 = new Sheet() { Name = "Sheet1", State = SheetStateValues.Visible, SheetId = (UInt32Value)1U, Id = "rId1" };
  Sheet sheet2 = new Sheet() { Name = "Sheet2", State = SheetStateValues.Hidden, SheetId = (UInt32Value)2U, Id = "rId2" };

Cesspool