Class: Xlsxrb::StreamWriter

Inherits:
Object
  • Object
show all
Defined in:
lib/xlsxrb.rb,
sig/generated/xlsxrb.rbs

Overview

DSL context for Xlsxrb.generate streaming writes.

Defined Under Namespace

Classes: WorksheetProxy

Instance Attribute Summary collapse

Instance Method Summary collapse

Constructor Details

#initialize(target, strict_excel_mode: true) ⇒ StreamWriter

: (untyped target, ?strict_excel_mode: bool) -> void

Parameters:

  • target (Object)
  • strict_excel_mode: (Boolean) (defaults to: true) 


1528
1529
1530
1531
1532
1533
1534
1535
1536
1537
1538
1539
1540
1541
1542
1543
1544
1545
1546
1547
1548
1549
1550
1551
1552
1553
1554
1555
1556
1557
1558
1559
1560
1561
1562
1563
1564
1565
1566
1567
1568
1569
1570
1571
1572
1573
1574
 
# File 'lib/xlsxrb.rb', line 1528

def initialize(target, strict_excel_mode: true)
  @target = target
  @strict_excel_mode = strict_excel_mode
  @sst = []
  @sst_index = {}
  @sheets = []
  @current_sheet = nil
  @current_row_index = 0
  @tempfiles = []
  @current_tempfile = nil
  @current_row_writer = nil
  @current_columns = []
  @current_charts = []
  @current_hyperlinks = []
  @current_auto_filter = nil
  @current_filter_columns = {}
  @current_sort_state = nil
  @current_data_validations = []
  @current_conditional_formats = []
  @current_tables = []
  @current_comments = []
  @current_merge_cells = []
  @current_freeze_pane = nil
  @current_split_pane = nil
  @current_selection = nil
  @current_page_margins = nil
  @current_page_setup = {}
  @current_header_footer = {}
  @current_print_options = {}
  @current_sheet_protection = nil
  @current_images = []
  @current_shapes = []
  @current_sheet_properties = {}
  @current_sheet_view = {}
  @current_row_breaks = []
  @current_col_breaks = []
  @styles = {} # { style_name => StyleBuilder }
  @style_writer = Ooxml::Writer.new
  @style_name_to_id = {}
  # Workbook-level settings
  @defined_names = []
  @core_properties = {}
  @app_properties = {}
  @custom_properties = []
  @workbook_protection = nil
  @workbook_properties = { update_links: "never" }
end

Instance Attribute Details

#current_sheet ⇒ Object (readonly)

Returns:

  • (Object) 


1525
1526
1527
 
# File 'lib/xlsxrb.rb', line 1525

def current_sheet
  @current_sheet
end

Instance Method Details

#absolute_range(range) ⇒ Object

: (untyped range) -> untyped

Parameters:

  • range (Object)

Returns:

  • (Object) 


2665
2666
2667
2668
2669
2670
 
# File 'lib/xlsxrb.rb', line 2665

def absolute_range(range)
  # simplecov:disable
  # Edge case / untested delegation block
  range.gsub(/([A-Z]+)(\d+)/, '$\1$\2')
  # simplecov:enable
end

#app_property(name, value) ⇒ void

This method returns an undefined value.

Set an app document property.

: (Symbol name, String | Integer | Time value) -> void

Parameters:

  • name (Symbol) —

    The property name.

  • value (String, Integer, Time) —

    The property value.



2584
2585
2586
2587
2588
2589
 
# File 'lib/xlsxrb.rb', line 2584

def app_property(name, value)
  # simplecov:disable
  # Edge case / untested delegation block
  @app_properties[name] = value
  # simplecov:enable
end

#auto_filter(range) ⇒ void

This method returns an undefined value.

--- Auto Filter / Sort --- : (String range) -> void

Parameters:

  • range (String) 


2286
2287
2288
2289
 
# File 'lib/xlsxrb.rb', line 2286

def auto_filter(range)
  sheet if @current_sheet.nil?
  @current_auto_filter = range
end

#chart(**options) ⇒ void

This method returns an undefined value.

Add a chart to the current sheet. : (**untyped options) ?{ (ChartBuilder) -> void } -> void

Parameters:

  • options (Object) 


2260
2261
2262
2263
2264
2265
2266
2267
2268
2269
2270
 
# File 'lib/xlsxrb.rb', line 2260

def chart(**options)
  sheet if @current_sheet.nil?

  if block_given?
    builder = ChartBuilder.new
    yield builder
    options = builder.options.merge(options)
  end

  @current_charts << options
end

#cleanup! ⇒ void

This method returns an undefined value.

Explicitly remove any remaining tempfiles. Called via ensure block. : () -> void



2654
2655
2656
2657
2658
2659
2660
 
# File 'lib/xlsxrb.rb', line 2654

def cleanup!
  @tempfiles.each do |tmp|
    tmp.close
    tmp.unlink
  end
  @tempfiles.clear
end

#close ⇒ Object

: () -> untyped

Returns:

  • (Object) 


2620
2621
2622
2623
2624
2625
2626
2627
2628
2629
2630
2631
2632
2633
2634
2635
2636
2637
2638
2639
2640
2641
2642
2643
2644
2645
2646
2647
2648
2649
2650
 
# File 'lib/xlsxrb.rb', line 2620

def close
  raise ArgumentError, "Workbook must contain at least one sheet (Excel limitation)" if @strict_excel_mode && @sheets.empty? && @current_sheet.nil?

  Xlsxrb.in_span("StreamWriter#close") do
    flush_current_sheet

    styles_definition = {
      fonts: @style_writer.fonts.dup,
      fills: @style_writer.fills.dup,
      borders: @style_writer.borders.dup,
      xf_entries: @style_writer.xf_entries.dup,
      num_fmts: @style_writer.num_fmts.dup
    }

    resolved_names = resolve_defined_names(@defined_names, @sheets)

    Ooxml::WorkbookWriter.write(
      @target,
      sheets: @sheets,
      shared_strings: @sst,
      styles: styles_definition,
      defined_names: resolved_names.empty? ? nil : resolved_names,
      core_properties: @core_properties.empty? ? nil : @core_properties,
      app_properties: @app_properties.empty? ? nil : @app_properties,
      custom_properties: @custom_properties.empty? ? nil : @custom_properties,
      workbook_protection: @workbook_protection
    )
  end
ensure
  cleanup!
end

#column(index, width: nil, hidden: false, custom_width: false, outline_level: nil) ⇒ void

Note:

Excel's column width max is 255.

This method returns an undefined value.

Set column width for a 0-based column index. Add a column to the sheet.

: (Integer | String | Range[Integer | String] | Array[Integer | String] index, ?width: Float | Integer | nil, ?hidden: bool, ?custom_width: bool, ?outline_level: Integer | nil) -> void

Parameters:

  • index (Integer, String) —

    The column index (0-based) or letter.

  • width (Float, nil) (defaults to: nil) —

    The column width.

  • hidden (Boolean) (defaults to: false) —

    Whether the column is hidden.

  • custom_width (Boolean) (defaults to: false) —

    Whether it's a custom width.

  • outline_level (Integer, nil) (defaults to: nil) —

    The outline level.

  • width: (Float, Integer, nil) (defaults to: nil)
  • hidden: (Boolean) (defaults to: false)
  • custom_width: (Boolean) (defaults to: false)
  • outline_level: (Integer, nil) (defaults to: nil) 


2241
2242
2243
2244
2245
2246
2247
2248
2249
2250
2251
2252
2253
2254
2255
2256
 
# File 'lib/xlsxrb.rb', line 2241

def column(index, width: nil, hidden: false, custom_width: false, outline_level: nil)
  raise ArgumentError, "Column width #{width} must be between 0 and 255 characters (Excel limitation)" if @strict_excel_mode && width && (width.negative? || width > 255)

  indices = case index
            when Range, Array
              index.map { |i| Elements::Cell.column_index(i) }
            else
              [Elements::Cell.column_index(index)]
            end

  sheet if @current_sheet.nil?

  indices.each do |idx|
    @current_columns << { index: idx, width: width, hidden: hidden, custom_width: custom_width || !width.nil?, outline_level: outline_level }
  end
end

#comment(cell, text, author: "Author") ⇒ void

This method returns an undefined value.

--- Comments --- : (String | Integer cell, String text, ?author: ::String) -> void

Parameters:

  • cell (String, Integer)
  • text (String)
  • author: (::String) (defaults to: "Author") 


2344
2345
2346
2347
 
# File 'lib/xlsxrb.rb', line 2344

def comment(cell, text, author: "Author")
  sheet if @current_sheet.nil?
  @current_comments << { cell: cell, text: text, author: author }
end

#conditional_format(sqref, **opts) ⇒ void

This method returns an undefined value.

--- Conditional Formatting --- : (untyped sqref, **untyped opts) -> void

Parameters:

  • sqref (Object)
  • opts (Object) 


2312
2313
2314
2315
 
# File 'lib/xlsxrb.rb', line 2312

def conditional_format(sqref, **opts)
  sheet if @current_sheet.nil?
  @current_conditional_formats << opts.merge(sqref: sqref)
end

#core_property(name, value) ⇒ void

This method returns an undefined value.

Set a core document property.

: (Symbol name, String | Integer | Time value) -> void

Parameters:

  • name (Symbol) —

    The property name.

  • value (String, Integer, Time) —

    The property value.



2574
2575
2576
 
# File 'lib/xlsxrb.rb', line 2574

def core_property(name, value)
  @core_properties[name] = value
end

#custom_property(name, value, type: :string) ⇒ void

This method returns an undefined value.

Add a custom document property.

: (String name, String | Integer | Float | bool | Time value, ?type: ::Symbol) -> void

Parameters:

  • name (String) —

    The property name.

  • value (String, Integer, Float, Boolean, Time) —

    The property value.

  • type (Symbol) (defaults to: :string) —

    The type of property (:string, :number, :bool, :date).

  • type: (::Symbol) (defaults to: :string) 


2612
2613
2614
2615
2616
2617
 
# File 'lib/xlsxrb.rb', line 2612

def custom_property(name, value, type: :string)
  # simplecov:disable
  # Edge case / untested delegation block
  @custom_properties << { name: name, value: value, type: type }
  # simplecov:enable
end

#defined_name(name, value, sheet: nil, hidden: false) ⇒ void

This method returns an undefined value.

Add a defined name.

: (String name, String value, ?sheet: String?, ?hidden: bool) -> void

Parameters:

  • name (String) —

    The defined name.

  • value (String) —

    The formula or value.

  • sheet (String, nil) (defaults to: nil) —

    Local sheet name.

  • hidden (Boolean) (defaults to: false) —

    Whether the defined name is hidden.

  • sheet: (String, nil) (defaults to: nil)
  • hidden: (Boolean) (defaults to: false) 


2526
2527
2528
2529
2530
2531
2532
2533
 
# File 'lib/xlsxrb.rb', line 2526

def defined_name(name, value, sheet: nil, hidden: false)
  entry = { name: name, value: value, hidden: hidden }
  if sheet
    # local_sheet_id will be resolved at close time
    entry[:local_sheet_name] = sheet
  end
  @defined_names << entry
end

#filter_column(col_id, filter) ⇒ Object

: (untyped col_id, untyped filter) -> untyped

Parameters:

  • col_id (Object)
  • filter (Object)

Returns:

  • (Object) 


2292
2293
2294
2295
 
# File 'lib/xlsxrb.rb', line 2292

def filter_column(col_id, filter)
  sheet if @current_sheet.nil?
  @current_filter_columns[col_id] = filter
end

#flush_current_sheet ⇒ nil, untyped

: () -> (nil | untyped)

Returns:

  • (nil, untyped) 


2687
2688
2689
2690
2691
2692
2693
2694
2695
2696
2697
2698
2699
2700
2701
2702
2703
2704
2705
2706
2707
2708
2709
2710
2711
2712
2713
2714
2715
2716
2717
2718
2719
2720
2721
2722
2723
2724
2725
2726
2727
 
# File 'lib/xlsxrb.rb', line 2687

def flush_current_sheet
  return unless @current_sheet

  @current_tempfile.close

  sheet_data = { name: @current_sheet, rows_tmp_path: @current_tempfile.path, columns: @current_columns }
  sheet_data[:cells] = @current_cells if @current_cells && !@current_cells.empty?
  @current_cells = nil
  sheet_data[:charts] = @current_charts unless @current_charts.empty?
  sheet_data[:hyperlinks] = @current_hyperlinks unless @current_hyperlinks.empty?
  sheet_data[:auto_filter] = @current_auto_filter if @current_auto_filter
  sheet_data[:filter_columns] = @current_filter_columns unless @current_filter_columns.empty?
  sheet_data[:sort_state] = @current_sort_state if @current_sort_state
  sheet_data[:data_validations] = @current_data_validations unless @current_data_validations.empty?
  sheet_data[:conditional_formats] = @current_conditional_formats unless @current_conditional_formats.empty?
  sheet_data[:tables] = @current_tables unless @current_tables.empty?
  sheet_data[:pivot_tables] = @current_pivot_tables unless @current_pivot_tables.empty?
  sheet_data[:sparkline_groups] = @current_sparkline_groups unless @current_sparkline_groups.empty?
  sheet_data[:comments] = @current_comments unless @current_comments.empty?
  sheet_data[:merge_cells] = @current_merge_cells unless @current_merge_cells.empty?
  sheet_data[:freeze_pane] = @current_freeze_pane if @current_freeze_pane
  sheet_data[:split_pane] = @current_split_pane if @current_split_pane
  sheet_data[:selection] = @current_selection if @current_selection
  sheet_data[:page_margins] = @current_page_margins if @current_page_margins
  sheet_data[:page_setup] = @current_page_setup unless @current_page_setup.empty?
  sheet_data[:header_footer] = @current_header_footer unless @current_header_footer.empty?
  sheet_data[:print_options] = @current_print_options unless @current_print_options.empty?
  sheet_data[:sheet_protection] = @current_sheet_protection if @current_sheet_protection
  sheet_data[:images] = @current_images unless @current_images.empty?
  sheet_data[:shapes] = @current_shapes unless @current_shapes.empty?
  sheet_data[:sheet_properties] = @current_sheet_properties unless @current_sheet_properties.empty?
  sheet_data[:sheet_view] = @current_sheet_view unless @current_sheet_view.empty?
  sheet_data[:row_breaks] = @current_row_breaks unless @current_row_breaks.empty?
  sheet_data[:col_breaks] = @current_col_breaks unless @current_col_breaks.empty?
  @sheets << sheet_data

  @tempfiles << @current_tempfile
  @current_sheet = nil
  @current_tempfile = nil
  @current_row_writer = nil
end

#freeze_pane(row: 0, col: 0) ⇒ void

This method returns an undefined value.

Freeze panes at the given row and column.

: (?row: Integer, ?col: (Integer | String)) -> void

Parameters:

  • row (Integer) (defaults to: 0) —

    The row index to freeze at (0-based).

  • col (Integer, String) (defaults to: 0) —

    The column index to freeze at (0-based or letter).

  • row: (Integer) (defaults to: 0)
  • col: (Integer, String) (defaults to: 0) 


2403
2404
2405
2406
2407
 
# File 'lib/xlsxrb.rb', line 2403

def freeze_pane(row: 0, col: 0)
  col = Elements::Cell.column_index(col)
  sheet if @current_sheet.nil?
  @current_freeze_pane = { row: row, col: col }
end

#header_footer(**opts) ⇒ void

This method returns an undefined value.

: (**untyped opts) -> void

Parameters:

  • opts (Object) 


2436
2437
2438
2439
 
# File 'lib/xlsxrb.rb', line 2436

def header_footer(**opts)
  sheet if @current_sheet.nil?
  @current_header_footer.merge!(opts)
end

#hyperlink(cell, url = nil, display: nil, tooltip: nil, location: nil) ⇒ void

This method returns an undefined value.

--- Hyperlinks --- : (String | Integer cell, ?String? url, ?display: String?, ?tooltip: String?, ?location: String?) -> void

Parameters:

  • cell (String, Integer)
  • url (String, nil) (defaults to: nil)
  • display: (String, nil) (defaults to: nil)
  • tooltip: (String, nil) (defaults to: nil)
  • location: (String, nil) (defaults to: nil) 


2274
2275
2276
2277
2278
2279
2280
2281
2282
 
# File 'lib/xlsxrb.rb', line 2274

def hyperlink(cell, url = nil, display: nil, tooltip: nil, location: nil)
  sheet if @current_sheet.nil?
  link = { cell: cell }
  link[:url] = url if url
  link[:display] = display if display
  link[:tooltip] = tooltip if tooltip
  link[:location] = location if location
  @current_hyperlinks << link
end

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

This method returns an undefined value.

--- Images --- : (String file_data, ?ext: ::String, ?from_col: ::Integer, ?from_row: ::Integer, ?to_col: ::Integer, ?to_row: ::Integer, **untyped opts) -> void

Parameters:

  • file_data (String)
  • ext: (::String) (defaults to: "png")
  • from_col: (::Integer) (defaults to: 0)
  • from_row: (::Integer) (defaults to: 0)
  • to_col: (::Integer) (defaults to: 5)
  • to_row: (::Integer) (defaults to: 10)
  • opts (Object) 


2466
2467
2468
2469
2470
2471
 
# File 'lib/xlsxrb.rb', line 2466

def image(file_data, ext: "png", from_col: 0, from_row: 0, to_col: 5, to_row: 10, **opts)
  sheet if @current_sheet.nil?
  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)
  @current_images << img
end

#internal_sheet_setup(name = nil) ⇒ void

This method returns an undefined value.

Internal: Start or switch to a named sheet (internal helper). : (?String? name) ?{ (WorksheetProxy) -> void } -> (WorksheetProxy | nil)

Parameters:

  • name (String, nil) (defaults to: nil) 


2076
2077
2078
2079
2080
2081
2082
2083
2084
2085
2086
2087
2088
2089
2090
2091
2092
2093
2094
2095
2096
2097
2098
2099
2100
2101
2102
2103
2104
2105
2106
2107
2108
2109
2110
2111
2112
2113
2114
2115
2116
2117
2118
2119
2120
2121
2122
 
# File 'lib/xlsxrb.rb', line 2076

def internal_sheet_setup(name = nil)
  flush_current_sheet
  name ||= "Sheet#{@sheets.size + 1}"
  @current_sheet = name
  @current_row_index = 0
  @current_tempfile = Tempfile.new(["xlsxrb_rows", ".xml"])
  @current_tempfile.binmode
  @current_row_writer = Ooxml::WorksheetWriter.new(@current_tempfile)
  @current_row_writer.instance_variable_set(:@started, true)

  @current_columns = []
  @current_charts = []
  @current_hyperlinks = []
  @current_auto_filter = nil
  @current_filter_columns = {}
  @current_sort_state = nil
  @current_data_validations = []
  @current_conditional_formats = []
  @current_tables = []
  @current_pivot_tables = []
  @current_sparkline_groups = []
  @current_comments = []
  @current_merge_cells = []
  @current_freeze_pane = nil
  @current_split_pane = nil
  @current_selection = nil
  @current_page_margins = nil
  @current_page_setup = {}
  @current_header_footer = {}
  @current_print_options = {}
  @current_sheet_protection = nil
  @current_images = []
  @current_shapes = []
  @current_sheet_properties = {}
  @current_sheet_view = {}
  @current_row_breaks = []
  @current_col_breaks = []
  @current_cells = {}

  return unless block_given?

  # simplecov:disable
  # Edge case / untested delegation block
  yield self
  flush_current_sheet
  # simplecov:enable
end

#merge(range = nil, row: nil, col_start: nil, col_end: nil, row_start: nil, row_end: nil) ⇒ void

This method returns an undefined value.

Merge a range of cells (e.g. "A1:B2"), or by coordinate indices.

: (?(String | Hash[Symbol, Integer | String])? range, ?row: Integer?, ?col_start: (Integer | String)?, ?col_end: (Integer | String)?, ?row_start: Integer?, ?row_end: Integer?) -> void

Parameters:

  • range (String, nil) (defaults to: nil) —

    The string range.

  • row (Integer, nil) (defaults to: nil) —

    Single row index.

  • col_start (Integer, nil) (defaults to: nil) —

    Starting column index.

  • col_end (Integer, nil) (defaults to: nil) —

    Ending column index.

  • row_start (Integer, nil) (defaults to: nil) —

    Starting row index.

  • row_end (Integer, nil) (defaults to: nil) —

    Ending row index.

  • row: (Integer, nil) (defaults to: nil)
  • col_start: (Integer, String, nil) (defaults to: nil)
  • col_end: (Integer, String, nil) (defaults to: nil)
  • row_start: (Integer, nil) (defaults to: nil)
  • row_end: (Integer, nil) (defaults to: nil) 


2369
2370
2371
2372
2373
2374
2375
2376
2377
2378
2379
2380
2381
2382
2383
2384
2385
2386
2387
2388
2389
2390
2391
2392
2393
 
# File 'lib/xlsxrb.rb', line 2369

def merge(range = nil, row: nil, col_start: nil, col_end: nil, row_start: nil, row_end: nil)
  sheet if @current_sheet.nil?
  if range.is_a?(Hash)
    row = range[:row]
    row_start = range[:row_start]
    row_end = range[:row_end]
    col_start = range[:col_start]
    col_end = range[:col_end]
    range = nil
  end

  if range
    raise ArgumentError, "Invalid merge range format: '#{range}'. Expected format like 'A1:B2'." if @strict_excel_mode && !range.match?(/^[A-Za-z]{1,3}\d+(:[A-Za-z]{1,3}\d+)?$/)

    @current_merge_cells << range
  else
    r_start = row || row_start || 0
    r_end = row || row_end || 0
    c_start = Elements::Cell.column_index(col_start || 0)
    c_end = Elements::Cell.column_index(col_end || 0)
    start_ref = "#{Xlsxrb::Elements::Cell.column_letter(c_start)}#{r_start + 1}"
    end_ref = "#{Xlsxrb::Elements::Cell.column_letter(c_end)}#{r_end + 1}"
    @current_merge_cells << "#{start_ref}:#{end_ref}"
  end
end

#page_break_col(col_index) ⇒ void

This method returns an undefined value.

: (Integer col_index) -> void

Parameters:

  • col_index (Integer) 


2507
2508
2509
2510
2511
2512
2513
2514
 
# File 'lib/xlsxrb.rb', line 2507

def page_break_col(col_index)
  # simplecov:disable
  # Edge case / untested delegation block
  col_index = Elements::Cell.column_index(col_index)
  sheet if @current_sheet.nil?
  @current_col_breaks << col_index
  # simplecov:enable
end

#page_break_row(row_num) ⇒ void

This method returns an undefined value.

--- Row / Column Breaks --- : (Integer row_num) -> void

Parameters:

  • row_num (Integer) 


2498
2499
2500
2501
2502
2503
2504
 
# File 'lib/xlsxrb.rb', line 2498

def page_break_row(row_num)
  # simplecov:disable
  # Edge case / untested delegation block
  sheet if @current_sheet.nil?
  @current_row_breaks << row_num
  # simplecov:enable
end

#page_margins(left: nil, right: nil, top: nil, bottom: nil, header: nil, footer: nil) ⇒ void

This method returns an undefined value.

--- Page Setup / Margins / Print --- : (?left: Float?, ?right: Float?, ?top: Float?, ?bottom: Float?, ?header: Float?, ?footer: Float?) -> void

Parameters:

  • left: (Float, nil) (defaults to: nil)
  • right: (Float, nil) (defaults to: nil)
  • top: (Float, nil) (defaults to: nil)
  • bottom: (Float, nil) (defaults to: nil)
  • header: (Float, nil) (defaults to: nil)
  • footer: (Float, nil) (defaults to: nil) 


2424
2425
2426
2427
 
# File 'lib/xlsxrb.rb', line 2424

def page_margins(left: nil, right: nil, top: nil, bottom: nil, header: nil, footer: nil)
  sheet if @current_sheet.nil?
  @current_page_margins = { left: left, right: right, top: top, bottom: bottom, header: header, footer: footer }.compact
end

#page_setup(**opts) ⇒ void

This method returns an undefined value.

: (**untyped opts) -> void

Parameters:

  • opts (Object) 


2430
2431
2432
2433
 
# File 'lib/xlsxrb.rb', line 2430

def page_setup(**opts)
  sheet if @current_sheet.nil?
  @current_page_setup.merge!(opts)
end

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

This method returns an undefined value.

--- Pivot Tables --- : (untyped source_ref, **untyped opts) -> void

Parameters:

  • source_ref (Object)
  • opts (Object) 


2331
2332
2333
2334
2335
2336
2337
2338
2339
2340
 
# File 'lib/xlsxrb.rb', line 2331

def pivot_table(source_ref, row_fields:, data_fields:, col_fields: [], dest_ref: "E1", name: nil, field_names: nil, items: nil)
  sheet if @current_sheet.nil?
  @current_pivot_tables ||= []
  @current_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

#print_area(range, sheet: nil) ⇒ void

This method returns an undefined value.

Set the print area for the current or named sheet. : (String range, ?sheet: String?) -> void

Parameters:

  • range (String)
  • sheet: (String, nil) (defaults to: nil) 


2537
2538
2539
2540
2541
2542
2543
2544
2545
 
# File 'lib/xlsxrb.rb', line 2537

def print_area(range, sheet: nil)
  # simplecov:disable
  # Edge case / untested delegation block
  sheet_name = sheet || @current_sheet || "Sheet1"
  value = "'#{sheet_name}'!#{absolute_range(range)}"
  @defined_names.reject! { |dn| dn[:name] == "_xlnm.Print_Area" && dn[:local_sheet_name] == sheet_name }
  defined_name("_xlnm.Print_Area", value, sheet: sheet_name)
  # simplecov:enable
end

#print_options(name, value) ⇒ void

This method returns an undefined value.

: (Symbol name, untyped value) -> void

Parameters:

  • name (Symbol)
  • value (Object) 


2442
2443
2444
2445
 
# File 'lib/xlsxrb.rb', line 2442

def print_options(name, value)
  sheet if @current_sheet.nil?
  @current_print_options[name] = value
end

#print_titles(rows: nil, cols: nil, sheet: nil) ⇒ void

This method returns an undefined value.

Set print titles for the current or named sheet. : (?rows: String?, ?cols: String?, ?sheet: String?) -> void

Parameters:

  • rows: (String, nil) (defaults to: nil)
  • cols: (String, nil) (defaults to: nil)
  • sheet: (String, nil) (defaults to: nil) 


2549
2550
2551
2552
2553
2554
2555
2556
2557
 
# File 'lib/xlsxrb.rb', line 2549

def print_titles(rows: nil, cols: nil, sheet: nil)
  sheet_name = sheet || @current_sheet || "Sheet1"
  parts = []
  parts << "'#{sheet_name}'!$#{cols.sub(":", ":$")}" if cols
  parts << "'#{sheet_name}'!$#{rows.sub(":", ":$")}" if rows
  value = parts.join(",")
  @defined_names.reject! { |dn| dn[:name] == "_xlnm.Print_Titles" && dn[:local_sheet_name] == sheet_name }
  defined_name("_xlnm.Print_Titles", value, sheet: sheet_name)
end

#properties(core: nil, app: nil) ⇒ void

This method returns an undefined value.

Set multiple core and/or app properties.

: (?core: Hash[Symbol, String | Integer | Time]?, ?app: Hash[Symbol, String | Integer | Time]?) -> void

Parameters:

  • core (Hash, nil) (defaults to: nil) —

    Core properties.

  • app (Hash, nil) (defaults to: nil) —

    App properties.

  • core: (Hash[Symbol, String | Integer | Time], nil) (defaults to: nil)
  • app: (Hash[Symbol, String | Integer | Time], nil) (defaults to: nil) 


2597
2598
2599
2600
2601
2602
2603
 
# File 'lib/xlsxrb.rb', line 2597

def properties(core: nil, app: nil)
  # simplecov:disable
  # Edge case / untested delegation block
  core&.each { |k, v| core_property(k, v) }
  app&.each { |k, v| app_property(k, v) }
  # simplecov:enable
end

#protect_sheet(**opts) ⇒ void

This method returns an undefined value.

--- Sheet Protection --- : (**untyped opts) -> void

Parameters:

  • opts (Object) 


2449
2450
2451
2452
2453
2454
2455
2456
2457
2458
2459
2460
2461
2462
 
# File 'lib/xlsxrb.rb', line 2449

def protect_sheet(**opts)
  sheet if @current_sheet.nil?
  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
  @current_sheet_protection = normalized
end

#protect_workbook(**opts) ⇒ void

This method returns an undefined value.

Set workbook protection.

: (**String | Integer | bool | nil opts) -> void

Parameters:

  • opts (Hash) —

    Protection options.



2564
2565
2566
 
# File 'lib/xlsxrb.rb', line 2564

def protect_workbook(**opts)
  @workbook_protection = opts
end

#resolve_defined_names(names, sheets) ⇒ Object

: (untyped names, untyped sheets) -> untyped

Parameters:

  • names (Object)
  • sheets (Object)

Returns:

  • (Object) 


2673
2674
2675
2676
2677
2678
2679
2680
2681
2682
2683
2684
 
# File 'lib/xlsxrb.rb', line 2673

def resolve_defined_names(names, sheets)
  sheet_names = sheets.map { |s| s[:name] }
  names.map do |dn|
    resolved = dn.dup
    if dn[:local_sheet_name]
      idx = sheet_names.index(dn[:local_sheet_name])
      resolved[:local_sheet_id] = idx if idx
      resolved.delete(:local_sheet_name)
    end
    resolved
  end
end

#row(values, styles: nil, height: nil, hidden: false, custom_height: false, outline_level: nil) ⇒ void

Note:

Excel's column limit is 16,384, row limit is 1,048,576, string max length is 32,767.

This method returns an undefined value.

Add a row of values. values is an Array.

styles

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

Add a row to the sheet.

: (Array | Hash[untyped, untyped] values, ?styles: untyped, ?height: Float | Integer | nil, ?hidden: bool, ?custom_height: bool, ?outline_level: Integer | nil) -> void

Parameters:

  • values (Array, Hash) —

    The cell values.

  • styles (String, Array<String>, nil) (defaults to: nil) —

    Styles to apply to cells.

  • height (Float, nil) (defaults to: nil) —

    The row height.

  • hidden (Boolean) (defaults to: false) —

    Whether the row is hidden.

  • custom_height (Boolean) (defaults to: false) —

    Whether it's a custom height.

  • outline_level (Integer, nil) (defaults to: nil) —

    The outline level.

  • styles: (Object) (defaults to: nil)
  • height: (Float, Integer, nil) (defaults to: nil)
  • hidden: (Boolean) (defaults to: false)
  • custom_height: (Boolean) (defaults to: false)
  • outline_level: (Integer, nil) (defaults to: nil) 


2137
2138
2139
2140
2141
2142
2143
2144
2145
2146
2147
2148
2149
2150
2151
2152
2153
2154
2155
2156
2157
2158
2159
2160
2161
2162
2163
2164
2165
2166
2167
2168
2169
2170
2171
2172
2173
2174
2175
2176
2177
2178
2179
2180
2181
2182
2183
2184
2185
2186
2187
2188
2189
2190
2191
2192
2193
2194
2195
2196
2197
2198
2199
2200
2201
2202
2203
2204
2205
2206
2207
2208
2209
2210
2211
2212
2213
2214
2215
2216
2217
2218
2219
2220
2221
2222
2223
2224
2225
2226
2227
2228
 
# File 'lib/xlsxrb.rb', line 2137

def row(values, styles: nil, height: nil, hidden: false, custom_height: false, outline_level: nil)
  sheet if @current_sheet.nil?

  row_index = @current_row_index
  # See: https://support.microsoft.com/en-us/office/excel-specifications-and-limits-1672b34d-7043-467e-8e27-269d656771c3
  if @strict_excel_mode
    raise ArgumentError, "Row index #{row_index} exceeds Excel limit of 1,048,576 rows" if row_index >= 1_048_576
    raise ArgumentError, "Row height #{height} must be between 0 and 409 points (Excel limitation)" if height && (height.negative? || height > 409)
  end
  @current_row_index += 1

  if values.is_a?(Hash)
    max_col = values.keys.map { |k| Elements::Cell.column_index(k) }.max || -1
    cells_array = Array.new(max_col + 1)
    values.each do |k, v|
      idx = Elements::Cell.column_index(k)
      cells_array[idx] = v
    end
    values = cells_array
  end

  if styles.is_a?(Hash)
    expanded_styles = {}
    styles.each do |k, v|
      if k.is_a?(Range) || k.is_a?(Array)
        k.each { |idx| expanded_styles[Elements::Cell.column_index(idx)] = v }
      else
        expanded_styles[Elements::Cell.column_index(k)] = v
      end
    end
    max_col_style = expanded_styles.keys.max || -1
    styles_array = Array.new(max_col_style + 1)
    expanded_styles.each do |idx, v|
      styles_array[idx] = v
    end
    styles = styles_array
  end

  # Auto-detect Date / Time for built-in styles
  values.each_with_index do |val, idx|
    cell_style = styles.is_a?(Array) ? styles[idx] : styles

    if val.is_a?(Date) && cell_style.nil?
      style("__xlsxrb_date", number_format: "yyyy-mm-dd") unless @styles.key?("__xlsxrb_date")
      styles = [] if styles.nil?
      styles = Array.new(values.size, styles) unless styles.is_a?(Array)
      styles[idx] = "__xlsxrb_date"
    elsif val.is_a?(Time) && cell_style.nil?
      style("__xlsxrb_time", number_format: "yyyy-mm-dd hh:mm:ss") unless @styles.key?("__xlsxrb_time")
      styles = [] if styles.nil?
      styles = Array.new(values.size, styles) unless styles.is_a?(Array)
      styles[idx] = "__xlsxrb_time"
    end
  end

  has_charts = @current_charts && !@current_charts.empty?
  row_num = row_index + 1 if has_charts

  max_len = values.size
  max_len = [max_len, styles.size].max if styles.is_a?(Array)
  # See: https://support.microsoft.com/en-us/office/excel-specifications-and-limits-1672b34d-7043-467e-8e27-269d656771c3
  raise ArgumentError, "Row contains #{max_len} columns, exceeding Excel limit of 16_384 columns" if @strict_excel_mode && max_len > 16_384

  if has_charts || @strict_excel_mode
    @current_cells ||= {} if has_charts
    max_len.times do |col_idx|
      val = col_idx < values.size ? values[col_idx] : nil
      next if val.nil?

      raise ArgumentError, "Invalid cell value type or value: #{val.class} for value #{val.inspect}" unless val.nil? || val.is_a?(String) || (val.is_a?(Numeric) && !(val.is_a?(Float) && (val.infinite? || val.nan?))) || val.is_a?(TrueClass) || val.is_a?(FalseClass) || val.is_a?(Date) || val.is_a?(Time) || val.is_a?(Elements::Formula) || (val.is_a?(Hash) && val.key?(:formula)) || val.is_a?(Elements::RichText) || val.is_a?(Elements::CellError)

      # See: https://support.microsoft.com/en-us/office/excel-specifications-and-limits-1672b34d-7043-467e-8e27-269d656771c3
      raise ArgumentError, "Cell text length #{val.length} exceeds Excel limit of 32,767 characters" if @strict_excel_mode && val.is_a?(String) && val.length > 32_767

      if has_charts
        addr = "#{Elements::Cell.column_letter(col_idx)}#{row_num}"
        @current_cells[addr] = val
      end
    end
  end

  attrs = nil
  if height || hidden || outline_level
    attrs = {}
    attrs[:height] = height if height
    attrs[:hidden] = true if hidden
    attrs[:custom_height] = custom_height || !height.nil?
    attrs[:outline_level] = outline_level if outline_level
  end

  @current_row_writer.write_row_values(row_index, values, styles: styles, style_map: @style_name_to_id, sst: @sst, sst_index: @sst_index, attrs: attrs)
end

#select_cell(active_cell, sqref: nil, pane: nil) ⇒ void

This method returns an undefined value.

: (String active_cell, ?sqref: String?, ?pane: (String | Symbol)?) -> void

Parameters:

  • active_cell (String)
  • sqref: (String, nil) (defaults to: nil)
  • pane: (String, Symbol, nil) (defaults to: nil) 


2416
2417
2418
2419
2420
 
# File 'lib/xlsxrb.rb', line 2416

def select_cell(active_cell, sqref: nil, pane: nil)
  sheet if @current_sheet.nil?
  @current_selection = { active_cell: active_cell, sqref: sqref || active_cell }
  @current_selection[:pane] = pane if pane
end

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

This method returns an undefined value.

--- Shapes --- : (**untyped opts) -> void

Parameters:

  • opts (Object) 


2475
2476
2477
2478
2479
2480
2481
 
# File 'lib/xlsxrb.rb', line 2475

def shape(preset: "rect", text: nil, from_col: 0, from_row: 0, to_col: 5, to_row: 5, **opts)
  sheet if @current_sheet.nil?
  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 #{@current_shapes.size + 1}"
  shape.merge!(opts)
  @current_shapes << shape
end

#sheet(name = nil, **opts) {|sheet_builder| ... } ⇒ void

This method returns an undefined value.

Add a new sheet.

: (?String? name, **untyped opts) ?{ (WorksheetProxy) -> void } -> untyped

Parameters:

  • name (String, nil) (defaults to: nil) —

    The name of the sheet.

  • opts (Hash) —

    Sheet properties.

Yields:

  • (sheet_builder)

Yield Parameters:



2061
2062
2063
2064
2065
2066
2067
2068
2069
2070
2071
2072
 
# File 'lib/xlsxrb.rb', line 2061

def sheet(name = nil, **opts)
  name ||= "Sheet#{@sheets.size + 1}"
  raise ArgumentError, "Sheet name '#{name}' must be <= 31 characters (Excel limitation)" if @strict_excel_mode && name.length > 31
  raise ArgumentError, "Sheet name '#{name}' contains invalid characters (ECMA-376 OOXML specification)" if name.match?(%r{[\[\]*?/\\]})
  raise ArgumentError, "Sheet name '#{name}' is already used. Excel requires unique sheet names." if @strict_excel_mode && @sheets.map { |s| s.respond_to?(:name) ? s.name.downcase : s.to_s.downcase }.include?(name.downcase)

  internal_sheet_setup(name)
  opts.each { |k, v| set_sheet_property(k, v) }

  yield WorksheetProxy.new(self, @current_sheet) if block_given?
  @current_sheet
end

#sheet_properties(name, value) ⇒ void

This method returns an undefined value.

--- Sheet Properties --- : (Symbol name, untyped value) -> void

Parameters:

  • name (Symbol)
  • value (Object) 


2485
2486
2487
2488
 
# File 'lib/xlsxrb.rb', line 2485

def sheet_properties(name, value)
  sheet if @current_sheet.nil?
  @current_sheet_properties[name] = value
end

#sheet_view(name, value) ⇒ void

This method returns an undefined value.

: (Symbol name, untyped value) -> void

Parameters:

  • name (Symbol)
  • value (Object) 


2491
2492
2493
2494
 
# File 'lib/xlsxrb.rb', line 2491

def sheet_view(name, value)
  sheet if @current_sheet.nil?
  @current_sheet_view[name] = value
end

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

: (untyped ref, untyped sort_conditions, **untyped opts) -> untyped

Parameters:

  • ref (Object)
  • sort_conditions (Object)
  • opts (Object)

Returns:

  • (Object) 


2298
2299
2300
2301
 
# File 'lib/xlsxrb.rb', line 2298

def sort_state(ref, sort_conditions, **opts)
  sheet if @current_sheet.nil?
  @current_sort_state = { ref: ref, sort_conditions: sort_conditions }.merge(opts)
end

#sparkline_group(sparklines:, type: nil, **opts) ⇒ void

This method returns an undefined value.

--- Sparklines --- : (**untyped opts) -> void

Parameters:

  • opts (Object) 


2351
2352
2353
2354
2355
2356
2357
 
# File 'lib/xlsxrb.rb', line 2351

def sparkline_group(sparklines:, type: nil, **opts)
  sheet if @current_sheet.nil?
  group = { sparklines: sparklines }
  group[:type] = type if type
  group.merge!(opts)
  @current_sparkline_groups << group
end

#split_pane(x_split: 0, y_split: 0, top_left_cell: nil) ⇒ void

This method returns an undefined value.

: (?x_split: ::Integer, ?y_split: ::Integer, ?top_left_cell: String?) -> void

Parameters:

  • x_split: (::Integer) (defaults to: 0)
  • y_split: (::Integer) (defaults to: 0)
  • top_left_cell: (String, nil) (defaults to: nil) 


2410
2411
2412
2413
 
# File 'lib/xlsxrb.rb', line 2410

def split_pane(x_split: 0, y_split: 0, top_left_cell: nil)
  sheet if @current_sheet.nil?
  @current_split_pane = { x_split: x_split, y_split: y_split, top_left_cell: top_left_cell }
end

#style(name, **opts) {|style_builder| ... } ⇒ StyleBuilder

Define a named style that can be applied to cells.

: (String name, **untyped opts) ?{ (StyleBuilder) -> void } -> StyleBuilder

Parameters:

  • name (String) —

    The name of the style.

  • opts (Hash) —

    Style options (e.g. bold: true).

Yields:

  • (style_builder)

Yield Parameters:

Returns:



1598
1599
1600
1601
1602
1603
1604
1605
1606
1607
1608
 
# File 'lib/xlsxrb.rb', line 1598

def style(name, **opts)
  style_builder = StyleBuilder.new(name)
  style_builder.apply_options!(**opts) unless opts.empty?
  yield style_builder if block_given?
  @styles[name] = style_builder

  # Register immediately
  @style_name_to_id[name] = style_builder.register_with(@style_writer)

  style_builder
end

#table(ref, columns:, name: nil, display_name: nil, style: nil, **opts) ⇒ void

This method returns an undefined value.

--- Tables --- : (untyped ref, **untyped opts) -> void

Parameters:

  • ref (Object)
  • opts (Object) 


2319
2320
2321
2322
2323
2324
2325
2326
2327
 
# File 'lib/xlsxrb.rb', line 2319

def table(ref, columns:, name: nil, display_name: nil, style: nil, **opts)
  sheet if @current_sheet.nil?
  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)
  @current_tables << tbl
end

#validate_data(sqref, **opts) ⇒ void

This method returns an undefined value.

--- Data Validation --- : (untyped sqref, **untyped opts) -> void

Parameters:

  • sqref (Object)
  • opts (Object) 


2305
2306
2307
2308
 
# File 'lib/xlsxrb.rb', line 2305

def validate_data(sqref, **opts)
  sheet if @current_sheet.nil?
  @current_data_validations << opts.merge(sqref: sqref)
end

#workbook_property(name, value) ⇒ void

Note:

SECURITY WARNING: If you set :update_links to anything other than "never", you may expose end-users to malicious external reference vulnerabilities (e.g., CSV/DDE Injection) when they open the generated Excel file. Ensure you fully trust the exported data.

This method returns an undefined value.

Set a workbook property.

: (Symbol name, String | Integer | bool value) -> void

Parameters:

  • name (Symbol) —

    The property name (e.g. :update_links).

  • value (String, Integer, Boolean) —

    The property value.



1586
1587
1588
 
# File 'lib/xlsxrb.rb', line 1586

def workbook_property(name, value)
  @workbook_properties[name] = value
end