Class: RubyXL::Workbook

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

Overview

www.datypic.com/sc/ooxml/e-ssml_workbook.html

Constant Summary collapse

CONTENT_TYPE = 
'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet.main+xml'.freeze
CONTENT_TYPE_TEMPLATE = 
'application/vnd.openxmlformats-officedocument.spreadsheetml.template.main+xml'.freeze
CONTENT_TYPE_TEMPLATE_WITH_MACROS = 
'application/vnd.ms-excel.template.macroEnabled.main+xml'.freeze
CONTENT_TYPE_WITH_MACROS = 
'application/vnd.ms-excel.sheet.macroEnabled.main+xml'.freeze
REL_TYPE = 
'http://schemas.openxmlformats.org/officeDocument/2006/relationships/officeDocument'.freeze
SHEET_NAME_FORBIDDEN_CHARS =

www.accountingweb.com/technology/excel/seven-characters-you-cant-use-in-worksheet-names

%r{[/\\*\[\]:?]}
SHEET_NAME_FORBIDDEN_NAMES =

answers.microsoft.com/en-us/msoffice/forum/all/excel-history-is-a-reserved-name-help/f8a398a4-b72c-48e3-82da-4f132c305e91

[ 'History' ]
DATE1904 = 
DateTime.new(1904, 1, 1)
DATE1899 =

Subtracting one day to accommodate 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.



454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
 
# File 'lib/rubyXL/objects/workbook.rb', line 454

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_template ⇒ Object

Returns the value of attribute is_template.



374
375
376
 
# File 'lib/rubyXL/objects/workbook.rb', line 374

def is_template
  @is_template
end

#worksheets ⇒ Object

Returns the value of attribute worksheets.



374
375
376
 
# File 'lib/rubyXL/objects/workbook.rb', line 374

def worksheets
  @worksheets
end

Instance Method Details

#[](ind) ⇒ Object

Finds worksheet by its name or numerical index



487
488
489
490
491
492
 
# File 'lib/rubyXL/objects/workbook.rb', line 487

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



497
498
499
500
501
502
503
504
505
506
507
508
509
 
# File 'lib/rubyXL/objects/workbook.rb', line 497

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

#application ⇒ Object 



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

def application
  root.document_properties.application&.value
end

#application=(v) ⇒ Object 



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

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

#appversion ⇒ Object 



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

def appversion
  root.document_properties.app_version&.value
end

#appversion=(v) ⇒ Object 



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

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

#before_write_xml ⇒ Object 



376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
 
# File 'lib/rubyXL/objects/workbook.rb', line 376

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
    raise "Worksheet name '#{sheet.sheet_name}' is forbidden" if SHEET_NAME_FORBIDDEN_NAMES.include?(sheet.sheet_name)

    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

#company ⇒ Object 



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

def company
  root.document_properties.company&.value
end

#company=(v) ⇒ Object 



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

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

#content_type ⇒ Object 



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

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_at ⇒ Object 



511
512
513
 
# File 'lib/rubyXL/objects/workbook.rb', line 511

def created_at
  root.core_properties.created_at
end

#created_at=(v) ⇒ Object 



515
516
517
 
# File 'lib/rubyXL/objects/workbook.rb', line 515

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

#creator ⇒ Object 



554
555
556
 
# File 'lib/rubyXL/objects/workbook.rb', line 554

def creator
  root.core_properties.creator
end

#creator=(v) ⇒ Object 



558
559
560
 
# File 'lib/rubyXL/objects/workbook.rb', line 558

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

#date1904 ⇒ Object 



570
571
572
 
# File 'lib/rubyXL/objects/workbook.rb', line 570

def date1904
  workbook_properties&.date1904
end

#date1904=(v) ⇒ Object 



574
575
576
577
 
# File 'lib/rubyXL/objects/workbook.rb', line 574

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

#date_to_num(date) ⇒ Object 



430
431
432
433
434
435
 
# File 'lib/rubyXL/objects/workbook.rb', line 430

def date_to_num(date)
  case date
  when Date, DateTime then (date.ajd - base_date.ajd).to_f
  when Time then ((date.to_r - base_date.to_time.to_r) / 86400).to_f
  end
end

#modified_at ⇒ Object 



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

def modified_at
  root.core_properties.modified_at
end

#modified_at=(v) ⇒ Object 



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

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

#modifier ⇒ Object 



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

def modifier
  root.core_properties.modifier
end

#modifier=(v) ⇒ Object 



566
567
568
 
# File 'lib/rubyXL/objects/workbook.rb', line 566

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

#num_to_date(num) ⇒ Object 



437
438
439
440
441
442
443
444
445
446
447
 
# File 'lib/rubyXL/objects/workbook.rb', line 437

def num_to_date(num)
  return nil if num.nil?

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

  dateparts = num.divmod(1)
  base_date + (dateparts[0] + ((dateparts[1] * 86400).round(6) / 86400))
end

#related_objects ⇒ Object 



327
328
329
 
# File 'lib/rubyXL/objects/workbook.rb', line 327

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



406
407
408
409
410
411
412
413
414
415
416
417
 
# File 'lib/rubyXL/objects/workbook.rb', line 406

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

#stream ⇒ Object

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



401
402
403
 
# File 'lib/rubyXL/objects/workbook.rb', line 401

def stream
  root.stream
end

#xlsx_path ⇒ Object 



396
397
398
 
# File 'lib/rubyXL/objects/workbook.rb', line 396

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