Class: Xlsxrb::WorkbookBuilder

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

Overview

DSL context for building an in-memory Elements::Workbook in build.

Examples:

Build an in-memory workbook with multiple sheets

workbook = Xlsxrb.build do |builder|
  builder.sheet("Sales") do |sheet|
    sheet.row(["Product", "Revenue"])
    sheet.row(["Widget", 1000])
  end
  builder.core_property(:creator, "Reporting System")
end

Instance Method Summary collapse

Constructor Details

#initialize(strict_excel_mode: true) ⇒ WorkbookBuilder

: (?strict_excel_mode: bool) -> void

Parameters:

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

    Whether to enforce Microsoft Excel specifications.

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


23
24
25
26
27
28
29
30
31
32
33
 
# File 'lib/xlsxrb/workbook_builder.rb', line 23

def initialize(strict_excel_mode: true)
  @strict_excel_mode = strict_excel_mode
  @sheets = []
  @sheet_builders = [] # Keep track of sheet builders for style processing
  @defined_names = []
  @core_properties = {}
  @app_properties = {}
  @custom_properties = []
  @workbook_protection = nil
  @workbook_properties = { update_links: "never" }
end

Instance Method Details

#absolute_range(range) ⇒ Object

: (untyped range) -> untyped

Parameters:

  • range (Object)

Returns:

  • (Object) 


212
213
214
 
# File 'lib/xlsxrb/workbook_builder.rb', line 212

def absolute_range(range)
  range.gsub(/([A-Z]+)(\d+)/, '$\1$\2')
end

#app_property(name, value) ⇒ void

This method returns an undefined value.

Sets an extended application metadata property.

: (Symbol name, String | Integer | Time value) -> void

Parameters:

  • name (Symbol) —

    Application property name (:company, :manager, :app_version, etc.).

  • value (String, Integer, Time) —

    Property value.



151
152
153
 
# File 'lib/xlsxrb/workbook_builder.rb', line 151

def app_property(name, value)
  @app_properties[name] = value
end

#build ⇒ Elements::Workbook

Builds and returns the compiled Elements::Workbook.

: () -> Elements::Workbook

Returns:

Raises:

  • (ArgumentError) —

    If workbook contains zero sheets in strict mode.



187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
 
# File 'lib/xlsxrb/workbook_builder.rb', line 187

def build
  raise ArgumentError, "Workbook must contain at least one sheet (Excel limitation)" if @strict_excel_mode && @sheets.empty?

  # Process styles from all sheets and collect style definitions
  processed_sheets, styles_definition = process_styles(@sheets)

  # Store workbook-level metadata in unmapped_data
  wb_meta = {}
  wb_meta[:defined_names] = resolve_defined_names(@defined_names, processed_sheets) unless @defined_names.empty?
  wb_meta[:core_properties] = @core_properties unless @core_properties.empty?
  wb_meta[:app_properties] = @app_properties unless @app_properties.empty?
  wb_meta[:custom_properties] = @custom_properties unless @custom_properties.empty?
  wb_meta[:workbook_protection] = @workbook_protection if @workbook_protection
  wb_meta[:workbook_properties] = @workbook_properties unless @workbook_properties.empty?

  Elements::Workbook.new(
    sheets: processed_sheets,
    styles: styles_definition,
    unmapped_data: wb_meta.empty? ? {} : { facade: wb_meta }
  )
end

#core_property(name, value) ⇒ void

This method returns an undefined value.

Sets a Dublin Core document metadata property.

: (Symbol name, String | Integer | Time value) -> void

Parameters:

  • name (Symbol) —

    Core property name (:creator, :title, :subject, :description, etc.).

  • value (String, Integer, Time) —

    Property value.



140
141
142
 
# File 'lib/xlsxrb/workbook_builder.rb', line 140

def core_property(name, value)
  @core_properties[name] = value
end

#custom_property(name, value, type: :string) ⇒ void

This method returns an undefined value.

Adds a custom document property.

: (String name, String | Integer | Float | bool | Time value, ?type: ::Symbol) -> void

Parameters:

  • name (String) —

    Property name.

  • value (String, Integer, Float, Boolean, Time) —

    Property value.

  • type (Symbol) (defaults to: :string) —

    Value type (:string, :number, :bool, :date).

  • type: (::Symbol) (defaults to: :string) 


177
178
179
 
# File 'lib/xlsxrb/workbook_builder.rb', line 177

def custom_property(name, value, type: :string)
  @custom_properties << { name: name, value: value, type: type }
end

#defined_name(name, value, sheet: nil, hidden: false) ⇒ void

This method returns an undefined value.

Adds a defined name (named range or formula constant) to the workbook.

: (String name, String value, ?sheet: String?, ?hidden: bool) -> void

Parameters:

  • name (String) —

    Defined name (e.g. "TaxRate").

  • value (String) —

    Formula expression or range reference (e.g. "Sheet1!$B$2").

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

    Optional sheet name for sheet-scoped defined names.

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

    Whether the defined name is hidden from Excel's UI.

  • sheet: (String, nil) (defaults to: nil)
  • hidden: (Boolean) (defaults to: false) 


85
86
87
88
89
 
# File 'lib/xlsxrb/workbook_builder.rb', line 85

def defined_name(name, value, sheet: nil, hidden: false)
  entry = { name: name, value: value, hidden: hidden }
  entry[:local_sheet_name] = sheet if sheet
  @defined_names << entry
end

#extract_styles_from_writer(writer) ⇒ { fonts: untyped, fills: untyped, borders: untyped, xf_entries: untyped, num_fmts: untyped }

: (untyped writer) -> { fonts: untyped, fills: untyped, borders: untyped, xf_entries: untyped, num_fmts: untyped }

Parameters:

  • writer (Object)

Returns:

  • ({ fonts: untyped, fills: untyped, borders: untyped, xf_entries: untyped, num_fmts: untyped }) 


297
298
299
300
301
302
303
304
305
306
307
 
# File 'lib/xlsxrb/workbook_builder.rb', line 297

def extract_styles_from_writer(writer)
  # Extract style definitions from the writer that can be reused
  # This captures the fonts, fills, borders, and xf entries that were created
  {
    fonts: writer.fonts.dup,
    fills: writer.fills.dup,
    borders: writer.borders.dup,
    xf_entries: writer.xf_entries.dup,
    num_fmts: writer.num_fmts.dup
  }
end

#print_area(range, sheet: nil) ⇒ void

This method returns an undefined value.

Sets the print area for a sheet.

: (String range, ?sheet: String?) -> void

Parameters:

  • range (String) —

    Cell range (e.g. "A1:G50").

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

    Target sheet name (defaults to latest or "Sheet1").

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


98
99
100
101
102
103
 
# File 'lib/xlsxrb/workbook_builder.rb', line 98

def print_area(range, sheet: nil)
  sheet_name = sheet || @sheets.last&.name || "Sheet1"
  value = "'#{sheet_name}'!#{absolute_range(range)}"
  @defined_names.reject! { |dn| dn[:name] == "_xlnm.Print_Area" && dn[:local_sheet_name] == sheet_name }
  defined_name("_xlnm.Print_Area", value, sheet: sheet_name)
end

#print_titles(rows: nil, cols: nil, sheet: nil) ⇒ void

This method returns an undefined value.

Sets repeating print titles (rows and/or columns) for pagination.

: (?rows: String?, ?cols: String?, ?sheet: String?) -> void

Parameters:

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

    Repeating row range (e.g. "1:2").

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

    Repeating column range (e.g. "A:B").

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

    Target sheet name.

  • rows: (String, nil) (defaults to: nil)
  • cols: (String, nil) (defaults to: nil)
  • sheet: (String, nil) (defaults to: nil) 


113
114
115
116
117
118
119
120
121
 
# File 'lib/xlsxrb/workbook_builder.rb', line 113

def print_titles(rows: nil, cols: nil, sheet: nil)
  sheet_name = sheet || @sheets.last&.name || "Sheet1"
  parts = []
  parts << "'#{sheet_name}'!$#{cols.sub(":", ":$")}" if cols
  parts << "'#{sheet_name}'!$#{rows.sub(":", ":$")}" if rows
  value = parts.join(",")
  @defined_names.reject! { |dn| dn[:name] == "_xlnm.Print_Titles" && dn[:local_sheet_name] == sheet_name }
  defined_name("_xlnm.Print_Titles", value, sheet: sheet_name)
end

#process_styles(sheets) ⇒ ::Array[untyped | ::Hash[untyped, untyped]], ::Array[untyped]

: (untyped sheets) -> (::Array[untyped | ::Hash[untyped, untyped]] | ::Array)

Parameters:

  • sheets (Object)

Returns:

  • (::Array[untyped | ::Hash[untyped, untyped]], ::Array[untyped]) 


231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
 
# File 'lib/xlsxrb/workbook_builder.rb', line 231

def process_styles(sheets)
  # Collect all unique StyleBuilders from all sheets
  all_style_builders = {}
  @sheet_builders.each do |sheet_builder|
    sheet_builder.styles.each do |style_name, style_builder|
      all_style_builders[style_name] = style_builder
    end
  end

  return [sheets, {}] if all_style_builders.empty?

  # Create a temporary writer to register styles and get numeric IDs
  temp_writer = Ooxml::Writer.new
  style_name_to_id = {}
  all_style_builders.each do |name, builder|
    style_id = builder.register_with(temp_writer)
    style_name_to_id[name] = style_id
  end

  # Capture the style definitions from the temporary writer
  styles_definition = extract_styles_from_writer(temp_writer)

  # Update all cells with their resolved style IDs
  updated_sheets = sheets.map do |sheet|
    new_rows = sheet.rows.map do |row|
      new_cells = row.cells.map do |cell|
        # If style_index is a string (style name), resolve it to a numeric ID
        if cell.style_index.is_a?(String) && style_name_to_id.key?(cell.style_index)
          Elements::Cell.new(
            row_index: cell.row_index,
            column_index: cell.column_index,
            value: cell.value,
            formula: cell.formula,
            style_index: style_name_to_id[cell.style_index],
            unmapped_data: cell.unmapped_data,
            errors: cell.errors
          )
        else
          cell
        end
      end
      Elements::Row.new(
        index: row.index,
        cells: new_cells,
        height: row.height,
        hidden: row.hidden,
        custom_height: row.custom_height,
        outline_level: row.outline_level,
        unmapped_data: row.unmapped_data,
        errors: row.errors
      )
    end
    Elements::Worksheet.new(
      name: sheet.name,
      rows: new_rows,
      columns: sheet.columns,
      charts: sheet.charts,
      unmapped_data: sheet.unmapped_data,
      errors: sheet.errors
    )
  end

  [updated_sheets, styles_definition]
end

#properties(core: nil, app: nil, custom: nil) ⇒ void

This method returns an undefined value.

Sets multiple core, app, and/or custom properties simultaneously.

: (?core: Hash[Symbol, String | Integer | Time]?, ?app: Hash[Symbol, String | Integer | Time]?, ?custom: Hash[String | Symbol, untyped]?) -> void

Parameters:

  • core (Hash{Symbol => Object}, nil) (defaults to: nil) —

    Core properties map.

  • app (Hash{Symbol => Object}, nil) (defaults to: nil) —

    App properties map.

  • custom (Hash{String, Symbol => Object}, nil) (defaults to: nil) —

    Custom properties map.

  • core: (Hash[Symbol, String | Integer | Time], nil) (defaults to: nil)
  • app: (Hash[Symbol, String | Integer | Time], nil) (defaults to: nil)
  • custom: (Hash[String | Symbol, untyped], nil) (defaults to: nil) 


163
164
165
166
167
 
# File 'lib/xlsxrb/workbook_builder.rb', line 163

def properties(core: nil, app: nil, custom: nil)
  core&.each { |k, v| core_property(k, v) }
  app&.each { |k, v| app_property(k, v) }
  custom&.each { |k, v| custom_property(k.to_s, v) }
end

#protect_workbook(**opts) ⇒ void

This method returns an undefined value.

Sets workbook structure and window protection.

: (**String | Integer | bool | nil opts) -> void

Parameters:

  • opts (Hash) —

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



129
130
131
 
# File 'lib/xlsxrb/workbook_builder.rb', line 129

def protect_workbook(**opts)
  @workbook_protection = opts
end

#resolve_defined_names(names, sheets) ⇒ Object

: (untyped names, untyped sheets) -> untyped

Parameters:

  • names (Object)
  • sheets (Object)

Returns:

  • (Object) 


217
218
219
220
221
222
223
224
225
226
227
228
 
# File 'lib/xlsxrb/workbook_builder.rb', line 217

def resolve_defined_names(names, sheets)
  sheet_names = sheets.map(&:name)
  names.map do |dn|
    resolved = dn.dup
    if dn[:local_sheet_name]
      idx = sheet_names.index(dn[:local_sheet_name])
      resolved[:local_sheet_id] = idx if idx
      resolved.delete(:local_sheet_name)
    end
    resolved
  end
end

#sheet(name = nil, **opts) {|sheet_builder| ... } ⇒ Elements::Worksheet Also known as: []

Adds a new worksheet to the workbook.

: (?String? name, **untyped opts) ?{ (WorksheetBuilder) -> void } -> untyped

Parameters:

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

    Sheet name (max 31 chars, no forbidden chars [ ] * ? / \).

  • opts (Hash) —

    Sheet properties.

Yields:

  • (sheet_builder)

Yield Parameters:

Returns:

Raises:

  • (ArgumentError) —

    If sheet name violates Excel limits in strict mode.



60
61
62
63
64
65
66
67
68
69
70
71
 
# File 'lib/xlsxrb/workbook_builder.rb', line 60

def sheet(name = nil, **opts)
  name ||= "Sheet#{@sheets.size + 1}"
  raise ArgumentError, "Sheet name '#{name}' must be <= 31 characters (Excel limitation)" if @strict_excel_mode && name.length > 31
  raise ArgumentError, "Sheet name '#{name}' contains invalid characters (ECMA-376 OOXML specification)" if name.match?(%r{[\[\]*?/\\]})
  raise ArgumentError, "Sheet name '#{name}' is already used. Excel requires unique sheet names." if @strict_excel_mode && @sheets.map { |s| s.respond_to?(:name) ? s.name.downcase : s.to_s.downcase }.include?(name.downcase)

  sheet_builder = WorksheetBuilder.new(name, strict_excel_mode: @strict_excel_mode)
  opts.each { |k, v| sheet_builder.sheet_properties(k, v) }
  yield sheet_builder if block_given?
  @sheet_builders << sheet_builder
  @sheets << sheet_builder.build
end

#workbook_property(name, value) ⇒ void

Note:

SECURITY WARNING: If you set :update_links to anything other than "never", you may expose end-users to malicious external reference vulnerabilities (e.g., CSV/DDE Injection) when they open the generated Excel file. Ensure you fully trust the exported data.

This method returns an undefined value.

Sets a workbook-level property.

: (Symbol name, String | Integer | bool value) -> (String | Integer | bool)

Parameters:

  • name (Symbol) —

    Property name (e.g. :update_links).

  • value (String, Integer, Boolean) —

    Property value.



46
47
48
 
# File 'lib/xlsxrb/workbook_builder.rb', line 46

def workbook_property(name, value)
  @workbook_properties[name] = value
end