Class: Statsample::Excel

Inherits:
SpreadsheetBase show all
Defined in:
lib/statsample/converters.rb

Class Method Summary collapse

Methods inherited from SpreadsheetBase

convert_to_numeric_and_date, extract_fields, process_row

Class Method Details

.preprocess_row(row, dates) ⇒ Object

This should be fixed. If we have a Formula, should be resolver first



162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
 
# File 'lib/statsample/converters.rb', line 162

def preprocess_row(row, dates)
  i=-1
  row.collect!{|c|
    i+=1
    if c.is_a? Spreadsheet::Formula
      if(c.value.is_a? Spreadsheet::Excel::Error)
        nil
      else
        c.value
      end
    elsif dates.include? i and !c.nil? and c.is_a? Numeric
        row.date(i)
    else
        c
    end
  }
end

.read(filename, opts = Hash.new) ⇒ Object

Returns a dataset based on a xls file USE:

ds = Statsample::Excel.read("test.xls")



185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
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
238
239
240
241
 
# File 'lib/statsample/converters.rb', line 185

def read(filename, opts=Hash.new)
  require 'spreadsheet'
  raise "options should be Hash" unless opts.is_a? Hash
  opts_default={
    :worksheet_id=>0,
    :ignore_lines=>0,
    :empty=>['']
  }

  opts=opts_default.merge opts

  worksheet_id=opts[:worksheet_id]
  ignore_lines=opts[:ignore_lines]
  empty=opts[:empty]

  first_row=true
  fields=[]
  ds=nil
  line_number=0
  book = Spreadsheet.open filename
  sheet= book.worksheet worksheet_id
  sheet.each do |row|
    begin
      dates=[]
      row.formats.each_index{|i|
        if !row.formats[i].nil? and row.formats[i].number_format=="DD/MM/YYYY"
          dates.push(i)
        end
      }
      line_number+=1
      next if(line_number<=ignore_lines)

      preprocess_row(row,dates)
      if first_row
        fields=extract_fields(row)
        ds=Statsample::Dataset.new(fields)
        first_row=false
      else
        rowa=process_row(row,empty)
        (fields.size - rowa.size).times {
          rowa << nil
        }
        ds.add_case(rowa,false)
      end
    rescue => e
      error="#{e.to_s}\nError on Line # #{line_number}:#{row.join(",")}"
      raise
    end
  end
  convert_to_numeric_and_date(ds, fields)
  ds.update_valid_data
  fields.each {|f|
    ds[f].name=f
  }
  ds.name=filename
  ds
end

.write(dataset, filename) ⇒ Object

Write a Excel spreadsheet based on a dataset

  • TODO: Format nicely date values



144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
 
# File 'lib/statsample/converters.rb', line 144

def write(dataset,filename)
  require 'spreadsheet'
  book = Spreadsheet::Workbook.new
  sheet = book.create_worksheet
  format = Spreadsheet::Format.new :color => :blue,
                     :weight => :bold
  sheet.row(0).concat(dataset.fields.map {|i| i.dup}) # Unfreeze strings
  sheet.row(0).default_format = format
  i=1
  dataset.each_array{|row|
    sheet.row(i).concat(row)
    i+=1
  }
  book.write(filename)
end