CODEWITHSUNDEEP
rubyarraysxlsx
khakistone
khakistone

Reputation: 85

I'm using the ruby gem "roo" to read xlsx file, how to return the content of one column as an array?

require 'gchart'
require 'rubygems'
require 'roo'

oo = Excelx.new("datav.xlsx")
oo.default_sheet = oo.sheets.first
2.upto(47) do |line|
  data_a = [oo.cell(line,'B')]
  data_b = [oo.cell(line,'E')]

  chart_a = Gchart.new( :type => 'line',
                        :title => "A",
                        :theme => :keynote,
                        :width => 600,
                        :height => 500,
                        :data => data_a, 
                        :line_colors => 'e0440e',
                        :axis_with_labels => ['x', 'y'], 
                        :axis_range => [[0,50,20], [0,3000,500]],
                        :filename => "tmp/chart_a.png")

  chart_b = Gchart.new( :type => 'line',
                        :title => "B",
                        :theme => :keynote,
                        :width => 600,
                        :height => 500,
                        :data => data_b, 
                        :line_colors => 'e62ae5',
                        :axis_with_labels => ['x', 'y'], 
                        :axis_range => [[0,50,20], [0,3000,500]],
                        :filename => "tmp/chart_b.png")

  # Record file in filesystem
  chart_a.file
  chart_b.file

end

This will get every cell's content of column B and E to be the argument :data alone. How to return it as an array? If roo can't return array, then is there any else gem do this?

Upvotes: 1

Views: 4404

Answers (2)

Greg Benedict
Greg Benedict

Reputation: 219

I needed the row back as a hash to be compatible with the logic I used for FasterCSV. This will give you a hash of the first row as the key and current line as the value.

def row_from_excel(s, line)
  row = {}
  s.first_column.upto(s.last_column) do |col|
    cell_name = s.cell(1, col)
    logger.debug "************* #{col} => #{cell_name} => #{s.cell(line, col)}"
    row[cell_name] = s.cell(line, col)      
  end

  row    
end

s = Excelx.new(path_to_file) # or Excel.new(path_to_file)
2.upto(s.last_row) do |line|
  row = row_from_excel(s, line)
end

Upvotes: 0

Iuri G.
Iuri G.

Reputation: 10630

there is a column method that returns values of a given column as an array. Calling oo.column(2) should return you values for column B. oo.column('B') might work also. haven't tested it.

Upvotes: 1

Related Questions