Module: Oxcelix::Numformats

Included in:
Numberhelper, Workbook
Defined in:
lib/oxcelix/numformats.rb,
lib/oxcelix/nf.rb

Overview

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

Constant Summary collapse

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.

[
  {:id => '0', :xl => 'General', :ostring => nil, :cls => 'string'},
  {:id => '1', :xl => '0', :ostring => '%1d', :cls => 'numeric'},
  {:id => '2', :xl => '0.00', :ostring => '%1.2f', :cls => 'numeric'},
  {:id => '3', :xl => '#,##0', :ostring => '%#4d', :cls => 'numeric'},
  {:id => '4', :xl => '#,##0.00', :ostring => '%#4.2f', :cls => 'numeric'},
  {:id => '5', :xl => '', :ostring => nil, :cls => 'string'},
  {:id => '6', :xl => '', :ostring => nil, :cls => 'string'},
  {:id => '7', :xl => '', :ostring => nil, :cls => 'string'},
  {:id => '8', :xl => '', :ostring => nil, :cls => 'string'},
  {:id => '9', :xl => '0%', :ostring => '%1d%', :cls => 'numeric'},
  {:id => '10', :xl => '0.00%', :ostring => '%1.2f%', :cls => 'numeric'},
  {:id => '11', :xl => '0.00E+00', :ostring => '%1.2fE+', :cls => 'numeric'},
  {:id => '12', :xl => '# ?/?', :ostring => '%#1d', :cls => 'rational'},
  {:id => '13', :xl => '# ??/??', :ostring => '%#1d', :cls => 'rational'},
  {:id => '14', :xl => 'd/m/yyyy', :ostring => '%-d/%-m/%Y', :cls => 'date'},
  {:id => '15', :xl => 'd-mmm-yy', :ostring => '%-d-%b-%y', :cls => 'date'},
  {:id => '16', :xl => 'd-mmm', :ostring => '%-d-%b', :cls => 'date'},
  {:id => '17', :xl => 'mmm-yy', :ostring => '%b-%y', :cls => 'date'},
  {:id => '18', :xl => 'h:mm tt', :ostring => '%-k:%M tt', :cls => 'date'},
  {:id => '19', :xl => 'h:mm:ss tt', :ostring => '%-k:%M:%-S tt', :cls => 'date'},
  {:id => '20', :xl => 'H:mm', :ostring => '%-k:%M', :cls => 'date'},
  {:id => '21', :xl => 'H:mm:ss', :ostring => '%-k:%M:%-S', :cls => 'date'},
  {:id => '22', :xl => 'm/d/yyyy H:mm', :ostring => '%-m/%-d/%Y %-k:%M', :cls => 'date'},
  {:id => '23', :xl => '', :ostring => nil, :cls => 'string'},
  {:id => '24', :xl => '', :ostring => nil, :cls => 'string'},
  {:id => '25', :xl => '', :ostring => nil, :cls => 'string'},
  {:id => '26', :xl => '', :ostring => nil, :cls => 'string'},
  {:id => '27', :xl => '', :ostring => nil, :cls => 'string'},
  {:id => '28', :xl => '', :ostring => nil, :cls => 'string'},
  {:id => '29', :xl => '', :ostring => nil, :cls => 'string'},
  {:id => '30', :xl => '', :ostring => nil, :cls => 'string'},
  {:id => '31', :xl => '', :ostring => nil, :cls => 'string'},
  {:id => '32', :xl => '', :ostring => nil, :cls => 'string'},
  {:id => '33', :xl => '', :ostring => nil, :cls => 'string'},
  {:id => '34', :xl => '', :ostring => nil, :cls => 'string'},
  {:id => '35', :xl => '', :ostring => nil, :cls => 'string'},
  {:id => '36', :xl => '', :ostring => nil, :cls => 'string'},
  {:id => '37', :xl => '#,##0 ;(#,##0)', :ostring => '%#4d', :cls => 'numeric'},
  {:id => '38', :xl => '#,##0 ;[Red](#,##0)', :ostring => '%#4d', :cls => 'numeric'},
  {:id => '39', :xl => '#,##0.00;(#,##0.00)', :ostring => '%#4.2f', :cls => 'numeric'},
  {:id => '40', :xl => '#,##0.00;[Red](#,##0.00)', :ostring => '%#4.2f', :cls => 'numeric'},
  {:id => '41', :xl => '', :ostring => nil, :cls => 'string'},
  {:id => '42', :xl => '', :ostring => nil, :cls => 'string'},
  {:id => '43', :xl => '', :ostring => nil, :cls => 'string'},
  {:id => '44', :xl => '', :ostring => nil, :cls => 'string'},
  {:id => '45', :xl => 'mm:ss', :ostring => '%M:%-S', :cls => 'date'},
  {:id => '46', :xl => '[h]:mm:ss', :ostring => '%-k:%M:%-S', :cls => 'date'},
  {:id => '47', :xl => 'mmss.0', :ostring => '%M%-S.%1n', :cls => 'date'},
  {:id => '48', :xl => '##0.0E+0', :ostring => '%#3.1E', :cls => 'numeric'},
  {:id => '49', :xl => 'Text', :ostring => nil, :cls => 'string'},
  {:id => '50', :xl => '', :ostring => nil, :cls => 'string'},
  {:id => '51', :xl => '', :ostring => nil, :cls => 'string'},
  {:id => '52', :xl => '', :ostring => nil, :cls => 'string'},
  {:id => '53', :xl => '', :ostring => nil, :cls => 'string'},
  {:id => '54', :xl => '', :ostring => nil, :cls => 'string'},
  {:id => '55', :xl => '', :ostring => nil, :cls => 'string'},
  {:id => '56', :xl => '', :ostring => nil, :cls => 'string'},
  {:id => '57', :xl => '', :ostring => nil, :cls => 'string'},
  {:id => '58', :xl => '', :ostring => nil, :cls => 'string'},
  {:id => '59', :xl => '', :ostring => nil, :cls => 'string'},
  {:id => '60', :xl => '', :ostring => nil, :cls => 'string'},
  {:id => '61', :xl => '', :ostring => nil, :cls => 'string'},
  {:id => '62', :xl => '', :ostring => nil, :cls => 'string'},
  {:id => '63', :xl => '', :ostring => nil, :cls => 'string'},
  {:id => '64', :xl => '', :ostring => nil, :cls => 'string'},
  {:id => '65', :xl => '', :ostring => nil, :cls => 'string'},
  {:id => '66', :xl => '', :ostring => nil, :cls => 'string'},
  {:id => '67', :xl => '', :ostring => nil, :cls => 'string'},
  {:id => '68', :xl => '', :ostring => nil, :cls => 'string'},
  {:id => '69', :xl => '', :ostring => nil, :cls => 'string'},
  {:id => '70', :xl => '', :ostring => nil, :cls => 'string'},
  {:id => '71', :xl => '', :ostring => nil, :cls => 'string'},
  {:id => '72', :xl => '', :ostring => nil, :cls => 'string'},
  {:id => '73', :xl => '', :ostring => nil, :cls => 'string'},
  {:id => '74', :xl => '', :ostring => nil, :cls => 'string'},
  {:id => '75', :xl => '', :ostring => nil, :cls => 'string'},
  {:id => '76', :xl => '', :ostring => nil, :cls => 'string'},
  {:id => '77', :xl => '', :ostring => nil, :cls => 'string'},
  {:id => '78', :xl => '', :ostring => nil, :cls => 'string'},
  {:id => '79', :xl => '', :ostring => nil, :cls => 'string'},
  {:id => '80', :xl => '', :ostring => nil, :cls => 'string'},
  {:id => '81', :xl => '', :ostring => nil, :cls => 'string'},
  {:id => '82', :xl => '', :ostring => nil, :cls => 'string'},
  {:id => '83', :xl => '', :ostring => nil, :cls => 'string'},
  {:id => '84', :xl => '', :ostring => nil, :cls => 'string'},
  {:id => '85', :xl => '', :ostring => nil, :cls => 'string'},
  {:id => '86', :xl => '', :ostring => nil, :cls => 'string'},
  {:id => '87', :xl => '', :ostring => nil, :cls => 'string'},
  {:id => '88', :xl => '', :ostring => nil, :cls => 'string'},
  {:id => '89', :xl => '', :ostring => nil, :cls => 'string'},
  {:id => '90', :xl => '', :ostring => nil, :cls => 'string'},
  {:id => '91', :xl => '', :ostring => nil, :cls => 'string'},
  {:id => '92', :xl => '', :ostring => nil, :cls => 'string'},
  {:id => '93', :xl => '', :ostring => nil, :cls => 'string'},
  {:id => '94', :xl => '', :ostring => nil, :cls => 'string'},
  {:id => '95', :xl => '', :ostring => nil, :cls => 'string'},
  {:id => '96', :xl => '', :ostring => nil, :cls => 'string'},
  {:id => '97', :xl => '', :ostring => nil, :cls => 'string'},
  {:id => '98', :xl => '', :ostring => nil, :cls => 'string'},
  {:id => '99', :xl => '', :ostring => nil, :cls => 'string'},
  {:id => '100', :xl => '', :ostring => nil, :cls => 'string'},
  {:id => '101', :xl => '', :ostring => nil, :cls => 'string'},
  {:id => '102', :xl => '', :ostring => nil, :cls => 'string'},
  {:id => '103', :xl => '', :ostring => nil, :cls => 'string'},
  {:id => '104', :xl => '', :ostring => nil, :cls => 'string'},
  {:id => '105', :xl => '', :ostring => nil, :cls => 'string'},
  {:id => '106', :xl => '', :ostring => nil, :cls => 'string'},
  {:id => '107', :xl => '', :ostring => nil, :cls => 'string'},
  {:id => '108', :xl => '', :ostring => nil, :cls => 'string'},
  {:id => '109', :xl => '', :ostring => nil, :cls => 'string'},
  {:id => '110', :xl => '', :ostring => nil, :cls => 'string'},
  {:id => '111', :xl => '', :ostring => nil, :cls => 'string'},
  {:id => '112', :xl => '', :ostring => nil, :cls => 'string'},
  {:id => '113', :xl => '', :ostring => nil, :cls => 'string'},
  {:id => '114', :xl => '', :ostring => nil, :cls => 'string'},
  {:id => '115', :xl => '', :ostring => nil, :cls => 'string'},
  {:id => '116', :xl => '', :ostring => nil, :cls => 'string'},
  {:id => '117', :xl => '', :ostring => nil, :cls => 'string'},
  {:id => '118', :xl => '', :ostring => nil, :cls => 'string'},
  {:id => '119', :xl => '', :ostring => nil, :cls => 'string'},
  {:id => '120', :xl => '', :ostring => nil, :cls => 'string'},
  {:id => '121', :xl => '', :ostring => nil, :cls => 'string'},
  {:id => '122', :xl => '', :ostring => nil, :cls => 'string'},
  {:id => '123', :xl => '', :ostring => nil, :cls => 'string'},
  {:id => '124', :xl => '', :ostring => nil, :cls => 'string'},
  {:id => '125', :xl => '', :ostring => nil, :cls => 'string'},
  {:id => '126', :xl => '', :ostring => nil, :cls => 'string'},
  {:id => '127', :xl => '', :ostring => nil, :cls => 'string'},
  {:id => '128', :xl => '', :ostring => nil, :cls => 'string'},
  {:id => '129', :xl => '', :ostring => nil, :cls => 'string'},
  {:id => '130', :xl => '', :ostring => nil, :cls => 'string'},
  {:id => '131', :xl => '', :ostring => nil, :cls => 'string'},
  {:id => '132', :xl => '', :ostring => nil, :cls => 'string'},
  {:id => '133', :xl => '', :ostring => nil, :cls => 'string'},
  {:id => '134', :xl => '', :ostring => nil, :cls => 'string'},
  {:id => '135', :xl => '', :ostring => nil, :cls => 'string'},
  {:id => '136', :xl => '', :ostring => nil, :cls => 'string'},
  {:id => '137', :xl => '', :ostring => nil, :cls => 'string'},
  {:id => '138', :xl => '', :ostring => nil, :cls => 'string'},
  {:id => '139', :xl => '', :ostring => nil, :cls => 'string'},
  {:id => '140', :xl => '', :ostring => nil, :cls => 'string'},
  {:id => '141', :xl => '', :ostring => nil, :cls => 'string'},
  {:id => '142', :xl => '', :ostring => nil, :cls => 'string'},
  {:id => '143', :xl => '', :ostring => nil, :cls => 'string'},
  {:id => '144', :xl => '', :ostring => nil, :cls => 'string'},
  {:id => '145', :xl => '', :ostring => nil, :cls => 'string'},
  {:id => '146', :xl => '', :ostring => nil, :cls => 'string'},
  {:id => '147', :xl => '', :ostring => nil, :cls => 'string'},
  {:id => '148', :xl => '', :ostring => nil, :cls => 'string'},
  {:id => '149', :xl => '', :ostring => nil, :cls => 'string'},
  {:id => '150', :xl => '', :ostring => nil, :cls => 'string'},
  {:id => '151', :xl => '', :ostring => nil, :cls => 'string'},
  {:id => '152', :xl => '', :ostring => nil, :cls => 'string'},
  {:id => '153', :xl => '', :ostring => nil, :cls => 'string'},
  {:id => '154', :xl => '', :ostring => nil, :cls => 'string'},
  {:id => '155', :xl => '', :ostring => nil, :cls => 'string'},
  {:id => '156', :xl => '', :ostring => nil, :cls => 'string'},
  {:id => '157', :xl => '', :ostring => nil, :cls => 'string'},
  {:id => '158', :xl => '', :ostring => nil, :cls => 'string'},
  {:id => '159', :xl => '', :ostring => nil, :cls => 'string'},
  {:id => '160', :xl => '', :ostring => nil, :cls => 'string'},
  {:id => '161', :xl => '', :ostring => nil, :cls => 'string'},
  {:id => '162', :xl => '', :ostring => nil, :cls => 'string'},
  {:id => '163', :xl => '', :ostring => nil, :cls => 'string'},
]
Dtmap =

Map containing the Excel formatting strings and their ruby counterpart

{'hh'=>'%H', 'ii'=>'%M', 'i'=>'%-M', 'H'=>'%-k', 'h'=>'%-k',\
'ss'=>'%-S', 's'=>'%S', 'mmmmm'=>'%b', 'mmmm'=>'%B', 'mmm'=>'%b', 'mm'=>'%m', \
'm'=>'%-m', 'dddd'=>'%A', 'ddd'=>'%a', 'dd'=>'%d', 'd'=>'%-d', 'yyyy'=>'%Y', \
'yy'=>'%y', 'AM/PM'=>'%p', 'A/P'=>'%p', '.0'=>''}

Instance Method Summary collapse

Instance Method Details

#add_custom_formats(fmtary) ⇒ Object

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.


14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
# 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) ⇒ String

Convert excel-style date formats into ruby DateTime strftime format strings


67
68
69
70
71
72
73
# 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) ⇒ String

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.


39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
# 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