Using gem axlsx, how would I create multiple workbooks in one program?

Question

Using basic axlsx code, I need to create a workbook, close it and create a new workbook within the same program. The workbooks will have generated names and a variable number will be created. At this point, I can create the sheets I need but, given my requirements, I have duplicate sheet names. In any case, I need to create multiple files.

I am working from the basic github example:

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

Richard_G · Accepted Answer · 2015-05-18T18:03:30.727

Running code, simplified, as an example. It uses the to_stream method, but it seems the key is using different packages for each workbook. Using the to_stream method with a block closes the file when its done.

class Unload < ApplicationController
  require 'axlsx'

  package = Axlsx::Package.new
  workbook = package.workbook

  puts "Companies"
  workbook.add_worksheet(name: "Companies") do |sheet|
    sheet.add_row ["Name", "Domain", "Prefix"]
    Company.all.each do |a|
      sheet.add_row [a.name, a.domain, a.prefix]
    end
  end

  stream = package.to_stream()
  File.open('db/unloaded/Joint.xlsx', 'wb') { |f| f.write(stream.read) }
  workbook = nil
  stream   = nil
  package  = nil

  Company.all.each do |c|
    puts "Company is: " << c.name
    ActsAsTenant.with_tenant(c) do
      c.package  = Axlsx::Package.new
      c.workbook = c.package.workbook
      company_name = c.blank? ? "Invalid Company" : c.name

      puts "Associates for #{company_name}"
      c.workbook.add_worksheet(name: "Associates") do |sheet|
        sheet.add_row ["Logon", "Name", "Email", "Cell", "Company"]
        Associate.all.each do |a|
          sheet.add_row [a.logon, a.name, a.email, a.cell, company_name]
        end
      end

      puts "Serializing for #{company_name}"
      stream = c.package.to_stream()
      File.open("db/unloaded/#{company_name}.xlsx", 'wb') { |f| f.write(stream.read) }
      c.workbook = nil
      stream     = nil
      c.package  = nil

    end
  end

end

Using gem axlsx, how would I create multiple workbooks in one program?

1 Answers1