Cannot Hide Worksheet

Oct 4, 2012 at 8:42 PM

Hi everyone. I have a somewhat frustrating problem.

I am using EPPlus to populate and format a excel template which includes hiding three worksheets. I have tried numerous ways, but the end result is that two of the three get hidden, with the first one being the one that gets skipped.

Here is my code. Please let me know what I am doing wrong. i just downloaded the newest library so I should have the most updated version.

 

Dim fi As FileInfo = New FileInfo(strTemplate)
Dim xl As ExcelPackage = New ExcelPackage(fi)

Dim ws As ExcelWorksheet

For i = 2 To xl.Workbook.Worksheets.Count

ws = xl.Workbook.Worksheets(i)
ws.Hidden = eWorkSheetHidden.Hidden

Next

Editor
Oct 4, 2012 at 9:24 PM

Excel doesn't allow you to hide all yours sheets.

Oct 5, 2012 at 5:43 PM

Hi everyone.

 

mrxrsd, In the code above, I wasn't hiding all, just the second through the last sheet.

So I fixed the issue, although I don't know why.

I had saved my template with the active sheet being one of the sheets i needed hidden. I went back to my sheet, moved the the first worksheet and saved. Magic. Again, not sure why this worked, I would think that EPPlus would be able to hide any sheet as long as not all of the sheets are being hidden.

 

Hope this helps someone else.

Oct 5, 2012 at 5:44 PM

Hi everyone.

 

mrxrsd, In the code above, I wasn't hiding all, just the second through the last sheet.

So I fixed the issue, although I don't know why.

I had saved my template with the active sheet being one of the sheets i needed hidden. I went back to my sheet, moved the the first worksheet and saved. Magic. Again, not sure why this worked, I would think that EPPlus would be able to hide any sheet as long as not all of the sheets are being hidden.

 

Hope this helps someone else.

Oct 5, 2012 at 6:52 PM

I had the same issue.  I had two worksheet and I was trying to hide the first worksheet.  This was resolved when I change the worksheet order.  Sheet 1 becomes Sheet 2 and Sheet 2 to Sheet 1 and I set Sheet 2 to hidden.