Project: Ruport-util

Revision: 40

Author: wes

Date: 31 Jan 2008 14:07:27

• /trunk/lib/ruport/util/xls_table.rb

  ......	@@ -0,0 +1,136 @@
  	1	require 'ruport'
  	2
  	3	# === Overview
  	4	#
  	5	# This class extends the core class Ruport::Data::Table and adds support for loading Excel
  	6	# spreadsheet files using roo. The idea is to get data from speadsheets that may contain
  	7	# already calculated values entered by non-programmers.
  	8	#
  	9	# Once your data is in a Table object, it can be manipulated
  	10	# to suit your needs, then used to build a report.
  	11	#
  	12	# Copyright (C) 2008, Wes Hays
  	13	# All Rights Reserved.
  	14	#
  	15	class Ruport::Data::Table
  	16
  	17	# === Overview
  	18	#
  	19	# This module provides facilities for creating tables from Excel spreadsheet file (xls).
  	20	#
  	21	module FromXLS
  	22	# Loads a xls file directly into a Table using the roo library.
  	23	#
  	24	# Example:
  	25	#
  	26	# # Load data from an Excel xls file with defaults
  	27	# table = Table.load_xls('myspreadsheet.xls')
  	28	#
  	29	# # do not assume the data has column names - default is false.
  	30	# table = Table.load_xls('myspreadsheet.xls',{:has_column_names => false})
  	31	#
  	32	# # Select sheet - default is the first sheet.
  	33	# table = Table.load_xls('myspreadsheet.xls', {:select_sheet => 1})
  	34	#
  	35	# # Start row - default is the first row. Use this to override where
  	36	# the first row should start.
  	37	# table = Table.load_xls('myspreadsheet.xls', {:start_row => 1})
  	38	#
  	39	def load_xls(xls_file, options={})
  	40	get_table_from_xls_file(xls_file, options)
  	41	end
  	42
  	43	# Creates a Table from an Excel object (from roo library).
  	44	#
  	45	# Example:
  	46	#
  	47	# # parse excel object with defaults.
  	48	# table = Table.parse_xls(excel_object)
  	49	#
  	50	# # do not assume the data has column names.
  	51	# table = Table.parse_xls(excel_object,{:has_column_names => false})
  	52	#
  	53	# # Select sheet - default is the first sheet.
  	54	# table = Table.parse_xls(excel_object, {:select_sheet => 1})
  	55	#
  	56	# # Start row - default is the first row. Use this to override where
  	57	# the first row should start.
  	58	# table = Table.parse_xls('myspreadsheet.xls', {:start_row => 1})
  	59	#
  	60	def parse_xls(xls_object, options={})
  	61	get_table_from_xls(xls_object, options)
  	62	end
  	63
  	64	private
  	65
  	66	def get_table_from_xls_file(xls_file, options) #:nodoc:
  	67	require 'roo'
  	68	oo = Excel.new(xls_file)
  	69	get_table_from_xls(oo, options)
  	70	end
  	71
  	72	def get_table_from_xls(oo, options) #:nodoc:
  	73	options = {:has_column_names => true,
  	74	:select_sheet => oo.sheets.first,
  	75	:start_row => 0}.merge(options)
  	76	oo.default_sheet = options[:select_sheet]
  	77
  	78	options[:start_row] = options[:start_row].to_i + 1 unless options[:start_row].nil?
  	79	start_row = options[:start_row]
  	80
  	81	raise 'start_row must be greater than or equal to zero' if options[:start_row].to_i < 0
  	82
  	83	last_row_index_zero = oo.last_row - 1
  	84	raise "start_row must be less than or equal to #{last_row_index_zero}" if !oo.last_row.nil? and
  	85	(options[:start_row].to_i > oo.last_row)
  	86
  	87	table = self.new(options) do |feeder|
  	88
  	89	if options[:has_column_names] == true
  	90	feeder.data.column_names = oo.row(start_row)
  	91	start_row = start_row + 1
  	92	end
  	93
  	94	unless oo.last_row.nil?
  	95	start_row.upto(oo.last_row) do |row|
  	96	tempArr = []
  	97	1.upto(oo.last_column) do |col|
  	98	tempArr << oo.cell(row,col)
  	99	end
  	100	feeder << tempArr
  	101	end
  	102	end
  	103
  	104	end
  	105
  	106	return table
  	107	end
  	108
  	109	end
  	110
  	111	extend FromXLS
  	112
  	113	end
  	114
  	115
  	116	module Kernel
  	117
  	118	alias :RuportTableMethod2 :Table
  	119
  	120	# Updates the Ruport interface for creating Data::Tables with
  	121	# the ability to pass in a XLS file or Roo Excel object.
  	122	#
  	123	# t = Table("myspreadsheet.xls")
  	124	# t = Table("myspreadsheet.xls", :has_column_names => true)
  	125	def Table(*args,&block)
  	126	table=
  	127	case(args[0])
  	128	when /\.xls/
  	129	Ruport::Data::Table.load_xls(*args)
  	130	else
  	131	RuportTableMethod2(*args,&block)
  	132	end
  	133
  	134	return table
  	135	end
  	136	end

• /trunk/test/test_xls_table.rb

  ......	@@ -0,0 +1,236 @@
  	1	# Copyright (C) 2008, Wes Hays
  	2	# All Rights Reserved.
  	3
  	4	require 'test/helper'
  	5	testcase_requires 'roo'
  	6
  	7	describe 'Ruport::Data::TableFromXLS' do
  	8	before(:each) do
  	9	@xls_file = 'test/samples/people.xls'
  	10	@csv_file = 'test/samples/data.csv'
  	11
  	12	@xls_file_column_names = %w(Name Age DOB)
  	13	# This test will pass once Spreadsheet and Roo support
  	14	# formulas in an excel file.
  	15	# @rows = [ ['Andy', 27.0, Date.parse('01/20/1980')],
  	16	# ['Bob', 26.0, Date.parse('02/11/1981')],
  	17	# ['Charlie', 20.0, Date.parse('03/14/1987')],
  	18	# ['David', 73.0, Date.parse('04/26/1997')] ]
  	19
  	20	# Delete this once Roo supports formulas in an excel file.
  	21	@rows = [ ['Andy', 27.0, Date.parse('01/20/1980')],
  	22	['Bob', 26.0, Date.parse('02/11/1981')],
  	23	['Charlie', 20.0, Date.parse('03/14/1987')],
  	24	['David', nil, Date.parse('04/26/1997')] ]
  	25
  	26
  	27	@xls_file_column_names2 = %w(Name Age Pet_Type)
  	28	# This test will pass once Spreadsheet and Roo support
  	29	# formulas in an excel file.
  	30	# @rows2 = [ ['Tigger', 3.0, 'Cat'],
  	31	# ['Chai', 4.0, 'Dog'],
  	32	# ['Rusky', 6.0, 'Dog'],
  	33	# ['Sam', 13.0, 'Dog'] ]
  	34
  	35	# Delete this once Roo supports formulas in an excel file.
  	36	@xls_file_column_names2 = %w(Name Age Pet_Type)
  	37	@rows2 = [ ['Tigger', 3.0, 'Cat'],
  	38	['Chai', 4.0, 'Dog'],
  	39	['Rusky', 6.0, 'Dog'],
  	40	['Sam', nil, 'Dog'] ]
  	41	end
  	42
  	43	# ==== File check ====
  	44	# Raise error if file is not found
  	45	it "should raise if xls file is not found" do
  	46	lambda do
  	47	Ruport::Data::Table.load_xls('people.xls')
  	48	end.should raise_error
  	49	end
  	50
  	51	# Raise error if file is not found
  	52	it "shouldn't raise if xls file exists" do
  	53	lambda do
  	54	Ruport::Data::Table.load_xls(@xls_file)
  	55	end.should_not raise_error
  	56	end
  	57
  	58
  	59	# ==== Constructor check ====
  	60	it "shouldn't be nil if a xls file is passed" do
  	61	table = Table(@xls_file)
  	62	table.should_not be_nil
  	63	end
  	64
  	65	it "shouldn't be nil if a Excel object is passed" do
  	66	oo = Excel.new(@xls_file)
  	67	oo.default_sheet = oo.sheets.first
  	68	table = Table(oo) # This will be passed to the base Ruport::Data::Table class.
  	69	table.should_not be_nil
  	70	end
  	71
  	72	it "shouldn't be nil if a Ruport::Data::Table parameter is passed" do
  	73	table = Table(@csv_file) # Pass cs file
  	74	table.should_not be_nil
  	75	end
  	76
  	77
  	78	# ==== Constructor check with options params ====
  	79	it "shouldn't be nil if a xls file is passed with options params" do
  	80	table = Table(@xls_file, {:has_column_names => false})
  	81	table.should_not be_nil
  	82	end
  	83
  	84	it "shouldn't be nil if a Excel object is passed with options params using parse_xls method" do
  	85	oo = Excel.new(@xls_file)
  	86	oo.default_sheet = oo.sheets.first
  	87	table = Ruport::Data::Table.parse_xls(oo, {:has_column_names => false})
  	88	table.should_not be_nil
  	89	end
  	90
  	91	it "shouldn't be nil if a Ruport::Data::Table parameter is passed with options params" do
  	92	table = Table(@csv_file, {:has_column_names => false}) # Pass cs file
  	93	table.should_not be_nil
  	94	end
  	95
  	96	it "should raise if start_row is less than zero" do
  	97	lambda do
  	98	Table(@xls_file, {:start_row => -2})
  	99	end.should raise_error
  	100	end
  	101
  	102	it "should raise if start_row is greater than the number of rows (starting at 0) in the spreadsheet" do
  	103	lambda do
  	104	Table(@xls_file, {:start_row => 20})
  	105	end.should raise_error
  	106	end
  	107
  	108
  	109	# ==== Table load check ====
  	110
  	111	# Output:
  	112	# +-----------------------------+
  	113	# | Name | Age | DOB |
  	114	# | Andy | 27.0 | 1980-01-20 |
  	115	# | Bob | 26.0 | 1981-02-11 |
  	116	# | Charlie | 20.0 | 1987-03-14 |
  	117	# | David | 73.0 | 1997-04-26 |
  	118	# +-----------------------------+
  	119	it "table should be valid without column names loaded from xls file starting at the row 1 (index 0) - column names will be data" do
  	120	# Load data from xls file but do not load column headers.
  	121	table = Table(@xls_file, {:has_column_names => false, :start_row => 0})
  	122	table.should_not be_nil
  	123	table.column_names.should == []
  	124
  	125	# Add headers to the first position
  	126	@rows.insert(0, @xls_file_column_names)
  	127
  	128	table.each { |r| r.to_a.should == @rows.shift
  	129	r.attributes.should == [0, 1, 2] }
  	130	end
  	131
  	132	# Output:
  	133	# +-----------------------------+
  	134	# | Bob | 26.0 | 1981-02-11 |
  	135	# | Charlie | 20.0 | 1987-03-14 |
  	136	# | David | 73.0 | 1997-04-26 |
  	137	# +-----------------------------+
  	138	it "table should be valid without column names loaded from xls file starting at row 3 (index 2)" do
  	139	# Load data from xls file but do not load column headers.
  	140	# Will start at Row 3 (index 2): ['Bob', 26.0, Date.parse('02/11/1981')]
  	141	table = Table(@xls_file, {:has_column_names => false, :start_row => 2})
  	142	table.should_not be_nil
  	143	table.column_names.should == []
  	144
  	145	# The header row has not been included yet so don't worry about that one
  	146	# just delete the first row in @rows.
  	147	@rows.delete_at(0) # delete ['Andy', 27.0, Date.parse('01/20/1980')]
  	148
  	149	table.each { |r| r.to_a.should == @rows.shift
  	150	r.attributes.should == [0, 1, 2] }
  	151	end
  	152
  	153	# Output:
  	154	# +-----------------------------+
  	155	# | Name | Age | DOB |
  	156	# | Andy | 27.0 | 1980-01-20 |
  	157	# | Bob | 26.0 | 1981-02-11 |
  	158	# | Charlie | 20.0 | 1987-03-14 |
  	159	# | David | 73.0 | 1997-04-26 |
  	160	# +-----------------------------+
  	161	it "table should be valid without column names loaded from xls file" do
  	162	# Load data from xls file but do not load column headers.
  	163	table = Table(@xls_file, {:has_column_names => false})
  	164	table.should_not be_nil
  	165	table.column_names.should == []
  	166
  	167	# Add headers to the first position
  	168	@rows.insert(0, @xls_file_column_names)
  	169
  	170	table.each { |r| r.to_a.should == @rows.shift
  	171	r.attributes.should == [0, 1, 2] }
  	172	end
  	173
  	174	# Output:
  	175	# +-----------------------------+
  	176	# | Name | Age | DOB |
  	177	# +-----------------------------+
  	178	# | Andy | 27.0 | 1980-01-20 |
  	179	# | Bob | 26.0 | 1981-02-11 |
  	180	# | Charlie | 20.0 | 1987-03-14 |
  	181	# | David | 73.0 | 1997-04-26 |
  	182	# +-----------------------------+
  	183	it "table should be valid with column names loaded from xls file" do
  	184	# Load data from xls file but do not load column headers.
  	185	table = Table(@xls_file)
  	186	table.should_not be_nil
  	187	table.column_names.should == @xls_file_column_names
  	188
  	189	table.each { |r| r.to_a.should == @rows.shift
  	190	r.attributes.should == @xls_file_column_names }
  	191	end
  	192
  	193	# Output:
  	194	# +--------------------------+
  	195	# | Name | Age | Pet_Type |
  	196	# +--------------------------+
  	197	# | Tigger | 3.0 | Cat |
  	198	# | Chai | 4.0 | Dog |
  	199	# | Rusky | 6.0 | Dog |
  	200	# | Sam | 13.0 | Dog |
  	201	# +--------------------------+
  	202	it "table should be valid with column names loaded from xls file using Sheet2" do
  	203	# Load data from xls file but do not load column headers.
  	204	table = Table(@xls_file, {:select_sheet => 'Sheet2'})
  	205	table.should_not be_nil
  	206	table.column_names.should == @xls_file_column_names2
  	207
  	208	table.each { |r| r.to_a.should == @rows2.shift
  	209	r.attributes.should == @xls_file_column_names2 }
  	210	end
  	211
  	212	# Output:
  	213	# +--------------------------+
  	214	# | Name | Age | Pet_Type |
  	215	# +--------------------------+
  	216	# | Tigger | 3.0 | Cat |
  	217	# | Chai | 4.0 | Dog |
  	218	# | Rusky | 6.0 | Dog |
  	219	# | Sam | 13.0 | Dog |
  	220	# +--------------------------+
  	221	it "should be valid if an Excel object is passed using parse_xls method" do
  	222	oo = Excel.new(@xls_file)
  	223	oo.default_sheet = oo.sheets.first
  	224	table = Ruport::Data::Table.parse_xls(oo)
  	225	table.should_not be_nil
  	226
  	227	table.column_names.should == @xls_file_column_names
  	228
  	229	table.each { |r| r.to_a.should == @rows.shift
  	230	r.attributes.should == @xls_file_column_names }
  	231	end
  	232
  	233	end
  	234
  	235
  	236

• /trunk/lib/ruport/util.rb

  ......	@@ -22,3 +22,4 @@
  22	22	require "ruport/util/query"
  23	23	require "ruport/util/xls"
  24	24	require "ruport/util/ods_table"
  	25	require "ruport/util/xls_table"

• /trunk/test/samples/people.xls

  + application/octet-stream