Skip to content

HTTPS clone URL

Subversion checkout URL

You can clone with
or
.
Download ZIP
Tree: 0fa5a4e3e4
Fetching contributors…

Cannot retrieve contributors at this time

386 lines (313 sloc) 13.458 kB
require 'spec_helper'
require 'date'
require 'stringio'
describe POI::Workbook do
it "should open a workbook and allow access to its worksheets" do
name = TestDataFile.expand_path("various_samples.xlsx")
book = POI::Workbook.open(name)
book.worksheets.size.should == 5
book.filename.should == name
end
it "should be able to create a Workbook from an IO object" do
content = StringIO.new(open(TestDataFile.expand_path("various_samples.xlsx"), 'rb'){|f| f.read})
book = POI::Workbook.open(content)
book.worksheets.size.should == 5
book.filename.should =~ /spreadsheet.xlsx$/
end
it "should be able to create a Workbook from a Java input stream" do
content = java.io.FileInputStream.new(TestDataFile.expand_path("various_samples.xlsx"))
book = POI::Workbook.open(content)
book.worksheets.size.should == 5
book.filename.should =~ /spreadsheet.xlsx$/
end
it "should create an HSSFWorkbook when passed a :format => :hssf option" do
book = POI::Workbook.create('test.xls', :format => :hssf)
book.poi_workbook.should be_kind_of(org.apache.poi.hssf.usermodel.HSSFWorkbook)
end
it "should create an XSSFWorkbook when passed a :format => :xssf option" do
book = POI::Workbook.create('test.xlsx', :format => :xssf)
book.poi_workbook.should be_kind_of(org.apache.poi.xssf.usermodel.XSSFWorkbook)
end
it "should create an XSSFWorkbook by default" do
book = POI::Workbook.create('test.xlsx')
book.poi_workbook.should be_kind_of(org.apache.poi.xssf.usermodel.XSSFWorkbook)
end
it "should return a column of cells by reference" do
name = TestDataFile.expand_path("various_samples.xlsx")
book = POI::Workbook.open(name)
book["numbers!$A"].should == book['numbers'].rows.collect{|e| e[0].value}
book["numbers!A"].should == book['numbers'].rows.collect{|e| e[0].value}
book["numbers!C"].should == book['numbers'].rows.collect{|e| e[2].value}
book["numbers!$D:$D"].should == book['numbers'].rows.collect{|e| e[3].value}
book["numbers!$c:$D"].should == {"C" => book['numbers'].rows.collect{|e| e[2].value}, "D" => book['numbers'].rows.collect{|e| e[3].value}}
end
it "should return cells by reference" do
name = TestDataFile.expand_path("various_samples.xlsx")
book = POI::Workbook.open(name)
book.cell("numbers!A1").value.should == 'NUM'
book.cell("numbers!A2").to_s.should == '1.0'
book.cell("numbers!A3").to_s.should == '2.0'
book.cell("numbers!A4").to_s.should == '3.0'
book.cell("numbers!A10").to_s.should == '9.0'
book.cell("numbers!B10").to_s.should == '81.0'
book.cell("numbers!C10").to_s.should == '729.0'
book.cell("numbers!D10").to_s.should == '3.0'
book.cell("text & pic!A10").value.should == 'This is an Excel XLSX workbook.'
book.cell("bools & errors!B3").value.should == true
book.cell("high refs!AM619").value.should == 'This is some text'
book.cell("high refs!AO624").value.should == 24.0
book.cell("high refs!AP631").value.should == 13.0
book.cell(%Q{'text & pic'!A10}).value.should == 'This is an Excel XLSX workbook.'
book.cell(%Q{'bools & errors'!B3}).value.should == true
book.cell(%Q{'high refs'!AM619}).value.should == 'This is some text'
book.cell(%Q{'high refs'!AO624}).value.should == 24.0
book.cell(%Q{'high refs'!AP631}).value.should == 13.0
end
it "should handle named cell ranges" do
name = TestDataFile.expand_path("various_samples.xlsx")
book = POI::Workbook.open(name)
book.named_ranges.length.should == 3
book.named_ranges.collect{|e| e.name}.should == %w{four_times_six NAMES nums}
book.named_ranges.collect{|e| e.sheet.name}.should == ['high refs', 'bools & errors', 'high refs']
book.named_ranges.collect{|e| e.formula}.should == ["'high refs'!$AO$624", "'bools & errors'!$D$2:$D$11", "'high refs'!$AP$619:$AP$631"]
book['four_times_six'].should == 24.0
book['nums'].should == (1..13).collect{|e| e * 1.0}
# NAMES is a range of empty cells
book['NAMES'].should == [nil, nil, nil, nil, nil, nil, nil, nil, nil, nil]
book.cell('NAMES').each do | cell |
cell.value.should be_nil
cell.poi_cell.should_not be_nil
cell.to_s.should be_empty
end
end
it "should return an array of cell values by reference" do
name = TestDataFile.expand_path("various_samples.xlsx")
book = POI::Workbook.open(name)
book['dates!A2:A16'].should == (Date.parse('2010-02-28')..Date.parse('2010-03-14')).to_a
end
it "should return cell values by reference" do
name = TestDataFile.expand_path("various_samples.xlsx")
book = POI::Workbook.open(name)
book['text & pic!A10'].should == 'This is an Excel XLSX workbook.'
book['bools & errors!B3'].should == true
book['high refs!AM619'].should == 'This is some text'
book['high refs!AO624'].should == 24.0
book['high refs!AP631'].should == 13.0
end
end
describe POI::Worksheets do
it "should allow indexing worksheets by ordinal" do
name = TestDataFile.expand_path("various_samples.xlsx")
book = POI::Workbook.open(name)
book.worksheets[0].name.should == "text & pic"
book.worksheets[1].name.should == "numbers"
book.worksheets[2].name.should == "dates"
book.worksheets[3].name.should == "bools & errors"
end
it "should allow indexing worksheets by name" do
name = TestDataFile.expand_path("various_samples.xlsx")
book = POI::Workbook.open(name)
book.worksheets["text & pic"].name.should == "text & pic"
book.worksheets["numbers"].name.should == "numbers"
book.worksheets["dates"].name.should == "dates"
end
it "should be enumerable" do
name = TestDataFile.expand_path("various_samples.xlsx")
book = POI::Workbook.open(name)
book.worksheets.should be_kind_of Enumerable
book.worksheets.each do |sheet|
sheet.should be_kind_of POI::Worksheet
end
book.worksheets.count.should == 5
book.worksheets.collect.count.should == 5
end
it "returns cells when passing a cell reference" do
name = TestDataFile.expand_path("various_samples.xlsx")
book = POI::Workbook.open(name)
book['dates']['A2'].to_s.should == '2010-02-28'
book['dates']['a2'].to_s.should == '2010-02-28'
book['dates']['B2'].to_s.should == '2010-03-14'
book['dates']['b2'].to_s.should == '2010-03-14'
book['dates']['C2'].to_s.should == '2010-03-28'
book['dates']['c2'].to_s.should == '2010-03-28'
end
end
describe POI::Rows do
it "should be enumerable" do
name = TestDataFile.expand_path("various_samples.xlsx")
book = POI::Workbook.open(name)
sheet = book.worksheets["text & pic"]
sheet.rows.should be_kind_of Enumerable
sheet.rows.each do |row|
row.should be_kind_of POI::Row
end
sheet.rows.count.should == 7
sheet.rows.collect.count.should == 7
end
end
describe POI::Cells do
before :each do
@name = TestDataFile.expand_path("various_samples.xlsx")
@book = POI::Workbook.open(@name)
end
def book
@book
end
def name
@name
end
it "should be enumerable" do
sheet = book.worksheets["text & pic"]
rows = sheet.rows
cells = rows[0].cells
cells.should be_kind_of Enumerable
cells.count.should == 1
cells.collect.count.should == 1
end
end
describe POI::Cell do
before :each do
@name = TestDataFile.expand_path("various_samples.xlsx")
@book = POI::Workbook.open(@name)
end
def book
@book
end
def name
@name
end
it "should provide dates for date cells" do
sheet = book.worksheets["dates"]
rows = sheet.rows
dates_by_column = [
(Date.parse('2010-02-28')..Date.parse('2010-03-14')),
(Date.parse('2010-03-14')..Date.parse('2010-03-28')),
(Date.parse('2010-03-28')..Date.parse('2010-04-11'))]
(0..2).each do |col|
dates_by_column[col].each_with_index do |date, index|
row = index + 1
rows[row][col].value.should equal_at_cell(date, row, col)
end
end
end
it "should provide numbers for numeric cells" do
sheet = book.worksheets["numbers"]
rows = sheet.rows
(1..15).each do |number|
row = number
rows[row][0].value.should equal_at_cell(number, row, 0)
rows[row][1].value.should equal_at_cell(number ** 2, row, 1)
rows[row][2].value.should equal_at_cell(number ** 3, row, 2)
rows[row][3].value.should equal_at_cell(Math.sqrt(number), row, 3)
end
rows[9][0].to_s.should == '9.0'
rows[9][1].to_s.should == '81.0'
rows[9][2].to_s.should == '729.0'
rows[9][3].to_s.should == '3.0'
end
it "should handle array access from the workbook down to cells" do
book[1][9][0].to_s.should == '9.0'
book[1][9][1].to_s.should == '81.0'
book[1][9][2].to_s.should == '729.0'
book[1][9][3].to_s.should == '3.0'
book["numbers"][9][0].to_s.should == '9.0'
book["numbers"][9][1].to_s.should == '81.0'
book["numbers"][9][2].to_s.should == '729.0'
book["numbers"][9][3].to_s.should == '3.0'
end
it "should provide error text for error cells" do
sheet = book.worksheets["bools & errors"]
rows = sheet.rows
rows[6][0].value.should == 0.0 #'~CIRCULAR~REF~'
rows[6][0].error_value.should be_nil
rows[7][0].value.should be_nil
rows[7][0].error_value.should == '#DIV/0!'
rows[8][0].value.should be_nil
rows[8][0].error_value.should == '#N/A'
rows[9][0].value.should be_nil
rows[9][0].error_value.should == '#NAME?'
rows[10][0].value.should be_nil
rows[10][0].error_value.should == '#NULL!'
rows[11][0].value.should be_nil
rows[11][0].error_value.should == '#NUM!'
rows[12][0].value.should be_nil
rows[12][0].error_value.should == '#REF!'
rows[13][0].value.should be_nil
rows[13][0].error_value.should == '#VALUE!'
lambda{ rows[14][0].value }.should_not raise_error(Java::java.lang.RuntimeException)
rows[6][0].to_s.should == '0.0' #'~CIRCULAR~REF~'
rows[7][0].to_s.should == '' #'#DIV/0!'
rows[8][0].to_s.should == '' #'#N/A'
rows[9][0].to_s.should == '' #'#NAME?'
rows[10][0].to_s.should == '' #'#NULL!'
rows[11][0].to_s.should == '' #'#NUM!'
rows[12][0].to_s.should == '' #'#REF!'
rows[13][0].to_s.should == '' #'#VALUE!'
rows[14][0].to_s.should == ''
end
it "should provide booleans for boolean cells" do
sheet = book.worksheets["bools & errors"]
rows = sheet.rows
rows[1][0].value.should == false
rows[1][0].to_s.should == 'false'
rows[1][1].value.should == false
rows[1][1].to_s.should == 'false'
rows[2][0].value.should == true
rows[2][0].to_s.should == 'true'
rows[2][1].value.should == true
rows[2][1].to_s.should == 'true'
end
it "should provide the cell value as a string" do
sheet = book.worksheets["text & pic"]
rows = sheet.rows
rows[0][0].value.should == "This"
rows[1][0].value.should == "is"
rows[2][0].value.should == "an"
rows[3][0].value.should == "Excel"
rows[4][0].value.should == "XLSX"
rows[5][0].value.should == "workbook"
rows[9][0].value.should == 'This is an Excel XLSX workbook.'
rows[0][0].to_s.should == "This"
rows[1][0].to_s.should == "is"
rows[2][0].to_s.should == "an"
rows[3][0].to_s.should == "Excel"
rows[4][0].to_s.should == "XLSX"
rows[5][0].to_s.should == "workbook"
rows[9][0].to_s.should == 'This is an Excel XLSX workbook.'
end
it "should provide formulas instead of string-ified values" do
sheet = book.worksheets["numbers"]
rows = sheet.rows
(1..15).each do |number|
row = number
rows[row][0].to_s(false).should == "#{number}.0"
rows[row][1].to_s(false).should == "A#{row + 1}*A#{row + 1}"
rows[row][2].to_s(false).should == "B#{row + 1}*A#{row + 1}"
rows[row][3].to_s(false).should == "SQRT(A#{row + 1})"
end
sheet = book.worksheets["bools & errors"]
rows = sheet.rows
rows[1][0].to_s(false).should == '1=2'
rows[1][1].to_s(false).should == 'FALSE'
rows[2][0].to_s(false).should == '1=1'
rows[2][1].to_s(false).should == 'TRUE'
rows[14][0].to_s(false).should == 'foobar(1)'
sheet = book.worksheets["text & pic"]
sheet.rows[9][0].to_s(false).should == 'CONCATENATE(A1," ", A2," ", A3," ", A4," ", A5," ", A6,".")'
end
it "should handle getting values out of 'non-existent' cells" do
sheet = book.worksheets["bools & errors"]
sheet.rows[14][2].value.should be_nil
end
it "should notify the workbook that I have been updated" do
book['dates!A10'].to_s.should == '2010-03-08'
book['dates!A16'].to_s.should == '2010-03-14'
book['dates!B2'].to_s.should == '2010-03-14'
cell = book.cell('dates!B2')
cell.formula.should == 'A16'
cell.formula = 'A10 + 1'
book.cell('dates!B2').poi_cell.should === cell.poi_cell
book.cell('dates!B2').formula.should == 'A10 + 1'
book['dates!B2'].to_s.should == '2010-03-09'
end
end
Jump to Line
Something went wrong with that request. Please try again.