Class: RobustExcelOle::Excel

Inherits:
VbaObjects show all
Defined in:
lib/robust_excel_ole/excel.rb

Overview

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

Constant Summary collapse

PROPERTIES = 
[:visible, :displayalerts, :calculation, :screenupdating]
@@hwnd2excel = 
{}

Instance Attribute Summary collapse

Class Method Summary collapse

Instance Method Summary collapse

Methods inherited from VbaObjects

#to_reo

Constructor Details

#initialize(options = {}) ⇒ Excel

Returns a new instance of Excel.



154
 
# File 'lib/robust_excel_ole/excel.rb', line 154

def initialize(options = {}) end

Dynamic Method Handling

This class handles dynamic methods through the method_missing method

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

Raises:

  • (ObjectNotAlive) 


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

def method_missing(name, *args) 
  super unless name.to_s[0,1] =~ /[A-Z]/
  raise ObjectNotAlive, 'method missing: Excel not alive' unless alive?
  if ::ERRORMESSAGE_JRUBY_BUG
    begin
      @ole_excel.send(name, *args)
    rescue Java::OrgRacobCom::ComFailException
      raise VBAMethodMissingError, "unknown VBA property or method #{name.inspect}"
    end
  else
    begin
      @ole_excel.send(name, *args)
    rescue NoMethodError
      raise VBAMethodMissingError, "unknown VBA property or method #{name.inspect}"
    end
  end
end

Instance Attribute Details

#hwnd ⇒ Object (readonly)

Returns the value of attribute hwnd



77
78
79
 
# File 'lib/robust_excel_ole/excel.rb', line 77

def hwnd
  @hwnd
end

#ole_excel ⇒ Object (readonly)

Returns the value of attribute ole_excel



74
75
76
 
# File 'lib/robust_excel_ole/excel.rb', line 74

def ole_excel
  @ole_excel
end

#properties ⇒ Object (readonly)

Returns the value of attribute properties



75
76
77
 
# File 'lib/robust_excel_ole/excel.rb', line 75

def properties
  @properties
end

Class Method Details

.close_all(options = { if_unsaved: :raise }, &blk) ⇒ Integer

closes all Excel instances remark: the returned number of closed Excel instances is valid only for known Excel instances if there are unknown Excel instances (opened not via this class), then they are counted as 1 options:

:if_unsaved    if unsaved workbooks are open in an Excel instance
                    :raise (default) -> raises an exception
                    :save            -> saves the workbooks before closing
                    :forget          -> closes the excel instance without saving the workbooks
                    :alert           -> give control to Excel

Parameters:

  • options (Hash) (defaults to: { if_unsaved: :raise }) —

    the options

Options Hash (options):

  • :if_unsaved (Symbol) —

    :raise, :save, :forget, or :alert

  • block (Proc)

Returns:

  • (Integer, Integer) —

    number of closed Excel instances, number of errors



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
 
# File 'lib/robust_excel_ole/excel.rb', line 285

def self.close_all(options = { if_unsaved: :raise }, &blk)
  options[:if_unsaved] = blk if blk
  finished_number = error_number = overall_number = 0
  first_error = nil
  finishing_action = proc do |excel|
    if excel
      begin
        overall_number += 1
        finished_number += excel.close(if_unsaved: options[:if_unsaved])
      rescue
        first_error = $!
        error_number += 1
      end
    end
  end

  # known Excel-instances
  @@hwnd2excel.each do |hwnd, wr_excel|
    if wr_excel.weakref_alive?
      excel = wr_excel.__getobj__
      if excel.alive?
        excel.displayalerts = false
        finishing_action.call(excel)
      end
    else
      @@hwnd2excel.delete(hwnd)
    end
  end

  # unknown Excel-instances
  old_error_number = error_number
  9.times do |_index|
    sleep 0.1
    excel = new(WIN32OLE.connect('Excel.Application')) rescue nil
    finishing_action.call(excel) if excel
    free_all_ole_objects unless (error_number > 0) && (options[:if_unsaved] == :raise)
    break unless excel
    break if error_number > old_error_number # + 3
  end
  raise first_error if ((options[:if_unsaved] == :raise) && first_error) || (first_error.class == OptionInvalid)
  [finished_number, error_number]
end

.create(options = {}) ⇒ Excel

creates a new Excel instance

Parameters:

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

    the options

Options Hash (options):

  • :displayalerts (Variant)
  • :visible (Boolean)
  • :calculation (Symbol)
  • :screenupdating (Boolean)

Returns:

  • (Excel) —

    a new Excel instance



92
93
94
 
# File 'lib/robust_excel_ole/excel.rb', line 92

def self.create(options = {})
  new(options.merge(reuse: false))
end

.current(options = {}) ⇒ Excel

connects to the current (first opened) Excel instance, if such a running Excel instance exists returns a new Excel instance, otherwise

Parameters:

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

    a customizable set of options

Options Hash (options):

  • :displayalerts (Variant)
  • :visible (Boolean)
  • :calculation (Symbol)
  • :screenupdating (Boolean)

Returns:

  • (Excel) —

    an Excel instance



103
104
105
 
# File 'lib/robust_excel_ole/excel.rb', line 103

def self.current(options = {})
  new(options.merge(reuse: true))
end

.init ⇒ Object 



386
387
388
 
# File 'lib/robust_excel_ole/excel.rb', line 386

def self.init
  @@hwnd2excel = {}
end

.instance_count ⇒ Object 



408
409
410
 
# File 'lib/robust_excel_ole/excel.rb', line 408

def self.instance_count
  WIN32OLE.connect('winmgmts:\\\\.').InstancesOf('win32_process').select { |p| p.Name == 'EXCEL.EXE' }.size
end

.kill_all ⇒ Integer

kill all Excel instances

Returns:

  • (Integer) —

    number of killed Excel processes



392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
 
# File 'lib/robust_excel_ole/excel.rb', line 392

def self.kill_all
  number = 0
  WIN32OLE.connect('winmgmts:\\\\.').InstancesOf('win32_process').each do |p|
    begin
      if p.Name == 'EXCEL.EXE'
        Process.kill('KILL', p.processid)
        number += 1
      end
    rescue
      # trace "kill error: #{$!}"
    end
  end
  init
  number
end

.known_instances_count ⇒ Object 



412
413
414
 
# File 'lib/robust_excel_ole/excel.rb', line 412

def self.known_instances_count
  @@hwnd2excel.size
end

.new(win32ole_excel = nil, options = {}) ⇒ Excel

returns an Excel instance options:

:reuse            connects to an already running Excel instance (true) or
                  creates a new Excel instance (false)  (default: true)
:visible          makes the Excel visible               (default: false)
:displayalerts    enables or disables DisplayAlerts     (true, false, :if_visible (default))
:calculation      calculation mode is being forced to be manual (:manual) or automatic (:automtic)
                  or is not being forced (default: nil)
:screenupdating  turns on or off screen updating (default: true)

Parameters:

  • (optional) (Win32Ole) —

    a WIN32OLE object representing an Excel instance

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

    the options

Options Hash (options):

  • :reuse (Boolean)
  • :visible (Boolean)
  • :displayalerts (Variant)
  • :screenupdating (Boolean)
  • :calculation (Symbol)

Returns:

  • (Excel) —

    an Excel instance



124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
 
# File 'lib/robust_excel_ole/excel.rb', line 124

def self.new(win32ole_excel = nil, options = {})
  if win32ole_excel.is_a? Hash
    options = win32ole_excel
    win32ole_excel = nil
  end
  options = { reuse: true }.merge(options)
  ole_xl = if !win32ole_excel.nil? 
    win32ole_excel
  elsif options[:reuse] == true
    current_ole_excel
  end
  connected = (not ole_xl.nil?) && win32ole_excel.nil?
  ole_xl ||= WIN32OLE.new('Excel.Application')
  hwnd_xl = ole_xl.Hwnd
  stored = hwnd2excel(hwnd_xl)
  if stored && stored.alive?
    result = stored
  else
    result = super(options)
    result.instance_variable_set(:@ole_excel, ole_xl)
    result.instance_variable_set(:@hwnd, hwnd_xl)
    WIN32OLE.const_load(ole_xl, RobustExcelOle) unless RobustExcelOle.const_defined?(:CONSTANTS)
    @@hwnd2excel[hwnd_xl] = WeakRef.new(result)
  end
  reused = options[:reuse] && stored && stored.alive? 
  options = { displayalerts: :if_visible, visible: false, screenupdating: true }.merge(options) unless reused || connected
  result.set_options(options)        
  result
end

Instance Method Details

#==(other_excel) ⇒ Object

returns true, if the Excel instances are alive and identical, false otherwise



556
557
558
 
# File 'lib/robust_excel_ole/excel.rb', line 556

def == other_excel
  self.Hwnd == other_excel.Hwnd if other_excel.is_a?(Excel) && alive? && other_excel.alive?
end

#alive? ⇒ Boolean

returns true, if the Excel instances responds to VBA methods, false otherwise

Returns:

  • (Boolean) 


573
574
575
576
577
578
579
 
# File 'lib/robust_excel_ole/excel.rb', line 573

def alive?
  @ole_excel.Name
  true
rescue
  # trace $!.message
  false
end

#calculation=(calculation_mode) ⇒ Object

sets calculation mode retains the saved-status of the workbooks when set to manual



643
644
645
646
647
648
649
650
651
652
653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
670
671
672
 
# File 'lib/robust_excel_ole/excel.rb', line 643

def calculation= calculation_mode
  return if calculation_mode.nil?
  @properties[:calculation] = calculation_mode
  calc_mode_changable = @ole_excel.Workbooks.Count > 0 && @ole_excel.Calculation.is_a?(Integer)
  return unless calc_mode_changable
  retain_saved_workbooks do
    begin
      best_wb_to_make_visible = @ole_excel.Workbooks.sort_by {|wb|
        score =
          (wb.Saved    ? 0 : 40) +  # an unsaved workbooks is most likely the main workbook
          (wb.ReadOnly ? 0 : 20) +  # the main wb is usually writable
          case wb.Name.split(".").last.downcase
            when "xlsm" then 10  # the main workbook is more likely to have macros
            when "xls"  then  8
            when "xlsx" then  4
            when "xlam" then -2  # libraries are not normally the main workbook
            else 0
          end
        score
      }.last
      best_wb_to_make_visible.Windows(1).Visible = true
    rescue => e
      trace "error setting calculation=#{calculation_mode} msg: " + e.message
      trace e.backtrace
      # continue on errors here, failing would usually disrupt too much
    end
    @ole_excel.CalculateBeforeSave = false
    @ole_excel.Calculation = calculation_mode == :automatic ? XlCalculationAutomatic : XlCalculationManual
  end
end

#Calculation=(calculation_vba_mode) ⇒ Object

VBA method overwritten



675
676
677
678
679
680
681
682
683
 
# File 'lib/robust_excel_ole/excel.rb', line 675

def Calculation= calculation_vba_mode
  case calculation_vba_mode
  when XlCalculationManual
    @properties[:calculation] = :manual
  when XlCalculationAutomatic
    @properties[:calculation] = :automatic
  end
  @ole_excel.Calculation = calculation_vba_mode
end

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

closes the Excel

Parameters:

  • options (Hash) (defaults to: { if_unsaved: :raise }) —

    the options

Options Hash (options):

  • :if_unsaved (Symbol) —

    :raise, :save, :forget, :alert

  • :hard (Boolean) —

    :if_unsaved if unsaved workbooks are open in an Excel instance

    :raise (default) -> raises an exception
:save            -> saves the workbooks before closing
:forget          -> closes the Excel instance without saving the workbooks
:alert           -> Excel takes over



337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
 
# File 'lib/robust_excel_ole/excel.rb', line 337

def close(options = { if_unsaved: :raise })
  finishing_living_excel = alive?
  if finishing_living_excel
    hwnd = @ole_excel.Hwnd rescue nil
    close_workbooks(if_unsaved: options[:if_unsaved])
    @ole_excel.Quit
    weak_wkbks.ole_free if false && defined?(weak_wkbks) && weak_wkbks.weakref_alive?
    weak_xl = WeakRef.new(@ole_excel)
  else
    weak_xl = nil
  end
  @ole_excel = nil
  GC.start
  sleep 0.1
  if finishing_living_excel
  #  if hwnd
  #    pid_puffer = ' ' * 32
   #   User32::GetWindowThreadProcessId(hwnd, pid_puffer)         
   #   pid = pid_puffer.unpack('L')[0]
   #   Process.kill('KILL', pid) rescue nil
  #  end
    @@hwnd2excel.delete(hwnd)
    weak_xl.ole_free if weak_xl.weakref_alive?
  end
  weak_xl ? 1 : 0
end

#displayalerts ⇒ Object

returns, wheter DisplayAlerts is enabled



624
625
626
 
# File 'lib/robust_excel_ole/excel.rb', line 624

def displayalerts
  @ole_excel.DisplayAlerts
end

#displayalerts=(displayalerts_value) ⇒ Object

enables DisplayAlerts in the current Excel instance



629
630
631
632
633
 
# File 'lib/robust_excel_ole/excel.rb', line 629

def displayalerts= displayalerts_value
  return if displayalerts_value.nil?
  @properties[:displayalerts] = displayalerts_value
  @ole_excel.DisplayAlerts = @properties[:displayalerts] == :if_visible ? @ole_excel.Visible : displayalerts_value
end

#each ⇒ Enumerator

Returns traversing all workbook objects.

Returns:

  • (Enumerator) —

    traversing all workbook objects



709
710
711
712
713
714
715
716
717
 
# File 'lib/robust_excel_ole/excel.rb', line 709

def each
  if block_given?
    ole_workbooks.lazy.each do |ole_workbook|
      yield workbook_class.new(ole_workbook)
    end
  else
    to_enum(:each).lazy
  end
end

#each_workbook(opts = { }) ⇒ Object

traverses all workbooks and sets options if provided



725
726
727
728
729
730
 
# File 'lib/robust_excel_ole/excel.rb', line 725

def each_workbook(opts = { })
  ole_workbooks.lazy.each do |ow|
    wb = workbook_class.new(ow, opts)
    block_given? ? (yield wb) : wb
  end
end

#each_workbook_with_index(opts = { }, offset = 0) ⇒ Object 



732
733
734
735
736
737
738
 
# File 'lib/robust_excel_ole/excel.rb', line 732

def each_workbook_with_index(opts = { }, offset = 0)
  i = offset
  ole_workbooks.each do |ow| 
    yield workbook_class.new(ow, opts), i 
    i += 1
  end
end

#focus ⇒ Object

Raises:

  • (ExcelREOError) 


742
743
744
745
746
 
# File 'lib/robust_excel_ole/excel.rb', line 742

def focus
  self.visible = true
  status = User32::SetForegroundWindow(@ole_excel.Hwnd)
  raise ExcelREOError, "could not set Excel window as foreground" if status == 0     
end

#recreate(opts = {}) ⇒ Excel

reopens a closed Excel instance options: reopen_workbooks (default: false): reopen the workbooks in the Excel instances :visible (default: false), :displayalerts (default: :if_visible), :calculation (default: false)

Parameters:

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

    the options

Options Hash (opts):

  • :reopen_workbooks (Boolean)
  • :displayalerts (Boolean)
  • :visible (Boolean)
  • :calculation (Boolean)

Returns:

  • (Excel) —

    an Excel instance



165
166
167
168
169
170
171
172
173
174
175
176
177
 
# File 'lib/robust_excel_ole/excel.rb', line 165

def recreate(opts = {})
  unless alive?
    opts = {visible: false, displayalerts: :if_visible}.merge(
           {visible: @properties[:visible], displayalerts: @properties[:displayalerts]}).merge(opts)        
    @ole_excel = WIN32OLE.new('Excel.Application')
    @hwnd = @ole_excel.Hwnd
    set_options(opts)
    if opts[:reopen_workbooks]
      workbook_class.books.each{ |book| book.open if !book.alive? && book.excel.alive? && book.excel == self }
    end
  end
  self
end

#screenupdating=(screenupdating_value) ⇒ Object

sets ScreenUpdating



636
637
638
639
 
# File 'lib/robust_excel_ole/excel.rb', line 636

def screenupdating= screenupdating_value
  return if screenupdating_value.nil?
  @ole_excel.ScreenUpdating = @properties[:screenupdating] = screenupdating_value
end

#set_options(options) ⇒ Object

set options in this Excel instance



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

def set_options(options)      
  @properties ||= { }
  PROPERTIES.each do |property|
    method = (property.to_s + '=').to_sym
    send(method, options[property]) 
  end
end

#visible ⇒ Object

returns, whether the current Excel instance is visible



612
613
614
 
# File 'lib/robust_excel_ole/excel.rb', line 612

def visible
  @ole_excel.Visible
end

#visible=(visible_value) ⇒ Object

makes the current Excel instance visible or invisible



617
618
619
620
621
 
# File 'lib/robust_excel_ole/excel.rb', line 617

def visible= visible_value
  return if visible_value.nil?
  @ole_excel.Visible = @properties[:visible] = visible_value
  @ole_excel.DisplayAlerts = @properties[:visible] if @properties[:displayalerts] == :if_visible
end

#with_calculation(calculation_mode) ⇒ Object

sets calculation mode in a block



686
687
688
689
690
691
692
693
694
695
 
# File 'lib/robust_excel_ole/excel.rb', line 686

def with_calculation(calculation_mode)
  return unless calculation_mode
  old_calculation_mode = @ole_excel.Calculation
  begin
    self.calculation = calculation_mode
    yield self
  ensure
    @ole_excel.Calculation = old_calculation_mode if @ole_excel.Calculation.is_a?(Integer)
  end
end

#with_displayalerts(displayalerts_value) ⇒ Object

sets DisplayAlerts in a block



601
602
603
604
605
606
607
608
609
 
# File 'lib/robust_excel_ole/excel.rb', line 601

def with_displayalerts displayalerts_value
  old_displayalerts = @properties[:displayalerts]
  self.displayalerts = displayalerts_value
  begin
    yield self
  ensure
    self.displayalerts = old_displayalerts if alive?
  end
end

#workbooks ⇒ Array

Returns all workbook objects.

Returns:

  • (Array) —

    all workbook objects



720
721
722
 
# File 'lib/robust_excel_ole/excel.rb', line 720

def workbooks
  to_a
end