Class: RubyXL::Workbook

Inherits:

OOXMLTopLevelObject

• Object
• OOXMLObject
• OOXMLTopLevelObject
• RubyXL::Workbook

Includes:

Enumerable, RelationshipSupport

Defined in:

lib/rubyXL/objects/workbook.rb

Overview

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

Constant Summary

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

• #is_template ⇒ Object

  Returns the value of attribute is_template.

• #worksheets ⇒ Object

  Returns the value of attribute worksheets.

Attributes included from RelationshipSupport

#generic_storage, #relationship_container

Attributes inherited from OOXMLTopLevelObject

#root

Attributes included from OOXMLObjectInstanceMethods

#local_namespaces

Instance Method Summary

• #[](ind) ⇒ Object

  Finds worksheet by its name or numerical index.

• #add_worksheet(name = nil) ⇒ Object

  Create new simple worksheet and add it to the workbook worksheets.

• #application ⇒ Object
• #application=(v) ⇒ Object
• #appversion ⇒ Object
• #appversion=(v) ⇒ Object
• #before_write_xml ⇒ Object
• #company ⇒ Object
• #company=(v) ⇒ Object
• #content_type ⇒ Object
• #created_at ⇒ Object
• #created_at=(v) ⇒ Object
• #creator ⇒ Object
• #creator=(v) ⇒ Object
• #date1904 ⇒ Object
• #date1904=(v) ⇒ Object
• #date_to_num(date) ⇒ Object
• #initialize(worksheets = [], src_file_path = nil, creator = nil, modifier = nil, created_at = nil, company = '', application = APPLICATION, appversion = APPVERSION, date1904 = 0, is_template = false) ⇒ Workbook constructor

  A new instance of Workbook.

• #modified_at ⇒ Object
• #modified_at=(v) ⇒ Object
• #modifier ⇒ Object
• #modifier=(v) ⇒ Object
• #num_to_date(num) ⇒ Object
• #related_objects ⇒ Object
• #save(dst_file_path = nil) ⇒ Object (also: #write)

  Save the resulting XLSX file to the specified location.

• #stream ⇒ Object

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

• #xlsx_path ⇒ Object

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.

# File 'lib/rubyXL/objects/workbook.rb', line 447

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.

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

def is_template
  @is_template
end

#worksheets ⇒ Object

Returns the value of attribute worksheets.

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

def worksheets
  @worksheets
end

Instance Method Details

#[](ind) ⇒ Object

Finds worksheet by its name or numerical index

# File 'lib/rubyXL/objects/workbook.rb', line 480

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

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

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

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

def application
  root.document_properties.application&.value
end

#application=(v) ⇒ Object

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

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

#appversion ⇒ Object

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

def appversion
  root.document_properties.app_version&.value
end

#appversion=(v) ⇒ Object

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

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

#before_write_xml ⇒ Object

# 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
    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

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

def company
  root.document_properties.company&.value
end

#company=(v) ⇒ Object

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

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

#content_type ⇒ Object

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

# File 'lib/rubyXL/objects/workbook.rb', line 504

def created_at
  root.core_properties.created_at
end

#created_at=(v) ⇒ Object

# File 'lib/rubyXL/objects/workbook.rb', line 508

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

#creator ⇒ Object

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

def creator
  root.core_properties.creator
end

#creator=(v) ⇒ Object

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

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

#date1904 ⇒ Object

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

def date1904
  workbook_properties&.date1904
end

#date1904=(v) ⇒ Object

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

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

#date_to_num(date) ⇒ Object

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

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

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

def modified_at
  root.core_properties.modified_at
end

#modified_at=(v) ⇒ Object

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

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

#modifier ⇒ Object

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

def modifier
  root.core_properties.modifier
end

#modifier=(v) ⇒ Object

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

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

#num_to_date(num) ⇒ Object

# File 'lib/rubyXL/objects/workbook.rb', line 430

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

# 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

# File 'lib/rubyXL/objects/workbook.rb', line 399

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)

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

def stream
  root.stream
end

#xlsx_path ⇒ Object

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

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