Class: Excel2003XML

Inherits:
GenericSpreadsheet show all
Defined in:
lib/roo/excel2003xml.rb

Defined Under Namespace

Classes: Font

Constant Summary collapse

@@nr = 
0

Instance Attribute Summary

Attributes inherited from GenericSpreadsheet

#default_sheet, #header_line

Instance Method Summary collapse

Methods inherited from GenericSpreadsheet

#column, #empty?, #find, #first_column, #first_column_as_letter, #first_row, #info, #initialise_tmp, #last_column, #last_column_as_letter, #last_row, #reload, #remove_tmp, #row, #to_csv, #to_xml, #to_yaml

Constructor Details

#initialize(filename, packed = nil, file_warning = :error) ⇒ Excel2003XML

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



14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
 
# File 'lib/roo/excel2003xml.rb', line 14

def initialize(filename, packed=nil, file_warning=:error) 
  @file_warning = file_warning
  super()
  filename = open_from_uri(filename) if filename[0,7] == "http://"
  filename = unzip(filename) if packed and packed == :zip
  begin
    file_type_check(filename,'.xml','an Excel 2003 XML')
    @cells_read = Hash.new
    @filename = filename
    unless File.file?(@filename)
      raise IOError, "file #{@filename} does not exist"
    end
    @doc = XML::Parser.file(@filename).parse
  ensure
    FileUtils::rm_r(@tmpdir) if @tmpdir
  end
  @default_sheet = self.sheets.first
  @cell = Hash.new
  @cell_type = Hash.new
  @formula = Hash.new
  @first_row = Hash.new
  @last_row = Hash.new
  @first_column = Hash.new
  @last_column = Hash.new
  @style = Hash.new
  @style_defaults = Hash.new { |h,k| h[k] = [] }
  @style_definitions = Hash.new 
  @header_line = 1
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.



48
49
50
51
52
53
54
55
56
57
 
# File 'lib/roo/excel2003xml.rb', line 48

def cell(row, col, sheet=nil)
  sheet = @default_sheet unless sheet
  read_cells(sheet) unless @cells_read[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



132
133
134
135
136
137
138
139
140
141
 
# File 'lib/roo/excel2003xml.rb', line 132

def celltype(row,col,sheet=nil)
  sheet = @default_sheet unless sheet
  read_cells(sheet) unless @cells_read[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



98
99
100
101
102
103
104
 
# File 'lib/roo/excel2003xml.rb', line 98

def font(row, col, sheet=nil)
  sheet = @default_sheet unless sheet
  read_cells(sheet) unless @cells_read[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

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



62
63
64
65
66
67
68
69
70
71
 
# File 'lib/roo/excel2003xml.rb', line 62

def formula(row,col,sheet=nil)
  sheet = @default_sheet unless sheet
  read_cells(sheet) unless @cells_read[sheet]
  row,col = normalize(row,col)
  if @formula[sheet][[row,col]] == nil
    return nil
  else
    return @formula[sheet][[row,col]]["oooc:".length..-1]
  end
end

#formula?(row, col, sheet = nil) ⇒ Boolean

true, if there is a formula

Returns:

  • (Boolean) 


74
75
76
77
78
79
 
# File 'lib/roo/excel2003xml.rb', line 74

def formula?(row,col,sheet=nil)
  sheet = @default_sheet unless sheet
  read_cells(sheet) unless @cells_read[sheet]
  row,col = normalize(row,col)
  formula(row,col) != nil
end

#formulas(sheet = nil) ⇒ Object

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

row, col, formula 


173
174
175
176
177
178
179
180
181
182
183
184
185
186
 
# File 'lib/roo/excel2003xml.rb', line 173

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

#officeversion ⇒ Object

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



153
154
155
156
 
# File 'lib/roo/excel2003xml.rb', line 153

def officeversion
  oo_version
  @officeversion
end

#save ⇒ Object

save spreadsheet



167
168
169
 
# File 'lib/roo/excel2003xml.rb', line 167

def save #:nodoc:
  42
end

#set(row, col, value, sheet = nil) ⇒ Object

set a cell to a certain value (this will not be saved back to the spreadsheet file!)



108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
 
# File 'lib/roo/excel2003xml.rb', line 108

def set(row,col,value,sheet=nil) #:nodoc:
  sheet = @default_sheet unless sheet
  read_cells(sheet) unless @cells_read[sheet]
  row,col = normalize(row,col)
  set_value(row,col,value,sheet)
  if value.class == Fixnum
    set_type(row,col,:float,sheet)
  elsif value.class == String
    set_type(row,col,:string,sheet)
  elsif value.class == Float
    set_type(row,col,:string,sheet)
  else
    raise ArgumentError, "Type for "+value.to_s+" not set"
  end
end

#sheets ⇒ Object 



143
144
145
146
147
148
149
 
# File 'lib/roo/excel2003xml.rb', line 143

def sheets
  return_sheets = []
  @doc.find("//ss:Worksheet").each do |sheet|
    return_sheets << sheet.attributes['Name']
  end
  return_sheets
end

#to_s(sheet = nil) ⇒ Object

shows the internal representation of all cells mainly for debugging purposes



160
161
162
163
164
 
# File 'lib/roo/excel2003xml.rb', line 160

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