Class: Writeexcel::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.



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

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.



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

def byte_order
  @byte_order
end

#ext_ref_count ⇒ Object

Returns the value of attribute ext_ref_count.



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

def ext_ref_count
  @ext_ref_count
end

#ext_refs ⇒ Object

Returns the value of attribute ext_refs.



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

def ext_refs
  @ext_refs
end

#ext_sheets ⇒ Object

Returns the value of attribute ext_sheets.



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

def ext_sheets
  @ext_sheets
end

#workbook ⇒ Object

Returns the value of attribute workbook.



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

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.



556
557
558
 
# File 'lib/writeexcel/formula.rb', line 556

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.



512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
 
# File 'lib/writeexcel/formula.rb', line 512

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

  # Handle utf8 sheetnames
  ruby_18 do
    if sheet_name =~ NonAscii
      sheet_name = utf8_to_16be(sheet_name)
    end
  end
  ruby_19 do
    if sheet_name.encoding == Encoding::UTF_8
      sheet_name = sheet_name.encode('UTF-16BE')
    end
  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 



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

def next_token
  @q.shift
end

#parse(formula) ⇒ Object 



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

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.



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

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.



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
156
157
158
 
# File 'lib/writeexcel/formula.rb', line 71

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



221
222
223
 
# File 'lib/writeexcel/formula.rb', line 221

def reverse(expression)
  expression.flatten
end

#scan(formula) ⇒ Object 



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
204
205
206
207
208
 
# File 'lib/writeexcel/formula.rb', line 160

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

set_ext_name()

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



594
595
596
 
# File 'lib/writeexcel/formula.rb', line 594

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.



542
543
544
545
546
 
# File 'lib/writeexcel/formula.rb', line 542

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