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

I investigated this two months ago and I believe it is not possible with axlsx. I ended up putting the Excel file in a password protected zip file. — dbugger, Oct 28 '20 at 15:35
@DeezNuuts `IO.popen("secure-spreadsheet --password secret", "r+") do |io|` doesn't work for you? or don't work like you want it to? — zhisme, Oct 29 '20 at 15:13
I just checked this https://github.com/ankane/secure-spreadsheet#ruby example and it encrypts whole file with password — zhisme, Oct 29 '20 at 15:16
yes it encrypts the whole file but I don't know how to integrate using axlsx — wiwit, Oct 30 '20 at 04:03
ok, I think it needs to be wrapped in file before sending in Rails. I will investigate and post answer if I succeed — zhisme, Oct 30 '20 at 08:58
https://stackoverflow.com/a/64531785/5347939 I just checked this answer solves your problem, after trying to open this output.xlsx file it asks for password — zhisme, Oct 30 '20 at 13:19
or you need outside this rails code, you want to write a script that will work the same as above? @DeezNuuts — zhisme, Oct 30 '20 at 13:20
@zhisme sorry what do you mean by that. for now IO.open. works well. but i dont know how to get the file(execel file that created in axlsx gem) to put the password using secure-spreadsheet — wiwit, Nov 01 '20 at 05:23
@DeezNuuts I'm not sure that I get an idea what you want to do. The code you shared in question is working for Rails and downloading in browser, that's correct? But you want it to work outside of rails, e.g. in simple ruby script, and as a result you want it to be saved in simple xlsx file? — zhisme, Nov 03 '20 at 14:22
@zhisme really? you can open simple.xlsx with password secret? — wiwit, Nov 04 '20 at 13:49
@DeezNuuts yes, I did little fixes to your code and it is working. I will post an answer — zhisme, Nov 04 '20 at 15:09

score 3 · Accepted Answer · answered Nov 04 '20 at 15:19

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

dbugger · Answer 2 · 2020-10-28T18:04:41.523

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

Add password in xlsx using secure-spreadsheet

2 Answers2

Linked