Class: RobustExcelOle::Excel

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

Constant Summary collapse

@@hwnd2excel = 
{}

Class Method Summary collapse

Instance Method Summary collapse

Constructor Details

#initialize(options = {}) ⇒ Excel

:nodoc:



65
66
 
# File 'lib/robust_excel_ole/excel.rb', line 65

def initialize(options= {}) # :nodoc:

end

Dynamic Method Handling

This class handles dynamic methods through the method_missing method

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



216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
 
# File 'lib/robust_excel_ole/excel.rb', line 216

def method_missing(name, *args) 
  if name.to_s[0,1] =~ /[A-Z]/ 
    begin
      @excel.send(name, *args)
    rescue WIN32OLERuntimeError => msg
      if msg.message =~ /unknown property or method/
        raise VBAMethodMissingError, "unknown VBA property or method #{name}"
      else 
        raise msg
      end
    end
  else  
    super 
  end
end

Class Method Details

.close_all ⇒ Object

closes all Excel instances



10
11
12
13
14
15
16
17
 
# File 'lib/robust_excel_ole/excel.rb', line 10

def self.close_all
  while current_excel do
    close_one_excel
    GC.start
    sleep 0.3
    #free_all_ole_objects

  end
end

.create ⇒ Object

creates a new Excel instance



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

def self.create
  new(:reuse => false)
end

.current ⇒ Object

uses the current Excel instance (connects), if such a running Excel instance exists creates a new one, otherwise



26
27
28
 
# File 'lib/robust_excel_ole/excel.rb', line 26

def self.current
  new(:reuse => true)
end

.hwnd2excel(hwnd) ⇒ Object 



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

def self.hwnd2excel(hwnd)
  @@hwnd2excel[hwnd]
end

.new(options = {}) ⇒ Object

returns an Excel instance

options:

:reuse          use an already running Excel instance (default: true)
:displayalerts  allow display alerts in Excel         (default: false)
:visible        make visible in Excel                 (default: false)
if :reuse => true, then DisplayAlerts and Visible are set only if they are given



36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
 
# File 'lib/robust_excel_ole/excel.rb', line 36

def self.new(options= {})
  options = {:reuse => true}.merge(options)
  if options[:reuse] then
    excel = current_excel
  end
  if not (excel)
    excel = WIN32OLE.new('Excel.Application')
    options = {
      :displayalerts => false,
      :visible => false,
    }.merge(options)
  end
  excel.DisplayAlerts = options[:displayalerts] unless options[:displayalerts].nil?
  excel.Visible = options[:visible] unless options[:visible].nil?

  hwnd = excel.HWnd
  stored = hwnd2excel(hwnd)

  if stored 
    result = stored
  else
    result = super(options)
    result.instance_variable_set(:@excel, excel)
    WIN32OLE.const_load(excel, RobustExcelOle) unless RobustExcelOle.const_defined?(:CONSTANTS)
    @@hwnd2excel[hwnd] = result        
  end
  result
end

Instance Method Details

#==(other_excel) ⇒ Object

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



86
87
88
 
# File 'lib/robust_excel_ole/excel.rb', line 86

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

#alive? ⇒ Boolean

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

Returns:

  • (Boolean) 


91
92
93
94
95
96
97
 
# File 'lib/robust_excel_ole/excel.rb', line 91

def alive?
  @excel.Name
  true
rescue
  puts $!.message
  false
end

#displayalerts ⇒ Object

return if in the current Excel instance DisplayAlerts is enabled



116
117
118
 
# File 'lib/robust_excel_ole/excel.rb', line 116

def displayalerts 
  @excel.DisplayAlerts
end

#displayalerts=(displayalerts_value) ⇒ Object

enable DisplayAlerts in the current Excel instance



111
112
113
 
# File 'lib/robust_excel_ole/excel.rb', line 111

def displayalerts= displayalerts_value
  @excel.DisplayAlerts = displayalerts_value
end

#generate_workbook(file_name) ⇒ Object

generate, save and close an empty workbook



69
70
71
72
73
74
75
 
# File 'lib/robust_excel_ole/excel.rb', line 69

def generate_workbook file_name                  
  self.Workbooks.Add                           
  empty_workbook = self.Workbooks.Item(1)          
  filename = RobustExcelOle::absolute_path(file_name)
  empty_workbook.SaveAs(filename.gsub("/","\\"))  
  empty_workbook                               
end

#hwnd ⇒ Object 



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

def hwnd
  self.Hwnd
end

#visible ⇒ Object

return if the current Excel instance is visible



126
127
128
 
# File 'lib/robust_excel_ole/excel.rb', line 126

def visible 
  @excel.Visible
end

#visible=(visible_value) ⇒ Object

make the current Excel instance visible or invisible



121
122
123
 
# File 'lib/robust_excel_ole/excel.rb', line 121

def visible= visible_value
  @excel.Visible = visible_value
end

#with_displayalerts(displayalerts_value) ⇒ Object

set DisplayAlerts in a block



100
101
102
103
104
105
106
107
108
 
# File 'lib/robust_excel_ole/excel.rb', line 100

def with_displayalerts displayalerts_value
  old_displayalerts = @excel.DisplayAlerts
  @excel.DisplayAlerts = displayalerts_value
  begin
     yield self
  ensure
    @excel.DisplayAlerts = old_displayalerts
  end
end