.. SPDX-License-Identifier: BSD-2-Clause
   Copyright 2013-2023, John McNamara, jmcnamara@cpan.org

.. _cell_notation:

Working with Cell Notation
==========================

XlsxWriter supports two forms of notation to designate the position of cells:
**Row-column** notation and **A1** notation.

Row-column notation uses a zero based index for both row and column while A1
notation uses the standard Excel alphanumeric sequence of column letter and
1-based row. For example::


    (0, 0)      # Row-column notation.
    ('A1')      # The same cell in A1 notation.

    (6, 2)      # Row-column notation.
    ('C7')      # The same cell in A1 notation.

Row-column notation is useful if you are referring to cells programmatically::

    for row in range(0, 5):
        worksheet.write(row, 0, 'Hello')

A1 notation is useful for setting up a worksheet manually and for working with
formulas::

    worksheet.write('H1', 200)
    worksheet.write('H2', '=H1+1')

In general when using the XlsxWriter module you can use A1 notation anywhere
you can use row-column notation. This also applies to methods that take a
range of cells::

    worksheet.merge_range(2, 1, 3, 3, 'Merged Cells', merge_format)
    worksheet.merge_range('B3:D4',    'Merged Cells', merge_format)

XlsxWriter supports Excel's worksheet limits of 1,048,576 rows by 16,384
columns.

.. note::
   * Ranges in A1 notation must be in uppercase, like in Excel.

   * In Excel it is also possible to use R1C1 notation. This is not
     supported by XlsxWriter.


.. _full_row_col:

Row and Column Ranges
---------------------

In Excel you can specify row or column ranges such as ``1:1`` for all of the
first row or ``A:A`` for all of the first column. In XlsxWriter these can be
set by specifying the full cell range for the row or column::

    worksheet.print_area('A1:XFD1')      # Same as 1:1
    worksheet.print_area('A1:A1048576')  # Same as A:A

This is actually how Excel stores ranges such as ``1:1`` and ``A:A``
internally.

These ranges can also be specified using row-column notation, as explained
above::

    worksheet.print_area(0, 0,       0, 16383)  # Same as 1:1
    worksheet.print_area(0, 0, 1048575,     0)  # Same as A:A

To select the entire worksheet range you can specify
``A1:XFD1048576``.


.. _abs_reference:

Relative and Absolute cell references
-------------------------------------

When dealing with Excel cell references it is important to distinguish between
relative and absolute cell references in Excel.

**Relative** cell references change when they are copied while **Absolute**
references maintain fixed row and/or column references. In Excel absolute
references are prefixed by the dollar symbol as shown below::

    'A1'    # Column and row are relative.
    '$A1'   # Column is absolute and row is relative.
    'A$1'   # Column is relative and row is absolute.
    '$A$1'  # Column and row are absolute.

See the Microsoft Office documentation for
`more information on relative and absolute references <https://support.microsoft.com/en-us/office/switch-between-relative-absolute-and-mixed-references-dfec08cd-ae65-4f56-839e-5f0d8d0baca9>`_.

Some functions such as :func:`conditional_format()` may require absolute
references, depending on the range being specified.


Defined Names and Named Ranges
------------------------------

It is also possible to define and use "Defined names/Named ranges" in
workbooks and worksheets, see :func:`define_name`::

    workbook.define_name('Exchange_rate', '=0.96')
    worksheet.write('B3', '=B2*Exchange_rate')

See also :ref:`ex_defined_name`.


.. _cell_utility:

Cell Utility Functions
----------------------

The ``XlsxWriter`` ``utility`` module contains several helper functions for
dealing with A1 notation as shown below. These functions can be imported as
follows::

    from xlsxwriter.utility import xl_rowcol_to_cell

    cell = xl_rowcol_to_cell(1, 2)  # C2


xl_rowcol_to_cell()
~~~~~~~~~~~~~~~~~~~

.. py:function:: xl_rowcol_to_cell(row, col[, row_abs, col_abs])

   Convert a zero indexed row and column cell reference to a A1 style string.

   :param int row:      The cell row.
   :param int col:      The cell column.
   :param bool row_abs: Optional flag to make the row absolute.
   :param bool col_abs: Optional flag to make the column absolute.
   :rtype:              A1 style string.


The ``xl_rowcol_to_cell()`` function converts a zero indexed row and column
cell values to an ``A1`` style string::

    cell = xl_rowcol_to_cell(0, 0)   # A1
    cell = xl_rowcol_to_cell(0, 1)   # B1
    cell = xl_rowcol_to_cell(1, 0)   # A2

The optional parameters ``row_abs`` and ``col_abs`` can be used to indicate
that the row or column is absolute::

    str = xl_rowcol_to_cell(0, 0, col_abs=True)                # $A1
    str = xl_rowcol_to_cell(0, 0, row_abs=True)                # A$1
    str = xl_rowcol_to_cell(0, 0, row_abs=True, col_abs=True)  # $A$1


xl_cell_to_rowcol()
~~~~~~~~~~~~~~~~~~~

.. py:function:: xl_cell_to_rowcol(cell_str)

   Convert a cell reference in A1 notation to a zero indexed row and column.

   :param string cell_str: A1 style string, absolute or relative.
   :rtype:                 Tuple of ints for (row, col).


The ``xl_cell_to_rowcol()`` function converts an Excel cell reference in ``A1``
notation to a zero based row and column. The function will also handle Excel's
absolute, ``$``, cell notation::

    (row, col) = xl_cell_to_rowcol('A1')    # (0, 0)
    (row, col) = xl_cell_to_rowcol('B1')    # (0, 1)
    (row, col) = xl_cell_to_rowcol('C2')    # (1, 2)
    (row, col) = xl_cell_to_rowcol('$C2')   # (1, 2)
    (row, col) = xl_cell_to_rowcol('C$2')   # (1, 2)
    (row, col) = xl_cell_to_rowcol('$C$2')  # (1, 2)


xl_col_to_name()
~~~~~~~~~~~~~~~~

.. py:function:: xl_col_to_name(col[, col_abs])

   Convert a zero indexed column cell reference to a string.

   :param int col:      The cell column.
   :param bool col_abs: Optional flag to make the column absolute.
   :rtype:              Column style string.


The ``xl_col_to_name()`` converts a zero based column reference to a string::

    column = xl_col_to_name(0)    # A
    column = xl_col_to_name(1)    # B
    column = xl_col_to_name(702)  # AAA

The optional parameter ``col_abs`` can be used to indicate if the column is
absolute::

    column = xl_col_to_name(0, False)  # A
    column = xl_col_to_name(0, True)   # $A
    column = xl_col_to_name(1, True)   # $B


xl_range()
~~~~~~~~~~

.. py:function:: xl_range(first_row, first_col, last_row, last_col)

   Converts zero indexed row and column cell references to a A1:B1 range
   string.

   :param int first_row:     The first cell row.
   :param int first_col:     The first cell column.
   :param int last_row:      The last cell row.
   :param int last_col:      The last cell column.
   :rtype:                   A1:B1 style range string.


The ``xl_range()`` function converts zero based row and column cell references
to an ``A1:B1`` style range string::

    cell_range = xl_range(0, 0, 9, 0)  # A1:A10
    cell_range = xl_range(1, 2, 8, 2)  # C2:C9
    cell_range = xl_range(0, 0, 3, 4)  # A1:E4
    cell_range = xl_range(0, 0, 0, 0)  # A1


xl_range_abs()
~~~~~~~~~~~~~~

.. py:function:: xl_range_abs(first_row, first_col, last_row, last_col)

   Converts zero indexed row and column cell references to a $A$1:$B$1
   absolute range string.

   :param int first_row:     The first cell row.
   :param int first_col:     The first cell column.
   :param int last_row:      The last cell row.
   :param int last_col:      The last cell column.
   :rtype:                   $A$1:$B$1 style range string.


The ``xl_range_abs()`` function converts zero based row and column cell
references to an absolute ``$A$1:$B$1`` style range string::

    cell_range = xl_range_abs(0, 0, 9, 0)  # $A$1:$A$10
    cell_range = xl_range_abs(1, 2, 8, 2)  # $C$2:$C$9
    cell_range = xl_range_abs(0, 0, 3, 4)  # $A$1:$E$4
    cell_range = xl_range_abs(0, 0, 0, 0)  # $A$1