How to check if Excel cell is hidden?
I want to check if a value appears on an Excel file, but I have to ignore cells that are on a hidden row/column.
Do any of you know how to check if a cell is hidden? I've been searching if it's possible checking that usign both excelConnector and MicrosoftExcel components but find nothing.
Thanks in advance.
***Edited by Moderator: Pooja Gadige to add capability tag***
Accepted Solution
Updated: 16 Aug 2022 10:31 EDT
Pegasystems Inc.
US
@emonfort I don't believe you can hide a cell in Excel. Your requirement though is to ignore hidden columns. The trick here is to get a range object for a particular cell in that column and then collect a range from that range by accessing EntireColumn property (which is a range referring to the entire column). If you are using the MicrosoftExcel connector, you can do the following;
- Use the WorksheetGetRange method to get a range corresponding to a cell in a given column. I use the first row in the cell (which starts with 1).
- For cellStart and cellEnd parameters, you may use the same value (A1 for example).
- Extract a proxy from the range being returned by the method.
- This range will have a property named "EntireColumn".
- Extract a proxy from this object (you may also access it via Explore Component Properties if you prefer). This will be the range for the entire row.
- This range has a "Hidden" property that will return true or false.
Bits In Glass
GB
@emonfort Do you want to achieve this with a manual mapping or automated?
If it is manual, you can use pxGenerateExcelFile and map the pagelist property for the columns needed and leave out the columns which are hidden
If you are automating it, it is better you try java code, as pega cannot determine if a cell or a field is hidden.
Accepted Solution
Updated: 16 Aug 2022 10:31 EDT
Pegasystems Inc.
US
@emonfort I don't believe you can hide a cell in Excel. Your requirement though is to ignore hidden columns. The trick here is to get a range object for a particular cell in that column and then collect a range from that range by accessing EntireColumn property (which is a range referring to the entire column). If you are using the MicrosoftExcel connector, you can do the following;
- Use the WorksheetGetRange method to get a range corresponding to a cell in a given column. I use the first row in the cell (which starts with 1).
- For cellStart and cellEnd parameters, you may use the same value (A1 for example).
- Extract a proxy from the range being returned by the method.
- This range will have a property named "EntireColumn".
- Extract a proxy from this object (you may also access it via Explore Component Properties if you prefer). This will be the range for the entire row.
- This range has a "Hidden" property that will return true or false.
Practia
AR
@ThomasSasnettThank you for the detailed explanation! That solved my problem.