Module: RubyXL::WorksheetConvenienceMethods

Included in:
Worksheet
Defined in:
lib/rubyXL/convenience_methods.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

#change_column_alignment(column_index, &block) ⇒ Object 



803
804
805
806
807
808
809
810
811
812
813
814
815
816
 
# File 'lib/rubyXL/convenience_methods.rb', line 803

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



736
737
738
739
740
741
 
# File 'lib/rubyXL/convenience_methods.rb', line 736

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 



765
766
767
768
769
770
771
772
773
774
775
 
# File 'lib/rubyXL/convenience_methods.rb', line 765

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|
    c = row.cells[column_index]
    c.change_border(direction, weight) unless c.nil?
  }
end

#change_column_border_color(column_index, direction, color) ⇒ Object 



777
778
779
780
781
782
783
784
785
786
787
788
 
# File 'lib/rubyXL/convenience_methods.rb', line 777

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 



533
534
535
536
537
538
539
540
541
542
543
544
 
# File 'lib/rubyXL/convenience_methods.rb', line 533

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|
    c = row[column_index]
    c.change_fill(color_code) if c
  }
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



693
694
695
696
697
698
699
700
701
702
703
704
 
# File 'lib/rubyXL/convenience_methods.rb', line 693

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 



720
721
722
723
724
725
726
727
 
# File 'lib/rubyXL/convenience_methods.rb', line 720

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 



706
707
708
709
710
711
 
# File 'lib/rubyXL/convenience_methods.rb', line 706

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 



713
714
715
716
717
718
 
# File 'lib/rubyXL/convenience_methods.rb', line 713

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 



757
758
759
 
# File 'lib/rubyXL/convenience_methods.rb', line 757

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 



729
730
731
732
733
734
 
# File 'lib/rubyXL/convenience_methods.rb', line 729

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 



750
751
752
753
754
755
 
# File 'lib/rubyXL/convenience_methods.rb', line 750

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 



743
744
745
746
747
748
 
# File 'lib/rubyXL/convenience_methods.rb', line 743

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 



761
762
763
 
# File 'lib/rubyXL/convenience_methods.rb', line 761

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



516
517
518
 
# File 'lib/rubyXL/convenience_methods.rb', line 516

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



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

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 



790
791
792
793
794
795
796
797
798
799
800
801
 
# File 'lib/rubyXL/convenience_methods.rb', line 790

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 



661
662
663
664
665
666
 
# File 'lib/rubyXL/convenience_methods.rb', line 661

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 



587
588
589
590
591
592
593
594
595
596
 
# File 'lib/rubyXL/convenience_methods.rb', line 587

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 



598
599
600
601
602
603
604
605
606
607
608
 
# File 'lib/rubyXL/convenience_methods.rb', line 598

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 



610
611
612
613
614
615
616
617
 
# File 'lib/rubyXL/convenience_methods.rb', line 610

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



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

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 



646
647
648
649
650
651
652
 
# File 'lib/rubyXL/convenience_methods.rb', line 646

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 



632
633
634
635
636
637
 
# File 'lib/rubyXL/convenience_methods.rb', line 632

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 



639
640
641
642
643
644
 
# File 'lib/rubyXL/convenience_methods.rb', line 639

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 



682
683
684
685
686
687
688
689
 
# File 'lib/rubyXL/convenience_methods.rb', line 682

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 



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

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 



654
655
656
657
658
659
 
# File 'lib/rubyXL/convenience_methods.rb', line 654

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 



675
676
677
678
679
680
 
# File 'lib/rubyXL/convenience_methods.rb', line 675

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 



668
669
670
671
672
673
 
# File 'lib/rubyXL/convenience_methods.rb', line 668

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 



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

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 



562
563
564
565
566
 
# File 'lib/rubyXL/convenience_methods.rb', line 562

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



246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
 
# File 'lib/rubyXL/convenience_methods.rb', line 246

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|
      c = sheet_data.rows[index].cells[column_index] = sheet_data.rows[index + 1].cells[column_index]
      c.row -= 1 if c.is_a?(Cell)
    }
  else
    raise 'invalid shift option'
  end

  return old_cell
end

#delete_column(column_index = 0) ⇒ Object 



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

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

  # Delete column
  sheet_data.rows.each { |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|
    row.cells.each_with_index { |c, ci|
      c.column = ci if c.is_a?(Cell)
    }
  }

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

#delete_row(row_index = 0) ⇒ Object 



311
312
313
314
315
316
317
318
319
320
321
322
323
324
 
# File 'lib/rubyXL/convenience_methods.rb', line 311

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? }
  }

  return deleted
end

#get_col_style(column_index) ⇒ Object

Helper method to get the style index for a column



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

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 



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

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 



568
569
570
571
572
573
 
# File 'lib/rubyXL/convenience_methods.rb', line 568

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 



546
547
548
549
550
551
552
 
# File 'lib/rubyXL/convenience_methods.rb', line 546

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 



554
555
556
557
558
559
560
 
# File 'lib/rubyXL/convenience_methods.rb', line 554

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 



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

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 



463
464
465
466
 
# File 'lib/rubyXL/convenience_methods.rb', line 463

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 



453
454
455
456
 
# File 'lib/rubyXL/convenience_methods.rb', line 453

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

#get_column_font_size(col = 0) ⇒ Object 



458
459
460
461
 
# File 'lib/rubyXL/convenience_methods.rb', line 458

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



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

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



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

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 



436
437
438
439
440
441
442
443
444
445
 
# File 'lib/rubyXL/convenience_methods.rb', line 436

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 



418
419
420
421
422
423
 
# File 'lib/rubyXL/convenience_methods.rb', line 418

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 



425
426
427
428
429
430
 
# File 'lib/rubyXL/convenience_methods.rb', line 425

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 



377
378
379
 
# File 'lib/rubyXL/convenience_methods.rb', line 377

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

#get_row_font_color(row = 0) ⇒ Object 



389
390
391
392
393
 
# File 'lib/rubyXL/convenience_methods.rb', line 389

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 



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

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

#get_row_font_size(row = 0) ⇒ Object 



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

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

#get_row_height(row = 0) ⇒ Object 



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

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 



372
373
374
375
 
# File 'lib/rubyXL/convenience_methods.rb', line 372

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 



223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
 
# File 'lib/rubyXL/convenience_methods.rb', line 223

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”



328
329
330
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/convenience_methods.rb', line 328

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”



273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
 
# File 'lib/rubyXL/convenience_methods.rb', line 273

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)
    }
  }

  return new_row
end

#is_column_bolded(col = 0) ⇒ Object 



473
474
475
476
 
# File 'lib/rubyXL/convenience_methods.rb', line 473

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

#is_column_italicized(col = 0) ⇒ Object 



468
469
470
471
 
# File 'lib/rubyXL/convenience_methods.rb', line 468

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

#is_column_struckthrough(col = 0) ⇒ Object 



483
484
485
486
 
# File 'lib/rubyXL/convenience_methods.rb', line 483

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

#is_column_underlined(col = 0) ⇒ Object 



478
479
480
481
 
# File 'lib/rubyXL/convenience_methods.rb', line 478

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

#is_row_bolded(row = 0) ⇒ Object 



399
400
401
 
# File 'lib/rubyXL/convenience_methods.rb', line 399

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

#is_row_italicized(row = 0) ⇒ Object 



395
396
397
 
# File 'lib/rubyXL/convenience_methods.rb', line 395

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

#is_row_struckthrough(row = 0) ⇒ Object 



407
408
409
 
# File 'lib/rubyXL/convenience_methods.rb', line 407

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

#is_row_underlined(row = 0) ⇒ Object 



403
404
405
 
# File 'lib/rubyXL/convenience_methods.rb', line 403

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



819
820
821
822
823
824
825
 
# File 'lib/rubyXL/convenience_methods.rb', line 819

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 



432
433
434
 
# File 'lib/rubyXL/convenience_methods.rb', line 432

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