BarePivot

BarePivot is a pivot table for the browser, written in plain JavaScript and CSS, with the familiar behaviour of a spreadsheet pivot. It has no runtime dependencies and no framework, so the same package embeds in React, Vue, Angular, Svelte or a static HTML page.

Give it data in almost any shape (JSON, CSV text, a SQL result, a spreadsheet range) and it gives you a field list, drag-and-drop Filters / Columns / Rows / Values zones, sorting, filtering, expand / collapse, subtotals and grand totals — plus the parts of a spreadsheet pivot people actually reach for: slicers and timelines, grouping, calculated fields and items, Show Values As, Excel number-format codes, and a linked pivot chart.

npm install barepivot        # ~140 kB minified, ~48 kB gzipped, 0 runtime dependencies

Usage guide — task-by-task wiring, every framework, recipes and troubleshooting · Internals — for contributors

What it covers

| Area | | |---|---| | Data in | flat JSON · tidy (column-oriented) · SQL result sets · CSV / TSV text with delimiter detection · spreadsheet ranges | | Layout | Compact / Outline / Tabular · subtotals top or bottom · Repeat All Item Labels · Insert Blank Line · grand total row and column · a named header cell per column level | | Values | Sum, Count, Average, Max, Min, Product, Count Numbers, StdDev, StdDevp, Var, Varp, Count Unique, Median · several value fields · custom names | | Show Values As | % of grand / row / column / parent row / parent column total · Difference from · % Difference from · Running total · % Running total · Rank ascending / descending · Index | | Filtering | Excel's checkbox dropdown with search and Select All · Label filters · Value filters · Top 10 (items / percent / sum) · report filters · slicers · timelines | | Grouping | date parts (years → seconds) · number ranges · hand-picked item groups | | Formulas | calculated fields and calculated items · 40+ worksheet functions · compiled, never eval-ed | | Formatting | General / Number / Currency / Percentage / Scientific · full Excel format codes including conditions, fractions and dates · heatmap | | Charting | column, stacked column, bar, line, area and pie, linked to the table | | Interaction | pointer drag-and-drop between and within zones · click-to-sort · sort by a specific column's values · hand-arranged item order · expand / collapse · drill-down | | Export | copy to clipboard · CSV · a real .xlsx workbook with live numbers and their formats — no dependency, works headlessly too | | Housekeeping | Refresh · Defer Layout Update · Show items with no data · PivotTable Options · light / dark / auto themes · every label translatable |

Contents

• Install · Quick start
• Data input — the five accepted formats and the parser
• Configuration — values, layouts, filtering, grouping, sorting
  • Aggregations · Show values as · Subtotals per field · Number formats
  • Numeric precision
  • Calculated fields · Calculated items
  • Slicers · Timelines · Pivot charts
  • Moving items by hand · PivotTable options
  • Report layouts · Filtering · Sorting and custom lists · Grouping
  • Exporting — CSV, clipboard and a real Excel workbook
• Using the table — what people can do in the UI
• Options · API · Events
• Framework integration · Styling · Headless use
• Project layout · Development · Limitations

Install

npm install barepivot

The package ships prebuilt: ES module, CommonJS, a minified browser script, the stylesheet (plain and minified), source maps and TypeScript declarations. Nothing is built on install.

From a CDN, with no install at all (pin the version in production):

<link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/barepivot@1/dist/barepivot.min.css">
<script src="https://cdn.jsdelivr.net/npm/barepivot@1/dist/barepivot.min.js"></script>

unpkg.com/barepivot@1/dist/… serves the same files.

From a git checkout. dist/ is build output and is not committed, so build it once before installing the folder into another project:

git clone https://github.com/chisaelim/BarePivot.git && cd BarePivot
npm install && npm run build
npm install /path/to/BarePivot     # from the other project (or: npm link)

Requirements: a browser with ES2020 and pointer events for the table; Node 18+ for the headless engine.

Quick start

import { BarePivot } from 'barepivot';
import 'barepivot/style.css';

const pivot = new BarePivot({
  container: document.getElementById('pivot-container'),
  data: rawDataInput,   // JSON rows, CSV text, array of arrays, ...
  format: 'auto',       // 'auto' | 'json' | 'tidy' | 'sql' | 'csv' | 'tsv' | 'array'
  config: {             // the initial layout (all optional)
    rows: ['Region', 'Category'],
    columns: ['Year'],
    values: [{ field: 'Sales', aggregation: 'sum' }],
    filters: ['Status'],
  },
});

Plain HTML, no bundler:

<link rel="stylesheet" href="node_modules/barepivot/dist/barepivot.min.css">
<script src="node_modules/barepivot/dist/barepivot.min.js"></script>
<div id="pivot"></div>
<script>
  // the global `BarePivot` is the class; helpers hang off it, e.g. BarePivot.normalizeData(text)
  const pivot = new BarePivot({ container: '#pivot', data: csvText });
</script>

CommonJS: const { BarePivot } = require('barepivot');

TypeScript declarations ship with the package; no @types install is needed.

The usage guide walks through each of these entry points and every major framework in detail.

Data input

Whatever you pass as data is converted by normalizeData() into one internal shape: a list of flat records ({ column: value }) plus the column names. With format: 'auto' (the default) the shape is detected. The format value is only a hint, so a valid input is never rejected because it was labelled wrongly.

| Format | What you pass | Notes | |---|---|---| | json | [{ Region: 'East', Sales: 10 }, …] | Also accepted as JSON text or NDJSON. Rows may have different keys (they are merged). Nested objects are flattened to a.b, arrays are joined with , . Flat rows are used as they are, without copying. | | tidy | { Region: ['East', …], Sales: [10, …] } | One array per variable, one position per observation. Use melt() to turn wide data into this shape. | | sql | { columns: ['Region', 'Sales'], rows: [['East', 10], …] } | Also accepted: a node-postgres result ({ fields, rows: [objects] }), a sql.js result ([{ columns, values }]), and bare tuples [['East', 10], …] together with parser: { columns: [...] }. | | csv / tsv | 'Region,Sales\nEast,10' | RFC 4180 text: quoted fields, "" escapes, embedded newlines, CRLF, BOM. The first line holds the column names; duplicate or blank names are made unique. | | array | [['Region', 'Sales'], ['East', 10], …] | A spreadsheet range. Row 0 is the header. |

Parser options

Pass these as parser: { … } (they are also the options of normalizeData(input, options)).

| Option | Default | Meaning | |---|---|---| | delimiter | auto-detect | Text only. ',', '\t', ';', '\|' or the names 'comma', 'tab', 'semicolon', 'pipe'. Auto-detection picks the delimiter that splits the first lines into a consistent number of columns, so a ; file whose numbers use decimal commas is still split on ;. | | decimal | 'auto' | Text only. The decimal separator: '.' or ',' ('1.234,50' → 1234.5). 'auto' reads a file that is not comma-delimited with a decimal comma when its numbers can only be read that way (2,5, 1.234,50) and none only with a decimal point, which is what a ; export from a German, French or Spanish spreadsheet looks like. An ambiguous 1,234 alone never switches it. The choice is reported as decimal in the result. | | quote | " | Text only. | | header | csv and array: true | false generates Column 1, Column 2, …. For SQL tuples the default is 'auto': row 0 is a header only if every cell is text. | | columns | — | SQL tuples: the column names. No row is then used as a header. | | inferTypes | true | Text only. '12.5' becomes the number 12.5. Values such as 007, dates, and digit strings longer than 15 characters stay text, because they are identifiers, not quantities. | | trim | true | Text only. Trims every cell. | | nullValues | [''] | Text only. Cell values that mean "blank"; add 'NULL' or 'N/A' if your export uses them. | | skipEmptyLines | true | Text only. | | flatten | true | JSON only. |

Using the normalizer on its own

import { normalizeData, melt } from 'barepivot';

const { format, columns, records, warnings } = normalizeData(text);
// format: 'csv', columns: ['Region', 'Sales'], records: [{ Region: 'East', Sales: 10 }, …]

melt([{ Region: 'East', Q1: 5, Q2: 7 }], { idVars: ['Region'], varName: 'Quarter', valueName: 'Sales' });
// [{ Region: 'East', Quarter: 'Q1', Sales: 5 }, { Region: 'East', Quarter: 'Q2', Sales: 7 }]

Input that cannot be understood throws a PivotDataError with a readable message. Non-fatal problems (a renamed duplicate column, a row that is not an object) are collected in warnings. The table passes them to onWarning(warnings) if you provide it, otherwise to console.warn — along with problems in the config itself: an unknown field name, an unknown aggregation, or an item selection on a field nothing shows.

Configuration

config sets the starting layout. pivot.getConfig() returns the same shape, so a layout can be saved and restored later.

config: {
  rows: ['Region', 'Category'],          // order = hierarchy depth
  columns: ['Year'],
  values: [
    { field: 'Sales', aggregation: 'sum', name: 'Revenue', decimals: 0, prefix: '$' },
    { field: 'Sales', aggregation: 'average', showAs: 'pctRow' },
  ],
  filters: ['Status', { field: 'Region', include: ['East', 'West'] }],   // fields in the Filters zone
  itemFilters: { Category: { exclude: ['Other'] } },                     // item selection for any field
  filterRules: { Region: { kind: 'top', dir: 'top', n: 5, by: 'items' } },// Label / Value / Top 10 filters
  groups: { Date: { kind: 'date', by: ['years', 'quarters'] } },         // Excel's Group Field: Date shows quarters, plus a Years field
  calculatedFields: [{ name: 'Margin', formula: '(Rev - Cost) / Rev' }], // formulas over aggregates
  slicers: ['Region'],                                                   // Excel slicers above the table
  timelines: [{ field: 'Date', unit: 'months' }],                        // Excel timeline slicers
  sort: { Region: { by: 'value', dir: 'desc', valueIdx: 0, path: ['2023'] } },
  layout: 'compact',                     // 'compact' | 'outline' | 'tabular'
  subtotals: true,
  subtotalPos: 'top',                    // 'top' (Excel's default) | 'bottom'; tabular is always bottom
  repeatLabels: false,                   // Excel's "Repeat All Item Labels"
  blankLines: false,                     // Excel's "Insert Blank Line after Each Item"
  totalRow: true,                        // Grand Total row, at the bottom
  totalColumn: true,                     // Grand Total column, on the right
  heatmap: 'none',                       // 'table' | 'row' | 'col'
  chart: 'none',                         // 'column' | 'stackedColumn' | 'bar' | 'line' | 'area' | 'pie'
  showEmpty: ['Region'],                 // Excel's "Show items with no data"
  emptyText: '',                         // Excel's "For empty cells show"
  showExpand: true,                      // Excel's "Show expand/collapse buttons"
  valuesAxis: 'columns',                 // where "Σ Values" goes when there are 2+ value fields
}

| Key | Meaning | |---|---| | rows, columns | Field names. The order is the nesting order. cols is accepted as an alias of columns. | | values | Strings ('Sales') or objects: field, aggregation, name, showAs, showAsField, decimals, prefix, suffix. With no aggregation, a field whose every record holds a number summarises by Sum, and one with any text or blank cell by Count, as in Excel. The same field may appear more than once with different aggregations; a repeated caption is numbered, as Excel names the second Sum of Sales Sum of Sales2. | | filters | The fields in the Filters zone. An entry can be a string or { field, include } / { field, exclude }. | | itemFilters | Which items are shown: { Field: { include: [...] } } or { exclude: [...] }. The field must be in Rows, Columns or Filters, or have a slicer or timeline; a selection on any other field is dropped with a warning, since Excel has no filter on a field that is not in the report. Passing itemFilters replaces every field's current selection, so include the ones you want to keep. include is stored as the excluded remainder, which is what getConfig() returns. | | sort | Per field: { by: 'label' \| 'value' \| 'manual', dir: 'asc' \| 'desc' }. For by: 'value', valueIdx picks the value field and path picks the column to compare (['2023']); without path the grand total is used. For by: 'manual', order lists the item keys — see Moving items by hand. | | layout | Excel's three report layouts — see Report layouts. | | filterRules | One Label / Value / Top 10 rule per field — see Filtering. | | groups | Grouping per field — see Grouping. | | calculatedFields | Fields defined by a formula — see Calculated fields. | | calculatedItems | Extra items inside a field, per field — see Calculated items. | | slicers | Fields shown as slicers above the table. | | timelines | Date fields shown as timeline slicers — see Timelines. | | chart | The pivot chart drawn above the table — see Pivot charts. | | layout, subtotals, subtotalPos, repeatLabels, blankLines, totalRow, totalColumn | Report layout — see Report layouts. | | heatmap | 'none' (default), 'table', 'row' or 'col': a colour scale over the value cells, scoped to the whole table, each row or each column. | | valuesAxis | 'columns' (default) or 'rows': where the "Σ Values" header goes when there are two or more value fields. | | showEmpty | Row / column fields that list every item, even those no record reaches — see PivotTable options. | | fieldSubtotals | Excel's Field Settings → Subtotals, per field — see Subtotals per field. | | useCustomLists | Excel's Use Custom Lists when sorting; default true — see Sorting and custom lists. | | fieldHeaders | Excel's Field Headers / Display field captions and filter drop downs; default true. | | compactIndent | Excel's When in compact form indent row labels _ character(s); 0–127, default 1. | | emptyText | What to put in a cell that has no value. Default: nothing. | | showExpand | Whether the + / − buttons are drawn. Default true. | | collapsed | Keys of collapsed groups. Managed by the UI; read it from getConfig(). |

Aggregations

| Key | Spreadsheet name | | |---|---|---| | sum, count, average, max, min | Sum, Count, Average, Max, Min | count counts non-blank values of any type. max and min of records that hold no number are 0, like Excel's MAX() / MIN(). | | product | Product | 0 when there are no numbers, like Excel. | | countNums | Count Numbers | Counts numeric values only. | | stdev, stdevp | StdDev, StdDevp | Sample and population standard deviation. | | var, varp | Var, Varp | Sample and population variance. | | countUnique, median | — | Extras. |

You can write the key, the label, or a common alias ('Sum', 'Count Numbers', 'Std Dev', 'avg'); a name that matches none of them is reported through onWarning and replaced by Excel's default (Sum for a numeric field, Count otherwise). Subtotals and grand totals are aggregated from the raw records, so the grand total of an Average is the mean of all rows, not a mean of means. Add your own with the aggregators option: { key: { label, create: () => ({ push(value, record) {}, value() {} }) } }.

Show values as

All fifteen of Excel's calculations, in Excel's own order. The percentages of a total need no extra settings:

| showAs | | |---|---| | none | the raw aggregate | | pctGrand, pctRow, pctCol | % of the grand, row or column total | | pctParentRow, pctParentCol | % of the enclosing row / column group's total | | index | (cell × grand total) ÷ (row total × column total) |

The rest are driven by a base field (showAsField), exactly as Excel drives them: the row or column field whose items the calculation walks, holding every other field fixed. A cell on a subtotal or grand total above that field has no item of it, so it stays blank, as it does in Excel.

| showAs | needs | | |---|---|---| | pctParent | base field | % of the item of the base field that contains the cell | | runTotal, pctRunTotal | base field | running total along the base field, absolute or relative | | rankAsc, rankDesc | base field | rank among the base field's items; ties share a rank and the next is not skipped (8, 8, 5 → 1, 1, 2), as in Excel | | pctOf | base field + item | % of one item of the base field | | diff, pctDiff | base field + item | difference from one item, absolute or relative |

showAsBase names that item: 'prev' (the default), 'next', or an item's key. As in Excel, Difference From and % Difference From leave the base item's own cells blank (so 'prev' leaves the first item blank), while % Of shows it as 100.00%. A base field that is not on either axis leaves every cell blank rather than quietly falling back to something else.

// each region's share of its own category, and the sales running up the year
values: [
  { field: 'Sales', aggregation: 'sum', showAs: 'pctOf', showAsField: 'Region', showAsBase: 'East' },
  { field: 'Sales', aggregation: 'sum', showAs: 'runTotal', showAsField: 'Year' },
]

Because the base field holds the other fields fixed, a running total in Region over rows of Region → Category accumulates down the regions within each category, not within each region — which is what Excel does, and what makes the choice of base field matter.

Leaving showAsField out falls back to the older, simpler showAsAxis ('row' or 'col'), which walks whatever items sit beside the cell on that axis. Existing configurations keep working unchanged.

Subtotals per field

Excel's Field Settings → Subtotals, as fieldSubtotals. A field is left out for Automatic (one subtotal using each value's own aggregation), set to 'none' for no subtotal at all, or given a list of aggregations for Custom — one subtotal row per function, named the way Excel names them:

config: {
  rows: ['Region', 'Category'],
  values: ['Sales'],
  fieldSubtotals: {
    Region: ['sum', 'average'],   // "East Sum", then "East Average"
    Category: 'none',
  },
}

Custom subtotals are aggregated from the records, not derived from the cells above them, so an average subtotal is the mean of the underlying rows. They are always rows of their own, even with subtotals set to the top, since several of them cannot share one group row. In the UI they are the Subtotals… entry in a row or column field's ▾ menu.

Number formats

Each value field carries Excel's Value Field Settings → Number Format:

{ field: 'Sales', aggregation: 'sum', numFmt: 'currency', currency: '$', decimals: 2, negParens: true }

| Key | | |---|---| | numFmt | general (default), number, currency, percent, scientific, custom — with Excel's defaults: General shows up to 11 characters with no separator (1234567, 0.333333333, 1.23457E-05); Number, Currency and Percentage show 2 decimals; Scientific shows 1.23E+06 | | formatCode | with numFmt: 'custom', an Excel format code (below) | | decimals | fixed decimal places (0–20, anything outside is clamped); left out, the category's default above | | thousands | Excel's Use 1000 Separator; left out, off for Number and General and on for Currency, as in Excel | | currency | a symbol ('$', '€') or an ISO code ('USD'), which places itself per locale | | negParens | show negatives as (1,234.00) | | prefix, suffix | wrapped around the formatted number |

Format codes. numFmt: 'custom' takes an Excel format code:

{ field: 'Sales', numFmt: 'custom', formatCode: '$#,##0.00;[Red]($#,##0.00);"—"' }

Up to four ;-separated sections (positive; negative; zero; text), # 0 ? digit placeholders, the thousands separator, ., %, "literals", and a colour in brackets ([Red], [Blue], [Green], …) that is applied to the cell. Commas trailing the placeholders scale by thousands, so #,##0.0,," M" renders 1234567 as 1.2 M.

| Beyond the basics | |---| | Conditions. '[Red][<100]0" low";[Green][>1000]0" high";0" ok"' — sections carrying a [<100]-style condition are tried in order and the first match wins; the first section without one is the fallback. | | Fractions. '# ?/?' gives 2 3/4, '# ??/??' finds the closest denominator up to 99, and a fixed denominator like '# ?/8' rounds to eighths. # hides a zero whole part (1/2), 0 keeps it (0 1/2). | | Dates. 'yyyy-mm-dd', 'd mmm yyyy', 'dddd, mmmm d, yyyy', 'h:mm:ss AM/PM'. The number is read as an Excel date serial (days since 1899-12-30) and rendered in UTC, so the day never shifts with the viewer's timezone. mm is minutes next to an hour and months otherwise, as in Excel. | | Text. The fourth section formats a text result, with @ standing for the text: '0.0;;;"["@"]"'. |

A percentage showAs wins over numFmt and any format code, since it has already scaled the number.

Numeric precision

What counts as a number. A number, or a string holding a plain decimal (1234.5, -.5, 1e3, 1,234.50 — commas are read as thousands separators only when they group by three, so 1,5 is text and can never be summed as 15). Hex, octal and binary prefixes (0x1A), Infinity and anything that overflows are text, so an id column of hex codes is not summed, and one stray "Infinity" cannot turn a total into ∞.

JavaScript numbers are IEEE-754 doubles, which cannot hold 0.1 exactly. Left alone that shows up in a pivot table the way it shows up everywhere else: a column of prices adds up to 3.3000000000000003, and a debit that should cancel its credit leaves -2.8e-17 behind, which a two-decimal format renders as -0.00. BarePivot applies the same two rules a spreadsheet does, so the numbers read the way they were typed.

Totals are accumulated with compensation. Every sum and average, every running total and every SUM() inside a formula uses Neumaier compensated summation: each addition's dropped low bits are carried forward instead of thrown away. Ten thousand 0.1s come to exactly 1000, not 1000.0000000001588, and a small figure is not lost next to a large one.

Finished values keep 15 significant decimal digits. That is Excel's stored precision, and it is where 3.3000000000000003 becomes 3.3. The rounding happens once, as each cell's value is finalized, so the table, the CSV and TSV exports, the chart, the value filters and the heatmap all see the same number — including values from a custom aggregator, since every aggregator finishes at the same point.

A total that cancels reads as zero. When what is left of a sum or a subtraction is smaller than the rounding slack of the terms that produced it, the answer is 0 rather than -2.8e-17. Whole numbers are held exactly and so contribute no slack, which is why 1e16 + 1 + 1 - 1e16 still comes to 2.

This also fixes the places the noise used to leak into the UI: grouping by a fractional step puts 0.3 in the 0.3-0.4 bucket rather than 0.2-0.3, a value filter for equals 3.3 matches a total of 1.1 + 2.2, chart gridlines are labelled 0.3 rather than 0.30000000000000004, and ROUND(1.005, 2) is 1.01, not 1 — ROUND, ROUNDUP and ROUNDDOWN decide on the decimal value rather than the binary one.

The two helpers are exported for use outside a pivot:

import { round15, roundTo, compensatedSum } from 'barepivot';

round15(1.1 + 2.2);           // 3.3
roundTo(1.005, 2);            // 1.01   (half away from zero, like Excel)
roundTo(0.1 + 0.2, 1, 'up');  // 0.3    ('round' | 'up' | 'down')

const acc = compensatedSum();
[0.1, 0.2, -0.3].forEach((x) => acc.add(x));
acc.value();                  // 0

Calculated fields

A field defined by a formula over the other fields, like Excel's Fields, Items & Sets → Calculated Field. Use the ƒx button above the field list, or the config:

calculatedFields: [
  { name: 'Profit', formula: 'Revenue - Cost' },
  { name: 'Margin', formula: '(Revenue - Cost) / Revenue' },
  { name: 'Total',  formula: "'Unit Price' * Qty" },   // quote names containing spaces
]

The formula supports + - * / % ^, parentheses, unary minus, & for joining text, and the comparisons = <> < <= > >=. Field names are bare words, or 'quoted' / [bracketed] when they contain spaces or punctuation (a bare word may hold letters of any script, digits, _ and ., so Größe and 销售额 need no quotes); "double quotes" are a text literal, as in Excel. MOD takes the sign of the divisor, as in Excel. These worksheet functions are available:

| | | |---|---| | Maths | ABS ROUND ROUNDUP ROUNDDOWN INT SQRT POWER MOD SIGN EXP LN LOG MIN MAX SUM AVERAGE | | Logic | IF AND OR NOT TRUE FALSE ISNUMBER ISTEXT ISBLANK | | Text | CONCATENATE CONCAT LEFT RIGHT MID LEN UPPER LOWER TRIM TEXT VALUE FIND SEARCH SUBSTITUTE REPT EXACT N T |

{ name: 'Margin', formula: 'IF(Revenue > 0, (Revenue - Cost) / Revenue, 0)' }
{ name: 'Verdict', formula: 'IF(Revenue > Cost, "profit", "loss")' }
{ name: 'Rate',   formula: 'TEXT(Profit / Revenue, "0.0%")' }

A formula may produce text as well as a number; text goes into the cell as-is (only a @ section of a custom format code touches it). Arithmetic coerces text to a number the way Excel does, and = compares two strings case-insensitively. Field references are always the field's sum, so text functions are for shaping the result — there is no way to read one record's text value, and, as in Excel, no cell references or ranges.

Like Excel, a calculated field is evaluated against the sum of each referenced field inside every cell — so Margin is Sum(Revenue - Cost) / Sum(Revenue), not the average of each row's margin. Dividing by zero gives Excel's #DIV/0! (and SQRT(-1) #NUM!, VALUE("x") #VALUE!), and an error passes through the rest of the formula (IF still shields the branch it does not take). A formula that does not parse is reported inline in the dialog rather than throwing.

The parser is hand-written, so nothing is passed to eval or new Function and the library still works under a strict Content-Security-Policy.

Calculated items

An extra item inside a field, computed from that field's other items — Excel's Calculated Item. Reach it from a row or column field chip's ▾ menu, or the config:

calculatedItems: {
  Region: [
    { name: 'East + West', formula: "'East' + 'West'" },
    { name: 'Gap',         formula: "'West' - 'East'" },
  ],
}

The names inside the formula are items of that field, not fields. An item that does not exist counts as zero. Calculated items are appended after the real items and, as in Excel, count in the subtotals and grand totals above them — so 'East' + 'West' makes the grand total count East and West twice, exactly as Excel's does. That holds for Sum, Count and Count Numbers; for other summaries (Average, Max, …) the totals stay aggregated from the records, since a calculated item has none. For the same reason double-clicking one does not open a drill-down.

Slicers

slicers: ['Region', 'Category']

Each slicer is a box of item buttons above the table. Clicking an item shows only it, as Excel does; hold Ctrl/Cmd (or Shift) to add and remove items. Each box has a button to clear its filter and one to remove the slicer. Slicers share itemFilters, so a slicer and the header dropdown stay in step. Add one from a field chip's ▾ menu.

Timelines

Excel's timeline slicer: a chronological track over a date field.

timelines: ['Date', { field: 'Shipped', unit: 'quarters' }]

unit is 'years', 'quarters', 'months' (default) or 'days'. Click a period to select it, or drag across the track to pick a range. The caption above shows the current range, and the funnel button clears it. Add one from a field chip's ▾ menu — the menu entry is disabled for a field whose values do not parse as dates.

A timeline filters through the same itemFilters as everything else, so it stays in step with the header dropdown and any slicer on the same field. The field does not have to be in the layout: a timeline (or slicer) on a field that is in neither Rows, Columns nor Filters still filters the table.

Pivot charts

chart: 'column'   // 'none' | 'column' | 'stackedColumn' | 'bar' | 'line' | 'area' | 'pie'

The chart is drawn as SVG from the same model as the table, so expanding, collapsing, filtering and sorting move both together, exactly like Excel's PivotChart. Subtotal, group and grand-total lines are left out, since charting them would count the same numbers twice. Hovering a bar, point or slice shows its category, its series and its value; hovering the plot fades the other marks so the one under the pointer stands out.

Leaf rows become the categories and leaf columns the series. A pivot with no column field — rows and values alone — charts its single data column as one series. A pie charts the first series and names it under the chart; with a single category it charts the series instead.

Everything the chart draws, it draws in full. The layout is measured from the text itself rather than from fixed margins, so the pieces are sized in this order and the plot takes what is left:

| Piece | What it does | | --- | --- | | Legend | Wraps onto as many rows as it needs, above the plot, titled with the column field names. Labels are shortened to fit, keeping the end of the path — the part that tells two series apart — and the full text stays in the tooltip. | | Value axis | Gridline values in the first value's number format. A label is drawn only where one fits; the two ends always keep theirs. | | Category axis | Straight labels while they fit, turned 45° when they do not, and labelled every nth when even that would overlap. | | Axis titles | The row fields under (or beside) the categories, the value titles along the value axis. | | Data labels | Drawn where they fit: on the bars of a small chart, the total above each stack, the series name at the end of each line. | | Notes | What the chart could not show, under it — see below. |

In a box too small for all of that, the axis titles go first and then legend rows, so the plot never falls below a readable height.

Nothing disappears quietly. Where a limit bites, the chart says so in a note beneath it: Showing 10 of 24 series — filter or collapse columns to chart the rest. The same goes for categories past the cap, category labels that had to be thinned, the series a pie picked, values a pie cannot draw, and the tail of a pie rolled into one Other wedge. The notes come from the labels object, so they translate with everything else.

Series colours are assigned in a fixed order from CHART_PALETTE and never cycled, so a series keeps its colour as the pivot changes and no two series in one chart share a hue. The order is chosen so that neighbouring colours stay apart for red/green colour blindness.

Set chartHeight (an option, not config) to change the height from its default of 300px. A bar chart grows past it — up to chartMaxHeight, by default two and a half times chartHeight — rather than squeezing its bars into slivers.

The chart can be used on its own, without the table:

import { renderChart, chartData } from 'barepivot';

const svg = renderChart(model, { type: 'bar', width: 640, height: 320 });
const { categories, series, totalSeries } = chartData(model);

renderChart(model, options) takes:

| Option | Default | Meaning | | --- | --- | --- | | type | 'column' | column, stackedColumn, bar, line, area or pie. | | width / height | 760 / 320 | Size of the SVG. A bar chart may return a taller one. | | format | rounds to 2 dp | Formats the value-axis labels. Data labels use each cell's own format. | | labels | CHART_LABELS | The chart's strings, for translation. | | maxCategories / maxSeries | 60 / 10 | Caps on what is charted; the rest is reported in a note. | | maxSlices | 10 | Wedges a pie draws before rolling the rest into Other. | | maxHeight | height * 2.5 | How tall a bar chart may grow. | | dataLabels | 'auto' | 'all' labels every mark, 'none' labels none. | | axisTitles | true | Name the fields behind each axis. | | title | — | A heading above the chart. | | fontSize | 11 | Font size the layout measures against; must match .bp-chart-text. |

chartData(model, options) returns { categories, series, truncated, totalCategories, totalSeries, categoryTitle, seriesTitle, valueTitle }, where each series is { label, points, texts } — points the numbers, texts the same cells as the table formatted them. textWidth(text, size) measures text the way the layout does, for anything you draw around the chart yourself.

Moving items by hand

Right-click any row or column item for Move to beginning / up / down / to end, Excel's hand-arranged item order. The order belongs to the field, not to one group, so moving West to the front moves it in every group at once — again as Excel does.

It is stored as an ordinary sort spec, so it round-trips through getConfig():

sort: { Region: { by: 'manual', order: ['West', 'North', 'East'] } }

Items the order does not mention keep their natural place at the end, so new data does not disturb an arrangement. Reset item order in the same menu drops back to A → Z.

PivotTable options

The Options… button opens Excel's PivotTable Options dialog. Every switch has exactly one home: what the toolbar carries is not repeated here, and the other way round.

| Section | | | |---|---|---| | Layout & format | For empty cells show (emptyText) | text put in a cell that has no value | | Layout & format | Show expand/collapse buttons (showExpand) | whether the + / − buttons are drawn | | Layout & format | Display field captions and filter drop downs (fieldHeaders) | Excel's ribbon Field Headers: off, the "Row Labels" / "Column Labels" band, the field-name captions and the ▾ dropdowns all go, and the items keep their places | | Layout & format | Indent row labels (compactIndent) | how far each compact-form level is indented, in characters (0–127, default 1 as in Excel); only applies to compact layout | | Layout & format | Repeat labels (repeatLabels) | Excel's Repeat All Item Labels; greyed out in compact form, which has nothing to repeat | | Layout & format | Blank rows (blankLines) | Excel's Insert Blank Line after Each Item, on the outermost row field | | Display | Show items with no data (showEmpty) | per row / column field: list every item the filter still allows, even the ones no record reaches, so the table keeps a constant shape | | Sorting | Use custom lists when sorting (useCustomLists) | month and weekday names sort in calendar order — see Sorting and custom lists | | Data | Defer layout update | collect changes without redrawing the table until Update |

The grand total row and column (totalRow, totalColumn) are toolbar checkboxes, and Refresh is a toolbar button.

showEmpty never brings back an item that a filter removed — only items that survive filtering but happen to have no matching records.

Defer Layout Update is useful while rearranging a large pivot: the field list keeps reacting so you can see what you are building, but nothing is re-aggregated until you press Update. From code it is pivot.setDefer(true) and pivot.update().

Refresh matters because the aggregation is computed once and cached: rows you add to your own array, or values you change in it, are not visible until pivot.refresh() (or pivot.setData(...)).

Report layouts

| layout | | |---|---| | compact (default) | every row field in one indented column, under a "Row Labels" header | | outline | a column per row field, field names as headers, each item on its own row above its children | | tabular | a column per row field, no heading row, subtotals always at the bottom |

subtotalPos is Excel's Show all Subtotals at Top / Bottom of Group. At 'top' (the default, as in Excel) compact and outline form put each group's subtotal on the group's own row; at 'bottom' the group row stays empty and an East Total row follows the items. Tabular form has no group row, so its subtotals are always at the bottom; the setting is kept, and applies again when you switch back.

repeatLabels fills in the labels that are normally blanked when they repeat, and blankLines inserts an empty row after each item of the outermost row field.

Column headers. Above the column items sits a band, as in Excel: compact shows a single "Column Labels" header there, while outline and tabular name the fields. With two or more column fields each level gets its own named, sortable, filterable cell — the first in the band, the deeper ones in the corner column of the header row above their items:

┌──────────────┬───────────────────────────────────────────┐
│ Sum of Sales │ Shift ▾                                   │  ← band: the first column field
├──────────────┼─────────────────┬─────────────────┬───────┤
│ Year       ▾ │ Day             │ Night           │ Grand │  ← its items, named in the corner
├──────────────┼────────┬────────┼────────┬────────┤ Total │
│ Region     ▾ │ 2023   │ 2024   │ 2023   │ 2024   │       │  ← row fields on the last row
├──────────────┼────────┼────────┼────────┼────────┼───────┤
│ East         │    120 │    140 │     90 │    110 │   460 │

(Subtotal columns are left out of the sketch.) In compact the corner stays a single merged cell under one "Column Labels" band, which is what Excel does there.

With two or more value fields, each value gets its own grand total, named as Excel names it — Total Sum of Sales, Total Count of Orders — on whichever axis "Σ Values" sits.

Filtering

Three things can filter a field, and they combine:

1. itemFilters — the checkbox selection, as { include: [...] } or { exclude: [...] }.
2. filterRules — one Excel rule per field. Applying a rule clears that field's checkbox selection, as Excel does.
3. The Filters zone — the same, driven from the report filter bar above the table.

filterRules: {
  // Label filters: equals, notEquals, beginsWith, endsWith, contains, greaterThan,
  // lessThan, between… each also has a negated form (notContains, notBetween, …).
  // As in Excel, ? is any one character, * any run of characters, and ~ escapes them
  Category: { kind: 'label', op: 'contains', a: 'bike' },
  // Value filters compare the aggregate of each item; valueIdx picks which value field
  Region:   { kind: 'value', op: 'greaterThan', valueIdx: 0, a: 1000 },
  // Top 10: the n largest ('top') or smallest ('bottom') by Items, Percent or Sum
  Product:  { kind: 'top', dir: 'top', n: 10, by: 'items', valueIdx: 0 },
  // Date filters: relative to today, a named period, or a date you give
  OrderDate: { kind: 'date', op: 'lastMonth' },
}

Date filters. On a date field the ▾ menu offers Date filters… in place of Label filters…, exactly as Excel's does, with the same list:

| | op | |---|---| | Dated | equals, notEquals, before, after, between, notBetween — with a (and b) as YYYY-MM-DD | | Relative to today | yesterday, today, tomorrow, and last / this / next × Week, Month, Quarter, Year | | Year to date | yearToDate | | All dates in the period | quarter1…quarter4, month1…month12 — that period in any year |

The week runs Sunday to Saturday and quarters are calendar quarters, as in Excel. before and after exclude the date given; between includes both ends; yearToDate stops at today. A value that is not a date never passes a date filter. "Today" comes from the clock; pass now to PivotEngine to pin it.

Value and Top 10 rules are applied to the aggregated total of each item, so the grand total afterwards reflects only the items that survived — again like Excel. On an inner field they are applied within each item of the fields outside it, as Excel does: a Top 3 on City under State keeps the top three cities of every state, not the three biggest cities overall. Top 10 by Items keeps every item tied with the last one, so Top 1 of two equal items shows both.

Sorting and custom lists

Labels sort the way Excel sorts them: numbers first, by value; then text, character by character and ignoring case (so Item 10 comes before Item 2, as it does in Excel), with apostrophes and hyphens ignored (co-op sorts as coop, and after it when the two differ only by the hyphen); blanks last.

Custom lists, Excel's Use Custom Lists when sorting, are on by default: a column of month or weekday names sorts in calendar order rather than alphabetically, in both the short and the long forms, matched without regard to case.

new BarePivot({
  container: '#pivot',
  data,
  customLists: [['Small', 'Medium', 'Large', 'X-Large']],  // consulted before the built-in ones
  config: { useCustomLists: true },                        // the default; false sorts alphabetically
});

A list is used when it covers at least two of a field's items and at least half of them, so an ordinary word column that happens to hold May is not mistaken for months. Items the list does not name keep their alphabetical order, after the ones it does. A grouped field keeps the grouping's own chronological or range order instead.

Grouping

groups: {
  // OrderDate now shows quarters (the finest unit picked) and a Years field is added,
  // with Excel's item names (2024, Qtr1, Jan, 7-Mar, 1 PM, :05) in chronological order
  OrderDate: { kind: 'date', by: ['years', 'quarters'] },
  // turns the values into ranges in place, named as Excel names them:
  // '<0', '0-99', '100-199', … '900-999', '>1000', as Excel names them (0-100 … 900-1000
  // once any value has decimals; "Ending at" is rounded up to a whole step and is inclusive)
  Sales: { kind: 'number', start: 0, end: 1000, by: 100 },
  // folds chosen items into a named group; anything unlisted stays as it is
  Region: { kind: 'manual', map: { 'DACH': ['DE', 'AT', 'CH'] } },
}

Date grouping works as Excel's Group dialog does: the grouped field itself shows the finest unit you pick, and each coarser unit becomes a new field named after it — Years, Quarters, Months, Days, Hours, Minutes — or Years2, Years3, … when the name is taken. Grouping OrderDate by years, quarters and months therefore gives the fields Years, Quarters and OrderDate (showing months). Grouping from the UI puts the new fields on the same axis, just outside the grouped one; Ungroup takes them away again. The field keeps its dates underneath, as in Excel: Date filters and a timeline on it filter by the dates, and a timeline over a grouped field sets a date filter between the first and last day it covers. Dates are read in UTC so the day never shifts with the viewer's timezone. A Date object at local midnight — what new Date(2024, 0, 15) or a SQL DATE from a driver gives you — is a calendar day and is read as that day; any other Date is an instant and is read in UTC. A string that is not a real date (2024-02-31) is not grouped: it keeps its own item, and is blank in the coarser fields. Number and manual grouping replace the field's items, as Excel does. In the UI this is the Group… / Ungroup pair in a field chip's ▾ menu.

Exporting

| | | |---|---| | Copy | tab-separated text on the clipboard; pastes straight into Excel or Google Sheets | | CSV | downloadCSV() — RFC 4180, UTF-8 with a BOM so Excel reads accents correctly | | Excel | downloadXLSX() — a real .xlsx workbook |

The Excel export is not a renamed CSV. It is an Open XML workbook written by the library itself — no dependency, no server — and the numbers stay numbers:

• each data cell holds its value, not its formatted text, so the file opens live and can be re-summed, sorted and charted in Excel;
• each value field's number format travels with it, so a currency column is currency in Excel and a custom format code is the same code there;
• the header rows are bold, the columns are sized to their contents, and the panes are frozen below the headers and beside the row labels.

pivot.downloadXLSX('sales.xlsx', { sheetName: 'Sales 2024' });

const bytes = pivot.toXLSX();      // Uint8Array, for uploading or saving yourself

| Option | | | |---|---|---| | sheetName | 'Σ Values' | forbidden characters are replaced and it is cut to 31 characters, as Excel requires | | freezePanes | true | keep the headers and row labels in view | | autoWidth | true | size the columns to their contents |

It works headlessly too, so a server can build workbooks with no browser:

import { PivotEngine, buildModel, toXLSX, DEFAULT_LABELS } from 'barepivot';

const engine = new PivotEngine({ data });
const model = buildModel(engine.compute(state), state, { engine, labels: DEFAULT_LABELS });
await writeFile('report.xlsx', toXLSX(model));

The archive is stored rather than deflated — the library carries no compression code — so a workbook is a few times larger than one Excel would write. Excel opens it either way.

Using the table

Field list and zones. The panel on the right (panel: 'left' | 'top' | 'bottom' moves it, panel: 'none' removes it; the Field list button hides it) lists every field. Tick a field to add it: numeric fields go to Values, all others to Rows. Or drag it into one of the four zones.

Drag and drop. Uses pointer events, so it works with mouse, pen and touch.

• Drag a field between zones to move it. A field lives in only one of Rows, Columns and Filters; the same field can be in Values several times.
• Drag a chip up or down inside a zone to reorder it. In Rows and Columns this changes the nesting depth.
• Drag a chip onto the field list, or press its ×, to remove it. Esc cancels a drag.
• A row, column or filter chip's ▾ menu has Move up, Move down and Move to Filters / Columns / Rows / Values as a keyboard-friendly alternative, alongside the field's filter, grouping, slicer, timeline and (for rows and columns) sort, calculated-item, subtotal, expand / collapse and Show items with no data entries. A value chip's ▾ opens its value field settings instead.

Sorting.

• Click a column header at its deepest level to sort the rows by the numbers in that column. The first click puts the largest first; click again to reverse.
• Click a higher-level column header, the Row Labels corner, or a field-name corner to sort labels A → Z / Z → A.
• An arrow on the header shows the active sort.
• The chip menu offers the same choices, plus Sort largest → smallest and smallest → largest by grand total.

Expand / collapse. The + / − buttons on headers, Expand field / Collapse field in the chip menu, or Expand all / Collapse all in the toolbar. The scroll position is kept.

Filtering. Every header carries Excel's ▾ dropdown: "Row Labels", "Column Labels" and each field-name header. It holds Sort A→Z / Z→A, Clear filter, Label filters…, Value filters…, Top 10…, and the searchable checkbox list with (Select all) and per-item record counts. The same menu is on each field chip and in the report filter bar above the table.

Grouping. A field chip's ▾ menu has Group… and Ungroup. The dialog adapts to the field: date parts for dates, start / end / by for numbers, and a pick-the-items form for text.

Calculated fields. The ƒx button above the field list opens Excel's calculated-field dialog: a name, a formula, a picker that inserts a field name at the caret, and live syntax checking. Calculated fields are marked ƒx in the field list; their chip menu can edit or delete them.

Slicers and timelines. Add either from a field chip's ▾ menu. Click a slicer item to isolate it, Ctrl/Cmd-click to multi-select; click or drag across a timeline to pick a range of periods.

Moving items. Right-click a row or column item for Move to beginning / up / down / to end, and Reset item order.

Charts. Pick a type in the toolbar's Chart box. The chart sits above the table and follows every change made to it.

Value field settings. The ▾ on a value chip opens a form: custom name, summarize by, show values as (with base field and base item when the calculation needs them), number format, currency, format code, decimals (0–10 here; up to 20 from config), thousands separator, negatives in parentheses, prefix, suffix, and a Remove field button.

Toolbar. Excel's Design tab, essentially: Layout (compact / outline / tabular), Subtotals (do not show / top of group / bottom of group — top is greyed out in tabular form), Total row, Total column, Heatmap, Chart, Expand all, Collapse all, Copy (pastes into Excel or Google Sheets), CSV download, Excel download, Refresh, Reset, Options…, Field list. toolbar: false hides it. The rarer report-layout switches — Repeat labels and Blank rows — live in PivotTable options instead, so nothing appears in both places. On a narrow container the bar wraps, keeping the action buttons together on their own line.

Drill down. Double-click a cell to list the records behind it, as Excel's Show Details does: the source rows with their own values (a grouped date shows its date, not Jan), plus any derived fields. Use onCellDblClick to handle it yourself; it receives the same rows.

Options

Everything except container is optional.

| Option | Default | Meaning | |---|---|---| | container | required | An element or a CSS selector. | | data, format, parser, config | — | Described above. | | theme | 'light' | 'light', 'dark', or 'auto' (follows the operating system). | | panel | 'right' | Position of the field list: 'right', 'left', 'top', 'bottom', or 'none' for no field list at all (a read-only report driven from code). | | showPanel | true | Start with the field list open. | | toolbar | true | Show the toolbar. | | maxCells | 150000 | Above this many cells the table asks before rendering. | | drilldown | true | Double-click a cell to see its records. | | maxDrillRows | 1000 | Rows shown in the drill-down list. | | maxFilterItems | 500 | Items listed in a filter popup before you must search. | | chartHeight | 300 | Height in pixels of the pivot chart. | | chartMaxHeight | chartHeight * 2.5 | How tall a bar chart may grow to keep its bars thick. | | defaultAggregation | 'sum' | Used when a numeric field is added to Values. | | derived | — | Computed fields: { Balance: (row) => row.Price - row.Paid }. | | hiddenFields | — | Field names to keep out of the field list. | | aggregators | — | Extra aggregation functions (see Aggregations). | | customLists | — | Your own sort orders, e.g. [['Small', 'Medium', 'Large']], consulted before the built-in month and weekday lists (see Sorting and custom lists). | | locale | browser default | Used for number formatting and for comparing text when sorting. | | formatter | — | (value, valueField, ctx) => string to format numbers yourself. | | labels | — | Override or translate any UI text, for example { rows: 'Lignes', grandTotal: 'Total général' }. | | onChange(config) | — | The user changed the layout. | | onWarning(list) | console.warn | Non-fatal data and config problems (see Data input). | | onCellDblClick(info) | — | Receives { records, rowPath, colPath, value, text, valueField }. Return false to skip the built-in dialog. |

API

| Method | | |---|---| | setData(data, format?, parser?) | Replace the data. format and parser default to the ones given to the constructor. Throws PivotDataError on unusable input. | | getData() | { format, columns, records, warnings } of the normalized input. | | getConfig() | The current layout, in the shape of config. | | setConfig(partial) | Merge a partial layout. Only the keys you pass change. Does not fire change, so it is safe inside a reactive update. | | on(type, fn) / off(type, fn) | Subscribe / unsubscribe. on returns an unsubscribe function. | | setTheme(theme) | 'light', 'dark' or 'auto'. | | toMatrix(), toCSV(), downloadCSV(name?), copy() | Export the table exactly as displayed. | | toXLSX(options?), downloadXLSX(name?, options?) | Export as a real Excel workbook — see Exporting. | | expandAll(), collapseAll() | Open or close every group on both axes. | | reset() | Restore the layout the pivot started with. | | refresh() | Excel's Refresh: read the source you passed in again, re-aggregate and redraw. The aggregation is cached, so changes to your array show up only after this. Fires no event. | | setDefer(on) / update() | Excel's Defer Layout Update: collect changes without touching the table, then apply them. | | destroy() | Removes the DOM and all listeners. Call it when your component unmounts. |

Events

| Event | Payload | Fires when | |---|---|---| | change | getConfig() | The layout changed through the UI (drag, sort, filter, expand…) or through expandAll(), collapseAll() or reset(). Not for setConfig(). | | data | the normalized dataset | setData() finished. | | render | the table model (null when there is nothing to show) | The pivot re-rendered, including while layout updates are deferred. |

const stop = pivot.on('change', (config) => localStorage.setItem('layout', JSON.stringify(config)));
stop(); // unsubscribe

Framework integration

The pattern is the same everywhere: create the table when your component mounts, call setData() when the data changes, and call destroy() when it unmounts. The library owns everything inside the container element.

// React
import { useEffect, useRef } from 'react';
import { BarePivot } from 'barepivot';
import 'barepivot/style.css';

export function Pivot({ data, config, onChange }) {
  const box = useRef(null);
  const pivot = useRef(null);
  useEffect(() => {
    pivot.current = new BarePivot({ container: box.current, data, config, onChange });
    return () => pivot.current.destroy();
  }, []);
  useEffect(() => { pivot.current && pivot.current.setData(data); }, [data]);
  return <div ref={box} />;
}

<!-- Vue 3 -->
<script setup>
import { onMounted, onBeforeUnmount, ref, watch, toRaw } from 'vue';
import { BarePivot } from 'barepivot';
import 'barepivot/style.css';

const props = defineProps({ data: Array, config: Object });
const el = ref(null);
let pivot;
onMounted(() => (pivot = new BarePivot({ container: el.value, data: toRaw(props.data), config: props.config })));
onBeforeUnmount(() => pivot.destroy());
watch(() => props.data, (d) => pivot.setData(toRaw(d)));
</script>
<template><div ref="el" /></template>

<!-- Svelte -->
<script>
  import { BarePivot } from 'barepivot';
  import 'barepivot/style.css';
  export let data;
  export let config = {};
  let pivot;
  const mount = (node) => {
    pivot = new BarePivot({ container: node, data, config });
    return { destroy: () => pivot.destroy() };
  };
  $: pivot && pivot.setData(data);
</script>
<div use:mount />

// Angular
import { AfterViewInit, Component, ElementRef, Input, OnChanges, OnDestroy, SimpleChanges, ViewChild } from '@angular/core';
import { BarePivot, PivotConfig } from 'barepivot';

@Component({ selector: 'app-pivot', template: '<div #host></div>' })
export class PivotComponent implements AfterViewInit, OnChanges, OnDestroy {
  @Input() data: unknown;
  @Input() config?: PivotConfig;
  @ViewChild('host') host!: ElementRef<HTMLElement>;
  private pivot?: BarePivot;

  ngAfterViewInit() { this.pivot = new BarePivot({ container: this.host.nativeElement, data: this.data, config: this.config }); }
  ngOnChanges(c: SimpleChanges) { if (c['data'] && !c['data'].firstChange) this.pivot?.setData(this.data); }
  ngOnDestroy() { this.pivot?.destroy(); }
}

Add barepivot/style.css to your global styles (or import it as shown). Importing the package does not touch the DOM, so it is safe in server-side rendering; only new BarePivot(...) needs a browser.

Styling

Every class is prefixed bp-, so nothing collides with your app. Change the look with CSS variables on .bp-root (and on .bp-float, which the popups use). Load your overrides after the library's stylesheet so they win:

.bp-root, .bp-float {
  --bp-accent: #0f766e;
  --bp-accent-soft: #ccfbf1;
  --bp-radius: 4px;
}
.bp-root { --bp-max-height: 60vh; }   /* height of the scrolling table area */

Available variables: --bp-bg, --bp-bg-soft, --bp-bg-head, --bp-bg-total, --bp-border, --bp-text, --bp-muted, --bp-accent, --bp-accent-soft, --bp-danger, --bp-heat, --bp-shadow, --bp-radius, --bp-max-height.

Headless use

The engine has no DOM dependency, so it also runs in Node, web workers and build scripts, for example to pre-aggregate on a server:

import { normalizeData, PivotEngine, buildModel, toMatrix, DEFAULT_LABELS } from 'barepivot';

const engine = new PivotEngine({ data: normalizeData(csvText).records });
const state = {
  rows: ['Region'], cols: ['Year'],
  values: [{ field: 'Sales', agg: 'sum', showAs: 'none', name: '', decimals: '', prefix: '', suffix: '' }],
  filters: {}, sort: {}, collapsed: [], layout: 'tabular', subtotals: true, totalRow: true, totalColumn: true,
};
const table = toMatrix(buildModel(engine.compute(state), state, { engine, labels: DEFAULT_LABELS }));

state here is the engine's own shape, which differs slightly from config: cols rather than columns, agg rather than aggregation, and filters as a map of excluded item keys. Everything else — showEmpty, emptyText, layout, subtotalPos, repeatLabels, blankLines, sort — is the same.

The number formatter, the formula compiler and the format-code reader are exported too, so they can be used without a pivot at all:

import { applyFormatCode, compileFormula, serialToDate } from 'barepivot';

applyFormatCode(1234567, '#,##0.0,," M"');        // { text: '1.2 M', color: null }
applyFormatCode(45358, 'dddd, mmmm d, yyyy');      // { text: 'Thursday, March 7, 2024', … }
compileFormula('IF(A > B, "up", "down")').evaluate({ A: 2, B: 1 });   // 'up'

renderChart() is the only export that touches the DOM (it builds SVG elements); chartData(), niceScale() and textWidth() are headless like everything else above, so a worker or a server can work out what a chart would contain without drawing it.

Project layout

package.json               entry points: main, module, exports, types, style; "files" whitelist
src/
  index.js                 public exports
  barepivot.js             the UI: field list, drop zones, table, popups, drag and drop
  barepivot.css            styles (theme variables, light / dark / auto)
  pivot-engine.js          aggregation, sorting, subtotals, formats, table model (no DOM)
  chart.js                 the pivot chart, drawn as SVG from the same model
  xlsx.js                  the .xlsx writer: a store-only ZIP plus the Open XML parts (no DOM)
  data/
    normalize.js           the five input formats -> one record format
    csv.js                 CSV / TSV parser and delimiter detection
types/index.d.ts           TypeScript declarations (copied to dist/)
scripts/build.mjs          builds dist/ with esbuild
scripts/serve.mjs          tiny static server for the demo
test/                      node --test suites: ingestion, engine, UI state, UI smoke, chart, xlsx, built package
demo.html                  interactive demo
README.md                  this reference
USAGE.md                   the task-oriented usage guide
ARCHITECTURE.md            internals reference for contributors
dist/                      build output (not committed; shipped in the npm package)

The build produces barepivot.esm.js, barepivot.cjs, a minified browser script (barepivot.min.js, global BarePivot), the stylesheet (.css and .min.css), source maps and index.d.ts.

The npm package contains dist/, README.md, USAGE.md, LICENSE and package.json — nothing else. Sources, tests and the demo stay in the repository.

Development

npm install
npm run dev      # serves demo.html at http://localhost:5173/demo.html (PORT=8080 npm run dev to change the port)
npm test         # ingestion, engine, UI, chart, xlsx and built-package tests, using Node's built-in test runner
npm run build    # rebuilds dist/  (esbuild is the only dev dependency)

demo.html feeds the same dataset through each of the five input formats, and lets you paste text or open a file. Add ?dist to the URL to run it against the built package instead of src/. The tests in test/dist.test.mjs check the built package and are skipped until npm run build has been run.

Releasing. npm publish runs prepublishOnly, which rebuilds dist/ and runs the full test suite, so a package cannot be published from a stale build or a failing tree. Bump the version first (npm version patch|minor|major); the build stamps it into the banner of every file and into the exported version. npm pack --dry-run lists exactly what will ship.

For scale, in development testing (Node and Chrome on one machine, so your numbers will differ) one million CSV rows were parsed in about 0.35 s and aggregated in about 0.5 s, and a table of roughly 87,000 cells rendered in about 0.23 s.

Limitations

Honest list of what BarePivot does not do, or does differently from Excel.

Deliberate differences

• No cell references or ranges in formulas. Excel does not allow them in a calculated field either, so this matches rather than falls short. A field reference is always that field's sum inside the cell, which is also what Excel does.
• Formulas are compiled, never evaluated. The parser is hand-written, so nothing reaches eval or new Function and the library works under a strict Content-Security-Policy. The cost is that only the documented functions exist.
• Error values are Excel's, but For error values show is not offered. Cells show #DIV/0! (an Average of no numbers, a StdDev or Var of fewer than two, a percentage of a zero total, a division by zero), #NUM! (SQRT(-1), LN(0), an overflow), #VALUE! (VALUE("x"), FIND with no match, arithmetic on text) and #N/A (% Of a named base item the cell has no data for), as Excel does. They sort last, never pass a value filter, stay unshaded by the heatmap and export to .xlsx as real error cells. Excel's option to replace them with other text is not offered.
• Calculated items in non-additive totals. Excel runs a calculated item's formula on the underlying records; BarePivot computes it from the item totals. The two agree for Sum, Count and Count Numbers, which is where calculated items count in subtotals and grand totals. An Average, Max, Min, StdDev or Var total leaves them out.
• Two aggregations Excel's classic PivotTable lacks, countUnique and median (Excel offers Distinct Count only through the Data Model). They are extras; nothing else changes when they are unused.

Not implemented

• No Pivo