@univerjs-pro/engine-pivot

Introduction

Pivot table is a powerful data analysis tool that can quickly summarize, organize and analyze large amounts of data, thereby helping users discover patterns and trends in the data. This package contains the analyze logic for univer pivot.

• 11 subtotal methods supported like excel
• Dimension label filtering and sorting (using localCompare method), label value filter.
• tabular layout
• expand / collapse
• Supports multiple value dimensions, and can customize the area (value position) and position (value index) of the multi-value dimension.
• rename field & format filed

More info you can get from link https://docs.univer.ai/guides/sheets/features/pivot-table#data-source

This npm package does not depend on any third-party libraries.

Usage

We do not recommend using this npm package alone because it is pure calculation logic without any UI. You can use @univerjs-pro/sheets-pivot and @univerjs-pro/sheets-pivot-ui to get the complete experience, here are the reference links: https://docs.univer.ai/en-US/guides/sheets/features/pivot-table

Installation

npm i @univerjs-pro/engine-pivot

Performance of Pivot Table

Test machine spec: a MacBook Pro with Apple Silicon M2 Pro.

| Spreadsheet Cells Count | Read data (s) | Query time (s) | | :---------------------------------------------------------------------- | :------------ | :------------- | | 500k (1 pivot row dimension, 1 pivot col dimension, 2 value dimensions) | 0.252 | 0.040 | | 1M (1 pivot row dimension, 1 pivot col dimension, 2 value dimensions) | 0.376 | 0.078 | | 2M (1 pivot row dimension, 1 pivot col dimension, 2 value dimensions) | 0.732 | 0.140 | | 5M (1 pivot row dimension, 1 pivot col dimension, 2 value dimensions) | 3.200 | 0.415 |

Custom Parameters

The following config is global config for pivot engine.

import {setMaxLimitItemCount, setDateSystem, PivotDateGroupFieldDateSystemEnum, setDefaultSortType} from "@univerjs-pro/engine-pivot"

// use the 1904 date system
setDateSystem(PivotDateGroupFieldDateSystemEnum.Date1904)

// set the max field items count to 200, this case only use for some data has many no repeated items, the performance is bad and human unreadable
setMaxLimitItemCount(200)

// change the default pivot sort type, if the pivot table not set sort info, it will works as default
setDefaultSortType('ascending')