Periback Periback - 18 days ago 10
PHP Question

Is it possible to hide an excel sheet using phpexcel?

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?

Answer
$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

Comments