How can we protect some parts of a sheet using rubyXL?

Question

I'd like the user of my xlsx spreadsheet to edit some parts of a sheet but not the most of it. Or in other words, I'd like only some parts of the sheet to be protected.

I learned how to protect the sheet using rubyXL with the code below:

sheetProtection = RubyXL::WorksheetProtection.new(
    password: hashedPass,
    sheet: true,
    objects: true,
    scenarios: true,
    format_cells: true,
    format_columns: true,
    insert_columns: true,
    delete_columns: true,
    insert_rows: true,
    delete_rows: true
);
wsData = workbook['data'];
wsData.sheet_protection = sheetProtection;

Say, I'd like the user to only edit the cell range C2:C13 of the said sheet.

I can't find the syntax on how to do that from the documentation of the rubyXL nor how to use that documentation (please forgive my ignorance). I'm at a loss when I click any link on the side of that page, because for me, it seems the only friendly one is the main page. Google is not helping. In the above code, I don't know how they get the sheet_protection property of the worksheet as available for use.

In the closest clue I found, I learned that "unprotection" of a cell can be achieved by a cell style. So I tried creating a style and putting that one in a cell, but that one too proved to be difficult due to lack of guides.

In the cell_style.rb of the github repo, I found something about Protection and CellStyle classes.

unprotected = RubyXL::Protection.new(
    locked: false,
    hidden: false
);

unprotecStyle = RubyXL::CellStyle.new(
    name: 'unprotected style'
);

I can't find in the docs how to put them together, or even apply a style on a cell:

wsData[1][2].cell_style = unprotecStyle;
# undefined method `cell_style=' for #<RubyXL::Cell(1,2): "cell-content", datatype="str", style_index=8>

I'm not even sure if I'm on the right track. Please help.

score 2 · Answer 1 · answered Jun 10 '19 at 23:03

Perhaps you figured this out already, but this worked for me...

Given a worksheet and a cell, I did:

worksheet.
  workbook.
  cell_xfs[cell.style_index || 0].
  protection = RubyXL::Protection.new(
    locked: false,
    hidden: false
  )

Then I did:

worksheet.sheet_protection = RubyXL::WorksheetProtection.new(
  sheet:          true,
  objects:        true,
  scenarios:      true,
  format_cells:   true,
  format_columns: true,
  insert_columns: true,
  delete_columns: true,
  insert_rows:    true,
  delete_rows:    true
)

Which protected the entire worksheet except for the cell.

How can we protect some parts of a sheet using rubyXL?

1 Answers1

Linked