SHAPELY_POLYGON
Constructs a polygon geometry from an outer shell of coordinate pairs and an optional inner hole boundary.
A polygon area with holes can be interpreted as outer area minus interior ring areas:
A_{polygon} = A_{shell} - \sum_i A_{hole,i}
The function returns polygon WKT as the primary value, with area, perimeter, and centroid metadata.
Excel Usage
=SHAPELY_POLYGON(shell, holes)shell(list[list], required): List of [x, y] coordinates for the outer boundary.holes(list[list], optional, default: null): Optional list of lists of coordinates for holes. (Currently treating as single hole or needing complex parsing if multiple holes are passed from Excel 2D range).
Returns (dict): Excel Entity representing the polygon (WKT).
Example 1: Simple Triangle
Inputs:
| shell | |
|---|---|
| 0 | 0 |
| 4 | 0 |
| 0 | 3 |
Excel formula:
=SHAPELY_POLYGON({0,0;4,0;0,3})Expected output:
{"type":"String","basicValue":"POLYGON ((0 0, 4 0, 0 3, 0 0))","properties":{"Area":{"type":"Double","basicValue":6},"Perimeter":{"type":"Double","basicValue":12},"Centroid":{"type":"String","basicValue":"POINT (1.3333333333333333 1)"},"WKT":{"type":"String","basicValue":"POLYGON ((0 0, 4 0, 0 3, 0 0))"}}}
Example 2: Square with Hole
Inputs:
| shell | holes | ||
|---|---|---|---|
| 0 | 0 | 2 | 2 |
| 10 | 0 | 8 | 2 |
| 10 | 10 | 8 | 8 |
| 0 | 10 | 2 | 8 |
Excel formula:
=SHAPELY_POLYGON({0,0;10,0;10,10;0,10}, {2,2;8,2;8,8;2,8})Expected output:
{"type":"String","basicValue":"POLYGON ((0 0, 10 0, 10 10, 0 10, 0 0), (2 2, 8 2, 8 8, 2 8, 2 2))","properties":{"Area":{"type":"Double","basicValue":64},"Perimeter":{"type":"Double","basicValue":64},"Centroid":{"type":"String","basicValue":"POINT (5 5)"},"WKT":{"type":"String","basicValue":"POLYGON ((0 0, 10 0, 10 10, 0 10, 0 0), (2 2, 8 2, 8 8, 2 8, 2 2))"}}}
Example 3: Simple square polygon
Inputs:
| shell | |
|---|---|
| 0 | 0 |
| 5 | 0 |
| 5 | 5 |
| 0 | 5 |
Excel formula:
=SHAPELY_POLYGON({0,0;5,0;5,5;0,5})Expected output:
{"type":"String","basicValue":"POLYGON ((0 0, 5 0, 5 5, 0 5, 0 0))","properties":{"Area":{"type":"Double","basicValue":25},"Perimeter":{"type":"Double","basicValue":20},"Centroid":{"type":"String","basicValue":"POINT (2.5 2.5)"},"WKT":{"type":"String","basicValue":"POLYGON ((0 0, 5 0, 5 5, 0 5, 0 0))"}}}
Example 4: Pentagon shell polygon
Inputs:
| shell | |
|---|---|
| 0 | 0 |
| 3 | 0 |
| 4 | 2 |
| 2 | 4 |
| 0 | 2 |
Excel formula:
=SHAPELY_POLYGON({0,0;3,0;4,2;2,4;0,2})Expected output:
{"type":"String","basicValue":"POLYGON ((0 0, 3 0, 4 2, 2 4, 0 2, 0 0))","properties":{"Area":{"type":"Double","basicValue":11},"Perimeter":{"type":"Double","basicValue":12.8929},"Centroid":{"type":"String","basicValue":"POINT (1.8484848484848484 1.6363636363636365)"},"WKT":{"type":"String","basicValue":"POLYGON ((0 0, 3 0, 4 2, 2 4, 0 2, 0 0))"}}}
Python Code
from shapely import Polygon
def shapely_polygon(shell, holes=None):
"""
Create a geometric polygon from a shell of points and optional holes.
See: https://shapely.readthedocs.io/en/stable/reference/shapely.Polygon.html
This example function is provided as-is without any representation of accuracy.
Args:
shell (list[list]): List of [x, y] coordinates for the outer boundary.
holes (list[list], optional): Optional list of lists of coordinates for holes. (Currently treating as single hole or needing complex parsing if multiple holes are passed from Excel 2D range). Default is None.
Returns:
dict: Excel Entity representing the polygon (WKT).
"""
try:
# Helper to extract points from a list of lists or 2D range
def get_points(data):
pts = []
if not isinstance(data, list):
return []
for row in data:
if isinstance(row, list) and len(row) >= 2:
try:
pts.append((float(row[0]), float(row[1])))
except:
pass
return pts
clean_shell = get_points(shell)
if len(clean_shell) < 3:
return "Error: Polygon shell requires at least 3 points"
# Handle holes.
# Note: Excel passing 3D structures (list of polygons) is hard.
# We will assume 'holes' input is EITHER:
# 1. A single list of points (one hole)
# 2. None/Empty
clean_holes = []
if holes is not None:
# Ideally we'd support multiple holes, but representing list of lists of lists in Excel arguments is tricky.
# For now, let's assume 'holes' is one hole if it looks like a list of points.
one_hole = get_points(holes)
if len(one_hole) >= 3:
clean_holes.append(one_hole)
poly = Polygon(clean_shell, holes=clean_holes)
wkt_str = poly.wkt
return {
"type": "String",
"basicValue": wkt_str,
"properties": {
"Area": {"type": "Double", "basicValue": float(poly.area)},
"Perimeter": {"type": "Double", "basicValue": float(poly.length)},
"Centroid": {"type": "String", "basicValue": poly.centroid.wkt},
"WKT": {"type": "String", "basicValue": wkt_str}
}
}
except Exception as e:
return f"Error: {str(e)}"