From: Charles Mills <cmills@...>
Date: 2005-04-29T00:14:30+09:00
Subject: Re: I need a formula parser

Daniel Berger wrote:
> Hi all,
>
> I'm slowly but surely working on the next major release of my
> spreadsheet package (a port of John McNamara's
Spreadsheet::WriteExcel
> Perl module, 2.x).
>
> However, I still do not have a formula parser.  John's module uses
> Parse::RecDescent to parse formulas.  I need something similar (or
> better).  Preferably something generic that everyone can use and
enjoy,
> but I'll take a more tailored parser if necessary.
>
> Can anyone please help me?
>
> For more information on Spreadsheet::WriteExcel, see
> http://search.cpan.org/~jmcnamara/Spreadsheet-WriteExcel-2.13/
>
> The new project page is at http://rubyforge.org/projects/spreadsheet.
>
> Regards,
>
> Dan
>
> PS - And, if anyone is up to porting OLE::Storage or
OLE::Storage_Lite,
> that would be swell.

Below is a shameless copy of LXL I wrote a while back.  lxl_parse.rb is
a command line app.  lxl.y requires RACC.

-Charlie

lxl.y
------------------------------------------------------------------

class LXL::Parser

	# see http://www.informit.com/articles/article.asp?p=328639&seqNum=2
	# for XL operator precidence table
	prechigh
		left ':'
		nonassoc UMINUS PERCENT
		left '^' '*' '/'
		left '+' '-'
		left '&' # concatination
		left '=' '<' '>' '<=' '>=' '<>'
	preclow

	token INTEGER FLOAT STRING SHEET PATH IDENTIFIER
				PERCENT UMINUS TRUE FALSE


rule

	target
		: statement
		| statement ';'
		;

	statement
		: formula { result = val[0,1] }
		| statement ';' expression { result = val[0] << val[2] }
		;

	formula
		: '=' expression { result = val[1] }
		;

	base_range
		: PATH SHEET IDENTIFIER	{ result = self.lookup(val[0], val[1],
val[2]) }
		| SHEET IDENTIFIER			{ result = self.lookup(nil, val[0], val[1]) }
		| IDENTIFIER						{ result = self.lookup(nil, nil, val[0]) }
		;

	range
		: base_range { result = val[0].Value }
#		| base_range ':' IDENTIFIER
#		| base_range ' ' IDENTIFIER
#		| base_range ',' IDENTIFIER
		;

	constant
		: TRUE		{ result = true }
		| FALSE		{ result = false }
		| INTEGER { result = val[0].to_f }
		| FLOAT		{ result = val[0].to_f }
		| STRING	{ result = LXL::XL.unquote(val[0]) }
		;

	expression
		: constant
		| range
		| function_call
		# unary operations
		| '-' expression = UMINUS
			{ result = LXL::XL.uminus(val[1]) }
		| '%' expression = PERCENT
			{ result = LXL::XL.percent(val[1]) }
		# arithmatic
		| expression '^' expression
			{ result = LXL::XL.power(val[0], val[2]) }
		| expression '*' expression
			{ result = LXL::XL.multiply(val[0], val[2]) }
		| expression '/' expression
			{ result = LXL::XL.divide(val[0], val[2]) }
		| expression '+' expression
			{ result = LXL::XL.add(val[0], val[2]) }
		| expression '-' expression
			{ result = LXL::XL.subtract(val[0], val[2]) }
		# concat
		| expression '&' expression
			{ result = LXL::XL.concat(val[0], val[2]) }
		# comparison
		| expression '=' expression
			{ result = LXL::XL.eq(val[0], val[2]) }
		| expression '<' expression
			{ result = LXL::XL.lt(val[0], val[2]) }
		| expression '>' expression
			{ result = LXL::XL.gt(val[0], val[2]) }
		| expression '<=' expression
			{ result = LXL::XL.le(val[0], val[2]) }
		| expression '>=' expression
			{ result = LXL::XL.ge(val[0], val[2]) }
		| expression '<>' expression
			{ result = LXL::XL.ne(val[0], val[2]) }
		;

	function_call
		: IDENTIFIER '(' args ')'	{ result = self.call_function(val[0],
*val[2]) }
		| IDENTIFIER '(' ')'			{ result = self.call_function(val[0]) }
		;

	args
		: expression						{ result = val[0,1] }
		| ',' expression				{ result = [0.0, val[1]] }
		| ',' 									{ result = [0.0, 0.0] }
		| args ',' expression		{ result = val[0] << val[2] }
		| args ','							{ result = val[0] << 0.0 }
		;

---- header ----

require 'win32ole'
require 'strscan'

# redefine to_s methods so they behave like XL
class Float
	def to_s
		if self % 1.0 == 0.0
			self.to_i.to_s
		else
			self.to_s
		end
	end
end

class True
	def to_s
		"TRUE"
	end
end

class False
	def to_s
		"FALSE"
	end
end

module LXL
	class FormulaError < StandardError; end

	def LXL.value_error(msg)
		raise(LXL::FormulaError, "#VALUE: #{msg}")
	end

	def LXL.name_error(msg)
		raise(LXL::FormulaError, "#NAME: #{msg}")
	end

	module XL
		def XL.to_num(v)
			LXL.value_error("#{v.inspect} is not a number") \
				if v.is_a? String and v !~ /[0-9]+(?:\.[0-9]*)?/
			v.to_f # all Excel numbers are floats
		end
		def XL.to_bool(v)
			LXL.value_error("cannot use #{v.inspect} as a boolean") if v.is_a?
String

			if v == true or v == false
				v
			else
				v != 0.0 # non zero values are true
			end
		end
		def XL.uminus(a)
			-to_num(a)
		end
		def XL.percent(a)
			to_num(a) / 100.0
		end
		def XL.concat(a,b)
			a.to_s + b.to_s
		end
		# to arithmetic with Floats
		[	['power', '**'],
			['multiply', '*'],
			['divide', '/'],
			['add', '+'],
			['subtract', '-']	].each do |p|
			#puts(%{
			self.module_eval(%{
				def XL.#{p[0]}(a,b)
					to_num(a) #{p[1]} to_num(b)
				end
			})
		end
		# do comparison with Strings
		[ ['eq', '=='],
			['lt', '<'],
			['gt', '>'],
			['le', '<='],
			['ge', '>='],
			['ne', '!='] ].each do |p|
			self.module_eval(%{
				def XL.#{p[0]}(a,b)
					a.to_s #{p[1]} b.to_s
				end
			})
		end
		def XL.unquote(a)
			a = a[1...-1] # remove quotes
			a.gsub!('""','"')
			a
		end
	end # XL
end # LXL

---- inner ----

	def Parser.open(*args)
		v = self.new(*args)
		if block_given?
			begin
				yield(v)
			ensure
				v.close
			end
		else
			v
		end
	end

  s = [	'<=', '>=', '=',  '<>', '<', '>',
				'+', '-', '*', '^', '/', '(', ')',
				'&', ',', ';' ].collect! do |op|
		Regexp.quote(op)
	end.join('|')
	SYMBOL = /#{s}/

	def initialize(base_wkbk=nil, base_sheet=nil)
		@xl_functions = {}
		self.add_xl_functions
		if base_wkbk and base_sheet
			@wkbks = {}
			@xl = WIN32OLE.new('EXCEL.Application')
			begin
				# turn off screen updating, etc
				#@xl
			rescue
				warn $!
			end
			@base_wkbk = @xl.Workbooks.Open(base_wkbk)
			@base_sheet = @base_wkbk.Sheets(base_sheet) if base_sheet
		end
	end

	def add_function(name)
		name = name.upcase.to_sym unless name.is_a? Symbol
		warn "function #{name} already exists, overwriting" if
@xl_functions[name]
		@xl_functions[name] = Proc.new
		self # return self
	end

	def add_xl_functions
		add_function(:AND) { |a,b| LXL::XL.to_bool(a) and LXL::XL.to_bool(b)
}
		add_function(:OR)  { |a,b| LXL::XL.to_bool(a) or LXL::XL.to_bool(b) }
		add_function(:NOT) { |a| not LXL::XL.to_bool(a) }
		add_function(:IF)  { |a,b,c| LXL::XL.to_bool(a) ? b : c }
	end

	def closed?
		@xl.nil?
	end

	def close
		if @wkbks
			begin
				@wkbks.each do |name, wkbk|
					wkbk.Close
				end .clear
			ensure
				begin
					@base_sheet = nil
					@base_wkbk.Close
				ensure
					@base_wkbk = nil
					@xl.Quit
					@xl = nil
				end
			end
		end
		self # return self
	end

	def eval(str)
		# check for initial '=' sign
		return str unless str[0] == ?=
		s = StringScanner.new(str)

		tokens = []
		until s.empty?
			case
				when m = s.scan(/\s+/)
					# ignore whitespace
				when m = s.scan(/[0-9]+\.[0-9]+/)
					# float
					tokens << [:FLOAT, m]
				when m = s.scan(/[0-9]+/)
					# integer
					tokens << [:INTEGER, m]
				when m = s.scan(/L?"(?:""|[^"])*"/)
					# string
					tokens << [:STRING, m]
				when m = s.scan(/[_A-Za-z]\w*/)
					# identifier
					m.upcase!
					tokens << \
					[ if m == "TRUE"
							:TRUE
						elsif m == "FALSE"
							:FALSE
						else
							:IDENTIFIER
						end, m ]
				when m = s.scan(SYMBOL)
					tokens << [m, m]
				when m = s.scan(/(?:\[([^\]]+)\])?(\w+)!/)
					tokens << [:PATH, s[1]] if s[1]
					tokens << [:SHEET, s[2]]
				else
					m = s.getch
					raise ParseError, "unrecognized token #{m}"
			end
		end # until
		tokens << [false, false] # end of tokens
		yyparse(tokens, :each)
	end

protected
	def workbook(name)
		@wkbks[name] || @wkbks[name] = @xl.Workbooks.Open(name)
	end

	def lookup(wkbk, sheet, address)
		LXL.name_error("no base workbook given cannot resolve #{address}") if
self.closed?

		if wkbk
			wkbk = self.workbook(wkbk)
		else
			wkbk = @base_wkbk
		end
		if sheet
			sheet = wkbk.Sheets(sheet)
		elsif @base_sheet
			sheet = @base_sheet
		else
			LXL.name_error("no base worksheet given cannot resolve #{address}")
		end
		sheet.Range(address)
	end

	def call_function(name, *args)
		LXL.name_error("no such function #{name}") \
			unless f = @xl_functions[name.to_sym]
		f.call(*args)
	end

	def on_error(error_token_id, error_value, value_stack)
		raise ParseError, "parse error on #{error_value.to_s.inspect}"
	end


lxl_parse.rb
--------------------------------------------------------------

#!/usr/bin/env ruby

require 'optparse'

# create output directories if they don't exist
def create_dest_dirs(dest_file)
	dest_dir = File.dirname(dest_file)
	dirs = []
	until File.directory?(dest_dir)
		# [ "/home/boson/fake", "/home/boson" ]
		# "/home/boson" exists so loop ends
		dirs << dest_dir
		dest_dir = File.dirname(dest_dir)
	end
	dirs.reverse_each do |d|
		Dir.mkdir(d)
	end
	nil
end

class OptionParser
	def error(msg)
		raise OptionParser::InvalidArgument, msg
	end
end

command =File.basename($0)
if $0 != command
	$:.unshift(File.join(File.dirname($0),'..','lib'))
end
require 'lxl'

# initialize with defaults
base_wkbk = nil
base_sheet = nil
dest_file = nil
in_file = nil

opts = OptionParser.new do |opts|
	opts.banner = "Usage: #{command} [options] [input formulas]"
	opts.separator ""
	opts.separator "Specific options:"

	opts.on("-o", "--output file", String, \
					"Output to file (default STDOUT)") do |file|
		opts.error("multiple output files give") if dest_file
		opts.error("destination file '#{file}' exists") if File.exists?(file)
		dest_file = file
	end
	opts.on("-i", "--input file", String, \
					"Input formulas from file") do |file|
		opts.error("multiple input files give") if in_file
		opts.error("input file #{file} does not exist") unless
File.exists?(file)
		in_file = file
	end
	opts.on("-w", "--wkbk=(workbook)", String, "Set base workbook") do
|wkbk|
		opts.error("#{wkbk} does not exist") unless File.exists?(wkbk)
		base_wkbk = wkbk
	end
	opts.on("-s", "--sheet=(worksheet)", String, "Set base worksheet") do
|sheet|
		base_sheet = sheet
	end

	opts.on("-h", "--help", "Print this message") do
		puts opts
		exit(0)
	end

	opts.separator ""
	opts.separator "Reads from STDIN if input file is '-'"
	opts.separator "Formulas read from a file or from STDIN must be
seperated by a newline"
end

begin
	opts.parse!(ARGV)
rescue
	warn "#{$!} (-h will show vaild options)"
	exit(2)
end

# make sure opts gets gc'ed
opts = nil

if dest_file
	begin
		create_dest_dirs(dest_file)
	rescue
		warn "could not create destination directory: #{$?}"
		exit(2)
	end
end

if in_file == '-'
	f = STDIN.readlines
elsif in_file
	f = File.open(in_file) { |f| f.readlines }
end

if f
	f += ARGV
else
	f = ARGV
end

begin
	out = LXL::Parser.open(base_wkbk, base_sheet) do |xl|
		f.collect do |text|
			r = xl.eval(text)
			r.is_a?(Array) ? r.join('; ') : r
		end
	end
	out = out.join("\n")
	if dest_file
		File.open(dest_file, 'w') { |f| f.write(out) }
	else
		$stdout.write(out)
	end
rescue ParseError, LXL::FormulaError
	warn $!
	exit(2)
end