Add password in xlsx using secure-spreadsheet

Question

im try to put password in my excel file

def excel_file
  

    test = Axlsx::Package.new do |p|

      p.workbook.add_worksheet(:name => "Pie Chart") do |sheet|
        sheet.add_row ["Simple Pie Chart"]
        %w(first second third).each { |label| sheet.add_row [label, rand(24)+1] }
        sheet.add_chart(Axlsx::Pie3DChart, :start_at => [0,5], :end_at => [10, 20], :title => "example 3: Pie Chart") do |chart|
          chart.add_series :data => sheet["B2:B4"], :labels => sheet["A2:A4"],  :colors => ['FF0000', '00FF00', '0000FF']
        end
      end
      p.serialize('simple.xlsx')
    end


   IO.popen("secure-spreadsheet --password secret", "r+") do |io|
    io.write(test)
    io.close_write
    io.read
  end

end

in this code above it will generate excel file with name simple.xlsx in my project file. then i want to put password using secure-spreadsheet.

The "IO.popen" part of the code is. Im not sure is that right code that Im doing. the goal is get the created alxsx file then add a password.

def download_excel
  respond_to do |format|
    format.xlsx { send_data excel_file, type: 'application/xlsx; header=present', disposition: "attachment", filename: "output.xlsx"  }
  end
end

this code above will download in the browser.

here's origin of the question rails http response to Donwload excel file

here's the repo im using

https://github.com/randym/axlsx

https://github.com/ankane/secure-spreadsheet

im not using spreadsheet protection. i dont need that because it only lock the spreadsheet. what i need is a password for the entire excel file

Answer 1

Digging into library and documentation I found an answer.

Protect an existing XLSX

cat input.xlsx | secure-spreadsheet --password secret --input-format xlsx > output.xlsx

What's wrong with the code you provided, it is doesn't take into consideration input-format option and File.write(test) actually writing instance of Axlsx::Package to a file, that's not what you want. You want serialized contents of that Axlsx::Package.

Here is solution

excel_filename = 'simple.xlsx'
test = Axlsx::Package.new do |p|
  p.workbook.add_worksheet(:name => "Pie Chart") do |sheet|
    sheet.add_row ["Simple Pie Chart"]
    %w(first second third).each { |label| sheet.add_row [label, rand(24)+1] }
    sheet.add_chart(Axlsx::Pie3DChart, :start_at => [0,5], :end_at => [10, 20], :title => "example 3: Pie Chart") do |chart|
      chart.add_series :data => sheet["B2:B4"], :labels => sheet["A2:A4"],  :colors => ['FF0000', '00FF00', '0000FF']
    end
  end

  p.serialize(excel_filename)
end

secured = IO.popen("secure-spreadsheet --password secret --input-format xlsx", "r+") do |io|
  io.write(File.read(excel_filename))
  io.close_write
  io.read
end

my_new_secured_file = File.open('secured_sheet.xlsx', 'w') { |f| f.write(secured) }

secured contains your unsecured contains of file(simple.xlsx) and some metadata to tell excel that this new file (my_new_secured_file variable) is actually encrypted and need passphrase in order to open it

so you need to store encrypted data in secured variable (cause it is actually being called and modified by your internal shell) and then write it to a new file which will be secured xlsx spreadsheet

try to open in your terminal

open secured_sheet.xlsx

and you will be prompted to write a password secret

Answer 2

Based on my understanding of axlsx, it is not currently possible to password protect the opening of the spreadsheet.

As a workaround, I placed the generated spreadsheet in a password protected zip file and downloaded that.

This requires the rubyZip gem

Here's code that takes an Axlsx package and puts into to zip file stream, which you can then download or save to a file.

  def zip_and_protect(package, spreadsheet_filename, password)
    buffer = Zip::OutputStream.write_buffer(::StringIO.new(''), Zip::TraditionalEncrypter.new(password)) do |out|
      out.put_next_entry(spreadsheet_filename)
      out.write package.to_stream.read
    end
    buffer.rewind
    buffer
  end