Class: Xlsxrb::WorksheetBuilder

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

Overview

DSL context for building a single in-memory worksheet in build.

Instance Attribute Summary collapse

Instance Method Summary collapse

Constructor Details

#initialize(name, strict_excel_mode: true) ⇒ WorksheetBuilder

: (String name, ?strict_excel_mode: bool) -> void

Parameters:

  • name (String) —

    The worksheet name.

  • strict_excel_mode (Boolean) (defaults to: true) —

    Whether to enforce Microsoft Excel limits.

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


13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
 
# File 'lib/xlsxrb/worksheet_builder.rb', line 13

def initialize(name, strict_excel_mode: true)
  @name = name
  @strict_excel_mode = strict_excel_mode
  @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 ⇒ Hash{String => StyleBuilder} (readonly)

Internal: returns styles for later processing by WorkbookBuilder : Hash[String, StyleBuilder]

Returns:



689
690
691
 
# File 'lib/xlsxrb/worksheet_builder.rb', line 689

def styles
  @styles
end

Instance Method Details

#auto_filter(range) ⇒ String

Sets an auto-filter range on the sheet.

: (String range) -> String

Parameters:

  • range (String) —

    The cell range (e.g. "A1:E100").

Returns:

  • (String) 


302
303
304
 
# File 'lib/xlsxrb/worksheet_builder.rb', line 302

def auto_filter(range)
  @auto_filter = range
end

#build ⇒ Elements::Worksheet

Builds and returns the in-memory Elements::Worksheet.

: () -> Elements::Worksheet

Returns:



652
653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
670
671
672
673
674
675
676
677
678
679
680
681
682
683
684
 
# File 'lib/xlsxrb/worksheet_builder.rb', line 652

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

#chart(**options) {|builder| ... } ⇒ void

This method returns an undefined value.

Adds a chart to the worksheet.

: (**untyped options) ?{ (ChartBuilder) -> void } -> void

Parameters:

  • options (Hash) —

    Chart configuration options.

Yields:

  • (builder)

Yield Parameters:



268
269
270
271
272
273
274
275
 
# File 'lib/xlsxrb/worksheet_builder.rb', line 268

def chart(**options)
  if block_given?
    builder = ChartBuilder.new
    yield builder
    options = builder.options.merge(options)
  end
  @charts << options
end

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

This method returns an undefined value.

Sets column formatting and properties for one or multiple columns.

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

Examples:

Set width for column A

sheet.column("A", width: 20.0)

Set width for a range of columns

sheet.column("A".."D", width: 15.0)

Parameters:

  • index (Integer, String, Range, Array) —

    Column index (0-based), letter ("A"), or range ("A".."D").

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

    Column width in character units (0 to 255).

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

    Whether the column is hidden.

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

    Whether custom width is explicitly set.

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

    Grouping/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)

Raises:

  • (ArgumentError) —

    If width exceeds 255 in strict mode.



239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
 
# File 'lib/xlsxrb/worksheet_builder.rb', line 239

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

  indices.each do |idx|
    @columns << Elements::Column.new(
      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.

Adds a cell comment / note.

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

Parameters:

  • cell (String, Integer) —

    Cell reference (e.g. "B2").

  • text (String) —

    The comment text.

  • author (String) (defaults to: "Author") —

    Author name.

  • author: (::String) (defaults to: "Author") 


402
403
404
 
# File 'lib/xlsxrb/worksheet_builder.rb', line 402

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

#conditional_format(sqref, **opts) ⇒ void

This method returns an undefined value.

Adds a conditional formatting rule to a cell range.

: (String sqref, **untyped opts) -> void

Parameters:

  • sqref (String) —

    Target cell range reference (e.g. "C2:C50").

  • opts (Hash) —

    Rule options (e.g. type: :cellIs, operator: :greaterThan, formula: "100").



347
348
349
 
# File 'lib/xlsxrb/worksheet_builder.rb', line 347

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

#filter_column(col_id, filter) ⇒ Hash

Configures filtering rules on a specific column of an auto-filter.

: (Integer col_id, Hash[untyped, untyped] filter) -> Hash[untyped, untyped]

Parameters:

  • col_id (Integer) —

    0-based column index relative to filter range.

  • filter (Hash) —

    Filter criteria (e.g. values, custom filters).

Returns:

  • (Hash) 


313
314
315
 
# File 'lib/xlsxrb/worksheet_builder.rb', line 313

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

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

This method returns an undefined value.

Freezes window panes at the specified row and column.

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

Parameters:

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

    Number of rows to freeze from top (0-based split position).

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

    Number of columns to freeze from left (0-based or letter).

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


471
472
473
474
 
# File 'lib/xlsxrb/worksheet_builder.rb', line 471

def freeze_pane(row: 0, col: 0)
  col = Elements::Cell.column_index(col)
  @freeze_pane = { row: row, col: col }
end

#header_footer(**opts) ⇒ void

This method returns an undefined value.

Configures headers and footers for printing.

: (**untyped opts) -> void

Parameters:

  • opts (Hash) —

    Header and footer options (e.g. odd_header: "&CHeader Text").



532
533
534
 
# File 'lib/xlsxrb/worksheet_builder.rb', line 532

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

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

This method returns an undefined value.

Adds a hyperlink to a cell.

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

Parameters:

  • cell (String, Integer) —

    Cell coordinate (e.g. "A1").

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

    Target external URL.

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

    Optional display text.

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

    Optional hover tooltip text.

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

    Optional internal sheet location (e.g. "Sheet2!A1").

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


287
288
289
290
291
292
293
294
 
# File 'lib/xlsxrb/worksheet_builder.rb', line 287

def 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

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

Inserts an embedded image from raw binary data.

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

Parameters:

  • file_data (String) —

    Binary image data.

  • ext (String) (defaults to: "png") —

    File extension ("png", "jpeg", etc.).

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

    Top-left starting column (0-based).

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

    Top-left starting row (0-based).

  • to_col (Integer) (defaults to: 5) —

    Bottom-right ending column (0-based).

  • to_row (Integer) (defaults to: 10) —

    Bottom-right ending row (0-based).

  • opts (Hash) —

    Additional image anchoring options.

  • 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) 


579
580
581
582
583
 
# File 'lib/xlsxrb/worksheet_builder.rb', line 579

def 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

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

This method returns an undefined value.

Merges a range of cells into a single cell.

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

Examples:

Merge with string range

sheet.merge("A1:C1")

Merge with row and column bounds

sheet.merge(row_start: 0, row_end: 2, col_start: "A", col_end: "C")

Parameters:

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

    Cell range (e.g. "A1:B2") or hash of coordinates.

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

    Single row index.

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

    Starting column.

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

    Ending column.

  • 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) 


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
 
# File 'lib/xlsxrb/worksheet_builder.rb', line 438

def merge(range = nil, row: nil, col_start: nil, col_end: nil, row_start: nil, row_end: 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+)?$/)
    return if @merge_cells_ranges.include?(range)

    @merge_cells_ranges << 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}"
    @merge_cells_ranges << "#{start_ref}:#{end_ref}"
  end
end

#page_break_col(col_index) ⇒ void

This method returns an undefined value.

Inserts a vertical page break before the specified column.

: (Integer | String col_index) -> void

Parameters:

  • col_index (Integer, String) —

    Column index (0-based) or letter ("B").



642
643
644
645
 
# File 'lib/xlsxrb/worksheet_builder.rb', line 642

def page_break_col(col_index)
  col_index = Elements::Cell.column_index(col_index)
  @col_breaks << col_index
end

#page_break_row(row_num) ⇒ void

This method returns an undefined value.

Inserts a horizontal page break before the specified row.

: (Integer row_num) -> void

Parameters:

  • row_num (Integer) —

    1-based row number.



632
633
634
 
# File 'lib/xlsxrb/worksheet_builder.rb', line 632

def page_break_row(row_num)
  @row_breaks << row_num
end

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

This method returns an undefined value.

Sets page margins in inches for printing.

: (?left: Float?, ?right: Float?, ?top: Float?, ?bottom: Float?, ?header: Float?, ?footer: Float?) -> void

Parameters:

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

    Left margin.

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

    Right margin.

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

    Top margin.

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

    Bottom margin.

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

    Header margin.

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

    Footer margin.

  • 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) 


512
513
514
 
# File 'lib/xlsxrb/worksheet_builder.rb', line 512

def 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

#page_setup(**opts) ⇒ void

This method returns an undefined value.

Sets page setup configuration (orientation, paper size, fit to page, scaling).

: (**untyped opts) -> void

Parameters:

  • opts (Hash) —

    Page setup options (e.g. orientation: :landscape, paper_size: 9).



522
523
524
 
# File 'lib/xlsxrb/worksheet_builder.rb', line 522

def page_setup(**opts)
  @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.

Adds a Pivot Table to the sheet.

: (String source_ref, row_fields: Array[String | Integer], data_fields: Array[String | Hash[Symbol, untyped]], ?col_fields: Array[String | Integer], ?dest_ref: String, ?name: String?, ?field_names: Array?, ?items: Array?, **untyped opts) -> void

Parameters:

  • source_ref (String) —

    Source data range (e.g. "DataSheet!A1:D100").

  • row_fields (Array<Integer, String>) —

    Field names or indices for row headers.

  • data_fields (Array<Hash>) —

    Data aggregation fields.

  • col_fields (Array<Integer, String>) (defaults to: []) —

    Field names or indices for column headers.

  • dest_ref (String) (defaults to: "E1") —

    Top-left destination cell (default "E1").

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

    Pivot table name.

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

    Custom field display names.

  • items (Array, nil) (defaults to: nil) —

    Item configuration.

  • row_fields: (Array[String | Integer])
  • data_fields: (Array[String | Hash[Symbol, untyped]])
  • col_fields: (Array[String | Integer]) (defaults to: [])
  • dest_ref: (String) (defaults to: "E1")
  • name: (String, nil) (defaults to: nil)
  • field_names: (Array[String], nil) (defaults to: nil)
  • items: (Array[untyped], nil) (defaults to: nil)
  • opts (Object) 


384
385
386
387
388
389
390
391
392
 
# File 'lib/xlsxrb/worksheet_builder.rb', line 384

def 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

#print_options(name, value) ⇒ void

This method returns an undefined value.

Sets a print option (e.g. grid_lines: true, headings: true).

: (Symbol name, untyped value) -> void

Parameters:

  • name (Symbol) —

    Option name.

  • value (Object) —

    Option value.



543
544
545
 
# File 'lib/xlsxrb/worksheet_builder.rb', line 543

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

#protect_sheet(**opts) ⇒ void

This method returns an undefined value.

Sets sheet-level protection with optional password hashing.

: (**untyped opts) -> void

Parameters:

  • opts (Hash) —

    Protection options (e.g. password: "secret", select_locked_cells: true).



553
554
555
556
557
558
559
560
561
562
563
564
565
 
# File 'lib/xlsxrb/worksheet_builder.rb', line 553

def protect_sheet(**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

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

This method returns an undefined value.

Appends a row of cells to the worksheet.

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

Examples:

Add a row with values and array styles

sheet.row(["ID", "Name", "Total"], styles: [:header, :header, :header])

Add a row with a hash of column keys

sheet.row({ "A" => "Invoice", "C" => 12345 })

Parameters:

  • values (Array<Object>, Hash{String, Integer => Object}) —

    The cell values.

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

    Style names or inline styles to apply.

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

    The row height in points (0 to 409).

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

    Whether the row is hidden.

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

    Whether custom row height is enforced.

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

    Grouping/outline hierarchy 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)

Raises:

  • (ArgumentError) —

    If limits are exceeded when strict_excel_mode is enabled.



86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
 
# File 'lib/xlsxrb/worksheet_builder.rb', line 86

def row(values, styles: nil, height: nil, hidden: false, custom_height: false, outline_level: nil)
  row_index = @rows.size
  # 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

  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

  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

  cells = Array.new(max_len)
  style_lookup = styles.is_a?(Array)

  col_index = 0
  while col_index < max_len
    val = col_index < values.size ? values[col_index] : 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) || (val.is_a?(Array) && val.first.is_a?(Hash) && (val.first.key?(:text) || val.first.key?("text")))
    # 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 val.is_a?(Array) && val.first.is_a?(Hash) && (val.first.key?(:text) || val.first.key?("text"))
      # Coerce array of hashes to RichText
      runs = val.map do |run|
        text = run[:text] || run["text"]
        font = run.reject { |k| k.to_s == "text" }
        { text: text, font: font.empty? ? nil : font }.compact
      end
      val = Elements::RichText.new(runs: runs)
    end

    style_name = if style_lookup
                   col_index < styles.size ? styles[col_index] : nil
                 else
                   styles
                 end

    if style_name.is_a?(Hash)
      inline_name = "__inline_#{style_name.hash}"
      style(inline_name, **style_name) unless @styles.key?(inline_name)
      style_name = inline_name
    end
    if val.nil? && style_name.nil?
      col_index += 1
      next
    end
    # If value is a Formula object or Hash with :formula, 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: val.cached_value,
                           formula: val,
                           style_index: style_name
                         )
                       elsif val.is_a?(Hash) && val.key?(:formula)
                         f_obj = Elements::Formula.new(
                           expression: val[:formula],
                           cached_value: val[:value],
                           calculate_always: val[:calculate_always]
                         )
                         Elements::Cell.new(
                           row_index: row_index,
                           column_index: col_index,
                           value: val[:value],
                           formula: f_obj,
                           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

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

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

This method returns an undefined value.

Sets the active cell and selection area for the worksheet.

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

Parameters:

  • active_cell (String) —

    Active cell reference (e.g. "A1").

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

    Selected range reference (e.g. "A1:D10").

  • pane (String, Symbol, nil) (defaults to: nil) —

    Target pane (:topLeft, :topRight, :bottomLeft, :bottomRight).

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


496
497
498
499
 
# File 'lib/xlsxrb/worksheet_builder.rb', line 496

def select_cell(active_cell, sqref: nil, pane: nil)
  @selection = { active_cell: active_cell, sqref: sqref || active_cell }
  @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.

Inserts a drawing shape (e.g. rectangle, callout, arrow).

: (**untyped opts) -> void

Parameters:

  • preset (String) (defaults to: "rect") —

    Shape preset name (e.g. "rect", "roundRect").

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

    Shape label text.

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

    Top-left column.

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

    Top-left row.

  • to_col (Integer) (defaults to: 5) —

    Bottom-right column.

  • to_row (Integer) (defaults to: 5) —

    Bottom-right row.

  • opts (Hash) —

    Additional shape formatting options.



597
598
599
600
601
602
 
# File 'lib/xlsxrb/worksheet_builder.rb', line 597

def 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

#sheet_properties(name, value) ⇒ void

This method returns an undefined value.

Sets a sheet-level property (e.g. tab_color: "FF0000").

: (Symbol name, untyped value) -> void

Parameters:

  • name (Symbol) —

    Property name.

  • value (Object) —

    Property value.



611
612
613
 
# File 'lib/xlsxrb/worksheet_builder.rb', line 611

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

#sheet_view(name, value) ⇒ void

This method returns an undefined value.

Sets a sheet view display property (e.g. show_grid_lines: true, zoom_scale: 120).

: (Symbol name, untyped value) -> void

Parameters:

  • name (Symbol) —

    View property name.

  • value (Object) —

    View property value.



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

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

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

Configures column sort state for the sheet.

: (String ref, Array[Hash[untyped, untyped]] sort_conditions, **untyped opts) -> Hash[untyped, untyped]

Parameters:

  • ref (String) —

    The sorted range.

  • sort_conditions (Array<Hash>) —

    List of sort condition definitions.

  • opts (Hash) —

    Additional sort state options.

Returns:

  • (Hash) 


325
326
327
 
# File 'lib/xlsxrb/worksheet_builder.rb', line 325

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

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

This method returns an undefined value.

Adds a sparkline group to the sheet.

: (sparklines: Array[String | Hash[Symbol, untyped]], ?type: (String | Symbol)?, **untyped opts) -> void

Parameters:

  • sparklines (Array<Hash>) —

    List of { data_ref:, location_ref: } items.

  • type (String, Symbol, nil) (defaults to: nil) —

    "line" (default), "column", or "stacked".

  • opts (Hash) —

    Color and formatting options.

  • sparklines: (Array[String | Hash[Symbol, untyped]])
  • type: (String, Symbol, nil) (defaults to: nil) 


414
415
416
417
418
419
 
# File 'lib/xlsxrb/worksheet_builder.rb', line 414

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

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

This method returns an undefined value.

Splits window panes without freezing.

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

Parameters:

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

    Horizontal split offset in points.

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

    Vertical split offset in points.

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

    Top-left cell reference in bottom-right pane.

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


484
485
486
 
# File 'lib/xlsxrb/worksheet_builder.rb', line 484

def 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

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

Defines or configures a named cell style.

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

Examples:

Define a bold header style

sheet.style(:header, bold: true, fill_color: "4F81BD", font_color: "FFFFFF")

Parameters:

  • name (String, Symbol) —

    The name of the style.

  • opts (Hash) —

    Style options (e.g. bold: true, fill_color: "FF0000").

Yields:

  • (style_builder)

Yield Parameters:

Returns:



59
60
61
62
63
64
65
66
 
# File 'lib/xlsxrb/worksheet_builder.rb', line 59

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

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

This method returns an undefined value.

Adds an Excel Table (ListObject) to the sheet.

: (String ref, columns: Array[String | Hash[Symbol, untyped]], ?name: String?, ?display_name: String?, ?style: String?, **untyped opts) -> void

Parameters:

  • ref (String) —

    The table range (e.g. "A1:D20").

  • columns (Array<String>) —

    Column header names.

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

    Table name identifier.

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

    Table display name.

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

    Built-in table style name (e.g. "TableStyleMedium2").

  • opts (Hash) —

    Additional table properties.

  • columns: (Array[String | Hash[Symbol, untyped]])
  • name: (String, nil) (defaults to: nil)
  • display_name: (String, nil) (defaults to: nil)
  • style: (String, nil) (defaults to: nil) 


362
363
364
365
366
367
368
369
 
# File 'lib/xlsxrb/worksheet_builder.rb', line 362

def 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

#validate_data(sqref, **opts) ⇒ void

This method returns an undefined value.

Adds a data validation rule to a cell or range.

: (String sqref, **untyped opts) -> void

Parameters:

  • sqref (String) —

    Target cell or range reference (e.g. "B2:B100").

  • opts (Hash) —

    Validation configuration (e.g. type: :list, formula1: '"Option1,Option2"').



336
337
338
 
# File 'lib/xlsxrb/worksheet_builder.rb', line 336

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