Creating large number of worksheets in one Workbook using a loop

21 views
Skip to first unread message

McNels Sylvestre Philippe Auguste

unread,
Nov 8, 2017, 12:12:16 PM11/8/17
to spreadsheet
I am writing a script that uses Spreadsheet because I need to output data in an excel document. More specifically, I want to create one workbook with multiple worksheets within it. So, I used this approach:

Spreadsheet.client_encoding = 'UTF-8'
book = Spreadsheet::Workbook.new
book.worksheets.each do |sheet| 
   sheet.each do |row|
      # data to be written
   end
end

Unfortunately, this didn't work. Alternatively, I tried this to test it out:

Spreadsheet.client_encoding = 'UTF-8'
book = Spreadsheet::Workbook.new
for i in 0..2
   sheet= book.create_worksheet :name => i
   sheet.row(0).push "data"
end

This didn't work either. So, my question is how do you suggest I do this? As I said before what I'm trying to do is to create one excel document with a fixed and predetermined number of sheets. 
Please let me know if you have any advice on how I can achieve this. 

Thank you

Ernest Ellingson

unread,
Dec 8, 2017, 10:35:15 PM12/8/17
to spreadsheet
This is how I have done it.

require 'rubygems'
require 'date'
require 'spreadsheet'

Spreadsheet.client_encoding = 'UTF-8'
  
csv_files=["flash_20171201.csv","flash_20171204.csv","flash_20171205.csv", "flash_20171206.csv"]
  
have_file=false
book_file="#{Date.today.strftime("%Y%m")}_engie.xls"
book = Spreadsheet::Workbook.new
csv_files.each do |file|
  aIn=File.open(file){|f| f.readlines}
  sname=file.sub(/\.csv/,"")
  sheet1 = book.create_worksheet
  sheet1.name = sname
  aIn.each_with_index do |line, idx|
    a=line.chomp.split(/,/)
    row=sheet1.row(idx)
    row.concat a
  end
end
book.write book_file
Reply all
Reply to author
Forward
0 new messages