winexcel

[WinExcel] Makes it possible to access MS Excel from Ruby via Excel COM/Win32OLE interface. Enables to read and write from/to Excel files with the use of Excel's API. Requires MS Excel application to be installed.

Install

gem install winexcel

Usage

Basic example script

require 'rubygems'
require 'winexcel'
include WinExcel

#
# Opening existing file and reading all data
file_full_path = File.expand_path(File.join(File.dirname(__FILE__), "fixtures\\a.xls"))
xls = ExcelFile.new(file_full_path)
arrRecords = xls.get2DArray('A1')
puts arrRecords
#
# reading specified data
arrRecords = xls.get2DArray('A1:B2')
puts arrRecords
xls.close
#
# Writing some data to existing file
file_full_path = File.expand_path(File.join(File.dirname(__FILE__), "my_basic_file.xls"))
xls = ExcelFile.new(file_full_path)
arrRecords = []
arrRecords << ['ID', 'NAME', 'NICKNAME']
arrRecords << ['001', 'Fredrick White', 'fred']
arrRecords << ['002', 'Robert Green', 'bob']
xls.write2DArray(arrRecords, 'A1')
xls.save
xls.close
#
# Creating a new file from template
file_full_path = File.expand_path(File.join(File.dirname(__FILE__), "my_2nd_new_file.xls"))
template_full_path = File.expand_path(File.join(File.dirname(__FILE__), "fixtures\\template.xls"))
xls = ExcelFile.new(file_full_path, false, template_full_path)
arrRecords = []
arrRecords << ['Patrick Red', 'xyz', '[email protected]']
arrRecords << ['Martin Blue', 'abc']
xls.append2DArray(arrRecords)
xls.save
xls.close # not necessary as it would be handled by finalize
#
# Close any open file by calling finalize
ExcelFile.finalize # always call finalize at the end of a script

Extended example script

require 'rubygems'
require 'winexcel'
include WinExcel

arrRecords = []

# path to already created empty Excel file that we will write to
file_full_path = File.expand_path(File.join(File.dirname(__FILE__), "my_extended_file.xls"))

xls = ExcelFile.new(file_full_path)

begin

  #### writing data

  # writing array of arrays
  arrRecords << ['ID', 'NAME', 'NICKNAME']
  arrRecords << ['001', 'Fredrick White', 'fred']
  arrRecords << ['002', 'Robert Green', 'bob']
  xls.write2DArray(arrRecords, 'A1')

  # appending array of arrays
  arrRecords.clear
  arrRecords << ['003', 'Patrick Red', 'pati']
  arrRecords << ['004', 'Martin Blue', 'mati']
  xls.append2DArray(arrRecords)

  # lets delete the current sheet for now
  xls.addSheet('hashesSheet')

  # writing array of hashes
  arrRecords.clear
  arrRecords << {'ID'=>'001', 'NAME'=>'Fredrick White', 'NICKNAME'=>'fred'}
  arrRecords << {'ID'=>'002', 'NAME'=>'Robert Green', 'NICKNAME'=>'bob'}
  xls.writeArrayHash(arrRecords, 'A1')

  # appending array of hashes
  arrRecords.clear
  arrRecords << {'ID'=>'003', 'NAME'=>'Patrick Red', 'NICKNAME'=>'pati'}
  arrRecords << {'ID'=>'004', 'NAME'=>'Martin Blue', 'NICKNAME'=>'mati'}
  xls.appendArrayHash(arrRecords)

  # lets delete it to switch to the previous, default sheet
  xls.deleteSheet('hashesSheet')

  #### reading data

  # we can specify a range of data to retrieve
  arrRecords = xls.get2DArray('A1:C3')
  p arrRecords

  # if there is no range argument specified, then all occupied rows will be returned,
  # giving us the whole file contents
  arrRecords = xls.get2DArray()
  p arrRecords

ensure
  xls.save
  xls.close # not necessary as it would be handled by finalize
  # close any open file by calling finalize
  ExcelFile.finalize # finalize it at the end of a script
end

For more examples please look at Cucumber features.

License

Copyright (c) 2011 Kamil Sobieraj, [email protected]

(New BSD License)

New BSD License claims: Redistribution and use in source and binary forms, with or without modification, are permitted provided that the following conditions are met:

  1. Redistributions of source code must retain the above copyright notice, this list of conditions and the following disclaimer.

  2. Redistributions in binary form must reproduce the above copyright notice, this list of conditions and the following disclaimer in the documentation and/or other materials provided with the distribution.

  3. Neither the name of Zend Technologies USA, Inc. nor the names of its contributors may be used to endorse or promote products derived from this software without specific prior written permission.

THIS SOFTWARE IS PROVIDED BY THE AUTHOR ``AS IS'' AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE AUTHOR BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.