I need to loop through all visible worksheets in a workbook. If I use the worksheets list in the Getproperties for the workbook I get all worksheets and can't see if it's visible or not.
Am I missing an action or is this property not available in the component.I did find a way to set it, but that is not what I need...
Hi @Desmond van de Woestijne,
Workbook_GetProperties lists the workbook's sheets (name and index) but not whether each one is hidden, because visibility belongs to the worksheet itself.
Advanced Excel 2.14.0 adds a Worksheet_GetVisibility action for this. It returns Hidden: 0 = Visible, 1 = Hidden, 2 = VeryHidden (the same values Worksheet_Hide_Show uses).
To process only the visible sheets:
1. Call Workbook_GetProperties.
2. For each item in Worksheets, call Worksheet_SelectByIndex with its Index.
3. Call Worksheet_GetVisibility on that worksheet.
4. Process the sheet only when Hidden = 0.
Hi Desmond,
You're not missing an action: the component lets you set a worksheet's visibility but doesn't expose a getter, and the worksheet list from the workbook properties doesn't include the hidden state. Two ways to get it:
Option 1: read the file structure directly (no custom code) An .xlsx file is a zip archive, and sheet visibility is stored in xl/workbook.xml as an attribute on each sheet element:
xl/workbook.xml
sheet
<sheet name="Data" sheetId="1" r:id="rId1"/> <sheet name="Lookups" sheetId="2" state="hidden" r:id="rId2"/> <sheet name="Config" sheetId="3" state="veryHidden" r:id="rId3"/>
A sheet with no state attribute (or state="visible") is visible. So you can unzip the binary with a Zip component from Forge, read xl/workbook.xml, and loop the sheet nodes with an XML component, keeping the names where state is empty or "visible". Then use those names with the existing Advanced Excel actions.
state
state="visible"
Option 2: add a getter to the extension Advanced Excel is an Integration Studio extension built on EPPlus, where every worksheet has a Hidden property of type eWorkSheetHidden with three values: Visible, Hidden and VeryHidden. If you clone the extension (so Forge updates don't overwrite your change), you can add an action that takes the workbook object, casts it the same way the existing worksheet actions in the source do, loops Workbook.Worksheets, and returns the names where Hidden == eWorkSheetHidden.Visible. Returning the filtered list in one call keeps your OutSystems logic to a single For Each.
Hidden
eWorkSheetHidden
Visible
VeryHidden
Workbook.Worksheets
Hidden == eWorkSheetHidden.Visible
Option 1 is quicker if you just need this once; Option 2 is cleaner if you'll use it in several places. Either way, keep "Hidden" and "VeryHidden" in mind: a very hidden sheet can't be unhidden from Excel's UI, so if your loop is meant to match what users see, both should be excluded.
Hope this helps!
Hi Rashid, option 1 was the approach I took as a workaround.
For now it is a one time thing. If, in the feature, I need it again, I will have a look at option 2. (If it is not already built in off course)
Thanks anyway for responding.
Glad it worked, Desmond! Since option 1 is the approach you used, would you mind marking it as the solution? That helps others with the same question find it quickly.
Hi everyone,
I'm evaluating this and expect to cover this scenario in the next version of Advanced Excel.