lib/rbbt/tsv/excel.rb in rbbt-util-5.21.56 vs lib/rbbt/tsv/excel.rb in rbbt-util-5.21.57
- old
+ new
@@ -1,16 +1,44 @@
require 'spreadsheet'
+require 'rubyXL'
+
module TSV
- def self.excel(tsv, filename, options ={})
+
+ def self._remove_link(value)
+ if value =~ /<([\w]+)[^>]*>(.*?)<\/\1>/
+ $2
+ else
+ value
+ end
+ end
+
+ def self._clean_float(v)
+ case v
+ when Float
+ v.to_s.sub(/e(-?\d+)$/,'E\1')
+ when String
+ if v =~ /^-?[\d\.]+e(-?\d+)$/
+ v.sub(/e(-?\d+)$/,'E\1')
+ else
+ v
+ end
+ else
+ v
+ end
+ end
+
+
+ def self._excel_data(tsv, options ={})
+ options = Misc.add_defaults options, :sep2 => ', '
+
name = Misc.process_options options, :name
+ sep2 = Misc.process_options options, :sep2
+ unmerge = Misc.process_options options, :unmerge
sort_by = Misc.process_options options, :sort_by
sort_by_cast = Misc.process_options options, :sort_by_cast
- fields = Misc.process_options(options, :fields) || tsv.all_fields
+ remove_links = Misc.process_options options, :remove_links
- book = Spreadsheet::Workbook.new
- sheet1 = book.create_worksheet
- sheet1.row(0).concat fields
i = 1
if sort_by
if sort_by_cast
data = tsv.sort_by sort_by do |k, v|
if Array === v
@@ -24,96 +52,184 @@
end
else
data = tsv
end
- data.each do |key, values|
+ rows = []
+ data.through do |key, values|
cells = []
cells.push((name and key.respond_to?(:name)) ? key.name || key : key )
values = [values] unless Array === values
values.each do |value|
v = (name and value.respond_to?(:name)) ? value.name || value : value
if Array === v
- cells.push v * ", "
+ v = v.collect{|_v| _remove_link(_v)} if remove_links
+ v = v.collect{|_v| _clean_float(_v)}
+ if unmerge
+ cells.push v
+ else
+ cells.push v * sep2
+ end
else
+ v = remove_link(v) if remove_links
cells.push v
end
end
- sheet1.row(i).concat cells
+ rows << cells
i += 1
end
- book.write filename
+ if unmerge
+ new_rows = []
+ rows.each do |row|
+ header = row.shift
+ Misc.zip_fields(row).each do |values|
+ new_rows << [header] + values
+ end
+ end
+ rows = new_rows
+ end
+ [tsv.all_fields, rows]
end
- def remove_link(value)
- if value =~ /<([\w]+)[^>]*>(.*?)<\/\1>/
- $2
- else
- value
+ module XLS
+ def self.read(file, options = {})
+ options = Misc.add_defaults options, :sep2 => /[,|]\s?/
+ sheet = Misc.process_options options, :sheet
+ header = Misc.process_options options, :header
+
+ header = true unless header == false
+ sheet ||= 0
+ TmpFile.with_file do |filename|
+ workbook = Spreadsheet.open Open.open(file)
+ sheet = workbook.worksheet sheet
+
+ rows = []
+
+ sheet.each do |row|
+ rows << row.values_at(0..(row.size - 1))
+ end
+
+ File.open(filename, 'w') do |f|
+ if header
+ header = rows.shift
+ f.puts "#" + header * "\t"
+ end
+
+ rows.each do |row| f.puts row * "\t" end
+ end
+
+ TSV.open(filename, options)
+ end
end
+
+ 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
+
+ sheet1.row(0).concat fields
+
+ rows.each_with_index do |cells,i|
+ sheet1.row(i+1).concat cells
+ end
+
+ book.write file
+ end
end
- def excel(filename, options ={})
- name = Misc.process_options options, :name
- sort_by = Misc.process_options options, :sort_by
- sort_by_cast = Misc.process_options options, :sort_by_cast
- remove_links = Misc.process_options options, :remove_links
+ module XLSX
+ def self.read(file, options = {})
+ options = Misc.add_defaults options, :sep2 => /[,|]\s?/
+ sheet = Misc.process_options options, :sheet
+ header = Misc.process_options options, :header
- book = Spreadsheet::Workbook.new
- sheet1 = book.create_worksheet
- sheet1.row(0).concat all_fields
- i = 1
- if sort_by
- if sort_by_cast
- data = self.sort_by sort_by do |k, v|
- if Array === v
- v.first.send(sort_by_cast)
- else
- v.send(sort_by_cast)
+ header = true unless header == false
+ TmpFile.with_file do |filename|
+ workbook = RubyXL::Parser.parse file
+ sheet = sheet ? workbook[sheet] : workbook.worksheets.first
+
+ rows = []
+
+ sheet.each do |row|
+ rows << row.cells.collect{|c| c.value}
+ end
+
+ File.open(filename, 'w') do |f|
+ if header
+ header = rows.shift
+ f.puts "#" + header * "\t"
end
+
+ rows.each do |row| f.puts row * "\t" end
end
- else
- data = self.sort_by sort_by
+
+ TSV.open(filename, options)
end
- else
- data = self
end
- data.through do |key, values|
- cells = []
- cells.push((name and key.respond_to?(:name)) ? key.name || key : key )
+ def self.write(tsv, file, options = {})
+ sheet = Misc.process_options options, :sheet
- values = [values] unless Array === values
- values.each do |value|
- v = (name and value.respond_to?(:name)) ? value.name || value : value
- if Array === v
- v = v.collect{|_v| remove_link(_v)} if remove_links
- cells.push v * ", "
- else
- v = remove_link(v) if remove_links
- cells.push v
- end
+ fields, rows = TSV._excel_data(tsv, options)
+
+ book = RubyXL::Workbook.new
+ sheet1 = book.worksheets.first
+ sheet1.sheet_name = sheet if sheet
+
+ fields.each_with_index do |e,i|
+ sheet1.add_cell(0, i, e)
end
- cells = cells.collect do |v|
- case v
- when Float
- v.to_s.sub(/e(-?\d+)$/,'E\1')
- when String
- if v =~ /^-?[\d\.]+e(-?\d+)$/
- v.sub(/e(-?\d+)$/,'E\1')
- else
- v
- end
- else
- v
+ rows.each_with_index do |cells,i|
+ cells.each_with_index do |e,j|
+ sheet1.add_cell(i+1, j, e)
end
end
- sheet1.row(i).concat cells
- i += 1
+ book.write file
end
- book.write filename
end
+
+ def xls(filename, options ={})
+ TSV::XLS.write(self, filename, options)
+ end
+
+ def xlsx(filename, options ={})
+ TSV::XLSX.write(self, filename, options)
+ end
+
+ def excel(filename, options ={})
+ if filename =~ /\.xlsx$/
+ xlsx(filename, options)
+ else
+ xls(filename, options)
+ end
+ end
+
+
+ def self.xls(filename, options ={})
+ TSV::XLS.read(filename, options)
+ end
+
+ def self.xlsx(filename, options ={})
+ TSV::XLSX.read(filename, options)
+ end
+
+ def self.excel(filename, options = {})
+ if filename =~ /\.xlsx$/
+ xlsx(filename, options)
+ else
+ xls(filename, options)
+ end
+ end
+
+ def self.excel2tsv(filename, options ={})
+ excel(filename, options={})
+ end
+
end