Module: RubyXL::LegacyWorksheet

Includes:
Enumerable
Included in:
Worksheet
Defined in:
lib/rubyXL/worksheet.rb

Instance Method Summary collapse

Instance Method Details

#[](row = 0) ⇒ Object

allows for easier access to sheet_data



15
16
17
 
# File 'lib/rubyXL/worksheet.rb', line 15

def [](row = 0)
  sheet_data[row]
end

#add_cell(row = 0, column = 0, data = '', formula = nil, overwrite = true) ⇒ Object 



331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
 
# File 'lib/rubyXL/worksheet.rb', line 331

def add_cell(row = 0, column = 0, data = '', formula = nil, overwrite = true)
  validate_workbook
  ensure_cell_exists(row, column)

  if overwrite || sheet_data.rows[row].cells[column].nil?
    c = RubyXL::Cell.new
    c.worksheet = self
    c.row = row
    c.column = column
    c.raw_value = data
    c.datatype = RubyXL::DataType::RAW_STRING unless formula || data.is_a?(Numeric)
    c.formula = RubyXL::Formula.new(:expression => formula) if formula
    
    range = cols && cols.find(column)
    c.style_index = sheet_data.rows[row].style_index || (range && range.style_index) || 0

    sheet_data.rows[row].cells[column] = c
  end

  add_cell_style(row, column)

  sheet_data.rows[row].cells[column]
end

#add_row(row = 0, params = {}) ⇒ Object 



325
326
327
328
329
 
# File 'lib/rubyXL/worksheet.rb', line 325

def add_row(row = 0, params = {})
  new_row = RubyXL::Row.new(params)
  new_row.worksheet = self
  sheet_data.rows[row] = new_row
end

#change_column_bold(column_index, bolded = false) ⇒ Object 



240
241
242
243
244
245
 
# File 'lib/rubyXL/worksheet.rb', line 240

def change_column_bold(column_index, bolded = false)
  xf = get_col_xf(column_index)
  font = @workbook.fonts[xf.font_id].dup
  font.set_bold(bolded)
  change_column_font(column_index, Worksheet::BOLD, bolded, font, xf)
end

#change_column_border_bottom(column_index, weight = 'thin') ⇒ Object 



309
310
311
 
# File 'lib/rubyXL/worksheet.rb', line 309

def change_column_border_bottom(column_index, weight = 'thin')
  change_column_border(column_index, :bottom, weight)
end

#change_column_border_diagonal(column_index, weight = 'thin') ⇒ Object 



313
314
315
 
# File 'lib/rubyXL/worksheet.rb', line 313

def change_column_border_diagonal(column_index, weight = 'thin')
  change_column_border(column_index, :diagonal, weight)
end

#change_column_border_left(column_index, weight = 'thin') ⇒ Object 



301
302
303
 
# File 'lib/rubyXL/worksheet.rb', line 301

def change_column_border_left(column_index, weight = 'thin')
  change_column_border(column_index, :left, weight)
end

#change_column_border_right(column_index, weight = 'thin') ⇒ Object 



305
306
307
 
# File 'lib/rubyXL/worksheet.rb', line 305

def change_column_border_right(column_index, weight = 'thin')
  change_column_border(column_index, :right, weight)
end

#change_column_border_top(column_index, weight = 'thin') ⇒ Object 



297
298
299
 
# File 'lib/rubyXL/worksheet.rb', line 297

def change_column_border_top(column_index, weight = 'thin')
  change_column_border(column_index, :top, weight)
end

#change_column_fill(column_index, color_index = 'ffffff') ⇒ Object 



276
277
278
279
280
281
282
283
284
285
286
287
 
# File 'lib/rubyXL/worksheet.rb', line 276

def change_column_fill(column_index, color_index='ffffff')
  validate_workbook
  Color.validate_color(color_index)
  ensure_cell_exists(0, column_index)

  cols.get_range(column_index).style_index = @workbook.modify_fill(get_col_style(column_index), color_index)

  sheet_data.rows.each { |row|
    c = row[column_index]
    c.change_fill(color_index) if c
  }
end

#change_column_font_color(column_index, font_color = '000000') ⇒ Object

Changes font color of column



224
225
226
227
228
229
230
231
 
# File 'lib/rubyXL/worksheet.rb', line 224

def change_column_font_color(column_index, font_color='000000')
  Color.validate_color(font_color)

  xf = get_col_xf(column_index)
  font = @workbook.fonts[xf.font_id].dup
  font.set_rgb_color(font_color)
  change_column_font(column_index, Worksheet::COLOR, font_color, font, xf)
end

#change_column_font_name(column_index = 0, font_name = 'Verdana') ⇒ Object

Changes font name of column



208
209
210
211
212
213
 
# File 'lib/rubyXL/worksheet.rb', line 208

def change_column_font_name(column_index = 0, font_name = 'Verdana')
  xf = get_col_xf(column_index)
  font = @workbook.fonts[xf.font_id].dup
  font.set_name(font_name)
  change_column_font(column_index, Worksheet::NAME, font_name, font, xf)
end

#change_column_font_size(column_index, font_size = 10) ⇒ Object

Changes font size of column



216
217
218
219
220
221
 
# File 'lib/rubyXL/worksheet.rb', line 216

def change_column_font_size(column_index, font_size=10)
  xf = get_col_xf(column_index)
  font = @workbook.fonts[xf.font_id].dup
  font.set_size(font_size)
  change_column_font(column_index, Worksheet::SIZE, font_size, font, xf)
end

#change_column_horizontal_alignment(column_index, alignment = 'center') ⇒ Object 



289
290
291
 
# File 'lib/rubyXL/worksheet.rb', line 289

def change_column_horizontal_alignment(column_index, alignment = 'center')
  change_column_alignment(column_index, alignment,true)
end

#change_column_italics(column_index, italicized = false) ⇒ Object 



233
234
235
236
237
238
 
# File 'lib/rubyXL/worksheet.rb', line 233

def change_column_italics(column_index, italicized = false)
  xf = get_col_xf(column_index)
  font = @workbook.fonts[xf.font_id].dup
  font.set_italic(italicized)
  change_column_font(column_index, Worksheet::ITALICS, italicized, font, xf)
end

#change_column_strikethrough(column_index, struckthrough = false) ⇒ Object 



254
255
256
257
258
259
 
# File 'lib/rubyXL/worksheet.rb', line 254

def change_column_strikethrough(column_index, struckthrough=false)
  xf = get_col_xf(column_index)
  font = @workbook.fonts[xf.font_id].dup
  font.set_strikethrough(struckthrough)
  change_column_font(column_index, Worksheet::STRIKETHROUGH, struckthrough, font, xf)
end

#change_column_underline(column_index, underlined = false) ⇒ Object 



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

def change_column_underline(column_index, underlined = false)
  xf = get_col_xf(column_index)
  font = @workbook.fonts[xf.font_id].dup
  font.set_underline(underlined)
  change_column_font(column_index, Worksheet::UNDERLINE, underlined, font, xf)
end

#change_column_vertical_alignment(column_index, alignment = 'center') ⇒ Object 



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

def change_column_vertical_alignment(column_index, alignment = 'center')
  change_column_alignment(column_index, alignment, false)
end

#change_column_width(column_index, width_in_chars = RubyXL::ColumnRange::DEFAULT_WIDTH) ⇒ Object

Get column width measured in number of digits, as per msdn.microsoft.com/en-us/library/documentformat.openxml.spreadsheet.column%28v=office.14%29.aspx



272
273
274
 
# File 'lib/rubyXL/worksheet.rb', line 272

def change_column_width(column_index, width_in_chars = RubyXL::ColumnRange::DEFAULT_WIDTH)
  change_column_width_raw(column_index, ((width_in_chars + (5.0 / RubyXL::Font::MAX_DIGIT_WIDTH)) * 256).to_i / 256.0)
end

#change_column_width_raw(column_index, width) ⇒ Object

Set raw column width value



262
263
264
265
266
267
268
 
# File 'lib/rubyXL/worksheet.rb', line 262

def change_column_width_raw(column_index, width)
  validate_workbook
  ensure_cell_exists(0, column_index)
  range = cols.get_range(column_index)
  range.width = width
  range.custom_width = true
end

#change_row_bold(row = 0, bolded = false) ⇒ Object 



138
139
140
141
142
143
 
# File 'lib/rubyXL/worksheet.rb', line 138

def change_row_bold(row = 0, bolded = false)
  ensure_cell_exists(row)
  font = row_font(row).dup
  font.set_bold(bolded)
  change_row_font(row, Worksheet::BOLD, bolded, font)
end

#change_row_border_bottom(row = 0, weight = 'thin') ⇒ Object 



199
200
201
 
# File 'lib/rubyXL/worksheet.rb', line 199

def change_row_border_bottom(row = 0, weight = 'thin')
  change_row_border(row, :bottom, weight)
end

#change_row_border_diagonal(row = 0, weight = 'thin') ⇒ Object 



203
204
205
 
# File 'lib/rubyXL/worksheet.rb', line 203

def change_row_border_diagonal(row = 0, weight = 'thin')
  change_row_border(row, :diagonal, weight)
end

#change_row_border_left(row = 0, weight = 'thin') ⇒ Object 



191
192
193
 
# File 'lib/rubyXL/worksheet.rb', line 191

def change_row_border_left(row = 0, weight = 'thin')
  change_row_border(row, :left, weight)
end

#change_row_border_right(row = 0, weight = 'thin') ⇒ Object 



195
196
197
 
# File 'lib/rubyXL/worksheet.rb', line 195

def change_row_border_right(row = 0, weight = 'thin')
  change_row_border(row, :right, weight)
end

#change_row_border_top(row = 0, weight = 'thin') ⇒ Object 



187
188
189
 
# File 'lib/rubyXL/worksheet.rb', line 187

def change_row_border_top(row = 0, weight = 'thin')
  change_row_border(row, :top, weight)
end

#change_row_fill(row_index = 0, rgb = 'ffffff') ⇒ Object

changes color of fill in (zer0 indexed) row



100
101
102
103
104
105
106
107
 
# File 'lib/rubyXL/worksheet.rb', line 100

def change_row_fill(row_index = 0, rgb = 'ffffff')
  validate_workbook
  ensure_cell_exists(row_index)
  Color.validate_color(rgb)

  sheet_data.rows[row_index].style_index = @workbook.modify_fill(get_row_style(row_index), rgb)
  sheet_data[row_index].cells.each { |c| c.change_fill(rgb) unless c.nil? }
end

#change_row_font_color(row = 0, font_color = '000000') ⇒ Object 



123
124
125
126
127
128
129
 
# File 'lib/rubyXL/worksheet.rb', line 123

def change_row_font_color(row = 0, font_color = '000000')
  ensure_cell_exists(row)
  Color.validate_color(font_color)
  font = row_font(row).dup
  font.set_rgb_color(font_color)
  change_row_font(row, Worksheet::COLOR, font_color, font)
end

#change_row_font_name(row = 0, font_name = 'Verdana') ⇒ Object 



109
110
111
112
113
114
 
# File 'lib/rubyXL/worksheet.rb', line 109

def change_row_font_name(row = 0, font_name = 'Verdana')
  ensure_cell_exists(row)
  font = row_font(row).dup
  font.set_name(font_name)
  change_row_font(row, Worksheet::NAME, font_name, font)
end

#change_row_font_size(row = 0, font_size = 10) ⇒ Object 



116
117
118
119
120
121
 
# File 'lib/rubyXL/worksheet.rb', line 116

def change_row_font_size(row = 0, font_size=10)
  ensure_cell_exists(row)
  font = row_font(row).dup
  font.set_size(font_size)
  change_row_font(row, Worksheet::SIZE, font_size, font)
end

#change_row_height(row = 0, height = 10) ⇒ Object 



159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
 
# File 'lib/rubyXL/worksheet.rb', line 159

def change_row_height(row = 0, height=10)
  validate_workbook
  ensure_cell_exists(row)

  if height.to_i.to_s == height.to_s
    height = Integer(height)
  elsif height.to_f.to_s == height.to_s
    height = Float(height)
  else
    raise 'You must enter a number for the height'
  end

  sheet_data.rows[row].ht = height
  sheet_data.rows[row].custom_height = true
end

#change_row_horizontal_alignment(row = 0, alignment = 'center') ⇒ Object 



175
176
177
178
179
 
# File 'lib/rubyXL/worksheet.rb', line 175

def change_row_horizontal_alignment(row = 0,alignment='center')
  validate_workbook
  validate_nonnegative(row)
  change_row_alignment(row,alignment,true)
end

#change_row_italics(row = 0, italicized = false) ⇒ Object 



131
132
133
134
135
136
 
# File 'lib/rubyXL/worksheet.rb', line 131

def change_row_italics(row = 0, italicized = false)
  ensure_cell_exists(row)
  font = row_font(row).dup
  font.set_italic(italicized)
  change_row_font(row, Worksheet::ITALICS, italicized, font)
end

#change_row_strikethrough(row = 0, struckthrough = false) ⇒ Object 



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

def change_row_strikethrough(row = 0, struckthrough=false)
  ensure_cell_exists(row)
  font = row_font(row).dup
  font.set_strikethrough(struckthrough)
  change_row_font(row, Worksheet::STRIKETHROUGH, struckthrough, font)
end

#change_row_underline(row = 0, underlined = false) ⇒ Object 



145
146
147
148
149
150
 
# File 'lib/rubyXL/worksheet.rb', line 145

def change_row_underline(row = 0, underlined=false)
  ensure_cell_exists(row)
  font = row_font(row).dup
  font.set_underline(underlined)
  change_row_font(row, Worksheet::UNDERLINE, underlined, font)
end

#change_row_vertical_alignment(row = 0, alignment = 'center') ⇒ Object 



181
182
183
184
185
 
# File 'lib/rubyXL/worksheet.rb', line 181

def change_row_vertical_alignment(row = 0,alignment='center')
  validate_workbook
  validate_nonnegative(row)
  change_row_alignment(row,alignment,false)
end

#delete_cell(row = 0, col = 0, shift = nil) ⇒ Object

by default, only sets cell to nil if :left is specified, method will shift row contents to the right of the deleted cell to the left if :up is specified, method will shift column contents below the deleted cell upward



475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
 
# File 'lib/rubyXL/worksheet.rb', line 475

def delete_cell(row = 0, col=0, shift=nil)
  validate_workbook
  validate_nonnegative(row)
  validate_nonnegative(col)

  return nil unless row_exists(row) && column_exists(col)

  cell = sheet_data[row][col]

  case shift
  when nil then
    sheet_data.rows[row].cells[col] = nil
  when :left then
    sheet_data.rows[row].delete_cell_shift_left(col)
  when :up then
    (row...(sheet_data.size - 1)).each { |index|
      c = sheet_data.rows[index].cells[col] = sheet_data.rows[index + 1].cells[col]
      c.row -= 1 if c.is_a?(Cell)
    }
  else
    raise 'invalid shift option'
  end

  return cell
end

#delete_column(column_index = 0) ⇒ Object 



405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
 
# File 'lib/rubyXL/worksheet.rb', line 405

def delete_column(column_index = 0)
  validate_workbook
  validate_nonnegative(column_index)

  return nil unless column_exists(column_index)

  sheet_data.rows.each { |row| row.cells.delete_at(column_index) }

  # Change column numbers for cells to the right of the deleted column
  sheet_data.rows.each_with_index { |row, row_index|
    row.cells.each_with_index { |c, column_index|
      c.column = column_index if c.is_a?(Cell)
    }
  }

  cols.column_ranges.each { |range| range.delete_column(column_index) }
end

#delete_row(row_index = 0) ⇒ Object 



355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
 
# File 'lib/rubyXL/worksheet.rb', line 355

def delete_row(row_index=0)
  validate_workbook
  validate_nonnegative(row_index)
  return nil unless row_exists(row_index)

  deleted = sheet_data.rows.delete_at(row_index)
  row_num = row_index+1

  # Change cell row numbers
  row_index.upto(sheet_data.size - 1) { |index|
    sheet_data[index].cells.each{ |c| c.row -= 1 unless c.nil? }
  }

  return deleted
end

#each ⇒ Object 



19
20
21
 
# File 'lib/rubyXL/worksheet.rb', line 19

def each
  sheet_data.rows.each { |row| yield(row) }
end

#extract_data(args = {}) ⇒ Object

returns 2d array of just the cell values (without style or formula information)



24
25
26
27
28
 
# File 'lib/rubyXL/worksheet.rb', line 24

def extract_data(args = {})
  sheet_data.rows.map { |row| 
    row.cells.map { |c| c && c.value(args) } unless row.nil?
  }
end

#get_column_border_bottom(col = 0) ⇒ Object 



652
653
654
 
# File 'lib/rubyXL/worksheet.rb', line 652

def get_column_border_bottom(col=0)
  get_column_border(col, :bottom)
end

#get_column_border_diagonal(col = 0) ⇒ Object 



656
657
658
 
# File 'lib/rubyXL/worksheet.rb', line 656

def get_column_border_diagonal(col=0)
  get_column_border(col, :diagonal)
end

#get_column_border_left(col = 0) ⇒ Object 



644
645
646
 
# File 'lib/rubyXL/worksheet.rb', line 644

def get_column_border_left(col=0)
  get_column_border(col, :left)
end

#get_column_border_right(col = 0) ⇒ Object 



648
649
650
 
# File 'lib/rubyXL/worksheet.rb', line 648

def get_column_border_right(col=0)
  get_column_border(col, :right)
end

#get_column_border_top(col = 0) ⇒ Object 



640
641
642
 
# File 'lib/rubyXL/worksheet.rb', line 640

def get_column_border_top(col=0)
  get_column_border(col, :top)
end

#get_column_fill(col = 0) ⇒ Object 



624
625
626
627
628
629
630
 
# File 'lib/rubyXL/worksheet.rb', line 624

def get_column_fill(col=0)
  validate_workbook
  validate_nonnegative(col)
  return nil unless column_exists(col)

  @workbook.get_fill_color(get_col_xf(col))
end

#get_column_font_color(col = 0) ⇒ Object 



581
582
583
584
 
# File 'lib/rubyXL/worksheet.rb', line 581

def get_column_font_color(col = 0)
  font = column_font(col)
  font && (font.get_rgb_color || '000000')
end

#get_column_font_name(col = 0) ⇒ Object 



571
572
573
574
 
# File 'lib/rubyXL/worksheet.rb', line 571

def get_column_font_name(col = 0)
  font = column_font(col)
  font && font.get_name
end

#get_column_font_size(col = 0) ⇒ Object 



576
577
578
579
 
# File 'lib/rubyXL/worksheet.rb', line 576

def get_column_font_size(col = 0)
  font = column_font(col)
  font && font.get_size
end

#get_column_horizontal_alignment(col = 0) ⇒ Object 



632
633
634
 
# File 'lib/rubyXL/worksheet.rb', line 632

def get_column_horizontal_alignment(col=0)
  get_column_alignment(col, :horizontal)
end

#get_column_vertical_alignment(col = 0) ⇒ Object 



636
637
638
 
# File 'lib/rubyXL/worksheet.rb', line 636

def get_column_vertical_alignment(col=0)
  get_column_alignment(col, :vertical)
end

#get_column_width(column_index = 0) ⇒ Object

Get column width measured in number of digits, as per msdn.microsoft.com/en-us/library/documentformat.openxml.spreadsheet.column%28v=office.14%29.aspx



618
619
620
621
622
 
# File 'lib/rubyXL/worksheet.rb', line 618

def get_column_width(column_index = 0)
  width = get_column_width_raw(column_index)
  return RubyXL::ColumnRange::DEFAULT_WIDTH if width.nil?
  (width - (5.0 / RubyXL::Font::MAX_DIGIT_WIDTH)).round
end

#get_column_width_raw(column_index = 0) ⇒ Object

Get raw column width value as stored in the file



607
608
609
610
611
612
613
614
 
# File 'lib/rubyXL/worksheet.rb', line 607

def get_column_width_raw(column_index = 0)
  validate_workbook
  validate_nonnegative(column_index)
  return nil unless column_exists(column_index)

  range = cols.find(column_index)
  range && range.width
end

#get_row_border_bottom(row = 0) ⇒ Object 



563
564
565
 
# File 'lib/rubyXL/worksheet.rb', line 563

def get_row_border_bottom(row = 0)
  return get_row_border(row, :bottom)
end

#get_row_border_diagonal(row = 0) ⇒ Object 



567
568
569
 
# File 'lib/rubyXL/worksheet.rb', line 567

def get_row_border_diagonal(row = 0)
  return get_row_border(row, :diagonal)
end

#get_row_border_left(row = 0) ⇒ Object 



555
556
557
 
# File 'lib/rubyXL/worksheet.rb', line 555

def get_row_border_left(row = 0)
  return get_row_border(row, :left)
end

#get_row_border_right(row = 0) ⇒ Object 



559
560
561
 
# File 'lib/rubyXL/worksheet.rb', line 559

def get_row_border_right(row = 0)
  return get_row_border(row, :right)
end

#get_row_border_top(row = 0) ⇒ Object 



551
552
553
 
# File 'lib/rubyXL/worksheet.rb', line 551

def get_row_border_top(row = 0)
  return get_row_border(row, :top)
end

#get_row_fill(row = 0) ⇒ Object 



501
502
503
 
# File 'lib/rubyXL/worksheet.rb', line 501

def get_row_fill(row = 0)
  (row = sheet_data.rows[row]) && row.get_fill_color
end

#get_row_font_color(row = 0) ⇒ Object 



513
514
515
516
517
 
# File 'lib/rubyXL/worksheet.rb', line 513

def get_row_font_color(row = 0)
  font = row_font(row)
  color = font && font.color
  color && (color.rgb || '000000')
end

#get_row_font_name(row = 0) ⇒ Object 



505
506
507
 
# File 'lib/rubyXL/worksheet.rb', line 505

def get_row_font_name(row = 0)
  (font = row_font(row)) && font.get_name
end

#get_row_font_size(row = 0) ⇒ Object 



509
510
511
 
# File 'lib/rubyXL/worksheet.rb', line 509

def get_row_font_size(row = 0)
  (font = row_font(row)) && font.get_size
end

#get_row_height(row = 0) ⇒ Object 



535
536
537
538
539
540
541
 
# File 'lib/rubyXL/worksheet.rb', line 535

def get_row_height(row = 0)
  validate_workbook
  validate_nonnegative(row)
  return nil unless row_exists(row)
  row = sheet_data.rows[row]
  row && row.ht || 13
end

#get_row_horizontal_alignment(row = 0) ⇒ Object 



543
544
545
 
# File 'lib/rubyXL/worksheet.rb', line 543

def get_row_horizontal_alignment(row = 0)
  return get_row_alignment(row, true)
end

#get_row_vertical_alignment(row = 0) ⇒ Object 



547
548
549
 
# File 'lib/rubyXL/worksheet.rb', line 547

def get_row_vertical_alignment(row = 0)
  return get_row_alignment(row, false)
end

#get_table(headers = [], opts = {}) ⇒ Object 



30
31
32
33
34
35
36
37
38
39
40
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
 
# File 'lib/rubyXL/worksheet.rb', line 30

def get_table(headers = [], opts = {})
  validate_workbook

  headers = [headers] unless headers.is_a?(Array)
  row_num = find_first_row_with_content(headers)
  return nil if row_num.nil?

  table_hash = {}
  table_hash[:table] = []

  header_row = sheet_data[row_num]
  header_row.cells.each_with_index { |header_cell, index|
    break if index>0 && !opts[:last_header].nil? && !header_row[index-1].nil? && !header_row[index-1].value.nil? && header_row[index-1].value.to_s==opts[:last_header]
    next if header_cell.nil? || header_cell.value.nil?
    header = header_cell.value.to_s
    table_hash[:sorted_headers]||=[]
    table_hash[:sorted_headers] << header
    table_hash[header] = []

    original_row = row_num + 1
    current_row = original_row

    row = sheet_data.rows[current_row]
    cell = row && row.cells[index]

    # makes array of hashes in table_hash[:table]
    # as well as hash of arrays in table_hash[header]
    table_index = current_row - original_row
    cell_test = (!cell.nil? && !cell.value.nil?)

    while cell_test || (table_hash[:table][table_index] && !table_hash[:table][table_index].empty?)
      table_hash[header] << cell.value if cell_test
      table_index = current_row - original_row

      if cell_test then
        table_hash[:table][table_index] ||= {}
        table_hash[:table][table_index][header] = cell.value 
      end

      current_row += 1
      if sheet_data.rows[current_row].nil? then
        cell = nil
      else
        cell = sheet_data.rows[current_row].cells[index]
      end
      cell_test = (!cell.nil? && !cell.value.nil?)
    end
  }

  return table_hash
end

#initialize(params = {}) ⇒ Object 



5
6
7
8
9
10
11
12
 
# File 'lib/rubyXL/worksheet.rb', line 5

def initialize(params = {})
  super
  self.workbook   = params[:workbook]
  self.sheet_name = params[:sheet_name]
  self.sheet_id   = params[:sheet_id]
  self.sheet_data = RubyXL::SheetData.new
  self.cols = RubyXL::ColumnRanges.new
end

#insert_cell(row = 0, col = 0, data = nil, formula = nil, shift = nil) ⇒ Object 



452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
 
# File 'lib/rubyXL/worksheet.rb', line 452

def insert_cell(row = 0, col = 0, data = nil, formula = nil, shift = nil)
  validate_workbook
  ensure_cell_exists(row, col)

  case shift
  when nil then # No shifting at all
  when :right then
    sheet_data.rows[row].insert_cell_shift_right(nil, col)
  when :down then
    add_row(sheet_data.size, :cells => Array.new(sheet_data.rows[row].size))
    (sheet_data.size - 1).downto(row+1) { |index|
      sheet_data.rows[index].cells[col] = sheet_data.rows[index-1].cells[col]
    }
  else
    raise 'invalid shift option'
  end

  return add_cell(row,col,data,formula)
end

#insert_column(column_index = 0) ⇒ Object

Inserts column at column_index, pushes everything right, takes styles from column to left NOTE: use of this method will break formulas which reference cells which are being “pushed right”



425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
 
# File 'lib/rubyXL/worksheet.rb', line 425

def insert_column(column_index = 0)
  validate_workbook
  ensure_cell_exists(0, column_index)

  old_range = cols.get_range(column_index)

  #go through each cell in column
  sheet_data.rows.each_with_index { |row, row_index|
    old_cell = row[column_index]
    c = nil

    if old_cell && old_cell.style_index != 0 &&
         old_range && old_range.style_index != old_cell.style_index then

      c = RubyXL::Cell.new(:style_index => old_cell.style_index, :worksheet => self,
                           :row => row_index, :column => column_index,
                           :datatype => RubyXL::DataType::SHARED_STRING)
    end

    row.insert_cell_shift_right(c, column_index)
  }

  cols.insert_column(column_index)

  # TODO: update column numbers
end

#insert_row(row_index = 0) ⇒ Object

Inserts row at row_index, pushes down, copies style from the row above (that’s what Excel 2013 does!) NOTE: use of this method will break formulas which reference cells which are being “pushed down”



373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
 
# File 'lib/rubyXL/worksheet.rb', line 373

def insert_row(row_index = 0)
  validate_workbook
  ensure_cell_exists(row_index)

  old_row = new_cells = nil

  if row_index > 0 then
    old_row = sheet_data.rows[row_index - 1]
    if old_row then
      new_cells = old_row.cells.collect { |c| 
                                          if c.nil? then nil
                                          else RubyXL::Cell.new(:style_index => c.style_index)
                                          end }
    end
  end

  row0 = sheet_data.rows[0]
  new_cells ||= Array.new((row0 && row0.cells.size) || 0)

  sheet_data.rows.insert(row_index, nil)
  new_row = add_row(row_index, :cells => new_cells, :style_index => old_row && old_row.style_index)

  # Update row values for all rows below
  row_index.upto(sheet_data.rows.size - 1) { |i|
    row = sheet_data.rows[i]
    next if row.nil?
    row.cells.each { |c| c.row = i unless c.nil? }
  }

  return new_row
end

#is_column_bolded(col = 0) ⇒ Object 



591
592
593
594
 
# File 'lib/rubyXL/worksheet.rb', line 591

def is_column_bolded(col = 0)
  font = column_font(col)
  font && font.is_bold
end

#is_column_italicized(col = 0) ⇒ Object 



586
587
588
589
 
# File 'lib/rubyXL/worksheet.rb', line 586

def is_column_italicized(col = 0)
  font = column_font(col)
  font && font.is_italic
end

#is_column_struckthrough(col = 0) ⇒ Object 



601
602
603
604
 
# File 'lib/rubyXL/worksheet.rb', line 601

def is_column_struckthrough(col = 0)
  font = column_font(col)
  font && font.is_strikethrough
end

#is_column_underlined(col = 0) ⇒ Object 



596
597
598
599
 
# File 'lib/rubyXL/worksheet.rb', line 596

def is_column_underlined(col = 0)
  font = column_font(col)
  font && font.is_underlined
end

#is_row_bolded(row = 0) ⇒ Object 



523
524
525
 
# File 'lib/rubyXL/worksheet.rb', line 523

def is_row_bolded(row = 0)
  (font = row_font(row)) && font.is_bold
end

#is_row_italicized(row = 0) ⇒ Object 



519
520
521
 
# File 'lib/rubyXL/worksheet.rb', line 519

def is_row_italicized(row = 0)
  (font = row_font(row)) && font.is_italic
end

#is_row_struckthrough(row = 0) ⇒ Object 



531
532
533
 
# File 'lib/rubyXL/worksheet.rb', line 531

def is_row_struckthrough(row = 0)
  (font = row_font(row)) && font.is_strikethrough
end

#is_row_underlined(row = 0) ⇒ Object 



527
528
529
 
# File 'lib/rubyXL/worksheet.rb', line 527

def is_row_underlined(row = 0)
  (font = row_font(row)) && font.is_underlined
end

#merge_cells(row1 = 0, col1 = 0, row2 = 0, col2 = 0) ⇒ Object

merges cells within a rectangular range



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

def merge_cells(row1 = 0, col1 = 0, row2 = 0, col2 = 0)
  validate_workbook

  self.merged_cells ||= RubyXL::MergedCells.new
  merged_cells << RubyXL::MergedCell.new(:ref => RubyXL::Reference.new(row1, row2, col1, col2))
end