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?
You can use the below steps to hide a worksheet in Excel: Right-click on the sheet tab that you want to hide. Click on Hide. This would instantly hide the worksheet, and you will not see it in the workbook. This setting remains intact when you save the workbook and reopen it again, or send it to some else.
While this method hides the worksheet, it’s extremely easy to unhide these worksheets as well. Here are the steps to unhide a worksheet in Excel: Right-click on any of the existing tabs. Click on Unhide. In the Unhide dialog box, select the sheet you want to unhide. Click OK.
In Excel, you may need to hide all other worksheet tabs and only leave the active sheet displaying, to hide them one by one will not a good choice, here, I can recommend a useful tool- Kutools for Excel, with its Hide Unselected Sheets utility, you can hide all other worksheet except the active one with one click.
With the sheet selected, click on the Properties icon in the toolbar (or use the keyboard shortcut F4). In the Properties pane that opens, select the drop-down in front of the option “Visible”. Select ‘2 – xlSheetVeryHidden’. Close the VB Editor. Now you will notice that the sheet is hidden.
$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:
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With