How can I create new spreadsheet worksheets in Ruby using the Spreadsheet gem?

Question

Specifically what I am trying to do is add new worksheets alongside ones already there. I've tried to use book.create_worksheet :name => 'new_sheet' but it overwrites the previous worksheet.

I searched the site here and saw some people using a different gem that allowed "book.add_worksheet" (the Spreadsheet gem is supposed to have support for other gems, like it's supposed to be like 3 gems in 1 or something...) and that almost worked as well but I get the error undefined method 'workbook=' for "new_sheet":String (NoMethodError) when doing the line sheet = book.add_worksheet("new_sheet").

Another thing I tried was sheet = Spreadsheet::Worksheet.new and I see on the Spreadsheet rubyforge page that there is the public class method new(opts={}) which, if you click to see the code, includes the line @name = opts[:name] || Worksheet which leads me to believe I should be able to use this to create and name a new worksheet, but I can't figure out the correct syntax.

Is what I am attempting possible? It seems like I'm getting close but I'm not quite hitting it.

Answer 1

book.create_worksheet(:name => 'unique_name') works for me!

book = Spreadsheet::Workbook.new
sheet1 = book.create_worksheet(:name => 'AAA')
sheet2 = book.create_worksheet(:name => 'BBB')


sheet1.row(0).concat %w{Name Country Acknowlegement}
sheet1[1,0] = 'Japan'
row = sheet1.row(1)
row.push 'Creator of Ruby'
row.unshift 'Yukihiro Matsumoto'
sheet1.row(2).replace [ 'Daniel J. Berger', 'U.S.A.',
                  'Author of original code for Spreadsheet::Excel' ]
sheet1.row(3).push 'Charles Lowe', 'Author of the ruby-ole Library'
sheet1.row(3).insert 1, 'Unknown'
sheet1.update_row 4, 'Hannes Wyss', 'Switzerland', 'Author'

sheet2.row(0).concat %w{NAME COUNYRY ACK}
sheet2[1,0] = 'JAPAN'
row = sheet2.row(1)
row.push 'CREATOR OF RUBY'
row.unshift 'YUKIHIRO MATSUMOTO'
sheet2.row(2).replace [ 'DANIEL J. BERGER', 'U.S.A.',
                  'AUTHOR OF ORIGINAL CODE FOR  Spreadsheet::Excel' ]
sheet2.row(3).push 'CHARLES LOWE', 'AUTHOR OF THE RUBY-OLE LIBRARY'
sheet2.row(3).insert 1, 'UNKNOWN'
sheet2.update_row 4, 'HANNES WYSS', 'SWITZERLAND', 'AUTHOR'

book.write '/Users/stephen/tmp/test2.xls'

The above, lifted pretty much verbatim from the documentation works a treat.