File encrypted password in xlsx

Question

Currently, I'm using gem caxlsx for generating my excel'

is there anyone try to file a password before generating the excel file?

the goal here. is the user click the button then some text will pop up to type the password of excel. then the system will generate the excel automatically download in the browser.

Answer 1

Looking through the docs, the gem supports password protecting sheets.

require 'axlsx'

p = Axlsx::Package.new
wb = p.workbook

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

wb.add_worksheet(name: 'Sheet Protection') do |sheet|
  sheet.sheet_protection do |protection|
    protection.password = 'fish'
    protection.auto_filter = false
  end

  sheet.add_row [1, 2, 3], style: unlocked # These cells won't be locked
  sheet.add_row [4, 5, 6]
  sheet.add_row [7, 8, 9]

  # Set up auto filters
  sheet.auto_filter = 'A1:C3'
end

p.serialize 'sheet_protection_example.xlsx'

See https://github.com/caxlsx/caxlsx/blob/master/examples/sheet_protection_example.md

Answer 2

Looking into source code of project, this definitely was not implemented. So the best option is to encrypt zip file as suggested above.

    # Encrypt the package into a CFB using the password provided
    # This is not ready yet
    def encrypt(file_name, password)
      return false
      # moc = MsOffCrypto.new(file_name, password)
      # moc.save
    end

Also I did investigation about vba scripting, and this is available by their macro

Dim password As Variant
password = Application.InputBox("Enter Password", "Password Protected")

Select Case password
    Case Is = False
        'do nothing
    Case Is = "easy"
        Range("A1").Value = "This is secret code"
    Case Else
        MsgBox "Incorrect Password"
End Select

But unfortunately I don't see that it can be possible to execute vba scripts passing it some function of caxlsx gem

Answer 3

As already mentioned caxlsx does not have encryption support yet.

Probably it's not the best workaround, but there is a CLI tool called secure-spreadsheet. You can install it on the server and use in your code like this:

xlsx_string = File.read('sample.xlsx')
password = 'foo'

result, status = Open3.capture2('secure-spreadsheet', '--password', password, '--input-format', 'xlsx', stdin_data: xlsx_string)

The result will be an encrypted xlsx string, then you can write it to a file or send it as a response - it will prompt a user to enter a password