1

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.

wiwit
  • 73
  • 1
  • 7
  • 26

3 Answers3

2

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

3DPrintScanner
  • 914
  • 8
  • 7
  • thanks for the answering i already test sheet protection but. sheet protection is creating a lock for the sheet. i need to encrypt the whole excel file – wiwit Oct 24 '20 at 05:59
  • Perhaps look at writing the file into a password protected ZIP file? https://www.rubydoc.info/github/rubyzip/rubyzip#password-protection-experimental – 3DPrintScanner Oct 24 '20 at 11:32
  • thats will be my second option if i not achieve this. – wiwit Oct 24 '20 at 14:19
1

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

zhisme
  • 2,368
  • 2
  • 19
  • 28
  • look at this bro i have another question. https://stackoverflow.com/questions/64574415/add-password-in-xlsx-using-secure-spreadsheet – wiwit Oct 29 '20 at 09:29
1

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

ARtoriouS
  • 73
  • 5