Class: WinExcel::ExcelFile

Inherits:
Object show all
Defined in:
lib/winexcel/excel_file.rb,
lib/winexcel/excel_file/other_methods.rb,
lib/winexcel/excel_file/common_methods.rb,
lib/winexcel/excel_file/write_2D_array.rb

Defined Under Namespace

Modules: Range Classes: ExcelCell, ExcelConst

Constant Summary collapse

XlsAutomationSecurity =

msoAutomationSecurityForceDisable

3
XlsDisplayAlerts = 
false
XlsVisible = 
false
XlsScreenUpdating = 
false
XlsInteractive = 
false
@@automationInstance = 
nil

Instance Attribute Summary collapse

Class Method Summary collapse

Instance Method Summary collapse

Constructor Details

#initialize(file, debug = false, template = nil) ⇒ ExcelFile

initilize creates an ExcelFile instance for a given .xls file. If the File is already open in excel, data is read from the open file and left open after the call to the close/finalize method. If the file is not open, It will be opened in the background and closed when the close/finalize method is called.

For examples look at the end of the file as well as Cucumber/RSpec files

Information for developers:

It is helpful to use Win32OLE tracking tool like "oakland ActiveX Inspector"
to clearly see what Excel instances are beeing created



107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
 
# File 'lib/winexcel/excel_file.rb', line 107

def initialize(file, debug = false, template = nil)
  @excelOpen=false
  @connectedToOpenWorkBook=false
  @log = Logger.new(STDERR)
  @log.level = Logger::WARN
  @log.level = Logger::DEBUG if debug

  if not @@automationInstance
    ExcelFile.killExcelAutomationProcesses
  end

  connectToOpenWorkbook = lambda {
      # open existing file in a traditional way connecting to running Excel instance
    workbookObj = WIN32OLE.connect(file)
    @excel = workbookObj.Application
    @excelOpen = true
    basename = File.basename(file)
    @workbook = @excel.Workbooks(basename)
    @connectedToOpenWorkBook=true
    @log.info(self.class) { "Attached to open Workbook: #{basename}" }
  }

  openAutomationInstance = lambda {
    if not @@automationInstance
      @@automationInstance = WIN32OLE::new(Excel::APPLICATION_NAME)
      setSettings @@automationInstance
      @log.info(self.class) { "Excel Automation Instance Created" }
    end
    @excel = @@automationInstance
  }

  makeFileFromTemplateAndOpen = lambda {
      # copy the template on place of the file (that's been just moved)
    openAutomationInstance.call
    @workbook = @@automationInstance.Workbooks.Add(template)
    @workbook.SaveAs(file.gsub("/", "\\"), Excel::XlFileFormat::XlWorkbookNormal)
    @log.info(self.class) { "File: '#{file}' created from template '#{template}'." }
  }

  openFileInAutomationInstance = lambda {
      # create isolated Excel and open that workbook/file inside of it
    openAutomationInstance.call
    @excelOpen = true
    begin
      basename = File.basename(file)
      @workbook = @excel.Workbooks(basename)
      @log.info(self.class) { "Attached to open Workbook: '#{basename}'." }
    rescue
      if File.exists?(file)
        @workbook = @excel.Workbooks.Open(file)
        @log.info(self.class) { "File: '#{file}' opened." }
      else
        raise "File: '#{file}' does not exist."
      end
    end
  }

  # check in ROT if workbook is open if so use WIN32OLE::connect(file) otherwise open in isolation
  rot = WIN32OLE::RunningObjectTable.new
  if rot.nil? then
    throw 'Cannot access WIN32OLE::RunningObjectTable'
  end
  begin
    isFileRunning = rot.is_running?(file)
    if isFileRunning and template
      connectToOpenWorkbook.call

      # save file, close it and move to backup
      save
      sleep 1
      @workbook.close
      while rot.is_running?(file) do
        sleep 1
      end
      @excel.quit
      @excel = nil
      GC.start
      FileUtils.moveFileToBackupDir file
      @log.info(self.class) { "File: #{file} successfully saved, closed and moved to BACKUP folder." }

      makeFileFromTemplateAndOpen.call
    elsif isFileRunning and not template
      connectToOpenWorkbook.call
    elsif not isFileRunning and template
      makeFileFromTemplateAndOpen.call
    elsif not isFileRunning and not template
      openFileInAutomationInstance.call
    end
  rescue
    @log.error(self.class) { "Error attaching: wasFileOpenedByUser: #{@connectedToOpenWorkBook}, file: #{file}, error: #{$!}" }
    raise
  end

  @records = {}

  if Excel::LOAD_ALL_CONSTANTS
    # Get the standard set of Excel constants
    WIN32OLE.const_load(@excel, ExcelConst) if ExcelConst.constants == []
  end
end

Instance Attribute Details

#excel ⇒ Object

Returns the value of attribute excel.



75
76
77
 
# File 'lib/winexcel/excel_file.rb', line 75

def excel
  @excel
end

#fileName ⇒ Object

Returns the value of attribute fileName.



78
79
80
 
# File 'lib/winexcel/excel_file.rb', line 78

def fileName
  @fileName
end

#records ⇒ Object

Returns the value of attribute records.



77
78
79
 
# File 'lib/winexcel/excel_file.rb', line 77

def records
  @records
end

#workbook ⇒ Object

Returns the value of attribute workbook.



76
77
78
 
# File 'lib/winexcel/excel_file.rb', line 76

def workbook
  @workbook
end

Class Method Details

.finalize ⇒ Object 



230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
 
# File 'lib/winexcel/excel_file.rb', line 230

def self.finalize()
  if not @@automationInstance.nil?
    begin
      @@automationInstance.Workbooks.each do |wb|
        begin
          wb.Close(false)
        rescue
        end
        wb = nil
      end
      GC.start
      sleep(3)
    rescue
    end
    @@automationInstance.quit
    sleep 1
    @@automationInstance = nil
    GC.start
    sleep(3)
  end
end

.killExcelAutomationProcesses ⇒ Object 



208
209
210
211
212
213
214
215
216
217
 
# File 'lib/winexcel/excel_file.rb', line 208

def self.killExcelAutomationProcesses
  begin
    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
  rescue
  end
end

.killInvisibleExcelProcesses ⇒ Object

not used do not work yet



221
222
223
224
225
226
227
228
 
# File 'lib/winexcel/excel_file.rb', line 221

def self.killInvisibleExcelProcesses
  2.times do
    excelObj = WIN32OLE::connect(Excel::APPLICATION_NAME)
    if not excelObj.Visible
      finalize(excelObj)
    end
  end
end

.toExcelCompoundRange(rangeBegin, rangeEnd) ⇒ Object 



69
70
71
 
# File 'lib/winexcel/excel_file/other_methods.rb', line 69

def self.toExcelCompoundRange(rangeBegin, rangeEnd)
  return (rangeBegin + Range::SEPARATOR + rangeEnd)
end

.toExcelRange(row, column) ⇒ Object

Given row and column number, returns Excel-formatted range e.g. given: row=5, column=4 returns: “D5”



54
55
56
57
58
59
60
61
62
63
64
65
66
67
 
# File 'lib/winexcel/excel_file/other_methods.rb', line 54

def self.toExcelRange(row, column)
  unless (row > 0 and column > 0)
    raise ArgumentError.new("Row and column should be positive numbers; given: row=#{row}, column=#{column}.")
  end

  columnName = ''
  begin
    column -= 1
    column, rest = column.divmod(Range::CHARS_IN_ALPHABET)
    columnName = (?A + rest).chr + columnName
  end while column > 0

  return columnName + row.to_s
end

Instance Method Details

#addSheet(sheetName) ⇒ Object

Adds a new worksheet to workbook



251
252
253
254
 
# File 'lib/winexcel/excel_file/other_methods.rb', line 251

def addSheet(sheetName)
  @workbook.Sheets.Add
  @workbook.ActiveSheet.Name = sheetName
end

#append2DArray(data, sheet = nil) ⇒ Object

appends the 2D Array data starting at the first empty row on the specified sheet.



367
368
369
370
371
372
373
374
375
376
377
 
# File 'lib/winexcel/excel_file/common_methods.rb', line 367

def append2DArray(data, sheet = nil)
  @log.info("append2DArray(data='...', sheet = '#{sheet})'")
  worksheet = getWorksheet(sheet)
  throw "append2DArray method cannot find '#{sheet}' sheet" if not worksheet
  row_min = worksheet.UsedRange.Row
  row_max = row_min + worksheet.UsedRange.Rows.Count - 1
  col_min = worksheet.UsedRange.Column
  col_max = col_min + worksheet.UsedRange.Columns.Count - 1
  firstEmptyRow = row_max + 1
  write2DArray(data, "A#{firstEmptyRow}", sheet)
end

#append2DArrayAtOnce(data, sheet = nil) ⇒ Object

appends the 2D Array data starting at the first empty row on the specified sheet.



354
355
356
357
358
359
360
361
362
363
364
 
# File 'lib/winexcel/excel_file/common_methods.rb', line 354

def append2DArrayAtOnce(data, sheet = nil)
  @log.info("append2DArray(data='...', sheet = '#{sheet})'")
  worksheet = getWorksheet(sheet)
  throw "append2DArray method cannot find '#{sheet}' sheet" if not worksheet
  row_min = worksheet.UsedRange.Row
  row_max = row_min + worksheet.UsedRange.Rows.Count - 1
  col_min = worksheet.UsedRange.Column
  col_max = col_min + worksheet.UsedRange.Columns.Count - 1
  firstEmptyRow = row_max + 1
  write2DArrayAtOnce(data, "A#{firstEmptyRow}", sheet)
end

#appendArrayHash(data, sheet = nil) ⇒ Object

appends the 2D Array data starting at the first empty row on the specified sheet. the keys are used as column headers.



296
297
298
299
300
301
 
# File 'lib/winexcel/excel_file/other_methods.rb', line 296

def appendArrayHash(data, sheet = nil)
  @log.info(self.class) { "appendArrayHash(data='...', sheet = '#{sheet})'" }
  data = convertArrayHashTo2DArray(data)
  data.slice!(0)
  append2DArray(data, sheet)
end

#appendWorksheet(name, visible = true) ⇒ Object 



346
347
348
349
350
351
 
# File 'lib/winexcel/excel_file/common_methods.rb', line 346

def appendWorksheet(name, visible = true)
  sheet = @workbook.Worksheets.Add('After' => @workbook.Worksheets(@workbook.Worksheets.Count))
  sheet.Name = name
  sheet.Cells.NumberFormat = "@"
  sheet.Visible = 0 if not visible
end

#close(forceClose = false) ⇒ Object 



301
302
303
 
# File 'lib/winexcel/excel_file/common_methods.rb', line 301

def close(forceClose=false)
  closeWorkbookOnly(forceClose)
end

#closeWorkbookOnly(forceClose = false) ⇒ Object

Closes Workbook if it was opened.



294
295
296
297
298
299
 
# File 'lib/winexcel/excel_file/common_methods.rb', line 294

def closeWorkbookOnly(forceClose=false)
  if forceClose or not @connectedToOpenWorkBook
    @workbook.Close(false) # false for not to save changes
    @log.info(self.class) { "Workbook Closed" }
  end
end

#convert2DArrayToArrayHash(myArray, columnHeaders = true) ⇒ Object

myArray should either have column or row headers to use as keys. columnHeader=false implies that there are row headers.



217
218
219
220
221
222
223
224
225
226
227
228
 
# File 'lib/winexcel/excel_file/other_methods.rb', line 217

def convert2DArrayToArrayHash(myArray, columnHeaders=true)
  myArray = myArray.transpose unless columnHeaders
  arrayHash=[]
  (1..myArray.length-1).each do |i|
    rowHash = Hash.new #OrderedHash.new  #
    (0..myArray[i].length-1).each do |j|
      rowHash[myArray[0][j]] = myArray[i][j]
    end
    arrayHash << rowHash
  end
  return arrayHash
end

#convertArrayHashTo2DArray(myArrayHash) ⇒ Object 



231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
 
# File 'lib/winexcel/excel_file/other_methods.rb', line 231

def convertArrayHashTo2DArray(myArrayHash)
  @log.info(self.class) { "convertArrayHashTo2DArray(myArrayHash)" }
  return [] if myArrayHash.empty?

  my2DArray = []
  #iterate through keys write out header row
  myKeys = myArrayHash[0].keys
  my2DArray << myKeys
  #write out data
  (0..myArrayHash.length-1).each do |row|
    myRow = []
    myKeys.each do |key|
      myRow << myArrayHash[row][key]
    end
    my2DArray << myRow
  end
  return my2DArray
end

#deleteSheet(sheetName = nil) ⇒ Object

Deletes a worksheet from the workbook



257
258
259
260
261
 
# File 'lib/winexcel/excel_file/other_methods.rb', line 257

def deleteSheet(sheetName = nil)
  @excel.DisplayAlerts=false
  sheet = getWorksheet(sheetName)
  sheet.delete
end

#find(expr, sheet, alphaNumOnly = true) ⇒ Object 



112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
 
# File 'lib/winexcel/excel_file/other_methods.rb', line 112

def find(expr, sheet, alphaNumOnly=true)
  if records[sheet].nil?
    records[sheet] = getVisibleUsed2DArray(sheet)
  end
  hpExpr = expr.human_proof(alphaNumOnly)
  hpExpr = Regexp.escape(hpExpr)
  hpExpr = hpExpr.gsub("\\*", '.*')
  hpExpr = /\A#{hpExpr}\Z/
  records[sheet].each_with_index do |record, ri|
    record.each_with_index do |cell, ci|
      if hpExpr.match(cell.val.human_proof(alphaNumOnly))
        return [ri, ci, cell.val]
      end
    end
  end

  return nil
end

#findDialog(expr, sheet = nil) ⇒ Object

Find an expression/text using standard excel find dialog



134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
 
# File 'lib/winexcel/excel_file/other_methods.rb', line 134

def findDialog(expr, sheet=nil)

  worksheet = getWorksheet(sheet)
  if worksheet == nil
    return nil
  end

  range = worksheet.Cells.Find('What' => expr,
                               'SearchDirection' => ExcelConst::XlNext,
                               'SearchOrder' => ExcelConst::XlByRows,
                               'LookIn' => ExcelConst::XlValues,
                               'LookAt' => ExcelConst::XlWhole)
  # Other options:
  # 	ExcelConst::XlPrevious,
  #	ExcelConst::XlByColumns,
  #	MatchCase => False,
  #	After => ActiveCell,
  return range
end

#get2DArray(myRange = nil, sheet = nil, enableCaching = false) ⇒ Object

returns a 2D Array representing the given range of Data stored in a given worksheet Note: All contiguious ranges are supported, however, only non-contigious column sellections of equal size are accepted. myRange can either be a string representing a range: “A1:C4”, a named range defined in the workbook: “SomeNamedRange”, or the text in a cell “myRangeing” a contigious table of values below it. If it is nil or not specified the CurrentRegion starting at “A1” is used.

EXAMPLE DATA:

         A                        B                      C
1        ID                   name		            nickname
2       001                Fredrick White		  fred
3       002	             Robert Green	        bob

RETURNS: Calling get2DArray(“A1:C3”) would return the following 2D array

[[ID, name, nickname],
 [001, Fredrick White, fred],
 [002, Robert Green, bob]]



83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
 
# File 'lib/winexcel/excel_file/common_methods.rb', line 83

def get2DArray(myRange=nil, sheet=nil, enableCaching=false)
  if myRange != nil and myRange.instance_of?(String)
    if myRange.match(/^[A-Za-z]+$/)
      worksheet = getWorksheet(sheet)
      row_min = worksheet.UsedRange.Row
      row_max = row_min + worksheet.UsedRange.Rows.Count - 1
      myRange = myRange + row_min.to_s + ':' + myRange + row_max.to_s
    end

    if enableCaching
      $ExcelCache ||= {}
      if myRange != nil and myRange.instance_of?(String)
        cacheKey = @workbook.Name.gsub(/\s+/, "").upcase + sheet.to_s + myRange.to_s
      end
    end
  end

  data = nil
  if enableCaching and $ExcelCache[cacheKey]
    data = $ExcelCache[cacheKey]
  else
    @log.info("get2DArray(myRange=#{myRange}, sheet = #{sheet}")

    if myRange == nil or myRange.instance_of?(String)
      myRange = getRange(myRange, sheet)
      if myRange == nil
        return nil
      end
    end
    data = []
    areas = []

    #Deal with non-contigious regions by looping through each region.
    myRange.Areas.each do |area|
      areas << area.value #get the data from each area
    end

    numRecords = myRange.Rows.Count
    (0..numRecords-1).each do |i|
      record=[]
      areas.each do |area|
        if (area.kind_of? Array)
          record.concat(area[i])
        else
          record << area
        end
      end
      #Clean up formatting
      record.collect! do |x|
        if x.is_a?(Float) and x % 1 == 0
          x.to_i.to_s
        else
          x.to_s.strip # need to_s.strip to get realword.
        end
      end
      data << record
    end

    # save it to global cache
    $ExcelCache[cacheKey] = data if enableCaching
  end

  return data.clone
end

#getAllWorksheets ⇒ Object 



244
245
246
 
# File 'lib/winexcel/excel_file/common_methods.rb', line 244

def getAllWorksheets()
  return @workbook.Worksheets
end

#getColumnRecords(myRange, sheet = nil) ⇒ Object

Returns an array of hashes representing data records stored in rows in the given myRange and sheet. myRange can either be a string representing a range: “A1:C4”, a named range defined in the workbook: “SomeNamedRange”, or the text in a cell “myRangeing” a contigious table of values below it. If it is nil or not specified the CurrentRegion starting at “A1” is used. Note: myRange should include headers, and may contain non-contigious column ranges of equal size EXAMPLE DATA:

         A                  B                           C
1        ID              001                          002
2       Name           Fredrick White		      Robert Green
3       NickName	   fred                         bob

Standard Range Example: getColumnRecords(“A1:C3”) would return the following array of hashes:

[  {'ID'=>'001', 'Name'=>'Fredrick White', 'Nickname'=>'fred'},
   {'ID'=>'002', 'Name'=>'Robert Green', 'Nickname'=>'bob'}  ]



191
192
193
 
# File 'lib/winexcel/excel_file/other_methods.rb', line 191

def getColumnRecords(myRange, sheet = nil)
  return convert2DArrayToArrayHash(get2DArray(myRange, sheet), false)
end

#getHash(myRange = nil, sheet = nil, columnHeaders = false) ⇒ Object

Returns a hash of key-value pairs where the keys are pulled from column 1 and the values are pulled form column 2 of myRange on sheet. myRange can either be a string representing a range: “A1:C4”, a named range defined in the workbook: “SomeNamedRange”, or the text in a cell “labeling” a contigious table of values below it. If it is nil or not specified the CurrentRegion starting at “A1” is used. Note: ‘:’s are striped off of each key if they exist. EXAMPLE DATA:

         A                  B
1        ID              001
2       Name:           Fredrick White
3       NickName:	   fred

Example usage: getHashFromRange(“A1:B3”) would return the following hash:

{'ID'=>'001', 'Name'=>'Fredrick White', 'Nickname'=>'fred'}



206
207
208
209
210
211
212
213
 
# File 'lib/winexcel/excel_file/other_methods.rb', line 206

def getHash(myRange = nil, sheet = nil, columnHeaders = false)
  tmpHash = convert2DArrayToArrayHash(get2DArray(myRange, sheet), columnHeaders)[0]
  newHash = CoreExt::OrderedHash.new
  tmpHash.each do |key, value|
    newHash[key.sub(/:/, '')] = value
  end
  return newHash
end

#getRange(myRange = "", sheet = nil) ⇒ Object

Searches for the first occurance of myRange on sheet and returns the address of the range representing the contigious set of cells below(xlDown) and to the right(xlRight) of myRange If sheet is not specified, the first sheet is used. myRange can either be a string representing a range: “A1:C4”, a named range defined in the workbook: “SomeNamedRange”, or the text in a cell “myRangeing” a contigious table of values below it. If it is nil or not specified the CurrentRegion starting at “A1” is used.



212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
 
# File 'lib/winexcel/excel_file/common_methods.rb', line 212

def getRange(myRange="", sheet=nil)
  @log.info(self.class) { "getRange(myRange=#{myRange}, sheet=#{sheet}" }
  worksheet = getWorksheet(sheet)
  if worksheet == nil
    return nil
  end
  #find where the data is
  if myRange.nil? or myRange == ""
    #rng=worksheet.Range("A1").CurrentRegion
    rng = worksheet.UsedRange
    rng = worksheet.Range("A1:#{rng.Address.split(':')[1]}")
  else
    begin
      #use myRange as an excel range if it is one
      rng = worksheet.Range(myRange)
    rescue WIN32OLERuntimeError #must not be a standard excel range...  look for the myRange.
      rng = worksheet.Range("A1", worksheet.UsedRange.SpecialCells(11)).Find(myRange) #xlCellTypeLastCell
      raise "getRange(myRange=#{myRange}, sheet=#{sheet}) --> Could not locate range via specified myRange." unless rng
      rng = rng.Offset(1)
      rng = worksheet.Range(rng, rng.End(-4121)) #-4121 --> xlDown
      rng = worksheet.Range(rng, rng.End(-4161)) #-4161  --> xlToRight
    end
  end

  return rng
end

#getRowRecords(myRange, sheet = nil) ⇒ Object

Returns an array of hashes representing data records stored in rows in the given myRange and sheet. myRange can either be a string representing a range: “A1:C4”, a named range defined in the workbook: “SomeNamedRange”, or the text in a cell “myRangeing” a contigious table of values below it. If it is nil or not specified the CurrentRegion starting at “A1” is used. Note: myRange should include headers, and may contain non-contigious column ranges of equal size EXAMPLE DATA:

         A                        B                      C
1        ID                   name		            nickname
2       001                Fredrick White		  fred
3       002	             Robert Green	        bob

Standard Range example: getRowRecords(“A1:C3”) would return the following array of hashes:

[  {'ID'=>'001', 'Name'=>'Fredrick White', 'Nickname'=>'fred'},
   {'ID'=>'002', 'Name'=>'Robert Green', 'Nickname'=>'bob'}  ]

Non-Contigious Range Example: getRowRecords(“A1:A3,C1:C3”) would return the following array of hashes:

[  {'ID'=>'001', 'Nickname'=>'fred'},
   {'ID'=>'002', 'Nickname'=>'bob'}  ]



173
174
175
 
# File 'lib/winexcel/excel_file/other_methods.rb', line 173

def getRowRecords(myRange, sheet = nil)
  return convert2DArrayToArrayHash(get2DArray(myRange, sheet), true)
end

#getVisibleUsed2DArray(sheet = nil, offsetTolerance = 5) ⇒ Object 



152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
 
# File 'lib/winexcel/excel_file/common_methods.rb', line 152

def getVisibleUsed2DArray(sheet=nil, offsetTolerance=5)
  worksheet = getWorksheet(sheet)
  if worksheet == nil
    return nil
  end

  offsetTolerance = 30
  contents = []
  records = []
  rowOffset = 0
  maxOccupiedColCnt = 0
  worksheet.UsedRange.Rows.each do |row|
    record = []
    colOffset = 0
    isRowEmpty = true
    row.Cells.each do |cell|
      if cell.HasFormula or cell.Rows.Hidden or cell.Columns.Hidden
        next
      end
      xlsCell = ExcelCell.new
      xlsCell.addr = cell.Address
      xlsCell.val = cell.Value.to_s.strip
      if xlsCell.val.empty? then
        colOffset = colOffset.succ
        if colOffset > offsetTolerance + maxOccupiedColCnt then
          break
        end
      else
        isRowEmpty = false
        colOffset = 0
      end

      record << xlsCell
    end

    if record.length > maxOccupiedColCnt then
      maxOccupiedColCnt = record.length
    end

    if record.empty? then
      next
    elsif isRowEmpty then
      rowOffset = rowOffset.succ
      if rowOffset > offsetTolerance then
        break
      end
    else
      rowOffset = 0
      records << record
    end
  end

  return records
end

#getWorksheet(sheet = nil) ⇒ Object 



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
 
# File 'lib/winexcel/excel_file/common_methods.rb', line 248

def getWorksheet(sheet=nil)
  if sheet.nil?
    worksheet = @workbook.Worksheets(1)
    if worksheet then
      @log.info("getWorksheet(sheet=#{sheet}) --> #{worksheet.Name}")
    end
    return worksheet
  elsif sheet.instance_of?(Regexp)
    @workbook.Worksheets.each do |s|
      if s.Name.upcase.match(sheet)
        if s.Visible == 0 then
          return nil
        end
        return s
      end
    end
  elsif sheet.instance_of?(String)
    @workbook.Worksheets.each do |s|
      if s.Name.gsub(/\s+/, '').upcase == sheet.gsub(/\s+/, '').upcase
        if s.Visible == 0 then
          return nil
        end
        return s
      end
    end
    #puts "Could not find sheet #{sheet} in #{@workbook.Name}" if @workbook.Name
  else
    return sheet
  end
end

#getWorksheetCount ⇒ Object 



240
241
242
 
# File 'lib/winexcel/excel_file/common_methods.rb', line 240

def getWorksheetCount()
  return @workbook.Worksheets.Count
end

#getWorksheets(visibleOnly = true) ⇒ Object 



279
280
281
282
283
284
285
286
287
288
289
290
 
# File 'lib/winexcel/excel_file/common_methods.rb', line 279

def getWorksheets(visibleOnly=true)
  if not visibleOnly then
    return @workbook.Worksheets
  end
  arrWorksheets = []
  @workbook.Worksheets.each do |s|
    if s.Visible != 0 then
      arrWorksheets << s
    end
  end
  return arrWorksheets
end

#protect(password = "zx") ⇒ Object

Protect method as it applies to the Worksheet object.

Protects a worksheet so that it cannot be modified.

sheet.Protect(Password, DrawingObjects, Contents, Scenarios, UserInterfaceOnly, AllowFormattingCells,
  AllowFormattingColumns, AllowFormattingRows, AllowInsertingColumns, AllowInsertingRows, AllowInsertingHyperlinks,
  AllowDeletingColumns, AllowDeletingRows, AllowSorting, AllowFiltering, AllowUsingPivotTables)



82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
 
# File 'lib/winexcel/excel_file/other_methods.rb', line 82

def protect(password="zx")
  @workbook.Worksheets.each do |s|
    s.Protect(
        'Password' => password,
        'DrawingObjects' => true,
        'Contents' => true,
        'Scenarios' => true,
        'UserInterFaceOnly' => true,
        'AllowFormattingCells' => true,
        'AllowFormattingColumns' => true,
        'AllowFormattingRows' => true,
        'AllowInsertingColumns' => false,
        'AllowInsertingRows' => false,
        'AllowInsertingHyperlinks' => false,
        'AllowDeletingColumns' => false,
        'AllowDeletingRows' => false,
        'AllowSorting' => true,
        'AllowFiltering' => true,
        'AllowUsingPivotTables' => true
    )
  end
end

#removeRowsWithSkipping(worksheet, rowsToSkip) ⇒ Object 



264
265
266
267
268
269
270
271
272
 
# File 'lib/winexcel/excel_file/other_methods.rb', line 264

def removeRowsWithSkipping(worksheet, rowsToSkip)
  ur = worksheet.UsedRange.Rows.Count
  start = rowsToSkip + 1
  if ur >= start
    myRange = "A#{start}:A#{ur}" #xlDown = -4121
    range = worksheet.Range(myRange).EntireRow
    range.Delete()
  end
end

#save ⇒ Object

Saves the current workbook.



385
386
387
388
389
390
391
392
 
# File 'lib/winexcel/excel_file/common_methods.rb', line 385

def save
  setDisplayAlerts(false)
  begin
    @workbook.Save
  rescue
    @excel.Save
  end
end

#save2DArraytoCSVFile(myArray, file) ⇒ Object

outputs a 2DArray myArray to a CSV file specified by file.



304
305
306
307
308
309
310
311
 
# File 'lib/winexcel/excel_file/other_methods.rb', line 304

def save2DArraytoCSVFile(myArray, file)
  myFile = File.open(file, 'w')
  @log.info(self.class) { "2DArraytoCSVFile(myArray=..., file=#{file})" }
  (0..myArray.length-1).each do |i|
    myFile.puts(myArray[i].join(',')) unless myArray[i].nil?
  end
  myFile.close
end

#saveAs(fileName) ⇒ Object

Saves as the current workbook.



400
401
402
 
# File 'lib/winexcel/excel_file/common_methods.rb', line 400

def saveAs(fileName)
  @workbook.SaveAs(fileName.gsub("/", "\\"))
end

#setDisplayAlerts(val = true) ⇒ Object 



380
381
382
 
# File 'lib/winexcel/excel_file/common_methods.rb', line 380

def setDisplayAlerts(val=true)
  @excel.DisplayAlerts = val
end

#setInteractive(val = true) ⇒ Object 



408
409
410
 
# File 'lib/winexcel/excel_file/common_methods.rb', line 408

def setInteractive(val=true)
  @excel.Interactive = val
end

#setScreenUpdating(val = true) ⇒ Object 



412
413
414
 
# File 'lib/winexcel/excel_file/common_methods.rb', line 412

def setScreenUpdating(val=true)
  @excel.ScreenUpdating = val
end

#setSettings(excel) ⇒ Object 



88
89
90
91
92
93
94
 
# File 'lib/winexcel/excel_file.rb', line 88

def setSettings excel
  excel.Application.AutomationSecurity = XlsAutomationSecurity
  excel.DisplayAlerts = XlsDisplayAlerts
  excel.Visible = XlsVisible
  excel.ScreenUpdating = XlsScreenUpdating
  excel.Interactive = XlsInteractive
end

#setVisible(val = true) ⇒ Object 



404
405
406
 
# File 'lib/winexcel/excel_file/common_methods.rb', line 404

def setVisible(val=true)
  @excel.Visible = val
end

#unprotect(password = "zx") ⇒ Object 



105
106
107
108
109
 
# File 'lib/winexcel/excel_file/other_methods.rb', line 105

def unprotect(password="zx")
  @workbook.Worksheets.each do |s|
    s.Unprotect(password)
  end
end

#worksheetExists?(sheet) ⇒ Boolean

Returns:

  • (Boolean) 


395
396
397
 
# File 'lib/winexcel/excel_file/common_methods.rb', line 395

def worksheetExists?(sheet)
  @workbook.Sheets(sheet).Name != ""
end

#write1DArrayColor(data, myRange, sheet = nil, col = 0) ⇒ Object 



275
276
277
278
279
280
281
282
283
284
 
# File 'lib/winexcel/excel_file/other_methods.rb', line 275

def write1DArrayColor(data, myRange, sheet = nil, col = 0)
  @log.info(self.class) { "write2DArray(data='...',myRange='#{myRange}', sheet = '#{sheet})'" }
  worksheet = getWorksheet(sheet)
  #get the actual excel range object
  myRange = worksheet.Range(myRange)
  data.each_index do |row|
    @log.debug(self.class) { data[row] }
    myRange.Offset(row, col).Interior.Color = data[row]
  end
end

#write2DArray(data, myRange, sheet = nil) ⇒ Object

writes out the 2D Array data starting at the specified range myRange on the specified sheet



45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
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
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
 
# File 'lib/winexcel/excel_file/write_2D_array.rb', line 45

def write2DArray(data, myRange, sheet = nil)
  @log.info("write2DArray(data='...',myRange='#{myRange}', sheet = '#{sheet})'")
  worksheet = getWorksheet(sheet)
  #get the actual excel range object
  myRange = worksheet.Range(myRange)

  # first check if there is any color information in the data
  isAdditionalInfo = false
  (0..data.length-1).each do |row|
    (0..data[row].length-1).each do |col|
      colorIdx = -1
      colorLen = -1
      begin
        if /\A<<COLOUR=([A-Z]+)>>/.match(data[row][col])
          colorLen = /\A<<COLOUR=([A-Z]+)>>/.match(data[row][col])[1].length
          case /\A<<COLOUR=([A-Z]+)>>/.match(data[row][col])[1]
            when 'BLACK'
              colorIdx = 1
            when 'RED'
              colorIdx = 3
            when 'YELLOW'
              colorIdx = 6
            when 'GREEN'
              colorIdx = 10
          end
        end
        if colorIdx > -1 or data[row][col] =~ /^=HYPERLINK/
          isAdditionalInfo = true
          break
        end
      rescue
      end
    end
    if isAdditionalInfo == true
      break
    end
  end

  if not isAdditionalInfo
    # find maximum row length (or column quantity) for the 'data' array,
    # it would be column quantity of 2D array if we would put the 'data' array into one
    maxWidth = 0
    maxCellLength = 0
    data.each do |row|
      maxWidth = row.length if maxWidth < row.length
      row.each do |cell|
        maxCellLength = cell.to_s.length if cell and maxCellLength < cell.to_s.length
      end
    end
    data.collect do |row|
      while row.length < maxWidth
        if not row.kind_of? Array
          row = []
        end
        row << ''
      end
    end
    if maxCellLength < 8204
      myRange.Resize(data.length, maxWidth).Value = data if data and data.length > 0
    else
      (0..data.length-1).each do |row|
        (0..data[row].length-1).each do |col|
          myRange.Offset(row, col).value = data[row][col]
        end
      end
    end
  else
    (0..data.length-1).each do |row|
      (0..data[row].length-1).each do |col|
        #puts data[row][col]

        colorIdx = -1
        colorLen = -1
        begin
          if /\A<<COLOUR=([A-Z]+)>>/.match(data[row][col])
            colorLen = /\A<<COLOUR=([A-Z]+)>>/.match(data[row][col])[1].length
            case /\A<<COLOUR=([A-Z]+)>>/.match(data[row][col])[1]
              when 'BLACK'
                colorIdx = 1
              when 'RED'
                colorIdx = 3
              when 'YELLOW'
                colorIdx = 6
              when 'GREEN'
                colorIdx = 10
            end
          end
        rescue
        end

        #TODO: Excel bottleneck - it needs some deley here
        #sleep(0.1)

        val = ''
        if colorIdx > -1 then
          myRange.Offset(row, col).Interior.ColorIndex = colorIdx
          beg = '<<COLOUR=>>'.length + colorLen
          val = data[row][col][beg..data[row][col].length]
        else
          val = data[row][col]
        end

        begin
          myRange.Offset(row, col).value = val
        rescue
          if val =~ /^=HYPERLINK/
            if val[',']
              val.gsub!(',', ';')
            elsif val[';']
              val.gsub!(';', ',')
            end

            myRange.Offset(row, col).value = val
          end
        end

      end
    end
  end

end

#write2DArrayAtOnce(data, myRange, sheet = nil) ⇒ Object

writes out the 2D Array data starting at the specified range myRange on the specified sheet



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
340
341
342
343
 
# File 'lib/winexcel/excel_file/common_methods.rb', line 307

def write2DArrayAtOnce(data, myRange, sheet = nil)
  @log.info("write2DArray(data='...',myRange='#{myRange}', sheet = '#{sheet})'")
  worksheet = getWorksheet(sheet)
  #get the actual excel range object
  myRange = worksheet.Range(myRange)


  # find maximum row length (or column quantity) for the 'data' array,
  # it would be column quantity of 2D array if we would put the 'data' array into one
  maxWidth = 0
  maxCellLength = 0
  data.each do |row|
    maxWidth = row.length if maxWidth < row.length
    row.each do |cell|
      maxCellLength = cell.to_s.length if cell and maxCellLength < cell.to_s.length
    end
  end
  data.collect do |row|
    while row.length < maxWidth
      if not row.kind_of? Array
        row = []
      end
      row << ''
    end
  end
  if maxCellLength < 8204
    myRange.Resize(data.length, maxWidth).Value = data if data and data.length > 0
  else
    (0..data.length-1).each do |row|
      (0..data[row].length-1).each do |col|
        myRange.Offset(row, col).value = data[row][col]
      end
    end
  end


end

#writeArrayHash(data, myRange, sheet = nil) ⇒ Object

writes out the Array hash data starting at the specified range myRange on the specified sheet. the keys are used as column headers starting at the specified range.



289
290
291
292
 
# File 'lib/winexcel/excel_file/other_methods.rb', line 289

def writeArrayHash(data, myRange, sheet = nil)
  @log.info(self.class) { "writeArrayHash(data='...',myRange='#{myRange}', sheet = '#{sheet})'" }
  write2DArray(convertArrayHashTo2DArray(data), myRange, sheet)
end