excelExpr.py 2.1 KB

12345678910111213141516171819202122232425262728293031323334353637383940414243444546474849505152535455565758596061626364656667686970
  1. # excelExpr.py
  2. #
  3. # Copyright 2010, Paul McGuire
  4. #
  5. # A partial implementation of a parser of Excel formula expressions.
  6. #
  7. from pyparsing import (CaselessKeyword, Suppress, Word, alphas,
  8. alphanums, nums, Optional, Group, oneOf, Forward,
  9. infixNotation, opAssoc, dblQuotedString, delimitedList,
  10. Combine, Literal, QuotedString, ParserElement, pyparsing_common as ppc)
  11. ParserElement.enablePackrat()
  12. EQ,LPAR,RPAR,COLON,COMMA = map(Suppress, '=():,')
  13. EXCL, DOLLAR = map(Literal,"!$")
  14. sheetRef = Word(alphas, alphanums) | QuotedString("'",escQuote="''")
  15. colRef = Optional(DOLLAR) + Word(alphas,max=2)
  16. rowRef = Optional(DOLLAR) + Word(nums)
  17. cellRef = Combine(Group(Optional(sheetRef + EXCL)("sheet") + colRef("col") +
  18. rowRef("row")))
  19. cellRange = (Group(cellRef("start") + COLON + cellRef("end"))("range")
  20. | cellRef | Word(alphas,alphanums))
  21. expr = Forward()
  22. COMPARISON_OP = oneOf("< = > >= <= != <>")
  23. condExpr = expr + COMPARISON_OP + expr
  24. ifFunc = (CaselessKeyword("if")
  25. - LPAR
  26. + Group(condExpr)("condition")
  27. + COMMA + Group(expr)("if_true")
  28. + COMMA + Group(expr)("if_false")
  29. + RPAR)
  30. statFunc = lambda name : Group(CaselessKeyword(name) + Group(LPAR + delimitedList(expr) + RPAR))
  31. sumFunc = statFunc("sum")
  32. minFunc = statFunc("min")
  33. maxFunc = statFunc("max")
  34. aveFunc = statFunc("ave")
  35. funcCall = ifFunc | sumFunc | minFunc | maxFunc | aveFunc
  36. multOp = oneOf("* /")
  37. addOp = oneOf("+ -")
  38. numericLiteral = ppc.number
  39. operand = numericLiteral | funcCall | cellRange | cellRef
  40. arithExpr = infixNotation(operand,
  41. [
  42. (multOp, 2, opAssoc.LEFT),
  43. (addOp, 2, opAssoc.LEFT),
  44. ])
  45. textOperand = dblQuotedString | cellRef
  46. textExpr = infixNotation(textOperand,
  47. [
  48. ('&', 2, opAssoc.LEFT),
  49. ])
  50. expr << (arithExpr | textExpr)
  51. (EQ + expr).runTests("""\
  52. =3*A7+5
  53. =3*Sheet1!$A$7+5
  54. =3*'Sheet 1'!$A$7+5"
  55. =3*'O''Reilly''s sheet'!$A$7+5
  56. =if(Sum(A1:A25)>42,Min(B1:B25),if(Sum(C1:C25)>3.14, (Min(C1:C25)+3)*18,Max(B1:B25)))
  57. =sum(a1:a25,10,min(b1,c2,d3))
  58. =if("T"&a2="TTime", "Ready", "Not ready")
  59. """)