8

I'm generating an excel template and I'm using 4 sheets, where 3 are just being used to store data from an array. These data are being used in a data validation list, so I added protection to the 3 sheets I use only to store data, but I'd like to know if it's possible to hide them, so that the user, when downloading the template, won't be able to see these sheets he doesn't need to know that exist.

Is it possible?

sergioviniciuss
  • 4,596
  • 3
  • 36
  • 50

1 Answers1

12
$objPHPExcel->getSheetByName('Worksheet 1')
    ->setSheetState(PHPExcel_Worksheet::SHEETSTATE_HIDDEN);

or

$objPHPExcel->getSheetByName('Worksheet 1')
    ->setSheetState(PHPExcel_Worksheet::SHEETSTATE_VERYHIDDEN);

EDIT

You can use Excel's Format/Sheet/Hide to hide an entire worksheet. This sets the worksheet's visible property to xlSheetHidden. But unless you password-protect the workbook structure, anyone can select Format/Sheet/Unhide to see the hidden sheet.

If you use Excel 97 or later, then you can "very hide" a sheet:

  • Press Alt-F11 to display the Visual Basic Editor
  • in the Project window, double-click Microsoft Excel Objects and select the sheet to hide.
  • Press F4 to display the Property box
  • Set the sheet's Visible property to xlSheetVeryHidden.

Now the sheet is no longer accessible via Format/Sheet/Unhide

This is what PHPExcel does rather more simply when you set SheetState to VERYHIDDEN

Mark Baker
  • 209,507
  • 32
  • 346
  • 385
  • what's the difference between hidden and veryhidden state? – sergioviniciuss Aug 21 '12 at 16:04
  • 1
    I just found the difference in the documentation, @Mark Baker! The very hidden state doesn't allow the user to set the sheet visible via excel user interface. Thanks again! – sergioviniciuss Aug 21 '12 at 16:09
  • 1
    @MarkBaker: Using `$objPHPExcel->getSheetByName('Worksheet 1')->setSheetState(PHPExcel_Worksheet::SHEETSTATE_HIDDEN);` on Excel2007 OR either of the above commands on Excel5, hides the sheet, but does not update sheet tab positioning. When opened in Excel, though the sheet tabs appear properly at the bottom of the window (and the hidden sheet is hidden), the clickable area for the visible sheet tabs is offset by the hidden sheet tab width (if the hidden sheet occurs before the visible sheet you are trying to click on by index position). Does my explanation make sense? Any known fix? – IIIOXIII Oct 18 '13 at 07:52
  • 1
    @MarkBaker, As a workaround, I just moved the sheet to the end prior to saving using `$objPHPExcel->setIndexByName('sheetName', 0);`. In any case, I figured you would want to know about the bug. – IIIOXIII Oct 18 '13 at 08:00