How to add data from array of array into cell using Ruby Spreadsheet gem

Question

I have an array of arrays like:

arr_all = [arr_1, arr_2, arr_3, arr_r]

where:

arr_1 = [2015-08-19 17:30:24 -0700, 2015-08-19 17:30:34 -0700, 2015-08-19 17:30:55 -0700]
arr_2 = ...
arr_3 = ...

I have a file to modify. I know how to add an array as a row, but I need help to insert each of the arrays in @@ar_data as columns. I find the Row to insert the data, and then I want to insert arr_1 in the cell (next_empty_row, B), then arr_2 at (next_empty_row, C), etc. Please advice. The Number of rows to fill the data is the size of each array. arr_1, arr_2, arr_3 are of size 3.

def performance_report
  Spreadsheet.client_encoding = 'UTF-8'
  f = "PerformanceTest_S.xls"
  if File.exist? (f)
    # Open the previously created Workbook
    book = Spreadsheet.open(f)
    sheet_1_row_index = book.worksheet(0).last_row_index + 1
    sheet_2_row_index = book.worksheet(1).last_row_index + 1
    # Indicate the row index to the user
    print "Inserting new row at index: #{sheet_2_row_index}\n"
    # Insert array as column - I need help with the below code to insert data in arr_data which is array of arrays. 
    column = 1
    row
    @@ar_data.each do |time|
      len = time.size
      book.worksheet(0).cell(sheet_1_row_index, )
      book.worksheet(0).Column.size
    end
    # This insert row is for worksheet 2 and works fine.
    book.worksheet(1).insert_row(sheet_2_row_index, @@ar_calc)
    # Delete the file so that it can be re-written
    File.delete(f)
    # puts @@ar_calc
    # Write out the Workbook again
    book.write(f)

Answer 1

I don't even know what you're talking about, but what you should probably do is convert that xls to csvs (one for each sheet), and parse it something like this. I'll use a hash to make it platform-independent (but normally I just directly add spreadsheet data to a database using rails):

require 'csv' #not necessary in newer versions of ruby
rows = CSV.open("filename.csv").read
column_names = rows.shift
records = []
rows.each do |row|
  this_record = {}
  column_names.each_with_index do |col, i|
    this_record[col] = row[i]
  end
  records << this_record
end

If you don't want to manually convert each sheet into CSV, what you could do is use the Spreadsheet gem or something like it to convert each sheet into an array of arrays and that's basically a CSV file right there.

In ruby, Hashes inherit from the Enumerable class just like Arrays do. So to convert your hash into an array of tuples (two-element arrays with key and value for each), you'd just have to do this:

records = records.map(&:to_a)

But that's not even necessary, you can directly iterate on and simultaneously assign on hashes just like you can with an array of arrays

records.each_with_index do |hsh, i|
  hsh.each do |k,v|
    puts "record #{i}: #{k}='#{v}'"
  end
end

Answer 2

Am not really sure what you tried to accomplish. This is an example of how you can write your arrays to the end of your file.

require 'spreadsheet'

arrays = [
['Text 1','Text 2','Text 3'],
['Text 4','Text 5','Text 6'],
['Text 7','Text 8','Text 9']]

f = '/your/file/path.xls'
Spreadsheet.client_encoding = 'UTF-8'
if File.exist? f
    book = Spreadsheet.open(f)
    sheet = book.worksheet(0)

    lastRow = sheet.last_row_index + 1
    arrays.each_with_index do |row, rowNum|
          row.each_with_index do |cell, cellNum|
              sheet[ rowNum + lastRow, cellNum ] = cell
          end
    end
    File.delete f
    book.write f
end