Module: RubyXL::WorksheetConvenienceMethods

Defined in:
lib/rubyXL/convenience_methods/worksheet.rb

Constant Summary collapse

NAME = 
0
SIZE = 
1
COLOR = 
2
ITALICS = 
3
BOLD = 
4
UNDERLINE = 
5
STRIKETHROUGH = 
6

Instance Method Summary collapse

Instance Method Details

#add_validation_list(ref, list_arr) ⇒ Object 



696
697
698
699
700
701
702
703
704
705
706
 
# File 'lib/rubyXL/convenience_methods/worksheet.rb', line 696

def add_validation_list(ref, list_arr)
  # "Any double quote characters in the value should be escaped with another double quote.
  # If the value does not contain a comma, newline or double quote, then the String value should be returned unchanged.
  # If the value contains a comma, newline or double quote, then the String value should be returned enclosed in double quotes."
  expr = '"' + list_arr.collect{|str| str.gsub('"', '""')}.join(',') + '"'
  self.data_validations ||= RubyXL::DataValidations.new
  self.data_validations <<
    RubyXL::DataValidation.new({:sqref => RubyXL::Reference.new(ref),
                                :formula1 => RubyXL::Formula.new(:expression => expr),
                                :type => 'list'})
end

#change_column_alignment(column_index, &block) ⇒ Object 



671
672
673
674
675
676
677
678
679
680
681
682
683
684
685
 
# File 'lib/rubyXL/convenience_methods/worksheet.rb', line 671

def change_column_alignment(column_index, &block)
  validate_workbook
  ensure_cell_exists(0, column_index)

  cols.get_range(column_index).style_index = @workbook.modify_alignment(get_col_style(column_index), &block)
  # Excel gets confused if width is not explicitly set for a column that had alignment changes
  change_column_width(column_index) if get_column_width_raw(column_index).nil?

  sheet_data.rows.each { |row|
    next if row.nil?
    c = row[column_index]
    next if c.nil?
    c.style_index = @workbook.modify_alignment(c.style_index, &block)
  }
end

#change_column_bold(column_index, bolded = false) ⇒ Object 



602
603
604
605
606
607
 
# File 'lib/rubyXL/convenience_methods/worksheet.rb', line 602

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(column_index, direction, weight) ⇒ Object 



631
632
633
634
635
636
637
638
639
640
641
642
643
 
# File 'lib/rubyXL/convenience_methods/worksheet.rb', line 631

def change_column_border(column_index, direction, weight)
  validate_workbook
  ensure_cell_exists(0, column_index)

  cols.get_range(column_index).style_index = @workbook.modify_border(get_col_style(column_index), direction, weight)

  sheet_data.rows.each { |row|
    next if row.nil?
    c = row.cells[column_index]
    next if c.nil?
    c.change_border(direction, weight)
  }
end

#change_column_border_color(column_index, direction, color) ⇒ Object 



645
646
647
648
649
650
651
652
653
654
655
656
 
# File 'lib/rubyXL/convenience_methods/worksheet.rb', line 645

def change_column_border_color(column_index, direction, color)
  validate_workbook
  ensure_cell_exists(0, column_index)
  Color.validate_color(color)

  cols.get_range(column_index).style_index = @workbook.modify_border_color(get_col_style(column_index), direction, color)

  sheet_data.rows.each { |row|
    c = row.cells[column_index]
    c.change_border_color(direction, color) unless c.nil?
  }
end

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



397
398
399
400
401
402
403
404
405
406
407
408
409
410
 
# File 'lib/rubyXL/convenience_methods/worksheet.rb', line 397

def change_column_fill(column_index, color_code = 'ffffff')
  validate_workbook
  RubyXL::Color.validate_color(color_code)
  ensure_cell_exists(0, column_index)

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

  sheet_data.rows.each { |row|
    next if row.nil?
    c = row[column_index]
    next if c.nil?
    c.change_fill(color_code)
  }
end

#change_column_font(column_index, change_type, arg, font, xf) ⇒ Object

Helper method to update the fonts and cell styles array main method to change font, called from each separate font mutator method



559
560
561
562
563
564
565
566
567
568
569
570
 
# File 'lib/rubyXL/convenience_methods/worksheet.rb', line 559

def change_column_font(column_index, change_type, arg, font, xf)
  validate_workbook
  ensure_cell_exists(0, column_index)

  xf = workbook.register_new_font(font, xf)
  cols.get_range(column_index).style_index = workbook.register_new_xf(xf)

  sheet_data.rows.each { |row|
    c = row && row[column_index]
    c.font_switch(change_type, arg) unless c.nil?
  }
end

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



586
587
588
589
590
591
592
593
 
# File 'lib/rubyXL/convenience_methods/worksheet.rb', line 586

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 



572
573
574
575
576
577
 
# File 'lib/rubyXL/convenience_methods/worksheet.rb', line 572

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 



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

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 



623
624
625
 
# File 'lib/rubyXL/convenience_methods/worksheet.rb', line 623

def change_column_horizontal_alignment(column_index, alignment = 'center')
  change_column_alignment(column_index) { |a| a.horizontal = alignment }
end

#change_column_italics(column_index, italicized = false) ⇒ Object 



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

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 



616
617
618
619
620
621
 
# File 'lib/rubyXL/convenience_methods/worksheet.rb', line 616

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 



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

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 



627
628
629
 
# File 'lib/rubyXL/convenience_methods/worksheet.rb', line 627

def change_column_vertical_alignment(column_index, alignment = 'center')
  change_column_alignment(column_index) { |a| a.vertical = alignment }
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



380
381
382
 
# File 'lib/rubyXL/convenience_methods/worksheet.rb', line 380

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



370
371
372
373
374
375
376
 
# File 'lib/rubyXL/convenience_methods/worksheet.rb', line 370

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_alignment(row, &block) ⇒ Object 



658
659
660
661
662
663
664
665
666
667
668
669
 
# File 'lib/rubyXL/convenience_methods/worksheet.rb', line 658

def change_row_alignment(row, &block)
  validate_workbook
  validate_nonnegative(row)
  ensure_cell_exists(row)

  sheet_data.rows[row].style_index = @workbook.modify_alignment(get_row_style(row), &block)

  sheet_data[row].cells.each { |c|
    next if c.nil?
    c.style_index = @workbook.modify_alignment(c.style_index, &block)
  }
end

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



527
528
529
530
531
532
 
# File 'lib/rubyXL/convenience_methods/worksheet.rb', line 527

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(row, direction, weight) ⇒ Object 



453
454
455
456
457
458
459
460
461
462
 
# File 'lib/rubyXL/convenience_methods/worksheet.rb', line 453

def change_row_border(row, direction, weight)
  validate_workbook
  ensure_cell_exists(row)

  sheet_data.rows[row].style_index = @workbook.modify_border(get_row_style(row), direction, weight)

  sheet_data[row].cells.each { |c|
    c.change_border(direction, weight) unless c.nil?
  }
end

#change_row_border_color(row, direction, color = '000000') ⇒ Object 



464
465
466
467
468
469
470
471
472
473
474
 
# File 'lib/rubyXL/convenience_methods/worksheet.rb', line 464

def change_row_border_color(row, direction, color = '000000')
  validate_workbook
  ensure_cell_exists(row)
  Color.validate_color(color)

  sheet_data.rows[row].style_index = @workbook.modify_border_color(get_row_style(row), direction, color)

  sheet_data[row].cells.each { |c|
    c.change_border_color(direction, color) unless c.nil?
  }
end

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



476
477
478
479
480
481
482
483
 
# File 'lib/rubyXL/convenience_methods/worksheet.rb', line 476

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(row_index, change_type, arg, font) ⇒ Object

Helper method to update the row styles array change_type - NAME or SIZE or COLOR etc main method to change font, called from each separate font mutator method



488
489
490
491
492
493
494
495
496
 
# File 'lib/rubyXL/convenience_methods/worksheet.rb', line 488

def change_row_font(row_index, change_type, arg, font)
  validate_workbook
  ensure_cell_exists(row_index)

  xf = workbook.register_new_font(font, get_row_xf(row_index))
  row = sheet_data[row_index]
  row.style_index = workbook.register_new_xf(xf)
  row.cells.each { |c| c.font_switch(change_type, arg) unless c.nil? }
end

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



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

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 



498
499
500
501
502
503
 
# File 'lib/rubyXL/convenience_methods/worksheet.rb', line 498

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 



505
506
507
508
509
510
 
# File 'lib/rubyXL/convenience_methods/worksheet.rb', line 505

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 



548
549
550
551
552
553
554
555
 
# File 'lib/rubyXL/convenience_methods/worksheet.rb', line 548

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

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

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



441
442
443
444
445
 
# File 'lib/rubyXL/convenience_methods/worksheet.rb', line 441

def change_row_horizontal_alignment(row = 0, alignment = 'center')
  validate_workbook
  validate_nonnegative(row)
  change_row_alignment(row) { |a| a.horizontal = alignment }
end

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



520
521
522
523
524
525
 
# File 'lib/rubyXL/convenience_methods/worksheet.rb', line 520

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 



541
542
543
544
545
546
 
# File 'lib/rubyXL/convenience_methods/worksheet.rb', line 541

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 



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

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 



447
448
449
450
451
 
# File 'lib/rubyXL/convenience_methods/worksheet.rb', line 447

def change_row_vertical_alignment(row = 0, alignment = 'center')
  validate_workbook
  validate_nonnegative(row)
  change_row_alignment(row) { |a| a.vertical = alignment }
end

#column_font(col) ⇒ Object 



428
429
430
431
432
 
# File 'lib/rubyXL/convenience_methods/worksheet.rb', line 428

def column_font(col)
  validate_workbook

  @workbook.fonts[@workbook.cell_xfs[get_cols_style_index(col)].font_id]
end

#delete_cell(row_index = 0, column_index = 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



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
 
# File 'lib/rubyXL/convenience_methods/worksheet.rb', line 41

def delete_cell(row_index = 0, column_index=0, shift=nil)
  validate_workbook
  validate_nonnegative(row_index)
  validate_nonnegative(column_index)

  row = sheet_data[row_index]
  old_cell = row && row[column_index]

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

  return old_cell
end

#delete_column(column_index = 0) ⇒ Object 



202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
 
# File 'lib/rubyXL/convenience_methods/worksheet.rb', line 202

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

  # Delete column
  sheet_data.rows.each { |row| row && row.cells.delete_at(column_index) }

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

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

  # Update row number of merged cells
  self.merged_cells ||= RubyXL::MergedCells.new
  merged_cells.delete_if { |mc| mc.ref.col_range == (column_index..column_index) }
  merged_cells.each { |mc|
    next if mc.ref.col_range.last < column_index

    in_merged_cell = mc.ref.col_range.first <= column_index
    mc.ref = RubyXL::Reference.new(
      mc.ref.row_range.first,
      mc.ref.row_range.last,
      mc.ref.col_range.first - (in_merged_cell ? 0 : 1),
      mc.ref.col_range.last - 1,
    )
  }
  merged_cells.delete_if { |mc| mc.ref.single_cell? }
end

#delete_row(row_index = 0) ⇒ Object 



126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
 
# File 'lib/rubyXL/convenience_methods/worksheet.rb', line 126

def delete_row(row_index=0)
  validate_workbook
  validate_nonnegative(row_index)

  deleted = sheet_data.rows.delete_at(row_index)

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

  # Update row number of merged cells
  self.merged_cells ||= RubyXL::MergedCells.new
  merged_cells.delete_if { |mc| mc.ref.row_range == (row_index..row_index) }
  merged_cells.each { |mc|
    next if mc.ref.row_range.last < row_index

    in_merged_cell = mc.ref.row_range.first <= row_index
    mc.ref = RubyXL::Reference.new(
      mc.ref.row_range.first - (in_merged_cell ? 0 : 1),
      mc.ref.row_range.last - 1,
      mc.ref.col_range.first,
      mc.ref.col_range.last,
    )
  }
  merged_cells.delete_if { |mc| mc.ref.single_cell? }

  return deleted
end

#get_col_style(column_index) ⇒ Object

Helper method to get the style index for a column



385
386
387
388
 
# File 'lib/rubyXL/convenience_methods/worksheet.rb', line 385

def get_col_style(column_index)
  range = cols.locate_range(column_index)
  (range && range.style_index) || 0
end

#get_cols_style_index(column_index) ⇒ Object 



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

def get_cols_style_index(column_index)
  validate_nonnegative(column_index)
  range = cols.locate_range(column_index)
  (range && range.style_index) || 0
end

#get_column_alignment(col, type) ⇒ Object 



434
435
436
437
438
439
 
# File 'lib/rubyXL/convenience_methods/worksheet.rb', line 434

def get_column_alignment(col, type)
  validate_workbook

  xf = @workbook.cell_xfs[get_cols_style_index(col)]
  xf.alignment && xf.alignment.send(type)
end

#get_column_border(col, border_direction) ⇒ Object 



412
413
414
415
416
417
418
 
# File 'lib/rubyXL/convenience_methods/worksheet.rb', line 412

def get_column_border(col, border_direction)
  validate_workbook

  xf = @workbook.cell_xfs[get_cols_style_index(col)]
  border = @workbook.borders[xf.border_id]
  border && border.get_edge_style(border_direction)
end

#get_column_border_color(col, border_direction) ⇒ Object 



420
421
422
423
424
425
426
 
# File 'lib/rubyXL/convenience_methods/worksheet.rb', line 420

def get_column_border_color(col, border_direction)
  validate_workbook

  xf = @workbook.cell_xfs[get_cols_style_index(col)]
  border = @workbook.borders[xf.border_id]
  border && border.get_edge_color(border_direction)
end

#get_column_fill(col = 0) ⇒ Object 



390
391
392
393
394
395
 
# File 'lib/rubyXL/convenience_methods/worksheet.rb', line 390

def get_column_fill(col=0)
  validate_workbook
  validate_nonnegative(col)

  @workbook.get_fill_color(get_col_xf(col))
end

#get_column_font_color(col = 0) ⇒ Object 



327
328
329
330
 
# File 'lib/rubyXL/convenience_methods/worksheet.rb', line 327

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 



317
318
319
320
 
# File 'lib/rubyXL/convenience_methods/worksheet.rb', line 317

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

#get_column_font_size(col = 0) ⇒ Object 



322
323
324
325
 
# File 'lib/rubyXL/convenience_methods/worksheet.rb', line 322

def get_column_font_size(col = 0)
  font = column_font(col)
  font && font.get_size
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



363
364
365
366
367
 
# File 'lib/rubyXL/convenience_methods/worksheet.rb', line 363

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



353
354
355
356
357
358
359
 
# File 'lib/rubyXL/convenience_methods/worksheet.rb', line 353

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

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

#get_row_alignment(row, is_horizontal) ⇒ Object 



300
301
302
303
304
305
306
307
308
309
 
# File 'lib/rubyXL/convenience_methods/worksheet.rb', line 300

def get_row_alignment(row, is_horizontal)
  validate_workbook

  xf_obj = get_row_xf(row)
  return nil if xf_obj.alignment.nil?

  if is_horizontal then return xf_obj.alignment.horizontal
  else                  return xf_obj.alignment.vertical
  end
end

#get_row_border(row, border_direction) ⇒ Object 



282
283
284
285
286
287
 
# File 'lib/rubyXL/convenience_methods/worksheet.rb', line 282

def get_row_border(row, border_direction)
  validate_workbook

  border = @workbook.borders[get_row_xf(row).border_id]
  border && border.get_edge_style(border_direction)
end

#get_row_border_color(row, border_direction) ⇒ Object 



289
290
291
292
293
294
 
# File 'lib/rubyXL/convenience_methods/worksheet.rb', line 289

def get_row_border_color(row, border_direction)
  validate_workbook

  border = @workbook.borders[get_row_xf(row).border_id]
  border && border.get_edge_color(border_direction)
end

#get_row_fill(row = 0) ⇒ Object 



241
242
243
 
# File 'lib/rubyXL/convenience_methods/worksheet.rb', line 241

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

#get_row_font_color(row = 0) ⇒ Object 



253
254
255
256
257
 
# File 'lib/rubyXL/convenience_methods/worksheet.rb', line 253

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 



245
246
247
 
# File 'lib/rubyXL/convenience_methods/worksheet.rb', line 245

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

#get_row_font_size(row = 0) ⇒ Object 



249
250
251
 
# File 'lib/rubyXL/convenience_methods/worksheet.rb', line 249

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

#get_row_height(row = 0) ⇒ Object 



275
276
277
278
279
280
 
# File 'lib/rubyXL/convenience_methods/worksheet.rb', line 275

def get_row_height(row = 0)
  validate_workbook
  validate_nonnegative(row)
  row = sheet_data.rows[row]
  row && row.ht || RubyXL::Row::DEFAULT_HEIGHT
end

#get_row_style(row_index) ⇒ Object 



236
237
238
239
 
# File 'lib/rubyXL/convenience_methods/worksheet.rb', line 236

def get_row_style(row_index)
  row = sheet_data.rows[row_index]
  (row && row.style_index) || 0
end

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



12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
 
# File 'lib/rubyXL/convenience_methods/worksheet.rb', line 12

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|
      old_row = sheet_data.rows[index - 1]
      if old_row.nil? then
        sheet_data.rows[index] = nil
      else
        new_row = sheet_data.rows[index] || add_row(index)
        new_row.cells[col] = old_row.cells[col]
      end
    }
  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”



159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
 
# File 'lib/rubyXL/convenience_methods/worksheet.rb', line 159

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|
    next if row.nil? # Do not process blank rows

    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)

  # Update merged cells for all rows below
  self.merged_cells ||= RubyXL::MergedCells.new
  merged_cells.each { |mc|
    next if mc.ref.col_range.last < column_index

    in_merged_cell = mc.ref.row_range.first < column_index
    mc.ref = RubyXL::Reference.new(
      mc.ref.row_range.first,
      mc.ref.row_range.last,
      mc.ref.col_range.first + (in_merged_cell ? 0 : 1),
      mc.ref.col_range.last + 1,
    )
  }

  # 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”



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
116
117
118
119
120
121
122
123
124
 
# File 'lib/rubyXL/convenience_methods/worksheet.rb', line 74

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 nc = RubyXL::Cell.new(:style_index => c.style_index)
                         nc.worksheet = self
                         nc
                    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) { |r|
    row = sheet_data.rows[r]
    next if row.nil?
    row.cells.each_with_index { |cell, c|
      next if cell.nil?
      cell.r = RubyXL::Reference.new(r, c)
    }
  }

  # Update merged cells for all rows below
  self.merged_cells ||= RubyXL::MergedCells.new
  merged_cells.each { |mc|
    next if mc.ref.row_range.last < row_index

    in_merged_cell = mc.ref.row_range.first < row_index
    mc.ref = RubyXL::Reference.new(
      mc.ref.row_range.first + (in_merged_cell ? 0 : 1),
      mc.ref.row_range.last + 1,
      mc.ref.col_range.first,
      mc.ref.col_range.last,
    )
  }

  return new_row
end

#is_column_bolded(col = 0) ⇒ Object 



337
338
339
340
 
# File 'lib/rubyXL/convenience_methods/worksheet.rb', line 337

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

#is_column_italicized(col = 0) ⇒ Object 



332
333
334
335
 
# File 'lib/rubyXL/convenience_methods/worksheet.rb', line 332

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

#is_column_struckthrough(col = 0) ⇒ Object 



347
348
349
350
 
# File 'lib/rubyXL/convenience_methods/worksheet.rb', line 347

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

#is_column_underlined(col = 0) ⇒ Object 



342
343
344
345
 
# File 'lib/rubyXL/convenience_methods/worksheet.rb', line 342

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

#is_row_bolded(row = 0) ⇒ Object 



263
264
265
 
# File 'lib/rubyXL/convenience_methods/worksheet.rb', line 263

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

#is_row_italicized(row = 0) ⇒ Object 



259
260
261
 
# File 'lib/rubyXL/convenience_methods/worksheet.rb', line 259

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

#is_row_struckthrough(row = 0) ⇒ Object 



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

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

#is_row_underlined(row = 0) ⇒ Object 



267
268
269
 
# File 'lib/rubyXL/convenience_methods/worksheet.rb', line 267

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

#merge_cells(start_row, start_col, end_row, end_col) ⇒ Object

Merges cells within a rectangular area



688
689
690
691
692
693
694
 
# File 'lib/rubyXL/convenience_methods/worksheet.rb', line 688

def merge_cells(start_row, start_col, end_row, end_col)
  validate_workbook

  self.merged_cells ||= RubyXL::MergedCells.new
  # TODO: add validation to make sure ranges are not intersecting with existing ones
  merged_cells << RubyXL::MergedCell.new(:ref => RubyXL::Reference.new(start_row, end_row, start_col, end_col))
end

#row_font(row) ⇒ Object 



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

def row_font(row)
  (row = sheet_data.rows[row]) && row.get_font
end