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 



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

def initialize(options = {}) end

Dynamic Method Handling

This class handles dynamic methods through the method_missing method

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



760
761
762
763
764
765
766
767
768
769
770
771
772
773
774
775
776
777
778
779
 
# File 'lib/robust_excel_ole/excel.rb', line 760

def method_missing(name, *args) 
  if 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 => msg
        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
  else
    super
  end
end

Instance Attribute Details

#ole_excel ⇒ Object (readonly)

Returns the value of attribute ole_excel



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

def ole_excel
  @ole_excel
end

#properties ⇒ Object (readonly)

Returns the value of attribute properties



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

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

Options Hash (options):

  • :if_unsaved (Symbol) —

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

  • block (Proc) 


237
238
239
240
241
242
243
244
245
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
 
# File 'lib/robust_excel_ole/excel.rb', line 237

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 = $!
        #trace "error when finishing #{$!}"
        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 = begin
              new(WIN32OLE.connect('Excel.Application'))
            rescue
              nil
            end
    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

Options Hash (options):

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


35
36
37
 
# File 'lib/robust_excel_ole/excel.rb', line 35

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

Options Hash (options):

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


46
47
48
 
# File 'lib/robust_excel_ole/excel.rb', line 46

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

.excels_number ⇒ Object 



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

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

.init ⇒ Object 



363
364
365
 
# File 'lib/robust_excel_ole/excel.rb', line 363

def self.init
  @@hwnd2excel = {}
end

.kill_all ⇒ Integer

kill all Excel instances



369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
 
# File 'lib/robust_excel_ole/excel.rb', line 369

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

returns all Excel objects for all Excel instances opened with RobustExcelOle



461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
 
# File 'lib/robust_excel_ole/excel.rb', line 461

def self.known_excel_instances
  pid2excel = {}
  @@hwnd2excel.each do |hwnd,wr_excel|
    next unless wr_excel.weakref_alive?

    excel = wr_excel.__getobj__
    process_id = Win32API.new('user32', 'GetWindowThreadProcessId', %w[I P], 'I')
    pid_puffer = ' ' * 32
    process_id.call(hwnd, pid_puffer)
    pid = pid_puffer.unpack('L')[0]
    pid2excel[pid] = excel
  end
  processes = WIN32OLE.connect('winmgmts:\\\\.').InstancesOf('win32_process')
  processes.select { |p| Excel.new(pid2excel[p.processid]) if p.name == 'EXCEL.EXE' && pid2excel.include?(p.processid) }
  result = []
  processes.each do |p|
    next unless p.name == 'EXCEL.EXE'

    if pid2excel.include?(p.processid)
      excel = pid2excel[p.processid]
      result << excel
    end
    # how to connect to an (interactively opened) Excel instance and get a WIN32OLE object?
    # after that, lift it to an Excel object
  end
  result
end

.known_excels_number ⇒ Object 



390
391
392
 
# File 'lib/robust_excel_ole/excel.rb', line 390

def self.known_excels_number
  @@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)

Options Hash (options):

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


67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
 
# File 'lib/robust_excel_ole/excel.rb', line 67

def self.new(win32ole_excel = nil, options = {})
  if win32ole_excel.is_a? Hash
    options = win32ole_excel
    win32ole_excel = nil
  end
  ole_xl = win32ole_excel unless win32ole_excel.nil?
  options = { :reuse => true }.merge(options)
  if options[:reuse] == true && ole_xl.nil?
    ole_xl = current_ole_excel
  end
  connected = (not ole_xl.nil?) && win32ole_excel.nil?
  ole_xl ||= WIN32OLE.new('Excel.Application')
  hwnd = ole_xl.HWnd
  stored = hwnd2excel(hwnd)
  if stored && stored.alive?
    result = stored
  else
    result = super(options)
    result.instance_variable_set(:@ole_excel, ole_xl)
    WIN32OLE.const_load(ole_xl, RobustExcelOle) unless RobustExcelOle.const_defined?(:CONSTANTS)
    @@hwnd2excel[hwnd] = WeakRef.new(result)
  end

  begin
    reused = options[:reuse] && stored && stored.alive? 
    unless reused || connected
      options = { :displayalerts => :if_visible, :visible => false, :screenupdating => true }.merge(options)
    end
    result.set_options(options)        
  end
  result
end

Instance Method Details

#==(other_excel) ⇒ Object

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



511
512
513
 
# File 'lib/robust_excel_ole/excel.rb', line 511

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

#[](name) ⇒ Object

returns the value of a range



696
697
698
 
# File 'lib/robust_excel_ole/excel.rb', line 696

def [] name
  namevalue_glob(name)
end

#[]=(name, value) ⇒ Object

sets the value of a range



703
704
705
 
# File 'lib/robust_excel_ole/excel.rb', line 703

def []=(name, value)
  set_namevalue_glob(name, value, :color => 42)
end

#alive? ⇒ Boolean

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



516
517
518
519
520
521
522
 
# File 'lib/robust_excel_ole/excel.rb', line 516

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



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

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)
  if 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
      saved = []
      @ole_excel.Workbooks.each { |w| saved << w.Saved }
      @ole_excel.CalculateBeforeSave = false
      @ole_excel.Calculation =
        calculation_mode == :automatic ? XlCalculationAutomatic : XlCalculationManual
      saved = []
      @ole_excel.Workbooks.each { |w| saved << w.Saved }
    end
    #(1..@ole_excel.Workbooks.Count).each { |i| @ole_excel.Workbooks(i).Saved = true if saved[i - 1] }
  end
end

#Calculation=(calculation_vba_mode) ⇒ Object

VBA method overwritten



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

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

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



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
337
338
339
 
# File 'lib/robust_excel_ole/excel.rb', line 296

def close(options = { :if_unsaved => :raise })
  finishing_living_excel = alive?
  if finishing_living_excel
    hwnd = (begin
              @ole_excel.HWnd
            rescue
              nil
            end)
    close_workbooks(:if_unsaved => options[:if_unsaved])
    @ole_excel.Quit
    if false && defined?(weak_wkbks) && weak_wkbks.weakref_alive?
      weak_wkbks.ole_free
    end
    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
      process_id = Win32API.new('user32', 'GetWindowThreadProcessId', %w[I P], 'I')
      pid_puffer = ' ' * 32
      process_id.call(hwnd, pid_puffer)
      pid = pid_puffer.unpack('L')[0]
      begin
        Process.kill('KILL', pid)
      rescue
        # trace "kill_error: #{$!}"
      end
    end
    @@hwnd2excel.delete(hwnd)
    if weak_xl.weakref_alive?
      # if WIN32OLE.ole_reference_count(weak_xlapp) > 0
      begin
        weak_xl.ole_free
      rescue
        # trace "weakref_probl_olefree"
      end
    end
  end
  weak_xl ? 1 : 0
end

#displayalerts=(displayalerts_value) ⇒ Object

enables DisplayAlerts in the current Excel instance



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

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_workbook(opts = { }) ⇒ Object

traverses over all workbooks and sets options if provided



669
670
671
672
673
674
 
# File 'lib/robust_excel_ole/excel.rb', line 669

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

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



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

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 



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

def focus
  self.visible = true
  # if not Windows10 then
  Win32API.new('user32','SetForegroundWindow','I','I').call(@ole_excel.Hwnd)
  # else
  # Win32API.new("user32","SetForegroundWindow","","I").call
  # end
end

#for_all_workbooks(options) ⇒ Object

set options in all workbooks



659
660
661
 
# File 'lib/robust_excel_ole/excel.rb', line 659

def for_all_workbooks(options)
  each_workbook(options)
end

#for_this_instance(options) ⇒ Object

set options in this Excel instance



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

def for_this_instance(options)
  set_options(options)
  #self.class.new(@ole_excel, options)
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)

Options Hash (opts):

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


111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
 
# File 'lib/robust_excel_ole/excel.rb', line 111

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')
    set_options(opts)
    if opts[:reopen_workbooks]
      books = workbook_class.books
      books.each do |book|
        book.reopen if !book.alive? && book.excel.alive? && book.excel == self
      end
    end
  end
  self
end

#screenupdating=(screenupdating_value) ⇒ Object

sets ScreenUpdating



571
572
573
574
 
# File 'lib/robust_excel_ole/excel.rb', line 571

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

#set_options(options) ⇒ Object

def set_options(options)

for_this_instance(options)

end



650
651
652
653
654
655
656
 
# File 'lib/robust_excel_ole/excel.rb', line 650

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

#visible=(visible_value) ⇒ Object

makes the current Excel instance visible or invisible



557
558
559
560
561
 
# File 'lib/robust_excel_ole/excel.rb', line 557

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



628
629
630
631
632
633
634
635
636
637
638
 
# File 'lib/robust_excel_ole/excel.rb', line 628

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



546
547
548
549
550
551
552
553
554
 
# File 'lib/robust_excel_ole/excel.rb', line 546

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



663
664
665
666
 
# File 'lib/robust_excel_ole/excel.rb', line 663

def workbooks
  #ole_workbooks.map {|ole_workbook| ole_workbook.to_reo }
  ole_workbooks.map {|ole_workbook| workbook_class.new(ole_workbook) }
end