Class: Formula

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

Overview

:nodoc:

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
31
 
# File 'lib/writeexcel/formula.rb', line 23

def initialize(byte_order)
  @byte_order     = byte_order
  @workbook       = ""
  @ext_sheets     = {}
  @ext_refs       = {}
  @ext_ref_count  = 0
  @ext_names      = {}
  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 get the worksheet references that were used in formulas for inclusion in the EXTERNSHEET Workbook record.



528
529
530
 
# File 'lib/writeexcel/formula.rb', line 528

def get_ext_sheets
  @ext_refs
end

#get_sheet_index(sheet_name) ⇒ Object

_get_sheet_index()

Look up the index that corresponds to an external sheet name. The hash of sheet names is updated by the add_worksheet() method of the Workbook class.



493
494
495
496
497
498
499
500
501
502
503
504
 
# File 'lib/writeexcel/formula.rb', line 493

def get_sheet_index(sheet_name)
  # Handle utf8 sheetnames
  if sheet_name =~ NonAscii
    sheet_name = NKF.nkf('-w16B0 -m0 -W', sheet_name)
  end

  if @ext_sheets[sheet_name].nil?
    raise "Unknown sheet name '#{sheet_name}' in formula\n"
  else
    return @ext_sheets[sheet_name]
  end
end

#next_token ⇒ Object 



212
213
214
 
# File 'lib/writeexcel/formula.rb', line 212

def next_token
  @q.shift
end

#parse(formula) ⇒ Object 



205
206
207
208
209
210
 
# File 'lib/writeexcel/formula.rb', line 205

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.



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

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()
    #
    parse_tokens(tokens)
    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.



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
148
149
150
151
152
153
154
155
 
# File 'lib/writeexcel/formula.rb', line 68

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 =~ /^_name/)
      modifier = token.sub(/_name/, '')
      _class      = _classary[-1]
      _class      = 0 if modifier == 'R'
      _class      = 1 if modifier == 'V'
      token      = args.shift
      parse_str = parse_str + convert_name(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 



216
217
218
 
# File 'lib/writeexcel/formula.rb', line 216

def reverse(expression)
  expression.flatten
end

#scan(formula) ⇒ Object 



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
192
193
194
195
196
197
198
199
200
201
202
203
 
# File 'lib/writeexcel/formula.rb', line 157

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+)?:\$?[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(/[A-Z0-9_.]+/)
      q.push [:FUNC,   s.matched]
    elsif s.scan(/[A-Za-z_]\w+/)
      q.push [:NAME , s.matched]
    elsif s.scan(/TRUE/)
      q.push [:TRUE, s.matched]
    elsif s.scan(/FALSE/)
      q.push [:FALSE, s.matched]
    elsif s.scan(/\s+/)
      ;
    elsif s.scan(/./)
      q.push [s.matched, s.matched]
    end
  end
  q.push [:EOL, nil]
end

#set_ext_name(name, index) ⇒ Object

set_ext_name()

This semi-public method is used to update the hash of defined names.



566
567
568
 
# File 'lib/writeexcel/formula.rb', line 566

def set_ext_name(name, index)
  @ext_names[name] = index
end

#set_ext_sheets(worksheet, index) ⇒ Object

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



514
515
516
517
518
 
# File 'lib/writeexcel/formula.rb', line 514

def set_ext_sheets(worksheet, index)
  # The _ext_sheets hash is used to translate between worksheet names
  # and their index
  @ext_sheets[worksheet] = index
end