Support extracting of hidden / protected cell properties

[dfherr]

i'm using your gem to validate correct state of xlsx files. I would like to test if certain cells (rows/columns) are correctly hidden or protected. Could you add support for this?

Something like

cell.is_protected?
cell.is_hidden?
row.is_hidden?
column.is_hidden?

having the methods for cells would be sufficient for me, but as hidden is always applied to a whole row or column it might be apropriate to add these too.

[weshatheleopard]

It is already possible to check the cell protected/hidden status though the object model:

# Assuming that the cells/rows/cols are respectively locked in the test file:
doc = RubyXL::Parser.parse('test.xlsx')
sheet = doc.worksheets[0]
sheet.sheet_data.rows[0].hidden
=> nil
sheet.sheet_data.rows[1].hidden
=> true
(c = sheet.cols.find(0)) && c.hidden
=> nil
(c = sheet.cols.find(1)) && c.hidden
=> true

xf = doc.workbook.cell_xfs[c.style_index || 0]
xf.apply_protection && xf.protection.locked
=> true
xf.apply_protection && xf.protection.hidden
=> true

To be honest I'm at a loss as to whether to implement these "convenience" accessor methods that you propose, since, for one, there will be a lot of them; and for two, they are going to give people the expectation that format is much simpler than it actually is. For example, the "trivial" operation of applying protection to a single cell will actually cause a lot of perturbation since protection is not an attribute of a cell, but an attribute of a style (which, in turn, is an attribute of a cell), which means the entire style table will have to be reshuffled behind the scenes, and if you call that accessor method in a loop, you will slow things down to a crawl.

[dfherr]

i'm fine with the way it works like that. I also saw you already track protected and hidden status, i just didn't find a way to retrieve that information :). So thanks for the fast reply and example. Perhabs put it in the readme or in an additional examples file?

Only thing thats really missleading for me is "xf.apply_protection". I would expect that this method automatically applies (sheet) protection and not returns if it's protected. xf.protected? is what should return the protection status.

The convenience methods I proposed are just for reading that information. Setting protection works through the style object, but getting the information if the cell is protected without accessing the style object would be elegant and leads to cleaner code. It's by the way also like that in the Excel application. You get notified if you access a locked cell, but to apply protection you have to go in the settings tabs.

As hidden is a row/column property it could be really misleading if you can access that information through a cell.

[weshatheleopard]

These methods (apply_protection, etc.) are merely accessors for the structure of OOXML DOM document (see http://www.datypic.com/sc/ooxml/e-ssml_xf-1.html for xf node). As such, they are named just like they are named in OOXML (actually, most of these names are, in fact, auto-generated).

I could certainly create helpers for anything — but do you realize how many of them will be there?

[dfherr]

No, not really :) didn't dig deep enough into OOXML and your structure to estimate that. Another question is, which of them are really interesting and which are not? Or do you mean, just creating helper methods for the protection/locked status requires methods in many files?

well as I said, I'm fine with it beeing possible at all. If there are convience methods or not is not that important.

[weshatheleopard]

I'm saying that people will start asking for all kinds of convenience methods — protection! patterns! colors! 3D scenes! (You will be laughing, but OOXML supports 3D scene definition, complete with lights and cameras: http://www.datypic.com/sc/ooxml/t-a_CT_Scene3D.html) — and that will grow huge fast.

[dfherr]

well thats for you to decide where to draw a line :). I can totally understand if that line is before protection. As far as i see, colors are already implemented :D

[Gauravbtc]

hello @weshatheleopard can we lock any cell using style property of cells if you know can you guide me

[Gauravbtc]

Setting protection works through the style object,

how can it possible can you gave me it's syntax

[koenhandekyn]

see also #320