Module: RubyXL::WorksheetConvenienceMethods

Included in:
Worksheet
Defined in:
lib/rubyXL/convenience_methods.rb

Instance Method Summary collapse

Instance Method Details

#change_column_alignment(column_index, &block) ⇒ Object 



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

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 



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

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 



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

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_font_color(column_index, font_color = '000000') ⇒ Object 



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

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 



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

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 



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

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 



524
525
526
 
# File 'lib/rubyXL/convenience_methods.rb', line 524

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 



496
497
498
499
500
501
 
# File 'lib/rubyXL/convenience_methods.rb', line 496

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 



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

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 



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

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 



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

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

#change_row_alignment(row, &block) ⇒ Object 



544
545
546
547
548
549
550
551
552
553
554
555
 
# File 'lib/rubyXL/convenience_methods.rb', line 544

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 



443
444
445
446
447
448
 
# File 'lib/rubyXL/convenience_methods.rb', line 443

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 



394
395
396
397
398
399
400
401
402
403
 
# File 'lib/rubyXL/convenience_methods.rb', line 394

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_fill(row_index = 0, rgb = 'ffffff') ⇒ Object 



405
406
407
408
409
410
411
412
 
# File 'lib/rubyXL/convenience_methods.rb', line 405

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

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

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



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

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 



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

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 



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

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 



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

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 



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

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 



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

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 



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

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 



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

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 



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

def change_row_vertical_alignment(row = 0, alignment = 'center')
  validate_workbook
  validate_nonnegative(row)
  change_row_alignment(row) { |a| a.vertical = alignment }
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



210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
 
# File 'lib/rubyXL/convenience_methods.rb', line 210

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

#extract_data(args = {}) ⇒ Object

Returns 2D array of just the cell values (without style or formula information)



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

def extract_data(args = {})
  warn "[DEPRECATION] `#{__method__}` is deprecated.  Please access data directly by iterating through .sheet_data.rows"
  sheet_data.rows.map { |row|
    row.cells.map { |c| c && c.value(args) } unless row.nil?
  }
end

#get_column_alignment(col, type) ⇒ Object 



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

def get_column_alignment(col, type)
  validate_workbook
  validate_nonnegative(col)

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

#get_column_border(col, border_direction) ⇒ Object 



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

def get_column_border(col, border_direction)
  validate_workbook
  validate_nonnegative(col)

  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_fill(col = 0) ⇒ Object 



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

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 



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

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 



306
307
308
309
 
# File 'lib/rubyXL/convenience_methods.rb', line 306

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

#get_column_font_size(col = 0) ⇒ Object 



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

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



352
353
354
355
356
 
# File 'lib/rubyXL/convenience_methods.rb', line 352

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



342
343
344
345
346
347
348
 
# File 'lib/rubyXL/convenience_methods.rb', line 342

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 



284
285
286
287
288
289
290
291
292
293
294
 
# File 'lib/rubyXL/convenience_methods.rb', line 284

def get_row_alignment(row, is_horizontal)
  validate_workbook
  validate_nonnegative(row)

  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 



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

def get_row_border(row, border_direction)
  validate_workbook
  validate_nonnegative(row)

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

#get_row_fill(row = 0) ⇒ Object 



235
236
237
 
# File 'lib/rubyXL/convenience_methods.rb', line 235

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

#get_row_font_color(row = 0) ⇒ Object 



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

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 



239
240
241
 
# File 'lib/rubyXL/convenience_methods.rb', line 239

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

#get_row_font_size(row = 0) ⇒ Object 



243
244
245
 
# File 'lib/rubyXL/convenience_methods.rb', line 243

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

#get_row_height(row = 0) ⇒ Object 



269
270
271
272
273
274
 
# File 'lib/rubyXL/convenience_methods.rb', line 269

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

#get_row_horizontal_alignment(row = 0) ⇒ Object 



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

def get_row_horizontal_alignment(row = 0)
  warn "[DEPRECATION] `#{__method__}` is deprecated.  Please use `get_row_alignment` instead."
  return get_row_alignment(row, true)
end

#get_row_vertical_alignment(row = 0) ⇒ Object 



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

def get_row_vertical_alignment(row = 0)
  warn "[DEPRECATION] `#{__method__}` is deprecated.  Please use `get_row_alignment` instead."
  return get_row_alignment(row, false)
end

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



580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
 
# File 'lib/rubyXL/convenience_methods.rb', line 580

def get_table(headers = [], opts = {})
  warn "[DEPRECATION] `#{__method__}` is deprecated.  Please access data directly by iterating through .sheet_data.rows"
  validate_workbook

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

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

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

    original_row = row_num + 1
    current_row = original_row

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

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

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

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

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

  return table_hash
end

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



187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
 
# File 'lib/rubyXL/convenience_methods.rb', line 187

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

#is_column_bolded(col = 0) ⇒ Object 



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

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

#is_column_italicized(col = 0) ⇒ Object 



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

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

#is_column_struckthrough(col = 0) ⇒ Object 



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

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

#is_column_underlined(col = 0) ⇒ Object 



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

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

#is_row_bolded(row = 0) ⇒ Object 



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

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

#is_row_italicized(row = 0) ⇒ Object 



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

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

#is_row_struckthrough(row = 0) ⇒ Object 



265
266
267
 
# File 'lib/rubyXL/convenience_methods.rb', line 265

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

#is_row_underlined(row = 0) ⇒ Object 



261
262
263
 
# File 'lib/rubyXL/convenience_methods.rb', line 261

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