How to read excel values using queries in ruby?

Question

I need to read an Excel sheet(.xls) values with Ruby using query. Is there any gems available in ruby to do this? If so please help me on this.

Any tips or advice on this would be great.

Thanks Anto

A google search for 'ruby gem to read excel files' turned up http://stackoverflow.com/questions/3309511/how-do-i-read-the-content-of-an-excel-spreadsheet-using-ruby as its first result. — mcfinnigan, Mar 25 '13 at 11:05
I want to implement queries to get values from excel sheet. IS it possible with spreadsheet gem? — user28, Mar 25 '13 at 11:10

knut · Accepted Answer · 2013-03-25T19:49:24.427

You can use Sequel and OLEDB to read Excel Files:

require 'sequel'
Encoding.default_external = 'utf-8' #needed for umlauts in excel

def read_excel(source)
  source = File.expand_path(source) #Full path needed

  db = Sequel.ado(:conn_string=>"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=#{source};Extended Properties=Excel 8.0;")
  # Excel 2000 (for table names, use a dollar after the sheet name, e.g. Sheet1$)
  p db.test_connection

  dataset = db[:'Tabelle1$']
  p dataset
  dataset.each{|row|
    puts row
  }
end #test_read

read_excel('my_spreadsheet.xls')

You should know the name of the tab (in my example it's Tabelle1)

The 'real' solution here is not Sequel, but the ADO-Interface. I'm not familiar with other ORM, so I may not really help you. But you may check for example active record.

There are hints, how to connect MS-Access or sqlserver via ADO, some use ActiveRecord. If you replace the connection string with the Excel-String in my Sequel example, then you may use other ORMs.

You may also try to read Excel-Data via an ODBC-connection.

Thanks for the quick reply. Can you please explain what does 'Tabelle1' refers?? — user28, Mar 25 '13 at 12:12
Is any other gems are available to get the excel values using queries in ruby? — user28, Mar 25 '13 at 13:21
I extended my answer. In short: Yes I think, there are other possibilities, but I don't know them. — knut, Mar 25 '13 at 19:50

score 0 · Answer 2 · answered Mar 25 '13 at 12:07

Read data from excel file using spreadsheet gem

require 'spreadsheet'
doc = Spreadsheet.open('simple.xls')
sheet = doc.worksheet(0)  # list number, first list is 0 and so on...

val = sheet2[r,c] # read particular cell from list 0, r for row, c for column

Some information is there.
More information on the net, just use Google.

How to read excel values using queries in ruby?

2 Answers2