class Xlsxrb::StreamSheet

Represents a worksheet being streamed sequentially from an XLSX file. Provides O(1) constant-memory streaming over rows and cells.

Call {#load} (or {#to_worksheet}) to convert this streaming sheet into an in-memory {Elements::Worksheet} supporting coordinate random access (‘sheet`).

@example Iterate rows and cells in streaming mode (O(1) memory)

Xlsxrb.read("large_data.xlsx") do |sheet|
  puts "Processing sheet: #{sheet.name}"
  sheet.each_row do |row|
    row.each_cell do |cell|
      puts "#{cell.ref}: #{cell.value}"
    end
  end
end

@example Load into an in-memory Worksheet for coordinate random access

wb = Xlsxrb.read("data.xlsx")
doc_sheet = wb.sheets.first.load
puts doc_sheet["A1"].value

@api public

Attributes

name [R] String

@return [String] The sheet name. @api public

state [R] Symbol

@return [Symbol] The sheet visibility state (:visible, :hidden, or :very_hidden). @api public

styles [R] Hash[untyped, untyped]?

@return [Hash, nil] The parsed styles definition hash. @api public

Public Class Methods

new (name, sheet_source, shared_strings, styles = nil, zip_reader: nil, entry_name: nil, state: :visible, date1904: false, dimension: nil, trim_empty_rows: false, pad_empty_rows: false, pad_empty_cells: false, images: nil)

(String name, untyped sheet_source, Array[String] shared_strings, ?Hash[untyped, untyped]? styles, ?zip_reader: Ooxml::ZipReader?, ?entry_name: String?, ?state: Symbol, ?date1904: bool, ?dimension: String?, ?trim_empty_rows: bool, ?pad_empty_rows: bool, ?pad_empty_cells: bool, ?images: Array[Elements::Image]?) → void

Source

# File lib/xlsxrb/stream_sheet.rb, line 61
def initialize(name, sheet_source, shared_strings, styles = nil, zip_reader: nil, entry_name: nil, state: :visible, date1904: false, dimension: nil, trim_empty_rows: false, pad_empty_rows: false, pad_empty_cells: false, images: nil)
  @name = name
  @sheet_source = sheet_source
  @shared_strings = shared_strings
  @styles = styles
  @zip_reader = zip_reader
  @entry_name = entry_name
  @state = state ? state.to_sym : :visible
  @date1904 = date1904 ? true : false
  @dimension = dimension
  @dimension_checked = !dimension.nil?
  @sheet_xml = sheet_source if sheet_source.is_a?(String)
  @trim_empty_rows = trim_empty_rows ? true : false
  @pad_empty_rows = pad_empty_rows ? true : false
  @pad_empty_cells = pad_empty_cells ? true : false
  @images = images
end

Initializes a streaming worksheet context.

@param name [String] The sheet name. @param sheet_source [String, Proc, IO, nil] Raw XML content or chunk supplier/stream. @param shared_strings [Array<String>] Shared strings table. @param styles [Hash, nil] Optional parsed styles hash. @param zip_reader [Ooxml::ZipReader, nil] Optional ZipReader context. @param entry_name [String, nil] Archive entry name for this sheet. @param date1904 [Boolean] Whether the 1904 date system is active. @param dimension [String, nil] Sheet dimension reference string (e.g. “A1:Z50000”). @param trim_empty_rows [Boolean] Whether to omit trailing empty rows during iteration. @param pad_empty_rows [Boolean] Whether to yield empty rows for skipped row numbers. @param pad_empty_cells [Boolean] Whether to pad missing cells within rows. @param images [Array<Elements::Image>, nil] Optional preloaded images array.

Public Instance Methods

auto_filter ()

() → String?

Source

# File lib/xlsxrb/stream_sheet.rb, line 480
def auto_filter
  @auto_filter ||= begin
    listener = Ooxml::Reader::AutoFilterListener.new
    Ooxml::XmlParser.parse(raw_sheet_xml, listener)
    listener.ref
  end
end

Returns the auto-filter range (e.g. “A1:E100”) for this worksheet, or nil if none.

@return [String, nil] @api public

cell (ref_or_row, col = nil)

(String | Symbol | Integer ref_or_row, ?Integer? col) → Elements::Cell?

Source

# File lib/xlsxrb/stream_sheet.rb, line 433
def cell(ref_or_row, col = nil)
  target_row, target_col = if col
                             [ref_or_row.to_i, col.to_i]
                           else
                             parsed = Elements::Cell.parse_ref(ref_or_row.to_s)
                             return nil unless parsed

                             parsed
                           end

  each_row do |row|
    next if row.index < target_row
    return row.cell_at(target_col) if row.index == target_row
    break if row.index > target_row
  end
  nil
end

Access a cell by reference or 0-based coordinates.

@param ref_or_row [String, Symbol, Integer] Cell reference (e.g. “A1”) or 0-based row index. @param col [Integer, nil] Optional 0-based column index. @return [Elements::Cell, nil] @api public

close ()

() → void

Source

# File lib/xlsxrb/stream_sheet.rb, line 516
def close
  @zip_reader&.close
  nil
end

Closes any underlying reader resources.

comment (ref_or_row, col = nil)

(String | Symbol | Integer ref_or_row, ?Integer? col) → Hash[Symbol, untyped]?

Source

# File lib/xlsxrb/stream_sheet.rb, line 362
def comment(ref_or_row, col = nil)
  ref = if col
          "#{Elements::Cell.column_letter(col)}#{ref_or_row.to_i + 1}"
        else
          ref_or_row.to_s.upcase
        end
  comments_by_ref[ref]
end

Returns comment metadata for a specific cell reference, or nil.

@param ref_or_row [String, Symbol, Integer] Cell reference (e.g. “A1”) or 0-based row index. @param col [Integer, nil] Optional 0-based column index. @return [Hash, nil] @api public

comments ()

() → Array[Hash[Symbol, untyped]]

Source

# File lib/xlsxrb/stream_sheet.rb, line 351
def comments
  @comments ||= Xlsxrb.send(:resolve_comments, zip_reader: @zip_reader, entry_name: @entry_name)
end

Returns comments configured for this worksheet as an Array of comment hashes.

@return [Array<Hash[Symbol, untyped]>] @api public

comments_by_ref ()

() → Hash[String, Hash[Symbol, untyped]]

Source

# File lib/xlsxrb/stream_sheet.rb, line 376
def comments_by_ref
  @comments_by_ref ||= comments.each_with_object({}) do |c, acc|
    r = c[:ref] || c[:cell]
    acc[r.to_s.upcase] = c if r
  end
end

Returns comments indexed by cell reference.

@return [Hash<String, Hash[Symbol, untyped]>] @api public

conditional_formats ()

() → Array[Hash[Symbol, untyped]]

Source

# File lib/xlsxrb/stream_sheet.rb, line 506
def conditional_formats
  @conditional_formats ||= begin
    listener = Ooxml::Reader::ConditionalFormattingListener.new
    Ooxml::XmlParser.parse(raw_sheet_xml, listener)
    listener.rules
  end
end

Returns conditional formatting rules configured for this worksheet.

@return [Array<Hash[Symbol, untyped]>] @api public

data_validations ()

() → Array[Hash[Symbol, untyped]]

Source

# File lib/xlsxrb/stream_sheet.rb, line 493
def data_validations
  @data_validations ||= begin
    listener = Ooxml::Reader::DataValidationsListener.new
    Ooxml::XmlParser.parse(raw_sheet_xml, listener)
    listener.validations
  end
end

Returns data validation rules configured for this worksheet.

@return [Array<Hash[Symbol, untyped]>] @api public

date1904? ()

() → bool

Source

# File lib/xlsxrb/stream_sheet.rb, line 164
def date1904?
  @date1904
end

Returns whether the sheet uses the 1904 date system.

@return [Boolean] @api public

dimension ()

() → String?

Source

# File lib/xlsxrb/stream_sheet.rb, line 114
def dimension
  return @dimension if @dimension_checked

  @dimension_checked = true
  @dimension = extract_dimension
end

Returns the dimension reference string (e.g. “A1:Z50000”), or nil if not present.

@return [String, nil] @api public

each (trim_empty_rows: nil, pad_empty_rows: nil, pad_empty_cells: nil, &)

(?trim_empty_rows: bool?, ?pad_empty_rows: bool?, ?pad_empty_cells: bool?) { (StreamRow | Elements::Row) → void } → void
(?trim_empty_rows: bool?, ?pad_empty_rows: bool?, ?pad_empty_cells: bool?) → Enumerator[StreamRow | Elements::Row, void]

Source

# File lib/xlsxrb/stream_sheet.rb, line 305
def each(trim_empty_rows: nil, pad_empty_rows: nil, pad_empty_cells: nil, &)
  each_row(trim_empty_rows: trim_empty_rows, pad_empty_rows: pad_empty_rows, pad_empty_cells: pad_empty_cells, &)
end

Default Enumerable iteration delegates to {#each_row}.

@overload each(trim_empty_rows: nil, pad_empty_rows: nil, pad_empty_cells: nil, &block)

@param trim_empty_rows [Boolean, nil] Whether to omit trailing empty rows.
@param pad_empty_rows [Boolean, nil] Whether to yield empty rows for skipped row numbers.
@param pad_empty_cells [Boolean, nil] Whether to pad missing cells within rows.
@yield [row]
@yieldparam row [StreamRow, Elements::Row]
@return [void]

@overload each(trim_empty_rows: nil, pad_empty_rows: nil, pad_empty_cells: nil)

@param trim_empty_rows [Boolean, nil] Whether to omit trailing empty rows.
@param pad_empty_rows [Boolean, nil] Whether to yield empty rows for skipped row numbers.
@param pad_empty_cells [Boolean, nil] Whether to pad missing cells within rows.
@return [Enumerator<StreamRow | Elements::Row, void>]

@api public

each_cell (&)

() { (Elements::Cell) → void } → void
() → Enumerator[Elements::Cell, void]

Source

# File lib/xlsxrb/stream_sheet.rb, line 249
def each_cell(&)
  return enum_for(:each_cell) unless block_given?

  each_row do |row|
    row.each_cell(&)
  end
end

Iterates over all cells across all rows continuously with O(1) memory.

@overload each_cell(&block)

@yield [cell]
@yieldparam cell [Elements::Cell] The current cell.
@return [void]

@overload each_cell

@return [Enumerator<Elements::Cell, void>]

@api public

each_row (trim_empty_rows: nil, pad_empty_rows: nil, pad_empty_cells: nil, &)

(?trim_empty_rows: bool?, ?pad_empty_rows: bool?, ?pad_empty_cells: bool?) { (StreamRow | Elements::Row) → void } → void
(?trim_empty_rows: bool?, ?pad_empty_rows: bool?, ?pad_empty_cells: bool?) → Enumerator[StreamRow | Elements::Row, void]

Source

# File lib/xlsxrb/stream_sheet.rb, line 208
def each_row(trim_empty_rows: nil, pad_empty_rows: nil, pad_empty_cells: nil, &)
  return enum_for(:each_row, trim_empty_rows: trim_empty_rows, pad_empty_rows: pad_empty_rows, pad_empty_cells: pad_empty_cells) unless block_given?

  trim = if trim_empty_rows.nil?
           @trim_empty_rows
         else
           (trim_empty_rows ? true : false)
         end
  pad_rows = if pad_empty_rows.nil?
               @pad_empty_rows
             else
               (pad_empty_rows ? true : false)
             end
  pad_cells = if pad_empty_cells.nil?
                @pad_empty_cells
              else
                (pad_empty_cells ? true : false)
              end

  source = if @sheet_source
             @sheet_source
           elsif @zip_reader && @entry_name
             ->(&blk) { @zip_reader.each_entry_chunk(@entry_name, &blk) }
           end

  Ooxml::WorksheetParser.each_row(source, shared_strings: @shared_strings, styles: @styles, date1904: @date1904, trim_empty_rows: trim, pad_empty_rows: pad_rows, pad_empty_cells: pad_cells, &)
end

Iterates over rows in this streaming worksheet with O(1) memory.

@overload each_row(&block)

@yield [row]
@yieldparam row [StreamRow, Elements::Row] The current row.

@overload each_row(trim_empty_rows: nil, pad_empty_rows: nil, pad_empty_cells: nil, &block)

@param trim_empty_rows [Boolean, nil] Whether to omit trailing empty rows (defaults to sheet setting).
@param pad_empty_rows [Boolean, nil] Whether to yield empty rows for skipped row numbers (defaults to sheet setting).
@param pad_empty_cells [Boolean, nil] Whether to pad missing cells within rows (defaults to sheet setting).
@yield [row]
@yieldparam row [StreamRow, Elements::Row] The current row.
@return [void]

@overload each_row(trim_empty_rows: nil, pad_empty_rows: nil, pad_empty_cells: nil)

@param trim_empty_rows [Boolean, nil] Whether to omit trailing empty rows (defaults to sheet setting).
@param pad_empty_rows [Boolean, nil] Whether to yield empty rows for skipped row numbers (defaults to sheet setting).
@param pad_empty_cells [Boolean, nil] Whether to pad missing cells within rows (defaults to sheet setting).
@return [Enumerator<StreamRow | Elements::Row, void>]

@api public

each_row_values (type_cast: false, trim_empty_rows: nil, pad_empty_rows: nil, pad_empty_cells: nil, &block)

(?type_cast: bool, ?trim_empty_rows: bool?, ?pad_empty_rows: bool?, ?pad_empty_cells: bool?) { (Array[untyped]) → void } → void
(?type_cast: bool, ?trim_empty_rows: bool?, ?pad_empty_rows: bool?, ?pad_empty_cells: bool?) → Enumerator[Array[untyped], void]

Source

# File lib/xlsxrb/stream_sheet.rb, line 278
def each_row_values(type_cast: false, trim_empty_rows: nil, pad_empty_rows: nil, pad_empty_cells: nil, &block)
  return enum_for(:each_row_values, type_cast: type_cast, trim_empty_rows: trim_empty_rows, pad_empty_rows: pad_empty_rows, pad_empty_cells: pad_empty_cells) unless block

  each_row(trim_empty_rows: trim_empty_rows, pad_empty_rows: pad_empty_rows, pad_empty_cells: pad_empty_cells) do |row|
    block.call(row.values(type_cast: type_cast))
  end
end

Iterates over row values directly as Arrays without wrapping cells in Elements::Cell objects.

@overload each_row_values(type_cast: false, trim_empty_rows: nil, pad_empty_rows: nil, pad_empty_cells: nil, &block)

@param type_cast [Boolean] Whether to coerce date/time serial numbers into Date/Time instances.
@param trim_empty_rows [Boolean, nil] Whether to omit trailing empty rows (defaults to sheet setting).
@param pad_empty_rows [Boolean, nil] Whether to yield empty rows for skipped row numbers (defaults to sheet setting).
@param pad_empty_cells [Boolean, nil] Whether to pad missing cells within rows (defaults to sheet setting).
@yield [values]
@yieldparam values [Array<Object>] Row values array.
@return [void]

@overload each_row_values(type_cast: false, trim_empty_rows: nil, pad_empty_rows: nil, pad_empty_cells: nil)

@param type_cast [Boolean] Whether to coerce date/time serial numbers into Date/Time instances.
@param trim_empty_rows [Boolean, nil] Whether to omit trailing empty rows (defaults to sheet setting).
@param pad_empty_rows [Boolean, nil] Whether to yield empty rows for skipped row numbers (defaults to sheet setting).
@param pad_empty_cells [Boolean, nil] Whether to pad missing cells within rows (defaults to sheet setting).
@return [Enumerator<Array<Object>, void>]

@api public

first_col ()

() → Integer?

Alias for: first_column

first_column ()

() → Integer?

Source

# File lib/xlsxrb/stream_sheet.rb, line 135
def first_column
  dimension_bounds&.[](1)
end

Returns the 1-based index of the first column defined in the sheet dimension, or nil.

@return [Integer, nil] @api public

Also aliased as: first_col

first_row ()

() → Integer?

Source

# File lib/xlsxrb/stream_sheet.rb, line 126
def first_row
  dimension_bounds&.[](0)
end

Returns the 1-based index of the first row defined in the sheet dimension, or nil.

@return [Integer, nil] @api public

formatted_value (ref_or_row, col = nil)

(String | Symbol | Integer ref_or_row, ?Integer? col) → String?

Source

# File lib/xlsxrb/stream_sheet.rb, line 458
def formatted_value(ref_or_row, col = nil)
  cell(ref_or_row, col)&.formatted_value
end

Returns the formatted string representation of a cell’s value.

@param ref_or_row [String, Symbol, Integer] Cell reference (e.g. “A1”) or 0-based row index. @param col [Integer, nil] Optional 0-based column index. @return [String, nil] @api public

hidden? ()

() → bool

Source

# File lib/xlsxrb/stream_sheet.rb, line 173
def hidden?
  @state == :hidden || @state == :very_hidden
end

Returns whether the sheet is hidden (:hidden or :very_hidden).

@return [Boolean] @api public

hyperlink (ref_or_row, col = nil)

(String | Symbol | Integer ref_or_row, ?Integer? col) → Hash[Symbol, untyped]?

Source

# File lib/xlsxrb/stream_sheet.rb, line 337
def hyperlink(ref_or_row, col = nil)
  ref = if col
          "#{Elements::Cell.column_letter(col)}#{ref_or_row.to_i + 1}"
        else
          ref_or_row.to_s.upcase
        end
  hyperlinks[ref]
end

Returns hyperlink metadata for a specific cell reference, or nil.

@param ref_or_row [String, Symbol, Integer] Cell reference (e.g. “A1”) or 0-based row index. @param col [Integer, nil] Optional 0-based column index. @return [Hash, nil] @api public

hyperlinks ()

() → Hash[String, Hash[Symbol, untyped]]

Source

# File lib/xlsxrb/stream_sheet.rb, line 326
def hyperlinks
  @hyperlinks ||= Xlsxrb.send(:resolve_hyperlinks, raw_sheet_xml, zip_reader: @zip_reader, entry_name: @entry_name)
end

Returns hyperlinks configured for this worksheet as a Hash of cell references to hyperlink hashes.

@return [Hash<String, Hash[Symbol, untyped]>] @api public

images ()

() → Array[Elements::Image]

Source

# File lib/xlsxrb/stream_sheet.rb, line 388
def images
  @images ||= Xlsxrb.send(:resolve_images, zip_reader: @zip_reader, entry_name: @entry_name)
end

Returns all embedded images in this worksheet.

@return [Array<Elements::Image>] @api public

images_at (ref_or_row, col = nil)

(String | Symbol | Integer ref_or_row, ?Integer? col) → Array[Elements::Image]

Source

# File lib/xlsxrb/stream_sheet.rb, line 399
def images_at(ref_or_row, col = nil)
  ref = if col
          "#{Elements::Cell.column_letter(col)}#{ref_or_row.to_i + 1}"
        else
          ref_or_row.to_s.upcase
        end
  images.select { |img| img.cell_ref == ref }
end

Returns all images anchored at a specific cell reference or 0-based coordinate.

@param ref_or_row [String, Symbol, Integer] Cell reference (e.g. “A1”) or 0-based row index. @param col [Integer, nil] Optional 0-based column index. @return [Array<Elements::Image>] @api public

last_col ()

() → Integer?

Alias for: last_column

last_column ()

() → Integer?

Source

# File lib/xlsxrb/stream_sheet.rb, line 154
def last_column
  dimension_bounds&.[](3)
end

Returns the 1-based index of the last column defined in the sheet dimension, or nil.

@return [Integer, nil] @api public

Also aliased as: last_col

last_row ()

() → Integer?

Source

# File lib/xlsxrb/stream_sheet.rb, line 145
def last_row
  dimension_bounds&.[](2)
end

Returns the 1-based index of the last row defined in the sheet dimension, or nil.

@return [Integer, nil] @api public

load ()

() → Elements::Worksheet

Source

# File lib/xlsxrb/stream_sheet.rb, line 316
def load
  Xlsxrb.send(:build_worksheet, @name, raw_sheet_xml, @shared_strings, @styles, state: @state, zip_reader: @zip_reader, entry_name: @entry_name, hyperlinks: hyperlinks, comments: comments, images: images, date1904: @date1904, dimension: dimension, trim_empty_rows: @trim_empty_rows, pad_empty_rows: @pad_empty_rows, pad_empty_cells: @pad_empty_cells)
end

Loads this sheet completely into an in-memory {Elements::Worksheet}, enabling coordinate random access (‘sheet`), row lookups (row_at), and immutable cell updates (update_cell).

@return [Elements::Worksheet] The fully parsed in-memory worksheet. @api public

Also aliased as: to_worksheet

merged_cells ()

() → Array[String]

Source

# File lib/xlsxrb/stream_sheet.rb, line 467
def merged_cells
  @merged_cells ||= begin
    listener = Ooxml::Reader::MergeCellsListener.new
    Ooxml::XmlParser.parse(raw_sheet_xml, listener)
    listener.ranges
  end
end

Returns merged cell ranges (e.g. [“A1:B2”]) for this worksheet.

@return [Array<String>] @api public

pad_empty_cells ()

() → bool

Alias for: pad_empty_cells?

pad_empty_cells? ()

() → bool

Source

# File lib/xlsxrb/stream_sheet.rb, line 104
def pad_empty_cells?
  @pad_empty_cells ? true : false
end

Returns whether missing cells within rows are padded during iteration.

@return [Boolean] @api public

Also aliased as: pad_empty_cells

pad_empty_rows ()

() → bool

Alias for: pad_empty_rows?

pad_empty_rows? ()

() → bool

Source

# File lib/xlsxrb/stream_sheet.rb, line 94
def pad_empty_rows?
  @pad_empty_rows ? true : false
end

Returns whether skipped rows are padded during iteration.

@return [Boolean] @api public

Also aliased as: pad_empty_rows

to_worksheet ()

() → Elements::Worksheet

Alias for: load

trim_empty_rows ()

() → bool

Alias for: trim_empty_rows?

trim_empty_rows? ()

() → bool

Source

# File lib/xlsxrb/stream_sheet.rb, line 84
def trim_empty_rows?
  @trim_empty_rows ? true : false
end

Returns whether trailing empty rows are omitted during iteration.

@return [Boolean] @api public

Also aliased as: trim_empty_rows

visible? ()

() → bool

Source

# File lib/xlsxrb/stream_sheet.rb, line 182
def visible?
  @state == :visible
end

Returns whether the sheet is visible.

@return [Boolean] @api public

with_images () { |self| ... }

() { (StreamSheet) → void } → void
() → StreamSheet

Source

# File lib/xlsxrb/stream_sheet.rb, line 416
def with_images
  images
  if block_given?
    yield self
    nil
  else
    self
  end
end

Loads and returns images for this worksheet, optionally yielding self for block syntax.

@yield [sheet] @yieldparam sheet [StreamSheet] @return [StreamSheet, void] @api public