Class: Roo::Excelx

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

Defined Under Namespace

Modules: Format Classes: Font

Constant Summary

Constants inherited from Base

Base::TEMP_PREFIX

Instance Attribute Summary

Attributes inherited from Base

#default_sheet, #header_line, #headers

Instance Method Summary collapse

Methods inherited from Base

#column, #each, #each_with_pagename, #empty?, #find, #first_column, #first_column_as_letter, #first_row, #info, #last_column, #last_column_as_letter, #last_row, #parse, #reload, #row, #row_with, #set, #sheet, #to_csv, #to_matrix, #to_xml, #to_yaml

Constructor Details

#initialize(filename, options = {}, deprecated_file_warning = :error) ⇒ Excelx

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


69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
# File 'lib/roo/excelx.rb', line 69

def initialize(filename, options = {}, deprecated_file_warning = :error)
  if Hash === options
    packed = options[:packed]
    file_warning = options[:file_warning] || :error
  else
    warn 'Supplying `packed` or `file_warning` as separate arguments to `Roo::Excelx.new` is deprecated. Use an options hash instead.'
    packed = options
    file_warning = deprecated_file_warning
  end

  file_type_check(filename,'.xlsx','an Excel-xlsx', file_warning, packed)
  make_tmpdir do |tmpdir|
    filename = download_uri(filename, tmpdir) if uri?(filename)
    filename = unzip(filename, tmpdir) if packed == :zip
    @filename = filename
    unless File.file?(@filename)
      raise IOError, "file #{@filename} does not exist"
    end
    @comments_files = Array.new
    @rels_files = Array.new
    extract_content(tmpdir, @filename)
    @workbook_doc = load_xml(File.join(tmpdir, "roo_workbook.xml"))
    @shared_table = []
    if File.exist?(File.join(tmpdir, 'roo_sharedStrings.xml'))
      @sharedstring_doc = load_xml(File.join(tmpdir, 'roo_sharedStrings.xml'))
      read_shared_strings(@sharedstring_doc)
    end
    @styles_table = []
    @style_definitions = Array.new # TODO: ??? { |h,k| h[k] = {} }
    if File.exist?(File.join(tmpdir, 'roo_styles.xml'))
      @styles_doc = load_xml(File.join(tmpdir, 'roo_styles.xml'))
      read_styles(@styles_doc)
    end
    @sheet_doc = load_xmls(@sheet_files)
    @comments_doc = load_xmls(@comments_files)
    @rels_doc = load_xmls(@rels_files)
  end
  super(filename, options)
  @formula = Hash.new
  @excelx_type = Hash.new
  @excelx_value = Hash.new
  @s_attribute = Hash.new # TODO: ggf. wieder entfernen nur lokal benoetigt
  @comment = Hash.new
  @comments_read = Hash.new
  @hyperlink = Hash.new
  @hyperlinks_read = Hash.new
end

Dynamic Method Handling

This class handles dynamic methods through the method_missing method

#method_missing(m, *args) ⇒ Object


117
118
119
120
121
122
123
124
125
126
127
128
129
# File 'lib/roo/excelx.rb', line 117

def method_missing(m,*args)
  # is method name a label name
  read_labels
  if @label.has_key?(m.to_s)
    sheet ||= @default_sheet
    read_cells(sheet)
    row,col = label(m.to_s)
    cell(row,col)
  else
    # call super for methods like #a1
    super
  end
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.


135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
# File 'lib/roo/excelx.rb', line 135

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)
  elsif celltype(row,col,sheet) == :datetime
    date_part,time_part = @cell[sheet][[row,col]].split(' ')
    yyyy,mm,dd = date_part.split('-')
    hh,mi,ss = time_part.split(':')
    return DateTime.civil(yyyy.to_i,mm.to_i,dd.to_i,hh.to_i,mi.to_i,ss.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


211
212
213
214
215
216
217
218
219
220
# File 'lib/roo/excelx.rb', line 211

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

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

returns the comment at (row/col) nil if there is no comment


310
311
312
313
314
315
316
317
# File 'lib/roo/excelx.rb', line 310

def comment(row,col,sheet=nil)
  sheet ||= @default_sheet
  #read_cells(sheet)
  read_comments(sheet) unless @comments_read[sheet]
  row,col = normalize(row,col)
  return nil unless @comment[sheet]
  @comment[sheet][[row,col]]
end

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

true, if there is a comment

Returns:

  • (Boolean)

320
321
322
323
324
325
326
# File 'lib/roo/excelx.rb', line 320

def comment?(row,col,sheet=nil)
  sheet ||= @default_sheet
  # read_cells(sheet)
  read_comments(sheet) unless @comments_read[sheet]
  row,col = normalize(row,col)
  comment(row,col) != nil
end

#comments(sheet = nil) ⇒ Object

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

row, col, comment

330
331
332
333
334
335
336
337
338
339
340
# File 'lib/roo/excelx.rb', line 330

def comments(sheet=nil)
  sheet ||= @default_sheet
  read_comments(sheet) unless @comments_read[sheet]
  if @comment[sheet]
    @comment[sheet].each.collect do |elem|
      [elem[0][0],elem[0][1],elem[1]]
    end
  else
    []
  end
end

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

returns the internal format of an excel cell


243
244
245
246
247
248
249
# File 'lib/roo/excelx.rb', line 243

def excelx_format(row,col,sheet=nil)
  sheet ||= @default_sheet
  read_cells(sheet)
  row,col = normalize(row,col)
  s = @s_attribute[sheet][[row,col]]
  attribute2format(s).to_s
end

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

returns the internal type of an excel cell

  • :numeric_or_formula

  • :string

Note: this is only available within the Excelx class


226
227
228
229
230
231
# File 'lib/roo/excelx.rb', line 226

def excelx_type(row,col,sheet=nil)
  sheet ||= @default_sheet
  read_cells(sheet)
  row,col = normalize(row,col)
  return @excelx_type[sheet][[row,col]]
end

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

returns the internal value of an excelx cell Note: this is only available within the Excelx class


235
236
237
238
239
240
# File 'lib/roo/excelx.rb', line 235

def excelx_value(row,col,sheet=nil)
  sheet ||= @default_sheet
  read_cells(sheet)
  row,col = normalize(row,col)
  return @excelx_value[sheet][[row,col]]
end

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

Given a cell, return the cell's style


193
194
195
196
197
198
199
200
201
# File 'lib/roo/excelx.rb', line 193

def font(row, col, sheet=nil)
  sheet ||= @default_sheet
  read_cells(sheet)
  row,col = normalize(row,col)
  s_attribute = @s_attribute[sheet][[row,col]]
  s_attribute ||= 0
  s_attribute = s_attribute.to_i
  @style_definitions[s_attribute]
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.


154
155
156
157
158
159
# File 'lib/roo/excelx.rb', line 154

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]]
end

#formulas(sheet = nil) ⇒ Object

row, col, formula

164
165
166
167
168
169
170
171
172
173
174
# File 'lib/roo/excelx.rb', line 164

def formulas(sheet=nil)
  sheet ||= @default_sheet
  read_cells(sheet)
  if @formula[sheet]
    @formula[sheet].each.collect do |elem|
      [elem[0][0], elem[0][1], elem[1]]
    end
  else
    []
  end
end

returns the hyperlink at (row/col) nil if there is no hyperlink


300
301
302
303
304
305
306
# File 'lib/roo/excelx.rb', line 300

def hyperlink(row,col,sheet=nil)
  sheet ||= @default_sheet
  read_hyperlinks(sheet) unless @hyperlinks_read[sheet]
  row,col = normalize(row,col)
  return nil unless @hyperlink[sheet]
  @hyperlink[sheet][[row,col]]
end

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

Returns:

  • (Boolean)

294
295
296
# File 'lib/roo/excelx.rb', line 294

def hyperlink?(row,col,sheet=nil)
  hyperlink(row, col, sheet) != nil
end

#label(labelname) ⇒ Object

returns the row,col values of the labelled cell (nil,nil) if label is not defined


268
269
270
271
272
273
274
275
276
277
# File 'lib/roo/excelx.rb', line 268

def label(labelname)
  read_labels
  if @label.empty? || !@label.has_key?(labelname)
    return nil,nil,nil
  else
    return @label[labelname][1].to_i,
      Roo::Base.letter_to_number(@label[labelname][2]),
      @label[labelname][0]
  end
end

#labelsObject

Returns an array which all labels. Each element is an array with

labelname, [row,col,sheetname]

281
282
283
284
285
286
287
288
289
290
291
292
# File 'lib/roo/excelx.rb', line 281

def labels
  # sheet ||= @default_sheet
  # read_cells(sheet)
  read_labels
  @label.map do |label|
    [ label[0], # name
      [ label[1][1].to_i, # row
        Roo::Base.letter_to_number(label[1][2]), # column
        label[1][0], # sheet
      ] ]
  end
end

#sheetsObject

returns an array of sheet names in the spreadsheet


252
253
254
255
256
# File 'lib/roo/excelx.rb', line 252

def sheets
  @workbook_doc.xpath("//xmlns:sheet").map do |sheet|
    sheet['name']
  end
end

#to_s(sheet = nil) ⇒ Object

shows the internal representation of all cells for debugging purposes


260
261
262
263
264
# File 'lib/roo/excelx.rb', line 260

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