How to make a particular row not editable using axlsx gem?

[dkumar431]

We are storing some internal information to a particular row of a excel. Those data are basically for our system reference when some one will upload the excel and we do not want anyone to edit/update the content of that row.

I just want to disable that row, so that no one can edit the content.

[straydogstudio]

Take a look at this example:

https://github.com/randym/axlsx/blob/master/examples/example.rb#L591-L599

See if that provides what you need.

[straydogstudio]

You should be able to reverse the example and set locked true on your reference row.

[dkumar431]

Nope, not working. It still allows the row to be edited.

` Axlsx::Package.new do |package|

  locked =  package.workbook.styles.add_style :locked => true

  package.workbook.add_worksheet(name: "Information") do |sheet|

    sheet.add_row(%w(REQUESTID RELEASEID LOCALE))

    sheet.add_row([ @request_id, @release_id, @requested_locale ], :style => locked )

  end

 package.serialize(@export_file_path)

end `

[straydogstudio]

Axlsx tries to implement the open office xlsx standard, and individual programs may or may not respond correctly. Sometimes OpenOffice will correctly handle what Microsoft Office will not. It may be worth checking in other programs.

The only other idea I have is to place your constants on their own sheet and lock the entire sheet, referring to the constants from there.

You're getting into Axlsx details here that I am not familiar with. I have not used locked cells. It may be worth opening an issue on the Axlsx github page, or on stack overflow. Let me know if there is anything else I can help with.

[dkumar431]

Yes, I am actually using Microsoft Office and most probably the targeted end user will also using Ms Office.

The only other idea I have is to place your constants on their own sheet and lock the entire sheet, referring to the constants from there.

Do you have any idea how to lock the entire sheet? Fortunately, all my constants are in a separate sheet only .

[straydogstudio]

If you follow that same link above, you'll notice this line:

sheet.sheet_protection.password = 'fish'

[straydogstudio]

@dkumar431 Did you ever get this solved?

[dkumar431]

@straydogstudio I was not able to make a particular row readonly, but i was able to make the whole sheet readonly. That worked for my use case. 👍

[straydogstudio]

@dkumar431 Glad to hear it.

[aandresrafael]

@dkumar431 how id you the whole sheet read-only?. Thanks

[MuhammadRizwanMughal]

@straydogstudio How can I lock my whole sheet? with password protected. I have tried many solutions, also go through the gem classes, I have used the sheet_protection for my sheet but it's not working. what I want is when anyone try to open the sheet, it should ask a password?

[straydogstudio]

@MuhammadRizwanMughal I would open an issue on the Caxlsx gem if sheet_protection is not working. Which gem versions are you using?

[littleforest]

In order to lock a column or row, you need to have sheet protection turned on, which automatically locks all the cells. So if you want to just lock one row, you need to turn on sheet protection and unlock the cells you want to edit.

wb = xlsx_package.workbook

s = wb.styles
unlocked = s.add_style locked: false

wb.add_worksheet(name: "Information") do |sheet|
  sheet.add_row(%w(REQUESTID RELEASEID LOCALE)
  sheet.add_row(%w(SOME OTHER ROW FOR GOOD MEASURE)
  sheet.add_row([ @request_id, @release_id, @requested_locale ])

  sheet.row_style 0..1, unlocked
  sheet.sheet_protection do |protection|
    protection.password = 'supersecret'
  end
end

Now you will be able to edit all the rows except the last one, and if you want to edit the last row you would have to unlock the sheet with the password.