lib/roo/generic_spreadsheet.rb in roo-1.10.1 vs lib/roo/generic_spreadsheet.rb in roo-1.10.2

- old
+ new

@@ -1,41 +1,25 @@ # encoding: utf-8 -require 'matrix' +require 'tmpdir' +require 'stringio' + # Base class for all other types of spreadsheets -class GenericSpreadsheet +class Roo::GenericSpreadsheet + include Enumerable - attr_reader :default_sheet + TEMP_PREFIX = "oo_" + attr_reader :default_sheet, :headers + # sets the line with attribute names (default: 1) attr_accessor :header_line protected - # Helper function for development - def fremdrechner? #nodoc - eigener = [ - 'C:\Users\thopre', - 'c:/Users/thopre', - '/c/Users/thopre', - '/home/tp', - ].include? ENV['HOME'] - # if eigener - # puts "fremdrechner? ==> false" - # else - # puts "fremdrechner? ==> true" - # end - ! eigener - end - - def self.next_tmpdir - tmpdir = "oo_"+$$.to_s+"_"+sprintf("%010d",rand(10_000_000_000)) - tmpdir - end - def self.split_coordinate(str) - letter,number = GenericSpreadsheet.split_coord(str) + letter,number = Roo::GenericSpreadsheet.split_coord(str) x = letter_to_number(letter) y = number return y, x end @@ -50,113 +34,113 @@ end public + def initialize(filename, packed=nil, file_warning=:error, tmpdir=nil) + @cell = Hash.new{|h,k| h[k] = {}} + @cell_type = Hash.new{|h,k| h[k] = {}} + @cells_read = {} + + @first_row = {} + @last_row = {} + @first_column = {} + @last_column = {} + + @style = {} + @style_defaults = Hash.new { |h,k| h[k] = [] } + @style_definitions = {} + + @default_sheet = self.sheets.first + @formula = {} + @header_line = 1 + end + # sets the working sheet in the document # 'sheet' can be a number (1 = first sheet) or the name of a sheet. def default_sheet=(sheet) - if sheet.kind_of? Fixnum - if sheet > 0 and sheet <= sheets.length - sheet = self.sheets[sheet-1] - else - raise RangeError - end - elsif sheet.kind_of?(String) - raise RangeError if ! self.sheets.include?(sheet) - else - raise TypeError, "what are you trying to set as default sheet?" - end + validate_sheet!(sheet) @default_sheet = sheet - check_default_sheet @first_row[sheet] = @last_row[sheet] = @first_column[sheet] = @last_column[sheet] = nil @cells_read[sheet] = false end # first non-empty column as a letter def first_column_as_letter(sheet=nil) - GenericSpreadsheet.number_to_letter(first_column(sheet)) + Roo::GenericSpreadsheet.number_to_letter(first_column(sheet)) end # last non-empty column as a letter def last_column_as_letter(sheet=nil) - GenericSpreadsheet.number_to_letter(last_column(sheet)) + Roo::GenericSpreadsheet.number_to_letter(last_column(sheet)) end # returns the number of the first non-empty row def first_row(sheet=nil) - if sheet == nil - sheet = @default_sheet - end + sheet ||= @default_sheet read_cells(sheet) unless @cells_read[sheet] if @first_row[sheet] return @first_row[sheet] end impossible_value = 999_999 # more than a spreadsheet can hold result = impossible_value @cell[sheet].each_pair {|key,value| - y,x = key # _to_string(key).split(',') - y = y.to_i + y = key.first.to_i # _to_string(key).split(',') result = [result, y].min if value } if @cell[sheet] result = nil if result == impossible_value @first_row[sheet] = result result end # returns the number of the last non-empty row def last_row(sheet=nil) - sheet = @default_sheet unless sheet + sheet ||= @default_sheet read_cells(sheet) unless @cells_read[sheet] if @last_row[sheet] return @last_row[sheet] end impossible_value = 0 result = impossible_value @cell[sheet].each_pair {|key,value| - y,x = key # _to_string(key).split(',') - y = y.to_i + y = key.first.to_i # _to_string(key).split(',') result = [result, y].max if value } if @cell[sheet] result = nil if result == impossible_value @last_row[sheet] = result result end # returns the number of the first non-empty column def first_column(sheet=nil) - if sheet == nil - sheet = @default_sheet - end + sheet ||= @default_sheet read_cells(sheet) unless @cells_read[sheet] if @first_column[sheet] return @first_column[sheet] end impossible_value = 999_999 # more than a spreadsheet can hold result = impossible_value @cell[sheet].each_pair {|key,value| - y,x = key # _to_string(key).split(',') - x = x # .to_i + x = key.last.to_i # _to_string(key).split(',') result = [result, x].min if value } if @cell[sheet] result = nil if result == impossible_value @first_column[sheet] = result result end # returns the number of the last non-empty column def last_column(sheet=nil) - sheet = @default_sheet unless sheet + sheet ||= @default_sheet read_cells(sheet) unless @cells_read[sheet] if @last_column[sheet] return @last_column[sheet] end impossible_value = 0 result = impossible_value @cell[sheet].each_pair {|key,value| - y,x = key # _to_string(key).split(',') - x = x.to_i + x = key.last.to_i # _to_string(key).split(',') result = [result, x].max if value } if @cell[sheet] result = nil if result == impossible_value @last_column[sheet] = result result @@ -164,11 +148,11 @@ # returns a rectangular area (default: all cells) as yaml-output # you can add additional attributes with the prefix parameter like: # oo.to_yaml({"file"=>"flightdata_2007-06-26", "sheet" => "1"}) def to_yaml(prefix={}, from_row=nil, from_column=nil, to_row=nil, to_column=nil,sheet=nil) - sheet = @default_sheet unless sheet + sheet ||= @default_sheet result = "--- \n" return '' unless first_row # empty result if there is no first_row in a sheet (from_row||first_row(sheet)).upto(to_row||last_row(sheet)) do |row| (from_column||first_column(sheet)).upto(to_column||last_column(sheet)) do |col| @@ -179,11 +163,11 @@ } result << " row: #{row} \n" result << " col: #{col} \n" result << " celltype: #{self.celltype(row,col,sheet)} \n" if self.celltype(row,col,sheet) == :time - result << " value: #{GenericSpreadsheet.integer_to_timestring( self.cell(row,col,sheet))} \n" + result << " value: #{Roo::GenericSpreadsheet.integer_to_timestring( self.cell(row,col,sheet))} \n" else result << " value: #{self.cell(row,col,sheet)} \n" end end end @@ -191,158 +175,123 @@ result end # write the current spreadsheet to stdout or into a file def to_csv(filename=nil,sheet=nil) - sheet = @default_sheet unless sheet + sheet ||= @default_sheet if filename - file = File.open(filename,"w") # do |file| - write_csv_content(file,sheet) - file.close + File.open(filename,"w") do |file| + write_csv_content(file,sheet) + end + return true else - write_csv_content(STDOUT,sheet) + sio = StringIO.new + write_csv_content(sio,sheet) + sio.rewind + return sio.read end - true end # returns a matrix object from the whole sheet or a rectangular area of a sheet def to_matrix(from_row=nil, from_column=nil, to_row=nil, to_column=nil,sheet=nil) - sheet = @default_sheet unless sheet - arr = [] - pos = 0 - return Matrix.rows([]) unless first_row + require 'matrix' - (from_row||first_row(sheet)).upto(to_row||last_row(sheet)) do |row| - line = [] - (from_column||first_column(sheet)).upto(to_column||last_column(sheet)) do |col| + sheet ||= @default_sheet + return Matrix.empty unless first_row - line << cell(row,col) + Matrix.rows((from_row||first_row(sheet)).upto(to_row||last_row(sheet)).map do |row| + (from_column||first_column(sheet)).upto(to_column||last_column(sheet)).map do |col| + cell(row,col) end - arr[pos] = line - pos += 1 - end - Matrix.rows(arr) + end) end # find a row either by row number or a condition # Caution: this works only within the default sheet -> set default_sheet before you call this method # (experimental. see examples in the test_roo.rb file) def find(*args) # :nodoc - result_array = false - args.each {|arg,val| - if arg.class == Hash - arg.each { |hkey,hval| - if hkey == :array and hval == true - result_array = true - end - } - end - } - column_with = {} - 1.upto(last_column) do |col| - column_with[cell(@header_line,col)] = col - end - result = Array.new + options = (args.last.is_a?(Hash) ? args.pop : {}) + result_array = options[:array] + header_for = Hash[1.upto(last_column).map do |col| + [col, cell(@header_line,col)] + end] #-- id if args[0].class == Fixnum rownum = args[0] if @header_line - tmp = {} + [Hash[1.upto(self.row().size).map {|j| + [header_for.fetch(j), cell(rownum,j)] + }]] else - tmp = [] - end - 1.upto(self.row(rownum).size) {|j| - x = '' - column_with.each { |key,val| - if val == j - x = key - end + self.row(rownum).size.times.map {|j| + cell(rownum,j + 1) } - if @header_line - tmp[x] = cell(rownum,j) - else - tmp[j-1] = cell(rownum,j) - end - - } - if @header_line - result = [ tmp ] - else - result = tmp end - #-- :all + #-- :all elsif args[0] == :all - if args[1].class == Hash - args[1].each {|key,val| - if key == :conditions - column_with = {} - 1.upto(last_column) do |col| - column_with[cell(@header_line,col)] = col - end - conditions = val - first_row.upto(last_row) do |i| - # are all conditions met? - found = 1 - conditions.each { |key,val| - if cell(i,column_with[key]) == val - found *= 1 - else - found *= 0 - end - } - if found > 0 - tmp = {} - 1.upto(self.row(i).size) {|j| - x = '' - column_with.each { |key,val| - if val == j - x = key - end - } - tmp[x] = cell(i,j) - } - if result_array - result << self.row(i) - else - result << tmp - end - end - end - end # :conditions - } + rows = first_row.upto(last_row) + + # are all conditions met? + if (conditions = options[:conditions]) && !conditions.empty? + column_with = header_for.invert + rows = rows.select do |i| + conditions.all? { |key,val| cell(i,column_with[key]) == val } + end end + + rows.map do |i| + if result_array + self.row(i) + else + Hash[1.upto(self.row(i).size).map do |j| + [header_for.fetch(j), cell(i,j)] + end] + end + end end - result end # returns all values in this row as an array # row numbers are 1,2,3,... like in the spreadsheet def row(rownumber,sheet=nil) - sheet = @default_sheet unless sheet + sheet ||= @default_sheet read_cells(sheet) unless @cells_read[sheet] - result = [] - first_column(sheet).upto(last_column(sheet)) do |col| - result << cell(rownumber,col,sheet) + first_column(sheet).upto(last_column(sheet)).map do |col| + cell(rownumber,col,sheet) end - result end # returns all values in this column as an array # column numbers are 1,2,3,... like in the spreadsheet def column(columnnumber,sheet=nil) if columnnumber.class == String - columnnumber = Excel.letter_to_number(columnnumber) + columnnumber = Roo::Excel.letter_to_number(columnnumber) end - sheet = @default_sheet unless sheet + sheet ||= @default_sheet read_cells(sheet) unless @cells_read[sheet] - result = [] - first_row(sheet).upto(last_row(sheet)) do |row| - result << cell(row,columnnumber,sheet) + first_row(sheet).upto(last_row(sheet)).map do |row| + cell(row,columnnumber,sheet) end - result end + # set a cell to a certain value + # (this will not be saved back to the spreadsheet file!) + def set(row,col,value,sheet=nil) #:nodoc: + sheet ||= @default_sheet + read_cells(sheet) unless @cells_read[sheet] + row, col = normalize(row,col) + cell_type = case value + when Fixnum then :float + when String, Float then :string + else + raise ArgumentError, "Type for #{value} not set" + end + + set_value(row,col,value,sheet) + set_type(row,col,cell_type,sheet) + end + # reopens and read a spreadsheet document def reload # von Abfrage der Klasse direkt auf .to_s == '..' umgestellt ds = @default_sheet if self.class.to_s == 'Google' @@ -354,27 +303,18 @@ #@first_row = @last_row = @first_column = @last_column = nil end # true if cell is empty def empty?(row, col, sheet=nil) - sheet = @default_sheet unless sheet - read_cells(sheet) unless @cells_read[sheet] or self.class == Excel + sheet ||= @default_sheet + read_cells(sheet) unless @cells_read[sheet] or self.class == Roo::Excel row,col = normalize(row,col) - return true unless cell(row, col, sheet) - return true if celltype(row, col, sheet) == :string && cell(row, col, sheet).empty? - return true if row < first_row(sheet) || row > last_row(sheet) || col < first_column(sheet) || col > last_column(sheet) - false + contents = cell(row, col, sheet) + !contents || (celltype(row, col, sheet) == :string && contents.empty?) \ + || (row < first_row(sheet) || row > last_row(sheet) || col < first_column(sheet) || col > last_column(sheet)) end - # recursively removes the current temporary directory - # this is only needed if you work with zipped files or files via the web - def remove_tmp - if File.exists?(@tmpdir) - FileUtils::rm_r(@tmpdir) - end - end - # returns information of the spreadsheet document and all sheets within # this document. def info result = "File: #{File.basename(@filename)}\n"+ "Number of sheets: #{sheets.size}\n"+ @@ -386,22 +326,22 @@ unless first_row result << " - empty -" else result << " First row: #{first_row}\n" result << " Last row: #{last_row}\n" - result << " First column: #{GenericSpreadsheet.number_to_letter(first_column)}\n" - result << " Last column: #{GenericSpreadsheet.number_to_letter(last_column)}" + result << " First column: #{Roo::GenericSpreadsheet.number_to_letter(first_column)}\n" + result << " Last column: #{Roo::GenericSpreadsheet.number_to_letter(last_column)}" end result << "\n" if sheet != sheets.last n += 1 } result end # returns an XML representation of all sheets of a spreadsheet file def to_xml - builder = Nokogiri::XML::Builder.new do |xml| + Nokogiri::XML::Builder.new do |xml| xml.spreadsheet { self.sheets.each do |sheet| self.default_sheet = sheet xml.sheet(:name => sheet) { |x| if first_row and last_row and first_column and last_column @@ -418,26 +358,25 @@ end end } end } - end - return builder.to_xml + end.to_xml end # when a method like spreadsheet.a42 is called # convert it to a call of spreadsheet.cell('a',42) def method_missing(m, *args) # #aa42 => #cell('aa',42) # #aa42('Sheet1') => #cell('aa',42,'Sheet1') if m =~ /^([a-z]+)(\d)$/ - col = GenericSpreadsheet.letter_to_number($1) + col = Roo::GenericSpreadsheet.letter_to_number($1) row = $2.to_i - if args.size > 0 - return cell(row,col,args[0]) + if args.empty? + cell(row,col) else - return cell(row,col) + cell(row,col,args.first) end else super end end @@ -446,11 +385,11 @@ #TODO: hier entfernen # returns each formula in the selected sheet as an array of elements # [row, col, formula] def formulas(sheet=nil) theformulas = Array.new - sheet = @default_sheet unless sheet + sheet ||= @default_sheet read_cells(sheet) unless @cells_read[sheet] return theformulas unless first_row(sheet) # if there is no first row then # there can't be formulas first_row(sheet).upto(last_row(sheet)) {|row| first_column(sheet).upto(last_column(sheet)) {|col| @@ -461,65 +400,217 @@ } theformulas end =end + + + # FestivalBobcats fork changes begin here + + + + # access different worksheets by calling spreadsheet.sheet(1) + # or spreadsheet.sheet('SHEETNAME') + def sheet(index,name=false) + @default_sheet = String === index ? index : self.sheets[index] + name ? [@default_sheet,self] : self + end + + # iterate through all worksheets of a document + def each_with_pagename + self.sheets.each do |s| + yield sheet(s,true) + end + end + + # by passing in headers as options, this method returns + # specific columns from your header assignment + # for example: + # xls.sheet('New Prices').parse(:upc => 'UPC', :price => 'Price') would return: + # [{:upc => 123456789012, :price => 35.42},..] + + # the queries are matched with regex, so regex options can be passed in + # such as :price => '^(Cost|Price)' + # case insensitive by default + + + # by using the :header_search option, you can query for headers + # and return a hash of every row with the keys set to the header result + # for example: + # xls.sheet('New Prices').parse(:header_search => ['UPC*SKU','^Price*\sCost\s']) + + # that example searches for a column titled either UPC or SKU and another + # column titled either Price or Cost (regex characters allowed) + # * is the wildcard character + + # you can also pass in a :clean => true option to strip the sheet of + # odd unicode characters and white spaces around columns + + def each(options={}) + if options.empty? + 1.upto(last_row) do |line| + yield row(line) + end + else + if options[:clean] + options.delete(:clean) + @cleaned ||= {} + @cleaned[@default_sheet] || clean_sheet(@default_sheet) + end + + if options[:header_search] + @headers = nil + @header_line = row_with(options[:header_search]) + elsif [:first_row,true].include?(options[:headers]) + @headers = [] + row(first_row).each_with_index {|x,i| @headers << [x,i + 1]} + else + set_headers(options) + end + + headers = @headers || + Hash[(first_column..last_column).map do |col| + [cell(@header_line,col), col] + end] + + @header_line.upto(last_row) do |line| + yield(Hash[headers.map {|k,v| [k,cell(line,v)]}]) + end + end + end + + def parse(options={}) + ary = [] + if block_given? + each(options) {|row| ary << yield(row)} + else + each(options) {|row| ary << row} + end + ary + end + + def row_with(query,return_headers=false) + query.map! {|x| Array(x.split('*'))} + line_no = 0 + each do |row| + line_no += 1 + # makes sure headers is the first part of wildcard search for priority + # ex. if UPC and SKU exist for UPC*SKU search, UPC takes the cake + headers = query.map do |q| + q.map {|i| row.grep(/#{i}/i)[0]}.compact[0] + end.compact + + if headers.length == query.length + @header_line = line_no + return return_headers ? headers : line_no + elsif line_no > 100 + raise "Couldn't find header row." + end + end + end + + # this method lets you find the worksheet with the most data + def longest_sheet + sheet(@workbook.worksheets.inject {|m,o| + o.row_count > m.row_count ? o : m + }.name) + end + protected - def file_type_check(filename, ext, name, packed=nil) + def file_type_check(filename, ext, name, warning_level, packed=nil) new_expression = { - '.ods' => 'Openoffice.new', - '.xls' => 'Excel.new', - '.xlsx' => 'Excelx.new', - '.csv' => 'Csv.new', + '.ods' => 'Roo::Openoffice.new', + '.xls' => 'Roo::Excel.new', + '.xlsx' => 'Roo::Excelx.new', + '.csv' => 'Roo::Csv.new', } if packed == :zip # lalala.ods.zip => lalala.ods # hier wird KEIN unzip gemacht, sondern nur der Name der Datei # getestet, falls es eine gepackte Datei ist. filename = File.basename(filename,File.extname(filename)) end case ext when '.ods', '.xls', '.xlsx', '.csv' - correct_class = "use #{new_expression[ext]} to handle #{ext} spreadsheet files" + correct_class = "use #{new_expression[ext]} to handle #{ext} spreadsheet files. This has #{File.extname(filename).downcase}" else raise "unknown file type: #{ext}" end if File.extname(filename).downcase != ext - case @file_warning + case warning_level when :error warn correct_class raise TypeError, "#{filename} is not #{name} file" when :warning warn "are you sure, this is #{name} spreadsheet file?" warn correct_class when :ignore # ignore else - raise "#{@file_warning} illegal state of file_warning" + raise "#{warning_level} illegal state of file_warning" end end end # konvertiert einen Key in der Form "12,45" (=row,column) in # ein Array mit numerischen Werten ([12,45]) # Diese Methode ist eine temp. Loesung, um zu erforschen, ob der # Zugriff mit numerischen Keys schneller ist. def key_to_num(str) r,c = str.split(',') - r = r.to_i - c = c.to_i - [r,c] + [r.to_i,c.to_i] end # see: key_to_num def key_to_string(arr) "#{arr[0]},#{arr[1]}" end private + def make_tmpdir(tmp_root = nil) + Dir.mktmpdir(TEMP_PREFIX, tmp_root || ENV['ROO_TMP']) do |tmpdir| + yield tmpdir + end + end + + def clean_sheet(sheet) + read_cells(sheet) unless @cells_read[sheet] + @cell[sheet].each_pair do |coord,value| + if String === value + @cell[sheet][coord] = sanitize_value(value) + end + end + @cleaned[sheet] = true + end + + def sanitize_value(v) + v.strip.unpack('U*').select {|b| b < 127}.pack('U*') + end + + def set_headers(hash={}) + # try to find header row with all values or give an error + # then create new hash by indexing strings and keeping integers for header array + @headers = row_with(hash.values,true) + @headers = Hash[hash.keys.zip(@headers.map {|x| header_index(x)})] + end + + def header_index(query) + row(@header_line).index(query) + first_column + end + + def set_value(row,col,value,sheet=nil) + sheet ||= @default_sheet + @cell[sheet][[row,col]] = value + end + + def set_type(row,col,type,sheet=nil) + sheet ||= @default_sheet + @cell_type[sheet][[row,col]] = type + end + # converts cell coordinate to numeric values of row,col def normalize(row,col) if row.class == String if col.class == Fixnum # ('A',1): @@ -528,165 +619,178 @@ else raise ArgumentError end end if col.class == String - col = GenericSpreadsheet.letter_to_number(col) + col = Roo::GenericSpreadsheet.letter_to_number(col) end return row,col end - def open_from_uri(uri) + def uri?(filename) + filename.start_with?("http://", "https://") + end + + def open_from_uri(uri, tmpdir) require 'open-uri' response = '' begin open(uri, "User-Agent" => "Ruby/#{RUBY_VERSION}") { |net| response = net.read - tempfilename = File.join(@tmpdir, File.basename(uri)) - f = File.open(tempfilename,"wb") - f.write(response) - f.close + tempfilename = File.join(tmpdir, File.basename(uri)) + File.open(tempfilename,"wb") do |file| + file.write(response) + end } rescue OpenURI::HTTPError raise "could not open #{uri}" end - File.join(@tmpdir, File.basename(uri)) + File.join(tmpdir, File.basename(uri)) end - - def open_from_stream(stream) - tempfilename = File.join(@tmpdir, "spreadsheet") - f = File.open(tempfilename,"wb") - f.write(stream[7..-1]) - f.close - File.join(@tmpdir, "spreadsheet") + + def open_from_stream(stream, tmpdir) + tempfilename = File.join(tmpdir, "spreadsheet") + File.open(tempfilename,"wb") do |file| + file.write(stream[7..-1]) + end + File.join(tmpdir, "spreadsheet") end + LETTERS = %w{A B C D E F G H I J K L M N O P Q R S T U V W X Y Z} + # convert a number to something like 'AB' (1 => 'A', 2 => 'B', ...) def self.number_to_letter(n) letters="" - while n > 0 - num = n%26 - letters = "ABCDEFGHIJKLMNOPQRSTUVWXYZ"[num-1,1] + letters - n = n.div(26) + if n > 26 + while n % 26 == 0 && n != 0 + letters << 'Z' + n = (n - 26) / 26 + end + while n > 0 + num = n%26 + letters = LETTERS[num-1] + letters + n = (n / 26) + end + else + letters = LETTERS[n-1] end letters end # convert letters like 'AB' to a number ('A' => 1, 'B' => 2, ...) def self.letter_to_number(letters) result = 0 while letters && letters.length > 0 character = letters[0,1].upcase - num = "ABCDEFGHIJKLMNOPQRSTUVWXYZ".index(character) + num = LETTERS.index(character) raise ArgumentError, "invalid column character '#{letters[0,1]}'" if num == nil num += 1 result = result * 26 + num letters = letters[1..-1] end result end - def unzip(filename) - ret = nil + def unzip(filename, tmpdir) Zip::ZipFile.open(filename) do |zip| - ret = process_zipfile_packed zip + process_zipfile_packed(zip, tmpdir) end - ret end # check if default_sheet was set and exists in sheets-array - def check_default_sheet - sheet_found = false - raise ArgumentError, "Error: default_sheet not set" if @default_sheet == nil - if sheets.index(@default_sheet) - sheet_found = true + def validate_sheet!(sheet) + case sheet + when nil + raise ArgumentError, "Error: sheet 'nil' not valid" + when Fixnum + self.sheets.fetch(sheet-1) do + raise RangeError, "sheet index #{sheet} not found" + end + when String + if !sheets.include? sheet + raise RangeError, "sheet '#{sheet}' not found" + end + else + raise TypeError, "not a valid sheet type: #{sheet.inspect}" end - if ! sheet_found - raise RangeError, "sheet '#{@default_sheet}' not found" - end end - def process_zipfile_packed(zip, path='') - ret=nil + def process_zipfile_packed(zip, tmpdir, path='') if zip.file.file? path # extract and return filename - file = File.open(File.join(@tmpdir, path),"wb") - file.write(zip.read(path)) - file.close - return File.join(@tmpdir, path) - else - unless path.empty? - path += '/' + File.open(File.join(tmpdir, path),"wb") do |file| + file.write(zip.read(path)) end + File.join(tmpdir, path) + else + ret=nil + path += '/' unless path.empty? zip.dir.foreach(path) do |filename| - ret = process_zipfile_packed(zip, path + filename) + ret = process_zipfile_packed(zip, tmpdir, path + filename) end + ret end - ret end # Write all cells to the csv file. File can be a filename or nil. If the this # parameter is nil the output goes to STDOUT def write_csv_content(file=nil,sheet=nil) - file = STDOUT unless file + file ||= STDOUT if first_row(sheet) # sheet is not empty 1.upto(last_row(sheet)) do |row| 1.upto(last_column(sheet)) do |col| file.print(",") if col > 1 - onecell = cell(row,col,sheet) - onecelltype = celltype(row,col,sheet) - file.print one_cell_output(onecelltype,onecell,empty?(row,col,sheet)) + file.print cell_to_csv(row,col,sheet) end file.print("\n") end # sheet not empty end end - # The content of a cell in the csv output - def one_cell_output(onecelltype, onecell, empty) - str = "" - if empty - str += '' + # The content of a cell in the csv output + def cell_to_csv(row, col, sheet) + if empty?(row,col,sheet) + '' else - case onecelltype + onecell = cell(row,col,sheet) + + case celltype(row,col,sheet) when :string unless onecell.empty? - one = onecell.gsub(/"/,'""') - str << ('"'+one+'"') + %{"#{onecell.gsub(/"/,'""')}"} end when :float, :percentage if onecell == onecell.to_i - str << onecell.to_i.to_s + onecell.to_i.to_s else - str << onecell.to_s + onecell.to_s end when :formula - if onecell.class == String + case onecell + when String unless onecell.empty? - one = onecell.gsub(/"/,'""') - str << '"'+one+'"' + %{"#{onecell.gsub(/"/,'""')}"} end - elsif onecell.class == Float + when Float if onecell == onecell.to_i - str << onecell.to_i.to_s + onecell.to_i.to_s else - str << onecell.to_s + onecell.to_s end + when DateTime + onecell.to_s else - raise "unhandled onecell-class "+onecell.class.to_s + raise "unhandled onecell-class #{onecell.class}" end - when :date - str << onecell.to_s + when :date, :datetime + onecell.to_s when :time - str << GenericSpreadsheet.integer_to_timestring(onecell) - when :datetime - str << onecell.to_s + Roo::GenericSpreadsheet.integer_to_timestring(onecell) else - raise "unhandled celltype "+onecelltype.to_s - end + raise "unhandled celltype #{celltype(row,col,sheet)}" + end || "" end - str end # converts an integer value to a time string like '02:05:06' def self.integer_to_timestring(content) h = (content/3600.0).floor @@ -694,7 +798,6 @@ m = (content/60.0).floor content = content - m*60 s = content sprintf("%02d:%02d:%02d",h,m,s) end - end