Module: RubyXL::WorksheetConvenienceMethods
- Included in:
- Worksheet
- 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
- #change_column_alignment(column_index, &block) ⇒ Object
- #change_column_bold(column_index, bolded = false) ⇒ Object
- #change_column_border(column_index, direction, weight) ⇒ Object
- #change_column_border_color(column_index, direction, color) ⇒ Object
- #change_column_fill(column_index, color_code = 'ffffff') ⇒ Object
-
#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.
- #change_column_font_color(column_index, font_color = '000000') ⇒ Object
- #change_column_font_name(column_index = 0, font_name = 'Verdana') ⇒ Object
- #change_column_font_size(column_index, font_size = 10) ⇒ Object
- #change_column_horizontal_alignment(column_index, alignment = 'center') ⇒ Object
- #change_column_italics(column_index, italicized = false) ⇒ Object
- #change_column_strikethrough(column_index, struckthrough = false) ⇒ Object
- #change_column_underline(column_index, underlined = false) ⇒ Object
- #change_column_vertical_alignment(column_index, alignment = 'center') ⇒ Object
-
#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.
-
#change_column_width_raw(column_index, width) ⇒ Object
Set raw column width value.
- #change_row_alignment(row, &block) ⇒ Object
- #change_row_bold(row = 0, bolded = false) ⇒ Object
- #change_row_border(row, direction, weight) ⇒ Object
- #change_row_border_color(row, direction, color = '000000') ⇒ Object
- #change_row_fill(row_index = 0, rgb = 'ffffff') ⇒ Object
-
#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.
- #change_row_font_color(row = 0, font_color = '000000') ⇒ Object
- #change_row_font_name(row = 0, font_name = 'Verdana') ⇒ Object
- #change_row_font_size(row = 0, font_size = 10) ⇒ Object
- #change_row_height(row = 0, height = 10) ⇒ Object
- #change_row_horizontal_alignment(row = 0, alignment = 'center') ⇒ Object
- #change_row_italics(row = 0, italicized = false) ⇒ Object
- #change_row_strikethrough(row = 0, struckthrough = false) ⇒ Object
- #change_row_underline(row = 0, underlined = false) ⇒ Object
- #change_row_vertical_alignment(row = 0, alignment = 'center') ⇒ Object
- #column_font(col) ⇒ Object
-
#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.
- #delete_column(column_index = 0) ⇒ Object
- #delete_row(row_index = 0) ⇒ Object
-
#get_col_style(column_index) ⇒ Object
Helper method to get the style index for a column.
- #get_cols_style_index(column_index) ⇒ Object
- #get_column_alignment(col, type) ⇒ Object
- #get_column_border(col, border_direction) ⇒ Object
- #get_column_border_color(col, border_direction) ⇒ Object
- #get_column_fill(col = 0) ⇒ Object
- #get_column_font_color(col = 0) ⇒ Object
- #get_column_font_name(col = 0) ⇒ Object
- #get_column_font_size(col = 0) ⇒ Object
-
#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.
-
#get_column_width_raw(column_index = 0) ⇒ Object
Get raw column width value as stored in the file.
- #get_row_alignment(row, is_horizontal) ⇒ Object
- #get_row_border(row, border_direction) ⇒ Object
- #get_row_border_color(row, border_direction) ⇒ Object
- #get_row_fill(row = 0) ⇒ Object
- #get_row_font_color(row = 0) ⇒ Object
- #get_row_font_name(row = 0) ⇒ Object
- #get_row_font_size(row = 0) ⇒ Object
- #get_row_height(row = 0) ⇒ Object
- #get_row_style(row_index) ⇒ Object
- #insert_cell(row = 0, col = 0, data = nil, formula = nil, shift = nil) ⇒ Object
-
#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”. -
#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”.
- #is_column_bolded(col = 0) ⇒ Object
- #is_column_italicized(col = 0) ⇒ Object
- #is_column_struckthrough(col = 0) ⇒ Object
- #is_column_underlined(col = 0) ⇒ Object
- #is_row_bolded(row = 0) ⇒ Object
- #is_row_italicized(row = 0) ⇒ Object
- #is_row_struckthrough(row = 0) ⇒ Object
- #is_row_underlined(row = 0) ⇒ Object
-
#merge_cells(start_row, start_col, end_row, end_col) ⇒ Object
Merges cells within a rectangular area.
- #row_font(row) ⇒ Object
Instance Method Details
#change_column_alignment(column_index, &block) ⇒ Object
592 593 594 595 596 597 598 599 600 601 602 603 604 605 |
# File 'lib/rubyXL/convenience_methods/worksheet.rb', line 592 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
525 526 527 528 529 530 |
# File 'lib/rubyXL/convenience_methods/worksheet.rb', line 525 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
554 555 556 557 558 559 560 561 562 563 564 |
# File 'lib/rubyXL/convenience_methods/worksheet.rb', line 554 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
566 567 568 569 570 571 572 573 574 575 576 577 |
# File 'lib/rubyXL/convenience_methods/worksheet.rb', line 566 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
322 323 324 325 326 327 328 329 330 331 332 333 |
# File 'lib/rubyXL/convenience_methods/worksheet.rb', line 322 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
482 483 484 485 486 487 488 489 490 491 492 493 |
# File 'lib/rubyXL/convenience_methods/worksheet.rb', line 482 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
509 510 511 512 513 514 515 516 |
# File 'lib/rubyXL/convenience_methods/worksheet.rb', line 509 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
495 496 497 498 499 500 |
# File 'lib/rubyXL/convenience_methods/worksheet.rb', line 495 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
502 503 504 505 506 507 |
# File 'lib/rubyXL/convenience_methods/worksheet.rb', line 502 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
546 547 548 |
# File 'lib/rubyXL/convenience_methods/worksheet.rb', line 546 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
518 519 520 521 522 523 |
# File 'lib/rubyXL/convenience_methods/worksheet.rb', line 518 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
539 540 541 542 543 544 |
# File 'lib/rubyXL/convenience_methods/worksheet.rb', line 539 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
532 533 534 535 536 537 |
# File 'lib/rubyXL/convenience_methods/worksheet.rb', line 532 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
550 551 552 |
# File 'lib/rubyXL/convenience_methods/worksheet.rb', line 550 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
305 306 307 |
# File 'lib/rubyXL/convenience_methods/worksheet.rb', line 305 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
295 296 297 298 299 300 301 |
# File 'lib/rubyXL/convenience_methods/worksheet.rb', line 295 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
579 580 581 582 583 584 585 586 587 588 589 590 |
# File 'lib/rubyXL/convenience_methods/worksheet.rb', line 579 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
450 451 452 453 454 455 |
# File 'lib/rubyXL/convenience_methods/worksheet.rb', line 450 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
376 377 378 379 380 381 382 383 384 385 |
# File 'lib/rubyXL/convenience_methods/worksheet.rb', line 376 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
387 388 389 390 391 392 393 394 395 396 397 |
# File 'lib/rubyXL/convenience_methods/worksheet.rb', line 387 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
399 400 401 402 403 404 405 406 |
# File 'lib/rubyXL/convenience_methods/worksheet.rb', line 399 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
411 412 413 414 415 416 417 418 419 |
# File 'lib/rubyXL/convenience_methods/worksheet.rb', line 411 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
435 436 437 438 439 440 441 |
# File 'lib/rubyXL/convenience_methods/worksheet.rb', line 435 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
421 422 423 424 425 426 |
# File 'lib/rubyXL/convenience_methods/worksheet.rb', line 421 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
428 429 430 431 432 433 |
# File 'lib/rubyXL/convenience_methods/worksheet.rb', line 428 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
471 472 473 474 475 476 477 478 |
# File 'lib/rubyXL/convenience_methods/worksheet.rb', line 471 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
364 365 366 367 368 |
# File 'lib/rubyXL/convenience_methods/worksheet.rb', line 364 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
443 444 445 446 447 448 |
# File 'lib/rubyXL/convenience_methods/worksheet.rb', line 443 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
464 465 466 467 468 469 |
# File 'lib/rubyXL/convenience_methods/worksheet.rb', line 464 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
457 458 459 460 461 462 |
# File 'lib/rubyXL/convenience_methods/worksheet.rb', line 457 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
370 371 372 373 374 |
# File 'lib/rubyXL/convenience_methods/worksheet.rb', line 370 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
351 352 353 354 355 |
# File 'lib/rubyXL/convenience_methods/worksheet.rb', line 351 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
35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 |
# File 'lib/rubyXL/convenience_methods/worksheet.rb', line 35 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
144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 |
# File 'lib/rubyXL/convenience_methods/worksheet.rb', line 144 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
100 101 102 103 104 105 106 107 108 109 110 111 112 113 |
# File 'lib/rubyXL/convenience_methods/worksheet.rb', line 100 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
310 311 312 313 |
# File 'lib/rubyXL/convenience_methods/worksheet.rb', line 310 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
236 237 238 239 240 |
# File 'lib/rubyXL/convenience_methods/worksheet.rb', line 236 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
357 358 359 360 361 362 |
# File 'lib/rubyXL/convenience_methods/worksheet.rb', line 357 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
335 336 337 338 339 340 341 |
# File 'lib/rubyXL/convenience_methods/worksheet.rb', line 335 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
343 344 345 346 347 348 349 |
# File 'lib/rubyXL/convenience_methods/worksheet.rb', line 343 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
315 316 317 318 319 320 |
# File 'lib/rubyXL/convenience_methods/worksheet.rb', line 315 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
252 253 254 255 |
# File 'lib/rubyXL/convenience_methods/worksheet.rb', line 252 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
242 243 244 245 |
# File 'lib/rubyXL/convenience_methods/worksheet.rb', line 242 def get_column_font_name(col = 0) font = column_font(col) font && font.get_name end |
#get_column_font_size(col = 0) ⇒ Object
247 248 249 250 |
# File 'lib/rubyXL/convenience_methods/worksheet.rb', line 247 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
288 289 290 291 292 |
# File 'lib/rubyXL/convenience_methods/worksheet.rb', line 288 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
278 279 280 281 282 283 284 |
# File 'lib/rubyXL/convenience_methods/worksheet.rb', line 278 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
225 226 227 228 229 230 231 232 233 234 |
# File 'lib/rubyXL/convenience_methods/worksheet.rb', line 225 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
207 208 209 210 211 212 |
# File 'lib/rubyXL/convenience_methods/worksheet.rb', line 207 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
214 215 216 217 218 219 |
# File 'lib/rubyXL/convenience_methods/worksheet.rb', line 214 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
166 167 168 |
# File 'lib/rubyXL/convenience_methods/worksheet.rb', line 166 def get_row_fill(row = 0) (row = sheet_data.rows[row]) && row.get_fill_color end |
#get_row_font_color(row = 0) ⇒ Object
178 179 180 181 182 |
# File 'lib/rubyXL/convenience_methods/worksheet.rb', line 178 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
170 171 172 |
# File 'lib/rubyXL/convenience_methods/worksheet.rb', line 170 def get_row_font_name(row = 0) (font = row_font(row)) && font.get_name end |
#get_row_font_size(row = 0) ⇒ Object
174 175 176 |
# File 'lib/rubyXL/convenience_methods/worksheet.rb', line 174 def get_row_font_size(row = 0) (font = row_font(row)) && font.get_size end |
#get_row_height(row = 0) ⇒ Object
200 201 202 203 204 205 |
# File 'lib/rubyXL/convenience_methods/worksheet.rb', line 200 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
161 162 163 164 |
# File 'lib/rubyXL/convenience_methods/worksheet.rb', line 161 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 |
# 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| 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”
117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 |
# File 'lib/rubyXL/convenience_methods/worksheet.rb', line 117 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”
62 63 64 65 66 67 68 69 70 71 72 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 |
# File 'lib/rubyXL/convenience_methods/worksheet.rb', line 62 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
262 263 264 265 |
# File 'lib/rubyXL/convenience_methods/worksheet.rb', line 262 def is_column_bolded(col = 0) font = column_font(col) font && font.is_bold end |
#is_column_italicized(col = 0) ⇒ Object
257 258 259 260 |
# File 'lib/rubyXL/convenience_methods/worksheet.rb', line 257 def is_column_italicized(col = 0) font = column_font(col) font && font.is_italic end |
#is_column_struckthrough(col = 0) ⇒ Object
272 273 274 275 |
# File 'lib/rubyXL/convenience_methods/worksheet.rb', line 272 def is_column_struckthrough(col = 0) font = column_font(col) font && font.is_strikethrough end |
#is_column_underlined(col = 0) ⇒ Object
267 268 269 270 |
# File 'lib/rubyXL/convenience_methods/worksheet.rb', line 267 def is_column_underlined(col = 0) font = column_font(col) font && font.is_underlined end |
#is_row_bolded(row = 0) ⇒ Object
188 189 190 |
# File 'lib/rubyXL/convenience_methods/worksheet.rb', line 188 def is_row_bolded(row = 0) (font = row_font(row)) && font.is_bold end |
#is_row_italicized(row = 0) ⇒ Object
184 185 186 |
# File 'lib/rubyXL/convenience_methods/worksheet.rb', line 184 def is_row_italicized(row = 0) (font = row_font(row)) && font.is_italic end |
#is_row_struckthrough(row = 0) ⇒ Object
196 197 198 |
# File 'lib/rubyXL/convenience_methods/worksheet.rb', line 196 def is_row_struckthrough(row = 0) (font = row_font(row)) && font.is_strikethrough end |
#is_row_underlined(row = 0) ⇒ Object
192 193 194 |
# File 'lib/rubyXL/convenience_methods/worksheet.rb', line 192 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
608 609 610 611 612 613 614 |
# File 'lib/rubyXL/convenience_methods/worksheet.rb', line 608 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
221 222 223 |
# File 'lib/rubyXL/convenience_methods/worksheet.rb', line 221 def row_font(row) (row = sheet_data.rows[row]) && row.get_font end |