Skip to content

armgit / macro

Public Imported from GitHub
0

Macro is a unified workspace for teams: email, chat, docs, tasks, agents, calls, and CRM — @-linked together with shared AI memory.

21.2 KB
Raw Blame
import {
  type ChartGrouping,
  type ChartKind,
  type ChartPlot,
  type ChartRange,
  type ChartSeries,
  isChartLiteral,
  parseChartReference,
  type SheetChart,
} from '@macro-inc/spreadsheet/sheet-drawings';
import { formatCellAddress } from './spreadsheet-document';

/**
 * Chart types Macro creates and edits, with their Excel grouping. Types
 * marked `more` are offered after the common ones.
 */
export const CHART_TYPES = [
  { id: 'column', label: 'Column', kind: 'column' },
  {
    id: 'column-stacked',
    label: 'Stacked column',
    kind: 'column',
    grouping: 'stacked',
  },
  {
    id: 'column-percent',
    label: '100% stacked column',
    kind: 'column',
    grouping: 'percentStacked',
  },
  { id: 'bar', label: 'Bar', kind: 'bar' },
  { id: 'bar-stacked', label: 'Stacked bar', kind: 'bar', grouping: 'stacked' },
  {
    id: 'bar-percent',
    label: '100% stacked bar',
    kind: 'bar',
    grouping: 'percentStacked',
    catalog: true,
  },
  { id: 'line', label: 'Line', kind: 'line' },
  {
    id: 'line-stacked',
    label: 'Stacked line',
    kind: 'line',
    grouping: 'stacked',
    catalog: true,
  },
  {
    id: 'line-percent',
    label: '100% stacked line',
    kind: 'line',
    grouping: 'percentStacked',
    catalog: true,
  },
  { id: 'area', label: 'Area', kind: 'area' },
  {
    id: 'area-stacked',
    label: 'Stacked area',
    kind: 'area',
    grouping: 'stacked',
  },
  {
    id: 'area-percent',
    label: '100% stacked area',
    kind: 'area',
    grouping: 'percentStacked',
    catalog: true,
  },
  { id: 'pie', label: 'Pie', kind: 'pie' },
  { id: 'doughnut', label: 'Doughnut', kind: 'doughnut' },
  { id: 'scatter', label: 'Scatter', kind: 'scatter' },
  // Columns for every series but the last, which is a line on its own axis.
  { id: 'combo', label: 'Combo', kind: 'column', combo: true, catalog: true },
  { id: 'radar', label: 'Radar', kind: 'radar', more: true },
  {
    id: 'radar-filled',
    label: 'Filled radar',
    kind: 'radar',
    filled: true,
    more: true,
  },
  { id: 'bubble', label: 'Bubble', kind: 'bubble', more: true },
  { id: 'stock', label: 'Stock', kind: 'stock', more: true },
  { id: 'contour', label: 'Contour', kind: 'surface', more: true },
  { id: 'histogram', label: 'Histogram', kind: 'histogram', more: true },
  { id: 'pareto', label: 'Pareto', kind: 'pareto', catalog: true },
  { id: 'waterfall', label: 'Waterfall', kind: 'waterfall', more: true },
  { id: 'funnel', label: 'Funnel', kind: 'funnel', more: true },
  {
    id: 'box-whisker',
    label: 'Box and whisker',
    kind: 'boxWhisker',
    more: true,
  },
  { id: 'treemap', label: 'Treemap', kind: 'treemap', more: true },
  { id: 'sunburst', label: 'Sunburst', kind: 'sunburst', more: true },
  {
    id: 'pie-of-pie',
    label: 'Pie of pie',
    kind: 'pie',
    ofPie: 'pie',
    catalog: true,
  },
  {
    id: 'bar-of-pie',
    label: 'Bar of pie',
    kind: 'pie',
    ofPie: 'bar',
    catalog: true,
  },
] as const satisfies readonly {
  id: string;
  label: string;
  kind: ChartKind;
  grouping?: ChartGrouping;
  filled?: true;
  /** A pie with some points in a second pie or a bar. */
  ofPie?: 'pie' | 'bar';
  /** Columns plus a line on the secondary axis. */
  combo?: true;
  /**
   * Listed in Edit chart. Kept out of the insert menu so that menu, and its
   * submenu, still fit on screen.
   */
  catalog?: true;
  more?: true;
}[];
export type ChartTypeId = (typeof CHART_TYPES)[number]['id'];
type ChartType = (typeof CHART_TYPES)[number];

const definitionOf = (type: ChartTypeId): ChartType | undefined =>
  CHART_TYPES.find((entry) => entry.id === type);
const groupingOf = (type: ChartType) =>
  'grouping' in type ? type.grouping : undefined;
const filledOf = (type: ChartType) => 'filled' in type && type.filled;
const comboOf = (type: ChartType) => 'combo' in type && type.combo;
const ofPieOf = (type: ChartType) => ('ofPie' in type ? type.ofPie : undefined);
/** Kinds whose series all belong in one plot. */
const SINGLE_PLOT: readonly ChartKind[] = [
  'radar',
  'bubble',
  'stock',
  'surface',
  'funnel',
  'boxWhisker',
];
const isRound = (kind: ChartKind) => kind === 'pie' || kind === 'doughnut';
/** Kinds that show one series, as Excel's do. */
const ONE_SERIES: readonly ChartKind[] = [
  'pie',
  'doughnut',
  'histogram',
  'pareto',
  'waterfall',
  'treemap',
  'sunburst',
];
/** Kinds whose labels are levels, in every column before the values. */
const isNested = (kind: ChartKind) => kind === 'treemap' || kind === 'sunburst';
/** Kinds that plot numbers on both axes. */
const isXy = (kind: ChartKind) => kind === 'scatter' || kind === 'bubble';

/** Columns together with a line, as a combination chart. */
function isCombo(chart: SheetChart): boolean {
  return (
    chart.plots.length > 1 &&
    chart.plots.every(
      (plot) => plot.kind === 'column' || plot.kind === 'line'
    ) &&
    chart.plots.some((plot) => plot.kind === 'column') &&
    chart.plots.some((plot) => plot.kind === 'line')
  );
}

/** The type a chart's first plot shows, as Macro offers it. */
export function chartType(chart: SheetChart): ChartTypeId | undefined {
  if (isCombo(chart)) return 'combo';
  const plot = chart.plots[0];
  return CHART_TYPES.find(
    (type) =>
      !comboOf(type) &&
      type.kind === plot?.kind &&
      groupingOf(type) ===
        (plot.grouping === 'clustered' ? undefined : plot.grouping) &&
      filledOf(type) === !!plot.filled &&
      ofPieOf(type) === plot.ofPie?.type
  )?.id;
}

/**
 * Why charts of a type cannot show this many series, if they cannot: a
 * stock chart reads three or four, as Excel's do.
 */
export function chartTypeProblem(
  type: ChartTypeId,
  series: number
): string | undefined {
  if (definitionOf(type)?.kind === 'stock' && (series < 3 || series > 4))
    return 'A stock chart needs three or four series: high, low and close, or open, high, low and close.';
  if (type === 'combo' && series < 2)
    return 'A combo chart needs at least two series: columns, and a line on the secondary axis.';
}

export type CellBounds = {
  top: number;
  left: number;
  bottom: number;
  right: number;
};

/**
 * How a chart reads a block of cells: each series is a column or a row, and
 * the first row and first column may hold series names and category labels
 * (a scatter chart's x values).
 */
export type ChartLayout = {
  range: CellBounds;
  orientation: 'columns' | 'rows';
  firstRow: boolean;
  firstColumn: boolean;
};

/** A cell as charts read it. */
type CellValue = { text: string; number?: number } | undefined;

/**
 * The block of filled cells around a cell, as Excel charts by default: it
 * grows while a neighboring row or column has a filled cell.
 */
export function dataRegion(
  filled: (row: number, column: number) => boolean,
  start: CellBounds,
  limits: { rows: number; columns: number }
): CellBounds | undefined {
  const area = { ...start };
  const rowFilled = (row: number, from: number, to: number) => {
    for (
      let column = Math.max(0, from);
      column <= Math.min(limits.columns - 1, to);
      column++
    )
      if (filled(row, column)) return true;
    return false;
  };
  const columnFilled = (column: number, from: number, to: number) => {
    for (
      let row = Math.max(0, from);
      row <= Math.min(limits.rows - 1, to);
      row++
    )
      if (filled(row, column)) return true;
    return false;
  };
  // A selection of several cells is charted as it is.
  if (area.top !== area.bottom || area.left !== area.right) return area;
  if (
    !filled(area.top, area.left) &&
    !rowFilled(area.top - 1, area.left - 1, area.right + 1) &&
    !rowFilled(area.top + 1, area.left - 1, area.right + 1) &&
    !columnFilled(area.left - 1, area.top, area.bottom) &&
    !columnFilled(area.left + 1, area.top, area.bottom)
  )
    return;
  for (let changed = true; changed; ) {
    changed = false;
    if (
      area.top > 0 &&
      rowFilled(area.top - 1, area.left - 1, area.right + 1)
    ) {
      area.top--;
      changed = true;
    }
    if (
      area.bottom < limits.rows - 1 &&
      rowFilled(area.bottom + 1, area.left - 1, area.right + 1)
    ) {
      area.bottom++;
      changed = true;
    }
    if (
      area.left > 0 &&
      columnFilled(area.left - 1, area.top - 1, area.bottom + 1)
    ) {
      area.left--;
      changed = true;
    }
    if (
      area.right < limits.columns - 1 &&
      columnFilled(area.right + 1, area.top - 1, area.bottom + 1)
    ) {
      area.right++;
      changed = true;
    }
  }
  return area;
}

/**
 * Excel's reading of a block: text in the first column (or a blank corner)
 * makes it labels, text in the first row makes it names, and series follow
 * the longer side.
 */
export function guessLayout(
  range: CellBounds,
  value: (row: number, column: number) => CellValue,
  type?: ChartTypeId
): ChartLayout {
  const text = (row: number, column: number) => {
    const cell = value(row, column);
    return !!cell && cell.text.trim() !== '' && cell.number === undefined;
  };
  const blank = (row: number, column: number) =>
    !value(row, column)?.text.trim();
  const rows = range.bottom - range.top + 1;
  const columns = range.right - range.left + 1;
  const corner = blank(range.top, range.left);
  let firstColumn = columns > 1 && corner;
  for (let row = range.top + 1; !firstColumn && row <= range.bottom; row++)
    firstColumn = columns > 1 && text(row, range.left);
  let firstRow = rows > 1 && corner;
  for (
    let column = range.left + (firstColumn ? 1 : 0);
    !firstRow && column <= range.right;
    column++
  )
    firstRow = rows > 1 && text(range.top, column);
  const dataRows = rows - (firstRow ? 1 : 0);
  const dataColumns = columns - (firstColumn ? 1 : 0);
  const orientation = dataRows >= dataColumns ? 'columns' : 'rows';
  const kind = type && definitionOf(type)?.kind;
  // Scatter and bubble charts' x values, and a treemap's or sunburst's
  // levels, come before the values even when they are numbers.
  if (kind && (isXy(kind) || isNested(kind))) {
    if (orientation === 'columns' && columns > 1) firstColumn = true;
    if (orientation === 'rows' && rows > 1) firstRow = true;
  }
  return { range, orientation, firstRow, firstColumn };
}

const quote = (sheet: string) => `'${sheet.replaceAll("'", "''")}'`;

function reference(sheet: string, area: CellBounds) {
  const start = formatCellAddress(area.top, area.left).replace(
    /^([A-Z]+)(\d+)$/,
    '$$$1$$$2'
  );
  const end = formatCellAddress(area.bottom, area.right).replace(
    /^([A-Z]+)(\d+)$/,
    '$$$1$$$2'
  );
  return `${quote(sheet)}!${start === end ? start : `${start}:${end}`}`;
}

/**
 * Options a type sets as Excel's do: labeled values make a histogram of
 * categories, waterfalls and funnels write their values, and a pie of pie
 * or bar of pie names its second plot.
 */
function kindOptions(
  type: ChartType,
  series: ChartSeries[]
): Partial<ChartPlot> {
  const ofPie = ofPieOf(type);
  if (ofPie) return { ofPie: { type: ofPie } };
  if (type.kind === 'histogram' || type.kind === 'pareto')
    return series.some((value) => value.categories !== undefined)
      ? { aggregate: true }
      : {};
  if (type.kind === 'waterfall' || type.kind === 'funnel')
    return { labels: true };
  return {};
}

/**
 * A chart of a block of cells, or undefined when the block has no values
 * beside its names and labels.
 */
export function chartFromLayout(
  type: ChartTypeId,
  sheet: string,
  layout: ChartLayout,
  previous?: SheetChart
): SheetChart | undefined {
  const definition = definitionOf(type);
  if (!definition) return;
  const { range, orientation, firstRow, firstColumn } = layout;
  const dataTop = range.top + (firstRow ? 1 : 0);
  const dataLeft = range.left + (firstColumn ? 1 : 0);
  if (dataTop > range.bottom || dataLeft > range.right) return;
  const references: string[] = [];
  const add = (area: CellBounds) => {
    references.push(reference(sheet, area));
    return references.length - 1;
  };
  const series: ChartSeries[] = [];
  const lines =
    orientation === 'columns'
      ? range.right - dataLeft + 1
      : range.bottom - dataTop + 1;
  // A bubble series reads its values and then its sizes.
  const bubble = definition.kind === 'bubble';
  const step = bubble && lines > 1 ? 2 : 1;
  const count = Math.ceil(lines / step);
  const shown = ONE_SERIES.includes(definition.kind) ? 1 : Math.min(count, 255);
  const colors = previous?.plots.flatMap((plot) =>
    plot.series.map((value) => value.color)
  );
  /** The `index`th line of data: a column or a row of values. */
  const line = (index: number): CellBounds =>
    orientation === 'columns'
      ? {
          top: dataTop,
          bottom: range.bottom,
          left: dataLeft + index,
          right: dataLeft + index,
        }
      : {
          top: dataTop + index,
          bottom: dataTop + index,
          left: dataLeft,
          right: range.right,
        };
  // A treemap's or sunburst's levels are every line before its values.
  const nested =
    isNested(definition.kind) &&
    (orientation === 'columns'
      ? firstColumn && range.right > range.left
      : firstRow && range.bottom > range.top);
  if (nested) {
    const columns = orientation === 'columns';
    const last = columns ? range.right : range.bottom;
    const name = columns
      ? firstRow && {
          top: range.top,
          bottom: range.top,
          left: last,
          right: last,
        }
      : firstColumn && {
          top: last,
          bottom: last,
          left: range.left,
          right: range.left,
        };
    const color = colors?.[0];
    series.push({
      ...(name && { nameRef: add(name) }),
      categories: add(
        columns
          ? {
              top: dataTop,
              bottom: range.bottom,
              left: range.left,
              right: last - 1,
            }
          : {
              top: range.top,
              bottom: last - 1,
              left: dataLeft,
              right: range.right,
            }
      ),
      values: add(
        columns
          ? { top: dataTop, bottom: range.bottom, left: last, right: last }
          : { top: last, bottom: last, left: dataLeft, right: range.right }
      ),
      ...(color && { color }),
    });
  }
  for (let index = 0; !nested && index < shown; index++) {
    const at = index * step;
    const name =
      orientation === 'columns'
        ? firstRow && {
            top: range.top,
            bottom: range.top,
            left: dataLeft + at,
            right: dataLeft + at,
          }
        : firstColumn && {
            top: dataTop + at,
            bottom: dataTop + at,
            left: range.left,
            right: range.left,
          };
    const labels =
      orientation === 'columns'
        ? firstColumn && {
            top: dataTop,
            bottom: range.bottom,
            left: range.left,
            right: range.left,
          }
        : firstRow && {
            top: range.top,
            bottom: range.top,
            left: dataLeft,
            right: range.right,
          };
    const color = colors?.[index];
    series.push({
      ...(name && { nameRef: add(name) }),
      ...(labels && { categories: add(labels) }),
      values: add(line(at)),
      ...(bubble && at + 1 < lines && { sizes: add(line(at + 1)) }),
      ...(color && { color }),
      // Prices are drawn by their high-low lines and bars, not series lines.
      // A scatter chart plots markers; connecting them would invent an order.
      ...((definition.kind === 'stock' || definition.kind === 'scatter') && {
        noLine: true as const,
      }),
    });
  }
  const shell = {
    ...(previous?.title && { title: previous.title }),
    ...(previous
      ? previous.legend && { legend: previous.legend }
      : // A waterfall's legend names its colors; a treemap's, its branches.
        definition.kind === 'waterfall'
        ? { legend: 'top' as const }
        : definition.kind === 'treemap'
          ? { legend: 'right' as const }
          : (series.length > 1 ||
              isRound(definition.kind) ||
              definition.kind === 'surface' ||
              comboOf(definition)) && { legend: 'bottom' as const }),
    references,
    ...(previous?.colors && { colors: previous.colors }),
  };
  // The last series uses its own axis, as Excel's column-and-line chart does.
  if (comboOf(definition)) {
    const line = series.at(-1);
    if (!line || series.length < 2) return;
    return {
      ...shell,
      plots: [
        { kind: 'column', series: series.slice(0, -1) },
        { kind: 'line', secondary: true, series: [line] },
      ],
    };
  }
  const plot: ChartPlot = {
    kind: definition.kind,
    ...('grouping' in definition && { grouping: definition.grouping }),
    ...(filledOf(definition) && { filled: true }),
    ...(definition.kind === 'stock' && {
      hiLow: true,
      ...(series.length === 4 && { upDown: true }),
    }),
    ...kindOptions(definition, series),
    series,
  };
  return { ...shell, plots: [plot] };
}

/** How many series a block of cells makes in a chart of a type. */
export function layoutSeries(type: ChartTypeId, layout: ChartLayout): number {
  const { range, orientation, firstRow, firstColumn } = layout;
  const lines = Math.max(
    0,
    orientation === 'columns'
      ? range.right - range.left + 1 - (firstColumn ? 1 : 0)
      : range.bottom - range.top + 1 - (firstRow ? 1 : 0)
  );
  return definitionOf(type)?.kind === 'bubble' && lines > 1
    ? Math.ceil(lines / 2)
    : lines;
}

/**
 * The block of cells a chart reads and how, when its ranges form one block
 * on one sheet, as charts Macro makes do.
 */
export function chartLayout(
  chart: SheetChart,
  home: string
): { sheet: string; layout: ChartLayout } | undefined {
  const ranges = chart.references
    .filter((value) => !isChartLiteral(value))
    .map((value) => parseChartReference(value.replace(/^\[0\]!/, '')));
  if (!ranges.length || ranges.some((value) => !value)) return;
  const sheets = new Set(
    (ranges as ChartRange[]).map((value) => (value.sheet ?? home).toLowerCase())
  );
  if (sheets.size !== 1) return;
  const parsed = ranges as ChartRange[];
  const range = {
    top: Math.min(...parsed.map((value) => value.top)),
    left: Math.min(...parsed.map((value) => value.left)),
    bottom: Math.max(...parsed.map((value) => value.bottom)),
    right: Math.max(...parsed.map((value) => value.right)),
  };
  const all = chart.plots.flatMap((plot) => plot.series);
  const first = all[0];
  const values = first && parseChartReference(chart.references[first.values]);
  if (!values) return;
  const orientation =
    values.top === values.bottom && values.left !== values.right
      ? 'rows'
      : 'columns';
  const names = all.some((series) => series.nameRef !== undefined);
  const labels = all.some((series) => series.categories !== undefined);
  return {
    sheet: parsed[0].sheet ?? home,
    layout: {
      range,
      orientation,
      firstRow: orientation === 'columns' ? names : labels,
      firstColumn: orientation === 'columns' ? labels : names,
    },
  };
}

/**
 * A chart with another type for each of its plots, keeping its data; one
 * plot for types that combine with no other. Undefined when the type
 * cannot show the chart's series.
 */
export function withChartType(
  chart: SheetChart,
  type: ChartTypeId
): SheetChart | undefined {
  const definition = definitionOf(type);
  if (!definition) return chart;
  const kind = definition.kind;
  // Stock and scatter series have no lines of their own; other kinds draw theirs.
  const shown = (series: ChartSeries, from: ChartKind): ChartSeries => {
    if (kind === 'stock' || kind === 'scatter')
      return { ...series, noLine: true };
    if (from !== 'stock' && from !== 'scatter') return series;
    const { noLine: _noLine, ...rest } = series;
    return rest;
  };
  const all = chart.plots.flatMap((plot) =>
    plot.series.map((series) => shown(series, plot.kind))
  );
  if (chartTypeProblem(type, all.length)) return;
  if (comboOf(definition)) {
    const line = all.at(-1);
    if (!line) return;
    return {
      ...chart,
      plots: [
        { kind: 'column', series: all.slice(0, -1) },
        { kind: 'line', secondary: true, series: [line] },
      ],
    };
  }
  const flags = (series: ChartSeries[]) => ({
    ...('grouping' in definition && { grouping: definition.grouping }),
    ...(filledOf(definition) && { filled: true as const }),
    ...(kind === 'stock' && {
      hiLow: true as const,
      ...(series.length === 4 && { upDown: true as const }),
    }),
    ...kindOptions(definition, series),
  });
  if (ONE_SERIES.includes(kind)) {
    const first = all.slice(0, 1);
    return { ...chart, plots: [{ kind, ...flags(first), series: first }] };
  }
  if (SINGLE_PLOT.includes(kind))
    return {
      ...chart,
      plots: [{ kind, ...flags(all), series: all }],
    };
  return {
    ...chart,
    plots: chart.plots.map((plot) => ({
      kind,
      ...flags(plot.series),
      ...(plot.secondary && { secondary: true }),
      series: plot.series.map((series) => shown(series, plot.kind)),
    })),
  };
}

/** What the chart dialog shows and changes. */
export type ChartSettings = {
  type: ChartTypeId | undefined;
  title: string;
  legend: NonNullable<SheetChart['legend']> | 'none';
  /** The cells charted, as `A1:C7` or `'Sheet'!A1:C7`; empty when they are several ranges. */
  range: string;
  orientation: ChartLayout['orientation'];
  firstRow: boolean;
  firstColumn: boolean;
};