使用Ruby读取excel文件的简单例子

本篇文章已经比较老，全新的导入导出处理见 这里

Rails 的 Wiki 上有講到幾個方法可以直接用 Ruby 寫 Excel file：


1. office 2003 之後有支援以 xml 做為儲存格式。只要符合schema 的 XML file 就會被正確解讀成 Excel file。這樣產生出來的文件只有 office 2003 以後才能讀取

2. 用 Jakarta POI  的 Ruby Binding

3. 用 spreadsheet

4. 最後是用 CSV

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