Class: WinExcelDb::Handler

Inherits:
Object
  • Object
show all
Defined in:
lib/win_excel_db/handler.rb

Class Method Summary collapse

Instance Method Summary collapse

Constructor Details

#initialize(path) ⇒ Handler

Returns a new instance of Handler.



7
8
9
10
11
12
13
 
# File 'lib/win_excel_db/handler.rb', line 7

def initialize(path)
  @path = path
  @excel = WIN32OLE.new('Excel.Application')
  @excel.DisplayAlerts = false
  @excel.Visible = true
  @workbook = @excel.Workbooks.Open(@path)
end

Class Method Details

.kill_orphaned_instances ⇒ Object

Kills orphaned Excel automation processes left over from previous runs. Uses WMI to find excel.exe instances started with the /automation flag.



59
60
61
62
63
64
65
 
# File 'lib/win_excel_db/handler.rb', line 59

def self.kill_orphaned_instances
  wmi = WIN32OLE.connect('winmgmts://')
  processes = wmi.ExecQuery("select * from win32_process where commandline like '%excel.exe\"% /automation %'")
  processes.each do |process|
    Process.kill('KILL', process.ProcessID)
  end
end

Instance Method Details

#close ⇒ Object 



38
39
40
41
42
 
# File 'lib/win_excel_db/handler.rb', line 38

def close
  @workbook.Saved = true
  @workbook.Close
  @excel.Quit
end

#create_row_sheet(sheet_name, headers) ⇒ Object 



24
25
26
27
28
29
30
31
32
33
34
35
 
# File 'lib/win_excel_db/handler.rb', line 24

def create_row_sheet(sheet_name, headers)
  worksheet = @workbook.Worksheets.Add
  worksheet.ShowAllData if worksheet.FilterMode
  worksheet.Name=sheet_name
  count=@workbook.Worksheets.Count
  worksheet.Move(nil, @workbook.Worksheets(count))
  headers.uniq! #deletes duplicated entries in headersarray
  headers.each_with_index do |header, n|
    worksheet.Cells(1, n+1).Value=header
  end
  return RowSheet.new(worksheet, self)
end

#get_row_sheet(sheet_name) ⇒ Object 



16
17
18
19
20
21
22
 
# File 'lib/win_excel_db/handler.rb', line 16

def get_row_sheet(sheet_name)
  worksheet = @workbook.Worksheets(sheet_name)
  worksheet.Activate # only for active sheet Filtermode is evaluated correctly
  worksheet.ShowAllData if (worksheet.FilterMode || worksheet.AutoFilterMode)

  return RowSheet.new(worksheet, self)
end

#interactive(bool) ⇒ Object 



52
53
54
55
 
# File 'lib/win_excel_db/handler.rb', line 52

def interactive(bool)
  @excel.Interactive = bool
  @excel.ScreenUpdating = bool
end

#save ⇒ Object 



48
49
50
 
# File 'lib/win_excel_db/handler.rb', line 48

def save
  @workbook.Save
end

#save_as(path) ⇒ Object 



44
45
46
 
# File 'lib/win_excel_db/handler.rb', line 44

def save_as(path)
  @workbook.SaveAs(path)
end