to_array.rb(roo)

This Ruby script utilizes the roo library to read data from an Excel file and store it in a two-dimensional array. Here’s what it does:

  1. Require ‘roo’:
    • This line imports the roo library into your Ruby script, enabling you to work with Excel files.
  2. Initialize an Empty Array:
    • two_dimensional = []: This line initializes an empty array called two_dimensional, which will store the data read from the Excel sheet.
  3. Open Excel File and Select Sheet:
    • xlsx = Roo::Excelx.new('./book.xlsx'): This line creates a new instance of Roo::Excelx, specifying the Excel file book.xlsx to work with.
    • sh1 = xlsx.sheet('mysheet'): This line selects a specific sheet named 'mysheet' from the Excel file and assigns it to the variable sh1.
  4. Iterate Through Each Row:
    • sh1.each do |row|: This loop iterates through each row in the selected sheet ('mysheet').
    • two_dimensional << row: Within the loop, each row is appended to the two_dimensional array, effectively creating a two-dimensional array where each element represents a row in the Excel sheet.
  5. Return the Two-Dimensional Array:
    • two_dimensional: Finally, the script returns the two-dimensional array containing the data read from the Excel sheet.

This script provides a straightforward way to read data from an Excel file into a two-dimensional array in Ruby, making it accessible for further processing or manipulation within your script.

Execution:

require 'roo'
#=> true

two_dimensional = []
#=> []
xlsx = Roo::Excelx.new('./book.xlsx')
#=> <#Roo::Excelx:3600 @tmpdir @shared @filename @sheet_files @sheet_names @...
sh1 = xlsx.sheet('mysheet')
#=> <#Roo::Excelx:3600 @tmpdir @shared @filename @sheet_files @sheet_names @...
sh1.each do |row|
  two_dimensional << row
end
#=> 1
two_dimensional
#=> 
[["key", "value"],
 [1.0, "a"],
 [2.0, "b"],
 [3.0, "c"],
 [4.0, "d"],
 [5.0, "e"],
 [6.0, "f"]]

Executed with Ruby 3.3.6