Module: Xlsxrb

Defined in:
lib/xlsxrb.rb,
lib/xlsxrb/ooxml.rb,
lib/xlsxrb/version.rb,
lib/xlsxrb/elements.rb,
lib/xlsxrb/ooxml/cfb.rb,
lib/xlsxrb/stream_row.rb,
lib/xlsxrb/ooxml/utils.rb,
lib/xlsxrb/elements/row.rb,
lib/xlsxrb/ooxml/crypto.rb,
lib/xlsxrb/ooxml/reader.rb,
lib/xlsxrb/ooxml/writer.rb,
lib/xlsxrb/elements/cell.rb,
lib/xlsxrb/style_builder.rb,
lib/xlsxrb/elements/types.rb,
lib/xlsxrb/elements/column.rb,
lib/xlsxrb/ooxml/xml_parser.rb,
lib/xlsxrb/ooxml/zip_reader.rb,
lib/xlsxrb/ooxml/zip_writer.rb,
lib/xlsxrb/elements/workbook.rb,
lib/xlsxrb/ooxml/xml_builder.rb,
lib/xlsxrb/elements/worksheet.rb,
lib/xlsxrb/ooxml/crypto/agile.rb,
lib/xlsxrb/ooxml/styles_parser.rb,
lib/xlsxrb/ooxml/zip_generator.rb,
lib/xlsxrb/ooxml/crypto/standard.rb,
lib/xlsxrb/ooxml/workbook_parser.rb,
lib/xlsxrb/ooxml/workbook_writer.rb,
lib/xlsxrb/ooxml/worksheet_parser.rb,
lib/xlsxrb/ooxml/worksheet_writer.rb,
lib/xlsxrb/elements/coordinate_access.rb,
lib/xlsxrb/ooxml/shared_strings_parser.rb,
sig/generated/xlsxrb.rbs,
sig/generated/xlsxrb/ooxml.rbs,
sig/generated/xlsxrb/version.rbs,
sig/generated/xlsxrb/elements.rbs,
sig/generated/xlsxrb/ooxml/cfb.rbs,
sig/generated/xlsxrb/stream_row.rbs,
sig/generated/xlsxrb/ooxml/utils.rbs,
sig/generated/xlsxrb/elements/row.rbs,
sig/generated/xlsxrb/ooxml/crypto.rbs,
sig/generated/xlsxrb/ooxml/reader.rbs,
sig/generated/xlsxrb/ooxml/writer.rbs,
sig/generated/xlsxrb/elements/cell.rbs,
sig/generated/xlsxrb/style_builder.rbs,
sig/generated/xlsxrb/elements/types.rbs,
sig/generated/xlsxrb/elements/column.rbs,
sig/generated/xlsxrb/ooxml/xml_parser.rbs,
sig/generated/xlsxrb/ooxml/zip_reader.rbs,
sig/generated/xlsxrb/ooxml/zip_writer.rbs,
sig/generated/xlsxrb/elements/workbook.rbs,
sig/generated/xlsxrb/ooxml/xml_builder.rbs,
sig/generated/xlsxrb/elements/worksheet.rbs,
sig/generated/xlsxrb/ooxml/crypto/agile.rbs,
sig/generated/xlsxrb/ooxml/styles_parser.rbs,
sig/generated/xlsxrb/ooxml/zip_generator.rbs,
sig/generated/xlsxrb/ooxml/crypto/standard.rbs,
sig/generated/xlsxrb/ooxml/workbook_parser.rbs,
sig/generated/xlsxrb/ooxml/workbook_writer.rbs,
sig/generated/xlsxrb/ooxml/worksheet_parser.rbs,
sig/generated/xlsxrb/ooxml/worksheet_writer.rbs,
sig/generated/xlsxrb/elements/coordinate_access.rbs,
sig/generated/xlsxrb/ooxml/shared_strings_parser.rbs

Overview

Ruby XLSX read/write library.

Defined Under Namespace

Modules: Elements, Ooxml Classes: ChartBuilder, DecryptionError, EncryptedFileError, Error, InvalidPasswordError, ParseError, StreamRow, StreamSheet, StreamWriter, StyleBuilder, ValidationError, WorkbookBuilder, WorksheetBuilder, ZipError

Constant Summary collapse

TRACER =

Returns:

  • (Object) 
OpenTelemetry.tracer_provider.tracer("xlsxrb", Xlsxrb::VERSION)
VERSION =

Returns:

  • (::String) 
"0.1.9"

Class Method Summary collapse

Class Method Details

.build(strict_excel_mode: true) {|builder| ... } ⇒ Elements::Workbook

Builds an in-memory Elements::Workbook using a declarative DSL.

: (?strict_excel_mode: bool) ?{ (WorkbookBuilder) -> void } -> Elements::Workbook

Examples:

Build in-memory workbook

workbook = Xlsxrb.build do |builder|
  builder.sheet("Overview") do |sheet|
    sheet.row(["Title", "Date"])
    sheet.row(["Report", Date.today])
  end
end

Parameters:

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

    Whether to enforce Excel specifications.

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

Yields:

  • (builder)

Yield Parameters:

Returns:



791
792
793
794
795
796
797
798
799
 
# File 'lib/xlsxrb.rb', line 791

def self.build(strict_excel_mode: true)
  raise Error, "block is required" unless block_given?

  Xlsxrb.in_span("Xlsxrb.build") do
    builder = WorkbookBuilder.new(strict_excel_mode: strict_excel_mode)
    yield builder
    builder.build
  end
end

.build_raw_cell_from_value(row_index, col_index, value, sst, sst_index) ⇒ Object

Builds a raw cell hash from a value for streaming writes. : (untyped row_index, untyped col_index, untyped value, untyped sst, untyped sst_index) -> untyped

Parameters:

  • row_index (Object)
  • col_index (Object)
  • value (Object)
  • sst (Object)
  • sst_index (Object)

Returns:

  • (Object) 


3374
3375
3376
3377
3378
3379
3380
3381
3382
3383
3384
3385
3386
3387
3388
3389
3390
3391
3392
3393
3394
3395
3396
3397
3398
3399
3400
3401
3402
3403
3404
3405
3406
3407
3408
3409
3410
 
# File 'lib/xlsxrb.rb', line 3374

def self.build_raw_cell_from_value(row_index, col_index, value, sst, sst_index)
  # simplecov:disable
  # Edge case / untested delegation block
  ref = "#{Elements::Cell.column_letter(col_index)}#{row_index + 1}"
  result = { ref: ref }

  case value
  when Elements::Formula
    result[:formula] = value.expression
    result[:formula_ca] = true if value.calculate_always
    result[:value] = value.cached_value if value.cached_value
  when String
    idx = sst_index[value] ||= begin
      sst << value
      sst.size - 1
    end
    result[:value] = idx
    result[:type] = "s"
  when true, false
    result[:value] = value
    result[:type] = "b"
  when Integer, Float
    result[:value] = value
  when Date
    result[:value] = Xlsxrb::Ooxml::Utils.date_to_serial(value)
  when Time
    result[:value] = Xlsxrb::Ooxml::Utils.datetime_to_serial(value)
  # simplecov:enable
  when NilClass
    # empty cell
  end

  # simplecov:disable
  # Edge case / untested delegation block
  result
  # simplecov:enable
end

.formula(expression, cached_value: nil) ⇒ Elements::Formula

Creates a Formula object for use in row values.

: (String expression, ?cached_value: String | Numeric | bool | nil) -> Elements::Formula

Examples:

Create a basic sum formula

formula = Xlsxrb.formula("SUM(A1:A10)")

Create a formula with precomputed cached value

formula = Xlsxrb.formula("A1+B1", cached_value: 42)

Parameters:

  • expression (String) —

    The formula text without '=' (e.g. "SUM(A1:A10)").

  • cached_value (Object, nil) (defaults to: nil) —

    Optional cached result. If nil, Excel will calculate on open.

  • cached_value: (String, Numeric, bool, nil) (defaults to: nil)

Returns:



360
361
362
363
364
365
366
 
# File 'lib/xlsxrb.rb', line 360

def self.formula(expression, cached_value: nil)
  Elements::Formula.new(
    expression: expression,
    cached_value: cached_value,
    calculate_always: cached_value.nil? || nil
  )
end

.in_span(name, attributes: nil) ⇒ void

This method returns an undefined value.

Parameters:

  • name (Object)
  • attributes: (Object) (defaults to: nil) 


52
53
54
55
56
57
58
59
60
61
62
63
 
# File 'lib/xlsxrb.rb', line 52

def self.in_span(name, attributes: nil, &)
  if defined?(Ractor) && Ractor.current != Ractor.main
    # simplecov:disable
    # Test suite runs in the main Ractor. This branch is for multi-threaded usage via Ractors.
    yield
    # simplecov:enable
  elsif attributes
    TRACER.in_span(name, attributes: attributes, &)
  else
    TRACER.in_span(name, &)
  end
end

.modify(source, target = nil, password: nil) {|workbook| ... } ⇒ void

This method returns an undefined value.

Modifies an existing XLSX file. Reads the workbook, passes it to the block, and writes the result. The block receives an Elements::Workbook and must return a modified one (e.g. via update_sheet). If no target is given, the source is overwritten.

: (untyped source, ?untyped target, ?password: String?) ?{ (Elements::Workbook) -> untyped } -> void

Examples:

Modify a template and save to new file

Xlsxrb.modify("template.xlsx", "output.xlsx") do |workbook|
  workbook.update_sheet("Sheet1") do |sheet|
    sheet.update_cell("B1", value: "Updated Title")
         .update_cell("B2", value: 100)
  end
end

Parameters:

  • source (String, IO) —

    The source file path or IO object.

  • target (String, IO, nil) (defaults to: nil) —

    The target file path or IO object. If nil, overwrites source.

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

    Optional password for reading and writing protected files.

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

Yields:

  • (workbook) —

    Yields the parsed workbook.

Yield Parameters:

Yield Returns:



663
664
665
666
667
668
669
670
671
672
673
 
# File 'lib/xlsxrb.rb', line 663

def self.modify(source, target = nil, password: nil)
  raise Error, "source is required" if source.nil?
  raise Error, "block is required" unless block_given?

  workbook = read(source, password: password).load
  result_workbook = yield workbook
  result_workbook = workbook unless result_workbook.is_a?(Elements::Workbook)

  write_target = target || source
  write(write_target, result_workbook, password: password)
end

.read(source, password:) ⇒ void .read(source, password:) ⇒ Elements::Workbook

Reads an XLSX file (streaming / lazy-loaded by default) from a file path, IO stream, or binary String.

Sheets and rows are streamed lazily with O(1) constant memory. If a block is given, yields each StreamSheet sequentially.

Call #load on the returned Workbook or Sheet to convert to an in-memory representation for coordinate random access (e.g. sheet).

: (untyped source, ?password: String?) { (StreamSheet) -> void } -> void : (untyped source, ?password: String?) -> Elements::Workbook

Examples:

Streaming read across sheets and rows (O(1) memory)

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

Lazy workbook access and explicit in-memory loading

wb = Xlsxrb.read("data.xlsx")
sheet = wb.sheets.first
sheet.each_row { |row| ... }   # streams with O(1) memory
doc_sheet = sheet.load         # explicitly load into memory
puts doc_sheet["A1"].value     # coordinate random access

Overloads:

  • .read(source, password:) ⇒ void

    This method returns an undefined value.

    Parameters:

    • source (Object)
    • password: (String, nil)
  • .read(source, password:) ⇒ Elements::Workbook

    Parameters:

    • source (Object)
    • password: (String, nil)

    Returns:

Parameters:

  • source (String, IO) —

    File path, binary content string (starting with PK..), or IO object.

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

    Optional password to decrypt password-protected XLSX files.

Yields:

  • (sheet) —

    Yields each streaming sheet.

Yield Parameters:

  • sheet (StreamSheet) —

    The streaming worksheet object.

Returns:



399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
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
463
464
465
466
467
468
 
# File 'lib/xlsxrb.rb', line 399

def self.read(source, password: nil, &)
  if source.is_a?(String)
    if source.start_with?("PK\x03\x04") || source.include?("\x00") || Ooxml::Cfb::Reader.cfb?(source)
      if Ooxml::Cfb::Reader.cfb?(source)
        decrypted_zip = Ooxml::Crypto.decrypt(source, password)
        source = StringIO.new(decrypted_zip)
      else
        source = StringIO.new(source)
      end
    elsif File.file?(source)
      first_bytes = begin
        File.binread(source, 8)
      rescue StandardError
        nil
      end
      if Ooxml::Cfb::Reader.cfb?(first_bytes)
        encrypted_data = File.binread(source)
        decrypted_zip = Ooxml::Crypto.decrypt(encrypted_data, password)
        source = StringIO.new(decrypted_zip)
      end
    end
  elsif source.respond_to?(:read) && source.respond_to?(:pos) && source.respond_to?(:seek)
    begin
      cur_pos = source.pos
      first_bytes = source.read(8)
      source.seek(cur_pos)
      if Ooxml::Cfb::Reader.cfb?(first_bytes)
        full_data = source.read
        decrypted_zip = Ooxml::Crypto.decrypt(full_data, password)
        source = StringIO.new(decrypted_zip)
      end
    rescue StandardError
      # Fall through to standard reader if seeking fails
    end
  end

  attributes = source.is_a?(String) ? { "filepath" => source } : {}
  Xlsxrb.in_span("Xlsxrb.read", attributes: attributes) do
    entries = Ooxml::ZipReader.open(source, &:read_all)
    shared_strings = Ooxml::SharedStringsParser.parse(entries["xl/sharedStrings.xml"])
    workbook_sheets = Ooxml::WorkbookParser.parse(entries["xl/workbook.xml"])
    rels = Ooxml::RelationshipsParser.parse(entries["xl/_rels/workbook.xml.rels"])
    styles = Ooxml::StylesParser.parse(entries["xl/styles.xml"])

    sheets = workbook_sheets.map do |sheet_info|
      target = rels[sheet_info[:r_id]]
      next nil unless target

      sheet_path = target.start_with?("/") ? target.delete_prefix("/") : "xl/#{target}"
      sheet_xml = entries[sheet_path]
      next nil if sheet_xml.nil? || sheet_xml.empty?

      StreamSheet.new(
        sheet_info[:name],
        sheet_xml,
        shared_strings,
        styles
      )
    end.compact

    wb = Elements::Workbook.new(sheets: sheets, shared_strings: shared_strings, styles: styles)

    if block_given?
      sheets.each(&)
      nil
    else
      wb
    end
  end
end

.rich_text(*runs, text: nil, **font_props) ⇒ Elements::RichText

Helper to easily create RichText objects. Supports both Xlsxrb.rich_text({ text: "A" }, { text: "B" }) and Xlsxrb.rich_text(text: "Hi", bold: true)

: (*untyped runs, ?text: String?, **untyped font_props) -> untyped

Parameters:

  • runs (Array<Hash>) —

    Optional rich text runs.

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

    Simple text.

  • font_props (Hash) —

    Font styling options (e.g., bold: true).

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

Returns:



75
76
77
78
 
# File 'lib/xlsxrb.rb', line 75

def self.rich_text(*runs, text: nil, **font_props)
  runs = [{ text: text, font: font_props }] if text
  Elements::RichText.new(runs: runs)
end

.write(target, password: nil, strict_excel_mode: true) {|stream_writer| ... } ⇒ void .write(workbook, password: nil) ⇒ String .write(target, workbook, password: nil) ⇒ void .write(workbook, password:, encryption_mode:) ⇒ String .write(target, workbook, password:, encryption_mode:) ⇒ void .write(target_or_workbook, workbook_or_nil, password:, encryption_mode:, strict_excel_mode:) ⇒ void

Writes an XLSX file or IO stream (streaming or in-memory), or returns a binary string.

: (Elements::Workbook workbook, ?password: String?, ?encryption_mode: Symbol) -> String : (untyped target, Elements::Workbook | untyped workbook, ?password: String?, ?encryption_mode: Symbol) -> void : (untyped target_or_workbook, ?Elements::Workbook | untyped workbook_or_nil, ?password: String?, ?encryption_mode: Symbol, ?strict_excel_mode: bool) ?{ (StreamWriter) -> void } -> untyped

Examples:

Streaming write to file

Xlsxrb.write("output.xlsx") do |writer|
  writer.sheet("Sheet1") { |s| s.row(["Hello", "World"]) }
end

Password-protected streaming write

Xlsxrb.write("protected.xlsx", password: "secret_password") do |writer|
  writer.sheet("Confidential") { |s| s.row(["Private Data", 100]) }
end

In-memory export to binary string

binary_data = Xlsxrb.write(workbook)

In-memory write to file

Xlsxrb.write("output.xlsx", workbook)

Overloads:

  • .write(target, password: nil, strict_excel_mode: true) {|stream_writer| ... } ⇒ void

    This method returns an undefined value.

    Streaming write: yields a StreamWriter context for high-speed, zero-allocation XLSX generation.

    Parameters:

    • target (String, IO) —

      Destination file path or writable IO object.

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

      Optional password to encrypt the generated XLSX file.

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

      Whether to enforce Excel specifications.

    Yields:

    • (stream_writer)

    Yield Parameters:

  • .write(workbook, password: nil) ⇒ String

    In-memory write: exports the workbook to an in-memory binary String.

    Parameters:

    • workbook (Elements::Workbook) —

      The workbook to write.

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

      Optional password to encrypt the binary string.

    Returns:

    • (String) —

      Binary data representing the XLSX file.

  • .write(target, workbook, password: nil) ⇒ void

    This method returns an undefined value.

    In-memory write: writes the workbook to a file path or IO stream.

    Parameters:

    • target (String, IO) —

      Destination file path or writable IO object.

    • workbook (Elements::Workbook) —

      The workbook to write.

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

      Optional password to encrypt the output file.

  • .write(workbook, password:, encryption_mode:) ⇒ String

    Parameters:

    Returns:

    • (String)
  • .write(target, workbook, password:, encryption_mode:) ⇒ void

    This method returns an undefined value.

    Parameters:

    • target (Object)
    • workbook (Elements::Workbook, untyped)
    • password: (String, nil)
    • encryption_mode: (Symbol)
  • .write(target_or_workbook, workbook_or_nil, password:, encryption_mode:, strict_excel_mode:) ⇒ void

    This method returns an undefined value.

    Parameters:

    • target_or_workbook (Object)
    • workbook_or_nil (Elements::Workbook, untyped)
    • password: (String, nil)
    • encryption_mode: (Symbol)
    • strict_excel_mode: (Boolean) 


514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
 
# File 'lib/xlsxrb.rb', line 514

def self.write(target_or_workbook, workbook_or_nil = nil, password: nil, encryption_mode: :standard, strict_excel_mode: true, &block)
  if block_given?
    target = target_or_workbook
    raise Error, "target is required" if target.nil?

    attributes = target.is_a?(String) ? { "filepath" => target } : {}
    return Xlsxrb.in_span("Xlsxrb.write", attributes: attributes) do
      if password && !password.empty?
        buf = StringIO.new
        buf.binmode
        stream_writer = StreamWriter.new(buf, strict_excel_mode: strict_excel_mode)
        begin
          yield stream_writer
          stream_writer.close
          plain_bytes = buf.string.b
          encrypted_bytes = Ooxml::Crypto.encrypt(plain_bytes, password, mode: encryption_mode)
          if target.is_a?(String)
            File.binwrite(target, encrypted_bytes)
          elsif target.respond_to?(:write)
            target.write(encrypted_bytes)
          end
        ensure
          stream_writer.cleanup!
        end
      else
        stream_writer = StreamWriter.new(target, strict_excel_mode: strict_excel_mode)
        begin
          yield stream_writer
          stream_writer.close
        ensure
          stream_writer.cleanup!
        end
      end
    end
  end

  if workbook_or_nil.nil?
    wb = target_or_workbook
    raise Error, "workbook must be an Elements::Workbook" unless wb.is_a?(Elements::Workbook)

    io = StringIO.new
    io.binmode
    write(io, wb, password: password, encryption_mode: encryption_mode)
    return io.string.b
  end

  target = target_or_workbook
  workbook = workbook_or_nil
  raise Error, "target is required" if target.nil?
  raise Error, "workbook must be an Elements::Workbook" unless workbook.is_a?(Elements::Workbook)

  attributes = target.is_a?(String) ? { "filepath" => target } : {}
  Xlsxrb.in_span("Xlsxrb.write", attributes: attributes) do
    sst = []
    sst_index = {}

    # Collect shared strings and build index without allocating new Hashes
    sheet_data = workbook.sheets.map do |raw_ws|
      ws = raw_ws.respond_to?(:load) ? raw_ws.load : raw_ws
      ws.rows.each do |row|
        row.cells.each do |cell|
          val = cell.value
          if (val.is_a?(String) || val.is_a?(Elements::RichText)) && !sst_index.key?(val)
            sst << val
            sst_index[val] = sst.size - 1
          end
        end
      end
      columns = ws.columns.map do |col|
        # simplecov:disable
        # Edge case / untested delegation block
        { index: col.index, width: col.width, hidden: col.hidden, custom_width: col.custom_width, outline_level: col.outline_level }
        # simplecov:enable
      end
      sd = { name: ws.name, rows: ws.rows, columns: columns }
      sd[:charts] = ws.charts unless ws.charts.empty?

      # Extract facade metadata from unmapped_data
      facade = ws.unmapped_data[:facade]
      facade&.each { |key, val| sd[key] = val }

      sd
    end

    # Extract workbook-level facade metadata
    wb_facade = workbook.unmapped_data[:facade] || {}

    if password && !password.empty?
      buf = StringIO.new
      buf.binmode
      Ooxml::WorkbookWriter.write(
        buf,
        sheets: sheet_data,
        shared_strings: sst,
        shared_strings_index: sst_index,
        styles: workbook.styles,
        defined_names: wb_facade[:defined_names],
        core_properties: wb_facade[:core_properties],
        app_properties: wb_facade[:app_properties],
        custom_properties: wb_facade[:custom_properties],
        workbook_protection: wb_facade[:workbook_protection],
        workbook_properties: wb_facade[:workbook_properties]
      )
      encrypted_bytes = Ooxml::Crypto.encrypt(buf.string.b, password, mode: encryption_mode)
      if target.is_a?(String)
        File.binwrite(target, encrypted_bytes)
      elsif target.respond_to?(:write)
        target.write(encrypted_bytes)
      end
    else
      Ooxml::WorkbookWriter.write(
        target,
        sheets: sheet_data,
        shared_strings: sst,
        shared_strings_index: sst_index,
        styles: workbook.styles,
        defined_names: wb_facade[:defined_names],
        core_properties: wb_facade[:core_properties],
        app_properties: wb_facade[:app_properties],
        custom_properties: wb_facade[:custom_properties],
        workbook_protection: wb_facade[:workbook_protection],
        workbook_properties: wb_facade[:workbook_properties]
      )
    end
  end
end