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 



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



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