Geek asks and answers

How to hide worksheet in Excel using OpenXML C #?

I have an Excel template with various sheets on which I dump data received from SQL Server using OpenXML, C #. After I finished dumping the data, I need to hide some sheets based on conditions. I could not find any piece of code to hide a specific sheet using C # OpenXML.

I tried the following, but the sheets were not 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.

Thank.

+5
source share
1 answer

You must set the ActiveTabclass property WorkbookView to an index that is different from the index of the worksheet that you would like to hide.

, , ( 0) excel ActiveTab .

( , ):

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

More articles:


All Articles