Class: Xlsxrb::WorksheetBuilder

Inherits:
Object
  • Object
show all
Defined in:
lib/xlsxrb.rb

Overview

DSL context for a single worksheet in Xlsxrb.build.

Instance Attribute Summary collapse

Instance Method Summary collapse

Constructor Details

#initialize(name) ⇒ WorksheetBuilder

Returns a new instance of WorksheetBuilder.



438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
 
# File 'lib/xlsxrb.rb', line 438

def initialize(name)
  @name = name
  @rows = []
  @columns = []
  @charts = []
  @styles = {} # { style_name => StyleBuilder }
  @style_index_map = {} # { style_name => xf_index } (populated at build time)
  @hyperlinks = []
  @auto_filter = nil
  @filter_columns = {}
  @sort_state = nil
  @data_validations = []
  @conditional_formats = []
  @tables = []
  @comments = []
  @sparkline_groups = []
  @merge_cells_ranges = []
  @freeze_pane = nil
  @split_pane = nil
  @selection = nil
  @page_margins = nil
  @page_setup = {}
  @header_footer = {}
  @print_options = {}
  @sheet_protection = nil
  @images = []
  @shapes = []
  @sheet_properties = {}
  @sheet_view = {}
  @row_breaks = []
  @col_breaks = []
end

Instance Attribute Details

#styles ⇒ Object (readonly)

Internal: returns styles for later processing by WorkbookBuilder



779
780
781
 
# File 'lib/xlsxrb.rb', line 779

def styles
  @styles
end

Instance Method Details

#add_chart(**options, &block) ⇒ Object

Add a chart to the sheet.



534
535
536
537
538
539
540
541
 
# File 'lib/xlsxrb.rb', line 534

def add_chart(**options, &block)
  if block_given?
    builder = ChartBuilder.new
    block.call(builder)
    options = builder.options.merge(options)
  end
  @charts << options
end

#add_col_break(col_index) ⇒ Object

Add a page break before a column.



740
741
742
 
# File 'lib/xlsxrb.rb', line 740

def add_col_break(col_index)
  @col_breaks << col_index
end

#add_comment(cell, text, author: "Author") ⇒ Object

Add a comment on a cell.



621
622
623
 
# File 'lib/xlsxrb.rb', line 621

def add_comment(cell, text, author: "Author")
  @comments << { cell: cell, text: text, author: author }
end

#add_conditional_format(sqref, **opts) ⇒ Object

Add a conditional formatting rule.



584
585
586
 
# File 'lib/xlsxrb.rb', line 584

def add_conditional_format(sqref, **opts)
  @conditional_formats << opts.merge(sqref: sqref)
end

#add_data_validation(sqref, **opts) ⇒ Object

Add a data validation rule.



577
578
579
 
# File 'lib/xlsxrb.rb', line 577

def add_data_validation(sqref, **opts)
  @data_validations << opts.merge(sqref: sqref)
end

#add_filter_column(col_id, filter) ⇒ Object

Add a filter column to the auto filter.



565
566
567
 
# File 'lib/xlsxrb.rb', line 565

def add_filter_column(col_id, filter)
  @filter_columns[col_id] = filter
end

#add_hyperlink(cell, url = nil, display: nil, tooltip: nil, location: nil) ⇒ Object

Add a hyperlink on a cell.



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

def add_hyperlink(cell, url = nil, display: nil, tooltip: nil, location: nil)
  link = { cell: cell }
  link[:url] = url if url
  link[:display] = display if display
  link[:tooltip] = tooltip if tooltip
  link[:location] = location if location
  @hyperlinks << link
end

#add_image(file_data, ext: "png", from_col: 0, from_row: 0, to_col: 5, to_row: 10, **opts) ⇒ Object

Insert an image from raw file data.



704
705
706
707
708
 
# File 'lib/xlsxrb.rb', line 704

def add_image(file_data, ext: "png", from_col: 0, from_row: 0, to_col: 5, to_row: 10, **opts)
  img = { file_data: file_data, ext: ext, from_col: from_col, from_row: from_row, to_col: to_col, to_row: to_row }
  img.merge!(opts)
  @images << img
end

#add_pivot_table(source_ref, row_fields:, data_fields:, col_fields: [], dest_ref: "E1", name: nil, field_names: nil, items: nil) ⇒ Object

Add a pivot table to the sheet. source_ref: data source range (e.g. "Sheet1!A1:C10") row_fields: array of 0-based field indices for row axis data_fields: array of { fld:, name:, subtotal: } hashes col_fields: array of 0-based field indices for column axis dest_ref: top-left cell for the pivot table (default "E1")



608
609
610
611
612
613
614
615
616
 
# File 'lib/xlsxrb.rb', line 608

def add_pivot_table(source_ref, row_fields:, data_fields:, col_fields: [], dest_ref: "E1", name: nil, field_names: nil, items: nil)
  @pivot_tables ||= []
  @pivot_tables << {
    source_ref: source_ref, row_fields: row_fields,
    data_fields: data_fields, col_fields: col_fields,
    dest_ref: dest_ref, name: name,
    field_names: field_names, items: items
  }
end

#add_row(values, styles: nil, height: nil, hidden: false, custom_height: false, outline_level: nil) ⇒ Object

Add a row of values to the sheet.

values

Array of cell values

styles

Hash mapping column indices to style names, or Array of style names for each column



483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
 
# File 'lib/xlsxrb.rb', line 483

def add_row(values, styles: nil, height: nil, hidden: false, custom_height: false, outline_level: nil)
  row_index = @rows.size
  cells = Array.new(values.size)
  style_lookup = styles.is_a?(Hash) || styles.is_a?(Array)

  col_index = 0
  while col_index < values.size
    val = values[col_index]
    style_name = style_lookup ? styles[col_index] : nil
    # If value is a Formula object, store it as the cell's formula
    cells[col_index] = if val.is_a?(Elements::Formula)
                         Elements::Cell.new(
                           row_index: row_index,
                           column_index: col_index,
                           value: nil,
                           formula: val,
                           style_index: style_name
                         )
                       else
                         Elements::Cell.new(
                           row_index: row_index,
                           column_index: col_index,
                           value: val,
                           style_index: style_name
                         )
                       end
    col_index += 1
  end

  @rows << Elements::Row.new(
    index: row_index,
    cells: cells,
    height: height,
    hidden: hidden,
    custom_height: custom_height || !height.nil?,
    outline_level: outline_level
  )
end

#add_row_break(row_num) ⇒ Object

Add a page break before a row.



735
736
737
 
# File 'lib/xlsxrb.rb', line 735

def add_row_break(row_num)
  @row_breaks << row_num
end

#add_shape(preset: "rect", text: nil, from_col: 0, from_row: 0, to_col: 5, to_row: 5, **opts) ⇒ Object

Add a shape to the sheet.



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

def add_shape(preset: "rect", text: nil, from_col: 0, from_row: 0, to_col: 5, to_row: 5, **opts)
  shape = { preset: preset, text: text, from_col: from_col, from_row: from_row, to_col: to_col, to_row: to_row }
  shape[:name] = opts.delete(:name) || "Shape #{@shapes.size + 1}"
  shape.merge!(opts)
  @shapes << shape
end

#add_sparkline_group(sparklines:, type: nil, **opts) ⇒ Object

Add a sparkline group to the sheet. sparklines: Array of { data_ref:, location_ref: } hashes type: "line" (default), "column", or "stacked"



630
631
632
633
634
635
 
# File 'lib/xlsxrb.rb', line 630

def add_sparkline_group(sparklines:, type: nil, **opts)
  group = { sparklines: sparklines }
  group[:type] = type if type
  group.merge!(opts)
  @sparkline_groups << group
end

#add_style(name, **opts, &block) ⇒ Object

Define a named style that can be applied to cells.



472
473
474
475
476
477
478
 
# File 'lib/xlsxrb.rb', line 472

def add_style(name, **opts, &block)
  style_builder = StyleBuilder.new(name)
  style_builder.apply_options!(**opts) unless opts.empty?
  block.call(style_builder) if block_given?
  @styles[name] = style_builder
  style_builder
end

#add_table(ref, columns:, name: nil, display_name: nil, style: nil, **opts) ⇒ Object

Add a table to the sheet.



591
592
593
594
595
596
597
598
 
# File 'lib/xlsxrb.rb', line 591

def add_table(ref, columns:, name: nil, display_name: nil, style: nil, **opts)
  tbl = { ref: ref, columns: columns }
  tbl[:name] = name if name
  tbl[:display_name] = display_name if display_name
  tbl[:style] = style if style
  tbl.merge!(opts)
  @tables << tbl
end

#build ⇒ Object 



744
745
746
747
748
749
750
751
752
753
754
755
756
757
758
759
760
761
762
763
764
765
766
767
768
769
770
771
772
773
774
775
776
 
# File 'lib/xlsxrb.rb', line 744

def build
  facade_meta = {}
  facade_meta[:hyperlinks] = @hyperlinks unless @hyperlinks.empty?
  facade_meta[:auto_filter] = @auto_filter if @auto_filter
  facade_meta[:filter_columns] = @filter_columns unless @filter_columns.empty?
  facade_meta[:sort_state] = @sort_state if @sort_state
  facade_meta[:data_validations] = @data_validations unless @data_validations.empty?
  facade_meta[:conditional_formats] = @conditional_formats unless @conditional_formats.empty?
  facade_meta[:tables] = @tables unless @tables.empty?
  facade_meta[:pivot_tables] = @pivot_tables unless (@pivot_tables || []).empty?
  facade_meta[:comments] = @comments unless @comments.empty?
  facade_meta[:sparkline_groups] = @sparkline_groups unless @sparkline_groups.empty?
  facade_meta[:merge_cells] = @merge_cells_ranges unless @merge_cells_ranges.empty?
  facade_meta[:freeze_pane] = @freeze_pane if @freeze_pane
  facade_meta[:split_pane] = @split_pane if @split_pane
  facade_meta[:selection] = @selection if @selection
  facade_meta[:page_margins] = @page_margins if @page_margins
  facade_meta[:page_setup] = @page_setup unless @page_setup.empty?
  facade_meta[:header_footer] = @header_footer unless @header_footer.empty?
  facade_meta[:print_options] = @print_options unless @print_options.empty?
  facade_meta[:sheet_protection] = @sheet_protection if @sheet_protection
  facade_meta[:images] = @images unless @images.empty?
  facade_meta[:shapes] = @shapes unless @shapes.empty?
  facade_meta[:sheet_properties] = @sheet_properties unless @sheet_properties.empty?
  facade_meta[:sheet_view] = @sheet_view unless @sheet_view.empty?
  facade_meta[:row_breaks] = @row_breaks unless @row_breaks.empty?
  facade_meta[:col_breaks] = @col_breaks unless @col_breaks.empty?

  Elements::Worksheet.new(
    name: @name, rows: @rows, columns: @columns, charts: @charts,
    unmapped_data: facade_meta.empty? ? {} : { facade: facade_meta }
  )
end

#merge_cells(range) ⇒ Object

Merge a range of cells (e.g. "A1:B2").



640
641
642
 
# File 'lib/xlsxrb.rb', line 640

def merge_cells(range)
  @merge_cells_ranges << range
end

#set_auto_filter(range) ⇒ Object

Set an auto filter range (e.g. "A1:D10"). rubocop:disable Naming/AccessorMethodName



559
560
561
 
# File 'lib/xlsxrb.rb', line 559

def set_auto_filter(range)
  @auto_filter = range
end

#set_column(index, width: nil, hidden: false, custom_width: false, outline_level: nil) ⇒ Object

Set column width for a 0-based column index.



523
524
525
526
527
528
529
530
531
 
# File 'lib/xlsxrb.rb', line 523

def set_column(index, width: nil, hidden: false, custom_width: false, outline_level: nil)
  @columns << Elements::Column.new(
    index: index,
    width: width,
    hidden: hidden,
    custom_width: custom_width || !width.nil?,
    outline_level: outline_level
  )
end

#set_freeze_pane(row: 0, col: 0) ⇒ Object

Freeze panes at the given row and column.



647
648
649
 
# File 'lib/xlsxrb.rb', line 647

def set_freeze_pane(row: 0, col: 0)
  @freeze_pane = { row: row, col: col }
end

#set_header_footer(**opts) ⇒ Object

Set header/footer text.



675
676
677
 
# File 'lib/xlsxrb.rb', line 675

def set_header_footer(**opts)
  @header_footer.merge!(opts)
end

#set_page_margins(left: nil, right: nil, top: nil, bottom: nil, header: nil, footer: nil) ⇒ Object

Set page margins (in inches).



665
666
667
 
# File 'lib/xlsxrb.rb', line 665

def set_page_margins(left: nil, right: nil, top: nil, bottom: nil, header: nil, footer: nil)
  @page_margins = { left: left, right: right, top: top, bottom: bottom, header: header, footer: footer }.compact
end

#set_page_setup(**opts) ⇒ Object

Set page setup properties.



670
671
672
 
# File 'lib/xlsxrb.rb', line 670

def set_page_setup(**opts)
  @page_setup.merge!(opts)
end

#set_print_option(name, value) ⇒ Object

Set a print option.



680
681
682
 
# File 'lib/xlsxrb.rb', line 680

def set_print_option(name, value)
  @print_options[name] = value
end

#set_selection(active_cell, sqref: nil, pane: nil) ⇒ Object

Set active cell selection.



657
658
659
660
 
# File 'lib/xlsxrb.rb', line 657

def set_selection(active_cell, sqref: nil, pane: nil)
  @selection = { active_cell: active_cell, sqref: sqref || active_cell }
  @selection[:pane] = pane if pane
end

#set_sheet_property(name, value) ⇒ Object

Set a sheet-level property (e.g. :tab_color).



723
724
725
 
# File 'lib/xlsxrb.rb', line 723

def set_sheet_property(name, value)
  @sheet_properties[name] = value
end

#set_sheet_protection(**opts) ⇒ Object

Set sheet protection options.



687
688
689
690
691
692
693
694
695
696
697
698
699
 
# File 'lib/xlsxrb.rb', line 687

def set_sheet_protection(**opts)
  normalized = opts.dup
  plain_password = normalized[:password]
  needs_hash = plain_password.is_a?(String) && !plain_password.empty? &&
               normalized[:algorithm_name].nil? && normalized[:hash_value].nil? &&
               normalized[:salt_value].nil? && normalized[:spin_count].nil? &&
               !plain_password.match?(/\A[0-9A-Fa-f]{4}\z/)
  if needs_hash
    normalized.delete(:password)
    normalized.merge!(Xlsxrb::Ooxml::Utils.hash_password(plain_password))
  end
  @sheet_protection = normalized
end

#set_sheet_view(name, value) ⇒ Object

Set a sheet view property (e.g. :show_grid_lines, :zoom_scale).



728
729
730
 
# File 'lib/xlsxrb.rb', line 728

def set_sheet_view(name, value)
  @sheet_view[name] = value
end

#set_sort_state(ref, sort_conditions, **opts) ⇒ Object

Set sort state.



570
571
572
 
# File 'lib/xlsxrb.rb', line 570

def set_sort_state(ref, sort_conditions, **opts)
  @sort_state = { ref: ref, sort_conditions: sort_conditions }.merge(opts)
end

#set_split_pane(x_split: 0, y_split: 0, top_left_cell: nil) ⇒ Object

Split panes (non-frozen).



652
653
654
 
# File 'lib/xlsxrb.rb', line 652

def set_split_pane(x_split: 0, y_split: 0, top_left_cell: nil)
  @split_pane = { x_split: x_split, y_split: y_split, top_left_cell: top_left_cell }
end