Highly recommended gem does exactly what it says. Here’s the code to turn an entire worksheet from an excel doc into a multi-dimensional array:
wb = Spreadsheet::ParseExcel.parse(filename)
rows = wb.worksheet(worksheet).map() { |r| r }.compact
grid = rows.map() { |r| r.map() { |c| c.to_s('latin1')}.compact rescue nil }
Just ‘gem install parseexcel’ (though i installed from source before i realized it was a pre-packaged gem).
I have tried using parseexcel, but I am getting an error “Array is not implemented” Please help me out to resolve this problem.
thanks
-sanajy
Hi Sanjay – i sent you an email. Can you post a sample of the code you used to call the gem?
Hi Chad:
Following is the code (I could not check my mail yesterday). I tried to read the excel file by using two pieces of code but got the same error:
====
require ‘parseexcel’
puts “in the prog”
wbook = Spreadsheet::ParseExcel.parse(‘D:\san_infy\ruby\norm_dist_data.xls’)
## I am getting error at this point
puts “excel file read”
## Added to test ###
#Get the first worksheet
#rows = wbook.worksheet(0).map() { |r| r }.compact
#grid = rows.map() { |r| r.map() { |c| c.to_s(‘latin1′)}.compact rescue nil }
#puts “excel read”
worksheet1=Array.new
worksheet1 = wbook.worksheet(0)
puts “matrix generated”
## the program is not reading in
#cycle over every row
worksheet1.each do |row|
j=0
i=0
puts “in the row”
if row != nil
#cycle over each cell in this row if it’s not an empty row
row.each do |cell|
puts “in the cell”
if cell != nil
#Get the contents of the cell as a string
contents = cell.to_f()
puts “Row: #{j} Cell: #{i}> #{contents}”
end
i = i+1
end
end
j=j+1
end
=====
thanks
-sanjay
somehow i never saw your comment – if you do still need help, let me know and i’ll take a look.
You forgot:
require ‘rubygems’
before
require ‘parseexcel’
I am trying to use this parser. Am I missing some include or something?
require ‘watir’
include Watir
require ‘rubygems’
require ‘parseexcel’
require ‘test/unit’
class TC_recorded
i also get “array is not implemented” but i have
require ‘rubygems’
before
require ‘parseexcel’
def pull(x,y)
require ‘rubygems’
require ‘parseexcel’
wb = Spreadsheet::ParseExcel.parse(‘filepath’)
ws = Array.new
ws = wb.worksheet(13)
stp = Array.new
head = Array.new
stp = ws.row(6)
head = ws.row(7)
end
def pull(x,y)
require ‘rubygems’
require ‘parseexcel’
wb = Spreadsheet::ParseExcel.parse(‘filepath’)
ws = wb.worksheet(13)
stp = Array.new
head = Array.new
stp = ws.row(6)
head = ws.row(7)
end
i get the “array is not implemented” as well
tell me how exactly to fetch data from excel sheets using ruby in watir. pls provide me exact code , pls tell us which methods to include as well
I want to use this to GENERATE an .xls, but I don’t see any to_xls or write_xls or anything like that. Can somebody give me a hint?
require ‘parseexcel’
sheet = Spreadsheet::ParseExcel::Worksheet.new
sheet.add_cell(0, 0, ‘foo’)
# Now write…
#sheet.write(‘test.xls’) ?
#File.open(‘test.xls’, ‘w’) do |f| f.write(sheet.to_xls) end ?
I was able to make the Chad’s example. Here is what I have in my file:
———
require “parseexcel”
puts “Hello World”
wb = Spreadsheet::ParseExcel.parse(“C:/Apps/AppsData/workspace/requestcentral/public/downloads/TravelReportUploadTemplate_Test.xls”)
ws = wb.worksheet(0)
rows = ws.map() { |r| r }.compact
grid = rows.map() { |r| r.map() { |c| c.to_s(‘latin1′)}.compact rescue nil }
grid.each do |g|
p g
end
———
The code is supposed to print all the rows to the console. It tries but it prints ‘nil’ for most of the rows, even though it’s not nil in the actual file.
Bottom line, this code is not ready guys like they mentioned at the source where Chad got it from (Chad gave the link to source in his comment).
I wish someone could make the basic stripping work.
-Sri
BTW, above bug is filed int he bug tracking system. Try to go there from the project page:
http://rubyforge.org/projects/spreadsheet
HI can any get me the code in ruby ,where i need to write my gami ifo into excel sheet format
hai can u guve the code to write the information of gmail into excel format
How to loop through the worksheets if there is more than one? An example will work best for me.
Where is the gem? parseexcel
Unfortunately, the parseexcel gem does not seem to have been updated for a couple of years. (As of October, 2009.) And the latest version only reads Excel 95.
But Excel 2007 no longer offers the option of writing a file into Excel 95 format. So the you’ll run into more and more copies of Excel that can’t create an Excel file for this parser. Sigh…
Note, source git repo for the project is http://scm.ywesee.com/?p=parseexcel;a=summary
Thanks Larry, I was having the same problem with nil rows, but I saved as excel95 format and it solved the problem!
Gruff
Oh… one correction: Excel 2007 does let you save as Excel 5.0/95 … so it’s okay.
updated to exclude nil rows and cells
wb = Spreadsheet::ParseExcel.parse(file)
rows = wb.worksheet(0).map() {|r| r unless r.nil?}.compact
grid = rows.map() {|r| r.map() {|c| c.to_s(‘latin1′) unless c.nil?}.compact rescue nil}