Tutorial: Defining Specifications in a Spreadsheet
====================================================

When a project has many pipes, hand-writing a :class:`~routing.core.core.Port`,
:class:`~routing.core.core.PipeType` and :class:`~routing.core.core.Specification` for
each one in Python becomes tedious and error-prone. :class:`~routing.core.core.Specification`
can instead read your pipe definitions directly from a spreadsheet — either a single Excel
workbook or a pair of CSV files — and return a ready-to-route ``list[Specification]``.

This is the natural entry point for spreadsheet-driven workflows and for engineers who
prefer to maintain routing data in Excel rather than in code.

.. contents:: On this page
   :local:
   :depth: 1


The Data Model: Two Tables
--------------------------

Both the Excel and the CSV variant use the same two-table layout:

- **pipe_types** — one row per pipe type (its radius, curvature constraints, color, …).
- **specifications** — one row per pipe to route, referencing a pipe type *by name* and
  giving the start and end port of the route.

In Excel these are two **sheets** (named exactly ``pipe_types`` and ``specifications``) in
one workbook. In CSV they are two **separate files**.

Pipe types are deduplicated by name: every specification that references the same
``pipe_type`` name shares a single :class:`~routing.core.core.PipeType` instance.


Loading from Excel (``.xlsx``)
------------------------------

Wrap the file in a ``BinaryFile`` stream and call
:meth:`~routing.core.core.Specification.from_xlsx`:

.. code-block:: python

   from dessia_common.files import BinaryFile

   from routing.core.core import Specification

   stream = BinaryFile.from_file("specifications.xlsx")
   specifications = Specification.from_xlsx(stream)

   print(f"Loaded {len(specifications)} specifications")
   for spec in specifications:
       pipe_type = spec.pipe_type
       print(f"  {spec.name}: pipe_type={pipe_type.name}, "
             f"radius={pipe_type.section.radius_equivalent}")
       print(f"    start={spec.start.coordinates} -> end={spec.end.coordinates}")

``from_xlsx`` returns a flat ``list[Specification]``. Specifications that reference the
same pipe type share one ``PipeType`` object:

.. code-block:: python

   # Two specs that both use "hydraulic_12" point to the same instance
   assert specifications[0].pipe_type is specifications[2].pipe_type


Loading from CSV
----------------

The CSV variant takes the two file paths directly — no stream wrapper needed:

.. code-block:: python

   from routing.core.core import Specification

   specifications = Specification.from_csv(
       "pipe_types.csv",
       "specifications.csv",
   )

The columns are identical to the Excel sheets (see the reference tables below).


Routing the Loaded Specifications
---------------------------------

The returned list plugs straight into the normal routing pipeline — it is the same
``list[Specification]`` you would otherwise have built by hand:

.. code-block:: python

   from routing.core.finders import SmartFinder
   from routing.core.route_planner import RoutePlanner

   # cadmap built as usual from your CAD environment (see basic_routing)
   finder = SmartFinder("AStar", "manhattan", "never")
   route_planner = RoutePlanner(cadmap, finder)
   routing_result = route_planner.generate(specifications, 0, False)
   routing_result.display_3d()

See :doc:`basic_routing` for how to build the ``cadmap`` from your CAD model.


Column Reference: ``pipe_types``
--------------------------------

One row per pipe type. The first row is the header. ``name`` and ``radius`` are required;
empty cells in optional columns fall back to the :class:`~routing.core.core.PipeType`
default shown below.

.. list-table::
   :header-rows: 1
   :widths: 25 12 18 45

   * - Column
     - Required
     - Default
     - Description
   * - ``name``
     - yes
     - —
     - Unique pipe-type identifier, referenced from the ``specifications`` sheet.
   * - ``radius``
     - yes
     - —
     - Equivalent section radius, in metres.
   * - ``radius_of_curvature_ratio``
     - no
     - ``1.0``
     - Minimum ratio between bend radius and pipe radius.
   * - ``min_straight_length``
     - no
     - ``0.0``
     - Minimum straight length before a turn, in metres.
   * - ``min_slope``
     - no
     - no constraint
     - Minimum allowed slope (useful for gravity systems; negative value).
   * - ``max_slope``
     - no
     - no constraint
     - Maximum allowed slope.
   * - ``number_turn_max``
     - no
     - ``0`` (unlimited)
     - Maximum number of turns allowed (``0`` means no limit).
   * - ``pooling_type``
     - no
     - ``"None"``
     - Pooling group for this pipe type.
   * - ``priority_index``
     - no
     - none
     - Routing priority (lower routes first).
   * - ``color_r``
     - no
     - ``0.1``
     - Red component of the display color (0–1).
   * - ``color_g``
     - no
     - ``0.1``
     - Green component of the display color (0–1).
   * - ``color_b``
     - no
     - ``0.1``
     - Blue component of the display color (0–1).


Column Reference: ``specifications``
------------------------------------

One row per route. All columns are required. ``start_*`` columns describe the start port,
``end_*`` columns the end port.

.. list-table::
   :header-rows: 1
   :widths: 25 55 20

   * - Column
     - Description
     - Unit
   * - ``name``
     - Specification name.
     - —
   * - ``pipe_type``
     - Pipe-type name; must match a ``name`` in the ``pipe_types`` table.
     - —
   * - ``start_x`` / ``start_y`` / ``start_z``
     - Start-port coordinates.
     - metres
   * - ``start_dx`` / ``start_dy`` / ``start_dz``
     - Start-port exit direction (need not be normalized).
     - —
   * - ``start_length``
     - Straight length imposed at the start port before the first turn.
     - metres
   * - ``end_x`` / ``end_y`` / ``end_z``
     - End-port coordinates.
     - metres
   * - ``end_dx`` / ``end_dy`` / ``end_dz``
     - End-port exit direction (need not be normalized).
     - —
   * - ``end_length``
     - Straight length imposed at the end port.
     - metres

All lengths and coordinates are in metres, consistent with the rest of the routing module.
Direction vectors are normalized automatically.


Example Data
------------

A minimal ``pipe_types.csv``:

.. code-block:: text

   name,radius,radius_of_curvature_ratio,min_straight_length,pooling_type,color_r,color_g,color_b,priority_index,number_turn_max
   hydraulic_12,0.012,2.0,0.05,hydraulic,0.8,0.2,0.1,1,
   electric_8,0.008,,,electric,0.1,0.1,0.8,2,5

A matching ``specifications.csv``:

.. code-block:: text

   name,pipe_type,start_x,start_y,start_z,start_dx,start_dy,start_dz,start_length,end_x,end_y,end_z,end_dx,end_dy,end_dz,end_length
   spec_1,hydraulic_12,0.0,0.0,0.0,1.0,0.0,0.0,0.1,1.0,1.0,0.0,-1.0,0.0,0.0,0.1
   spec_2,electric_8,0.5,0.0,0.0,0.0,1.0,0.0,0.05,0.5,2.0,1.0,0.0,-1.0,0.0,0.05
   spec_3,hydraulic_12,0.0,0.0,1.0,0.0,0.0,1.0,0.08,1.0,1.0,1.0,0.0,0.0,-1.0,0.08

Here ``spec_1`` and ``spec_3`` both reference ``hydraulic_12`` and will therefore share
one ``PipeType`` instance.

A runnable version of this workflow, with sample ``.xlsx`` and ``.csv`` files, lives in
``scripts/simple_test_cases/specification_from_spreadsheet.py``.


Next Steps
----------

- :doc:`basic_routing` — build the CAD map and run the route planner.
- :doc:`port_import_excel` — import *ports only* from a spreadsheet (a lighter alternative
  when you build pipe types and pairings in Python).
- :doc:`../user_guide/core_concepts` — full ``Port``, ``PipeType`` and ``Specification`` reference.