Class: Statsample::Excel

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

Class Method Summary collapse

Methods inherited from SpreadsheetBase

convert_to_scale_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



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

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, worksheet_id = 0, ignore_lines = 0, empty = ['']) ⇒ Object

Returns a dataset based on a xls file USE:

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



184
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
 
# File 'lib/statsample/converters.rb', line 184

def read(filename, worksheet_id=0, ignore_lines=0, empty=[''])
  require 'spreadsheet'
  first_row=true
  fields=[]
  fields_data={}
  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_scale_and_date(ds, fields)
  ds.update_valid_data
  ds
end

.write(dataset, filename) ⇒ Object

Write a Excel spreadsheet based on a dataset

  • TODO: Format nicely date values



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

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)
  sheet.row(0).default_format = format
  i=1
  dataset.each_array{|row|
    sheet.row(i).concat(row)
    i+=1
  }
  book.write(filename)
end