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'
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 =

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

/[\/\\\*\[\]\:\?]/
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) ⇒ Workbook

Returns a new instance of Workbook.



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
 
# File 'lib/rubyXL/objects/workbook.rb', line 434

def initialize(worksheets = [], src_file_path = nil, creator = nil, modifier = nil, created_at = nil,
               company = '', application = APPLICATION, appversion = APPVERSION, date1904 = 0)
  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
end

Instance Attribute Details

#worksheets ⇒ Object

Returns the value of attribute worksheets.



361
362
363
 
# File 'lib/rubyXL/objects/workbook.rb', line 361

def worksheets
  @worksheets
end

Instance Method Details

#[](ind) ⇒ Object

Finds worksheet by its name or numerical index



465
466
467
468
469
470
 
# File 'lib/rubyXL/objects/workbook.rb', line 465

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



475
476
477
478
479
480
481
482
483
484
485
486
487
 
# File 'lib/rubyXL/objects/workbook.rb', line 475

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 



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

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

#application=(v) ⇒ Object 



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

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

#appversion ⇒ Object 



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

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

#appversion=(v) ⇒ Object 



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

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

#before_write_xml ⇒ Object 



363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
 
# File 'lib/rubyXL/objects/workbook.rb', line 363

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

#company ⇒ Object 



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

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

#company=(v) ⇒ Object 



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

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

#content_type ⇒ Object 



311
312
313
 
# File 'lib/rubyXL/objects/workbook.rb', line 311

def content_type
  if macros then CONTENT_TYPE_WITH_MACROS else CONTENT_TYPE end
end

#created_at ⇒ Object 



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

def created_at
  root.core_properties.created_at
end

#created_at=(v) ⇒ Object 



493
494
495
 
# File 'lib/rubyXL/objects/workbook.rb', line 493

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

#creator ⇒ Object 



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

def creator
  root.core_properties.creator
end

#creator=(v) ⇒ Object 



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

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

#date1904 ⇒ Object 



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

def date1904
  workbook_properties && workbook_properties.date1904
end

#date1904=(v) ⇒ Object 



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

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

#date_to_num(date) ⇒ Object 



416
417
418
 
# File 'lib/rubyXL/objects/workbook.rb', line 416

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

#modified_at ⇒ Object 



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

def modified_at
  root.core_properties.modified_at
end

#modified_at=(v) ⇒ Object 



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

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

#modifier ⇒ Object 



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

def modifier
  root.core_properties.modifier
end

#modifier=(v) ⇒ Object 



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

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

#num_to_date(num) ⇒ Object 



420
421
422
423
424
425
426
427
 
# File 'lib/rubyXL/objects/workbook.rb', line 420

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

#related_objects ⇒ Object 



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

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



392
393
394
395
396
397
398
399
400
401
402
403
 
# File 'lib/rubyXL/objects/workbook.rb', line 392

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

  extension = File.extname(dst_file_path)
  unless %w{.xlsx .xlsm}.include?(extension.downcase)
    raise "Unsupported extension: #{extension} (only .xlsx and .xlsm 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)



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

def stream
  root.stream
end

#xlsx_path ⇒ Object 



382
383
384
 
# File 'lib/rubyXL/objects/workbook.rb', line 382

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