Module: TSV::XLSX

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

Class Method Summary collapse

Class Method Details

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



168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
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
 
# File 'lib/rbbt/tsv/excel.rb', line 168

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 = RubyXL::Parser.parse file
  if sheet && sheet =~ /^\d+$/
    sheet = workbook.worksheets.collect{|s| s.sheet_name }[sheet.to_i]
  end
  sheet_name = sheet
  Log.debug "Opening XLSX #{file} sheet #{ sheet_name }"

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

    sheet    = sheet_name ? workbook[sheet_name] : workbook.worksheets.first

    raise "No sheet #{sheet_name} found" if sheet.nil?

    rows = []

    sheet.each do |row|
      next if row.nil?
      if skip_rows > 0
        skip_rows -= 1
        next
      end
      
      rows << row.cells.collect{|c| c.nil? ? nil : c.value}.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| 
        row[num_values-1] ||= nil
        f.puts row * "\t" 
      end
    end

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

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



221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
 
# File 'lib/rbbt/tsv/excel.rb', line 221

def self.write(tsv, file, options = {})
  sheet, add_sheet = Misc.process_options options, :sheet, :add_sheet

  fields, rows = TSV._excel_data(tsv, options)

  if Open.exists?(file) && add_sheet
    book = RubyXL::Parser.parse file
    sheet1 = book.add_worksheet(sheet)
  else
    book = RubyXL::Workbook.new
    sheet1 = book.worksheets.first
    sheet1.sheet_name = sheet if sheet
  end

  fields.each_with_index do |e,i|
    sheet1.add_cell(0, i, e)
  end if fields

  rows.each_with_index do |cells,i|
    i += 1 if fields
    cells.each_with_index do |e,j|
      sheet1.add_cell(i, j, e)
    end
  end

  book.write file
end