Module: TSV::XLS

Defined in:
lib/rbbt/tsv/excel.rb

Class Method Summary collapse

Class Method Details

.read(file, options = {}) ⇒ Object 



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/rbbt/tsv/excel.rb', line 96

def self.read(file, options = {})
  options = Misc.add_defaults options, :sep2 => /[,|]\s?/, :skip_rows => 0
  sheet = Misc.process_options options, :sheet
  header = Misc.process_options options, :header
  text = Misc.process_options options, :text
  skip_rows = Misc.process_options options, :skip_rows
  skip_rows = skip_rows.to_i

  header = true unless header == false
  sheet ||= "0"

  workbook = Spreadsheet.open Open.open(file)

  if sheet && sheet.to_s =~ /^\d+$/
    sheet = workbook.worksheets.collect{|s| s.name }[sheet.to_i]
  end
  sheet_name = sheet
  Log.debug "Opening LSX #{file} sheet #{ sheet_name }"

  TmpFile.with_file :extension => Misc.sanitize_filename(sheet_name.to_s) do |filename|

    sheet    = workbook.worksheet sheet

    rows = []

    sheet.each do |row|
      if skip_rows > 0
        skip_rows -= 1
        next
      end
      
      rows << row.values_at(0..(row.size - 1)).collect{|c| String === c ? c.gsub("\n", ' ') : c }
    end

    num_values = rows.first.length
    File.open(filename, 'w') do |f|
      if header
        header = rows.shift
        f.puts "#" + header * "\t"
      end

      rows.each do |row| 
        values =  row.collect{|c| c.respond_to?(:value) ? c.value : c }
        values[num_values-1] ||= nil
        f.puts values * "\t"
      end
    end

    text ? Open.read(filename) : TSV.open(filename, options)
  end
end

.write(tsv, file, options = {}) ⇒ Object 



148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
 
# File 'lib/rbbt/tsv/excel.rb', line 148

def self.write(tsv, file, options = {})
  options = Misc.add_defaults options, :sheet => "Sheet1"
  sheet = Misc.process_options options, :sheet
  fields, rows = TSV._excel_data(tsv, options)

  book = Spreadsheet::Workbook.new
  sheet1 = book.create_worksheet 
  sheet1.name = sheet if sheet

  if fields
    sheet1.row(0).concat fields if fields
  else
    sheet1.row(0).concat ["No field info"]
  end

  rows.each_with_index do |cells,i|
    sheet1.row(i+1).concat cells
  end

  book.write file
end