We Heart Code » Reading An Excel File With Ruby

Jump to Comments

This tutorial will cover how to read (or parse) an excel file with ruby. I had to write a script to unpivot some data for a co-worker that saved him hours of time, and I got to write a ruby script, so it was a win-win. Here’s how you can do the same thing.

Installing Parseexcel

Parseexcel is a ruby port of the perl parseexcel module.

It’s installable via a nice gem like so:

gem install parseexcel

That’s that, now remember since it’s a gem library we have to tell our script to use the gem libs when we run the script from the console using the -rubygems switch.

Using Parseexcel

Parseexcel is a very straight forward library, you can’t do too much with it, but it gets the job done.

Getting a Workbook

Spreadsheet::ParseExcel.parse(filenameandpath)

This returns the actual excel file’s workbook, from there we need to determine what worksheet we’re on.

Getting a Worksheet

worksheet = workbook.worksheet(0)

Will return the first worksheet, you could also use the each method on the workbook to iterate over all the worksheets.

Iterating over rows and columns

The worksheet object has a very nice each method that will allow us to iterate over the rows like so

worksheet.each { |row|
  j=0
  i=0
  if row != nil
  row.each { |cell|
    if cell != nil
      contents = cell.to_s(‘latin1’)
      puts “Row: #{j} Cell: #{i} #{contents}”
    end
    i = i+1
  }
  j = j +1
  end
}

Getting Cell Data

  • Getting a String: cell.to_s(‘latin1’)
  • Getting a Float: cell.to_s(‘latin1’)
  • Getting a Int: cell.to_i
  • Getting a Date: cell.date

Getting A Specific Cell

cell = row.at(3) #returns cell at column 3

A basic script for dumping an excel file

require ‘parseexcel’

#Open the excel file passed in from the commandline
workbook = Spreadsheet::ParseExcel.parse(ARGV[0])

#Get the first worksheet
worksheet = workbook.worksheet(0)

#cycle over every row
worksheet.each { |row|
  j=0
  i=0
  if row != nil
  #cycle over each cell in this row if it’s not an empty row
  row.each { |cell|
    if cell != nil
      #Get the contents of the cell as a string
      contents = cell.to_s(‘latin1’)
      puts “Row: #{j} Cell: #{i}> #{contents}”
    end
    i = i+1
  }
  end
}

To run the script, remember to use the -rubygems switch so that you can find the parsexcel library.

ruby -rubygems excelparse.rb myfile.xls
Bookmark to:
Add 'Reading An Excel File With Ruby' to Del.icio.us

Add 'Reading An Excel File With Ruby' to digg

Add 'Reading An Excel File With Ruby' to FURL

Add 'Reading An Excel File With Ruby' to reddit

Add 'Reading An Excel File With Ruby' to Technorati

Add 'Reading An Excel File With Ruby' to Google Bookmarks

43 Comments

Filed under ruby, tutorial

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s