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
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
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
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