LINE
Overview
The LINE function creates standard line visualizations using the Matplotlib library. It is designed to handle tabular data ranges from Excel using a flexible “smart column” pattern:
- Single Column: If a single column of data is provided, the function treats it as the Y-axis values and automatically generates an X-axis index starting at 0.
- Two Columns: The first column is interpreted as the X-axis values, and the second column as the Y-axis values.
- Three or More Columns: The first column is used as the X-axis for all series. Each subsequent column (Column 2, Column 3, etc.) is plotted as an individual line series.
The function automatically handles missing data or non-numeric values by treating them as NaN (Not a Number), which prevents gaps in the data from breaking the chart. It uses the Agg backend for rendering, making it compatible with server-side and browser-based environments like Pyodide.
Key technical features: - Automatic Legend: A legend is automatically displayed when plotting multiple series. - Data Cleaning: Heterogeneous Excel ranges are normalized to numeric floats. - Custom Styling: Supports standard Matplotlib markers (o, s, ^), linestyles (-, --, :, -.), and color palettes.
Excel Usage
=LINE(data, title, xlabel, ylabel, plot_color, linestyle, marker, grid, legend)data(list[list], required): Input data (N rows x M columns). Supports single column (Y-only) or multiple columns (X, Y1, Y2…).title(str, optional, default: null): Chart title.xlabel(str, optional, default: null): Label for X-axis.ylabel(str, optional, default: null): Label for Y-axis.plot_color(str, optional, default: null): Line color.linestyle(str, optional, default: “-”): Line style (e.g., ‘-’, ‘–’, ‘:’, ‘-.’).marker(str, optional, default: null): Marker style (e.g., ‘o’, ‘s’, ‘^’).grid(str, optional, default: “true”): Show grid lines.legend(str, optional, default: “false”): Show legend.
Returns (object): Matplotlib Figure object (standard Python) or base64 encoded PNG string (Pyodide).
Examples
Example 1: Single column (automatic X)
Inputs:
| data | title |
|---|---|
| 10 | Sales Growth |
| 20 | |
| 15 | |
| 25 |
Excel formula:
=LINE({10;20;15;25}, "Sales Growth")Expected output:
"chart"
Example 2: Two columns (X and Y)
Inputs:
| data | xlabel | ylabel | |
|---|---|---|---|
| 1 | 2 | Day | Value |
| 2 | 4 | ||
| 3 | 8 | ||
| 4 | 16 |
Excel formula:
=LINE({1,2;2,4;3,8;4,16}, "Day", "Value")Expected output:
"chart"
Example 3: Multiple series (X, Y1, Y2)
Inputs:
| data | legend | ||
|---|---|---|---|
| 1 | 10 | 5 | true |
| 2 | 20 | 15 | |
| 3 | 15 | 25 |
Excel formula:
=LINE({1,10,5;2,20,15;3,15,25}, "true")Expected output:
"chart"
Example 4: Styled single series
Inputs:
| data | plot_color | linestyle | marker |
|---|---|---|---|
| 1 | red | – | o |
| 4 | |||
| 9 | |||
| 16 |
Excel formula:
=LINE({1;4;9;16}, "red", "--", "o")Expected output:
"chart"
Python Code
import sys
import matplotlib
IS_PYODIDE = sys.platform == "emscripten"
if IS_PYODIDE:
matplotlib.use('Agg')
import matplotlib.pyplot as plt
import io
import base64
import numpy as np
def line(data, title=None, xlabel=None, ylabel=None, plot_color=None, linestyle='-', marker=None, grid='true', legend='false'):
"""
Create a line chart from data.
See: https://matplotlib.org/stable/api/_as_gen/matplotlib.pyplot.plot.html
This example function is provided as-is without any representation of accuracy.
Args:
data (list[list]): Input data (N rows x M columns). Supports single column (Y-only) or multiple columns (X, Y1, Y2...).
title (str, optional): Chart title. Default is None.
xlabel (str, optional): Label for X-axis. Default is None.
ylabel (str, optional): Label for Y-axis. Default is None.
plot_color (str, optional): Line color. Valid options: Blue, Green, Red, Cyan, Magenta, Yellow, Black, White. Default is None.
linestyle (str, optional): Line style (e.g., '-', '--', ':', '-.'). Valid options: Solid, Dashed, Dotted, Dash-dot. Default is '-'.
marker (str, optional): Marker style (e.g., 'o', 's', '^'). Valid options: None, Point, Pixel, Circle, Square, Triangle Down, Triangle Up. Default is None.
grid (str, optional): Show grid lines. Valid options: True, False. Default is 'true'.
legend (str, optional): Show legend. Valid options: True, False. Default is 'false'.
Returns:
object: Matplotlib Figure object (standard Python) or base64 encoded PNG string (Pyodide).
"""
try:
# helper to normalize input to 2D list
def to2d(x):
if isinstance(x, list):
if len(x) > 0 and isinstance(x[0], list):
return x
return [x]
return [[x]]
# helper to convert string booleans
def to_bool(x):
if isinstance(x, str):
return x.lower() == 'true'
return bool(x)
data = to2d(data)
grid = to_bool(grid)
legend = to_bool(legend)
# Clear previous plots
plt.clf()
fig = plt.figure(figsize=(10, 6))
ax = fig.add_subplot(111)
# Convert to numpy array for easier column slicing
# Excel often passes heterogeneous data, so we clean it
row_count = len(data)
col_count = len(data[0]) if row_count > 0 else 0
if row_count == 0 or col_count == 0:
return "Error: No data provided."
# Determine X and Y series
if col_count == 1:
# Case 1: Single column - use index as X
y_data = []
for row in data:
try:
val = float(row[0]) if row[0] is not None and str(row[0]).strip() != "" else np.nan
y_data.append(val)
except:
y_data.append(np.nan)
x = np.arange(len(y_data))
series_to_plot = [(y_data, "Series 1")]
else:
# Case 2+: First column is X, others are Y
x = []
for row in data:
try:
val = float(row[0]) if row[0] is not None and str(row[0]).strip() != "" else np.nan
x.append(val)
except:
x.append(np.nan)
x = np.array(x)
series_to_plot = []
for j in range(1, col_count):
y_col = []
for row in data:
try:
val = float(row[j]) if row[j] is not None and str(row[j]).strip() != "" else np.nan
y_col.append(val)
except:
y_col.append(np.nan)
series_to_plot.append((y_col, f"Series {j}"))
# Plot each series
for y_vals, label in series_to_plot:
# Mask NaNs so they aren't plotted as 0 or breaking the line
y_vals = np.array(y_vals)
mask = ~np.isnan(y_vals)
if not any(mask):
continue
plot_kwargs = {}
if plot_color and len(series_to_plot) == 1: # Only apply single color if one series
plot_kwargs['color'] = plot_color
if linestyle:
plot_kwargs['linestyle'] = linestyle
if marker:
plot_kwargs['marker'] = marker
ax.plot(x[mask], y_vals[mask], label=label, **plot_kwargs)
if title: ax.set_title(title)
if xlabel: ax.set_xlabel(xlabel)
if ylabel: ax.set_ylabel(ylabel)
if grid: ax.grid(True)
if legend or len(series_to_plot) > 1:
ax.legend()
if IS_PYODIDE:
buf = io.BytesIO()
plt.savefig(buf, format='png', bbox_inches='tight')
plt.close(fig)
buf.seek(0)
img_str = base64.b64encode(buf.read()).decode('utf-8')
return f"data:image/png;base64,{img_str}"
else:
return fig
except Exception as e:
return f"Error: {str(e)}"