class RobustExcelOle::Workbook

This class essentially wraps a Win32Ole Workbook object. You can apply all VBA methods (starting with a capital letter) that you would apply for a Workbook object. See docs.microsoft.com/en-us/office/vba/api/excel.workbook#methods

Constants

ABBREVIATIONS
CORE_DEFAULT_OPEN_OPTS
CheckCompatibility
DEFAULT_OPEN_OPTS
Saved
Visible

Attributes

excel[R]
ole_object[R]
ole_workbook[R]
stored_filename[R]

Public Class Methods

books() click to toggle source

@private

# File lib/robust_excel_ole/workbook.rb, line 1034
def self.books
  bookstore.books
end
bookstore() click to toggle source

@private

# File lib/robust_excel_ole/workbook.rb, line 1039
def self.bookstore   
  @@bookstore ||= Bookstore.new
end
close(file, opts = {if_unsaved: :raise}) click to toggle source

closes a given file if it is open @options opts [Symbol] :if_unsaved

# File lib/robust_excel_ole/workbook.rb, line 790
def self.close(file, opts = {if_unsaved: :raise})
  book = begin
    bookstore.fetch(file)
    rescue
      nil
    end
  book.close(opts) if book && book.alive?
end
create(filename, opts = { }) click to toggle source

creates, i.e., opens a new, empty workbook, and saves it under a given filename @param [String] filename the filename under which the new workbook should be saved @param [Hash] opts the options as in Workbook::open

# File lib/robust_excel_ole/workbook.rb, line 500
def self.create(filename, opts = { })
  open(filename, if_absent: :create)
end
excel_class() click to toggle source

@private

# File lib/robust_excel_ole/workbook.rb, line 1068
def self.excel_class    
  @excel_class ||= begin
    module_name = self.parent_name
    "#{module_name}::Excel".constantize        
  rescue NameError => e
    # trace "excel_class: NameError: #{e}"
    Excel
  end
end
for_modifying(arg, opts = { }, &block) click to toggle source
# File lib/robust_excel_ole/workbook.rb, line 574
def self.for_modifying(arg, opts = { }, &block)
  unobtrusively(arg, {writable: true}.merge(opts), &block)
end
for_reading(arg, opts = { }, &block) click to toggle source
# File lib/robust_excel_ole/workbook.rb, line 570
def self.for_reading(arg, opts = { }, &block)
  unobtrusively(arg, {writable: false}.merge(opts), &block)
end
new(file_or_workbook, opts = { }) click to toggle source

opens a workbook. @param [String,Pathname] file_or_workbook a file name (string or pathname) or WIN32OLE workbook @param [Hash] opts the options @option opts [Hash] :default or :d @option opts [Hash] :force or :f @option opts [Symbol] :if_unsaved :raise (default), :forget, :save, :accept, :alert, :excel, or :new_excel @option opts [Symbol] :if_blocked :raise (default), :forget, :save, :close_if_saved, or _new_excel @option opts [Symbol] :if_absent :raise (default) or :create @option opts [Boolean] :read_only true (default) or false @option opts [Boolean] :update_links :never (default), :always, :alert @option opts [Boolean] :calculation :manual, :automatic, or nil (default) options: :default : if the workbook was already open before, then use (unchange) its properties,

otherwise, i.e. if the workbook cannot be reopened, use the properties stated in :default

:force : no matter whether the workbook was already open before, use the properties stated in :force :default and :force contain: :excel

:excel   :current (or :active or :reuse)
                  -> connects to a running (the first opened) Excel instance,
                     excluding the hidden Excel instance, if it exists,
                     otherwise opens in a new Excel instance.
         :new     -> opens in a new Excel instance
         <excel-instance> -> opens in the given Excel instance
:visible true, false, or nil (default)
alternatives: :default_excel, :force_excel, :visible, :d, :f, :e, :v

:if_unsaved if an unsaved workbook with the same name is open, then

:raise               -> raises an exception
:forget              -> close the unsaved workbook, open the new workbook
:accept              -> lets the unsaved workbook open
:alert or :excel     -> gives control to Excel
:new_excel           -> opens the new workbook in a new Excel instance

:if_obstructed if a workbook with the same name in a different path is open, then or :raise -> raises an exception :if_blocked :forget -> closes the old workbook, open the new workbook

:save                -> saves the old workbook, close it, open the new workbook
:close_if_saved      -> closes the old workbook and open the new workbook, if the old workbook is saved,
                        otherwise raises an exception.
:new_excel           -> opens the new workbook in a new Excel instance

:if_absent :raise -> raises an exception , if the file does not exists

:create              -> creates a new Excel file, if it does not exists

:read_only true -> opens in read-only mode :visible true -> makes the workbook visible :check_compatibility true -> check compatibility when saving :update_links true -> user is being asked how to update links, false -> links are never updated @return [Workbook] a representation of a workbook

Calls superclass method
# File lib/robust_excel_ole/workbook.rb, line 89
def self.new(file_or_workbook, opts = { })
  process_options(opts)
  case file_or_workbook
  when NilClass
    raise FileNameNotGiven, "filename is nil"
  when WIN32OLE
    begin
      file_or_workbook.send(:LinkSources)
      file = file_or_workbook.Fullname.tr('\\','/')
    rescue
      raise TypeREOError, "given win32ol object is not a workbook"
    end
  when Workbook
    file = file_or_workbook.Fullname.tr('\\','/')
  when String
    file = file_or_workbook
    raise FileNotFound, "file #{General.absolute_path(file).inspect} is a directory" if File.directory?(file)
  when ->(n){ n.respond_to? :to_path }
    file = file_or_workbook.to_path
    raise FileNotFound, "file #{General.absolute_path(file).inspect} is a directory" if File.directory?(file)
  else
    raise TypeREOError, "given object is neither a filename, a Win32ole, nor a Workbook object"
  end
  # try to fetch the workbook from the bookstore
  set_was_open opts, file_or_workbook.is_a?(WIN32OLE)
  book = nil
  if opts[:force][:excel] != :new
    # if readonly is true, then prefer a book that is given in force_excel if this option is set
    forced_excel = begin
      (opts[:force][:excel].nil? || opts[:force][:excel] == :current) ? 
        (excel_class.new(reuse: true) if !::CONNECT_JRUBY_BUG) : opts[:force][:excel].to_reo.excel
    rescue NoMethodError
      raise TypeREOError, "provided Excel option value is neither an Excel object nor a valid option"
    end
    begin
      book = if File.exists?(file)
        bookstore.fetch(file, prefer_writable: !(opts[:read_only]),
                              prefer_excel: (opts[:read_only] ? forced_excel : nil))
      end
    rescue
      raise
      #trace "#{$!.message}"
    end
    if book 
      set_was_open opts, book.alive?
      # drop the fetched workbook if it shall be opened in another Excel instance
      # or the workbook is an unsaved workbook that should not be accepted
      if (opts[:force][:excel].nil? || opts[:force][:excel] == :current || forced_excel == book.excel) &&
        !(book.alive? && !book.saved && (opts[:if_unsaved] != :accept))
        opts[:force][:excel] = book.excel if book.excel && book.excel.alive?
        book.ensure_workbook(file,opts)
        book.send :apply_options, file, opts
        return book
      end
    end
  end        
  super(file_or_workbook, opts)
end
new(file_or_workbook, opts) { |self| ... } click to toggle source

creates a new Workbook object, if a file name is given Promotes the win32ole workbook to a Workbook object, if a win32ole-workbook is given @param [Variant] file_or_workbook file name or workbook @param [Hash] opts

@option opts [Symbol] see above @return [Workbook] a workbook

# File lib/robust_excel_ole/workbook.rb, line 156
def initialize(file_or_workbook, opts)
  if file_or_workbook.is_a? WIN32OLE
    @ole_workbook = file_or_workbook
    ole_excel = begin 
      @ole_workbook.Application
    rescue WIN32OLERuntimeError
      raise ExcelREOError, "could not determine the Excel instance\n#{$!.message}"
    end
    @excel = excel_class.new(ole_excel)
    filename = @ole_workbook.Fullname.tr('\\','/') 
  else
    filename = file_or_workbook
    ensure_workbook(filename, opts)        
  end      
  apply_options(filename, opts)
  store_myself
  if block_given?
    begin
      yield self
    ensure
      close
    end
  end
end
save(file) click to toggle source

saves a given file if it is open

# File lib/robust_excel_ole/workbook.rb, line 800
def self.save(file)
  book = bookstore.fetch(file) rescue nil
  book.save if book && book.alive?
end
save_as(file, new_file, opts = { }) click to toggle source

saves a given file under a new name if it is open

# File lib/robust_excel_ole/workbook.rb, line 806
def self.save_as(file, new_file, opts = { })
  book = begin
    bookstore.fetch(file)
  rescue 
    nil
  end
  book.save_as(new_file, opts) if book && book.alive?
end
unobtrusively(file_or_workbook, opts = { }, &block) click to toggle source

allows to read or modify a workbook such that its state remains unchanged state comprises: open, saved, writable, visible, calculation mode, check compatibility @param [String] file_or_workbook a file name or WIN32OLE workbook @param [Hash] opts the options

@option opts [Boolean] :read_only true/false (default), force to open the workbook in read-only/read-write mode @option opts [Boolean] :writable true (default)/false changes of the workbook shall be saved/not saved,

and the workbook is being opened in read-only/read-write mode by default 
(when the workbook was not open before)

@option opts [Boolean] :keep_open whether the workbook shall be kept open after unobtrusively opening (default: false) @option opts [Variant] :if_closed :current (default), :new or an Excel instance @return [Workbook] a workbook

# File lib/robust_excel_ole/workbook.rb, line 589
def self.unobtrusively(file_or_workbook, opts = { }, &block)
  file = (file_or_workbook.is_a? WIN32OLE) ? file_or_workbook.Fullname.tr('\\','/') : file_or_workbook
  unobtrusively_opening(file, opts, nil, &block)
end
worksheet_class() click to toggle source

@private

# File lib/robust_excel_ole/workbook.rb, line 1079
def self.worksheet_class    
  @worksheet_class ||= begin
    module_name = self.parent_name
    "#{module_name}::Worksheet".constantize        
  rescue NameError => e
    Worksheet
  end
end

Private Class Methods

process_options(opts, proc_opts = {use_defaults: true}) click to toggle source
# File lib/robust_excel_ole/workbook.rb, line 191
def self.process_options(opts, proc_opts = {use_defaults: true})
  translate(opts)
  default_opts = (proc_opts[:use_defaults] ? DEFAULT_OPEN_OPTS : CORE_DEFAULT_OPEN_OPTS).dup
  translate(default_opts)
  opts.merge!(default_opts) { |key, v1, v2| !v2.is_a?(Hash) ? v1 : v2.merge(v1 || {}) }
end
set_was_open(hash, value) click to toggle source
# File lib/robust_excel_ole/workbook.rb, line 183
def self.set_was_open(hash, value)
  hash[:was_open] = value if hash.has_key?(:was_open)
end
translate(opts) click to toggle source
# File lib/robust_excel_ole/workbook.rb, line 198
def self.translate(opts)
  erg = {}
  opts.each do |key,value|
    new_key = key
    ABBREVIATIONS.each { |long,short| new_key = long if key == short }
    if value.is_a?(Hash)
      erg[new_key] = {}
      value.each do |k,v|
        new_k = k
        ABBREVIATIONS.each { |l,s| new_k = l if k == s }
        erg[new_key][new_k] = v
      end
    else
      erg[new_key] = value
    end
  end
  opts.merge!(erg)
  opts[:default] ||= {}
  opts[:force] ||= {}
  force_list = [:visible, :excel]
  opts.each { |key,value| opts[:force][key] = value if force_list.include?(key) }
  opts[:default][:excel] = opts[:default_excel] unless opts[:default_excel].nil?
  opts[:force][:excel] = opts[:force_excel] unless opts[:force_excel].nil?
  opts[:default][:excel] = :current if opts[:default][:excel] == :reuse || opts[:default][:excel] == :active
  opts[:force][:excel] = :current if opts[:force][:excel] == :reuse || opts[:force][:excel] == :active
end
unobtrusively_opening(file, opts, book_is_alive) { |book| ... } click to toggle source
# File lib/robust_excel_ole/workbook.rb, line 601
def self.unobtrusively_opening(file, opts, book_is_alive, &block)
  process_options(opts)
  opts = {if_closed: :current, keep_open: false}.merge(opts)    
  raise OptionInvalid, "contradicting options" if opts[:writable] && opts[:read_only] 
  if book_is_alive.nil?
    prefer_writable = ((!(opts[:read_only]) || opts[:writable] == true) &&
                       !(opts[:read_only].nil? && opts[:writable] == false))
    known_book = bookstore.fetch(file, prefer_writable: prefer_writable) 
  end
  excel_opts = if (book_is_alive==false || (book_is_alive.nil? && (known_book.nil? || !known_book.alive?)))
    {force: {excel: opts[:if_closed]}}
  else
    {force: {excel: opts[:force][:excel]}, default: {excel: opts[:default][:excel]}}
  end
  open_opts = excel_opts.merge({if_unsaved: :accept})
  begin
    open_opts[:was_open] = nil  
    book = open(file, open_opts)
    was_visible = book.visible
    was_writable = book.writable
    was_saved = book.saved
    was_check_compatibility = book.check_compatibility
    was_calculation = book.excel.properties[:calculation]
    opts[:read_only] = !opts[:writable] unless (!opts[:read_only].nil? || opts[:writable].nil? || open_opts[:was_open])
    book.send :apply_options, file, opts
    yield book
  ensure
    if book && book.alive?
      do_not_write = opts[:read_only] || opts[:writable]==false
      book.save unless book.saved || do_not_write || !book.writable
      if  ((opts[:read_only] && was_writable) || (!opts[:read_only] && !was_writable))
        book.send :apply_options, file, opts.merge({read_only: !was_writable, 
                                        if_unsaved: (opts[:writable]==false ? :forget : :save)})
      end
      was_open = open_opts[:was_open]
      if was_open
        book.visible = was_visible    
        book.CheckCompatibility = was_check_compatibility
        book.excel.calculation = was_calculation
      end
      book.Saved = (was_saved || !was_open)
      book.close unless was_open || opts[:keep_open]
    end
  end
end

Public Instance Methods

==(other_book) click to toggle source

@return [Boolean] true, if the full workbook names and excel Instances are identical, false otherwise

# File lib/robust_excel_ole/workbook.rb, line 1027
def == other_book
  other_book.is_a?(Workbook) &&
    @excel == other_book.excel &&
    self.filename == other_book.filename
end
[](name) click to toggle source

returns the value of a range @param [String] name the name of a range @returns [Variant] the value of the range

# File lib/robust_excel_ole/workbook.rb, line 940
def [] name
  namevalue_global(name)
end
[]=(name, value) click to toggle source

sets the value of a range @param [String] name the name of the range @param [Variant] value the contents of the range

# File lib/robust_excel_ole/workbook.rb, line 947
def []= (name, value)
  set_namevalue_global(name, value)   
end
add_or_copy_sheet(sheet = nil, opts = { }) click to toggle source

copies a sheet to another position if a sheet is given, or adds an empty sheet default: copied or empty sheet is appended, i.e. added behind the last sheet @param [Worksheet] sheet a sheet that shall be copied (optional) @param [Hash] opts the options @option opts [Symbol] :as new name of the copied or added sheet @option opts [Symbol] :before a sheet before which the sheet shall be inserted @option opts [Symbol] :after a sheet after which the sheet shall be inserted @return [Worksheet] the copied or added sheet

# File lib/robust_excel_ole/workbook.rb, line 855
def add_or_copy_sheet(sheet = nil, opts = { })
  if sheet.is_a? Hash
    opts = sheet
    sheet = nil
  end
  begin
    sheet = sheet.to_reo unless sheet.nil?
    new_sheet_name = opts.delete(:as)
    last_sheet_local = last_sheet
    after_or_before, base_sheet = opts.to_a.first || [:after, last_sheet_local]
    base_sheet_ole = base_sheet.to_reo.ole_worksheet
    if !::COPYSHEETS_JRUBY_BUG          
      add_or_copy_sheet_simple(sheet, { after_or_before.to_s => base_sheet_ole })
    else
      if after_or_before == :before 
        add_or_copy_sheet_simple(sheet, base_sheet_ole)
      else
        if base_sheet.name != last_sheet_local.name
          add_or_copy_sheet_simple(sheet, base_sheet.Next)
        else
          add_or_copy_sheet_simple(sheet, base_sheet_ole)
          base_sheet.Move(ole_workbook.Worksheets.Item(ole_workbook.Worksheets.Count-1))
          ole_workbook.Worksheets.Item(ole_workbook.Worksheets.Count).Activate
        end
      end
    end
  rescue # WIN32OLERuntimeError, NameNotFound, Java::OrgRacobCom::ComFailException
    raise WorksheetREOError, "could not add given worksheet #{sheet.inspect}\n#{$!.message}"
  end
  new_sheet = worksheet_class.new(ole_workbook.Activesheet)
  new_sheet.name = new_sheet_name if new_sheet_name
  new_sheet
end
add_sheet(sheet = nil, opts = { }) click to toggle source

for compatibility to older versions

# File lib/robust_excel_ole/workbook.rb, line 902
def add_sheet(sheet = nil, opts = { })
  add_or_copy_sheet(sheet, opts)
end
alive?() click to toggle source

returns true, if the workbook reacts to methods, false otherwise

# File lib/robust_excel_ole/workbook.rb, line 967
def alive?
  @ole_workbook.Name
  true
rescue
  @ole_workbook = nil  # dead object won't be alive again
  false
end
bookstore() click to toggle source

@private

# File lib/robust_excel_ole/workbook.rb, line 1044
def bookstore    
  self.class.bookstore
end
calculation() click to toggle source
# File lib/robust_excel_ole/workbook.rb, line 990
def calculation
  @excel.properties[:calculation] if @ole_workbook
end
check_compatibility() click to toggle source

@private

# File lib/robust_excel_ole/workbook.rb, line 995
def check_compatibility
  @ole_workbook.CheckCompatibility if @ole_workbook
end
close(opts = {if_unsaved: :raise}) click to toggle source

closes the workbook, if it is alive @param [Hash] opts the options @option opts [Symbol] :if_unsaved :raise (default), :save, :forget, :keep_open, or :alert options:

:if_unsaved    if the workbook is unsaved
                    :raise           -> raises an exception
                    :save            -> saves the workbook before it is closed
                    :forget          -> closes the workbook
                    :keep_open       -> keep the workbook open
                    :alert or :excel -> gives control to excel

@raise WorkbookNotSaved if the option :if_unsaved is :raise and the workbook is unsaved @raise OptionInvalid if the options is invalid

# File lib/robust_excel_ole/workbook.rb, line 516
def close(opts = {if_unsaved: :raise})
  return close_workbook unless (alive? && !@ole_workbook.Saved && writable)
  case opts[:if_unsaved]
  when :raise
    raise WorkbookNotSaved, "workbook is unsaved: #{File.basename(self.stored_filename).inspect}" +
    "\nHint: Use option :save or :forget to close the workbook with or without saving"
  when :save
    save
    close_workbook
  when :forget
    @excel.with_displayalerts(false) { close_workbook }
  when :keep_open
    # nothing
  when :alert, :excel
    @excel.with_displayalerts(true) { close_workbook }
  else
    raise OptionInvalid, ":if_unsaved: invalid option: #{opts[:if_unsaved].inspect}" +
    "\nHint: Valid values are :raise, :save, :keep_open, :alert, :excel"
  end
end
copy_sheet(sheet, opts = { }) click to toggle source

for compatibility to older versions

# File lib/robust_excel_ole/workbook.rb, line 907
def copy_sheet(sheet, opts = { })
  add_or_copy_sheet(sheet, opts)
end
each() { |worksheet_class| ... } click to toggle source

@return [Enumerator] traversing all worksheet objects

# File lib/robust_excel_ole/workbook.rb, line 829
def each
  if block_given?
    @ole_workbook.Worksheets.lazy.each do |ole_worksheet|
      yield worksheet_class.new(ole_worksheet)
    end
  else
    to_enum(:each).lazy
  end
end
each_with_index(offset = 0) { |worksheet_class, i| ... } click to toggle source
# File lib/robust_excel_ole/workbook.rb, line 839
def each_with_index(offset = 0)
  i = offset
  @ole_workbook.Worksheets.lazy.each do |sheet|
    yield worksheet_class.new(sheet), i
    i += 1
  end
end
ensure_excel(options) click to toggle source

@private ensures an excel but not for jruby if current Excel shall be used

# File lib/robust_excel_ole/workbook.rb, line 229
def ensure_excel(options)
  return if @excel && @excel.alive?
  excel_option = options[:force][:excel] || options[:default][:excel] || :current
  @excel = if excel_option == :new
    excel_class.new(reuse: false) 
  elsif excel_option == :current
    excel_class.new(reuse: true)
  elsif excel_option.respond_to?(:to_reo)
    excel_option.to_reo.excel
  else
    raise TypeREOError, "provided Excel option value is neither an Excel object nor a valid option"
  end
  raise ExcelREOError, "Excel is not alive" unless @excel && @excel.alive?
end
ensure_workbook(filename, options) click to toggle source

@private

# File lib/robust_excel_ole/workbook.rb, line 245
def ensure_workbook(filename, options)
  set_was_open options, true
  return if (@ole_workbook && alive? && (options[:read_only].nil? || @ole_workbook.ReadOnly == options[:read_only]))
  set_was_open options, false
  if options[:if_unsaved]==:accept && alive? && 
    ((options[:read_only]==true && self.ReadOnly==false) || (options[:read_only]==false && self.ReadOnly==true))
    raise OptionInvalid, ":if_unsaved:accept and change of read-only mode is not possible"
  end
  filename = @stored_filename ? @stored_filename : filename 
  manage_nonexisting_file(filename,options)
  excel_option = options[:force][:excel].nil? ? options[:default][:excel] : options[:force][:excel]        
  ensure_excel(options)
  workbooks = @excel.Workbooks
  @ole_workbook = workbooks.Item(File.basename(filename)) rescue nil if @ole_workbook.nil?
  if @ole_workbook && alive?
    set_was_open options, true
    manage_blocking_or_unsaved_workbook(filename,options)
    open_or_create_workbook(filename,options) if (!options[:read_only].nil?) && options[:read_only] != @ole_workbook.ReadOnly
  else
    if (excel_option.nil? || excel_option == :current) &&  
      !(::CONNECT_JRUBY_BUG && filename[0] == '/')
      connect(filename,options)
    else 
      open_or_create_workbook(filename,options)
    end
  end       
end
excel_class() click to toggle source

@private

# File lib/robust_excel_ole/workbook.rb, line 1089
def excel_class        
  self.class.excel_class
end
filename() click to toggle source

returns the full file name of the workbook

# File lib/robust_excel_ole/workbook.rb, line 976
def filename
  General.canonize(@ole_workbook.Fullname.tr('\\','/')) rescue nil
end
first_sheet() click to toggle source
# File lib/robust_excel_ole/workbook.rb, line 915
def first_sheet
  worksheet_class.new(@ole_workbook.Worksheets.Item(1))
end
focus() click to toggle source

brings workbook to foreground, makes it available for heyboard inputs, makes the Excel instance visible

# File lib/robust_excel_ole/workbook.rb, line 960
def focus
  self.visible = true
  @excel.focus
  @ole_workbook.Activate
end
for_modifying(opts = { }, &block) click to toggle source
# File lib/robust_excel_ole/workbook.rb, line 566
def for_modifying(opts = { }, &block)
  unobtrusively({writable: true}.merge(opts), &block)
end
for_reading(opts = { }, &block) click to toggle source
# File lib/robust_excel_ole/workbook.rb, line 562
def for_reading(opts = { }, &block)
  unobtrusively({writable: false}.merge(opts), &block)
end
for_this_workbook(opts) click to toggle source

sets options @param [Hash] opts

# File lib/robust_excel_ole/workbook.rb, line 953
def for_this_workbook(opts)
  return unless alive?
  self.class.process_options(opts, use_defaults: false)
  self.send :apply_options, @stored_filename, opts
end
inspect() click to toggle source

@private

# File lib/robust_excel_ole/workbook.rb, line 1059
def inspect    
  #{}"#<Workbook: #{("not alive " unless alive?)} #{(File.basename(self.filename) if alive?)} #{@excel}>"
  "#<Workbook: #{(alive? ? File.basename(self.filename) : "not alive")} #{@excel} >"
end
last_sheet() click to toggle source
# File lib/robust_excel_ole/workbook.rb, line 911
def last_sheet
  worksheet_class.new(@ole_workbook.Worksheets.Item(@ole_workbook.Worksheets.Count))
end
range(name_or_worksheet, name_or_address = :__not_provided, address2 = :__not_provided) click to toggle source

creates a range from a given defined name or from a given worksheet and address @params [Variant] defined name or a worksheet @params [Address] address @return [Range] a range

# File lib/robust_excel_ole/workbook.rb, line 923
def range(name_or_worksheet, name_or_address = :__not_provided, address2 = :__not_provided)
  if name_or_worksheet.respond_to?(:gsub)
    name = name_or_worksheet
    RobustExcelOle::Range.new(get_name_object(name).RefersToRange)
  else 
    begin 
      worksheet = name_or_worksheet.to_reo
      worksheet.range(name_or_address, address2)
    rescue
      raise RangeNotCreated, "argument error: a defined name or a worksheet and an address must be provided"
    end          
  end
end
reopen(options = { }) click to toggle source

reopens a closed workbook @options options

# File lib/robust_excel_ole/workbook.rb, line 651
def reopen(options = { })
  book = self.class.open(@stored_filename, options)
  raise WorkbookREOError("cannot reopen workbook\n#{$!.message}") unless book && book.alive?
  book
end
retain_saved() { |self| ... } click to toggle source

keeps the saved-status unchanged

# File lib/robust_excel_ole/workbook.rb, line 553
def retain_saved
  saved = self.Saved
  begin
    yield self
  ensure
    self.Saved = saved
  end
end
save(opts = { }) click to toggle source

simple save of a workbook. @return [Boolean] true, if successfully saved, nil otherwise

# File lib/robust_excel_ole/workbook.rb, line 659
def save(opts = { })  # option opts is deprecated #
  raise ObjectNotAlive, "workbook is not alive" unless alive?
  raise WorkbookReadOnly, "Not opened for writing (opened with :read_only option)" if @ole_workbook.ReadOnly   
  begin
    @ole_workbook.Save
  rescue WIN32OLERuntimeError, Java::OrgRacobCom::ComFailException => msg
    if msg.message =~ /SaveAs/ && msg.message =~ /Workbook/
      raise WorkbookNotSaved, "workbook not saved"
    else
      raise UnexpectedREOError, "unknown WIN32OLERuntimeError:\n#{msg.message}"
    end
  end
  true
end
save_as(file, options = { }) click to toggle source

saves a workbook with a given file name. @param [String] file file name @param [Hash] opts the options @option opts [Symbol] :if_exists :raise (default), :overwrite, or :alert, :excel @option opts [Symbol] :if_obstructed :raise (default), :forget, :save, or :close_if_saved options: :if_exists if a file with the same name exists, then

             :raise     -> raises an exception, dont't write the file  (default)
             :overwrite -> writes the file, delete the old file
             :alert or :excel -> gives control to Excel
:if_obstructed   if a workbook with the same name and different path is already open and blocks the saving, then
or              :raise               -> raises an exception
:if_blocked     :forget              -> closes the blocking workbook
                :save                -> saves the blocking workbook and closes it
                :close_if_saved      -> closes the blocking workbook, if it is saved,
                                        otherwise raises an exception

@return [Workbook], the book itself, if successfully saved, raises an exception otherwise

# File lib/robust_excel_ole/workbook.rb, line 691
def save_as(file, options = { })
  raise FileNameNotGiven, "filename is nil" if file.nil?
  raise ObjectNotAlive, "workbook is not alive" unless alive?
  raise WorkbookReadOnly, "Not opened for writing (opened with :read_only option)" if @ole_workbook.ReadOnly
  raise(FileNotFound, "file #{General.absolute_path(file).inspect} is a directory") if File.directory?(file)
  self.class.process_options(options)
  begin  
    saveas_manage_if_exists(file, options)
    saveas_manage_if_blocked(file, options)
    save_as_workbook(file, options)
  rescue AlreadyManaged
    nil
  end
  self
end
saved() click to toggle source

@private

# File lib/robust_excel_ole/workbook.rb, line 986
def saved  
  @ole_workbook.Saved if @ole_workbook
end
sheet(name) click to toggle source

returns a sheet, if a sheet name or a number is given @param [String] or [Number] @returns [Worksheet]

# File lib/robust_excel_ole/workbook.rb, line 818
def sheet(name)
  worksheet_class.new(@ole_workbook.Worksheets.Item(name))
rescue WIN32OLERuntimeError, Java::OrgRacobCom::ComFailException => msg
  raise NameNotFound, "could not return a sheet with name #{name.inspect}"
end
to_s() click to toggle source

@private

# File lib/robust_excel_ole/workbook.rb, line 1054
def to_s    
  self.filename.to_s
end
unobtrusively(opts = { }, &block) click to toggle source
# File lib/robust_excel_ole/workbook.rb, line 594
def unobtrusively(opts = { }, &block)
  file = @stored_filename
  self.class.unobtrusively_opening(file, opts, alive?, &block)
end
visible() click to toggle source

returns true, if the workbook is visible, false otherwise

# File lib/robust_excel_ole/workbook.rb, line 1000
def visible
  @excel.Visible && @ole_workbook.Windows(@ole_workbook.Name).Visible
end
visible=(visible_value) click to toggle source

makes both the Excel instance and the window of the workbook visible, or the window invisible does not do anything if geben visible_value is nil @param [Boolean] visible_value determines whether the workbook shall be visible

# File lib/robust_excel_ole/workbook.rb, line 1007
def visible= visible_value
  return if visible_value.nil?
  @excel.visible = true if visible_value
  self.window_visible = @excel.Visible ? visible_value : true
end
window_visible() click to toggle source

returns true, if the window of the workbook is set to visible, false otherwise

# File lib/robust_excel_ole/workbook.rb, line 1014
def window_visible
  @ole_workbook.Windows(@ole_workbook.Name).Visible
end
window_visible=(visible_value) click to toggle source

makes the window of the workbook visible or invisible @param [Boolean] visible_value determines whether the window of the workbook shall be visible

# File lib/robust_excel_ole/workbook.rb, line 1020
def window_visible= visible_value
  retain_saved do
    @ole_workbook.Windows(@ole_workbook.Name).Visible = visible_value if @ole_workbook.Windows.Count > 0
  end
end
workbook() click to toggle source

@private

# File lib/robust_excel_ole/workbook.rb, line 1049
def workbook
  self
end
worksheet_class() click to toggle source

@private

# File lib/robust_excel_ole/workbook.rb, line 1094
def worksheet_class        
  self.class.worksheet_class
end
worksheets_count() click to toggle source
# File lib/robust_excel_ole/workbook.rb, line 824
def worksheets_count
  @ole_workbook.Worksheets.Count
end
writable() click to toggle source

@private

# File lib/robust_excel_ole/workbook.rb, line 981
def writable   
  !@ole_workbook.ReadOnly if @ole_workbook
end

Private Instance Methods

add_or_copy_sheet_simple(sheet, base_sheet_ole_or_hash) click to toggle source
# File lib/robust_excel_ole/workbook.rb, line 891
def add_or_copy_sheet_simple(sheet, base_sheet_ole_or_hash)
  if sheet
    sheet.Copy(base_sheet_ole_or_hash)  
  else
    ole_workbook.Worksheets.Add(base_sheet_ole_or_hash) 
  end
end
apply_options(filename, options) click to toggle source

applies options to workbook named with filename

# File lib/robust_excel_ole/workbook.rb, line 276
def apply_options(filename, options)
  # changing read-only mode
  if (!options[:read_only].nil?) && options[:read_only] != @ole_workbook.ReadOnly
    ensure_workbook(filename, options) 
    raise WorkbookReadOnly, "could not change read-only mode" if options[:read_only] != @ole_workbook.ReadOnly
  end
  retain_saved do
    self.visible = options[:force][:visible].nil? ? @excel.Visible : options[:force][:visible]
    @excel.calculation = options[:calculation] unless options[:calculation].nil?
    @ole_workbook.CheckCompatibility = options[:check_compatibility] unless options[:check_compatibility].nil?
  end      
end
close_workbook() click to toggle source
# File lib/robust_excel_ole/workbook.rb, line 539
def close_workbook
  if alive?
    begin
      @ole_workbook.Close 
    rescue WIN32OLERuntimeError, Java::OrgRacobCom::ComFailException => msg
      explore_workbook_error(msg)          
    end
  end      
  @ole_workbook = nil unless alive?
end
connect(filename, options) click to toggle source

connects to an unknown workbook

# File lib/robust_excel_ole/workbook.rb, line 290
def connect(filename, options)   
  workbooks_number = excel_class.instance_count==0 ? 0 : excel_class.current.Workbooks.Count
  @ole_workbook = begin
    WIN32OLE.connect(General.absolute_path(filename))
  rescue
    if $!.message =~ /moniker/
      raise WorkbookConnectingBlockingError, "some workbook is blocking when connecting"
    else 
      raise WorkbookConnectingUnknownError, "unknown error when connecting to a workbook\n#{$!.message}"
    end
  end
  ole_excel = begin
    @ole_workbook.Application     
  rescue 
    if $!.message =~ /dispid/
      raise WorkbookConnectingUnsavedError, "workbook is unsaved when connecting"
    else 
      raise WorkbookConnectingUnknownError, "unknown error when connecting to a workbook\n#{$!.message}"
    end
  end
  set_was_open options, (ole_excel.Workbooks.Count == workbooks_number)
  @excel = excel_class.new(ole_excel)
end
explore_workbook_error(msg, want_change_readonly = nil) click to toggle source
# File lib/robust_excel_ole/workbook.rb, line 421
def explore_workbook_error(msg, want_change_readonly = nil)
  if msg.message =~ /800A03EC/ && msg.message =~ /0x80020009/
    # error message:
    # 'This workbook is currently referenced by another workbook and cannot be closed'
    # 'Diese Arbeitsmappe wird momentan von einer anderen Arbeitsmappe verwendet und kann nicht geschlossen werden.'
    if want_change_readonly==true
      raise WorkbookLinked, "read-only mode of this workbook cannot be changed, because it is being used by another workbook"
    elsif want_change_readonly.nil?
      raise WorkbookLinked, "workbook is being used by another workbook"
    end
  end
  if msg.message !~ /800A03EC/ || msg.message !~ /0x80020009/ || want_change_readonly==false
    raise UnexpectedREOError, "unknown WIN32OLERuntimeError:\n#{msg.message}"
  end
end
manage_blocking_or_unsaved_workbook(filename, options) click to toggle source
# File lib/robust_excel_ole/workbook.rb, line 332
def manage_blocking_or_unsaved_workbook(filename, options)
  filename = General.absolute_path(filename)
  filename = General.canonize(filename)
  previous_file = General.canonize(@ole_workbook.Fullname.gsub('\\','/'))
  obstructed_by_other_book = (File.basename(filename) == File.basename(previous_file)) &&
                             (File.dirname(filename) != File.dirname(previous_file)) 
  if obstructed_by_other_book
    # workbook is being obstructed by a workbook with same name and different path
    manage_blocking_workbook(filename,options)        
  else
    unless @ole_workbook.Saved
      # workbook open and writable, not obstructed by another workbook, but not saved
      manage_unsaved_workbook(filename,options)
    end
  end        
end
manage_blocking_workbook(filename, options) click to toggle source
# File lib/robust_excel_ole/workbook.rb, line 349
def manage_blocking_workbook(filename, options)     
  blocked_filename = -> { General.canonize(@ole_workbook.Fullname.tr('\\','/')) }
  case options[:if_obstructed]
  when :raise
    raise WorkbookBlocked, "can't open workbook #{filename},
    because it is being blocked by #{blocked_filename.call} with the same name in a different path." +
    "\nHint: Use the option :if_blocked with values :forget or :save,
     to allow automatic closing of the old workbook (without or with saving before, respectively),
     before the new workbook is being opened."
  when :forget
    manage_forgetting_workbook(filename, options)       
  when :save
    manage_saving_workbook(filename, options)        
  when :close_if_saved
    if !@ole_workbook.Saved
      raise WorkbookBlocked, "workbook with the same name in a different path is unsaved: #{blocked_filename.call}" +
      "\nHint: Use the option if_blocked: :save to save the workbook"
    else
      manage_forgetting_workbook(filename, options)
    end
  when :new_excel
    manage_new_excel(filename, options)        
  else
    raise OptionInvalid, ":if_blocked: invalid option: #{options[:if_obstructed].inspect}" +
    "\nHint: Valid values are :raise, :forget, :save, :close_if_saved, :new_excel"
  end
end
manage_forgetting_workbook(filename, options) click to toggle source
# File lib/robust_excel_ole/workbook.rb, line 404
def manage_forgetting_workbook(filename, options)
  @excel.with_displayalerts(false) { @ole_workbook.Close }
  @ole_workbook = nil
  open_or_create_workbook(filename, options)
end
manage_new_excel(filename, options) click to toggle source
# File lib/robust_excel_ole/workbook.rb, line 415
def manage_new_excel(filename, options)
  @excel = excel_class.new(reuse: false)
  @ole_workbook = nil
  open_or_create_workbook(filename, options)
end
manage_nonexisting_file(filename, options) click to toggle source
# File lib/robust_excel_ole/workbook.rb, line 314
def manage_nonexisting_file(filename, options)   
  return if File.exist?(filename)
  abs_filename = General.absolute_path(filename)
  if options[:if_absent] == :create
    ensure_excel(options) unless @excel && @excel.alive?
    @excel.Workbooks.Add
    empty_ole_workbook = excel.Workbooks.Item(excel.Workbooks.Count)
    begin
      empty_ole_workbook.SaveAs(abs_filename)
    rescue WIN32OLERuntimeError, Java::OrgRacobCom::ComFailException => msg
      raise FileNotFound, "could not save workbook with filename #{filename.inspect}"
    end
  else
    raise FileNotFound, "file #{abs_filename.inspect} not found" +
      "\nHint: If you want to create a new file, use option :if_absent => :create or Workbook::create"
  end
end
manage_saving_workbook(filename, options) click to toggle source
# File lib/robust_excel_ole/workbook.rb, line 410
def manage_saving_workbook(filename, options)
  save unless @ole_workbook.Saved
  manage_forgetting_workbook(filename, options)
end
manage_unsaved_workbook(filename, options) click to toggle source
# File lib/robust_excel_ole/workbook.rb, line 377
def manage_unsaved_workbook(filename, options)
  case options[:if_unsaved]
  when :raise
    msg = if !options[:read_only].nil? && @ole_workbook.ReadOnly != options[:read_only]
      "cannot change read-only mode of the workbook #{File.basename(filename).inspect}, because it contains unsaved changes"
    else
      "workbook is already open but not saved: #{File.basename(filename).inspect}"
    end
    raise WorkbookNotSaved, msg +
    "\nHint: Use the option :if_unsaved with values :forget to close the unsaved workbook, 
     :accept to let it open, or :save to save it, respectivly"
  when :forget
    manage_forgetting_workbook(filename, options)
  when :accept
    # do nothing
  when :save
    manage_saving_workbook(filename, options)
  when :alert, :excel
    @excel.with_displayalerts(true) { open_or_create_workbook(filename,options) }
  when :new_excel
    manage_new_excel(filename, options)
  else
    raise OptionInvalid, ":if_unsaved: invalid option: #{options[:if_unsaved].inspect}" +
    "\nHint: Valid values are :raise, :forget, :save, :accept, :alert, :excel, :new_excel"
  end
end
method_missing(name, *args) click to toggle source
Calls superclass method
# File lib/robust_excel_ole/workbook.rb, line 1102
def method_missing(name, *args) 
  super unless name.to_s[0,1] =~ /[A-Z]/
  raise ObjectNotAlive, 'method missing: workbook not alive' unless alive?
  if ::ERRORMESSAGE_JRUBY_BUG 
    begin
      @ole_workbook.send(name, *args)
    rescue Java::OrgRacobCom::ComFailException 
      raise VBAMethodMissingError, "unknown VBA property or method #{name.inspect}"
    end
  else
    begin
      @ole_workbook.send(name, *args)
    rescue NoMethodError 
      raise VBAMethodMissingError, "unknown VBA property or method #{name.inspect}"
    end
  end
end
open_or_create_workbook(filename, options) click to toggle source
# File lib/robust_excel_ole/workbook.rb, line 437
def open_or_create_workbook(filename, options)
  return if @ole_workbook && options[:if_unsaved] != :alert && options[:if_unsaved] != :excel &&
            (options[:read_only].nil? || options[:read_only]==@ole_workbook.ReadOnly )
  abs_filename = General.absolute_path(filename)
  workbooks = begin 
    @excel.Workbooks
  rescue WIN32OLERuntimeError, Java::OrgRacobCom::ComFailException => msg
    raise UnexpectedREOError, "cannot access workbooks: #{msg.message} #{msg.backtrace}"
  end
  begin
    with_workaround_linked_workbooks_excel2007(options) do
      # temporary workaround until jruby-win32ole implements named parameters (Java::JavaLang::RuntimeException (createVariant() not implemented for class org.jruby.RubyHash)
      workbooks.Open(abs_filename, updatelinks_vba(options[:update_links]), options[:read_only] )
    end
  rescue WIN32OLERuntimeError, Java::OrgRacobCom::ComFailException => msg
    # for Excel2007: for option :if_unsaved => :alert and user cancels: this error appears?; distinguish these events
    want_change_readonly = !options[:read_only].nil? && (options[:read_only] != @ole_workbook.ReadOnly)
  end
  # workaround for bug in Excel 2010: workbook.Open does not always return the workbook when given file name
  @ole_workbook = begin
    workbooks.Item(File.basename(filename))
  rescue WIN32OLERuntimeError, Java::OrgRacobCom::ComFailException => msg
    raise UnexpectedREOError, "WIN32OLERuntimeError: #{msg.message}"
  end
end
save_as_workbook(file, options) click to toggle source
# File lib/robust_excel_ole/workbook.rb, line 759
def save_as_workbook(file, options)  
  dirname, basename = File.split(file)
  file_format =
    case File.extname(basename)
    when '.xls' then RobustExcelOle::XlExcel8
    when '.xlsx' then RobustExcelOle::XlOpenXMLWorkbook
    when '.xlsm' then RobustExcelOle::XlOpenXMLWorkbookMacroEnabled
    end
  @ole_workbook.SaveAs(General.absolute_path(file), file_format)
  store_myself
rescue WIN32OLERuntimeError, Java::OrgRacobCom::ComFailException => msg
  if msg.message =~ /SaveAs/ && msg.message =~ /Workbook/
    # trace "save: canceled by user" if options[:if_exists] == :alert || options[:if_exists] == :excel
    # another possible semantics. raise WorkbookREOError, "could not save Workbook"
  else
    raise UnexpectedREOError, "unknown WIN32OELERuntimeError:\n#{msg.message}"
  end
end
saveas_manage_if_blocked(file, options) click to toggle source
# File lib/robust_excel_ole/workbook.rb, line 735
def saveas_manage_if_blocked(file, options)
  other_workbook = @excel.Workbooks.Item(File.basename(file)) rescue nil
  return unless other_workbook && self.filename != other_workbook.Fullname.tr('\\','/')
  case options[:if_obstructed]
  when :raise
    raise WorkbookBlocked, "blocked by another workbook: #{other_workbook.Fullname.tr('\\','/')}" +
    "\nHint: Use the option :if_blocked with values :forget or :save to
     close or save and close the blocking workbook"
  when :forget
    # nothing
  when :save
    other_workbook.Save
  when :close_if_saved
    unless other_workbook.Saved
      raise WorkbookBlocked, "blocking workbook is unsaved: #{File.basename(file).inspect}" +
      "\nHint: Use option if_blocked: :save to save the blocking workbooks"
    end
  else
    raise OptionInvalid, "if_blocked: invalid option: #{options[:if_obstructed].inspect}" +
    "\nHint: Valid values are :raise, :forget, :save, :close_if_saved"
  end
  other_workbook.Close
end
saveas_manage_if_exists(file, options) click to toggle source
# File lib/robust_excel_ole/workbook.rb, line 709
def saveas_manage_if_exists(file, options)
  return unless File.exist?(file)
  case options[:if_exists]
  when :overwrite
    if file == self.filename
      save
      raise AlreadyManaged
    else
      begin
        File.delete(file)
      rescue Errno::EACCES
        raise WorkbookBeingUsed, "workbook is open and being used in an Excel instance"
      end
    end
  when :alert, :excel
    @excel.with_displayalerts(true){ save_as_workbook(file, options) }
    raise AlreadyManaged
  when :raise
    raise FileAlreadyExists, "file already exists: #{File.basename(file).inspect}" +
    "\nHint: Use option if_exists: :overwrite, if you want to overwrite the file" 
  else
    raise OptionInvalid, ":if_exists: invalid option: #{options[:if_exists].inspect}" +
    "\nHint: Valid values are :raise, :overwrite, :alert, :excel"
  end
end
set_was_open(hash, value) click to toggle source
# File lib/robust_excel_ole/workbook.rb, line 187
def set_was_open(hash, value)
  self.class.set_was_open(hash, value)
end
store_myself() click to toggle source
# File lib/robust_excel_ole/workbook.rb, line 781
def store_myself
  bookstore.store(self)
  @stored_filename = filename
end
with_workaround_linked_workbooks_excel2007(options) { |self| ... } click to toggle source

workaround for linked workbooks for Excel 2007: opening and closing a dummy workbook if Excel has no workbooks. delay: with visible: 0.2 sec, without visible almost none

# File lib/robust_excel_ole/workbook.rb, line 478
def with_workaround_linked_workbooks_excel2007(options)
  old_visible_value = @excel.Visible
  workbooks = @excel.Workbooks
  workaround_condition = @excel.Version.split('.').first.to_i == 12 && workbooks.Count == 0
  if workaround_condition
    workbooks.Add
    @excel.calculation = options[:calculation].nil? ? @excel.properties[:calculation] : options[:calculation]
  end
  begin
    # @excel.with_displayalerts(update_links_opt == :alert ? true : @excel.displayalerts) do
    yield self
  ensure
    @excel.with_displayalerts(false) { workbooks.Item(1).Close } if workaround_condition
    @excel.visible = old_visible_value
  end
end