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.



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

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) 


693
694
695
696
697
698
699
700
701
702
703
704
705
706
707
708
709
 
# File 'lib/robust_excel_ole/excel.rb', line 693

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

#ole_excel ⇒ Object (readonly)

Returns the value of attribute ole_excel



21
22
23
 
# File 'lib/robust_excel_ole/excel.rb', line 21

def ole_excel
  @ole_excel
end

#properties ⇒ Object (readonly)

Returns the value of attribute properties



22
23
24
 
# File 'lib/robust_excel_ole/excel.rb', line 22

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



229
230
231
232
233
234
235
236
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
 
# File 'lib/robust_excel_ole/excel.rb', line 229

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



38
39
40
 
# File 'lib/robust_excel_ole/excel.rb', line 38

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



49
50
51
 
# File 'lib/robust_excel_ole/excel.rb', line 49

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

.init ⇒ Object 



331
332
333
 
# File 'lib/robust_excel_ole/excel.rb', line 331

def self.init
  @@hwnd2excel = {}
end

.instance_count ⇒ Object 



353
354
355
 
# File 'lib/robust_excel_ole/excel.rb', line 353

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



337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
 
# File 'lib/robust_excel_ole/excel.rb', line 337

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



357
358
359
 
# File 'lib/robust_excel_ole/excel.rb', line 357

def self.known_instance_count
  @@hwnd2excel.size
end

.known_running_instance ⇒ Object

returns a running Excel instance opened with RobustExcelOle



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

def self.known_running_instance     
  self.known_running_instances.first
end

.known_running_instances ⇒ Enumerator

Returns known running Excel instances.

Returns:

  • (Enumerator) —

    known running Excel instances



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

def self.known_running_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.map{ |p| pid2excel[p.ProcessId] if p.Name == 'EXCEL.EXE'}.compact.lazy.each
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



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

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 = 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
  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



466
467
468
 
# File 'lib/robust_excel_ole/excel.rb', line 466

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) 


471
472
473
474
475
476
477
 
# File 'lib/robust_excel_ole/excel.rb', line 471

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

#Calculation=(calculation_vba_mode) ⇒ Object

VBA method overwritten



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

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

#calculation=(calculation_mode) ⇒ Object

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



529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
 
# File 'lib/robust_excel_ole/excel.rb', line 529

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

#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



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

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
      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]
      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=(displayalerts_value) ⇒ Object

enables DisplayAlerts in the current Excel instance



515
516
517
518
519
 
# File 'lib/robust_excel_ole/excel.rb', line 515

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



595
596
597
598
599
600
601
602
603
 
# File 'lib/robust_excel_ole/excel.rb', line 595

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



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

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 



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

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 



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

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

#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



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

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]
      workbook_class.books.each{ |book| book.reopen if !book.alive? && book.excel.alive? && book.excel == self }
    end
  end
  self
end

#screenupdating=(screenupdating_value) ⇒ Object

sets ScreenUpdating



522
523
524
525
 
# File 'lib/robust_excel_ole/excel.rb', line 522

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



584
585
586
587
588
589
590
 
# File 'lib/robust_excel_ole/excel.rb', line 584

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

#visible=(visible_value) ⇒ Object

makes the current Excel instance visible or invisible



508
509
510
511
512
 
# File 'lib/robust_excel_ole/excel.rb', line 508

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



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

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



497
498
499
500
501
502
503
504
505
 
# File 'lib/robust_excel_ole/excel.rb', line 497

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



606
607
608
 
# File 'lib/robust_excel_ole/excel.rb', line 606

def workbooks
  to_a
end