<commit_before>.. _converters:

Converters
==========

Introduced with v0.7.0, converters define how Excel ranges and their values are converted both during
**reading** and **writing** operations. They also provide a consistent experience across **xlwings.Range** objects and
**User Defined Functions** (UDFs).

Converters are explicitely set with the ``as_`` argument in the ``options`` method when manipulating ``xlwings.Range`` objects
or in the ``@xw.arg`` and ``@xw.ret`` decorators when using UDFs. If no converter is specified, the base converter
is applied when reading. When writing, xlwings will automatically apply the correct converter (if available) according to the
object's type that is being written to Excel. If no converter is found for that type, it falls back to the base converter.

**Syntax:**

==============================  ===========================================================  ===========
****                            **Range**                                                    **UDF**
==============================  ===========================================================  ===========
**reading**                     ``Range.options(as_=None, **kwargs).value``                  ``@arg('x', as_=None, **kwargs)``
**writing**                     ``Range.options(as_=None, **kwargs).value = myvalue``        ``@ret(as_=None, **kwargs)``
==============================  ===========================================================  ===========

.. note:: Keyword arguments (``kwargs``) may refer to the specific converter or the base converter.
  For example, to set the ``numbers`` option in the base converter and the ``index`` option in the DataFrame converter,
  you would write::

      Range('A1:C3').options(pd.DataFrame, index=False, numbers=int).value

Base Converter
--------------

If no options are set, the following conversions are performed:

* single cells are read in as ``floats`` in case the Excel cell holds a number, as ``unicode`` in case it holds text,
  as ``datetime`` if it contains a date and as ``None`` in case it is empty.
* columns/rows are read in as lists, e.g. ``[None, 1.0, 'a string']``
* 2d cell ranges are read in as list of lists, e.g. ``[[None, 1.0, 'a string'], [None, 2.0, 'another string']]``

The following options can be set:

* **ndim**

  Force the value to have either 1 or 2 dimensions regardless of the shape of the range:

  >>> import xlwings as xw
  >>> wb = Workbook()
  >>> xw.Range('A1').value = [[1, 2], [3, 4]]
  >>> xw.Range('A1').value
  1.0
  >>> xw.Range('A1').options(ndim=1).value
  [1.0]
  >>> xw.Range('A1').options(ndim=2).value
  [[1.0]]
  >>> xw.Range('A1:A2').value
  [1.0 3.0]
  >>> xw.Range('A1:A2').options(ndim=2).value
  [[1.0], [3.0]]

* **numbers**

  By default numbers are read as ``float``, but this can be overridden::

    >>> xw.Range('A1').value = 1
    >>> xw.Range('A1').value
    1.0
    >>> xw.Range('A1').options(numbers=int).value
    1

  Using this on UDFs looks like this::

    @xw.func
    @xw.arg('x', numbers=int)
    def myfunction(x):
        # all numbers in x arrive as int
        return x

  Note that this option can only be used for reading, as Excel always stores numbers internally as floats.

* **dates**

  Cells with dates are converted per default into ``datetime.datetime``, you can change it to ``datetime.date``:

  - Range::

    >>> import datetime as dt
    >>> xw.Range('A1').options(dates=dt.date).value

  - UDFs: ``@xw.arg('x', dates=dt.date)``<commit_msg>Extra notes about dates= and numbers= options<commit_after>.. _converters:

Converters
==========

Introduced with v0.7.0, converters define how Excel ranges and their values are converted both during
**reading** and **writing** operations. They also provide a consistent experience across **xlwings.Range** objects and
**User Defined Functions** (UDFs).

Converters are explicitely set with the ``as_`` argument in the ``options`` method when manipulating ``xlwings.Range`` objects
or in the ``@xw.arg`` and ``@xw.ret`` decorators when using UDFs. If no converter is specified, the base converter
is applied when reading. When writing, xlwings will automatically apply the correct converter (if available) according to the
object's type that is being written to Excel. If no converter is found for that type, it falls back to the base converter.

**Syntax:**

==============================  ===========================================================  ===========
****                            **Range**                                                    **UDF**
==============================  ===========================================================  ===========
**reading**                     ``Range.options(as_=None, **kwargs).value``                  ``@arg('x', as_=None, **kwargs)``
**writing**                     ``Range.options(as_=None, **kwargs).value = myvalue``        ``@ret(as_=None, **kwargs)``
==============================  ===========================================================  ===========

.. note:: Keyword arguments (``kwargs``) may refer to the specific converter or the base converter.
  For example, to set the ``numbers`` option in the base converter and the ``index`` option in the DataFrame converter,
  you would write::

      Range('A1:C3').options(pd.DataFrame, index=False, numbers=int).value

Base Converter
--------------

If no options are set, the following conversions are performed:

* single cells are read in as ``floats`` in case the Excel cell holds a number, as ``unicode`` in case it holds text,
  as ``datetime`` if it contains a date and as ``None`` in case it is empty.
* columns/rows are read in as lists, e.g. ``[None, 1.0, 'a string']``
* 2d cell ranges are read in as list of lists, e.g. ``[[None, 1.0, 'a string'], [None, 2.0, 'another string']]``

The following options can be set:

* **ndim**

  Force the value to have either 1 or 2 dimensions regardless of the shape of the range:

  >>> import xlwings as xw
  >>> wb = Workbook()
  >>> xw.Range('A1').value = [[1, 2], [3, 4]]
  >>> xw.Range('A1').value
  1.0
  >>> xw.Range('A1').options(ndim=1).value
  [1.0]
  >>> xw.Range('A1').options(ndim=2).value
  [[1.0]]
  >>> xw.Range('A1:A2').value
  [1.0 3.0]
  >>> xw.Range('A1:A2').options(ndim=2).value
  [[1.0], [3.0]]

* **numbers**

  By default cells with numbers are read as ``float``, but you can change it to ``int``::

    >>> xw.Range('A1').value = 1
    >>> xw.Range('A1').value
    1.0
    >>> xw.Range('A1').options(numbers=int).value
    1

  Alternatively, you can specify any other function or type which takes a single float argument.

  Using this on UDFs looks like this::

    @xw.func
    @xw.arg('x', numbers=int)
    def myfunction(x):
        # all numbers in x arrive as int
        return x

  Note that this option can only be used for reading, as Excel always stores numbers internally as floats.

* **dates**

  By default cells with dates are read as ``datetime.datetime``, but you can change it to ``datetime.date``:

  - Range::

    >>> import datetime as dt
    >>> xw.Range('A1').options(dates=dt.date).value

  - UDFs: ``@xw.arg('x', dates=dt.date)``

  Alternatively, you can specify any other function or type which takes the same keyword arguments
  as ``datetime.datetime``, for example:

    >>> my_date_handler = lambda year, month, day, **kwargs: "%04i-%02i-%02i" % (year, month, day)
    >>> xw.Range('A1').options(dates=my_date_handler).value
    '2017-02-20'

{'fraction_non_alphanumeric': 0.21956753870795515, 'fraction_numerical': 0.015616657768286172, 'mean_word_length': 3.516576250753466, 'pattern_counts': {'":': 0, '<': 3, '<?xml version=': 0, '>': 87, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 4, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '6301016', 'n_tokens_mistral': 2296, 'n_tokens_neox': 2124, 'n_words': 804}