Class: RobustExcelOle::Workbook

Inherits:
RangeOwners show all
Defined in:
lib/robust_excel_ole/workbook.rb

Overview

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

Constant Summary collapse

DEFAULT_OPEN_OPTS = 
{
  :default => {:excel => :current},
  :force => {},
  :if_unsaved    => :raise,
  :if_obstructed => :raise,
  :if_absent     => :raise,
  :read_only => false,
  :check_compatibility => false,
  :update_links => :never
}.freeze
ABBREVIATIONS = 
[[:default,:d], [:force, :f], [:excel, :e], [:visible, :v]].freeze

Instance Attribute Summary collapse

Class Method Summary collapse

Instance Method Summary collapse

Methods inherited from RangeOwners

#add_name, #delete_name, #name2range, #nameval, #namevalue, #namevalue_glob, #range, #rangeval, #rename_range, #set_name, #set_nameval, #set_namevalue, #set_namevalue_glob, #set_rangeval

Methods inherited from REOCommon

#own_methods, puts_hash, tr1, trace

Constructor Details

#initialize(file_or_workbook, options = { }, &block) ⇒ Workbook

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

Parameters:

  • file_or_workbook (Variant) —

    file name or workbook

  • opts (Hash) —

    the options



150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
 
# File 'lib/robust_excel_ole/workbook.rb', line 150

def initialize(file_or_workbook, options = { }, &block)
  # options = @options = self.class.process_options(options) if options.empty?
  if file_or_workbook.is_a? WIN32OLE
    workbook = file_or_workbook
    @ole_workbook = workbook
    # use the Excel instance where the workbook is opened
    win32ole_excel = WIN32OLE.connect(workbook.Fullname).Application rescue nil
    @excel = excel_class.new(win32ole_excel)
    @excel.visible = options[force][:visible] unless options[:force][:visible].nil?
    @excel.calculation = options[:calculation] unless options[:calculation].nil?
    ensure_excel(options)
  else
    file = file_or_workbook
    ensure_excel(options)
    ensure_workbook(file, options)
  end
  bookstore.store(self)
  @modified_cells = []
  @workbook = @excel.workbook = self
  if block
    begin
      yield self
    ensure
      close
    end
  end
end

Dynamic Method Handling

This class handles dynamic methods through the method_missing method

#method_missing(name, *args) ⇒ Object (private)

:nodoc:



928
929
930
931
932
933
934
935
936
937
938
939
940
941
942
943
 
# File 'lib/robust_excel_ole/workbook.rb', line 928

def method_missing(name, *args)   # :nodoc:
  if name.to_s[0,1] =~ /[A-Z]/
    begin
      raise ObjectNotAlive, 'method missing: workbook not alive' unless alive?
      @ole_workbook.send(name, *args)
    rescue WIN32OLERuntimeError => msg
      if msg.message =~ /unknown property or method/
        raise VBAMethodMissingError, "unknown VBA property or method #{name.inspect}"
      else
        raise msg
      end
    end
  else
    super
  end
end

Instance Attribute Details

#excel ⇒ Object

Returns the value of attribute excel



14
15
16
 
# File 'lib/robust_excel_ole/workbook.rb', line 14

def excel
  @excel
end

#modified_cells ⇒ Object

Returns the value of attribute modified_cells



18
19
20
 
# File 'lib/robust_excel_ole/workbook.rb', line 18

def modified_cells
  @modified_cells
end

#ole_workbook ⇒ Object

Returns the value of attribute ole_workbook



15
16
17
 
# File 'lib/robust_excel_ole/workbook.rb', line 15

def ole_workbook
  @ole_workbook
end

#options ⇒ Object

Returns the value of attribute options



17
18
19
 
# File 'lib/robust_excel_ole/workbook.rb', line 17

def options
  @options
end

#stored_filename ⇒ Object

Returns the value of attribute stored_filename



16
17
18
 
# File 'lib/robust_excel_ole/workbook.rb', line 16

def stored_filename
  @stored_filename
end

#workbook ⇒ Object (readonly)

Returns the value of attribute workbook



19
20
21
 
# File 'lib/robust_excel_ole/workbook.rb', line 19

def workbook
  @workbook
end

Class Method Details

.books ⇒ Object 



877
878
879
 
# File 'lib/robust_excel_ole/workbook.rb', line 877

def self.books
  bookstore.books
end

.bookstore ⇒ Object

:nodoc:



881
882
883
 
# File 'lib/robust_excel_ole/workbook.rb', line 881

def self.bookstore   
  @@bookstore ||= Bookstore.new
end

.close(file, opts = {:if_unsaved => :raise}) ⇒ Object

closes a given file if it is open



669
670
671
672
 
# File 'lib/robust_excel_ole/workbook.rb', line 669

def self.close(file, opts = {:if_unsaved => :raise})
  book = bookstore.fetch(file) rescue nil
  book.close(opts) if book && book.alive?
end

.excel_class ⇒ Object

:nodoc:



897
898
899
900
901
902
903
904
905
 
# File 'lib/robust_excel_ole/workbook.rb', line 897

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(*args, &block) ⇒ Object 



467
468
469
470
471
472
473
 
# File 'lib/robust_excel_ole/workbook.rb', line 467

def self.for_modifying(*args, &block)
  args = args.dup
  opts = args.last.is_a?(Hash) ? args.pop : {}
  opts = {:writable => true}.merge(opts)
  args.push opts
  unobtrusively(*args, &block)
end

.for_reading(*args, &block) ⇒ Object 



459
460
461
462
463
464
465
 
# File 'lib/robust_excel_ole/workbook.rb', line 459

def self.for_reading(*args, &block)
  args = args.dup
  opts = args.last.is_a?(Hash) ? args.pop : {}
  opts = {:writable => false}.merge(opts)
  args.push opts
  unobtrusively(*args, &block)
end

.new(workbook, opts = { }, &block) ⇒ Workbook

creates a Workbook object by opening an Excel file given its filename workbook or by promoting a Win32OLE object representing an Excel file

Parameters:

  • workbook (WIN32OLE) —

    a workbook

  • opts (Hash) (defaults to: { }) —

    the options

Options Hash (opts):

  • see (Symbol) —

    above

Returns:



125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
 
# File 'lib/robust_excel_ole/workbook.rb', line 125

def self.new(workbook, opts = { }, &block)
  opts = process_options(opts)
  if workbook && (workbook.is_a? WIN32OLE)
    filename = workbook.Fullname.tr('\\','/') rescue nil
    if filename
      book = bookstore.fetch(filename)
      if book && book.alive?
        book.visible = opts[:force][:visible] unless opts[:force].nil? || opts[:force][:visible].nil?
        book.excel.calculation = opts[:calculation] unless opts[:calculation].nil?
        return book
      else
        super
      end
    end
  else
    super
  end
end

.open(file, opts = { }, &block) ⇒ Workbook

opens a workbook. 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, :visible

: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

:raise               -> raises an exception
: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

Parameters:

  • file (String) —

    the file name

  • opts (Hash) (defaults to: { }) —

    the options

Options Hash (opts):

  • :default (Hash) —

    or :d

  • :force (Hash) —

    or :f

  • :if_unsaved (Symbol) —

    :raise (default), :forget, :accept, :alert, :excel, or :new_excel

  • :if_obstructed (Symbol) —

    :raise (default), :forget, :save, :close_if_saved, or _new_excel

  • :if_absent (Symbol) —

    :raise (default) or :create

  • :read_only (Boolean) —

    true (default) or false

  • :update_links (Boolean) —

    :never (default), :always, :alert

  • :calculation (Boolean) —

    :manual, :automatic, or nil (default)

Returns:

  • (Workbook) —

    a representation of a workbook



82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
 
# File 'lib/robust_excel_ole/workbook.rb', line 82

def open(file, opts = { }, &block)
  options = @options = process_options(opts)
  book = nil
  if (options[:force][:excel] != :new) && (options[:force][:excel] != :reserved_new)
    # if readonly is true, then prefer a book that is given in force_excel if this option is set
    forced_excel = if options[:force][:excel]
                     options[:force][:excel] == :current ? excel_class.new(:reuse => true) : excel_of(options[:force][:excel])
    end
    book = bookstore.fetch(file,
            :prefer_writable => !(options[:read_only]),
            :prefer_excel    => (options[:read_only] ? forced_excel : nil)) rescue nil
    if book
      # if forced_excel != book.excel &&
      #  (not (book.alive? && (not book.saved) && (not options[:if_unsaved] == :accept)))
      if (!(options[:force][:excel]) || (forced_excel == book.excel)) &&
         !(book.alive? && !book.saved && (options[:if_unsaved] != :accept))
        book.options = options
        book.ensure_excel(options) # unless book.excel.alive?
        # if the ReadOnly status shall be changed, save, close and reopen it
        if book.alive? && ((!book.writable && !(options[:read_only])) ||
           (book.writable && options[:read_only]))
          book.save if book.writable && !book.saved
          book.close(:if_unsaved => :forget)
        end
        # reopens the book if it was closed
        book.ensure_workbook(file,options) unless book.alive?
        book.visible = options[:force][:visible] unless options[:force][:visible].nil?
        book.CheckCompatibility = options[:check_compatibility] unless options[:check_compatibility].nil?
        book.excel.calculation = options[:calculation] unless options[:calculation].nil?
        return book
      end
    end
  end
  new(file, options, &block)
end

.save(file) ⇒ Object

saves a given file if it is open



675
676
677
678
 
# File 'lib/robust_excel_ole/workbook.rb', line 675

def self.save(file)
  book = bookstore.fetch(file) rescue nil
  book.save if book && book.alive?
end

.save_as(file, new_file, opts = { }) ⇒ Object

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



681
682
683
684
 
# File 'lib/robust_excel_ole/workbook.rb', line 681

def self.save_as(file, new_file, opts = { })
  book = bookstore.fetch(file) rescue nil
  book.save_as(new_file, opts) if book && book.alive?
end

.unobtrusively(file, opts = { }) ⇒ Workbook

allows to read or modify a workbook such that its state remains unchanged state comprises: open, saved, writable, visible, calculation mode, check compatibility remarks: works only for workbooks opened with RobustExcelOle

Parameters:

  • file (String) —

    the file name

  • opts (Hash) (defaults to: { }) —

    the options

Options Hash (opts):

  • :if_closed (Variant) —

    :current (default), :new or an Excel instance

  • :read_only (Boolean) —

    true/false, open the workbook in read-only/read-write modus (save changes)

  • :writable (Boolean) —

    true/false changes of the workbook shall be saved/not saved

  • :rw_change_excel (Boolean) —

    Excel instance in which the workbook with the new write permissions shall be opened :current (default), :new or an Excel instance

  • :keep_open (Boolean) —

    whether the workbook shall be kept open after unobtrusively opening

Returns:

Raises:



487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
 
# File 'lib/robust_excel_ole/workbook.rb', line 487

def self.unobtrusively(file, opts = { })
  opts = {:if_closed => :current,
          :rw_change_excel => :current,
          :keep_open => false}.merge(opts)
  raise OptionInvalid, 'contradicting options' if opts[:writable] && opts[:read_only]
  prefer_writable = ((!(opts[:read_only]) || opts[:writable] == true) &&
                     !(opts[:read_only].nil? && opts[:writable] == false))
  do_not_write = (opts[:read_only] || (opts[:read_only].nil? && opts[:writable] == false))
  book = bookstore.fetch(file, :prefer_writable => prefer_writable)
  was_open = book && book.alive?
  if was_open
    was_saved = book.saved
    was_writable = book.writable
    was_visible = book.visible
    was_calculation = book.calculation
    was_check_compatibility = book.check_compatibility
    if (opts[:writable] && !was_writable && !was_saved) ||
       (opts[:read_only] && was_writable && !was_saved)
      raise NotImplementedREOError, 'unsaved read-only workbook shall be written'
    end
    opts[:rw_change_excel] = book.excel if opts[:rw_change_excel] == :current
  end
  change_rw_mode = ((opts[:read_only] && was_writable) || (opts[:writable] && !was_writable))
  begin
    book =
      if was_open
        if change_rw_mode
          open(file, :force => {:excel => opts[:rw_change_excel]}, :read_only => do_not_write)
        else
          book
        end
      else
        open(file, :force => {:excel => opts[:if_closed]}, :read_only => do_not_write)
      end
    yield book
  ensure
    if book && book.alive?
      book.save unless book.saved || do_not_write || book.ReadOnly
      if was_open
        if opts[:rw_change_excel] == book.excel && change_rw_mode
          book.close
          book = open(file, :force => {:excel => opts[:rw_change_excel]}, :read_only => !was_writable)
        end
        book.excel.calculation = was_calculation
        book.CheckCompatibility = was_check_compatibility
        # book.visible = was_visible  # not necessary
      end
      book.Saved = (was_saved || !was_open)
      book.close unless was_open || opts[:keep_open]
    end
  end
end

.worksheet_class ⇒ Object

:nodoc:



907
908
909
910
911
912
913
914
 
# File 'lib/robust_excel_ole/workbook.rb', line 907

def self.worksheet_class    
  @worksheet_class ||= begin
    module_name = self.parent_name
    "#{module_name}::Worksheet".constantize
  rescue NameError => e
    Worksheet
  end
end

Instance Method Details

#==(other_book) ⇒ Boolean

Returns true, if the full book names and excel Instances are identical, false otherwise.

Returns:

  • (Boolean) —

    true, if the full book names and excel Instances are identical, false otherwise



871
872
873
874
875
 
# File 'lib/robust_excel_ole/workbook.rb', line 871

def == other_book
  other_book.is_a?(Workbook) &&
    @excel == other_book.excel &&
    self.filename == other_book.filename
end

#[](name) ⇒ Object

returns the value of a range

Parameters:

  • name (String) —

    the name of a range



776
777
778
 
# File 'lib/robust_excel_ole/workbook.rb', line 776

def [] name
  namevalue_glob(name)
end

#[]=(name, value) ⇒ Object

sets the value of a range

Parameters:

  • name (String) —

    the name of the range

  • value (Variant) —

    the contents of the range



783
784
785
 
# File 'lib/robust_excel_ole/workbook.rb', line 783

def []= (name, value)
  set_namevalue_glob(name,value, :color => 42)   # 42 - aqua-marin, 4-green
end

#add_empty_sheet(opts = { }) ⇒ Worksheet

adds an empty sheet default: empty sheet is appended

Parameters:

  • opts (Hash) (defaults to: { }) —

    the options

Options Hash (opts):

  • :as (Symbol) —

    new name of the copied added sheet

  • :before (Symbol) —

    a sheet before which the sheet shall be inserted

  • :after (Symbol) —

    a sheet after which the sheet shall be inserted

Returns:

Raises:

  • NameAlreadyExists if the sheet name already exists



735
736
737
738
739
740
741
742
 
# File 'lib/robust_excel_ole/workbook.rb', line 735

def add_empty_sheet(opts = { })
  new_sheet_name = opts.delete(:as)
  after_or_before, base_sheet = opts.to_a.first || [:after, last_sheet]
  @ole_workbook.Worksheets.Add({ after_or_before.to_s => base_sheet.ole_worksheet })
  new_sheet = worksheet_class.new(@excel.Activesheet)
  new_sheet.name = new_sheet_name if new_sheet_name
  new_sheet
end

#add_or_copy_sheet(sheet = nil, opts = { }) ⇒ Worksheet

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

Parameters:

  • sheet (Worksheet) (defaults to: nil) —

    a sheet that shall be copied (optional)

  • opts (Hash) (defaults to: { }) —

    the options

Options Hash (opts):

  • :as (Symbol) —

    new name of the copied or added sheet

  • :before (Symbol) —

    a sheet before which the sheet shall be inserted

  • :after (Symbol) —

    a sheet after which the sheet shall be inserted

Returns:



752
753
754
755
756
757
758
 
# File 'lib/robust_excel_ole/workbook.rb', line 752

def add_or_copy_sheet(sheet = nil, opts = { })
  if sheet.is_a? Hash
    opts = sheet
    sheet = nil
  end
  sheet ? copy_sheet(sheet, opts) : add_empty_sheet(opts)
end

#add_sheet(sheet = nil, opts = { }) ⇒ Object

for compatibility to older versions



761
762
763
 
# File 'lib/robust_excel_ole/workbook.rb', line 761

def add_sheet(sheet = nil, opts = { })
  add_or_copy_sheet(sheet, opts)
end

#alive? ⇒ Boolean

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

Returns:

  • (Boolean) 


815
816
817
818
819
820
821
822
 
# File 'lib/robust_excel_ole/workbook.rb', line 815

def alive?
  @ole_workbook.Name
  true
rescue
  @ole_workbook = nil  # dead object won't be alive again
  # t $!.message
  false
end

#bookstore ⇒ Object

:nodoc:



885
886
887
 
# File 'lib/robust_excel_ole/workbook.rb', line 885

def bookstore    
  self.class.bookstore
end

#calculation ⇒ Object 



837
838
839
 
# File 'lib/robust_excel_ole/workbook.rb', line 837

def calculation
  @excel.calculation if @ole_workbook
end

#check_compatibility ⇒ Object 



841
842
843
 
# File 'lib/robust_excel_ole/workbook.rb', line 841

def check_compatibility
  @ole_workbook.CheckCompatibility if @ole_workbook
end

#close(opts = {:if_unsaved => :raise}) ⇒ Object

closes the workbook, if it is alive 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

Parameters:

  • opts (Hash) (defaults to: {:if_unsaved => :raise}) —

    the options

Options Hash (opts):

  • :if_unsaved (Symbol) —

    :raise (default), :save, :forget, :keep_open, or :alert

Raises:

  • WorkbookNotSaved if the option :if_unsaved is :raise and the workbook is unsaved

  • OptionInvalid if the options is invalid



416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
 
# File 'lib/robust_excel_ole/workbook.rb', line 416

def close(opts = {:if_unsaved => :raise})
  if alive? && !@ole_workbook.Saved && writable
    case opts[:if_unsaved]
    when :raise
      raise WorkbookNotSaved, "workbook is unsaved: #{File.basename(self.stored_filename).inspect}"
    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}"
    end
  else
    close_workbook
  end
  # trace "close: canceled by user" if alive? &&
  #  (opts[:if_unsaved] == :alert || opts[:if_unsaved] == :excel) && (not @ole_workbook.Saved)
end

#copy_sheet(sheet, opts = { }) ⇒ Worksheet

copies a sheet to another position default: copied sheet is appended

Parameters:

  • sheet (Worksheet) —

    a sheet that shall be copied

  • opts (Hash) (defaults to: { }) —

    the options

Options Hash (opts):

  • :as (Symbol) —

    new name of the copied sheet

  • :before (Symbol) —

    a sheet before which the sheet shall be inserted

  • :after (Symbol) —

    a sheet after which the sheet shall be inserted

Returns:

Raises:

  • NameAlreadyExists if the sheet name already exists



718
719
720
721
722
723
724
725
 
# File 'lib/robust_excel_ole/workbook.rb', line 718

def copy_sheet(sheet, opts = { })
  new_sheet_name = opts.delete(:as)
  after_or_before, base_sheet = opts.to_a.first || [:after, last_sheet]
  sheet.Copy({ after_or_before.to_s => base_sheet.ole_worksheet })
  new_sheet = worksheet_class.new(@excel.Activesheet)
  new_sheet.name = new_sheet_name if new_sheet_name
  new_sheet
end

#each ⇒ Object 



695
696
697
698
699
 
# File 'lib/robust_excel_ole/workbook.rb', line 695

def each
  @ole_workbook.Worksheets.each do |sheet|
    yield worksheet_class.new(sheet)
  end
end

#each_with_index(offset = 0) ⇒ Object 



701
702
703
704
705
706
707
 
# File 'lib/robust_excel_ole/workbook.rb', line 701

def each_with_index(offset = 0)
  i = offset
  @ole_workbook.Worksheets.each do |sheet|
    yield worksheet_class.new(sheet), i
    i += 1
  end
end

#ensure_excel(options) ⇒ Object

:nodoc:



240
241
242
243
244
245
246
247
248
249
250
 
# File 'lib/robust_excel_ole/workbook.rb', line 240

def ensure_excel(options)   # :nodoc:
  if excel && @excel.alive?
    @excel.created = false
    return
  end
  excel_option = options[:force].nil? || options[:force][:excel].nil? ? options[:default][:excel] : options[:force][:excel]
  @excel = self.class.excel_of(excel_option) unless excel_option == :current || excel_option == :new || excel_option == :reserved_new
  excel_class.new(:reuse => false) if (excel_option == :reserved_new) && Excel.known_excel_instances.empty?
  @excel = excel_class.new(:reuse => (excel_option == :current)) unless @excel && @excel.alive?
  @excel
end

#ensure_workbook(file, options) ⇒ Object

:nodoc:

Raises:



252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
 
# File 'lib/robust_excel_ole/workbook.rb', line 252

def ensure_workbook(file, options)     # :nodoc:
  file = @stored_filename ? @stored_filename : file
  raise(FileNameNotGiven, 'filename is nil') if file.nil?
  raise(FileNotFound, "file #{General.absolute_path(file).inspect} is a directory") if File.directory?(file)
  unless File.exist?(file)
    if options[:if_absent] == :create
      @ole_workbook = excel_class.current.generate_workbook(file)
    else
      raise FileNotFound, "file #{General.absolute_path(file).inspect} not found"
    end
  end
  @ole_workbook = @excel.Workbooks.Item(File.basename(file)) rescue nil
  if @ole_workbook
    obstructed_by_other_book = (File.basename(file) == File.basename(@ole_workbook.Fullname)) &&
                               (General.absolute_path(file) != @ole_workbook.Fullname)
    # if workbook is obstructed by a workbook with same name and different path
    if obstructed_by_other_book
      case options[:if_obstructed]
      when :raise
        raise WorkbookBlocked, "blocked by a workbook with the same name in a different path: #{@ole_workbook.Fullname.tr('\\','/')}"
      when :forget
        @ole_workbook.Close
        @ole_workbook = nil
        open_or_create_workbook(file, options)
      when :save
        save unless @ole_workbook.Saved
        @ole_workbook.Close
        @ole_workbook = nil
        open_or_create_workbook(file, options)
      when :close_if_saved
        if !@ole_workbook.Saved
          raise WorkbookBlocked, "workbook with the same name in a different path is unsaved: #{@ole_workbook.Fullname.tr('\\','/')}"
        else
          @ole_workbook.Close
          @ole_workbook = nil
          open_or_create_workbook(file, options)
        end
      when :new_excel
        @excel = excel_class.new(:reuse => false)
        open_or_create_workbook(file, options)
      else
        raise OptionInvalid, ":if_obstructed: invalid option: #{options[:if_obstructed].inspect}"
      end
    else
      # book open, not obstructed by an other book, but not saved and writable
      unless @ole_workbook.Saved
        case options[:if_unsaved]
        when :raise
          raise WorkbookNotSaved, "workbook is already open but not saved: #{File.basename(file).inspect}"
        when :forget
          @ole_workbook.Close
          @ole_workbook = nil
          open_or_create_workbook(file, options)
        when :accept
          # do nothing
        when :alert, :excel
          @excel.with_displayalerts(true) { open_or_create_workbook(file,options) }
        when :new_excel
          @excel = excel_class.new(:reuse => false)
          open_or_create_workbook(file, options)
        else
          raise OptionInvalid, ":if_unsaved: invalid option: #{options[:if_unsaved].inspect}"
        end
      end
    end
  else
    # open a new workbook
    open_or_create_workbook(file, options)
  end
end

#excel_class ⇒ Object

:nodoc:



916
917
918
 
# File 'lib/robust_excel_ole/workbook.rb', line 916

def excel_class        
  self.class.excel_class
end

#filename ⇒ Object

returns the full file name of the workbook



825
826
827
 
# File 'lib/robust_excel_ole/workbook.rb', line 825

def filename
  @ole_workbook.Fullname.tr('\\','/') rescue nil
end

#first_sheet ⇒ Object 



769
770
771
 
# File 'lib/robust_excel_ole/workbook.rb', line 769

def first_sheet
  worksheet_class.new(@ole_workbook.Worksheets.Item(1))
end

#focus ⇒ Object

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



808
809
810
811
812
 
# File 'lib/robust_excel_ole/workbook.rb', line 808

def focus
  self.visible = true
  @excel.focus
  @ole_workbook.Activate
end

#for_this_workbook(opts) ⇒ Object

sets options

Parameters:

  • opts (Hash) 


789
790
791
792
793
794
795
796
797
798
799
800
801
802
803
804
805
 
# File 'lib/robust_excel_ole/workbook.rb', line 789

def for_this_workbook(opts)
  return unless alive?
  opts = self.class.process_options(opts, :use_defaults => false)
  visible_before = visible
  check_compatibility_before = check_compatibility
  unless opts[:read_only].nil?
    # if the ReadOnly status shall be changed, then close and reopen it
    if (!writable && !(opts[:read_only])) || (writable && opts[:read_only])
      opts[:check_compatibility] = check_compatibility if opts[:check_compatibility].nil?
      close(:if_unsaved => true)
      open_or_create_workbook(@stored_filename, opts)
    end
  end
  self.visible = opts[:force][:visible].nil? ? visible_before : opts[:force][:visible]
  self.CheckCompatibility = opts[:check_compatibility].nil? ? check_compatibility_before : opts[:check_compatibility]
  @excel.calculation = opts[:calculation] unless opts[:calculation].nil?
end

#inspect ⇒ Object

:nodoc:



893
894
895
 
# File 'lib/robust_excel_ole/workbook.rb', line 893

def inspect    
  '#<Workbook: ' + ('not alive ' unless alive?).to_s + (File.basename(self.filename) if alive?).to_s + " #{@ole_workbook} #{@excel}" + '>'
end

#last_sheet ⇒ Object 



765
766
767
 
# File 'lib/robust_excel_ole/workbook.rb', line 765

def last_sheet
  worksheet_class.new(@ole_workbook.Worksheets.Item(@ole_workbook.Worksheets.Count))
end

#reopen(options = { }) ⇒ Object

reopens a closed workbook



542
543
544
545
546
 
# File 'lib/robust_excel_ole/workbook.rb', line 542

def reopen(options = { })
  book = self.class.open(@stored_filename, options)
  raise WorkbookREOError('cannot reopen book') unless book && book.alive?
  book
end

#retain_saved ⇒ Object

keeps the saved-status unchanged



450
451
452
453
454
455
456
457
 
# File 'lib/robust_excel_ole/workbook.rb', line 450

def retain_saved
  saved = self.Saved
  begin
    yield self
  ensure
    self.Saved = saved
  end
end

#save(opts = {:discoloring => false}) ⇒ Boolean

simple save of a workbook.

Parameters:

  • opts (Hash) (defaults to: {:discoloring => false}) —

    a customizable set of options

Options Hash (opts):

  • :discoloring (Boolean) —

    states, whether colored ranges shall be discolored

Returns:

  • (Boolean) —

    true, if successfully saved, nil otherwise

Raises:



551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
 
# File 'lib/robust_excel_ole/workbook.rb', line 551

def save(opts = {:discoloring => false})
  raise ObjectNotAlive, 'workbook is not alive' unless alive?
  raise WorkbookReadOnly, 'Not opened for writing (opened with :read_only option)' if @ole_workbook.ReadOnly
  begin
    discoloring if opts[:discoloring]
    @modified_cells = []
    @ole_workbook.Save
  rescue WIN32OLERuntimeError => 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, opts = { }) ⇒ Workbook

saves a workbook with a given file name. 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
                :raise               -> raises an exception
                :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

:discoloring states, whether colored ranges shall be discolored

Parameters:

  • file (String) —

    file name

  • opts (Hash) (defaults to: { }) —

    the options

Options Hash (opts):

  • :if_exists (Symbol) —

    :raise (default), :overwrite, or :alert, :excel

  • :if_obstructed (Symbol) —

    :raise (default), :forget, :save, or :close_if_saved

Returns:

  • (Workbook) —

    , the book itself, if successfully saved, raises an exception otherwise

Raises:



586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
 
# File 'lib/robust_excel_ole/workbook.rb', line 586

def save_as(file, opts = { })
  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
  options = {
    :if_exists => :raise,
    :if_obstructed => :raise
  }.merge(opts)
  if File.exist?(file)
    case options[:if_exists]
    when :overwrite
      if file == self.filename
        save({:discoloring => opts[:discoloring]})
        return self
      else
        begin
          File.delete(file)
        rescue Errno::EACCES
          raise WorkbookBeingUsed, 'workbook is open and used in Excel'
        end
      end
    when :alert, :excel
      @excel.with_displayalerts true do
        save_as_workbook(file, options)
      end
      return self
    when :raise
      raise FileAlreadyExists, "file already exists: #{File.basename(file).inspect}"
    else
      raise OptionInvalid, ":if_exists: invalid option: #{options[:if_exists].inspect}"
    end
  end
  other_workbook = @excel.Workbooks.Item(File.basename(file)) rescue nil
  if other_workbook && self.filename != other_workbook.Fullname.tr('\\','/')
    case options[:if_obstructed]
    when :raise
      raise WorkbookBlocked, "blocked by another workbook: #{other_workbook.Fullname.tr('\\','/')}"
    when :forget
      # nothing
    when :save
      other_workbook.Save
    when :close_if_saved
      raise WorkbookBlocked, "blocking workbook is unsaved: #{File.basename(file).inspect}" unless other_workbook.Saved
    else
      raise OptionInvalid, ":if_obstructed: invalid option: #{options[:if_obstructed].inspect}"
    end
    other_workbook.Close
  end
  save_as_workbook(file, options)
  self
end

#saved ⇒ Object

:nodoc:



833
834
835
 
# File 'lib/robust_excel_ole/workbook.rb', line 833

def saved   
  @ole_workbook.Saved if @ole_workbook
end

#sheet(name) ⇒ Object

returns a sheet, if a sheet name or a number is given

Parameters:

Raises:



689
690
691
692
693
 
# File 'lib/robust_excel_ole/workbook.rb', line 689

def sheet(name)
  worksheet_class.new(@ole_workbook.Worksheets.Item(name))
rescue WIN32OLERuntimeError => msg
  raise NameNotFound, "could not return a sheet with name #{name.inspect}"
end

#to_s ⇒ Object

:nodoc:



889
890
891
 
# File 'lib/robust_excel_ole/workbook.rb', line 889

def to_s    
  self.filename.to_s
end

#visible ⇒ Object

returns true, if the workbook is visible, false otherwise



846
847
848
 
# File 'lib/robust_excel_ole/workbook.rb', line 846

def visible
  @excel.visible && @ole_workbook.Windows(@ole_workbook.Name).Visible
end

#visible=(visible_value) ⇒ Object

makes both the Excel instance and the window of the workbook visible, or the window invisible

Parameters:

  • visible_value (Boolean) —

    determines whether the workbook shall be visible



852
853
854
855
 
# File 'lib/robust_excel_ole/workbook.rb', line 852

def visible= visible_value
  @excel.visible = true if visible_value
  self.window_visible = visible_value
end

#window_visible ⇒ Object

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



858
859
860
 
# File 'lib/robust_excel_ole/workbook.rb', line 858

def window_visible
  @ole_workbook.Windows(@ole_workbook.Name).Visible
end

#window_visible=(visible_value) ⇒ Object

makes the window of the workbook visible or invisible

Parameters:

  • visible_value (Boolean) —

    determines whether the window of the workbook shall be visible



864
865
866
867
868
 
# File 'lib/robust_excel_ole/workbook.rb', line 864

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

#worksheet_class ⇒ Object

:nodoc:



920
921
922
 
# File 'lib/robust_excel_ole/workbook.rb', line 920

def worksheet_class        
  self.class.worksheet_class
end

#writable ⇒ Object

:nodoc:



829
830
831
 
# File 'lib/robust_excel_ole/workbook.rb', line 829

def writable   
  !@ole_workbook.ReadOnly if @ole_workbook
end