# encoding: utf-8
# damit keine falschen Vermutungen aufkommen: Ich habe religioes rein gar nichts
# mit diesem Bibelbund zu tun, aber die hatten eine ziemlich grosse
# Spreadsheet-Datei mit ca. 3500 Zeilen oeffentlich im Netz, die sich ganz gut
# zum Testen eignete.
#
#--
# these test cases were developed to run under Linux OS, some commands
# (like 'diff') must be changed (or commented out ;-)) if you want to run
# the tests under another OS
#
require 'rubygems'
require 'todonotes'
require 'tmpdir'
require './lib/roo'
#TODO
# Look at formulas in excel - does not work with date/time
# Dump warnings that come from the test to open files
# with the wrong spreadsheet class
#STDERR.reopen "/dev/null","w"
TESTDIR = File.dirname(__FILE__)
TMP_PREFIX = 'oo_*'
# require './' + TESTDIR + '/test_helper.rb'
require TESTDIR + '/test_helper.rb'
#require 'soap/rpc/driver'
require 'fileutils'
require 'timeout'
require 'logger'
$log = Logger.new(File.join(ENV['HOME'],"roo.log"))
#$log.level = Logger::WARN
$log.level = Logger::DEBUG
DISPLAY_LOG = true
DB_LOG = false
if DB_LOG
require 'activerecord'
end
include FileUtils
def running_windows?
# to do
# "besser loesen"
# end
File.exists? "C:\\"
end
if DB_LOG
def activerecord_connect
ActiveRecord::Base.establish_connection(:adapter => "mysql",
:database => "test_runs",
:host => "localhost",
:username => "root",
:socket => "/var/run/mysqld/mysqld.sock")
end
class Testrun < ActiveRecord::Base
end
end
class Test::Unit::TestCase
def key_of(spreadsheetname)
begin
return {
#'formula' => 'rt4Pw1WmjxFtyfrqqy94wPw',
'formula' => 'o10837434939102457526.3022866619437760118',
#"write.me" => 'r6m7HFlUOwst0RTUTuhQ0Ow',
"write.me" => '0AkCuGANLc3jFcHR1NmJiYWhOWnBZME4wUnJ4UWJXZHc',
#'numbers1' => "rYraCzjxTtkxw1NxHJgDU8Q",
'numbers1' => 'o10837434939102457526.4784396906364855777',
#'borders' => "r_nLYMft6uWg_PT9Rc2urXw",
'borders' => "o10837434939102457526.664868920231926255",
#'simple_spreadsheet' => "r3aMMCBCA153TmU_wyIaxfw",
'simple_spreadsheet' => "ptu6bbahNZpYe-L1vEBmgGA",
'testnichtvorhandenBibelbund.ods' => "invalidkeyforanyspreadsheet", # !!! intentionally false key
#"only_one_sheet" => "rqRtkcPJ97nhQ0m9ksDw2rA",
"only_one_sheet" => "o10837434939102457526.762705759906130135",
#'time-test' => 'r2XfDBJMrLPjmuLrPQQrEYw',
'time-test' => 'ptu6bbahNZpYBMhk01UfXSg',
#'datetime' => "r2kQpXWr6xOSUpw9MyXavYg",
'datetime' => "ptu6bbahNZpYQEtZwzL_dZQ",
'whitespace' => "rZyQaoFebVGeHKzjG6e9gRQ",
'matrix' => '0AkCuGANLc3jFdHY3cWtYUkM4bVdadjZ5VGpfTzFEUEE',
}[spreadsheetname]
# 'numbers1' => "o10837434939102457526.4784396906364855777",
# 'borders' => "o10837434939102457526.664868920231926255",
# 'simple_spreadsheet' => "ptu6bbahNZpYe-L1vEBmgGA",
# 'testnichtvorhandenBibelbund.ods' => "invalidkeyforanyspreadsheet", # !!! intentionally false key
# "only_one_sheet" => "o10837434939102457526.762705759906130135",
# "write.me" => 'ptu6bbahNZpY0N0RrxQbWdw&hl',
# 'formula' => 'o10837434939102457526.3022866619437760118',
# 'time-test' => 'ptu6bbahNZpYBMhk01UfXSg',
# 'datetime' => "ptu6bbahNZpYQEtZwzL_dZQ",
rescue
raise "unknown spreadsheetname: #{spreadsheetname}"
end
end
if DB_LOG
if ! (defined?(@connected) and @connected)
activerecord_connect
else
@connected = true
end
end
alias unlogged_run run
def run(result, &block)
t1 = Time.now
#RAILS_DEFAULT_LOGGER.debug "RUNNING #{self.class} #{@method_name} \t#{Time.now.to_s}"
if DISPLAY_LOG
v1,v2,v3 = RUBY_VERSION.split('.')
if v1.to_i > 1 or
(v1.to_i == 1 and v2.to_i > 8)
# Ruby 1.9.x
print "RUNNING #{self.class} #{self.__name__} \t#{Time.now.to_s}"
else
# Ruby < 1.9.x
print "RUNNING #{self.class} #{@method_name} \t#{Time.now.to_s}"
end
STDOUT.flush
end
unlogged_run result, &block
t2 = Time.now
if DISPLAY_LOG
puts "\t#{t2-t1} seconds"
end
if DB_LOG
domain = Testrun.create(
:class_name => self.class.to_s,
:test_name => @method_name,
:start => t1,
:duration => t2-t1
)
end
if Dir.glob("oo_*") != []
puts "nicht alle temp. Dateien geloescht"
puts Dir.glob("oo_*")
print "? "
STDOUT.flush
a = gets
end
end
end
class File
def File.delete_if_exist(filename)
if File.exist?(filename)
File.delete(filename)
end
end
end
# :nodoc
class Fixnum
def minutes
self * 60
end
end
class TestRoo < Test::Unit::TestCase
OPENOFFICE = true # do Openoffice-Spreadsheet Tests? (.ods files)
EXCEL = true # do Excel Tests? (.xls files)
GOOGLE = false # do Google-Spreadsheet Tests?
EXCELX = true # do Excelx Tests? (.xlsx files)
LIBREOFFICE = true # do Libreoffice tests? (.ods files)
ONLINE = false
LONG_RUN = true
GLOBAL_TIMEOUT = 48.minutes
def setup
#if DISPLAY_LOG
# puts " GLOBAL_TIMEOUT = #{GLOBAL_TIMEOUT}"
#end
end
def test_internal_minutes
assert_equal 42*60, 42.minutes
end
# call a block of code for each spreadsheet type
# and yield a reference to the roo object
def with_each_spreadsheet(options)
# test if the spreadsheet type is valid :nodoc
if options[:format]
if options[:format].is_a? Symbol
options[:format] = [options[:format]]
end
options[:format].each do |formatname|
unless [:openoffice,:excel,:excelx,:google,:libreoffice].include?(formatname)
raise "invalid spreadsheet type #{formatname}"
end
end
end
# end test spreadsheet type :nodoc
options[:format] ||= [:excel, :excelx, :openoffice, :google]
options[:format] = [options[:format]] if options[:format].class == Symbol
yield Roo::Spreadsheet.open(File.join(TESTDIR, options[:name] + '.xls')) if EXCEL && options[:format].include?(:excel)
yield Roo::Spreadsheet.open(File.join(TESTDIR, options[:name] + '.xlsx')) if EXCELX && options[:format].include?(:excelx)
yield Roo::Spreadsheet.open(File.join(TESTDIR, options[:name] + '.ods')) if OPENOFFICE && options[:format].include?(:openoffice)
yield Roo::Spreadsheet.open(key_of(options[:name]) || options[:name]) if GOOGLE && options[:format].include?(:google)
yield Roo::Spreadsheet.open(File.join(TESTDIR, options[:name] + '.ods')) if LIBREOFFICE && options[:format].include?(:libreoffice)
end
# Using Date.strptime so check that it's using the method
# with the value set in date_format
def test_date
with_each_spreadsheet(:name=>'numbers1', :format=>:google) do |oo|
# should default to DDMMYYYY
assert oo.date?("21/11/1962")
assert !oo.date?("11/21/1962")
oo.date_format = '%m/%d/%Y'
assert !oo.date?("21/11/1962")
assert oo.date?("11/21/1962")
oo.date_format = '%Y-%m-%d'
assert(oo.date?("1962-11-21"))
assert(!oo.date?("1962-21-11"))
end
end
def test_classes
if OPENOFFICE
oo = Openoffice.new(File.join(TESTDIR,"numbers1.ods"))
assert_kind_of Openoffice, oo
end
if EXCEL
oo = Excel.new(File.join(TESTDIR,"numbers1.xls"))
assert_kind_of Excel, oo
end
if GOOGLE
oo = Google.new(key_of("numbers1"))
assert_kind_of Google, oo
end
if EXCELX
oo = Excelx.new(File.join(TESTDIR,"numbers1.xlsx"))
assert_kind_of Excelx, oo
end
end
def test_letters
assert_equal 1, GenericSpreadsheet.letter_to_number('A')
assert_equal 1, GenericSpreadsheet.letter_to_number('a')
assert_equal 2, GenericSpreadsheet.letter_to_number('B')
assert_equal 26, GenericSpreadsheet.letter_to_number('Z')
assert_equal 27, GenericSpreadsheet.letter_to_number('AA')
assert_equal 27, GenericSpreadsheet.letter_to_number('aA')
assert_equal 27, GenericSpreadsheet.letter_to_number('Aa')
assert_equal 27, GenericSpreadsheet.letter_to_number('aa')
end
def test_sheets
with_each_spreadsheet(:name=>'numbers1') do |oo|
assert_equal ["Tabelle1","Name of Sheet 2","Sheet3","Sheet4","Sheet5"], oo.sheets
assert_raise(RangeError) { oo.default_sheet = "no_sheet" }
assert_raise(TypeError) { oo.default_sheet = [1,2,3] }
oo.sheets.each { |sh|
oo.default_sheet = sh
assert_equal sh, oo.default_sheet
}
end
end
def test_cells
with_each_spreadsheet(:name=>'numbers1') do |oo|
# warum ist Auswaehlen erstes sheet hier nicht
# mehr drin?
oo.default_sheet = oo.sheets.first
assert_equal 1, oo.cell(1,1)
assert_equal 2, oo.cell(1,2)
assert_equal 3, oo.cell(1,3)
assert_equal 4, oo.cell(1,4)
assert_equal 5, oo.cell(2,1)
assert_equal 6, oo.cell(2,2)
assert_equal 7, oo.cell(2,3)
assert_equal 8, oo.cell(2,4)
assert_equal 9, oo.cell(2,5)
assert_equal "test", oo.cell(2,6)
assert_equal :string, oo.celltype(2,6)
assert_equal 11, oo.cell(2,7)
assert_equal :float, oo.celltype(2,7)
assert_equal 10, oo.cell(4,1)
assert_equal 11, oo.cell(4,2)
assert_equal 12, oo.cell(4,3)
assert_equal 13, oo.cell(4,4)
assert_equal 14, oo.cell(4,5)
assert_equal 10, oo.cell(4,'A')
assert_equal 11, oo.cell(4,'B')
assert_equal 12, oo.cell(4,'C')
assert_equal 13, oo.cell(4,'D')
assert_equal 14, oo.cell(4,'E')
assert_equal :date, oo.celltype(5,1)
assert_equal Date.new(1961,11,21), oo.cell(5,1)
assert_equal "1961-11-21", oo.cell(5,1).to_s
end
end
def test_celltype
with_each_spreadsheet(:name=>'numbers1') do |oo|
assert_equal :string, oo.celltype(2,6)
end
end
def test_cell_address
with_each_spreadsheet(:name=>'numbers1') do |oo|
assert_equal "tata", oo.cell(6,1)
assert_equal "tata", oo.cell(6,'A')
assert_equal "tata", oo.cell('A',6)
assert_equal "tata", oo.cell(6,'a')
assert_equal "tata", oo.cell('a',6)
assert_raise(ArgumentError) { assert_equal "tata", oo.cell('a','f') }
assert_raise(ArgumentError) { assert_equal "tata", oo.cell('f','a') }
assert_equal "thisisc8", oo.cell(8,3)
assert_equal "thisisc8", oo.cell(8,'C')
assert_equal "thisisc8", oo.cell('C',8)
assert_equal "thisisc8", oo.cell(8,'c')
assert_equal "thisisc8", oo.cell('c',8)
assert_equal "thisisd9", oo.cell('d',9)
assert_equal "thisisa11", oo.cell('a',11)
end
end
def test_office_version
with_each_spreadsheet(:name=>'numbers1', :format=>:openoffice) do |oo|
assert_equal "1.0", oo.officeversion
end
end
def test_libre_office
if LIBREOFFICE
oo = Libreoffice.new("test/numbers1.ods")
oo.default_sheet = oo.sheets.first
assert_equal 41, oo.cell('a',12)
end
end
#TODO: inkonsequente Lieferung Fixnum/Float
def test_rows
with_each_spreadsheet(:name=>'numbers1') do |oo|
oo.default_sheet = oo.sheets.first
assert_equal 41, oo.cell('a',12)
assert_equal 42, oo.cell('b',12)
assert_equal 43, oo.cell('c',12)
assert_equal 44, oo.cell('d',12)
assert_equal 45, oo.cell('e',12)
assert_equal [41.0,42.0,43.0,44.0,45.0, nil, nil], oo.row(12)
assert_equal "einundvierzig", oo.cell('a',16)
assert_equal "zweiundvierzig", oo.cell('b',16)
assert_equal "dreiundvierzig", oo.cell('c',16)
assert_equal "vierundvierzig", oo.cell('d',16)
assert_equal "fuenfundvierzig", oo.cell('e',16)
assert_equal ["einundvierzig", "zweiundvierzig", "dreiundvierzig", "vierundvierzig", "fuenfundvierzig", nil, nil], oo.row(16)
end
end
def test_last_row
with_each_spreadsheet(:name=>'numbers1') do |oo|
oo.default_sheet = oo.sheets.first
assert_equal 18, oo.last_row
end
end
def test_last_column
with_each_spreadsheet(:name=>'numbers1') do |oo|
oo.default_sheet = oo.sheets.first
assert_equal 7, oo.last_column
end
end
def test_last_column_as_letter
with_each_spreadsheet(:name=>'numbers1') do |oo|
oo.default_sheet = oo.sheets.first
assert_equal 'G', oo.last_column_as_letter
end
end
def test_first_row
with_each_spreadsheet(:name=>'numbers1') do |oo|
oo.default_sheet = oo.sheets.first
assert_equal 1, oo.first_row
end
end
def test_first_column
with_each_spreadsheet(:name=>'numbers1') do |oo|
oo.default_sheet = oo.sheets.first
assert_equal 1, oo.first_column
end
end
def test_first_column_as_letter
with_each_spreadsheet(:name=>'numbers1') do |oo|
oo.default_sheet = oo.sheets.first
assert_equal 'A', oo.first_column_as_letter
end
end
def test_sheetname
with_each_spreadsheet(:name=>'numbers1') do |oo|
oo.default_sheet = "Name of Sheet 2"
assert_equal 'I am sheet 2', oo.cell('C',5)
assert_raise(RangeError) { oo.default_sheet = "non existing sheet name" }
assert_raise(RangeError) { oo.default_sheet = "non existing sheet name" }
assert_raise(RangeError) { dummy = oo.cell('C',5,"non existing sheet name")}
assert_raise(RangeError) { dummy = oo.celltype('C',5,"non existing sheet name")}
assert_raise(RangeError) { dummy = oo.empty?('C',5,"non existing sheet name")}
if oo.class == Excel
assert_raise(RuntimeError) { dummy = oo.formula?('C',5,"non existing sheet name")}
assert_raise(RuntimeError) { dummy = oo.formula('C',5,"non existing sheet name")}
else
assert_raise(RangeError) { dummy = oo.formula?('C',5,"non existing sheet name")}
assert_raise(RangeError) { dummy = oo.formula('C',5,"non existing sheet name")}
begin
assert_raise(RangeError) { dummy = oo.set('C',5,42,"non existing sheet name")} unless oo.class == Google
rescue NameError
#
end
assert_raise(RangeError) { dummy = oo.formulas("non existing sheet name")}
end
assert_raise(RangeError) { dummy = oo.to_yaml({},1,1,1,1,"non existing sheet name")}
end
end
def test_boundaries
with_each_spreadsheet(:name=>'numbers1') do |oo|
oo.default_sheet = "Name of Sheet 2"
assert_equal 2, oo.first_column
assert_equal 'B', oo.first_column_as_letter
assert_equal 5, oo.first_row
assert_equal 'E', oo.last_column_as_letter
assert_equal 14, oo.last_row
end
end
def test_multiple_letters
with_each_spreadsheet(:name=>'numbers1') do |oo|
oo.default_sheet = "Sheet3"
assert_equal "i am AA", oo.cell('AA',1)
assert_equal "i am AB", oo.cell('AB',1)
assert_equal "i am BA", oo.cell('BA',1)
assert_equal 'BA', oo.last_column_as_letter
assert_equal "i am BA", oo.cell(1,'BA')
end
end
def test_argument_error
with_each_spreadsheet(:name=>'numbers1') do |oo|
assert_nothing_raised(ArgumentError) { oo.default_sheet = "Tabelle1" }
end
end
def test_empty_eh
with_each_spreadsheet(:name=>'numbers1') do |oo|
assert oo.empty?('a',14)
assert ! oo.empty?('a',15)
assert oo.empty?('a',20)
end
end
def test_reload
with_each_spreadsheet(:name=>'numbers1') do |oo|
oo.default_sheet = oo.sheets.first
assert_equal 1, oo.cell(1,1)
oo.reload
assert_equal 1, oo.cell(1,1)
end
end
def test_bug_contiguous_cells
with_each_spreadsheet(:name=>'numbers1', :format=>:openoffice) do |oo|
oo.default_sheet = "Sheet4"
assert_equal Date.new(2007,06,16), oo.cell('a',1)
assert_equal 10, oo.cell('b',1)
assert_equal 10, oo.cell('c',1)
assert_equal 10, oo.cell('d',1)
assert_equal 10, oo.cell('e',1)
end
end
def test_bug_italo_ve
with_each_spreadsheet(:name=>'numbers1') do |oo|
oo.default_sheet = "Sheet5"
assert_equal 1, oo.cell('A',1)
assert_equal 5, oo.cell('b',1)
assert_equal 5, oo.cell('c',1)
assert_equal 2, oo.cell('a',2)
assert_equal 3, oo.cell('a',3)
end
end
def test_italo_table
with_each_spreadsheet(:name=>'simple_spreadsheet_from_italo', :format=>[:openoffice, :excel]) do |oo|
assert_equal '1', oo.cell('A',1)
assert_equal '1', oo.cell('B',1)
assert_equal '1', oo.cell('C',1)
assert_equal 1, oo.cell('A',2).to_i
assert_equal 2, oo.cell('B',2).to_i
assert_equal 1, oo.cell('C',2).to_i
assert_equal 1, oo.cell('A',3)
assert_equal 3, oo.cell('B',3)
assert_equal 1, oo.cell('C',3)
assert_equal 'A', oo.cell('A',4)
assert_equal 'A', oo.cell('B',4)
assert_equal 'A', oo.cell('C',4)
assert_equal 0.01, oo.cell('A',5)
assert_equal 0.01, oo.cell('B',5)
assert_equal 0.01, oo.cell('C',5)
assert_equal 0.03, oo.cell('a',5)+oo.cell('b',5)+oo.cell('c',5)
# Cells values in row 1:
assert_equal "1:string", oo.cell(1, 1)+":"+oo.celltype(1, 1).to_s
assert_equal "1:string",oo.cell(1, 2)+":"+oo.celltype(1, 2).to_s
assert_equal "1:string",oo.cell(1, 3)+":"+oo.celltype(1, 3).to_s
# Cells values in row 2:
assert_equal "1:string",oo.cell(2, 1)+":"+oo.celltype(2, 1).to_s
assert_equal "2:string",oo.cell(2, 2)+":"+oo.celltype(2, 2).to_s
assert_equal "1:string",oo.cell(2, 3)+":"+oo.celltype(2, 3).to_s
# Cells values in row 3:
assert_equal "1.0:float",oo.cell(3, 1).to_s+":"+oo.celltype(3, 1).to_s
assert_equal "3.0:float",oo.cell(3, 2).to_s+":"+oo.celltype(3, 2).to_s
assert_equal "1.0:float",oo.cell(3, 3).to_s+":"+oo.celltype(3, 3).to_s
# Cells values in row 4:
assert_equal "A:string",oo.cell(4, 1)+":"+oo.celltype(4, 1).to_s
assert_equal "A:string",oo.cell(4, 2)+":"+oo.celltype(4, 2).to_s
assert_equal "A:string",oo.cell(4, 3)+":"+oo.celltype(4, 3).to_s
# Cells values in row 5:
if oo.class == Openoffice
assert_equal "0.01:percentage",oo.cell(5, 1).to_s+":"+oo.celltype(5, 1).to_s
assert_equal "0.01:percentage",oo.cell(5, 2).to_s+":"+oo.celltype(5, 2).to_s
assert_equal "0.01:percentage",oo.cell(5, 3).to_s+":"+oo.celltype(5, 3).to_s
else
assert_equal "0.01:float",oo.cell(5, 1).to_s+":"+oo.celltype(5, 1).to_s
assert_equal "0.01:float",oo.cell(5, 2).to_s+":"+oo.celltype(5, 2).to_s
assert_equal "0.01:float",oo.cell(5, 3).to_s+":"+oo.celltype(5, 3).to_s
end
end
end
def test_formula_openoffice
with_each_spreadsheet(:name=>'formula', :format=>:openoffice) do |oo|
assert_equal 1, oo.cell('A',1)
assert_equal 2, oo.cell('A',2)
assert_equal 3, oo.cell('A',3)
assert_equal 4, oo.cell('A',4)
assert_equal 5, oo.cell('A',5)
assert_equal 6, oo.cell('A',6)
assert_equal 21, oo.cell('A',7)
assert_equal :formula, oo.celltype('A',7)
assert_equal "=[Sheet2.A1]", oo.formula('C',7)
assert_nil oo.formula('A',6)
assert_equal [[7, 1, "=SUM([.A1:.A6])"],
[7, 2, "=SUM([.$A$1:.B6])"],
[7, 3, "=[Sheet2.A1]"],
[8, 2, "=SUM([.$A$1:.B7])"],
], oo.formulas(oo.sheets.first)
# setting a cell
oo.set('A',15, 41)
assert_equal 41, oo.cell('A',15)
oo.set('A',16, "41")
assert_equal "41", oo.cell('A',16)
oo.set('A',17, 42.5)
assert_equal 42.5, oo.cell('A',17)
end
end
def test_formula_google
with_each_spreadsheet(:name=>'formula', :format=>:google) do |oo|
oo.default_sheet = oo.sheets.first
assert_equal 1, oo.cell('A',1)
assert_equal 2, oo.cell('A',2)
assert_equal 3, oo.cell('A',3)
assert_equal 4, oo.cell('A',4)
assert_equal 5, oo.cell('A',5)
assert_equal 6, oo.cell('A',6)
# assert_equal 21, oo.cell('A',7)
assert_equal 21.0, oo.cell('A',7) #TODO: better solution Fixnum/Float
assert_equal :formula, oo.celltype('A',7)
# assert_equal "=[Sheet2.A1]", oo.formula('C',7)
# !!! different from formulas in Openoffice
#was: assert_equal "=sheet2!R[-6]C[-2]", oo.formula('C',7)
# has Google changed their format of formulas/references to other sheets?
assert_equal "=Sheet2!R[-6]C[-2]", oo.formula('C',7)
assert_nil oo.formula('A',6)
# assert_equal [[7, 1, "=SUM([.A1:.A6])"],
# [7, 2, "=SUM([.$A$1:.B6])"],
# [7, 3, "=[Sheet2.A1]"],
# [8, 2, "=SUM([.$A$1:.B7])"],
# ], oo.formulas(oo.sheets.first)
# different format than in openoffice spreadsheets:
#was:
# assert_equal [[7, 1, "=SUM(R[-6]C[0]:R[-1]C[0])"],
# [7, 2, "=SUM(R1C1:R[-1]C[0])"],
# [7, 3, "=sheet2!R[-6]C[-2]"],
# [8, 2, "=SUM(R1C1:R[-1]C[0])"]],
# oo.formulas(oo.sheets.first)
assert_equal [[7, 1, "=SUM(R[-6]C:R[-1]C)"],
[7, 2, "=SUM(R1C1:R[-1]C)"],
[7, 3, "=Sheet2!R[-6]C[-2]"],
[8, 2, "=SUM(R1C1:R[-1]C)"]],
oo.formulas(oo.sheets.first)
end
end
def test_formula_excelx
with_each_spreadsheet(:name=>'formula', :format=>:excelx) do |oo|
assert_equal 1, oo.cell('A',1)
assert_equal 2, oo.cell('A',2)
assert_equal 3, oo.cell('A',3)
assert_equal 4, oo.cell('A',4)
assert_equal 5, oo.cell('A',5)
assert_equal 6, oo.cell('A',6)
assert_equal 21, oo.cell('A',7)
assert_equal :formula, oo.celltype('A',7)
#steht nicht in Datei, oder?
#nein, diesen Bezug habe ich nur in der Openoffice-Datei
#assert_equal "=[Sheet2.A1]", oo.formula('C',7)
assert_nil oo.formula('A',6)
# assert_equal [[7, 1, "=SUM([.A1:.A6])"],
# [7, 2, "=SUM([.$A$1:.B6])"],
#[7, 3, "=[Sheet2.A1]"],
#[8, 2, "=SUM([.$A$1:.B7])"],
#], oo.formulas(oo.sheets.first)
assert_equal [[7, 1, 'SUM(A1:A6)'],
[7, 2, 'SUM($A$1:B6)'],
# [7, 3, "=[Sheet2.A1]"],
# [8, 2, "=SUM([.$A$1:.B7])"],
], oo.formulas(oo.sheets.first)
# setting a cell
oo.set('A',15, 41)
assert_equal 41, oo.cell('A',15)
oo.set('A',16, "41")
assert_equal "41", oo.cell('A',16)
oo.set('A',17, 42.5)
assert_equal 42.5, oo.cell('A',17)
end
end
# Excel can only read the cell's value
def test_formula_excel
with_each_spreadsheet(:name=>'formula', :format=>:excel) do |oo|
assert_equal 21, oo.cell('A',7)
assert_equal 21, oo.cell('B',7)
end
end
def test_borders_sheets
with_each_spreadsheet(:name=>'borders') do |oo|
oo.default_sheet = oo.sheets[1]
assert_equal 6, oo.first_row
assert_equal 11, oo.last_row
assert_equal 4, oo.first_column
assert_equal 8, oo.last_column
oo.default_sheet = oo.sheets.first
assert_equal 5, oo.first_row
assert_equal 10, oo.last_row
assert_equal 3, oo.first_column
assert_equal 7, oo.last_column
oo.default_sheet = oo.sheets[2]
assert_equal 7, oo.first_row
assert_equal 12, oo.last_row
assert_equal 5, oo.first_column
assert_equal 9, oo.last_column
end
end
def yaml_entry(row,col,type,value)
"cell_#{row}_#{col}: \n row: #{row} \n col: #{col} \n celltype: #{type} \n value: #{value} \n"
end
def test_to_yaml
with_each_spreadsheet(:name=>'numbers1') do |oo|
oo.default_sheet = oo.sheets.first
assert_equal "--- \n"+yaml_entry(5,1,"date","1961-11-21"), oo.to_yaml({}, 5,1,5,1)
assert_equal "--- \n"+yaml_entry(8,3,"string","thisisc8"), oo.to_yaml({}, 8,3,8,3)
assert_equal "--- \n"+yaml_entry(12,3,"float",43.0), oo.to_yaml({}, 12,3,12,3)
assert_equal \
"--- \n"+yaml_entry(12,3,"float",43.0) +
yaml_entry(12,4,"float",44.0) +
yaml_entry(12,5,"float",45.0), oo.to_yaml({}, 12,3,12)
assert_equal \
"--- \n"+yaml_entry(12,3,"float",43.0)+
yaml_entry(12,4,"float",44.0)+
yaml_entry(12,5,"float",45.0)+
yaml_entry(15,3,"float",43.0)+
yaml_entry(15,4,"float",44.0)+
yaml_entry(15,5,"float",45.0)+
yaml_entry(16,3,"string","dreiundvierzig")+
yaml_entry(16,4,"string","vierundvierzig")+
yaml_entry(16,5,"string","fuenfundvierzig"), oo.to_yaml({}, 12,3)
end
end
def test_only_one_sheet
with_each_spreadsheet(:name=>'only_one_sheet') do |oo|
assert_equal 42, oo.cell('B',4)
assert_equal 43, oo.cell('C',4)
assert_equal 44, oo.cell('D',4)
oo.default_sheet = oo.sheets.first
assert_equal 42, oo.cell('B',4)
assert_equal 43, oo.cell('C',4)
assert_equal 44, oo.cell('D',4)
end
end
def test_excel_open_from_uri_and_zipped
if EXCEL
if ONLINE
begin
url = 'http://stiny-leonhard.de/bode-v1.xls.zip'
excel = Excel.new(url, :zip)
excel.default_sheet = excel.sheets.first
assert_equal 'ist "e" im Nenner von H(s)', excel.cell('b', 5)
end
end
end
end
def test_openoffice_open_from_uri_and_zipped
if OPENOFFICE
if ONLINE
begin
url = 'http://spazioinwind.libero.it/s2/rata.ods.zip'
sheet = Openoffice.new(url, :zip)
#has been changed: assert_equal 'ist "e" im Nenner von H(s)', sheet.cell('b', 5)
assert_in_delta 0.001, 505.14, sheet.cell('c', 33).to_f
end
end
end
end
def test_excel_zipped
after Date.new(2011,8,30) do
to do
'hier wieder das Problem, dass ausgepacktes xls File
unter Windows nicht geloescht werden kann, weil
das spreadsheet gem die Datei nicht schliesst.
Fehler von spreadsheet gem'
end
if EXCEL
begin
oo = Excel.new(File.join(TESTDIR,"bode-v1.xls.zip"), :zip)
assert oo
assert_equal 'ist "e" im Nenner von H(s)', oo.cell('b', 5)
end
end
end
end
def test_openoffice_zipped
if OPENOFFICE
begin
oo = Openoffice.new(File.join(TESTDIR,"bode-v1.ods.zip"), :zip)
assert oo
assert_equal 'ist "e" im Nenner von H(s)', oo.cell('b', 5)
end
end
end
def test_bug_ric
with_each_spreadsheet(:name=>'ric', :format=>:openoffice) do |oo|
assert oo.empty?('A',1)
assert oo.empty?('B',1)
assert oo.empty?('C',1)
assert oo.empty?('D',1)
expected = 1
letter = 'e'
while letter <= 'u'
assert_equal expected, oo.cell(letter,1)
letter.succ!
expected += 1
end
assert_equal 'J', oo.cell('v',1)
assert_equal 'P', oo.cell('w',1)
assert_equal 'B', oo.cell('x',1)
assert_equal 'All', oo.cell('y',1)
assert_equal 0, oo.cell('a',2)
assert oo.empty?('b',2)
assert oo.empty?('c',2)
assert oo.empty?('d',2)
assert_equal 'B', oo.cell('e',2)
assert_equal 'B', oo.cell('f',2)
assert_equal 'B', oo.cell('g',2)
assert_equal 'B', oo.cell('h',2)
assert_equal 'B', oo.cell('i',2)
assert_equal 'B', oo.cell('j',2)
assert_equal 'B', oo.cell('k',2)
assert_equal 'B', oo.cell('l',2)
assert_equal 'B', oo.cell('m',2)
assert_equal 'B', oo.cell('n',2)
assert_equal 'B', oo.cell('o',2)
assert_equal 'B', oo.cell('p',2)
assert_equal 'B', oo.cell('q',2)
assert_equal 'B', oo.cell('r',2)
assert_equal 'B', oo.cell('s',2)
assert oo.empty?('t',2)
assert oo.empty?('u',2)
assert_equal 0 , oo.cell('v',2)
assert_equal 0 , oo.cell('w',2)
assert_equal 15 , oo.cell('x',2)
assert_equal 15 , oo.cell('y',2)
end
end
def test_mehrteilig
with_each_spreadsheet(:name=>'Bibelbund1', :format=>:openoffice) do |oo|
assert_equal "Tagebuch des Sekret\303\244rs. Letzte Tagung 15./16.11.75 Schweiz", oo.cell(45,'A')
end
#if EXCELX
# #Datei gibt es noch nicht
# oo = Excelx.new(File.join(TESTDIR,"Bibelbund1.xlsx"))
# oo.default_sheet = oo.sheets.first
# assert_equal "Tagebuch des Sekret\303\244rs. Letzte Tagung 15./16.11.75 Schweiz", oo.cell(45,'A')
#end
end
def tempdir
Dir::tmpdir
end
# "/tmp/xxxx" darf man unter Windows nicht verwenden, weil das nicht erkannt
# wird.
# Besser: Methode um temporaeres Dir. portabel zu bestimmen
def test_huge_document_to_csv
if LONG_RUN
with_each_spreadsheet(:name=>'Bibelbund', :format=>[:openoffice,
:excel,
:excelx
# Google hier nicht, weil Google-Spreadsheets nicht so gross werden
# duerfen
]) do |oo|
assert_nothing_raised(Timeout::Error) {
Timeout::timeout(GLOBAL_TIMEOUT) do |timeout_length|
File.delete_if_exist(File.join(tempdir,"Bibelbund.csv"))
assert_equal "Tagebuch des Sekret\303\244rs. Letzte Tagung 15./16.11.75 Schweiz", oo.cell(45,'A')
assert_equal "Tagebuch des Sekret\303\244rs. Nachrichten aus Chile", oo.cell(46,'A')
assert_equal "Tagebuch aus Chile Juli 1977", oo.cell(55,'A')
assert oo.to_csv(File.join(tempdir,"Bibelbund.csv"))
assert File.exists?(File.join(tempdir,"Bibelbund.csv"))
#if running_windows?
# to do
# "diff is not available under windows"
# end
#else
# assert_equal "", `diff test/Bibelbund.csv /tmp/Bibelbund.csv`, "error in class #{oo.class}"
# assert_equal "", `diff test/Bibelbund.csv #{File.join(tempdir,"Bibelbund.csv")}`, "error in class #{oo.class}"
assert_equal "",
diff("test/Bibelbund.csv","#{File.join(tempdir,"Bibelbund.csv")}"),
"error in class #{oo.class}"
#end
end
}
end
end
end
def test_bug_quotes_excelx
if LONG_RUN
with_each_spreadsheet(:name=>'Bibelbund', :format=>[:openoffice,
:excel,
:excelx]) do |oo|
oo.default_sheet = oo.sheets.first
assert_equal 'Einflüsse der neuen Theologie in "de gereformeerde Kerken van Nederland"',
oo.cell('a',76)
dummy = oo.to_csv("csv#{$$}")
assert_equal 'Einflüsse der neuen Theologie in "de gereformeerde Kerken van Nederland"',
oo.cell('a',78)
File.delete_if_exist("csv#{$$}")
end
end
end
def test_to_csv
with_each_spreadsheet(:name=>'numbers1') do |oo|
master = "#{TESTDIR}/numbers1.csv"
File.delete_if_exist(File.join(tempdir,"numbers1.csv"))
assert oo.to_csv(File.join(tempdir,"numbers1.csv"),oo.sheets.first)
assert(File.exists?(File.join(tempdir,"numbers1.csv")), "Datei #{tempdir}/numbers1.csv existiert nicht")
if running_windows?
warn "no 'diff' in windows"
else
assert_equal "", `diff #{master} #{File.join(tempdir,"numbers1.csv")}`
assert oo.to_csv(File.join(tempdir,"numbers1.csv"))
assert File.exists?(File.join(tempdir,"numbers1.csv"))
assert_equal "", `diff #{master} #{File.join(tempdir,"numbers1.csv")}`
end
end
end
def test_bug_mehrere_datum
with_each_spreadsheet(:name=>'numbers1') do |oo|
oo.default_sheet = 'Sheet5'
assert_equal :date, oo.celltype('A',4)
assert_equal :date, oo.celltype('B',4)
assert_equal :date, oo.celltype('C',4)
assert_equal :date, oo.celltype('D',4)
assert_equal :date, oo.celltype('E',4)
assert_equal Date.new(2007,11,21), oo.cell('A',4)
assert_equal Date.new(2007,11,21), oo.cell('B',4)
assert_equal Date.new(2007,11,21), oo.cell('C',4)
assert_equal Date.new(2007,11,21), oo.cell('D',4)
assert_equal Date.new(2007,11,21), oo.cell('E',4)
assert_equal :float, oo.celltype('A',5)
assert_equal :float, oo.celltype('B',5)
assert_equal :float, oo.celltype('C',5)
assert_equal :float, oo.celltype('D',5)
assert_equal :float, oo.celltype('E',5)
assert_equal 42, oo.cell('A',5)
assert_equal 42, oo.cell('B',5)
assert_equal 42, oo.cell('C',5)
assert_equal 42, oo.cell('D',5)
assert_equal 42, oo.cell('E',5)
assert_equal :string, oo.celltype('A',6)
assert_equal :string, oo.celltype('B',6)
assert_equal :string, oo.celltype('C',6)
assert_equal :string, oo.celltype('D',6)
assert_equal :string, oo.celltype('E',6)
assert_equal "ABC", oo.cell('A',6)
assert_equal "ABC", oo.cell('B',6)
assert_equal "ABC", oo.cell('C',6)
assert_equal "ABC", oo.cell('D',6)
assert_equal "ABC", oo.cell('E',6)
end
end
def test_multiple_sheets
with_each_spreadsheet(:name=>'numbers1') do |oo|
2.times do
oo.default_sheet = "Tabelle1"
assert_equal 1, oo.cell(1,1)
assert_equal 1, oo.cell(1,1,"Tabelle1")
assert_equal "I am sheet 2", oo.cell('C',5,"Name of Sheet 2")
sheetname = 'Sheet5'
assert_equal :date, oo.celltype('A',4,sheetname)
assert_equal :date, oo.celltype('B',4,sheetname)
assert_equal :date, oo.celltype('C',4,sheetname)
assert_equal :date, oo.celltype('D',4,sheetname)
assert_equal :date, oo.celltype('E',4,sheetname)
assert_equal Date.new(2007,11,21), oo.cell('A',4,sheetname)
assert_equal Date.new(2007,11,21), oo.cell('B',4,sheetname)
assert_equal Date.new(2007,11,21), oo.cell('C',4,sheetname)
assert_equal Date.new(2007,11,21), oo.cell('D',4,sheetname)
assert_equal Date.new(2007,11,21), oo.cell('E',4,sheetname)
assert_equal :float, oo.celltype('A',5,sheetname)
assert_equal :float, oo.celltype('B',5,sheetname)
assert_equal :float, oo.celltype('C',5,sheetname)
assert_equal :float, oo.celltype('D',5,sheetname)
assert_equal :float, oo.celltype('E',5,sheetname)
assert_equal 42, oo.cell('A',5,sheetname)
assert_equal 42, oo.cell('B',5,sheetname)
assert_equal 42, oo.cell('C',5,sheetname)
assert_equal 42, oo.cell('D',5,sheetname)
assert_equal 42, oo.cell('E',5,sheetname)
assert_equal :string, oo.celltype('A',6,sheetname)
assert_equal :string, oo.celltype('B',6,sheetname)
assert_equal :string, oo.celltype('C',6,sheetname)
assert_equal :string, oo.celltype('D',6,sheetname)
assert_equal :string, oo.celltype('E',6,sheetname)
assert_equal "ABC", oo.cell('A',6,sheetname)
assert_equal "ABC", oo.cell('B',6,sheetname)
assert_equal "ABC", oo.cell('C',6,sheetname)
assert_equal "ABC", oo.cell('D',6,sheetname)
assert_equal "ABC", oo.cell('E',6,sheetname)
oo.reload
end
end
end
def test_bug_empty_sheet
with_each_spreadsheet(:name=>'formula', :format=>[:openoffice, :excelx]) do |oo|
oo.default_sheet = 'Sheet3' # is an empty sheet
assert_nothing_raised() { oo.to_csv(File.join(tempdir,"emptysheet.csv")) }
assert_equal "", `cat #{File.join(tempdir,"emptysheet.csv")}`
end
end
def test_find_by_row_huge_document
if LONG_RUN
with_each_spreadsheet(:name=>'Bibelbund', :format=>[:openoffice,
:excel,
:excelx]) do |oo|
Timeout::timeout(GLOBAL_TIMEOUT) do |timeout_length|
oo.default_sheet = oo.sheets.first
rec = oo.find 20
assert rec
# assert_equal "Brief aus dem Sekretariat", rec[0]
#p rec
assert_equal "Brief aus dem Sekretariat", rec[0]['TITEL']
rec = oo.find 22
assert rec
# assert_equal "Brief aus dem Skretariat. Tagung in Amberg/Opf.",rec[0]
assert_equal "Brief aus dem Skretariat. Tagung in Amberg/Opf.",rec[0]['TITEL']
end
end
end
end
def test_find_by_row
with_each_spreadsheet(:name=>'numbers1') do |oo|
oo.header_line = nil
rec = oo.find 16
assert rec
assert_nil oo.header_line
# keine Headerlines in diesem Beispiel definiert
assert_equal "einundvierzig", rec[0]
#assert_equal false, rec
rec = oo.find 15
assert rec
assert_equal 41,rec[0]
end
end
def test_find_by_conditions
if LONG_RUN
with_each_spreadsheet(:name=>'Bibelbund', :format=>[:openoffice,
:excel,
:excelx]) do |oo|
assert_nothing_raised(Timeout::Error) {
Timeout::timeout(GLOBAL_TIMEOUT) do |timeout_length|
#-----------------------------------------------------------------
zeilen = oo.find(:all, :conditions => {
'TITEL' => 'Brief aus dem Sekretariat'
}
)
assert_equal 2, zeilen.size
assert_equal [{"VERFASSER"=>"Almassy, Annelene von",
"INTERNET"=>nil,
"SEITE"=>316.0,
"KENNUNG"=>"Aus dem Bibelbund",
"OBJEKT"=>"Bibel+Gem",
"PC"=>"#C:\\Bibelbund\\reprint\\BuG1982-3.pdf#",
"NUMMER"=>"1982-3",
"TITEL"=>"Brief aus dem Sekretariat"},
{"VERFASSER"=>"Almassy, Annelene von",
"INTERNET"=>nil,
"SEITE"=>222.0,
"KENNUNG"=>"Aus dem Bibelbund",
"OBJEKT"=>"Bibel+Gem",
"PC"=>"#C:\\Bibelbund\\reprint\\BuG1983-2.pdf#",
"NUMMER"=>"1983-2",
"TITEL"=>"Brief aus dem Sekretariat"}] , zeilen
#----------------------------------------------------------
zeilen = oo.find(:all,
:conditions => { 'VERFASSER' => 'Almassy, Annelene von' }
)
assert_equal 13, zeilen.size
#----------------------------------------------------------
zeilen = oo.find(:all, :conditions => {
'TITEL' => 'Brief aus dem Sekretariat',
'VERFASSER' => 'Almassy, Annelene von',
}
)
assert_equal 2, zeilen.size
assert_equal [{"VERFASSER"=>"Almassy, Annelene von",
"INTERNET"=>nil,
"SEITE"=>316.0,
"KENNUNG"=>"Aus dem Bibelbund",
"OBJEKT"=>"Bibel+Gem",
"PC"=>"#C:\\Bibelbund\\reprint\\BuG1982-3.pdf#",
"NUMMER"=>"1982-3",
"TITEL"=>"Brief aus dem Sekretariat"},
{"VERFASSER"=>"Almassy, Annelene von",
"INTERNET"=>nil,
"SEITE"=>222.0,
"KENNUNG"=>"Aus dem Bibelbund",
"OBJEKT"=>"Bibel+Gem",
"PC"=>"#C:\\Bibelbund\\reprint\\BuG1983-2.pdf#",
"NUMMER"=>"1983-2",
"TITEL"=>"Brief aus dem Sekretariat"}] , zeilen
# Result as an array
zeilen = oo.find(:all,
:conditions => {
'TITEL' => 'Brief aus dem Sekretariat',
'VERFASSER' => 'Almassy, Annelene von',
}, :array => true)
assert_equal 2, zeilen.size
assert_equal [
[
"Brief aus dem Sekretariat",
"Almassy, Annelene von",
"Bibel+Gem",
"1982-3",
316.0,
nil,
"#C:\\Bibelbund\\reprint\\BuG1982-3.pdf#",
"Aus dem Bibelbund",
],
[
"Brief aus dem Sekretariat",
"Almassy, Annelene von",
"Bibel+Gem",
"1983-2",
222.0,
nil,
"#C:\\Bibelbund\\reprint\\BuG1983-2.pdf#",
"Aus dem Bibelbund",
]] , zeilen
end # Timeout
} # nothing_raised
end
end
end
#TODO: temporaerer Test
def test_seiten_als_date
if LONG_RUN
with_each_spreadsheet(:name=>'Bibelbund', :format=>:excelx) do |oo|
assert_equal 'Bericht aus dem Sekretariat', oo.cell(13,1)
assert_equal '1981-4', oo.cell(13,'D')
assert_equal String, oo.excelx_type(13,'E')[1].class
assert_equal [:numeric_or_formula,"General"], oo.excelx_type(13,'E')
assert_equal '428', oo.excelx_value(13,'E')
assert_equal 428.0, oo.cell(13,'E')
end
end
end
def test_column
with_each_spreadsheet(:name=>'numbers1') do |oo|
expected = [1.0,5.0,nil,10.0,Date.new(1961,11,21),'tata',nil,nil,nil,nil,'thisisa11',41.0,nil,nil,41.0,'einundvierzig',nil,Date.new(2007,5,31)]
assert_equal expected, oo.column(1)
assert_equal expected, oo.column('a')
end
end
def test_column_huge_document
if LONG_RUN
with_each_spreadsheet(:name=>'Bibelbund', :format=>[:openoffice,
:excel,
:excelx]) do |oo|
assert_nothing_raised(Timeout::Error) {
Timeout::timeout(GLOBAL_TIMEOUT) do |timeout_length|
oo.default_sheet = oo.sheets.first
assert_equal 3735, oo.column('a').size
#assert_equal 499, oo.column('a').size
end
}
end
end
end
def test_simple_spreadsheet_find_by_condition
with_each_spreadsheet(:name=>'simple_spreadsheet') do |oo|
oo.header_line = 3
# oo.date_format = '%m/%d/%Y' if oo.class == Google
erg = oo.find(:all, :conditions => {'Comment' => 'Task 1'})
assert_equal Date.new(2007,05,07), erg[1]['Date']
assert_equal 10.75 , erg[1]['Start time']
assert_equal 12.50 , erg[1]['End time']
assert_equal 0 , erg[1]['Pause']
assert_equal 1.75 , erg[1]['Sum'] unless oo.class == Excel
assert_equal "Task 1" , erg[1]['Comment']
end
end
# Ruby-spreadsheet now allows us to at least give the current value
# from a cell with a formula (no possible with parseexcel)
def test_bug_false_borders_with_formulas
with_each_spreadsheet(:name=>'false_encoding', :format=>:excel) do |oo|
assert_equal 1, oo.first_row
assert_equal 3, oo.last_row
assert_equal 1, oo.first_column
assert_equal 4, oo.last_column
end
end
# We'ce added minimal formula support so we can now read these
# though not sure how the spreadsheet reports older values....
def test_fe
with_each_spreadsheet(:name=>'false_encoding', :format=>:excel) do |oo|
assert_equal Date.new(2007,11,1), oo.cell('a',1)
#DOES NOT WORK IN EXCEL FILES: assert_equal true, oo.formula?('a',1)
#DOES NOT WORK IN EXCEL FILES: assert_equal '=TODAY()', oo.formula('a',1)
assert_equal Date.new(2008,2,9), oo.cell('B',1)
#DOES NOT WORK IN EXCEL FILES: assert_equal true, oo.formula?('B',1)
#DOES NOT WORK IN EXCEL FILES: assert_equal "=A1+100", oo.formula('B',1)
assert_kind_of DateTime, oo.cell('C',1)
#DOES NOT WORK IN EXCEL FILES: assert_equal true, oo.formula?('C',1)
#DOES NOT WORK IN EXCEL FILES: assert_equal "=C1", oo.formula('C',1)
assert_equal 'H1', oo.cell('A',2)
assert_equal 'H2', oo.cell('B',2)
assert_equal 'H3', oo.cell('C',2)
assert_equal 'H4', oo.cell('D',2)
assert_equal 'R1', oo.cell('A',3)
assert_equal 'R2', oo.cell('B',3)
assert_equal 'R3', oo.cell('C',3)
assert_equal 'R4', oo.cell('D',3)
end
end
def test_excel_does_not_support_formulas
with_each_spreadsheet(:name=>'false_encoding', :format=>:excel) do |oo|
assert_raise(RuntimeError) { void = oo.formula('a',1) }
assert_raise(RuntimeError) { void = oo.formula?('a',1) }
assert_raise(RuntimeError) { void = oo.formulas(oo.sheets.first) }
end
end
def get_extension(oo)
case oo
when Openoffice
".ods"
when Excel
".xls"
when Excelx
".xlsx"
when Google
""
end
end
def test_info
expected_templ = "File: numbers1%s\n"+
"Number of sheets: 5\n"+
"Sheets: Tabelle1, Name of Sheet 2, Sheet3, Sheet4, Sheet5\n"+
"Sheet 1:\n"+
" First row: 1\n"+
" Last row: 18\n"+
" First column: A\n"+
" Last column: G\n"+
"Sheet 2:\n"+
" First row: 5\n"+
" Last row: 14\n"+
" First column: B\n"+
" Last column: E\n"+
"Sheet 3:\n"+
" First row: 1\n"+
" Last row: 1\n"+
" First column: A\n"+
" Last column: BA\n"+
"Sheet 4:\n"+
" First row: 1\n"+
" Last row: 1\n"+
" First column: A\n"+
" Last column: E\n"+
"Sheet 5:\n"+
" First row: 1\n"+
" Last row: 6\n"+
" First column: A\n"+
" Last column: E"
with_each_spreadsheet(:name=>'numbers1') do |oo|
ext = get_extension(oo)
expected = sprintf(expected_templ,ext)
begin
if oo.class == Google
assert_equal expected.gsub(/numbers1/,key_of("numbers1")), oo.info
else
assert_equal expected, oo.info
end
rescue NameError
#
end
end
end
def test_bug_excel_numbers1_sheet5_last_row
with_each_spreadsheet(:name=>'numbers1', :format=>:excel) do |oo|
oo.default_sheet = "Tabelle1"
assert_equal 1, oo.first_row
assert_equal 18, oo.last_row
assert_equal Openoffice.letter_to_number('A'), oo.first_column
assert_equal Openoffice.letter_to_number('G'), oo.last_column
oo.default_sheet = "Name of Sheet 2"
assert_equal 5, oo.first_row
assert_equal 14, oo.last_row
assert_equal Openoffice.letter_to_number('B'), oo.first_column
assert_equal Openoffice.letter_to_number('E'), oo.last_column
oo.default_sheet = "Sheet3"
assert_equal 1, oo.first_row
assert_equal 1, oo.last_row
assert_equal Openoffice.letter_to_number('A'), oo.first_column
assert_equal Openoffice.letter_to_number('BA'), oo.last_column
oo.default_sheet = "Sheet4"
assert_equal 1, oo.first_row
assert_equal 1, oo.last_row
assert_equal Openoffice.letter_to_number('A'), oo.first_column
assert_equal Openoffice.letter_to_number('E'), oo.last_column
oo.default_sheet = "Sheet5"
assert_equal 1, oo.first_row
assert_equal 6, oo.last_row
assert_equal Openoffice.letter_to_number('A'), oo.first_column
assert_equal Openoffice.letter_to_number('E'), oo.last_column
end
end
def test_should_raise_file_not_found_error
if OPENOFFICE
assert_raise(IOError) {
oo = Openoffice.new(File.join('testnichtvorhanden','Bibelbund.ods'))
}
end
if EXCEL
assert_raise(IOError) {
oo = Excel.new(File.join('testnichtvorhanden','Bibelbund.xls'))
}
end
if EXCELX
assert_raise(IOError) {
oo = Excelx.new(File.join('testnichtvorhanden','Bibelbund.xlsx'))
}
end
if GOOGLE
# assert_raise(Net::HTTPServerException) {
# oo = Google.new(key_of('testnichtvorhanden'+'Bibelbund.ods'))
# oo = Google.new('testnichtvorhanden')
# }
end
end
def test_write_google
# write.me: http://spreadsheets.google.com/ccc?key=ptu6bbahNZpY0N0RrxQbWdw&hl=en_GB
with_each_spreadsheet(:name=>'write.me', :format=>:google) do |oo|
oo.default_sheet = oo.sheets.first
oo.set_value(1,1,"hello from the tests")
assert_equal "hello from the tests", oo.cell(1,1)
oo.set_value(1,1, 1.0)
assert_equal 1.0, oo.cell(1,1)
end
end
def test_bug_set_value_with_more_than_one_sheet_google
# write.me: http://spreadsheets.google.com/ccc?key=ptu6bbahNZpY0N0RrxQbWdw&hl=en_GB
after Date.new(2011,6,19) do
with_each_spreadsheet(:name=>'write.me', :format=>:google) do |oo|
content1 = 'AAA'
content2 = 'BBB'
oo.default_sheet = oo.sheets.first
oo.set_value(1,1,content1)
oo.default_sheet = oo.sheets[1]
oo.set_value(1,1,content2) # in the second sheet
oo.default_sheet = oo.sheets.first
assert_equal content1, oo.cell(1,1)
oo.default_sheet = oo.sheets[1]
assert_equal content2, oo.cell(1,1)
end
end
end
def test_set_value_with_sheet_argument_google
with_each_spreadsheet(:name=>'write.me', :format=>:google) do |oo|
random_row = rand(10)+1
random_column = rand(10)+1
content1 = 'ABC'
content2 = 'DEF'
oo.set_value(random_row,random_column,content1,oo.sheets.first)
oo.set_value(random_row,random_column,content2,oo.sheets[1])
assert_equal content1, oo.cell(random_row,random_column,oo.sheets.first)
assert_equal content2, oo.cell(random_row,random_column,oo.sheets[1])
end
end
def test_set_value_for_non_existing_sheet_google
with_each_spreadsheet(:name=>'ptu6bbahNZpY0N0RrxQbWdw', :format=>:google) do |oo|
assert_raise(RangeError) { oo.set_value(1,1,"dummy","no_sheet") }
end
end
def test_bug_bbu
with_each_spreadsheet(:name=>'bbu', :format=>[:openoffice, :excelx, :excel]) do |oo|
assert_nothing_raised() {
assert_equal "File: bbu#{get_extension(oo)}
Number of sheets: 3
Sheets: 2007_12, Tabelle2, Tabelle3
Sheet 1:
First row: 1
Last row: 4
First column: A
Last column: F
Sheet 2:
- empty -
Sheet 3:
- empty -", oo.info
}
oo.default_sheet = oo.sheets[1] # empty sheet
assert_nil oo.first_row
assert_nil oo.last_row
assert_nil oo.first_column
assert_nil oo.last_column
end
end
def test_bug_time_nil
with_each_spreadsheet(:name=>'time-test') do |oo|
assert_equal 12*3600+13*60+14, oo.cell('B',1) # 12:13:14 (secs since midnight)
assert_equal :time, oo.celltype('B',1)
assert_equal 15*3600+16*60, oo.cell('C',1) # 15:16 (secs since midnight)
assert_equal :time, oo.celltype('C',1)
assert_equal 23*3600, oo.cell('D',1) # 23:00 (secs since midnight)
assert_equal :time, oo.celltype('D',1)
end
end
def test_date_time_to_csv
with_each_spreadsheet(:name=>'time-test') do |oo|
begin
csv_output = File.join(tempdir,'time_test.csv')
assert oo.to_csv(csv_output)
assert File.exists?(csv_output)
assert_equal "", `diff --strip-trailing-cr #{TESTDIR}/time-test.csv #{csv_output}`
# --strip-trailing-cr is needed because the test-file use 0A and
# the test on an windows box generates 0D 0A as line endings
ensure
File.delete_if_exist(csv_output)
end
end
end
def test_date_time_yaml
with_each_spreadsheet(:name=>'time-test') do |oo|
expected =
"--- \ncell_1_1: \n row: 1 \n col: 1 \n celltype: string \n value: Mittags: \ncell_1_2: \n row: 1 \n col: 2 \n celltype: time \n value: 12:13:14 \ncell_1_3: \n row: 1 \n col: 3 \n celltype: time \n value: 15:16:00 \ncell_1_4: \n row: 1 \n col: 4 \n celltype: time \n value: 23:00:00 \ncell_2_1: \n row: 2 \n col: 1 \n celltype: date \n value: 2007-11-21 \n"
assert_equal expected, oo.to_yaml
end
end
def test_no_remaining_tmp_files_openoffice
after Date.new(2011,8,30) do
# alles noch mal ueberarbeiten
# temp. Directories sollten in diesem Fall ueberhaupt nicht
# angelegt werden
if OPENOFFICE
assert_raise(Zip::ZipError) { #TODO: besseres Fehlerkriterium bei
# oo = Openoffice.new(File.join(TESTDIR,"no_spreadsheet_file.txt"))
# es soll absichtlich ein Abbruch provoziert werden, deshalb :ignore
begin
oo = Openoffice.new(File.join(TESTDIR,"no_spreadsheet_file.txt"),
false,
:ignore)
rescue Zip::ZipError
@tmp = Dir.glob(oo.tmpdir)
raise
end
}
assert @tmp.empty?, "temporay directory was not deleted"
end
end
end
def test_no_remaining_tmp_files_excel
after Date.new(2011,8,30) do
# alles noch mal ueberarbeiten
# temp. Directories sollten in diesem Fall ueberhaupt nicht
# angelegt werden
if EXCEL
prev = Dir.glob(TMP_PREFIX)
assert_raise(Ole::Storage::FormatError) {
oo = Excel.new(File.join(TESTDIR,"no_spreadsheet_file.txt"),
false,
:ignore)
}
now = Dir.glob(TMP_PREFIX)
assert (now-prev).empty?, "temporary directory not removed"
end
end
end
def test_no_remaining_tmp_files_excelx
after Date.new(2011,8,30) do
# alles noch mal ueberarbeiten
# temp. Directories sollten in diesem Fall ueberhaupt nicht
# angelegt werden
if EXCELX
prev = Dir.glob(TMP_PREFIX)
assert_raise(Zip::ZipError) { #TODO: besseres Fehlerkriterium bei
# oo = Excelx.new(File.join(TESTDIR,"no_spreadsheet_file.txt"))
# es soll absichtlich ein Abbruch provoziert werden, deshalb :ignore
oo = Excelx.new(File.join(TESTDIR,"no_spreadsheet_file.txt"),
false,
:ignore)
}
now = Dir.glob(TMP_PREFIX)
assert (now-prev).empty?
end
end
end
def test_no_remaining_tmp_files_google
# Exception ist irgendwie anders, nochmal ansehen TODO:
after Date.new(2011,6,19) do
if GOOGLE
prev = Dir.glob(TMP_PREFIX)
assert_nothing_raised() {
oo = Google.new(key_of("no_spreadsheet_file.txt"))
}
now = Dir.glob(TMP_PREFIX)
assert (now-prev).empty?
end
end
end
# Erstellt eine Liste aller Zellen im Spreadsheet. Dies ist nötig, weil ein einfacher
# Textvergleich des XML-Outputs nicht funktioniert, da xml-builder die Attribute
# nicht immer in der gleichen Reihenfolge erzeugt.
def init_all_cells(oo,sheet)
all = []
oo.first_row(sheet).upto(oo.last_row(sheet)) do |row|
oo.first_column(sheet).upto(oo.last_column(sheet)) do |col|
unless oo.empty?(row,col,sheet)
all << {:row => row.to_s,
:column => col.to_s,
:content => oo.cell(row,col,sheet).to_s,
:type => oo.celltype(row,col,sheet).to_s,
}
end
end
end
all
end
def test_to_xml
with_each_spreadsheet(:name=>'numbers1', :encoding => 'utf8') do |oo|
assert_nothing_raised {oo.to_xml}
sheetname = oo.sheets.first
doc = Nokogiri::XML(oo.to_xml)
sheet_count = 0
doc.xpath('//spreadsheet/sheet').each {|tmpelem|
sheet_count += 1
}
assert_equal 5, sheet_count
doc.xpath('//spreadsheet/sheet').each { |xml_sheet|
all_cells = init_all_cells(oo, sheetname)
x = 0
assert_equal sheetname, xml_sheet.attributes['name'].value
xml_sheet.children.each {|cell|
if cell.attributes['name']
expected = [all_cells[x][:row],
all_cells[x][:column],
all_cells[x][:content],
all_cells[x][:type],
]
result = [
cell.attributes['row'],
cell.attributes['column'],
cell.content,
cell.attributes['type'],
]
assert_equal expected, result
x += 1
end # if
} # end of sheet
sheetname = oo.sheets[oo.sheets.index(sheetname)+1]
}
end
end
def test_bug_row_column_fixnum_float
with_each_spreadsheet(:name=>'bug-row-column-fixnum-float', :format=>:excel) do |oo|
assert_equal 42.5, oo.cell('b',2)
assert_equal 43 , oo.cell('c',2)
assert_equal ['hij',42.5, 43], oo.row(2)
assert_equal ['def',42.5, 'nop'], oo.column(2)
end
end
def test_file_warning_default
if OPENOFFICE
prev = Dir.glob(TMP_PREFIX)
assert_raises(TypeError, "test/numbers1.xls is not an openoffice spreadsheet") { oo = Openoffice.new(File.join(TESTDIR,"numbers1.xls")) }
assert_raises(TypeError) { oo = Openoffice.new(File.join(TESTDIR,"numbers1.xlsx")) }
now = Dir.glob(TMP_PREFIX)
assert (now-prev).empty?
end
if EXCEL
prev = Dir.glob(TMP_PREFIX)
assert_raises(TypeError) { oo = Excel.new(File.join(TESTDIR,"numbers1.ods")) }
assert_raises(TypeError) { oo = Excel.new(File.join(TESTDIR,"numbers1.xlsx")) }
now = Dir.glob(TMP_PREFIX)
assert (now-prev).empty?, "temporary directory was not removed"
end
if EXCELX
prev = Dir.glob(TMP_PREFIX)
assert_raises(TypeError) { oo = Excelx.new(File.join(TESTDIR,"numbers1.ods")) }
assert_raises(TypeError) { oo = Excelx.new(File.join(TESTDIR,"numbers1.xls")) }
now = Dir.glob(TMP_PREFIX)
assert (now-prev).empty?
end
end
def test_file_warning_error
if OPENOFFICE
prev = Dir.glob(TMP_PREFIX)
assert_raises(TypeError) { oo = Openoffice.new(File.join(TESTDIR,"numbers1.xls"),false,:error) }
now = Dir.glob(TMP_PREFIX)
assert (now-prev).empty?
prev = Dir.glob(TMP_PREFIX)
assert_raises(TypeError) { oo = Openoffice.new(File.join(TESTDIR,"numbers1.xlsx"),false,:error) }
now = Dir.glob(TMP_PREFIX)
assert (now-prev).empty?
end
if EXCEL
prev = Dir.glob(TMP_PREFIX)
assert_raises(TypeError) { oo = Excel.new(File.join(TESTDIR,"numbers1.ods"),false,:error) }
now = Dir.glob(TMP_PREFIX)
assert (now-prev).empty?, "temporary directory was not deleted"
prev = Dir.glob(TMP_PREFIX)
assert_raises(TypeError) { oo = Excel.new(File.join(TESTDIR,"numbers1.xlsx"),false,:error) }
now = Dir.glob(TMP_PREFIX)
assert (now-prev).empty?, "temporary directory was not deleted"
end
if EXCELX
prev = Dir.glob(TMP_PREFIX)
assert_raises(TypeError) { oo = Excelx.new(File.join(TESTDIR,"numbers1.ods"),false,:error) }
now = Dir.glob(TMP_PREFIX)
assert (now-prev).empty?, "temporary directory was not deleted"
prev = Dir.glob(TMP_PREFIX)
assert_raises(TypeError) { oo = Excelx.new(File.join(TESTDIR,"numbers1.xls"),false,:error) }
now = Dir.glob(TMP_PREFIX)
assert (now-prev).empty?, "temporary directory was not deleted"
end
end
def test_file_warning_warning
after Date.new(2011,9,2) do
if OPENOFFICE
assert_nothing_raised(TypeError) {
assert_raises(Zip::ZipError) {
oo = Openoffice.new(File.join(TESTDIR,"numbers1.xls"),false, :warning)
}
}
prev = Dir.glob(TMP_PREFIX)
assert_nothing_raised(TypeError) {
assert_raises(Errno::ENOENT) {
oo = Openoffice.new(File.join(TESTDIR,"numbers1.xlsx"),false, :warning)
}
}
now = Dir.glob(TMP_PREFIX)
assert (now-prev).empty?
end
if EXCEL
prev = Dir.glob(TMP_PREFIX)
assert_nothing_raised(TypeError) {
assert_raises(Ole::Storage::FormatError) {
oo = Excel.new(File.join(TESTDIR,"numbers1.ods"),false, :warning) }
}
assert_nothing_raised(TypeError) {
assert_raises(Ole::Storage::FormatError) {
oo = Excel.new(File.join(TESTDIR,"numbers1.xlsx"),false, :warning) }
}
now = Dir.glob(TMP_PREFIX)
assert (now-prev).empty?, "temporary directory was not removed"
end
if EXCELX
prev = Dir.glob(TMP_PREFIX)
assert_nothing_raised(TypeError) {
assert_raises(Errno::ENOENT) {
oo = Excelx.new(File.join(TESTDIR,"numbers1.ods"),false, :warning) }
}
assert_nothing_raised(TypeError) {
assert_raises(Zip::ZipError) {
oo = Excelx.new(File.join(TESTDIR,"numbers1.xls"),false, :warning) }
}
now = Dir.glob(TMP_PREFIX)
assert (now-prev).empty?
end
end
end
def test_file_warning_ignore
if OPENOFFICE
# Files, die eigentlich Openoffice-
# Files sind, aber die falsche Endung haben.
# Es soll ohne Fehlermeldung oder Warnung
# oder Abbruch die Datei geoffnet werden
# xls
assert_nothing_raised() {
oo = Openoffice.new(File.join(TESTDIR,"type_openoffice.xls"),false, :ignore)
}
# xlsx
assert_nothing_raised() {
oo = Openoffice.new(File.join(TESTDIR,"type_openoffice.xlsx"),false, :ignore)
}
end
if EXCEL
assert_nothing_raised() {
oo = Excel.new(File.join(TESTDIR,"type_excel.ods"),false, :ignore)
}
assert_nothing_raised() {
oo = Excel.new(File.join(TESTDIR,"type_excel.xlsx"),false, :ignore)
}
end
if EXCELX
assert_nothing_raised() {
oo = Excelx.new(File.join(TESTDIR,"type_excelx.ods"),false, :ignore)
}
assert_nothing_raised() {
oo = Excelx.new(File.join(TESTDIR,"type_excelx.xls"),false, :ignore)
}
end
=begin
if OPENOFFICE
assert_nothing_raised() {
assert_raises(Zip::ZipError) {
oo = Openoffice.new(File.join(TESTDIR,"numbers1.xls"),false, :ignore) }
}
assert_nothing_raised() {
assert_raises(Errno::ENOENT) {
oo = Openoffice.new(File.join(TESTDIR,"numbers1.xlsx"),false, :ignore) }
}
assert Dir.glob(TMP_PREFIX).empty?
end
if EXCEL
assert_nothing_raised(TypeError) {
assert_raises(Ole::Storage::FormatError) {
oo = Excel.new(File.join(TESTDIR,"numbers1.ods"),false, :ignore) }
}
assert_nothing_raised(TypeError) {
assert_raises(Ole::Storage::FormatError) {oo = Excel.new(File.join(TESTDIR,"numbers1.xlsx"),false, :ignore) }}
assert Dir.glob(TMP_PREFIX).empty?
end
if EXCELX
assert_nothing_raised(TypeError) {
assert_raises(Errno::ENOENT) {
oo = Excelx.new(File.join(TESTDIR,"numbers1.ods"),false, :ignore)
}
}
assert_nothing_raised(TypeError) {
assert_raises(Zip::ZipError) {
oo = Excelx.new(File.join(TESTDIR,"numbers1.xls"),false, :ignore)
}
}
assert Dir.glob(TMP_PREFIX).empty?
end
=end
end
def test_bug_last_row_excel
with_each_spreadsheet(:name=>'time-test', :format=>:excel) do |oo|
assert_equal 2, oo.last_row
end
end
def test_bug_to_xml_with_empty_sheets
with_each_spreadsheet(:name=>'emptysheets', :format=>[:openoffice, :excel]) do |oo|
oo.sheets.each { |sheet|
assert_equal nil, oo.first_row, "first_row not nil in sheet #{sheet}"
assert_equal nil, oo.last_row, "last_row not nil in sheet #{sheet}"
assert_equal nil, oo.first_column, "first_column not nil in sheet #{sheet}"
assert_equal nil, oo.last_column, "last_column not nil in sheet #{sheet}"
assert_equal nil, oo.first_row(sheet), "first_row not nil in sheet #{sheet}"
assert_equal nil, oo.last_row(sheet), "last_row not nil in sheet #{sheet}"
assert_equal nil, oo.first_column(sheet), "first_column not nil in sheet #{sheet}"
assert_equal nil, oo.last_column(sheet), "last_column not nil in sheet #{sheet}"
}
assert_nothing_raised() { result = oo.to_xml }
end
end
def test_bug_simple_spreadsheet_time_bug
# really a bug? are cells really of type time?
# No! :float must be the correct type
with_each_spreadsheet(:name=>'simple_spreadsheet', :format=>:excelx) do |oo|
# puts oo.cell('B',5).to_s
# assert_equal :time, oo.celltype('B',5)
assert_equal :float, oo.celltype('B',5)
assert_equal 10.75, oo.cell('B',5)
assert_equal 12.50, oo.cell('C',5)
assert_equal 0, oo.cell('D',5)
assert_equal 1.75, oo.cell('E',5)
assert_equal 'Task 1', oo.cell('F',5)
assert_equal Date.new(2007,5,7), oo.cell('A',5)
end
end
def test_simple2_excelx
with_each_spreadsheet(:name=>'simple_spreadsheet', :format=>:excelx) do |oo|
assert_equal [:numeric_or_formula, "yyyy\\-mm\\-dd"], oo.excelx_type('A',4)
assert_equal [:numeric_or_formula, "#,##0.00"], oo.excelx_type('B',4)
assert_equal [:numeric_or_formula, "#,##0.00"], oo.excelx_type('c',4)
assert_equal [:numeric_or_formula, "General"], oo.excelx_type('d',4)
assert_equal [:numeric_or_formula, "General"], oo.excelx_type('e',4)
assert_equal :string, oo.excelx_type('f',4)
assert_equal "39209", oo.excelx_value('a',4)
assert_equal "yyyy\\-mm\\-dd", oo.excelx_format('a',4)
assert_equal "9.25", oo.excelx_value('b',4)
assert_equal "10.25", oo.excelx_value('c',4)
assert_equal "0", oo.excelx_value('d',4)
#... Sum-Spalte
# assert_equal "Task 1", oo.excelx_value('f',4)
assert_equal "Task 1", oo.cell('f',4)
assert_equal Date.new(2007,05,07), oo.cell('a',4)
assert_equal "9.25", oo.excelx_value('b',4)
assert_equal "#,##0.00", oo.excelx_format('b',4)
assert_equal 9.25, oo.cell('b',4)
assert_equal :float, oo.celltype('b',4)
assert_equal :float, oo.celltype('d',4)
assert_equal 0, oo.cell('d',4)
assert_equal :formula, oo.celltype('e',4)
assert_equal 1, oo.cell('e',4)
assert_equal 'C4-B4-D4', oo.formula('e',4)
assert_equal :string, oo.celltype('f',4)
assert_equal "Task 1", oo.cell('f',4)
end
end
def test_datetime
with_each_spreadsheet(:name=>'datetime') do |oo|
val = oo.cell('c',3)
assert_kind_of DateTime, val
assert_equal :datetime, oo.celltype('c',3)
assert_equal DateTime.new(1961,11,21,12,17,18), val
val = oo.cell('a',1)
assert_kind_of Date, val
assert_equal :date, oo.celltype('a',1)
assert_equal Date.new(1961,11,21), val
assert_equal Date.new(1961,11,21), oo.cell('a',1)
assert_equal DateTime.new(1961,11,21,12,17,18), oo.cell('a',3)
assert_equal DateTime.new(1961,11,21,12,17,18), oo.cell('b',3)
assert_equal DateTime.new(1961,11,21,12,17,18), oo.cell('c',3)
assert_equal DateTime.new(1961,11,21,12,17,18), oo.cell('a',4)
assert_equal DateTime.new(1961,11,21,12,17,18), oo.cell('b',4)
assert_equal DateTime.new(1961,11,21,12,17,18), oo.cell('c',4)
assert_equal DateTime.new(1961,11,21,12,17,18), oo.cell('a',5)
assert_equal DateTime.new(1961,11,21,12,17,18), oo.cell('b',5)
assert_equal DateTime.new(1961,11,21,12,17,18), oo.cell('c',5)
assert_equal Date.new(1961,11,21), oo.cell('a',6)
assert_equal Date.new(1961,11,21), oo.cell('b',6)
assert_equal Date.new(1961,11,21), oo.cell('c',6)
assert_equal Date.new(1961,11,21), oo.cell('a',7)
assert_equal Date.new(1961,11,21), oo.cell('b',7)
assert_equal Date.new(1961,11,21), oo.cell('c',7)
end
end
def test_cell_openoffice_html_escape
with_each_spreadsheet(:name=>'html-escape', :format=>:openoffice) do |oo|
assert_equal "'", oo.cell(1,1)
assert_equal "&", oo.cell(2,1)
assert_equal ">", oo.cell(3,1)
assert_equal "<", oo.cell(4,1)
assert_equal "`", oo.cell(5,1)
# test_openoffice_zipped will catch issues with "
end
end
def test_cell_boolean
with_each_spreadsheet(:name=>'boolean', :format=>[:openoffice, :excel, :excelx]) do |oo|
if oo.class == Excelx
assert_equal "TRUE", oo.cell(1,1), "failure in "+oo.class.to_s
assert_equal "FALSE", oo.cell(2,1), "failure in "+oo.class.to_s
else
assert_equal "true", oo.cell(1,1), "failure in "+oo.class.to_s
assert_equal "false", oo.cell(2,1), "failure in "+oo.class.to_s
end
end
end
def test_cell_multiline
with_each_spreadsheet(:name=>'paragraph', :format=>[:openoffice, :excel, :excelx]) do |oo|
assert_equal "This is a test\nof a multiline\nCell", oo.cell(1,1)
assert_equal "This is a test\n¶\nof a multiline\n\nCell", oo.cell(1,2)
assert_equal "first p\n\nsecond p\n\nlast p", oo.cell(2,1)
end
end
def test_cell_styles
# styles only valid in excel spreadsheets?
# TODO: what todo with other spreadsheet types
with_each_spreadsheet(:name=>'style', :format=>[# :openoffice,
:excel,
# :excelx
]) do |oo|
# bold
assert_equal true, oo.font(1,1).bold?
assert_equal false, oo.font(1,1).italic?
assert_equal false, oo.font(1,1).underline?
# italic
assert_equal false, oo.font(2,1).bold?
assert_equal true, oo.font(2,1).italic?
assert_equal false, oo.font(2,1).underline?
# normal
assert_equal false, oo.font(3,1).bold?
assert_equal false, oo.font(3,1).italic?
assert_equal false, oo.font(3,1).underline?
# underline
assert_equal false, oo.font(4,1).bold?
assert_equal false, oo.font(4,1).italic?
assert_equal true, oo.font(4,1).underline?
# bold italic
assert_equal true, oo.font(5,1).bold?
assert_equal true, oo.font(5,1).italic?
assert_equal false, oo.font(5,1).underline?
# bold underline
assert_equal true, oo.font(6,1).bold?
assert_equal false, oo.font(6,1).italic?
assert_equal true, oo.font(6,1).underline?
# italic underline
assert_equal false, oo.font(7,1).bold?
assert_equal true, oo.font(7,1).italic?
assert_equal true, oo.font(7,1).underline?
# bolded row
assert_equal true, oo.font(8,1).bold?
assert_equal false, oo.font(8,1).italic?
assert_equal false, oo.font(8,1).underline?
# bolded col
assert_equal true, oo.font(9,2).bold?
assert_equal false, oo.font(9,2).italic?
assert_equal false, oo.font(9,2).underline?
# bolded row, italic col
assert_equal true, oo.font(10,3).bold?
assert_equal true, oo.font(10,3).italic?
assert_equal false, oo.font(10,3).underline?
# normal
assert_equal false, oo.font(11,4).bold?
assert_equal false, oo.font(11,4).italic?
assert_equal false, oo.font(11,4).underline?
end
end
# If a cell has a date-like string but is preceeded by a '
# to force that date to be treated like a string, we were getting an exception.
# This test just checks for that exception to make sure it's not raised in this case
def test_date_to_float_conversion
with_each_spreadsheet(:name=>'datetime_floatconv', :format=>:excel) do |oo|
assert_nothing_raised(NoMethodError) do
oo.cell('a',1)
oo.cell('a',2)
end
end
end
# Need to extend to other formats
def test_row_whitespace
# auf dieses Dokument habe ich keinen Zugriff TODO:
with_each_spreadsheet(:name=>'whitespace') do |oo|
oo.default_sheet = "Sheet1"
assert_equal [nil, nil, nil, nil, nil, nil], oo.row(1)
assert_equal [nil, nil, nil, nil, nil, nil], oo.row(2)
assert_equal ["Date", "Start time", "End time", "Pause", "Sum", "Comment"], oo.row(3)
assert_equal [Date.new(2007,5,7), 9.25, 10.25, 0.0, 1.0, "Task 1"], oo.row(4)
assert_equal [nil, nil, nil, nil, nil, nil], oo.row(5)
assert_equal [Date.new(2007,5,7), 10.75, 10.75, 0.0, 0.0, "Task 1"], oo.row(6)
oo.default_sheet = "Sheet2"
assert_equal ["Date", nil, "Start time"], oo.row(1)
assert_equal [Date.new(2007,5,7), nil, 9.25], oo.row(2)
assert_equal [Date.new(2007,5,7), nil, 10.75], oo.row(3)
end
end
def test_col_whitespace
#TODO:
# kein Zugriff auf Dokument whitespace
with_each_spreadsheet(:name=>'whitespace') do |oo|
oo.default_sheet = "Sheet1"
assert_equal ["Date", Date.new(2007,5,7), nil, Date.new(2007,5,7)], oo.column(1)
assert_equal ["Start time", 9.25, nil, 10.75], oo.column(2)
assert_equal ["End time", 10.25, nil, 10.75], oo.column(3)
assert_equal ["Pause", 0.0, nil, 0.0], oo.column(4)
assert_equal ["Sum", 1.0, nil, 0.0], oo.column(5)
assert_equal ["Comment","Task 1", nil, "Task 1"], oo.column(6)
oo.default_sheet = "Sheet2"
assert_equal [nil, nil, nil], oo.column(1)
assert_equal [nil, nil, nil], oo.column(2)
assert_equal ["Date", Date.new(2007,5,7), Date.new(2007,5,7)], oo.column(3)
assert_equal [nil, nil, nil], oo.column(4)
assert_equal [ "Start time", 9.25, 10.75], oo.column(5)
end
end
# Excel has two base date formats one from 1900 and the other from 1904.
# There's a MS bug that 1900 base dates include an extra day due to erroneously
# including 1900 as a leap yar.
def test_base_dates_in_excel
with_each_spreadsheet(:name=>'1900_base', :format=>:excel) do |oo|
assert_equal Date.new(2009,06,15), oo.cell(1,1)
#we don't want to to 'interpret' formulas assert_equal Date.new(Time.now.year,Time.now.month,Time.now.day), oo.cell(2,1) #formula for TODAY()
# if we test TODAY() we have also have to calculate
# other date calculations
#
assert_equal :date, oo.celltype(1,1)
end
with_each_spreadsheet(:name=>'1904_base', :format=>:excel) do |oo|
assert_equal Date.new(2009,06,15), oo.cell(1,1)
# see comment above
# assert_equal Date.new(Time.now.year,Time.now.month,Time.now.day), oo.cell(2,1) #formula for TODAY()
assert_equal :date, oo.celltype(1,1)
end
end
def test_bad_date
with_each_spreadsheet(:name=>'prova', :format=>:excel) do |oo|
assert_nothing_raised(ArgumentError) {
assert_equal DateTime.new(2006,2,2,10,0,0), oo.cell('a',1)
}
end # each
end
def test_cell_methods
with_each_spreadsheet(:name=>'numbers1') do |oo|
assert_equal 10, oo.a4 # cell(4,'A')
assert_equal 11, oo.b4 # cell(4,'B')
assert_equal 12, oo.c4 # cell(4,'C')
assert_equal 13, oo.d4 # cell(4,'D')
assert_equal 14, oo.e4 # cell(4,'E')
assert_equal 'ABC', oo.c6('Sheet5')
#assert_raises(ArgumentError) {
assert_raises(NoMethodError) {
# a42a is not a valid cell name, should raise ArgumentError
assert_equal 9999, oo.a42a
}
end
end
# compare large spreadsheets
def test_compare_large_spreadsheets
# problematisch, weil Formeln in Excel nicht unterstützt werden
if LONG_RUN
qq = Openoffice.new(File.join('test',"Bibelbund.ods"))
with_each_spreadsheet(:name=>'Bibelbund') do |oo|
# p "comparing Bibelbund.ods with #{oo.class}"
oo.sheets.each do |sh|
oo.first_row.upto(oo.last_row) do |row|
oo.first_column.upto(oo.last_column) do |col|
c1 = qq.cell(row,col,sh)
c1.force_encoding("UTF-8") if c1.class == String
c2 = oo.cell(row,col,sh)
c2.force_encoding("UTF-8") if c2.class == String
assert_equal c1, c2, "diff in #{sh}/#{row}/#{col}}"
assert_equal qq.celltype(row,col,sh), oo.celltype(row,col,sh)
assert_equal qq.formula?(row,col,sh), oo.formula?(row,col,sh) if oo.class != Excel
end
end
end
end
end # LONG_RUN
end
def test_labeled_cells
# to do
# "more spreadsheet types"
# end
with_each_spreadsheet(:name=>'named_cells', :format=>:openoffice) do |oo|
oo.default_sheet = oo.sheets.first
begin
row,col = oo.label('anton')
rescue ArgumentError
puts "labels error at #{oo.class}"
raise
end
assert_equal 5, row
assert_equal 3, col
row,col = oo.label('anton')
assert_equal 'Anton', oo.cell(row,col)
row,col = oo.label('berta')
assert_equal 'Bertha', oo.cell(row,col)
row,col = oo.label('caesar')
assert_equal 'Cäsar', oo.cell(row,col)
row,col = oo.label('never')
assert_nil row
assert_nil col
row,col,sheet = oo.label('anton')
assert_equal 5, row
assert_equal 3, col
assert_equal "Sheet1", sheet
assert_equal "Anton", oo.anton
assert_raises(NoMethodError) {
row,col = oo.never
}
end
end
def test_bug_excel_last_row_255
if LONG_RUN
local_only do
oo = Excel.new(File.join('..','confidential','ScienceStaff.xls'))
oo.default_sheet = oo.sheets.first
assert_equal "COMSCI", oo.cell(255,1)
assert_equal "lala", oo.cell(256,1)
assert_equal 1537, oo.last_row
end
end
end
def test_bug_excel_last_row_255_modified
local_only do
oo = Excel.new(File.join('..','confidential','ScienceStaff_modified.xls'))
oo.default_sheet = oo.sheets.first
assert_equal 1537, oo.last_row
end
end
require 'matrix'
def test_matrix
with_each_spreadsheet(:name => 'matrix', :format => [:openoffice, :excel, :google]) do |oo|
oo.default_sheet = oo.sheets.first
assert_equal Matrix[
[1.0, 2.0, 3.0],
[4.0, 5.0, 6.0],
[7.0, 8.0, 9.0] ], oo.to_matrix
end
end
def test_matrix_selected_range
with_each_spreadsheet(:name => 'matrix', :format=>[:excel,:openoffice,:google]) do |oo|
oo.default_sheet = 'Sheet2'
assert_equal Matrix[
[1.0, 2.0, 3.0],
[4.0, 5.0, 6.0],
[7.0, 8.0, 9.0] ], oo.to_matrix(3,4,5,6)
end
end
def test_matrix_all_nil
with_each_spreadsheet(:name => 'matrix', :format=>[:excel,:openoffice,:google]) do |oo|
oo.default_sheet = 'Sheet2'
assert_equal Matrix[
[nil, nil, nil],
[nil, nil, nil],
[nil, nil, nil] ], oo.to_matrix(10,10,12,12)
end
end
def test_matrix_values_and_nil
with_each_spreadsheet(:name => 'matrix', :format=>[:excel,:openoffice,:google]) do |oo|
oo.default_sheet = 'Sheet3'
assert_equal Matrix[
[1.0, nil, 3.0],
[4.0, 5.0, 6.0],
[7.0, 8.0, nil] ], oo.to_matrix(1,1,3,3)
end end
def test_bug_date_mileszs
after Date.new(2011,8,28) do
# to do
# "An richtige Stelle kopieren. Ist das Dokument vertraulich?"
# 'ist auf dem Netbook nicht vorhanden'
# end
oo = Excel.new "/home/tp/Documents/feb-sales-analysis.xls"
oo.default_sheet = oo.sheets.first
# 2/1/2010 A2-A6 mm/dd/yyyy
2.upto(6) do |i|
assert_equal Date.new(2010,2,1), oo.cell('A',i)
end
end
end
#def test_no_generic_new
#oo = GenericSpreadsheet.new
#assert_equal GenericSpreadsheet, oo.class
#end
# unter Windows soll es laut Bug-Reports nicht moeglich sein, eine Excel-Datei, die
# mit Excel.new geoeffnet wurde nach dem Processing anschliessend zu loeschen.
# Anmerkung: Das Spreadsheet-Gem erlaubt kein explizites Close von Spreadsheet-Dateien,
# was verhindern koennte, das die Datei geloescht werden kann.
def test_bug_cannot_delete_opened_excel_sheet
# with_each_spreadsheet(:name=>'simple_spreadsheet') do |oo|
after Date.new(2011,7,17) do
# to do
# 'kopiere nach temporaere Datei
# und versuche diese zu oeffnen und zu loeschen'
# end
end
#end
end
def test_bug_xlsx_reference_cell
=begin
If cell A contains a string and cell B references cell A. When reading the value of cell B, the result will be
"0.0" instead of the value of cell A.
With the attached test case, I ran the following code:
spreadsheet = Excelx.new('formula_string_error.xlsx')
spreadsheet.default_sheet = 'sheet1'
p "A: #{spreadsheet.cell(1, 1)}"
p "B: #{spreadsheet.cell(2, 1)}"
with the following results
"A: TestString"
"B: 0.0"
where the expected result is
"A: TestString"
"B: TestString"
=end
xlsx = Excelx.new(File.join('test', "formula_string_error.xlsx"))
xlsx.default_sheet = xlsx.sheets.first
assert_equal 'Teststring', xlsx.cell('a',1)
assert_equal 'Teststring', xlsx.cell('a',2)
end
def test_bug_guest_list_2011_05_05
after Date.new(2011,8,28) do
oo = Excel.new(File.join("..","confidential","guest_list_addresses.xls"))
oo.default_sheet = oo.sheets.first
assert_equal "lalala", oo.cell('a',1) # anderer Inhalt im Spreadsheet
assert_equal :string, oo.celltype('a',1)
end
end
def test_bug_guest_list_2011_05_05_spreadsheet
# to do
# 'wieder entfernen'
# end
require 'spreadsheet'
book = Spreadsheet.open File.join('..','confidential','guest_list_addresses.xls')
sheet1 = book.worksheet 0
sheet1.each do |row|
p row[0]
end
end
# don't test it with other spreadsheet types! this was only a problem
# with .xlsx files
def test_bug_date_not_recognized_2011_05_21
#oo = Excelx.new(File.join(TESTDIR,'2011-05-21_sample_date_problem.xlsx'))
oo = Excelx.new(File.join('..','confidential','2011-05-21_sample_date_problem.xlsx'))
oo.default_sheet = oo.sheets.first
assert_equal Date.new(2011,3,24), oo.b4
assert_equal Date.new(2011,3,25), oo.b5
assert_equal Date.new(2011,5,5), oo.b6
assert_equal Date.new(2012,3,23), oo.b7
end
def test_bug_string_as_a_date_2011_05_21_spreadsheet_only
after Date.new(2011,8,28) do
# to do
# 'wieder entfernen'
# end
require 'spreadsheet'
book = Spreadsheet.open File.join('..','confidential','2011-05-21_sample_type_problem.xls')
sheet1 = book.worksheet 0
sheet1.each_with_index do |row,rownum|
# p row[0]
if rownum == 2
assert_equal 68, row[6]
end
end
end
end
def test_bug_string_as_a_date_2011_05_21
after Date.new(2011,8,28) do
#oo = Excel.new(File.join(TESTDIR,'2011-05-21_sample_type_problem.xls'))
oo = Excel.new(File.join('..','confidential','2011-05-21_sample_type_problem.xls'))
oo.default_sheet = oo.sheets.first
assert_equal 68, oo.g2
assert_equal 72, oo.g3
assert_equal 75, oo.g4
assert_equal 76, oo.g5
assert_equal 77, oo.g6
assert_equal 78, oo.g7
end
end
def test_bug_string_as_a_date_2011_05_21_saved_as_ods
#oo = Openoffice.new(File.join(TESTDIR,'2011-05-21_sample_type_problem.ods'))
oo = Openoffice.new(File.join('..','confidential','2011-05-21_sample_type_problem.ods'))
oo.default_sheet = oo.sheets.first
assert_equal 68, oo.g2
assert_equal 72, oo.g3
assert_equal 75, oo.g4
assert_equal 76, oo.g5
assert_equal 77, oo.g6
assert_equal 78, oo.g7
end
# #formulas of an empty sheet should return an empty array and not result in
# an error message
# 2011-06-24
def test_bug_formulas_empty_sheet
with_each_spreadsheet(:name =>'emptysheets',
:format=>[:openoffice,:excelx,:google]) do |oo|
assert_nothing_raised(NoMethodError) {
oo.default_sheet = oo.sheets.first
result = oo.formulas
}
assert_equal([], oo.formulas)
end
end
# #to_yaml of an empty sheet should return an empty string and not result in
# an error message
# 2011-06-24
def test_bug_to_yaml_empty_sheet
with_each_spreadsheet(:name =>'emptysheets',
:format=>[:openoffice,:excelx,:google]) do |oo|
assert_nothing_raised(NoMethodError) {
oo.default_sheet = oo.sheets.first
result = oo.to_yaml
}
assert_equal('', oo.to_yaml)
end
end
# #to_matrix of an empty sheet should return an empty matrix and not result in
# an error message
# 2011-06-25
def test_bug_to_matrix_empty_sheet
with_each_spreadsheet(:name =>'emptysheets',
:format=>[:openoffice,:excelx,:google]) do |oo|
assert_nothing_raised(NoMethodError) {
oo.default_sheet = oo.sheets.first
result = oo.to_matrix
}
assert_equal(Matrix.empty(0,0), oo.to_matrix)
end
end
# 2011-08-03
def test_bug_datetime_to_csv
with_each_spreadsheet(:name=>'datetime') do |oo|
assert oo.to_csv("datetime.csv")
assert File.exists?("datetime.csv")
assert_equal "", `diff test/so_datetime.csv datetime.csv`
end
end
# 2011-08-11
def test_bug_openoffice_formula_missing_letters
if OPENOFFICE
# Dieses Dokument wurde mit LibreOffice angelegt.
# Keine Ahnung, ob es damit zusammenhaengt, das diese
# Formeln anders sind, als in der Datei formula.ods, welche
# mit Openoffice angelegt wurde.
# Bei den Openoffice-Dateien ist in diesem Feld in der XML-
# Datei of: als Prefix enthalten, waehrend in dieser Datei
# irgendetwas mit oooc: als Prefix verwendet wird.
oo = Openoffice.new(File.join(TESTDIR,'dreimalvier.ods'))
oo.default_sheet = oo.sheets.first
assert_equal '=SUM([.A1:.D1])', oo.formula('e',1)
assert_equal '=SUM([.A2:.D2])', oo.formula('e',2)
assert_equal '=SUM([.A3:.D3])', oo.formula('e',3)
assert_equal [
[1,5,'=SUM([.A1:.D1])'],
[2,5,'=SUM([.A2:.D2])'],
[3,5,'=SUM([.A3:.D3])'],
], oo.formulas
end
end
end # class