Class: RubyXL::Cell

Inherits:
PrivateClass show all
Defined in:
lib/rubyXL/cell.rb

Instance Attribute Summary collapse

Class Method Summary collapse

Instance Method Summary collapse

Constructor Details

#initialize(worksheet, row, column, value = nil, formula = nil, datatype = 's', style_index = 0, fmla_attr = {}) ⇒ Cell

Returns a new instance of Cell.



7
8
9
10
11
12
13
14
15
16
17
18
 
# File 'lib/rubyXL/cell.rb', line 7

def initialize(worksheet,row,column,value=nil,formula=nil,datatype='s',style_index=0, fmla_attr={})
  @worksheet = worksheet

  @workbook = worksheet.workbook
  @row = row
  @column = column
  @datatype = datatype
  @value = value
  @formula=formula
  @style_index = style_index
  @formula_attributes = fmla_attr
end

Instance Attribute Details

#column ⇒ Object

Returns the value of attribute column.



4
5
6
 
# File 'lib/rubyXL/cell.rb', line 4

def column
  @column
end

#datatype ⇒ Object

Returns the value of attribute datatype.



4
5
6
 
# File 'lib/rubyXL/cell.rb', line 4

def datatype
  @datatype
end

#formula ⇒ Object

Returns the value of attribute formula.



4
5
6
 
# File 'lib/rubyXL/cell.rb', line 4

def formula
  @formula
end

#formula_attributes ⇒ Object (readonly)

Returns the value of attribute formula_attributes.



5
6
7
 
# File 'lib/rubyXL/cell.rb', line 5

def formula_attributes
  @formula_attributes
end

#row ⇒ Object

Returns the value of attribute row.



4
5
6
 
# File 'lib/rubyXL/cell.rb', line 4

def row
  @row
end

#style_index ⇒ Object

Returns the value of attribute style_index.



4
5
6
 
# File 'lib/rubyXL/cell.rb', line 4

def style_index
  @style_index
end

#workbook ⇒ Object (readonly)

Returns the value of attribute workbook.



5
6
7
 
# File 'lib/rubyXL/cell.rb', line 5

def workbook
  @workbook
end

#worksheet ⇒ Object

Returns the value of attribute worksheet.



4
5
6
 
# File 'lib/rubyXL/cell.rb', line 4

def worksheet
  @worksheet
end

Class Method Details

.convert_to_cell(row = 0, col = 0) ⇒ Object

returns Excel-style cell string from matrix indices



361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
 
# File 'lib/rubyXL/cell.rb', line 361

def Cell.convert_to_cell(row=0,col=0)
  row_string = (row + 1).to_s #+1 for 0 indexing
  col_string = ''

  if row < 0 || col < 0
    raise 'Invalid input: cannot convert negative numbers'
  end

  unless col == 0
    col_length = 1+Integer(Math.log(col) / Math.log(26)) #opposite of 26**
  else
    col_length = 1
  end

  1.upto(col_length) do |i|

    #for the last digit, 0 should mean A. easy way to do this.
    if i == col_length
      col+=1
    end

    if col >= 26**(col_length-i)
      int_val = col / 26**(col_length-i) #+1 for 0 indexing
      int_val += 64 #converts 1 to A, etc.

      col_string += int_val.chr

      #intval multiplier decrements by placeholder, essentially
      #a B subtracts more than an A this way.
      col -= (int_val-64)*26**(col_length-i)
    end
  end
  col_string+row_string
end

Instance Method Details

#border_bottom ⇒ Object

returns cell’s bottom border



351
352
353
 
# File 'lib/rubyXL/cell.rb', line 351

def border_bottom()
  return get_border(:bottom)
end

#border_diagonal ⇒ Object

returns cell’s diagonal border



356
357
358
 
# File 'lib/rubyXL/cell.rb', line 356

def border_diagonal()
  return get_border(:diagonal)
end

#border_left ⇒ Object

returns cell’s left border



341
342
343
 
# File 'lib/rubyXL/cell.rb', line 341

def border_left()
  return get_border(:left)
end

#border_right ⇒ Object

returns cell’s right border



346
347
348
 
# File 'lib/rubyXL/cell.rb', line 346

def border_right()
  return get_border(:right)
end

#border_top ⇒ Object

returns cell’s top border



336
337
338
 
# File 'lib/rubyXL/cell.rb', line 336

def border_top()
  return get_border(:top)
end

#change_border_bottom(weight = 'thin') ⇒ Object

changes bottom border of cell



221
222
223
 
# File 'lib/rubyXL/cell.rb', line 221

def change_border_bottom(weight='thin')
  change_border(:bottom, weight)
end

#change_border_diagonal(weight = 'thin') ⇒ Object

changes diagonal border of cell



226
227
228
 
# File 'lib/rubyXL/cell.rb', line 226

def change_border_diagonal(weight='thin')
  change_border(:diagonal, weight)
end

#change_border_left(weight = 'thin') ⇒ Object

changes left border of cell



211
212
213
 
# File 'lib/rubyXL/cell.rb', line 211

def change_border_left(weight='thin')
  change_border(:left, weight)
end

#change_border_right(weight = 'thin') ⇒ Object

changes right border of cell



216
217
218
 
# File 'lib/rubyXL/cell.rb', line 216

def change_border_right(weight='thin')
  change_border(:right, weight)
end

#change_border_top(weight = 'thin') ⇒ Object

changes top border of cell



206
207
208
 
# File 'lib/rubyXL/cell.rb', line 206

def change_border_top(weight='thin')
  change_border(:top, weight)
end

#change_contents(data, formula = nil) ⇒ Object

changes contents of cell, with formula option



231
232
233
234
235
236
237
238
239
240
241
242
 
# File 'lib/rubyXL/cell.rb', line 231

def change_contents(data, formula=nil)
  validate_worksheet
  @datatype='str'
  if data.is_a?(Date) || data.is_a?(DateTime)
    data = @workbook.date_to_num(data)
  end
  if (data.is_a?Integer) || (data.is_a?Float)
    @datatype = ''
  end
  @value=data
  @formula=formula
end

#change_fill(rgb = 'ffffff') ⇒ Object

changes fill color of cell



98
99
100
101
102
 
# File 'lib/rubyXL/cell.rb', line 98

def change_fill(rgb='ffffff')
  validate_worksheet
  Color.validate_color(rgb)
  @style_index = modify_fill(@workbook, @style_index,rgb)
end

#change_font(font) ⇒ Object

Helper method to update the font array and xf array



181
182
183
184
185
186
187
188
189
 
# File 'lib/rubyXL/cell.rb', line 181

def change_font(font)
  # Modify font array and retrieve new font id
  font_id = modify_font(@workbook, font, font_id())
  # Get copy of xf object with modified font id
  xf = deep_copy(xf_id())
  xf[:fontId] = Integer(font_id.to_i)
  # Modify xf array and retrieve new xf id
  @style_index = modify_xf(@workbook, xf)
end

#change_font_bold(bolded = false) ⇒ Object

Changes font bold settings of cell



151
152
153
154
155
156
157
158
 
# File 'lib/rubyXL/cell.rb', line 151

def change_font_bold(bolded=false)
  validate_worksheet
  # Get copy of font object with modified bold settings
  font = deep_copy(workbook.fonts[font_id().to_s][:font])
  font = modify_font_bold(font, bolded)
  # Update font and xf array
  change_font(font)
end

#change_font_color(font_color = '000000') ⇒ Object

Changes font color of cell



129
130
131
132
133
134
135
136
137
138
 
# File 'lib/rubyXL/cell.rb', line 129

def change_font_color(font_color='000000')
  validate_worksheet
  #if arg is a color name, convert to integer
  Color.validate_color(font_color)
  # Get copy of font object with modified color
  font = deep_copy(workbook.fonts[font_id().to_s][:font])
  font = modify_font_color(font, font_color.to_s)
  # Update font and xf array
  change_font(font)
end

#change_font_italics(italicized = false) ⇒ Object

Changes font italics settings of cell



141
142
143
144
145
146
147
148
 
# File 'lib/rubyXL/cell.rb', line 141

def change_font_italics(italicized=false)
  validate_worksheet
  # Get copy of font object with modified italics settings
  font = deep_copy(workbook.fonts[font_id().to_s][:font])
  font = modify_font_italics(font, italicized)
  # Update font and xf array
  change_font(font)
end

#change_font_name(font_name = 'Verdana') ⇒ Object

Changes font name of cell



105
106
107
108
109
110
111
112
 
# File 'lib/rubyXL/cell.rb', line 105

def change_font_name(font_name='Verdana')
  validate_worksheet
  # Get copy of font object with modified name
  font = deep_copy(workbook.fonts[font_id().to_s][:font])
  font[:name][:attributes][:val] = font_name.to_s
  # Update font and xf array
  change_font(font)
end

#change_font_size(font_size = 10) ⇒ Object

Changes font size of cell



115
116
117
118
119
120
121
122
123
124
125
126
 
# File 'lib/rubyXL/cell.rb', line 115

def change_font_size(font_size=10)
  validate_worksheet
  if font_size.is_a?(Integer) || font_size.is_a?(Float)
    # Get copy of font object with modified size
    font = deep_copy(workbook.fonts[font_id().to_s][:font])
    font[:sz][:attributes][:val] = font_size
    # Update font and xf array
    change_font(font)
  else
    raise 'Argument must be a number'
  end
end

#change_font_strikethrough(struckthrough = false) ⇒ Object

Changes font strikethrough settings of cell



171
172
173
174
175
176
177
178
 
# File 'lib/rubyXL/cell.rb', line 171

def change_font_strikethrough(struckthrough=false)
  validate_worksheet
  # Get copy of font object with modified strikethrough settings
  font = deep_copy(workbook.fonts[font_id().to_s][:font])
  font = modify_font_strikethrough(font, struckthrough)
  # Update font and xf array
  change_font(font)
end

#change_font_underline(underlined = false) ⇒ Object

Changes font underline settings of cell



161
162
163
164
165
166
167
168
 
# File 'lib/rubyXL/cell.rb', line 161

def change_font_underline(underlined=false)
  validate_worksheet
  # Get copy of font object with modified underline settings
  font = deep_copy(workbook.fonts[font_id().to_s][:font])
  font = modify_font_underline(font, underlined)
  # Update font and xf array
  change_font(font)
end

#change_horizontal_alignment(alignment = 'center') ⇒ Object

changes horizontal alignment of cell



192
193
194
195
196
 
# File 'lib/rubyXL/cell.rb', line 192

def change_horizontal_alignment(alignment='center')
  validate_worksheet
  validate_horizontal_alignment(alignment)
  @style_index = modify_alignment(@workbook,@style_index,true,alignment)
end

#change_vertical_alignment(alignment = 'center') ⇒ Object

changes vertical alignment of cell



199
200
201
202
203
 
# File 'lib/rubyXL/cell.rb', line 199

def change_vertical_alignment(alignment='center')
  validate_worksheet
  validate_vertical_alignment(alignment)
  @style_index = modify_alignment(@workbook,@style_index,false,alignment)
end

#fill_color ⇒ Object

returns cell’s fill color



309
310
311
312
313
 
# File 'lib/rubyXL/cell.rb', line 309

def fill_color()
  validate_worksheet
  xf = @workbook.get_style_attributes(@workbook.get_style(@style_index))
  return @workbook.get_fill_color(xf)
end

#font_color ⇒ Object

returns cell’s font color



299
300
301
302
303
304
305
306
 
# File 'lib/rubyXL/cell.rb', line 299

def font_color()
  validate_worksheet
  if @workbook.fonts[font_id()][:font][:color].nil?
    '000000' #black
  else
    @workbook.fonts[font_id()][:font][:color][:attributes][:rgb]
  end
end

#font_name ⇒ Object

returns cell’s font name



287
288
289
290
 
# File 'lib/rubyXL/cell.rb', line 287

def font_name()
  validate_worksheet
  @workbook.fonts[font_id()][:font][:name][:attributes][:val]
end

#font_size ⇒ Object

returns cell’s font size



293
294
295
296
 
# File 'lib/rubyXL/cell.rb', line 293

def font_size()
  validate_worksheet
  return @workbook.fonts[font_id()][:font][:sz][:attributes][:val]
end

#horizontal_alignment ⇒ Object

returns cell’s horizontal alignment



316
317
318
319
320
321
322
323
 
# File 'lib/rubyXL/cell.rb', line 316

def horizontal_alignment()
  validate_worksheet
  xf_obj = @workbook.get_style(@style_index)
  if xf_obj[:alignment].nil? || xf_obj[:alignment][:attributes].nil?
    return nil
  end
  xf_obj[:alignment][:attributes][:horizontal].to_s
end

#inspect ⇒ Object 



396
397
398
399
400
401
 
# File 'lib/rubyXL/cell.rb', line 396

def inspect
  str = "(#{@row},#{@column}): #{@value}" 
  str += " =#{@formula}" if @formula
  str += ", datatype = #{@datatype}, style_index = #{@style_index}"
  return str
end

#is_bolded ⇒ Object

returns if font is bolded



255
256
257
258
259
260
261
262
 
# File 'lib/rubyXL/cell.rb', line 255

def is_bolded()
  validate_worksheet
  if @workbook.fonts[font_id()][:font][:b].nil?
    false
  else
    true
  end
end

#is_date? ⇒ Boolean

Returns:

  • (Boolean) 


28
29
30
31
32
33
34
35
36
37
38
39
 
# File 'lib/rubyXL/cell.rb', line 28

def is_date?
  if !@value.is_a?(String)
    if @workbook.num_fmts
      num_fmt_id = xf_id()[:numFmtId]
      num_fmt = @workbook.num_fmts[:numFmt].select { |f| f[:attributes][:numFmtId] == num_fmt_id }[0].andand[:attributes].andand[:formatCode]
      if num_fmt && workbook.date_num_fmt?(num_fmt)
        return true
      end
    end
  end
  return false
end

#is_date_format?(num_fmt) ⇒ Boolean

Returns:

  • (Boolean) 


41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
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
 
# File 'lib/rubyXL/cell.rb', line 41

def is_date_format?(num_fmt)
  skip_chars = ['$', '-', '+', '/', '(', ')', ':', ' ']
  num_chars = ['0', '#', '?']
  non_date_formats = ['0.00E+00', '##0.0E+0', 'General', 'GENERAL', 'general', '@']
  date_chars = ['y','m','d','h','s']

  state = 0
  s = ''
  num_fmt.split(//).each do |c|
    if state == 0
      if c == '"'
        state = 1
      elsif ['\\', '_', '*'].include?(c)
        state = 2
      elsif skip_chars.include?(c)
        next
      else
        s << c
      end
    elsif state == 1
      if c == '"'
        state = 0
      end
    elsif state == 2
      state = 0
    end
  end
  s.gsub!(/\[[^\]]*\]/, '')
  if non_date_formats.include?(s)
    return false
  end
  separator = ';'
  got_sep = 0
  date_count = 0
  num_count = 0
  s.split(//).each do |c|
    if date_chars.include?(c)
      date_count += 1
    elsif num_chars.include?(c)
      num_count += 1
    elsif c == separator
      got_sep = 1
    end
  end
  if date_count > 0 && num_count == 0
    return true
  elsif num_count > 0 && date_count == 0
    return false
  elsif date_count
    # ambiguous result
  elsif got_sep == 0
    # constant result
  end
  return date_count > num_count
end

#is_italicized ⇒ Object

returns if font is italicized



245
246
247
248
249
250
251
252
 
# File 'lib/rubyXL/cell.rb', line 245

def is_italicized()
  validate_worksheet
  if @workbook.fonts[font_id()][:font][:i].nil?
    false
  else
    true
  end
end

#is_struckthrough ⇒ Object

returns if font has a strike through it



276
277
278
279
280
281
282
283
284
 
# File 'lib/rubyXL/cell.rb', line 276

def is_struckthrough()
  validate_worksheet
  xf = @workbook.get_style_attributes(@workbook.get_style(@style_index))
  if @workbook.fonts[font_id()][:font][:strike].nil?
    false
  else
    true
  end
end

#is_underlined ⇒ Object

returns if font is underlined



265
266
267
268
269
270
271
272
273
 
# File 'lib/rubyXL/cell.rb', line 265

def is_underlined()
  validate_worksheet
  xf = @workbook.get_style_attributes(@workbook.get_style(@style_index))
  if @workbook.fonts[font_id()][:font][:u].nil?
    false
  else
    true
  end
end

#value ⇒ Object 



20
21
22
23
24
25
26
 
# File 'lib/rubyXL/cell.rb', line 20

def value
  if is_date?
    return @workbook.num_to_date(@value)
  else
    return @value
  end
end

#vertical_alignment ⇒ Object

returns cell’s vertical alignment



326
327
328
329
330
331
332
333
 
# File 'lib/rubyXL/cell.rb', line 326

def vertical_alignment()
  validate_worksheet
  xf_obj = @workbook.get_style(@style_index)
  if xf_obj[:alignment].nil? || xf_obj[:alignment][:attributes].nil?
    return nil
  end
  xf_obj[:alignment][:attributes][:vertical].to_s
end