module Oxcelix::Numformats

The Numformats module provides helper methods that either return the Cell object’s raw @value as a ruby value (e.g. Numeric, DateTime, String) or formats it according to the excel numformat string (Cell.numformat).

Constants

Dtmap

Map containing the Excel formatting strings and their ruby counterpart

Formatarray
Formatarray is the array of default format strings in Excel. Nil values should apparently contain CJK date format strings,

feel free to add/document those according to existing standards.

Public Instance Methods

add_custom_formats(fmtary) click to toggle source

Convert the temporary format array (the collection of non-default number formatting strings defined in the excel sheet in use) to a series of hashes containing an id, an excel format string, a converted format string and an object class the format is interpreted on.

# File lib/oxcelix/numformats.rb, line 14
def add_custom_formats fmtary
  fmtary.each do |x|
    if x[:formatCode] =~ /[#0%\?]/
      ostring = numeric x[:formatCode]
      if x[:formatCode] =~ /\//
        cls = 'rational'
      else
        cls = 'numeric'
      end
    elsif x[:formatCode].downcase =~ /[dmysh]/
      ostring = datetime x[:formatCode]
      cls = 'date'
    elsif x[:formatCode].downcase == "general"
      ostring = nil
      cls = 'string'
    end
    Formatarray << {:id => x[:numFmtId].to_s, :xl => x[:formatCode].to_s, :ostring => ostring, :cls => cls}
  end
end
datetime(formatcode) click to toggle source

Convert excel-style date formats into ruby DateTime strftime format strings @param [String] formatcode an Excel number format string. @return [String] a DateTime::strftime format string.

# File lib/oxcelix/numformats.rb, line 67
def datetime formatcode
  deminutified = formatcode.downcase.gsub(/(?<hrs>H|h)(?<div>.)m/, '\k<hrs>\k<div>i')
                   .gsub(/im/, 'ii')
                   .gsub(/m(?<div>.)(?<secs>s)/, 'i\k<div>\k<secs>')
                   .gsub(/mi/, 'ii')
  return deminutified.gsub(/[yMmDdHhSsi]*/, Dtmap)
end
numeric(val) click to toggle source

Convert the excel-style number format to a ruby Kernel::Format string and return that String. The conversion is internally done by regexp’ing 7 groups: prefix, decimals, separator, floats, exponential (E+) and postfix. Rational numbers ar not handled yet. @param [String] val an Excel number format string. @return [String] a rubyish Kernel::Format string.

# File lib/oxcelix/numformats.rb, line 39
def numeric val
  ostring = "%"
  strippedfmt = val.gsub(/\?/, '0').gsub(',','')
  prefix, decimals, sep, floats, expo, postfix=/(^[^\#0e].?)?([\#0]*)?(\.)?([\#0]*)?(e.?)?(.?[^\#0e]$)?/i.match(strippedfmt).captures
  ostring.prepend prefix.to_s
  if !decimals.nil? && decimals.size != 0
    if (eval decimals) == nil
      ostring += "##{decimals.size}"
    elsif (eval decimals) == 0
      ostring += decimals.size.to_s
    end
  else
    ostring += decimals
  end
  ostring += sep.to_s
  if !floats.nil? && floats.size != 0 # expo!!!
    ostring += ((floats.size.to_s) +"f")
  end
  if sep.nil? && floats.nil? || floats.size == 0
    ostring += "d"
  end
  ostring += (expo.to_s + postfix.to_s) #postfix '+' ?
  return ostring
end