Class: Rasta::Spreadsheet::Sheet

Inherits:
Object
  • Object
show all
Defined in:
lib/rasta/spreadsheet.rb

Overview

Sheets store the information about records on the sheet. In order to allow the user to add comments and have flexibility with how the data is laid out we have the following requirements:

  • The data will start at the first non-bold cell closest to Cell ‘A1’

  • Bold cells will be ignored until we hit the first data cell.

  • The header row (this is usually the attribute you’re going to set) needs to be bolded. We will use which side of the data (top or left) to determine if the data is laid out in rows or in columns.

Iterating over the Sheet will return Records which represent the row/column

Defined Under Namespace

Classes: ObjectError

Instance Attribute Summary collapse

Instance Method Summary collapse

Constructor Details

#initialize(book, worksheet) ⇒ Sheet

Returns a new instance of Sheet.



232
233
234
235
236
237
238
239
240
241
242
243
 
# File 'lib/rasta/spreadsheet.rb', line 232

def initialize(book, worksheet)
  # TODO: Add check for duplicates if option set
  @book     = book
  @o        = worksheet
  @name     = worksheet.name
  self.select
  # Order here is important because in these functions we
  # will use the class variables from the prior call
  (@lastrow, @lastcol)   = locate_last_data_cell
  (@firstrow, @firstcol) = locate_first_data_cell
  (@headers, @style)     = locate_headers 
end

Instance Attribute Details

#book ⇒ Object (readonly)

Returns the value of attribute book.



227
228
229
 
# File 'lib/rasta/spreadsheet.rb', line 227

def book
  @book
end

#firstcol ⇒ Object (readonly)

Returns the value of attribute firstcol.



227
228
229
 
# File 'lib/rasta/spreadsheet.rb', line 227

def firstcol
  @firstcol
end

#firstrow ⇒ Object (readonly)

Returns the value of attribute firstrow.



227
228
229
 
# File 'lib/rasta/spreadsheet.rb', line 227

def firstrow
  @firstrow
end

#headers ⇒ Object (readonly)

Returns the value of attribute headers.



227
228
229
 
# File 'lib/rasta/spreadsheet.rb', line 227

def headers
  @headers
end

#lastcol ⇒ Object (readonly)

Returns the value of attribute lastcol.



227
228
229
 
# File 'lib/rasta/spreadsheet.rb', line 227

def lastcol
  @lastcol
end

#lastrow ⇒ Object (readonly)

Returns the value of attribute lastrow.



227
228
229
 
# File 'lib/rasta/spreadsheet.rb', line 227

def lastrow
  @lastrow
end

#name ⇒ Object (readonly)

Returns the value of attribute name.



227
228
229
 
# File 'lib/rasta/spreadsheet.rb', line 227

def name
  @name
end

#style ⇒ Object (readonly)

Returns the value of attribute style.



227
228
229
 
# File 'lib/rasta/spreadsheet.rb', line 227

def style
  @style
end

Instance Method Details

#[](index) ⇒ Object 



338
339
340
 
# File 'lib/rasta/spreadsheet.rb', line 338

def [](index)
  Record.new(self, index)
end

#cell(record_index, cell_index) ⇒ Object 



334
335
336
 
# File 'lib/rasta/spreadsheet.rb', line 334

def cell (record_index, cell_index)
  Cell.new(self, record_index, cell_index)
end

#cellrange(index, style = @style) ⇒ Object

Get the ole range object for a row or column



277
278
279
280
281
282
283
284
 
# File 'lib/rasta/spreadsheet.rb', line 277

def cellrange(index, style=@style)
  case style
  when :row
    @o.Range("#{colname(@firstcol)}#{index}:#{colname(@lastcol)}#{index}")
  when :col
    @o.Range("#{colname(index)}#{@firstrow}:#{colname(index)}#{@lastrow}")
  end
end

#cellrangevals(index, style = @style) ⇒ Object

Get an array of the values of cells in the range describing the row or column



287
288
289
290
291
292
293
294
295
296
297
298
299
 
# File 'lib/rasta/spreadsheet.rb', line 287

def cellrangevals(index, style=@style)
  range = cellrange(index, style)
  # It looks like range['Value'] returns an array with multiple
  # items and a string with one item so coerce the string into 
  # a one-dimensional array
  return [range['Value']] if range['Value'].class != Array
  case style
  when :row
    range['Value'][0] 
  when :col
    range['Value'].map{ |v| v[0] }
  end
end

#colname(col) ⇒ Object

Translate a numerical column index to the alpha worksheet column the user sees



302
303
304
 
# File 'lib/rasta/spreadsheet.rb', line 302

def colname(col)
  @o.Columns(col).address.slice!(/(\w+)/)
end

#datacell?(row, col) ⇒ Boolean

A cell is a data cell if the cell’s font is not bold and there is no background color

Returns:

  • (Boolean) 


272
273
274
 
# File 'lib/rasta/spreadsheet.rb', line 272

def datacell?(row,col)
  @o.Cells(row,col).Font.Bold == false && @o.Cells(row,col).Interior.ColorIndex == ExcelConst::XlColorIndexNone
end

#dump ⇒ Object 



342
343
344
345
346
 
# File 'lib/rasta/spreadsheet.rb', line 342

def dump
  vals = []
  self.each { |record| vals << record.dump }
  return vals
end

#each ⇒ Object 



306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
 
# File 'lib/rasta/spreadsheet.rb', line 306

def each
  case @style
  when :row
    firstrecord = @firstrow
    lastrecord  = @lastrow
  when :col
    firstrecord = @firstcol
    lastrecord  = @lastcol
  end
  (firstrecord..lastrecord).each do |record_index|
    case @style
    when :row
      recordid = record_index.to_s
    when :col
      recordid = colname(record_index)
    end    
    excel = Excel.instance
    next if excel.continue && !@book.bookmark.found?(:record, recordid)
    excel.currentrecord += 1
    return if (excel.recordcount > 0 && excel.currentrecord > excel.recordcount)
    yield Record.new(self, record_index)
  end
end

#ole_object ⇒ Object 



330
331
332
 
# File 'lib/rasta/spreadsheet.rb', line 330

def ole_object
  @o
end

#select ⇒ Object 



253
254
255
256
257
258
259
 
# File 'lib/rasta/spreadsheet.rb', line 253

def select
  begin
    @o.Select
  rescue WIN32OLERuntimeError
    raise ObjectError, "Unable to locate worksheet #{@name}"
  end
end

#select_home_cell ⇒ Object 



261
262
263
264
265
266
267
268
 
# File 'lib/rasta/spreadsheet.rb', line 261

def select_home_cell
  self.select
  begin
    @o.Cells(1,1).Select
  rescue WIN32OLERuntimeError
    raise ObjectError, "Unable to select cell in #{@name}"
  end
end

#to_s ⇒ Object 



245
246
247
248
249
250
251
 
# File 'lib/rasta/spreadsheet.rb', line 245

def to_s
  "firstrow = #{@firstrow}\n" +
  "firstcol = #{@firstcol}\n" +
  "lastrow  = #{@lastrow}\n"  +
  "lastcol  = #{@lastcol}\n"  +
  "style    = #{@style}\n"
end