Class: Cosmos::ExcelSpreadsheet

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

Overview

Open an Excel spreadsheet and build an easily manipulated spreadsheet in ruby

Defined Under Namespace

Classes: ExcelWorksheet

Instance Attribute Summary collapse

Instance Method Summary collapse

Constructor Details

#initialize(filename, archive: nil) ⇒ ExcelSpreadsheet

Returns a new instance of ExcelSpreadsheet.

Parameters:

  • filename (String) —

    Name of the Excel file to open

  • archive (true|String) (defaults to: nil) —

    If true, create an archive file in the default system LOGS directory. If an absolute path, create the archive file in the specified path.



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
99
100
101
102
103
104
105
 
# File 'lib/cosmos/win32/excel.rb', line 71

def initialize(filename, archive: nil)
  if archive
    time = Time.now.sys
    timestamp = sprintf("%04u_%02u_%02u_%02u_%02u_%02u", time.year, time.month, time.mday, time.hour, time.min, time.sec)
    # If archive is true we use the system LOGS path
    if archive == true
      archive = Cosmos::System.paths['LOGS']
    end
    archive = File.join(archive, "#{timestamp}_#{File.basename(filename)}")
    FileUtils.cp filename, archive
    File.chmod(0444, archive) # Mark read-only
  end

  begin
    excel = WIN32OLE.new('excel.application')
    excel.visible = false
    wb = excel.workbooks.open(filename)

    @worksheets = []
    @lkup = {}
    count = wb.worksheets.count
    count.times do |index|
      ws = wb.worksheets(index + 1)
      @worksheets << ExcelWorksheet.new(ws)
      @lkup[ws.name] = @worksheets[-1]
    end
  ensure
    if excel
      excel.DisplayAlerts = false
      excel.quit
    end
    excel = nil
    GC.start
  end
end

Instance Attribute Details

#worksheets ⇒ Object (readonly)

Returns the value of attribute worksheets.



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

def worksheets
  @worksheets
end

Instance Method Details

#[](index) ⇒ ExcelWorksheet

Access a worksheet by passing in the name or index

Parameters:

  • index (String|Integer) —

    Name of the worksheet or index

Returns:



116
117
118
119
120
121
122
 
# File 'lib/cosmos/win32/excel.rb', line 116

def [](index)
  if index.is_a? String
    @lkup[index]
  else
    @worksheets[index]
  end
end

#keys ⇒ Array<String>

Returns Array of all the worksheet names.

Returns:



108
109
110
 
# File 'lib/cosmos/win32/excel.rb', line 108

def keys
  @lkup.keys
end