Class: RubyXL::Workbook

Inherits:
OOXMLTopLevelObject show all
Includes:
Enumerable, RelationshipSupport
Defined in:
lib/rubyXL/objects/workbook.rb

Overview

Constant Summary collapse

CONTENT_TYPE =
'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet.main+xml'
CONTENT_TYPE_TEMPLATE =
'application/vnd.openxmlformats-officedocument.spreadsheetml.template.main+xml'
CONTENT_TYPE_TEMPLATE_WITH_MACROS =
'application/vnd.ms-excel.template.macroEnabled.main+xml'
CONTENT_TYPE_WITH_MACROS =
'application/vnd.ms-excel.sheet.macroEnabled.main+xml'
REL_TYPE =
'http://schemas.openxmlformats.org/officeDocument/2006/relationships/officeDocument'
SHEET_NAME_FORBIDDEN_CHARS =
/[\/\\\*\[\]\:\?]/
DATE1904 =
DateTime.new(1904, 1, 1)
DATE1899 =

Subtracting one day to accomodate for erroneous 1900 leap year compatibility only for 1900 based dates

DateTime.new(1899, 12, 31) - 1
MARCH_1_1900 =
61
APPLICATION =
'Microsoft Macintosh Excel'
APPVERSION =
'12.0000'
SHEET_NAME_TEMPLATE =
'Sheet%d'

Constants inherited from OOXMLTopLevelObject

OOXMLTopLevelObject::ROOT, OOXMLTopLevelObject::SAVE_ORDER

Instance Attribute Summary collapse

Attributes included from RelationshipSupport

#generic_storage, #relationship_container

Attributes inherited from OOXMLTopLevelObject

#root

Attributes included from OOXMLObjectInstanceMethods

#local_namespaces

Instance Method Summary collapse

Methods included from RelationshipSupport

#attach_relationship, #collect_related_objects, included, #load_relationships, #store_relationship

Methods inherited from OOXMLTopLevelObject

#add_to_zip, #file_index, parse_file, set_namespaces

Methods included from OOXMLObjectInstanceMethods

#==, included, #index_in_collection, #write_xml

Constructor Details

#initialize(worksheets = [], src_file_path = nil, creator = nil, modifier = nil, created_at = nil, company = '', application = APPLICATION, appversion = APPVERSION, date1904 = 0, is_template = false) ⇒ Workbook

Returns a new instance of Workbook.


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/rubyXL/objects/workbook.rb', line 440

def initialize(worksheets = [], src_file_path = nil, creator = nil, modifier = nil, created_at = nil,
               company = '', application = APPLICATION, appversion = APPVERSION, date1904 = 0,
               is_template = false)
  super()

  # Order of sheets in the +worksheets+ array corresponds to the order of pages in Excel UI.
  # SheetId's, rId's, etc. are completely unrelated to ordering.
  @worksheets = worksheets
  add_worksheet if @worksheets.empty?

  @theme                    = RubyXL::Theme.default
  @shared_strings_container = RubyXL::SharedStringsTable.new
  @stylesheet               = RubyXL::Stylesheet.default
  @relationship_container   = RubyXL::OOXMLRelationshipsFile.new
  @root                     = RubyXL::WorkbookRoot.default
  @root.workbook            = self
  @root.source_file_path    = src_file_path

  creation_time = DateTime.parse(created_at) rescue DateTime.now
  self.created_at  = creation_time
  self.modified_at = creation_time
  self.company     = company
  self.application = application
  self.appversion  = appversion
  self.creator     = creator
  self.modifier    = modifier
  self.date1904    = date1904 > 0
  self.is_template = is_template
end

Instance Attribute Details

#is_templateObject

Returns the value of attribute is_template


367
368
369
# File 'lib/rubyXL/objects/workbook.rb', line 367

def is_template
  @is_template
end

#worksheetsObject

Returns the value of attribute worksheets


366
367
368
# File 'lib/rubyXL/objects/workbook.rb', line 366

def worksheets
  @worksheets
end

Instance Method Details

#[](ind) ⇒ Object

Finds worksheet by its name or numerical index


473
474
475
476
477
478
# File 'lib/rubyXL/objects/workbook.rb', line 473

def [](ind)
  case ind
  when Integer then worksheets[ind]
  when String  then worksheets.find { |ws| ws.sheet_name == ind }
  end
end

#add_worksheet(name = nil) ⇒ Object

Create new simple worksheet and add it to the workbook worksheets

Parameters:

  • The (String)

    name for the new worksheet


483
484
485
486
487
488
489
490
491
492
493
494
495
# File 'lib/rubyXL/objects/workbook.rb', line 483

def add_worksheet(name = nil)
  if name.nil? then
    n = 0

    begin
      name = SHEET_NAME_TEMPLATE % (n += 1)
    end until self[name].nil?
  end

  new_worksheet = Worksheet.new(:workbook => self, :sheet_name => name)
  worksheets << new_worksheet
  new_worksheet
end

#applicationObject


522
523
524
# File 'lib/rubyXL/objects/workbook.rb', line 522

def application
  root.document_properties.application && root.document_properties.application.value
end

#application=(v) ⇒ Object


526
527
528
529
# File 'lib/rubyXL/objects/workbook.rb', line 526

def application=(v)
  root.document_properties.application ||= StringNode.new
  root.document_properties.application.value = v
end

#appversionObject


531
532
533
# File 'lib/rubyXL/objects/workbook.rb', line 531

def appversion
  root.document_properties.app_version && root.document_properties.app_version.value
end

#appversion=(v) ⇒ Object


535
536
537
538
# File 'lib/rubyXL/objects/workbook.rb', line 535

def appversion=(v)
  root.document_properties.app_version ||= StringNode.new
  root.document_properties.app_version.value = v
end

#before_write_xmlObject


369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
# File 'lib/rubyXL/objects/workbook.rb', line 369

def before_write_xml
  max_sheet_id = worksheets.collect(&:sheet_id).compact.max || 0

  self.sheets = RubyXL::Sheets.new

  worksheets.each { |sheet, i|
    rel = relationship_container.find_by_target(sheet.xlsx_path)

    raise "Worksheet name '#{sheet.sheet_name}' contains forbidden characters" if sheet.sheet_name =~ SHEET_NAME_FORBIDDEN_CHARS

    sheets << RubyXL::Sheet.new(:name     => sheet.sheet_name[0..30], # Max sheet name length is 31 char
                                :sheet_id => sheet.sheet_id || (max_sheet_id += 1),
                                :state    => sheet.state,
                                :r_id     => rel.id)
  }

  true
end

#companyObject


513
514
515
# File 'lib/rubyXL/objects/workbook.rb', line 513

def company
  root.document_properties.company && root.document_properties.company.value
end

#company=(v) ⇒ Object


517
518
519
520
# File 'lib/rubyXL/objects/workbook.rb', line 517

def company=(v)
  root.document_properties.company ||= StringNode.new
  root.document_properties.company.value = v
end

#content_typeObject


313
314
315
316
317
318
# File 'lib/rubyXL/objects/workbook.rb', line 313

def content_type
  content_type_name = 'CONTENT_TYPE'
  content_type_name << '_TEMPLATE' if is_template
  content_type_name << '_WITH_MACROS' if macros
  self.class.const_get(content_type_name)
end

#created_atObject


497
498
499
# File 'lib/rubyXL/objects/workbook.rb', line 497

def created_at
  root.core_properties.created_at
end

#created_at=(v) ⇒ Object


501
502
503
# File 'lib/rubyXL/objects/workbook.rb', line 501

def created_at=(v)
  root.core_properties.created_at = v
end

#creatorObject


540
541
542
# File 'lib/rubyXL/objects/workbook.rb', line 540

def creator
  root.core_properties.creator
end

#creator=(v) ⇒ Object


544
545
546
# File 'lib/rubyXL/objects/workbook.rb', line 544

def creator=(v)
  root.core_properties.creator = v
end

#date1904Object


556
557
558
# File 'lib/rubyXL/objects/workbook.rb', line 556

def date1904
  workbook_properties && workbook_properties.date1904
end

#date1904=(v) ⇒ Object


560
561
562
563
# File 'lib/rubyXL/objects/workbook.rb', line 560

def date1904=(v)
  self.workbook_properties ||= RubyXL::WorkbookProperties.new
  workbook_properties.date1904 = v
end

#date_to_num(date) ⇒ Object


422
423
424
# File 'lib/rubyXL/objects/workbook.rb', line 422

def date_to_num(date)
  date && (date.ajd - base_date().ajd).to_f
end

#modified_atObject


505
506
507
# File 'lib/rubyXL/objects/workbook.rb', line 505

def modified_at
  root.core_properties.modified_at
end

#modified_at=(v) ⇒ Object


509
510
511
# File 'lib/rubyXL/objects/workbook.rb', line 509

def modified_at=(v)
  root.core_properties.modified_at = v
end

#modifierObject


548
549
550
# File 'lib/rubyXL/objects/workbook.rb', line 548

def modifier
  root.core_properties.modifier
end

#modifier=(v) ⇒ Object


552
553
554
# File 'lib/rubyXL/objects/workbook.rb', line 552

def modifier=(v)
  root.core_properties.modifier = v
end

#num_to_date(num) ⇒ Object


426
427
428
429
430
431
432
433
# File 'lib/rubyXL/objects/workbook.rb', line 426

def num_to_date(num)
  # Bug-for-bug Excel compatibility (https://support.microsoft.com/kb/214058/)
  if num && num < MARCH_1_1900 then
    num += 1 unless workbook_properties && workbook_properties.date1904
  end

  num && (base_date + num)
end

320
321
322
# File 'lib/rubyXL/objects/workbook.rb', line 320

def related_objects
  [ calculation_chain, stylesheet, theme, shared_strings_container, macros ] + @worksheets
end

#save(dst_file_path = nil) ⇒ Object Also known as: write

Save the resulting XLSX file to the specified location


398
399
400
401
402
403
404
405
406
407
408
409
# File 'lib/rubyXL/objects/workbook.rb', line 398

def save(dst_file_path = nil)
  dst_file_path ||= root.source_file_path

  extension = File.extname(dst_file_path)
  unless %w{.xlsx .xlsm .xltx .xltm}.include?(extension.downcase)
    raise "Unsupported extension: #{extension} (only .xlsx, .xlsm, .xltx and .xltm files are supported)."
  end

  File.open(dst_file_path, "wb") { |output_file| FileUtils.copy_stream(root.stream, output_file) }

  return dst_file_path
end

#streamObject

Return the resulting XLSX file in a stream (useful for sending over HTTP)


393
394
395
# File 'lib/rubyXL/objects/workbook.rb', line 393

def stream
  root.stream
end

#xlsx_pathObject


388
389
390
# File 'lib/rubyXL/objects/workbook.rb', line 388

def xlsx_path
  ROOT.join('xl', 'workbook.xml')
end