Macro is a unified workspace for teams: email, chat, docs, tasks, agents, calls, and CRM — @-linked together with shared AI memory.
TypeScript agentaiai-agentsall-in-onecrmcrm-systememaillinearmcpmessagingnotesnotionofficerustslackslack-alternativestartupstartup-toolstypescriptworkspace
import type { SheetChart } from '@macro-inc/spreadsheet/sheet-drawings';
import { describe, expect, it } from 'vitest';
import {
chartFromLayout,
chartLayout,
chartType,
chartTypeProblem,
dataRegion,
guessLayout,
layoutSeries,
withChartType,
} from './chart-builder';
import { formatCellAddress } from './spreadsheet-document';
/** A sheet as rows of cells from A1; numbers are numbers. */
function sheet(rows: (string | number | undefined)[][]) {
const cells = new Map<string, string | number>();
rows.forEach((row, rowIndex) =>
row.forEach((value, column) => {
if (value !== undefined && value !== '')
cells.set(formatCellAddress(rowIndex, column), value);
})
);
return {
filled: (row: number, column: number) =>
cells.has(formatCellAddress(row, column)),
value: (row: number, column: number) => {
const value = cells.get(formatCellAddress(row, column));
if (value === undefined) return;
return typeof value === 'number'
? { text: String(value), number: value }
: { text: value };
},
};
}
const limits = { rows: 100, columns: 26 };
describe('charts of cells', () => {
const sales = sheet([
['Month', 'Revenue', 'Costs'],
['Jan', 1000, 700],
['Feb', 1250, 820],
['Mar', 1500, 940],
[],
['Note: unaudited'],
]);
it('charts the table around a cell, as Excel does', () => {
const region = dataRegion(
sales.filled,
{ top: 2, left: 1, bottom: 2, right: 1 },
limits
);
expect(region).toEqual({ top: 0, left: 0, bottom: 3, right: 2 });
// Nothing nearby: nothing to chart.
expect(
dataRegion(
sales.filled,
{ top: 20, left: 10, bottom: 20, right: 10 },
limits
)
).toBeUndefined();
// A selection is charted as it is.
expect(
dataRegion(sales.filled, { top: 0, left: 0, bottom: 3, right: 1 }, limits)
).toEqual({ top: 0, left: 0, bottom: 3, right: 1 });
});
it('reads names, labels and the series direction', () => {
const range = { top: 0, left: 0, bottom: 3, right: 2 };
expect(guessLayout(range, sales.value)).toEqual({
range,
orientation: 'columns',
firstRow: true,
firstColumn: true,
});
// Wider than tall: each row is a series.
const wide = sheet([
['', 'Q1', 'Q2', 'Q3', 'Q4'],
['North', 1, 2, 3, 4],
['South', 5, 6, 7, 8],
]);
expect(
guessLayout({ top: 0, left: 0, bottom: 2, right: 4 }, wide.value)
).toMatchObject({ orientation: 'rows', firstRow: true, firstColumn: true });
// Numbers only: no names or labels.
const numbers = sheet([[1], [2], [3]]);
expect(
guessLayout({ top: 0, left: 0, bottom: 2, right: 0 }, numbers.value)
).toMatchObject({ firstRow: false, firstColumn: false });
});
it('builds a chart of each series with its name and labels', () => {
const chart = chartFromLayout('column-stacked', 'Q1 Sales', {
range: { top: 0, left: 0, bottom: 3, right: 2 },
orientation: 'columns',
firstRow: true,
firstColumn: true,
});
expect(chart).toEqual({
legend: 'bottom',
plots: [
{
kind: 'column',
grouping: 'stacked',
series: [
{ nameRef: 0, categories: 1, values: 2 },
{ nameRef: 3, categories: 4, values: 5 },
],
},
],
references: [
"'Q1 Sales'!$B$1",
"'Q1 Sales'!$A$2:$A$4",
"'Q1 Sales'!$B$2:$B$4",
"'Q1 Sales'!$C$1",
"'Q1 Sales'!$A$2:$A$4",
"'Q1 Sales'!$C$2:$C$4",
],
});
expect(chartType(chart!)).toBe('column-stacked');
// A pie shows the first series; rows make series of rows.
const pie = chartFromLayout('pie', 'Q1 Sales', {
range: { top: 0, left: 0, bottom: 2, right: 4 },
orientation: 'rows',
firstRow: true,
firstColumn: true,
});
expect(pie?.plots[0].series).toEqual([
{ nameRef: 0, categories: 1, values: 2 },
]);
expect(pie?.references).toEqual([
"'Q1 Sales'!$A$2",
"'Q1 Sales'!$B$1:$E$1",
"'Q1 Sales'!$B$2:$E$2",
]);
// Only names and labels: nothing to chart.
expect(
chartFromLayout('line', 'Sheet1', {
range: { top: 0, left: 0, bottom: 0, right: 2 },
orientation: 'columns',
firstRow: true,
firstColumn: false,
})
).toBeUndefined();
});
it('finds the cells and reading of a chart, and changes its type', () => {
const layout = {
range: { top: 0, left: 0, bottom: 3, right: 2 },
orientation: 'columns' as const,
firstRow: true,
firstColumn: true,
};
const chart = chartFromLayout('line', 'Sales', layout)!;
expect(chartLayout(chart, 'Sales')).toEqual({ sheet: 'Sales', layout });
// Ranges on several sheets are not one block.
expect(
chartLayout(
{ ...chart, references: [...chart.references.slice(1), 'Other!A1'] },
'Sales'
)
).toBeUndefined();
const pie = withChartType(chart, 'pie');
expect(pie?.plots).toEqual([
{ kind: 'pie', series: [chart.plots[0].series[0]] },
]);
expect(chartType(withChartType(chart, 'bar-stacked')!)).toBe('bar-stacked');
expect(chartType(withChartType(chart, 'bar-percent')!)).toBe('bar-percent');
expect(chartType(withChartType(chart, 'line-stacked')!)).toBe(
'line-stacked'
);
expect(chartType(withChartType(chart, 'line-percent')!)).toBe(
'line-percent'
);
expect(chartType(withChartType(chart, 'area-percent')!)).toBe(
'area-percent'
);
});
it('plots against the first column for scatter and bubble charts', () => {
const points = sheet([
['Units', 'Price', 'Share'],
[10, 4, 0.2],
[20, 3, 0.5],
[30, 2, 0.3],
]);
const range = { top: 0, left: 0, bottom: 3, right: 2 };
// A line chart charts each column; a scatter chart plots them against
// the first, as in Excel.
expect(guessLayout(range, points.value, 'line')).toMatchObject({
firstColumn: false,
});
const layout = guessLayout(range, points.value, 'scatter');
expect(layout).toMatchObject({
orientation: 'columns',
firstRow: true,
firstColumn: true,
});
const scatter = chartFromLayout('scatter', 'Sheet1', layout);
expect(scatter?.plots[0].series).toHaveLength(2);
// Points are not joined: a scatter has no order along a line.
expect(scatter?.plots[0].series.every((series) => series.noLine)).toBe(
true
);
// A bubble series reads its values, then its sizes.
const bubble = chartFromLayout(
'bubble',
'Sheet1',
guessLayout(range, points.value, 'bubble')
);
expect(bubble?.plots[0].series).toEqual([
{ nameRef: 0, categories: 1, values: 2, sizes: 3 },
]);
expect(bubble?.references).toEqual([
"'Sheet1'!$B$1",
"'Sheet1'!$A$2:$A$4",
"'Sheet1'!$B$2:$B$4",
"'Sheet1'!$C$2:$C$4",
]);
expect(layoutSeries('bubble', layout)).toBe(1);
});
it('makes stock charts of three or four series', () => {
const layout = (right: number) => ({
range: { top: 0, left: 0, bottom: 3, right },
orientation: 'columns' as const,
firstRow: true,
firstColumn: true,
});
expect(chartTypeProblem('stock', layoutSeries('stock', layout(2)))).toBe(
'A stock chart needs three or four series: high, low and close, or open, high, low and close.'
);
expect(chartTypeProblem('line', 2)).toBeUndefined();
expect(chartTypeProblem('combo', 1)).toBe(
'A combo chart needs at least two series: columns, and a line on the secondary axis.'
);
// Open, high, low and close: bars from open to close.
const prices = chartFromLayout('stock', 'Sheet1', layout(4));
expect(prices?.plots[0]).toMatchObject({
kind: 'stock',
hiLow: true,
upDown: true,
});
expect(
chartFromLayout('stock', 'Sheet1', layout(3))?.plots[0].upDown
).toBeUndefined();
expect(chartType(prices!)).toBe('stock');
});
it('shows the series of every plot in one radar, stock or contour plot', () => {
const combo: SheetChart = {
plots: [
{ kind: 'column', series: [{ values: 0 }] },
{ kind: 'line', secondary: true, series: [{ values: 1 }] },
],
references: ['A1:A3', 'B1:B3'],
};
expect(chartType(combo)).toBe('combo');
const radar = withChartType(combo, 'radar-filled');
expect(radar?.plots).toEqual([
{ kind: 'radar', filled: true, series: [{ values: 0 }, { values: 1 }] },
]);
expect(chartType(radar!)).toBe('radar-filled');
expect(chartType(withChartType(combo, 'radar')!)).toBe('radar');
expect(chartType(withChartType(combo, 'contour')!)).toBe('contour');
// Two series cannot make a stock chart.
expect(withChartType(combo, 'stock')).toBeUndefined();
const lines: SheetChart = {
plots: [
{
kind: 'line',
series: [{ values: 0 }, { values: 1 }, { values: 2 }],
},
],
references: ['A1:A3', 'B1:B3', 'C1:C3'],
};
const stock = withChartType(lines, 'stock');
expect(stock?.plots).toEqual([
{
kind: 'stock',
hiLow: true,
series: [
{ values: 0, noLine: true },
{ values: 1, noLine: true },
{ values: 2, noLine: true },
],
},
]);
// As lines again, the series draw their lines.
expect(withChartType(stock!, 'line')?.plots).toEqual(lines.plots);
// A scatter's markers become a line again, and a combo splits the series.
const marked = withChartType(lines, 'scatter');
expect(marked?.plots[0].series.every((series) => series.noLine)).toBe(true);
expect(withChartType(marked!, 'line')?.plots).toEqual(lines.plots);
const built = chartFromLayout('combo', 'Sheet1', {
range: { top: 0, left: 0, bottom: 3, right: 2 },
orientation: 'columns',
firstRow: true,
firstColumn: true,
});
expect(chartType(built!)).toBe('combo');
expect(built?.plots).toMatchObject([
{ kind: 'column', series: [{}] },
{ kind: 'line', secondary: true, series: [{}] },
]);
expect(withChartType(lines, 'combo')?.plots).toEqual([
{ kind: 'column', series: [{ values: 0 }, { values: 1 }] },
{ kind: 'line', secondary: true, series: [{ values: 2 }] },
]);
});
});
describe("Excel 2016's charts and pie of pie", () => {
const scores = sheet([['Score'], [52], [58], [61], [63], [67]]);
const defects = sheet([
['Cause', 'Count'],
['Scratches', 40],
['Dents', 10],
['Chips', 30],
]);
const stores = sheet([
['Region', 'Store', 'Sales'],
['North', 'Downtown', 30],
[undefined, 'Airport', 20],
['South', 'Harbor', 25],
['West', undefined, 10],
]);
it('bins a column of numbers, and sums labeled values by category', () => {
const range = { top: 0, left: 0, bottom: 5, right: 0 };
expect(
chartFromLayout(
'histogram',
'Data',
guessLayout(range, scores.value, 'histogram')
)
).toEqual({
plots: [{ kind: 'histogram', series: [{ nameRef: 0, values: 1 }] }],
references: ["'Data'!$A$1", "'Data'!$A$2:$A$6"],
});
const labeled = { top: 0, left: 0, bottom: 3, right: 1 };
expect(
chartFromLayout(
'pareto',
'Data',
guessLayout(labeled, defects.value, 'pareto')
)?.plots
).toEqual([
{
kind: 'pareto',
aggregate: true,
series: [{ nameRef: 0, categories: 1, values: 2 }],
},
]);
});
it('labels waterfall and funnel bars, and legends the waterfall', () => {
const layout = guessLayout(
{ top: 0, left: 0, bottom: 3, right: 1 },
defects.value,
'waterfall'
);
expect(chartFromLayout('waterfall', 'Data', layout)).toMatchObject({
legend: 'top',
plots: [{ kind: 'waterfall', labels: true }],
});
expect(chartFromLayout('funnel', 'Data', layout)?.plots).toEqual([
{
kind: 'funnel',
labels: true,
series: [{ nameRef: 0, categories: 1, values: 2 }],
},
]);
});
it('reads every column before the values as a level of a treemap or sunburst', () => {
const range = { top: 0, left: 0, bottom: 4, right: 2 };
const layout = guessLayout(range, stores.value, 'treemap');
expect(layout).toEqual({
range,
orientation: 'columns',
firstRow: true,
firstColumn: true,
});
const treemap = chartFromLayout('treemap', 'Data', layout);
expect(treemap).toEqual({
legend: 'right',
plots: [
{
kind: 'treemap',
series: [{ nameRef: 0, categories: 1, values: 2 }],
},
],
references: ["'Data'!$C$1", "'Data'!$A$2:$B$5", "'Data'!$C$2:$C$5"],
});
// The chart's cells read back as the same block.
expect(treemap && chartLayout(treemap, 'Data')).toEqual({
sheet: 'Data',
layout,
});
expect(
chartFromLayout('sunburst', 'Data', layout)?.plots[0].series
).toEqual([{ nameRef: 0, categories: 1, values: 2 }]);
});
it('switches among pie of pie, bar of pie and other types', () => {
const layout = guessLayout(
{ top: 0, left: 0, bottom: 3, right: 1 },
defects.value
);
const ofPie = chartFromLayout('pie-of-pie', 'Data', layout);
expect(ofPie?.plots).toEqual([
{
kind: 'pie',
ofPie: { type: 'pie' },
series: [{ nameRef: 0, categories: 1, values: 2 }],
},
]);
if (!ofPie) throw new Error('Expected a chart.');
expect(chartType(ofPie)).toBe('pie-of-pie');
const bar = withChartType(ofPie, 'bar-of-pie');
expect(bar && chartType(bar)).toBe('bar-of-pie');
const pie = withChartType(ofPie, 'pie');
expect(pie?.plots[0]).toEqual({
kind: 'pie',
series: [{ nameRef: 0, categories: 1, values: 2 }],
});
// A labeled column chart becomes a histogram of its categories.
const columns = chartFromLayout('column', 'Data', layout);
const histogram = columns && withChartType(columns, 'histogram');
expect(histogram?.plots).toEqual([
{
kind: 'histogram',
aggregate: true,
series: [{ nameRef: 0, categories: 1, values: 2 }],
},
]);
expect(histogram && chartType(histogram)).toBe('histogram');
expect(
chartType({
plots: [{ kind: 'boxWhisker', quartiles: 'inclusive', series: [] }],
references: [],
})
).toBe('box-whisker');
});
});