Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Is it possible to hide an excel sheet using phpexcel?

Tags:

php

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?

like image 591
sergioviniciuss Avatar asked Aug 21 '12 15:08

sergioviniciuss


People also ask

How to hide a worksheet in Excel?

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.

How to unhide a worksheet in Excel Office 365?

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.

How to hide all other worksheet Tabs except the active one?

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.

How do I hide a sheet in VB editor?

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.


1 Answers

$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

like image 160
Mark Baker Avatar answered Sep 21 '22 02:09

Mark Baker