regex_msft_docs_to_python_funcs.txt

The functions available in excelbird.fn are generated from the html table
found at this link called 'Excel Functions (alphabetical)':
https://support.microsoft.com/en-us/office/excel-functions-alphabetical-b3944572-255d-4efb-bb96-c6d90033e188

To generate them, copy-paste the html code for the main table
(identify it as <table aria-label class="banded flipColors">)
into a python file inside Vim.

Then, while inside vim looking at the raw html source for the table,
run the following search replace commands. Author: Ryan Young

Regex
=========================================================================
Copy the following lines one at a time, starting AFTER the beginning colon for each.

Extract table body contents
:%s/\_.\{-}<tbody>\_.\(\_.\+\)<\/tbody>\_.\+/\1

Match needed info
:%s/<tr>\_.\{-}<p.*>\_.\{-}cpArticleLink">\(.\+\)<\/a>\_.\{-}ocpRunInHead">\(.\+\)<\/b>\(\(&nbsp;\)*\)\(.\+\)<\/p>\_.\{-}<\/tr>/\1 :: \1 :: **\2** :: \5

Remove starting spaces
:%s/^\s\+/

Remove 'function'
:%s/ functions\? :: / :: /g

When there's two functions in one line, remove the second version (Make sure you have a trailing space at the end of this line)
:%s/ :: [A-Z\.0-9]\+, [A-Z\.0-9]\+ :: / :: 

When there's two functions in one line, separate them onto two lines
:%s/^\([A-Z\.0-9]\+\), \([A-Z\.0-9]\+\) :: \(.\+\)$/\1 :: \1 :: \3\r\2 :: \2 :: \3

Underscore first element when there's two dot delimiters
:%s/^\([A-Z0-9]\+\)\(\.\)\([A-Z0-9]\+\)\(\.\)\([A-Z0-9]\+\)/\1_\3_\5

Underscore first element when there's one dot delimiter
:%s/^\([A-Z0-9]\+\)\(\.\)\([A-Z0-9]\+\)/\1_\3

Replace &nbsp; with empty string when it's inside the bold header where the colon is
:%s/:&nbsp;\*\*/:**

Replace each instance of &nbsp; on a line with single space, including surrounding spaces in our match.
:%s/ \?&nbsp; \?/ /g

Remove trailing space between header ending colon and double asterisk
:%s/\([a-z]\): \*\*/\1:**

Remove double spaces
:%s/  / /g

Make sure double colon is always surrounded by single spaces
:%s/ *:: */ :: /g

NOTE: If you think you might change your mind about how your functions will be formatted,
save a copy of your current progress before proceeding.

Pattern to format the functions. This is a single line, and should be pasted into vim in its entirety
:%s/^\([A-Z_0-9]\+\) :: \([A-Z\.0-9]\+\) :: \(.*\) :: \(.\+\)/def \1(*inner: Any, res_type: type | None = None, **kwargs: Any) -> Func:\r\t"""\r\t\3 \4\r\r\tIn Excel: ``\2()``\r\r\tReturns\r\t-------\r\t:class:`Func <excelbird.Func>`\r\t"""\r\treturn Func("\2(", *inner, ")", res_type=res_type, **kwargs)\r

----------------------------------------------------------------------------------------------------------------

Now, create a python set of all function names (excel version) in a new file. Here's how.

Grep the function names to an output file
:g/``\([A-Z\.0-9]\+\)/.w! >> _formulae.py

Replace text surrounding function names.
:%s/^.*\`\`\([A-Z\.0-9]\+\)()\`\`/\1

Surround text with quotes and commas
:%s/\(.\+\)/\t"\1",

Wrap with braces manually to create a set.



"""

.. role:: html(raw)
   :format: html

All ~500 Excel functions can be accessed in Python, from the :mod:`excelbird.fn` module.

Each is documented with the same short summary as provided by `Microsoft's documentation <https://support.microsoft.com/en-us/office/excel-functions-alphabetical-b3944572-255d-4efb-bb96-c6d90033e188>`_.

Allows for autocomplete and documentation preview in your IDE.

**Syntax Example:** Excel's ``T.DIST.2T()`` is :meth:`T_DIST_2T` in `excelbird`.

.. note::

    Each function is short for an :class:`excelbird.Func`

    .. code-block::

        fn.ABS(some_cell)

    is the same as

    .. code-block::

        Func("ABS(", some_cell, ")")
"""
from excelbird.core.function import Func
from typing import Any