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.



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

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) 


819
820
821
822
823
824
825
826
827
828
829
830
831
832
833
834
835
 
# File 'lib/robust_excel_ole/excel.rb', line 819

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



83
84
85
 
# File 'lib/robust_excel_ole/excel.rb', line 83

def hwnd
  @hwnd
end

#ole_excel ⇒ Object (readonly)

Returns the value of attribute ole_excel



80
81
82
 
# File 'lib/robust_excel_ole/excel.rb', line 80

def ole_excel
  @ole_excel
end

#properties ⇒ Object (readonly)

Returns the value of attribute properties



81
82
83
 
# File 'lib/robust_excel_ole/excel.rb', line 81

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



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

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



98
99
100
 
# File 'lib/robust_excel_ole/excel.rb', line 98

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



109
110
111
 
# File 'lib/robust_excel_ole/excel.rb', line 109

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

.init ⇒ Object 



396
397
398
 
# File 'lib/robust_excel_ole/excel.rb', line 396

def self.init
  @@hwnd2excel = {}
end

.instance_count ⇒ Object 



418
419
420
 
# File 'lib/robust_excel_ole/excel.rb', line 418

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



402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
 
# File 'lib/robust_excel_ole/excel.rb', line 402

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 



422
423
424
 
# File 'lib/robust_excel_ole/excel.rb', line 422

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



130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
 
# File 'lib/robust_excel_ole/excel.rb', line 130

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



566
567
568
 
# File 'lib/robust_excel_ole/excel.rb', line 566

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) 


571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
 
# File 'lib/robust_excel_ole/excel.rb', line 571

def alive?
  begin
    msg = 0x2008 
    wparam = 0
    lparam = 0
    flags = 0x0000 # 0x0002
    duration = 5000
    lpdw_result_puffer = ' ' * 32
    status = User32::SendMessageTimeoutA(hwnd, msg, wparam, lparam, flags, duration, lpdw_result_puffer)
    result = lpdw_result_puffer.unpack('L')[0]
    status != 0
  rescue NoMethodError => e
    #trace "#{e}"
    begin
      @ole_excel.Name
      true
    rescue
      # trace $!.message
      false
    end
  end
end

#calculation=(calculation_mode) ⇒ Object

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



654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
670
671
672
673
674
675
676
677
678
679
680
681
682
683
 
# File 'lib/robust_excel_ole/excel.rb', line 654

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



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

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



343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
 
# File 'lib/robust_excel_ole/excel.rb', line 343

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
      begin
        pid_puffer = ' ' * 32
        User32::GetWindowThreadProcessId(hwnd, pid_puffer)         
        pid = pid_puffer.unpack('L')[0]
        Process.kill('KILL', pid) rescue nil
      rescue NoMethodError => e
        # trace "#{e}"
      end
    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



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

def displayalerts
  @ole_excel.DisplayAlerts
end

#displayalerts=(displayalerts_value) ⇒ Object

enables DisplayAlerts in the current Excel instance



640
641
642
643
644
 
# File 'lib/robust_excel_ole/excel.rb', line 640

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



720
721
722
723
724
725
726
727
728
 
# File 'lib/robust_excel_ole/excel.rb', line 720

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



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

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 



743
744
745
746
747
748
749
 
# File 'lib/robust_excel_ole/excel.rb', line 743

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 



753
754
755
756
757
758
759
760
761
 
# File 'lib/robust_excel_ole/excel.rb', line 753

def focus
  self.visible = true
  begin
    status = User32::SetForegroundWindow(@ole_excel.Hwnd)
    raise ExcelREOError, "could not set Excel window as foreground" if status == 0 
  rescue NoMethodError => e
    raise ExcelREOError, "could not set Excel window as foreground because user32.dll not found"
  end
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



171
172
173
174
175
176
177
178
179
180
181
182
183
 
# File 'lib/robust_excel_ole/excel.rb', line 171

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



647
648
649
650
 
# File 'lib/robust_excel_ole/excel.rb', line 647

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



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

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



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

def visible
  @ole_excel.Visible
end

#visible=(visible_value) ⇒ Object

makes the current Excel instance visible or invisible



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

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



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

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



612
613
614
615
616
617
618
619
620
 
# File 'lib/robust_excel_ole/excel.rb', line 612

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



731
732
733
 
# File 'lib/robust_excel_ole/excel.rb', line 731

def workbooks
  to_a
end