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

#cell_modified?, #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

def initialize(file_or_workbook, opts={ }, &block)

Parameters:

  • file_or_workbook (Variant) —

    file name or workbook

  • opts (Hash) —

    the options



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

def initialize(file_or_workbook, options={ }, &block)
  #options = self.class.process_options(opts)
  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: #



965
966
967
968
969
970
971
972
973
974
975
976
977
978
979
980
 
# File 'lib/robust_excel_ole/book.rb', line 965

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 



914
915
916
 
# File 'lib/robust_excel_ole/book.rb', line 914

def self.books
  bookstore.books
end

.bookstore ⇒ Object

:nodoc: #



918
919
920
 
# File 'lib/robust_excel_ole/book.rb', line 918

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

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

closes a given file if it is open



682
683
684
685
 
# File 'lib/robust_excel_ole/book.rb', line 682

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



934
935
936
937
938
939
940
941
942
 
# File 'lib/robust_excel_ole/book.rb', line 934

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 



482
483
484
485
486
487
488
 
# File 'lib/robust_excel_ole/book.rb', line 482

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

.for_reading(*args, &block) ⇒ Object 



474
475
476
477
478
479
480
 
# File 'lib/robust_excel_ole/book.rb', line 474

def self.for_reading(*args, &block)
  args = args.dup
  opts = args.last.is_a?(Hash) ? args.pop : {}
  opts = {:read_only => true}.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



116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
 
# File 'lib/robust_excel_ole/book.rb', line 116

def self.new(workbook, opts={ }, &block)
  if workbook && (workbook.is_a? WIN32OLE)
    opts = process_options(opts)
    filename = workbook.Fullname.tr('\\','/') rescue nil
    if filename
      book = bookstore.fetch(filename)
      if book && book.alive?
        book.visible = opts[:force][:visible] unless opts[:force][:visible].nil?
        #book.excel.calculation = opts[:calculation].nil? ? book.excel.calculation : opts[:calculation]
        book.excel.calculation = opts[:calculation] unless opts[:calculation].nil?
        return book 
      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 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
 
# File 'lib/robust_excel_ole/book.rb', line 77

def open(file, opts={ }, &block)
  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 (((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, then save, close and reopen it
        book.close(:if_unsaved => :save) if (book.alive? && 
          (((not book.writable) and (not options[:read_only])) or
            (book.writable and options[:read_only]))) 
        # reopens the book
        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



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

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



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

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



944
945
946
947
948
949
950
951
 
# File 'lib/robust_excel_ole/book.rb', line 944

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

state = [:open, :saved, :writable, :visible, :calculation, :check_compatibility]



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
539
540
541
542
543
544
545
546
547
548
549
550
 
# File 'lib/robust_excel_ole/book.rb', line 504

def self.unobtrusively(file, opts = { }, &block) 
  options = {:if_closed => :current, 
             :read_only => false,
             :readonly_excel => false,
             :keep_open => false}.merge(opts)
  book = bookstore.fetch(file, :prefer_writable => (not options[:read_only]))
  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
  end     
  begin 
    book = 
      if was_open 
        if (not was_writable) && (not options[:read_only])
          open(file, :force => {:excel => (options[:readonly_excel] ? book.excel : :new)}, :read_only => false)
        else
          book
        end
      else
        open(file, :force => {:excel => options[:if_closed]}, :read_only => false)
      end
    yield book
  ensure
    if book && book.alive?
      unless book.saved
        book.save unless options[:read_only]
        book.Saved = true if (was_saved || (not was_open)) && options[:read_only]
        book.Saved = false if (not was_saved) && (not options[:read_only]) && was_open
      end
      if was_open
        if (not was_writable) && (not options[:read_only]) && options[:readonly_excel]
          book.close
          open(file, :force => {:excel => book.excel}, :if_obstructed => :new_excel, :read_only => true)
        end         
        book.excel.calculation = was_calculation
        book.CheckCompatibility = was_check_compatibility
        #book.visible = was_visible  # not necessary
      end          
      
      book.close unless was_open || options[: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



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

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



791
792
793
 
# File 'lib/robust_excel_ole/book.rb', line 791

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



798
799
800
 
# File 'lib/robust_excel_ole/book.rb', line 798

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



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

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



767
768
769
770
771
772
773
 
# File 'lib/robust_excel_ole/book.rb', line 767

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



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

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) 


850
851
852
853
854
855
856
857
858
859
 
# File 'lib/robust_excel_ole/book.rb', line 850

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



922
923
924
 
# File 'lib/robust_excel_ole/book.rb', line 922

def bookstore    
  self.class.bookstore
end

#calculation ⇒ Object 



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

def calculation
  @excel.calculation if @ole_workbook
end

#check_compatibility ⇒ Object 



878
879
880
 
# File 'lib/robust_excel_ole/book.rb', line 878

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



431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
 
# File 'lib/robust_excel_ole/book.rb', line 431

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



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

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 



710
711
712
713
714
 
# File 'lib/robust_excel_ole/book.rb', line 710

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

#each_with_index(offset = 0) ⇒ Object 



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

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

# work in progress#

def self.open_in_current_excel(file, opts = { }) # :nodoc: # 
  options = DEFAULT_OPEN_OPTS.merge(opts)
  filename = General::absolute_path(file)
  ole_workbook = WIN32OLE.connect(filename)
  workbook = Book.new(ole_workbook)
  workbook.visible = options[:force][:visible] unless options[:force][:visible].nil?
  update_links_opt =
        case options[:update_links]
        when :alert; RobustExcelOle::XlUpdateLinksUserSetting
        when :never; RobustExcelOle::XlUpdateLinksNever
        when :always; RobustExcelOle::XlUpdateLinksAlways
        else RobustExcelOle::XlUpdateLinksNever
      end
  workbook.UpdateLinks = update_links_opt
  workbook.CheckCompatibility = options[:check_compatibility]
  workbook
end



252
253
254
255
256
257
258
259
260
261
262
263
264
265
 
# File 'lib/robust_excel_ole/book.rb', line 252

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?)

  #options[:excel] = options[:force_excel] ? options[:force_excel] : options[:default_excel]
  #options[:excel] = :current if (options[:excel] == :reuse || options[:excel] == :active)
  #@excel = self.class.excel_of(options[:excel]) unless (options[:excel] == :current || options[:excel] == :new)
  #@excel = excel_class.new(:reuse => (options[:excel] == :current)) unless (@excel && @excel.alive?)
end

#ensure_workbook(file, options) ⇒ Object

:nodoc: #

Raises:



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
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
 
# File 'lib/robust_excel_ole/book.rb', line 267

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



953
954
955
 
# File 'lib/robust_excel_ole/book.rb', line 953

def excel_class        
  self.class.excel_class
end

#filename ⇒ Object

returns the full file name of the workbook



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

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

#first_sheet ⇒ Object 



784
785
786
 
# File 'lib/robust_excel_ole/book.rb', line 784

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



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

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

#for_this_workbook(opts) ⇒ Object

sets options

Parameters:

  • opts (Hash) 


820
821
822
823
824
825
826
827
828
829
830
831
832
833
834
835
836
837
838
839
840
 
# File 'lib/robust_excel_ole/book.rb', line 820

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] = opts[:check_compatibility].nil? ? check_compatibility :
      #                             DEFAULT_OPEN_OPTS[:check_compatibility] 
      #opts[:check_compatibility] = opts[:check_compatibility].nil? ? check_compatibility :
      #                             opts[:check_compatibility]
      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: #



930
931
932
 
# File 'lib/robust_excel_ole/book.rb', line 930

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

#last_sheet ⇒ Object 



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

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



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

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



554
555
556
 
# File 'lib/robust_excel_ole/book.rb', line 554

def reopen(options = { })
  self.class.open(@stored_filename, options)
end

#retain_saved ⇒ Object

keeps the saved-status unchanged



465
466
467
468
469
470
471
472
 
# File 'lib/robust_excel_ole/book.rb', line 465

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:



561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
 
# File 'lib/robust_excel_ole/book.rb', line 561

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:



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
637
638
639
640
641
642
643
644
645
646
 
# File 'lib/robust_excel_ole/book.rb', line 596

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



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

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:



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

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



957
958
959
 
# File 'lib/robust_excel_ole/book.rb', line 957

def sheet_class        
  self.class.sheet_class
end

#to_s ⇒ Object

:nodoc: #



926
927
928
 
# File 'lib/robust_excel_ole/book.rb', line 926

def to_s    
  "#{self.filename}"
end

#visible ⇒ Object

returns true, if the workbook is visible, false otherwise



883
884
885
 
# File 'lib/robust_excel_ole/book.rb', line 883

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



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

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



895
896
897
 
# File 'lib/robust_excel_ole/book.rb', line 895

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



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

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



866
867
868
 
# File 'lib/robust_excel_ole/book.rb', line 866

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