{
    "content": [
        {
            "type": "text",
            "text": "# Spreadsheet::WriteExcel::Formula (perldoc)\n\n## NAME\n\nFormula - A class for generating Excel formulas\n\n## SYNOPSIS\n\nSee the documentation for Spreadsheet::WriteExcel\n\n## DESCRIPTION\n\nThis module is used by Spreadsheet::WriteExcel. You do not need to use it directly.\n\n## Sections\n\n- **NAME**\n- **SYNOPSIS**\n- **DESCRIPTION**\n- **NOTES**\n- **AUTHOR**\n- **COPYRIGHT**\n\nUse structuredContent.sections for detailed options, examples, and full documentation.\n"
        }
    ],
    "structuredContent": {
        "command": "Spreadsheet::WriteExcel::Formula",
        "section": "",
        "mode": "perldoc",
        "summary": "Formula - A class for generating Excel formulas",
        "synopsis": "See the documentation for Spreadsheet::WriteExcel",
        "tldr_summary": null,
        "tldr_examples": [],
        "tldr_source": null,
        "flags": [],
        "examples": [],
        "see_also": [],
        "section_outline": [
            {
                "name": "NAME",
                "lines": 2,
                "subsections": []
            },
            {
                "name": "SYNOPSIS",
                "lines": 2,
                "subsections": []
            },
            {
                "name": "DESCRIPTION",
                "lines": 2,
                "subsections": []
            },
            {
                "name": "NOTES",
                "lines": 110,
                "subsections": []
            },
            {
                "name": "AUTHOR",
                "lines": 2,
                "subsections": []
            },
            {
                "name": "COPYRIGHT",
                "lines": 5,
                "subsections": []
            }
        ],
        "sections": {
            "NAME": {
                "content": "Formula - A class for generating Excel formulas\n",
                "subsections": []
            },
            "SYNOPSIS": {
                "content": "See the documentation for Spreadsheet::WriteExcel\n",
                "subsections": []
            },
            "DESCRIPTION": {
                "content": "This module is used by Spreadsheet::WriteExcel. You do not need to use it directly.\n",
                "subsections": []
            },
            "NOTES": {
                "content": "The following notes are to help developers and maintainers understand the sequence of operation.\nThey are also intended as a pro-memoria for the author. ;-)\n\nSpreadsheet::WriteExcel::Formula converts a textual representation of a formula into the\npre-parsed binary format that Excel uses to store formulas. For example \"1+2*3\" is stored as\nfollows: \"1E 01 00 1E 02 00 1E 03 00 05 03\".\n\nThis string is comprised of operators and operands arranged in a reverse-Polish format. The\nmeaning of the tokens in the above example is shown in the following table:\n\nToken   Name        Value\n1E      ptgInt      0001   (stored as 01 00)\n1E      ptgInt      0002   (stored as 02 00)\n1E      ptgInt      0003   (stored as 03 00)\n05      ptgMul\n03      ptgAdd\n\nThe tokens and token names are defined in the \"Excel Developer's Kit\" from Microsoft Press.\n\"ptg\" stands for Parse ThinG (as in \"That lexer can't grok it, it's a parse thang.\")\n\nIn general the tokens fall into two categories: operators such as \"ptgMul\" and operands such as\n\"ptgInt\". When the formula is evaluated by Excel the operand tokens push values onto a stack.\nThe operator tokens then pop the required number of operands off of the stack, perform an\noperation and push the resulting value back onto the stack. This methodology is similar to the\nbasic operation of a reverse-Polish (RPN) calculator.\n\nSpreadsheet::WriteExcel::Formula parses a formula using a \"Parse::RecDescent\" parser (at a later\nstage it may use a \"Parse::Yapp\" parser or \"Parse::FastDescent\").\n\nThe parser converts the textual representation of a formula into a parse tree. Thus, \"1+2*3\" is\nconverted into something like the following, \"e\" stands for expression:\n\ne\n/ | \\\n1   +   e\n/ | \\\n2   *   3\n\nThe function \"reversetree()\" recurses down through this structure swapping the order of\noperators followed by operands to produce a reverse-Polish tree. In other words the formula is\nconverted from in-fix notation to post-fix. Following the above example the resulting tree would\nlook like this:\n\ne\n/ | \\\n1   e   +\n/ | \\\n2   3   *\n\nThe result of the recursion is a single array of tokens. In our example the simplified form\nwould look like the following:\n\n(1, 2, 3, *, +)\n\nThe actual return value contains some additional information to help in the secondary parsing\nstage:\n\n(num, 1, num, 2, num, 3, ptgMul, ptgAdd, arg, 1)\n\nThe additional tokens are:\n\nToken       Meaning\nnum        The next token is a number\nstr        The next token is a string\nref2d      The next token is a 2d cell reference\nref3d      The next token is a 3d cell reference\nrange2d    The next token is a 2d range\nrange3d    The next token is a 3d range\nfuncV       The next token is a function\narg        The next token is the number of args for a function\nclass      The next token is a function name\nvol        The formula contains a voltile function\n\nThe \"arg\" token is generated for all lists but is only used for functions that take a variable\nnumber of arguments.\n\nThe \"class\" token indicates the start of the arguments to a function. This allows the\npost-processor to decide the \"class\" of the ref and range arguments that the function takes. The\nclass can be reference, value or array. Since function calls can be nested, the class variable\nis stored on a stack in the @class array. The class of the ref or range is then read as the top\nelement of the stack $class[-1]. When a \"funcV\" is read it pops the class value.\n\nCertain Excel functions such as RAND() and NOW() are designated as volatile and must be\nrecalculated by Excel every time that a cell is updated. Any formulas that contain one of these\nfunctions has a specially formatted \"ptgAttr\" tag prepended to it to indicate that it is\nvolatile.\n\nA secondary parsing stage is carried out by \"parsetokens()\" which converts these tokens into a\nbinary string. For the \"1+2*3\" example this would give:\n\n1E 01 00 1E 02 00 1E 03 00 05 03\n\nThis two-pass method could probably have been reduced to a single pass through the\n\"Parse::RecDescent\" parser. However, it was easier to develop and debug this way.\n\nThe token values and formula values are stored in the %ptg and %functions hashes. These hashes\nand the parser object $parser are exposed as global data. This breaks the OO encapsulation, but\nmeans that they can be shared by several instances of Spreadsheet::WriteExcel called from the\nsame program.\n\nNon-English function names can be added to the %functions hash using the \"functionlocale.pl\"\nprogram in the \"examples\" directory of the distro. The supported languages are: German, French,\nSpanish, Portuguese, Dutch, Finnish, Italian and Swedish. These languages are not added by\ndefault because there are conflicts between functions names in different languages.\n\nThe parser is initialised by \"initparser()\". The initialisation is delayed until the first\nformula is parsed. This eliminates the overhead of generating the parser in programs that are\nnot processing formulas. (The parser should really be pre-compiled, this is to-do when the\ngrammar stabilises).\n",
                "subsections": []
            },
            "AUTHOR": {
                "content": "John McNamara jmcnamara@cpan.org\n",
                "subsections": []
            },
            "COPYRIGHT": {
                "content": "Copyright MM-MMX, John McNamara.\n\nAll Rights Reserved. This module is free software. It may be used, redistributed and/or modified\nunder the same terms as Perl itself.\n",
                "subsections": []
            }
        }
    }
}