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

[View source]

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

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

[View source]

675
676
677
678
679
680
681
682
683
684
685
686
687
688
689
 
# File 'lib/rubyXL/convenience_methods/worksheet.rb', line 675

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

[View source]

606
607
608
609
610
611
 
# File 'lib/rubyXL/convenience_methods/worksheet.rb', line 606

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

[View source]

635
636
637
638
639
640
641
642
643
644
645
646
647
 
# File 'lib/rubyXL/convenience_methods/worksheet.rb', line 635

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

[View source]

649
650
651
652
653
654
655
656
657
658
659
660
 
# File 'lib/rubyXL/convenience_methods/worksheet.rb', line 649

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

[View source]

401
402
403
404
405
406
407
408
409
410
411
412
413
414
 
# File 'lib/rubyXL/convenience_methods/worksheet.rb', line 401

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

[View source]

563
564
565
566
567
568
569
570
571
572
573
574
 
# File 'lib/rubyXL/convenience_methods/worksheet.rb', line 563

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

[View source]

590
591
592
593
594
595
596
597
 
# File 'lib/rubyXL/convenience_methods/worksheet.rb', line 590

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

[View source]

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

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

[View source]

583
584
585
586
587
588
 
# File 'lib/rubyXL/convenience_methods/worksheet.rb', line 583

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

[View source]

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

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

[View source]

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

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

[View source]

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

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

[View source]

613
614
615
616
617
618
 
# File 'lib/rubyXL/convenience_methods/worksheet.rb', line 613

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

[View source]

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

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

[View source]

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

def change_column_width(column_index, width_in_chars = RubyXL::ColumnRange::DEFAULT_WIDTH)
  change_column_width_raw(column_index, RubyXL::ColumnRange::chars2raw(width_in_chars))
end

#change_column_width_raw(column_index, width) ⇒ Object

Set raw column width value

[View source]

374
375
376
377
378
379
380
 
# File 'lib/rubyXL/convenience_methods/worksheet.rb', line 374

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

[View source]

662
663
664
665
666
667
668
669
670
671
672
673
 
# File 'lib/rubyXL/convenience_methods/worksheet.rb', line 662

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

[View source]

531
532
533
534
535
536
 
# File 'lib/rubyXL/convenience_methods/worksheet.rb', line 531

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

[View source]

457
458
459
460
461
462
463
464
465
466
 
# File 'lib/rubyXL/convenience_methods/worksheet.rb', line 457

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

[View source]

468
469
470
471
472
473
474
475
476
477
478
 
# File 'lib/rubyXL/convenience_methods/worksheet.rb', line 468

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

[View source]

480
481
482
483
484
485
486
487
 
# File 'lib/rubyXL/convenience_methods/worksheet.rb', line 480

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

[View source]

492
493
494
495
496
497
498
499
500
 
# File 'lib/rubyXL/convenience_methods/worksheet.rb', line 492

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

[View source]

516
517
518
519
520
521
522
 
# File 'lib/rubyXL/convenience_methods/worksheet.rb', line 516

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

[View source]

502
503
504
505
506
507
 
# File 'lib/rubyXL/convenience_methods/worksheet.rb', line 502

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

[View source]

509
510
511
512
513
514
 
# File 'lib/rubyXL/convenience_methods/worksheet.rb', line 509

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

[View source]

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

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

[View source]

445
446
447
448
449
 
# File 'lib/rubyXL/convenience_methods/worksheet.rb', line 445

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

[View source]

524
525
526
527
528
529
 
# File 'lib/rubyXL/convenience_methods/worksheet.rb', line 524

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

[View source]

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

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

[View source]

538
539
540
541
542
543
 
# File 'lib/rubyXL/convenience_methods/worksheet.rb', line 538

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

[View source]

451
452
453
454
455
 
# File 'lib/rubyXL/convenience_methods/worksheet.rb', line 451

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

[View source]

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

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

[View source]

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

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

[View source]

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
235
236
237
238
 
# File 'lib/rubyXL/convenience_methods/worksheet.rb', line 204

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 { |row|
    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
  return unless self.merged_cells

  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

[View source]

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
156
 
# 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
  if self.merged_cells then
    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? }
  end

  return deleted
end

#get_col_style(column_index) ⇒ Object

Helper method to get the style index for a column

[View source]

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

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

[View source]

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

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

[View source]

438
439
440
441
442
443
 
# File 'lib/rubyXL/convenience_methods/worksheet.rb', line 438

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

[View source]

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

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

[View source]

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

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

[View source]

394
395
396
397
398
399
 
# File 'lib/rubyXL/convenience_methods/worksheet.rb', line 394

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

[View source]

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

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

[View source]

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

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

#get_column_font_size(col = 0) ⇒ Object

[View source]

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

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

[View source]

367
368
369
370
371
 
# File 'lib/rubyXL/convenience_methods/worksheet.rb', line 367

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

[View source]

357
358
359
360
361
362
363
 
# File 'lib/rubyXL/convenience_methods/worksheet.rb', line 357

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

[View source]

304
305
306
307
308
309
310
311
312
313
 
# File 'lib/rubyXL/convenience_methods/worksheet.rb', line 304

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

[View source]

286
287
288
289
290
291
 
# File 'lib/rubyXL/convenience_methods/worksheet.rb', line 286

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

[View source]

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

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

[View source]

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

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

#get_row_font_color(row = 0) ⇒ Object

[View source]

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

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

[View source]

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

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

#get_row_font_size(row = 0) ⇒ Object

[View source]

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

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

#get_row_height(row = 0) ⇒ Object

[View source]

279
280
281
282
283
284
 
# File 'lib/rubyXL/convenience_methods/worksheet.rb', line 279

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

[View source]

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

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

[View source]

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

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”

[View source]

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

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
  if self.merged_cells then
    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,
      )
    }
  end

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

[View source]

73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
 
# File 'lib/rubyXL/convenience_methods/worksheet.rb', line 73

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
  if self.merged_cells then
    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,
      )
    }
  end

  return new_row
end

#is_column_bolded(col = 0) ⇒ Object

[View source]

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

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

#is_column_italicized(col = 0) ⇒ Object

[View source]

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

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

#is_column_struckthrough(col = 0) ⇒ Object

[View source]

351
352
353
354
 
# File 'lib/rubyXL/convenience_methods/worksheet.rb', line 351

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

#is_column_underlined(col = 0) ⇒ Object

[View source]

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

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

#is_row_bolded(row = 0) ⇒ Object

[View source]

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

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

#is_row_italicized(row = 0) ⇒ Object

[View source]

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

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

#is_row_struckthrough(row = 0) ⇒ Object

[View source]

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

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

#is_row_underlined(row = 0) ⇒ Object

[View source]

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

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

#merge_cells(*params) ⇒ Object

Merges cells within a rectangular area #merge_cells(row_from, col_from, row_to, col_to) #merge_cells(reference_string) #merge_cells(row_from:, row_to:, col_from:, col_to:)

[View source]

695
696
697
698
699
700
701
702
703
704
705
706
707
708
709
710
711
712
713
714
715
716
717
718
 
# File 'lib/rubyXL/convenience_methods/worksheet.rb', line 695

def merge_cells(*params)
  validate_workbook

  row_from = col_from = row_to = col_to = nil
  case params.size
  when 4 then row_from, col_from, row_to, col_to = params
  when 1 then
    case params.first
    when Hash then
      row_from, row_to, col_from, col_to = params.first.fetch_values(:row_from, :row_to, :col_from, :col_to)
    when String then
      from, to = params[0].split(':')
      raise ArgumentError.new("reference for merging cells must be a range") if to.nil?
      row_from, col_from = RubyXL::Reference.ref2ind(from)
      row_to, col_to = RubyXL::Reference.ref2ind(to)
    else
      raise ArgumentError.new("invalid value for #{self.class}: #{params[0].inspect}") unless params[0].is_a?(String)
    end
  end

  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(row_from, row_to, col_from, col_to))
end

#row_font(row) ⇒ Object

[View source]

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

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