Class: Xlsxrb::WorksheetBuilder

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

Overview

DSL context for a single worksheet in Xlsxrb.build.

Instance Attribute Summary collapse

Instance Method Summary collapse

Constructor Details

#initialize(name, strict_excel_mode: true) ⇒ WorksheetBuilder

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

Parameters:

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


1001
1002
1003
1004
1005
1006
1007
1008
1009
1010
1011
1012
1013
1014
1015
1016
1017
1018
1019
1020
1021
1022
1023
1024
1025
1026
1027
1028
1029
1030
1031
1032
1033
 
# File 'lib/xlsxrb.rb', line 1001

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 ⇒ Object (readonly)

Internal: returns styles for later processing by WorkbookBuilder : untyped

Returns:

  • (Object) 


1650
1651
1652
 
# File 'lib/xlsxrb.rb', line 1650

def styles
  @styles
end

Instance Method Details

#auto_filter(range) ⇒ void

This method returns an undefined value.

Set an auto filter range (e.g. "A1:D10").

: (untyped range) -> untyped

Parameters:

  • range (String) —

    The filter range.



1274
1275
1276
 
# File 'lib/xlsxrb.rb', line 1274

def auto_filter(range)
  @auto_filter = range
end

#build ⇒ Elements::Worksheet

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

: () -> Elements::Worksheet

Returns:



1614
1615
1616
1617
1618
1619
1620
1621
1622
1623
1624
1625
1626
1627
1628
1629
1630
1631
1632
1633
1634
1635
1636
1637
1638
1639
1640
1641
1642
1643
1644
1645
1646
 
# File 'lib/xlsxrb.rb', line 1614

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.

Add a chart to the sheet.

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

Parameters:

  • options (Hash) —

    Chart options.

Yields:

  • (builder)

Yield Parameters:



1236
1237
1238
1239
1240
1241
1242
1243
 
# File 'lib/xlsxrb.rb', line 1236

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

Note:

Excel's column width max is 255.

This method returns an undefined value.

Add a column or multiple columns 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, Range, Array) —

    The column index (0-based), letter, or a collection of them.

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


1207
1208
1209
1210
1211
1212
1213
1214
1215
1216
1217
1218
1219
1220
1221
1222
1223
1224
1225
1226
 
# File 'lib/xlsxrb.rb', line 1207

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.

Add a comment on a cell.

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

Parameters:

  • cell (String) —

    The cell reference.

  • text (String) —

    The comment text.

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

    The author name.

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


1384
1385
1386
 
# File 'lib/xlsxrb.rb', line 1384

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.

Add a conditional formatting rule.

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

Parameters:

  • sqref (String) —

    The cell range.

  • opts (Hash) —

    Conditional format options.



1323
1324
1325
 
# File 'lib/xlsxrb.rb', line 1323

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

#filter_column(col_id, filter) ⇒ void

This method returns an undefined value.

Add a filter column to the auto filter.

: (untyped col_id, untyped filter) -> untyped

Parameters:

  • col_id (Integer) —

    0-based column index within the filter range.

  • filter (Hash) —

    The filter options.



1285
1286
1287
 
# File 'lib/xlsxrb.rb', line 1285

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.

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) 


1453
1454
1455
1456
 
# File 'lib/xlsxrb.rb', line 1453

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.

Set header/footer text.

: (**untyped opts) -> void

Parameters:

  • opts (Hash) —

    Header and footer options.



1516
1517
1518
 
# File 'lib/xlsxrb.rb', line 1516

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.

Add a hyperlink on a cell.

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

Parameters:

  • cell (String) —

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

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

    The URL.

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

    The display text.

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

    The tooltip.

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

    The internal location reference.

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


1257
1258
1259
1260
1261
1262
1263
1264
 
# File 'lib/xlsxrb.rb', line 1257

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.

Insert an image from raw file 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)
  • 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) 


1558
1559
1560
1561
1562
 
# File 'lib/xlsxrb.rb', line 1558

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.

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) 


1418
1419
1420
1421
1422
1423
1424
1425
1426
1427
1428
1429
1430
1431
1432
1433
1434
1435
1436
1437
1438
1439
1440
1441
1442
 
# File 'lib/xlsxrb.rb', line 1418

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.

Add a page break before a column. : (Integer | String col_index) -> void

Parameters:

  • col_index (Integer, String) 


1604
1605
1606
1607
 
# File 'lib/xlsxrb.rb', line 1604

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.

Add a page break before a row. : (Integer row_num) -> void

Parameters:

  • row_num (Integer) 


1597
1598
1599
 
# File 'lib/xlsxrb.rb', line 1597

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.

Set page margins (in inches).

: (?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) 


1496
1497
1498
 
# File 'lib/xlsxrb.rb', line 1496

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.

Set page setup properties.

: (**untyped opts) -> void

Parameters:

  • opts (Hash) —

    Page setup options.



1506
1507
1508
 
# File 'lib/xlsxrb.rb', line 1506

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.

Add a pivot table to the sheet.

: (untyped source_ref, row_fields: untyped, data_fields: untyped, ?col_fields: untyped, ?dest_ref: untyped, ?name: untyped, ?field_names: untyped, ?items: untyped, **untyped opts) -> void

Parameters:

  • source_ref (String) —

    data source range (e.g. "Sheet1!A1:C10").

  • row_fields (Array<Integer>) —

    array of 0-based field indices for row axis.

  • data_fields (Array<Hash>) —

    array of { fld:, name:, subtotal: } hashes.

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

    array of 0-based field indices for column axis.

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

    top-left cell for the pivot table (default "E1").

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

    Pivot table name.

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

    Override field names.

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

    Items configuration.

  • row_fields: (Object)
  • data_fields: (Object)
  • col_fields: (Object) (defaults to: [])
  • dest_ref: (Object) (defaults to: "E1")
  • name: (Object) (defaults to: nil)
  • field_names: (Object) (defaults to: nil)
  • items: (Object) (defaults to: nil)
  • opts (Object) 


1364
1365
1366
1367
1368
1369
1370
1371
1372
 
# File 'lib/xlsxrb.rb', line 1364

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.

Set a print option.

: (Symbol name, untyped value) -> void

Parameters:

  • name (Symbol) —

    Option name.

  • value (Object) —

    Option value.



1527
1528
1529
 
# File 'lib/xlsxrb.rb', line 1527

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

#protect_sheet(**opts) ⇒ void

This method returns an undefined value.

Set sheet protection options.

: (**untyped opts) -> void

Parameters:

  • opts (Hash) —

    Sheet protection options.



1539
1540
1541
1542
1543
1544
1545
1546
1547
1548
1549
1550
1551
 
# File 'lib/xlsxrb.rb', line 1539

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

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


1064
1065
1066
1067
1068
1069
1070
1071
1072
1073
1074
1075
1076
1077
1078
1079
1080
1081
1082
1083
1084
1085
1086
1087
1088
1089
1090
1091
1092
1093
1094
1095
1096
1097
1098
1099
1100
1101
1102
1103
1104
1105
1106
1107
1108
1109
1110
1111
1112
1113
1114
1115
1116
1117
1118
1119
1120
1121
1122
1123
1124
1125
1126
1127
1128
1129
1130
1131
1132
1133
1134
1135
1136
1137
1138
1139
1140
1141
1142
1143
1144
1145
1146
1147
1148
1149
1150
1151
1152
1153
1154
1155
1156
1157
1158
1159
1160
1161
1162
1163
1164
1165
1166
1167
1168
1169
1170
1171
1172
1173
1174
1175
1176
1177
1178
1179
1180
1181
1182
1183
1184
1185
1186
1187
1188
1189
1190
1191
1192
1193
1194
 
# File 'lib/xlsxrb.rb', line 1064

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?(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: nil,
                           formula: val,
                           style_index: style_name
                         )
                       elsif val.is_a?(Hash) && val.key?(:formula)
                         f_obj = Elements::Formula.new(val[:formula])
                         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.

Set active cell selection.

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

Parameters:

  • active_cell (String) —

    The active cell reference.

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

    The selected range.

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

    The pane to select in.

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


1478
1479
1480
1481
 
# File 'lib/xlsxrb.rb', line 1478

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.

Add a shape to the sheet. : (**untyped opts) -> void

Parameters:

  • opts (Object) 


1569
1570
1571
1572
1573
1574
 
# File 'lib/xlsxrb.rb', line 1569

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.

Set a sheet-level property (e.g. :tab_color). : (Symbol name, untyped value) -> void

Parameters:

  • name (Symbol)
  • value (Object) 


1581
1582
1583
 
# File 'lib/xlsxrb.rb', line 1581

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

#sheet_view(name, value) ⇒ void

This method returns an undefined value.

Set a sheet view property (e.g. :show_grid_lines, :zoom_scale). : (Symbol name, untyped value) -> void

Parameters:

  • name (Symbol)
  • value (Object) 


1588
1589
1590
 
# File 'lib/xlsxrb.rb', line 1588

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

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

This method returns an undefined value.

Set sort state.

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

Parameters:

  • ref (String) —

    The sort range.

  • sort_conditions (Array<Hash>) —

    Sort conditions.

  • opts (Hash) —

    Additional options.



1297
1298
1299
 
# File 'lib/xlsxrb.rb', line 1297

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.

Add a sparkline group to the sheet.

: (sparklines: untyped, ?type: untyped, **untyped opts) -> void

Parameters:

  • sparklines (Array<Hash>) —

    Array of { data_ref:, location_ref: } hashes.

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

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

  • opts (Hash) —

    Additional options.

  • sparklines: (Object)
  • type: (Object) (defaults to: nil) 


1398
1399
1400
1401
1402
1403
 
# File 'lib/xlsxrb.rb', line 1398

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.

Split panes (non-frozen).

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

Parameters:

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

    X coordinate.

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

    Y coordinate.

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

    Top left cell reference.

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


1466
1467
1468
 
# File 'lib/xlsxrb.rb', line 1466

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

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:



1044
1045
1046
1047
1048
1049
1050
 
# File 'lib/xlsxrb.rb', line 1044

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
  style_builder
end

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

This method returns an undefined value.

Add a table to the sheet.

: (untyped ref, columns: untyped, ?name: untyped?, ?display_name: untyped?, ?style: untyped?, **untyped opts) -> untyped

Parameters:

  • ref (String) —

    The table range.

  • columns (Array<String>) —

    The column names.

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

    The table name.

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

    The display name.

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

    The table style.

  • opts (Hash) —

    Additional options.

  • columns: (Object)
  • name: (Object, nil) (defaults to: nil)
  • display_name: (Object, nil) (defaults to: nil)
  • style: (Object, nil) (defaults to: nil) 


1340
1341
1342
1343
1344
1345
1346
1347
 
# File 'lib/xlsxrb.rb', line 1340

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.

Add a data validation rule.

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

Parameters:

  • sqref (String) —

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

  • opts (Hash) —

    Data validation options.



1310
1311
1312
 
# File 'lib/xlsxrb.rb', line 1310

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