0

I have an excel spreadsheet with 2 columns A and B and the rows of each column 1-10 are numbers. When I try to display the number values. The result is the following:

#<Spreadsheet::Column:0x007fba83810368>
#<Spreadsheet::Column:0x007fba83810d90>
...
...
...

Inside the spreadsheet I have this:

x    y
32  4
402 6
733 4
1   30
2   3128
2   4
1   1
6   2
10  63
90  333

This is how I am iterating through the file:

require 'spreadsheet'

importing = Spreadsheet.open 'file.xls'
book = importing.worksheet 'Sheet1'

book.each do |x, y|
  x = book.column(1)
  y = book.column(2)

  puts x
  puts y
end

How do I make the ruby program output the data in a formatted way? My expected output is the same as the spreadsheet format.

Beast_Code
  • 3,097
  • 12
  • 43
  • 54

1 Answers1

1

Here's a way to do it. The following code is basically the same as suggested in this answer (suggested as a more Ruby-like way to do so) to which I've added the two lines to produce the output format that you want:

puts "x      y"
require 'spreadsheet'
Spreadsheet.open('file.xls') do |book|
  book.worksheet('Sheet1').each do |row|
    break if row[0].nil?
    puts "%6d %6d" % row
  end
end

You'd need to vary the column widths depending on the longest number you'd be handling. I allowed for 6 digits each column (%6d) with 1 space (between the two format specifiers). I also lined up the column headings with the leftmost digit of each left justified number.

Community
  • 1
  • 1
Zhora
  • 549
  • 3
  • 11
  • I added my full code. When i insert 'puts "%6d %6d" % [a,b]' into my do block I received the following error: `%': can't convert Spreadsheet::Column into Integer (TypeError) – Beast_Code Sep 06 '14 at 18:30
  • I replaced my original answer as it was incorrect in assuming your resulting variables held the values and also because I carried over my test variable names and didn't correct them to the correct names. The original question and answer linked to should help explain the code used here. I basically took the code from that answer and added the extra formatting. – Zhora Sep 06 '14 at 21:34