CODEWITHSUNDEEP
c#openxml-sdk
Raghu
Raghu

Reputation: 105

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

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.

Upvotes: 8

Views: 8854

Answers (2)

Kiran Nakhale
Kiran Nakhale

Reputation: 1

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" };

Upvotes: 0

Hans
Hans

Reputation: 13030

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.");
}

Upvotes: 14

Related Questions