Class: Writeexcel::Formula

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

Overview

:nodoc:

Constant Summary

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.



25
26
27
28
29
30
31
32
33
 
# File 'lib/writeexcel/formula.rb', line 25

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

#ext_ref_count ⇒ Object

Returns the value of attribute ext_ref_count.



23
24
25
 
# File 'lib/writeexcel/formula.rb', line 23

def ext_ref_count
  @ext_ref_count
end

#ext_refs ⇒ Object

Returns the value of attribute ext_refs.



23
24
25
 
# File 'lib/writeexcel/formula.rb', line 23

def ext_refs
  @ext_refs
end

#ext_sheets ⇒ Object

Returns the value of attribute ext_sheets.



23
24
25
 
# File 'lib/writeexcel/formula.rb', line 23

def ext_sheets
  @ext_sheets
end

#workbook ⇒ Object

Returns the value of attribute workbook.



23
24
25
 
# File 'lib/writeexcel/formula.rb', line 23

def workbook
  @workbook
end

Instance Method Details

#get_ext_sheets ⇒ Object

This semi-public method is used to get the worksheet references that were used in formulas for inclusion in the EXTERNSHEET Workbook record.



494
495
496
 
# File 'lib/writeexcel/formula.rb', line 494

def get_ext_sheets
  @ext_refs
end

#get_sheet_index(sheet_name) ⇒ Object

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.



462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
 
# File 'lib/writeexcel/formula.rb', line 462

def get_sheet_index(sheet_name)
  ruby_19 { sheet_name = convert_to_ascii_if_ascii(sheet_name) }

  # Handle utf8 sheetnames
  if is_utf8?(sheet_name)
    ruby_18 { sheet_name = utf8_to_16be(sheet_name) } ||
    ruby_19 { sheet_name = sheet_name.encode('UTF-16BE') }
  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 



209
210
211
 
# File 'lib/writeexcel/formula.rb', line 209

def next_token
  @q.shift
end

#parse(formula) ⇒ Object 



202
203
204
205
206
207
 
# File 'lib/writeexcel/formula.rb', line 202

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

#parse_formula(formula, byte_stream = false) ⇒ Object

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
59
 
# 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()
    #
    parse_tokens(tokens)
    tokens
  else
    # Return byte stream to Worksheet::write_formula()
    parse_tokens(tokens)
  end
end

#parse_tokens(tokens) ⇒ Object

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



64
65
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
148
149
150
 
# File 'lib/writeexcel/formula.rb', line 64

def parse_tokens(tokens)
  parse_str   = ''
  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 += convert_volatile()
    elsif (token == 'ptgBool')
      token = args.shift
      parse_str += convert_bool(token)
    elsif (token == '_num')
      token = args.shift
      parse_str += convert_number(token)
    elsif (token == '_str')
      token = args.shift
      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 += 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 += 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 += 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 += 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 += convert_name(token, _class)
    elsif (token == '_func')
      token = args.shift
      parse_str += convert_function(token, num_args.to_i)
      _classary.pop
      num_args = 0 # Reset after use
    elsif @ptg[token]
      parse_str += [@ptg[token]].pack("C")
    else
      # Unrecognised token
      return nil
    end
  end

  parse_str
end

#reverse(expression) ⇒ Object 



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

def reverse(expression)
  expression.flatten
end

#scan(formula) ⇒ Object 



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

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

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



523
524
525
 
# File 'lib/writeexcel/formula.rb', line 523

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

#set_ext_sheets(worksheet, index) ⇒ Object

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.



483
484
485
486
487
 
# File 'lib/writeexcel/formula.rb', line 483

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