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

user_1153631 0
Viewed 7507

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.

openxml-sdk c#
2 Answers

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