Class: Formula

Inherits:
ExcelFormulaParser show all
Defined in:
lib/WriteExcel/formula.rb

Overview

Formula - A class for generating Excel formulas.

Used in conjunction with Spreadsheet::WriteExcel

Copyright 2000-2008, John McNamara, [email protected]

original written in Perl by John McNamara converted to Ruby by Hideo Nakamura, [email protected]

require ‘nkf’ require ‘strscan’ require ‘WriteExcel/excelformulaparser’

Constant Summary collapse

NonAscii = 
/[^!"#\$%&'\(\)\*\+,\-\.\/\:\;<=>\?@0-9A-Za-z_\[\\\]^` ~\0\n]/

Constants inherited from ExcelFormulaParser

ExcelFormulaParser::Racc_arg, ExcelFormulaParser::Racc_debug_parser, ExcelFormulaParser::Racc_token_to_s_table

Instance Attribute Summary collapse

Instance Method Summary collapse

Methods inherited from ExcelFormulaParser

#_reduce_none

Constructor Details

#initialize(byte_order) ⇒ Formula

Returns a new instance of Formula.



23
24
25
26
27
28
29
30
 
# File 'lib/WriteExcel/formula.rb', line 23

def initialize(byte_order)
  @byte_order     = byte_order
  @workbook       = ""
  @ext_sheets     = {}
  @ext_refs       = {}
  @ext_ref_count  = 0
  initialize_hashes
end

Instance Attribute Details

#byte_order ⇒ Object

Returns the value of attribute byte_order.



21
22
23
 
# File 'lib/WriteExcel/formula.rb', line 21

def byte_order
  @byte_order
end

#ext_ref_count ⇒ Object

Returns the value of attribute ext_ref_count.



21
22
23
 
# File 'lib/WriteExcel/formula.rb', line 21

def ext_ref_count
  @ext_ref_count
end

#ext_refs ⇒ Object

Returns the value of attribute ext_refs.



21
22
23
 
# File 'lib/WriteExcel/formula.rb', line 21

def ext_refs
  @ext_refs
end

#ext_sheets ⇒ Object

Returns the value of attribute ext_sheets.



21
22
23
 
# File 'lib/WriteExcel/formula.rb', line 21

def ext_sheets
  @ext_sheets
end

#workbook ⇒ Object

Returns the value of attribute workbook.



21
22
23
 
# File 'lib/WriteExcel/formula.rb', line 21

def workbook
  @workbook
end

Instance Method Details

#get_ext_sheets ⇒ Object

get_ext_sheets()

This semi-public method is used to update the hash of sheet names. It is updated by the add_worksheet() method of the Workbook class.

TODO



217
218
219
220
221
222
223
224
225
226
227
228
229
 
# File 'lib/WriteExcel/formula.rb', line 217

def get_ext_sheets
  # TODO
  refs = @ext_refs
  return refs

  #my @refs = sort {$refs{$a} <=> $refs{$b}} keys %refs;

  #foreach my $ref (@refs) {
  #    $ref = [split /:/, $ref];
  #}

  #return @refs;
end

#next_token ⇒ Object 



200
201
202
 
# File 'lib/WriteExcel/formula.rb', line 200

def next_token
  @q.shift
end

#parse(formula) ⇒ Object 



193
194
195
196
197
198
 
# File 'lib/WriteExcel/formula.rb', line 193

def parse(formula)
  @q = scan(formula)
  @q.push [false, nil]
@yydebug=true
  do_parse
end

#parse_formula(formula, byte_stream = false) ⇒ Object

parse_formula()

Takes a textual description of a formula and returns a RPN encoded byte string.



39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
 
# File 'lib/WriteExcel/formula.rb', line 39

def parse_formula(formula, byte_stream = false)
  # Build the parse tree for the formula
  tokens = reverse(parse(formula))

  # Add a volatile token if the formula contains a volatile function.
  # This must be the first token in the list
  #
  tokens.unshift('_vol') if check_volatile(tokens) != 0

  # The return value depends on which Worksheet.pm method is the caller
  unless byte_stream
    # Parse formula to see if it throws any errors and then
    # return raw tokens to Worksheet::store_formula()
    #
    tokens
  else
    # Return byte stream to Worksheet::write_formula()
    parse_tokens(tokens)
  end
end

#parse_tokens(tokens) ⇒ Object

parse_tokens()

Convert each token or token pair to its Excel ‘ptg’ equivalent.



66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
 
# File 'lib/WriteExcel/formula.rb', line 66

def parse_tokens(tokens)
  parse_str   = ''
  last_type   = ''
  modifier    = ''
  num_args    = 0
  _class      = 0
  _classary   = [1]
  args        = tokens.dup
  # A note about the class modifiers used below. In general the class,
  # "reference" or "value", of a function is applied to all of its operands.
  # However, in certain circumstances the operands can have mixed classes,
  # e.g. =VLOOKUP with external references. These will eventually be dealt
  # with by the parser. However, as a workaround the class type of a token
  # can be changed via the repeat_formula interface. Thus, a _ref2d token can
  # be changed by the user to _ref2dA or _ref2dR to change its token class.
  #
  while (!args.empty?)
    token = args.shift

    if (token == '_arg')
      num_args = args.shift
    elsif (token == '_class')
      token = args.shift
      _class = @functions[token][2]
      # If _class is undef then it means that the function isn't valid.
      exit "Unknown function #{token}() in formula\n" if _class.nil?
      _classary.push(_class)
    elsif (token == '_vol')
      parse_str = parse_str + convert_volatile()
    elsif (token == 'ptgBool')
      token = args.shift
      parse_str = parse_str + convert_bool(token)
    elsif (token == '_num')
      token = args.shift
      parse_str = parse_str + convert_number(token)
    elsif (token == '_str')
      token = args.shift
      parse_str = parse_str + convert_string(token)
    elsif (token =~ /^_ref2d/)
      modifier  = token.sub(/_ref2d/, '')
      _class      = _classary[-1]
      _class      = 0 if modifier == 'R'
      _class      = 1 if modifier == 'V'
      token      = args.shift
      parse_str = parse_str + convert_ref2d(token, _class)
    elsif (token =~ /^_ref3d/)
      modifier  = token.sub(/_ref3d/,'')
      _class      = _classary[-1]
      _class      = 0 if modifier == 'R'
      _class      = 1 if modifier == 'V'
      token      = args.shift
      parse_str = parse_str + convert_ref3d(token, _class)
    elsif (token =~ /^_range2d/)
      modifier  = token.sub(/_range2d/,'')
      _class      = _classary[-1]
      _class      = 0 if modifier == 'R'
      _class      = 1 if modifier == 'V'
      token      = args.shift
      parse_str = parse_str + convert_range2d(token, _class)
    elsif (token =~ /^_range3d/)
      modifier  = token.sub(/_range3d/,'')
      _class      = _classary[-1]
      _class      = 0 if modifier == 'R'
      _class      = 1 if modifier == 'V'
      token      = args.shift
      parse_str = parse_str + convert_range3d(token, _class)
    elsif (token == '_func')
      token = args.shift
      parse_str = parse_str + convert_function(token, num_args.to_i)
      _classary.pop
      num_args = 0 # Reset after use
    elsif @ptg[token]
      parse_str = parse_str + [@ptg[token]].pack("C")
    else
      # Unrecognised token
      return nil
    end
  end


  return parse_str
end

#reverse(expression) ⇒ Object 



204
205
206
 
# File 'lib/WriteExcel/formula.rb', line 204

def reverse(expression)
  expression.flatten
end

#scan(formula) ⇒ Object 



149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
 
# File 'lib/WriteExcel/formula.rb', line 149

def scan(formula)
  s = StringScanner.new(formula)
  q = []
  until s.eos?
    # order is important.
    if    s.scan(/(?=\d|\.\d)\d*(\.\d*)?([Ee]([+-]?\d+))?/)
      q.push [:NUMBER, s.matched]
    elsif s.scan(/"([^"]|"")*"/)
      q.push [:STRING, s.matched]
    elsif s.scan(/\$?[A-I]?[A-Z]\$?(\d+)?:\$?[A-I]?[A-Z]\$?(\d+)?/)
      q.push [:RANGE2D , s.matched]
    elsif s.scan(/[^!(,]+!\$?[A-I]?[A-Z]\$?(\d+)?:\$?[A-I]?[A-Z]\$?(\d+)?/)
      q.push [:RANGE3D , s.matched]
    elsif s.scan(/\$?[A-I]?[A-Z]\$?\d+/)
      q.push [:REF2D,  s.matched]
    elsif s.scan(/[^!(,]+!\$?[A-I]?[A-Z]\$?\d+/)
      q.push [:REF3D , s.matched]
    elsif s.scan(/'[^']+'!\$?[A-I]?[A-Z]\$?\d+/)
      q.push [:REF3D , s.matched]
    elsif s.scan(/<=/)
      q.push [:LE , s.matched]
    elsif s.scan(/>=/)
      q.push [:GE , s.matched]
    elsif s.scan(/<>/)
      q.push [:NE , s.matched]
    elsif s.scan(/</)
      q.push [:LT , s.matched]
    elsif s.scan(/>/)
      q.push [:GT , s.matched]
    elsif s.scan(/TRUE/)
      q.push [:TRUE, s.matched]
    elsif s.scan(/FALSE/)
      q.push [:FALSE, s.matched]
    elsif s.scan(/[A-Z0-9_.]+/)
      q.push [:FUNC,   s.matched]
    elsif s.scan(/\s+/)
      ;
    elsif s.scan(/./)
      q.push [s.matched, s.matched]
    end
  end
  q.push [:EOL, nil]
end