Create multiple sheets that uses same template using rubyxl

Question

I have an excel template which has only one sheet(Sheet1).Is it possible using rubyxl to generate multiple sheets that uses Sheet1 as template in my output file?

Answer 1

I was able to achieve this by following these steps:

1. parse xlsx/xlsm file

workbook = RubyXL::Parser.parse(File.join(Rails.root, "public", "template.xlsm")

2. take worksheet template

template = workbook[0]

3. add a new worksheet to the workbook

worksheet = workbook.add_worksheet("Example")

4. duplicate sheet_data

worksheet.sheet_data = template.sheet_data.dup
worksheet.sheet_data.rows = template.sheet_data.rows.map do |row|
  next unless row
  new_row = row.dup
  new_row.worksheet = worksheet
  new_row.cells = row.cells.map{ |cell| next unless cell; new_cell = cell.dup; new_cell.worksheet = worksheet; new_cell }
  new_row
end

Unfortunately Marshal.dump return error no _dump_data is defined for class Nokogiri::XML::Namespace for sheet_data's cells, so I had to write this dirty workaround.

5. copy everything else you need

worksheet.cols = Marshal.load(Marshal.dump(template.cols))
worksheet.merged_cells = Marshal.load(Marshal.dump(template.merged_cells))

for more attributes inspect template.instance_variables

You can also remove the template

workbook.worksheets.delete(template)

And return workbook with a new worksheet(s) by

send_data workbook.stream.string, filename: "example.xlsm", disposition: "attachment"

...in Your Rails controller

or just save it to file

workbook.write("path/to/desired/Excel/file.xlsx")

Answer 2

template = RubyXL::Parser.parse 'path/to/template.xlsx'
sheet1 = template.worksheets[0]
sheet2 = template.worksheets[0]
#your code manipulating sheet1, sheet2 etc.
return RubyXL::Workbook.new [sheet1, sheet2]