Module: IMW::Formats::Excel

Defined in:
lib/imw/formats/excel.rb

Overview

Defines methods for reading and writing Microsoft Excel data.

Instance Attribute Summary collapse

Class Method Summary collapse

Instance Method Summary collapse

Instance Attribute Details

#book ⇒ Object

Returns the value of attribute book.



7
8
9
 
# File 'lib/imw/formats/excel.rb', line 7

def book
  @book
end

#sheet ⇒ Object

Returns the value of attribute sheet.



7
8
9
 
# File 'lib/imw/formats/excel.rb', line 7

def sheet
  @sheet
end

Class Method Details

.extended(obj) ⇒ Object 



9
10
11
12
13
14
15
 
# File 'lib/imw/formats/excel.rb', line 9

def self.extended obj
  if obj.exist?
    @book = Spreadsheet.open path
    @sheet = book.worksheet(0)
    
  end
end

Instance Method Details

#<<(line) ⇒ Object

Processes a single line of data and updates internal variables. You shouldn’t need to call this directly.



71
72
73
74
75
 
# File 'lib/imw/formats/excel.rb', line 71

def << line
  @sheet.row(@sht_row).concat( line )
  @sht_row += 1
  @idx += 1
end

#dump(data) ⇒ Object

Dumps data, which is assumed to be an array of arrays, to a newly created Excel workbook. Attempting to dump to a book that already exists will typically result in file corruption. Raises a ‘too many lines’ error if the number of lines of data exceeds max_lines.



61
62
63
64
65
66
67
 
# File 'lib/imw/formats/excel.rb', line 61

def dump data
  data.each do |line|
    raise "too many lines" if too_many?
    self << line
  end
  save unless no_data?
end

#get_existing_book ⇒ Object

Opens an existing Excel workbook. You shoudn’t need to call this directly.



95
96
97
98
 
# File 'lib/imw/formats/excel.rb', line 95

def get_existing_book
  @sht_row = @sheet.row_count #would like to be able to dump new data, doesn't work
  @sht_idx += 1
end

#incr_sheet ⇒ Object

Increments the current sheet to the next one in an open book. Not necessary at the moment.



102
103
104
 
# File 'lib/imw/formats/excel.rb', line 102

def incr_sheet
  @sheet = book.worksheet @sht_idx
end

#initialize(uri, mode = 'r', options = {}) ⇒ Object

If an Excel file exists at the location specified by uri then it is opened and can be read out with a subsequent call to load(). Otherwise, a new workbook is created and can be written to with the dump() method.



36
37
38
39
40
41
42
43
44
45
46
47
48
 
# File 'lib/imw/formats/excel.rb', line 36

def initialize uri, mode='r', options={}
  self.uri = uri
  @max_lines = options[:max_lines] || 65000
  @idx = 0
  @book_idx = 0
  @sht_idx = 0
  unless self.exist?
    make_new_book
    make_new_sheet
  else
    get_existing_book
  end
end

#load ⇒ Object

Returns the data in an existing workbook as an array of arrays. Only capable of reading a single sheet.



52
53
54
 
# File 'lib/imw/formats/excel.rb', line 52

def load
  @sheet.map{|row| row.to_a}
end

#make_new_book ⇒ Object

Instantiates a new Excel workbook in memory. You shouldn’t need to call this directly.



79
80
81
82
 
# File 'lib/imw/formats/excel.rb', line 79

def make_new_book
  @book = Spreadsheet::Workbook.new
  @book_idx += 1
end

#make_new_sheet ⇒ Object

Makes a new worksheet for a pre-existing Excel workbook. This should be called after recovering from the ‘too many lines’ error.



87
88
89
90
91
 
# File 'lib/imw/formats/excel.rb', line 87

def make_new_sheet
  @sheet = @book.create_worksheet
  @sht_idx += 1
  @sht_row = 0 #always start at row 0 in a new sheet
end

#no_data? ⇒ Boolean

There is no data if the number of rows attempting to be written is zero.

Returns:

  • (Boolean) 


114
115
116
 
# File 'lib/imw/formats/excel.rb', line 114

def no_data?
  @sht_row == 0
end

#save ⇒ Object

Saves the workbook.



119
120
121
 
# File 'lib/imw/formats/excel.rb', line 119

def save
  @book.write path
end

#too_many? ⇒ Boolean

There are too many lines if the number of rows attempting to be written exceeds max_lines.

Returns:

  • (Boolean) 


108
109
110
 
# File 'lib/imw/formats/excel.rb', line 108

def too_many?
  @sht_row >= @max_lines
end