Class: RobustExcelOle::Book

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

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
}
ABBREVIATIONS = 
[[:default,:d], [:force, :f], [:excel, :e], [:visible, :v]]

Instance Attribute Summary collapse

Class Method Summary collapse

Instance Method Summary collapse

Methods inherited from RangeOwners

#name_object, #nameval, #rangeval, #set_nameval, #set_rangeval

Methods inherited from REOCommon

#own_methods, puts_hash, tr1, trace

Constructor Details

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

creates a new Book object, if a file name is given Promotes the workbook to a Book object, if a win32ole-workbook is given

Parameters:

  • file_or_workbook (Variant) —

    file name or workbook

  • opts (Hash) —

    the options



145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
 
# File 'lib/robust_excel_ole/book.rb', line 145

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



944
945
946
947
948
949
950
951
952
953
954
955
956
957
958
959
 
# File 'lib/robust_excel_ole/book.rb', line 944

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



9
10
11
 
# File 'lib/robust_excel_ole/book.rb', line 9

def excel
  @excel
end

#modified_cells ⇒ Object

Returns the value of attribute modified_cells



13
14
15
 
# File 'lib/robust_excel_ole/book.rb', line 13

def modified_cells
  @modified_cells
end

#ole_workbook ⇒ Object

Returns the value of attribute ole_workbook



10
11
12
 
# File 'lib/robust_excel_ole/book.rb', line 10

def ole_workbook
  @ole_workbook
end

#options ⇒ Object

Returns the value of attribute options



12
13
14
 
# File 'lib/robust_excel_ole/book.rb', line 12

def options
  @options
end

#stored_filename ⇒ Object

Returns the value of attribute stored_filename



11
12
13
 
# File 'lib/robust_excel_ole/book.rb', line 11

def stored_filename
  @stored_filename
end

#workbook ⇒ Object (readonly)

Returns the value of attribute workbook



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

def workbook
  @workbook
end

Class Method Details

.books ⇒ Object 



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

def self.books
  bookstore.books
end

.bookstore ⇒ Object

:nodoc: #



897
898
899
 
# File 'lib/robust_excel_ole/book.rb', line 897

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

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

closes a given file if it is open



665
666
667
668
 
# File 'lib/robust_excel_ole/book.rb', line 665

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



913
914
915
916
917
918
919
920
921
 
# File 'lib/robust_excel_ole/book.rb', line 913

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 



461
462
463
464
465
466
467
 
# File 'lib/robust_excel_ole/book.rb', line 461

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 



453
454
455
456
457
458
459
 
# File 'lib/robust_excel_ole/book.rb', line 453

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) ⇒ Book

creates a Book object by opening an Excel file given its filename workbook or by lifting 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:

  • (Book) —

    a workbook



120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
 
# File 'lib/robust_excel_ole/book.rb', line 120

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? or 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) ⇒ Book

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:

  • (Book) —

    a representation of a workbook



77
78
79
80
81
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
 
# File 'lib/robust_excel_ole/book.rb', line 77

def open(file, opts={ }, &block)
  options = @options = process_options(opts)
  book = nil
  if (not (options[:force][:excel] == :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 => (not 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 (((not options[:force][:excel]) || (forced_excel == book.excel)) &&
          (not (book.alive? && (not book.saved) && (not 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? and (((not book.writable) and (not options[:read_only])) or
           (book.writable and options[:read_only]))
          book.save if book.writable && (not 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



671
672
673
674
 
# File 'lib/robust_excel_ole/book.rb', line 671

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



677
678
679
680
 
# File 'lib/robust_excel_ole/book.rb', line 677

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

.sheet_class ⇒ Object

:nodoc: #



923
924
925
926
927
928
929
930
 
# File 'lib/robust_excel_ole/book.rb', line 923

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

.unobtrusively(file, opts = { }, &block) ⇒ Book

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:

  • (Book) —

    a workbook

Raises:



481
482
483
484
485
486
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
 
# File 'lib/robust_excel_ole/book.rb', line 481

def self.unobtrusively(file, opts = { }, &block) 
  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 = (((not opts[:read_only]) || opts[:writable]==true) && 
                     (not (opts[:read_only].nil?) && opts[:writable]==false))
  do_not_write = (opts[:read_only] or (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] && (not was_writable) && (not was_saved)) ||
        (opts[:read_only] && was_writable && (not 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) or (opts[:writable] && (not 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 => (not was_writable))
        end    
        book.excel.calculation = was_calculation
        book.CheckCompatibility = was_check_compatibility
        #book.visible = was_visible  # not necessary
      end
      book.Saved = (was_saved || (not was_open))
      book.close unless was_open || opts[:keep_open]
    end
  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



887
888
889
890
891
 
# File 'lib/robust_excel_ole/book.rb', line 887

def == other_book
  other_book.is_a?(Book) &&
  @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



774
775
776
 
# File 'lib/robust_excel_ole/book.rb', line 774

def [] name
  nameval(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



781
782
783
 
# File 'lib/robust_excel_ole/book.rb', line 781

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

#add_empty_sheet(opts = { }) ⇒ Sheet

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:

  • (Sheet) —

    the added sheet

Raises:

  • NameAlreadyExists if the sheet name already exists



733
734
735
736
737
738
739
740
 
# File 'lib/robust_excel_ole/book.rb', line 733

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 = sheet_class.new(@excel.Activesheet)
  new_sheet.name = new_sheet_name if new_sheet_name
  new_sheet
end

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

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 (Sheet) (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:

  • (Sheet) —

    the copied or added sheet



750
751
752
753
754
755
756
 
# File 'lib/robust_excel_ole/book.rb', line 750

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



759
760
761
 
# File 'lib/robust_excel_ole/book.rb', line 759

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) 


829
830
831
832
833
834
835
836
837
838
 
# File 'lib/robust_excel_ole/book.rb', line 829

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

#bookstore ⇒ Object

:nodoc: #



901
902
903
 
# File 'lib/robust_excel_ole/book.rb', line 901

def bookstore    
  self.class.bookstore
end

#calculation ⇒ Object 



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

def calculation
  @excel.calculation if @ole_workbook
end

#check_compatibility ⇒ Object 



857
858
859
 
# File 'lib/robust_excel_ole/book.rb', line 857

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



410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
 
# File 'lib/robust_excel_ole/book.rb', line 410

def close(opts = {:if_unsaved => :raise})
  if (alive? && (not @ole_workbook.Saved) && writable) then
    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 = { }) ⇒ Sheet

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

Parameters:

  • sheet (Sheet) —

    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:

  • (Sheet) —

    the copied sheet

Raises:

  • NameAlreadyExists if the sheet name already exists



716
717
718
719
720
721
722
723
 
# File 'lib/robust_excel_ole/book.rb', line 716

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 = sheet_class.new(@excel.Activesheet)
  new_sheet.name = new_sheet_name if new_sheet_name
  new_sheet
end

#each ⇒ Object 



693
694
695
696
697
 
# File 'lib/robust_excel_ole/book.rb', line 693

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

#each_with_index(offset = 0) ⇒ Object 



699
700
701
702
703
704
705
 
# File 'lib/robust_excel_ole/book.rb', line 699

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

#ensure_excel(options) ⇒ Object

:nodoc: #



235
236
237
238
239
240
241
242
243
244
 
# File 'lib/robust_excel_ole/book.rb', line 235

def ensure_excel(options)   # :nodoc: #
  if excel && @excel.alive?  
    @excel.created = false
    return
  end
  excel_option = (options[:force].nil? or 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 = excel_class.new(:reuse => (excel_option == :current)) unless (@excel && @excel.alive?)
  @excel
end

#ensure_workbook(file, options) ⇒ Object

:nodoc: #

Raises:



246
247
248
249
250
251
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
 
# File 'lib/robust_excel_ole/book.rb', line 246

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 then
    obstructed_by_other_book = (File.basename(file) == File.basename(@ole_workbook.Fullname)) && 
                               (not (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 then
      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 (not @ole_workbook.Saved) then
          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
      if (not @ole_workbook.Saved) then
        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: #



932
933
934
 
# File 'lib/robust_excel_ole/book.rb', line 932

def excel_class        
  self.class.excel_class
end

#filename ⇒ Object

returns the full file name of the workbook



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

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

#first_sheet ⇒ Object 



767
768
769
 
# File 'lib/robust_excel_ole/book.rb', line 767

def first_sheet
  sheet_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



822
823
824
825
826
 
# File 'lib/robust_excel_ole/book.rb', line 822

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

#for_this_workbook(opts) ⇒ Object

sets options

Parameters:

  • opts (Hash) 


803
804
805
806
807
808
809
810
811
812
813
814
815
816
817
818
819
 
# File 'lib/robust_excel_ole/book.rb', line 803

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 ((not writable) and (not opts[:read_only])) or (writable and 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: #



909
910
911
 
# File 'lib/robust_excel_ole/book.rb', line 909

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

#last_sheet ⇒ Object 



763
764
765
 
# File 'lib/robust_excel_ole/book.rb', line 763

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

#rename_range(name, new_name) ⇒ Object

renames a range

Parameters:

  • name (String) —

    the previous range name

  • new_name (String) —

    the new range name



788
789
790
791
792
793
794
795
796
797
798
799
 
# File 'lib/robust_excel_ole/book.rb', line 788

def rename_range(name, new_name)
  begin
    item = self.Names.Item(name)
  rescue WIN32OLERuntimeError
    raise NameNotFound, "name #{name.inspect} not in #{File.basename(self.stored_filename).inspect}"  
  end
  begin
    item.Name = new_name
  rescue WIN32OLERuntimeError
    raise UnexpectedREOError, "name error in #{File.basename(self.stored_filename).inspect}"      
  end
end

#reopen(options = { }) ⇒ Object

reopens a closed workbook



536
537
538
539
540
 
# File 'lib/robust_excel_ole/book.rb', line 536

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



444
445
446
447
448
449
450
451
 
# File 'lib/robust_excel_ole/book.rb', line 444

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:



545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
 
# File 'lib/robust_excel_ole/book.rb', line 545

def save(opts = {:discoloring => false})  
  raise ObjectNotAlive, "workbook is not alive" if (not 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/ and msg.message =~ /Workbook/ then
      raise WorkbookNotSaved, "workbook not saved"
    else
      raise UnexpectedREOError, "unknown WIN32OLERuntimeError:\n#{msg.message}"
    end       
  end      
  true
end

#save_as(file, opts = { }) ⇒ Book

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:

  • (Book) —

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

Raises:



580
581
582
583
584
585
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
 
# File 'lib/robust_excel_ole/book.rb', line 580

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) then
    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 && (not(self.filename == other_workbook.Fullname.tr('\\','/'))) then
    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: #



849
850
851
 
# File 'lib/robust_excel_ole/book.rb', line 849

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:



685
686
687
688
689
690
691
 
# File 'lib/robust_excel_ole/book.rb', line 685

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

#sheet_class ⇒ Object

:nodoc: #



936
937
938
 
# File 'lib/robust_excel_ole/book.rb', line 936

def sheet_class        
  self.class.sheet_class
end

#to_s ⇒ Object

:nodoc: #



905
906
907
 
# File 'lib/robust_excel_ole/book.rb', line 905

def to_s    
  "#{self.filename}"
end

#visible ⇒ Object

returns true, if the workbook is visible, false otherwise



862
863
864
 
# File 'lib/robust_excel_ole/book.rb', line 862

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



868
869
870
871
 
# File 'lib/robust_excel_ole/book.rb', line 868

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



874
875
876
 
# File 'lib/robust_excel_ole/book.rb', line 874

def window_visible
  return @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



880
881
882
883
884
 
# File 'lib/robust_excel_ole/book.rb', line 880

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

#writable ⇒ Object

:nodoc: #



845
846
847
 
# File 'lib/robust_excel_ole/book.rb', line 845

def writable   
  (not @ole_workbook.ReadOnly) if @ole_workbook
end