Class: Roo::Excel2003XML

Inherits:
Base
  • Object
show all
Defined in:
lib/roo/xls/excel_2003_xml.rb

Defined Under Namespace

Classes: Font

Instance Method Summary collapse

Constructor Details

#initialize(filename, options = {}) ⇒ Excel2003XML

initialization and opening of a spreadsheet file values for packed: :zip



9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
 
# File 'lib/roo/xls/excel_2003_xml.rb', line 9

def initialize(filename, options = {})
  packed = options[:packed]
  file_warning = options[:file_warning] || :error

  Dir.mktmpdir do |tmpdir|
    filename = download_uri(filename, tmpdir) if uri?(filename)
    filename = unzip(filename, tmpdir) if packed == :zip

    file_type_check(filename, '.xml', 'an Excel 2003 XML', file_warning)
    @filename = filename
    unless File.file?(@filename)
      raise IOError, "file #{@filename} does not exist"
    end
    @doc = ::Roo::Utils.load_xml(@filename)
  end
  namespace = @doc.namespaces.select { |_, urn| urn == 'urn:schemas-microsoft-com:office:spreadsheet' }.keys.last
  @namespace = (namespace.nil? || namespace.empty?) ? 'ss' : namespace.split(':').last
  super(filename, options)
  @formula = {}
  @style = {}
  @style_defaults = Hash.new { |h, k| h[k] = [] }
  @style_definitions = {}
  read_styles
end

Instance Method Details

#cell(row, col, sheet = nil) ⇒ Object

Returns the content of a spreadsheet-cell. (1,1) is the upper left corner. (1,1), (1,‘A’), (‘A’,1), (‘a’,1) all refers to the cell at the first line and first row.



38
39
40
41
42
43
44
45
46
47
 
# File 'lib/roo/xls/excel_2003_xml.rb', line 38

def cell(row, col, sheet = nil)
  sheet ||= @default_sheet
  read_cells(sheet)
  row, col = normalize(row, col)
  if celltype(row, col, sheet) == :date
    yyyy, mm, dd = @cell[sheet][[row, col]].split('-')
    return Date.new(yyyy.to_i, mm.to_i, dd.to_i)
  end
  @cell[sheet][[row, col]]
end

#celltype(row, col, sheet = nil) ⇒ Object

returns the type of a cell:

  • :float

  • :string

  • :date

  • :percentage

  • :formula

  • :time

  • :datetime



93
94
95
96
97
98
99
100
101
102
 
# File 'lib/roo/xls/excel_2003_xml.rb', line 93

def celltype(row, col, sheet = nil)
  sheet ||= @default_sheet
  read_cells(sheet)
  row, col = normalize(row, col)
  if @formula[sheet][[row, col]]
    return :formula
  else
    @cell_type[sheet][[row, col]]
  end
end

#font(row, col, sheet = nil) ⇒ Object

Given a cell, return the cell’s style



77
78
79
80
81
82
83
 
# File 'lib/roo/xls/excel_2003_xml.rb', line 77

def font(row, col, sheet = nil)
  sheet ||= @default_sheet
  read_cells(sheet)
  row, col = normalize(row, col)
  style_name = @style[sheet][[row, col]] || @style_defaults[sheet][col - 1] || 'Default'
  @style_definitions[style_name]
end

#formula(row, col, sheet = nil) ⇒ Object Also known as: formula?

Returns the formula at (row,col). Returns nil if there is no formula. The method #formula? checks if there is a formula.



52
53
54
55
56
57
 
# File 'lib/roo/xls/excel_2003_xml.rb', line 52

def formula(row, col, sheet = nil)
  sheet ||= @default_sheet
  read_cells(sheet)
  row, col = normalize(row, col)
  @formula[sheet][[row, col]] && @formula[sheet][[row, col]]['oooc:'.length..-1]
end

#formulas(sheet = nil) ⇒ Object

returns each formula in the selected sheet as an array of elements

row, col, formula 


132
133
134
135
136
137
138
139
140
141
142
143
144
145
 
# File 'lib/roo/xls/excel_2003_xml.rb', line 132

def formulas(sheet = nil)
  theformulas = []
  sheet ||= @default_sheet
  read_cells(sheet)
  first_row(sheet).upto(last_row(sheet)) do|row|
    first_column(sheet).upto(last_column(sheet)) do|col|
      if formula?(row, col, sheet)
        f = [row, col, formula(row, col, sheet)]
        theformulas << f
      end
    end
  end
  theformulas
end

#officeversion ⇒ Object

version of the openoffice document at 2007 this is always “1.0”



112
113
114
115
 
# File 'lib/roo/xls/excel_2003_xml.rb', line 112

def officeversion
  oo_version
  @officeversion
end

#save ⇒ Object

save spreadsheet



126
127
128
 
# File 'lib/roo/xls/excel_2003_xml.rb', line 126

def save #:nodoc:
  42
end

#sheets ⇒ Object 



104
105
106
107
108
 
# File 'lib/roo/xls/excel_2003_xml.rb', line 104

def sheets
  @doc.xpath("/#{@namespace}:Workbook/#{@namespace}:Worksheet").map do |sheet|
    sheet["#{@namespace}:Name"]
  end
end

#to_s(sheet = nil) ⇒ Object

shows the internal representation of all cells mainly for debugging purposes



119
120
121
122
123
 
# File 'lib/roo/xls/excel_2003_xml.rb', line 119

def to_s(sheet = nil)
  sheet ||= @default_sheet
  read_cells(sheet)
  @cell[sheet].inspect
end