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) 


943
944
945
946
947
948
949
950
951
952
953
954
955
956
957
958
959
960
961
962
963
964
965
966
967
968
969
970
971
972
973
974
975
 
# File 'lib/xlsxrb.rb', line 943

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) 


1592
1593
1594
 
# File 'lib/xlsxrb.rb', line 1592

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.



1216
1217
1218
 
# File 'lib/xlsxrb.rb', line 1216

def auto_filter(range)
  @auto_filter = range
end

#build ⇒ Elements::Worksheet

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

: () -> Elements::Worksheet

Returns:



1556
1557
1558
1559
1560
1561
1562
1563
1564
1565
1566
1567
1568
1569
1570
1571
1572
1573
1574
1575
1576
1577
1578
1579
1580
1581
1582
1583
1584
1585
1586
1587
1588
 
# File 'lib/xlsxrb.rb', line 1556

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:



1178
1179
1180
1181
1182
1183
1184
1185
 
# File 'lib/xlsxrb.rb', line 1178

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) 


1149
1150
1151
1152
1153
1154
1155
1156
1157
1158
1159
1160
1161
1162
1163
1164
1165
1166
1167
1168
 
# File 'lib/xlsxrb.rb', line 1149

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


1326
1327
1328
 
# File 'lib/xlsxrb.rb', line 1326

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.



1265
1266
1267
 
# File 'lib/xlsxrb.rb', line 1265

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.



1227
1228
1229
 
# File 'lib/xlsxrb.rb', line 1227

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) 


1395
1396
1397
1398
 
# File 'lib/xlsxrb.rb', line 1395

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.



1458
1459
1460
 
# File 'lib/xlsxrb.rb', line 1458

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) 


1199
1200
1201
1202
1203
1204
1205
1206
 
# File 'lib/xlsxrb.rb', line 1199

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) 


1500
1501
1502
1503
1504
 
# File 'lib/xlsxrb.rb', line 1500

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) 


1360
1361
1362
1363
1364
1365
1366
1367
1368
1369
1370
1371
1372
1373
1374
1375
1376
1377
1378
1379
1380
1381
1382
1383
1384
 
# File 'lib/xlsxrb.rb', line 1360

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) 


1546
1547
1548
1549
 
# File 'lib/xlsxrb.rb', line 1546

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) 


1539
1540
1541
 
# File 'lib/xlsxrb.rb', line 1539

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) 


1438
1439
1440
 
# File 'lib/xlsxrb.rb', line 1438

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.



1448
1449
1450
 
# File 'lib/xlsxrb.rb', line 1448

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) 


1306
1307
1308
1309
1310
1311
1312
1313
1314
 
# File 'lib/xlsxrb.rb', line 1306

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.



1469
1470
1471
 
# File 'lib/xlsxrb.rb', line 1469

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.



1481
1482
1483
1484
1485
1486
1487
1488
1489
1490
1491
1492
1493
 
# File 'lib/xlsxrb.rb', line 1481

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) 


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
1034
1035
1036
1037
1038
1039
1040
1041
1042
1043
1044
1045
1046
1047
1048
1049
1050
1051
1052
1053
1054
1055
1056
1057
1058
1059
1060
1061
1062
1063
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
 
# File 'lib/xlsxrb.rb', line 1006

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) 


1420
1421
1422
1423
 
# File 'lib/xlsxrb.rb', line 1420

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) 


1511
1512
1513
1514
1515
1516
 
# File 'lib/xlsxrb.rb', line 1511

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) 


1523
1524
1525
 
# File 'lib/xlsxrb.rb', line 1523

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) 


1530
1531
1532
 
# File 'lib/xlsxrb.rb', line 1530

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.



1239
1240
1241
 
# File 'lib/xlsxrb.rb', line 1239

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) 


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

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) 


1408
1409
1410
 
# File 'lib/xlsxrb.rb', line 1408

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:



986
987
988
989
990
991
992
 
# File 'lib/xlsxrb.rb', line 986

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) 


1282
1283
1284
1285
1286
1287
1288
1289
 
# File 'lib/xlsxrb.rb', line 1282

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.



1252
1253
1254
 
# File 'lib/xlsxrb.rb', line 1252

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