PivotTables are live! How to implement this powerful data analysis function in pure front-end?

Among the three major office suites of Office, Excel may be the one with the most functions and the most difficult to master. From daily table entry to complex business data statistical analysis, Excel is like a martial arts master with unremarkable appearance but unfathomable strength. Proficiency in using Excel can often get twice the result with half the effort in daily work, and the so-called "proficiency" here is not only familiar with a large number of formulas, chart drawing and other operations, but also master a very important feature - pivot table.

The so-called pivot table is the product of subdividing and summarizing each group of related data involved in the original detailed data table. Users can process raw data in different combinations. For example, the following raw sales performance data:

We want to know how many cars each salesperson has sold, and how much the sales are, we can use a pivot table to analyze:

From the above table, you can clearly see how many cars, how many sales, how many brands each sells, etc. by several salesmen in each quarter, and the fields included in the pivot table are selected at will. For example, we only want to count the sales and sales of each quarter:

The car brand information is omitted here, and the corresponding field selection settings are:

We can also click on each field and select the sub-content that needs to be displayed, such as only looking at the performance of 1-3 quarters, or only looking at the performance of a certain salesperson, and so on.

It can be seen that the pivot table is a convenient tool for on-demand visualization of raw data, and it is widely used in daily work. When there is a raw data table with a lot of information in the work scene, you can use the pivot table to quickly obtain meaningful data insights and provide valuable information for the business.

Why do you need a pivot table for your front end?

In the past, the only channel for most business people to use pivot tables was Excel. Because the Office office suite has long monopolized the corporate office market, most administrative and business personnel only have access to Excel as a data analysis tool, and the functions of Excel itself are powerful enough that it is difficult for other tools to gain a foothold in the market.

However, with the surging tide of digitalization, digital transformation has become an opportunity and challenge for almost all enterprises. After the outbreak of the epidemic, the rise of home office has launched a new impact on the traditional office software environment. Today's enterprise IT, business and administrative personnel often need to switch between multiple software environments, processing massive data that is one or even several orders of magnitude higher than in the past, and may need to process business requests at any time in offices, subways, hotels, and homes. In such a scenario, the need for data analysis begins to appear everywhere and at any time, and Excel or similar large-scale monolithic software alone is far from being able to cope with the complex challenges of the new era.

Still taking the above business data as an example, the business director of this car sales company may review the current year’s data on the internal performance management app, and need to use the pivot table function in the app; when personnel collect bonus information, they need to log in to the company Background management website to view business data, and obtain performance rankings on Web pages; when executives prepare annual reports, they try to integrate dynamic pivot table components in the report to facilitate on-site display... All these requirements are difficult to use in a single form such as Excel. In more cases, it is suitable to use the embedding method to embed the pivot table function into the corresponding front-end application.

As such demand grows rapidly, one-stop solutions are beginning to emerge on the market, allowing enterprises and software developers to integrate functions traditionally performed by Excel into various applications and pages. Our protagonist today - SpreadJS is one of the best. SpreadJS is developed by Grape City. It is a pure front-end table control based on HTML5, compatible with more than 450 kinds of Excel formulas, and has the product features of "high performance, cross-platform, and high compatibility with Excel". It is favored by well-known enterprises such as Optical Software, and has been recognized as "China's Excellent Software Product" by the China Software Industry Association. The SpreadJS 14.0 version released in 2020 introduces a powerful pivot table function, which meets the needs of enterprises to integrate in-depth data analysis capabilities in many scenarios, and also greatly reduces the burden on front-end software developers.

Integrating pivot tables on the front end: a brief tutorial

Using SpreadJS, it is very simple to create a front-end embedded pivot table as shown:

The PivotLayout workbook in the above figure is the page of the pivot table, the DataSource is the original data page, and the panel on the right side of the figure is the pivot panel generated by SpreadJS, where the user can adjust the required fields to change the pivot table on the left display information.

In a pivot table, there are four areas:

• Filters: Controls the data range of the pivot table.
• Columns: Controls the column distribution of the pivot table.
• Rows: Controls the row distribution of the pivot table.
• Values: Controls the calculated data and calculation method of the pivot table.

Enter the following code to create a pivot panel:

 let panel = new GC.Spread.Pivot.PivotPanel("myPivotPanel", myPivotTable, document.getElementById("panel"));

The user must set the width and height for the "panel" element.

GC.Spread.Pivot.PivotPanel constructor parameters are as follows:

Here is the app.js file that embeds the pivot table using standard JS code:

 window.onload = function () {
    var spread = new GC.Spread.Sheets.Workbook(_getElementById('ss'), 
{ sheetCount: 2 });
    initSpread(spread);
    var pivotLayoutSheet = spread.getSheet(0);
    initPivotTable(pivotLayoutSheet);
};


function initSpread(spread) {
    spread.suspendPaint();
    let sheet = spread.getSheet(1);
    sheet.name("DataSource");
    sheet.setRowCount(117);
    sheet.setColumnWidth(0, 120);
    sheet.getCell(-1, 0).formatter("YYYY-mm-DD");
    sheet.getRange(-1,4,0,2).formatter("$ #,##0");
    sheet.setArray(0, 0, pivotSales);
    let table = sheet.tables.add('tableSales', 0, 0, 117, 6);
    for(let i=2;i<=117;i++)
    {
      sheet.setFormula(i-1,5,'=D'+i+'*E'+i)
    }
    table.style(GC.Spread.Sheets.Tables.TableThemes["none"]);
    let sheet0 = spread.getSheet(0);
    sheet0.name("PivotLayout");
    spread.resumePaint();
}


function initPivotTable(sheet) {
    let myPivotTable = sheet.pivotTables.add("myPivotTable", "tableSales", 1, 1, GC.Spread.Pivot.PivotTableLayoutType.outline, GC.Spread.Pivot.PivotTableThemes.light8);
    myPivotTable.suspendLayout();
    myPivotTable.options.showRowHeader = true;
    myPivotTable.options.showColumnHeader = true;
    myPivotTable.add("salesperson", "Salesperson", GC.Spread.Pivot.PivotTableFieldType.rowField);
    myPivotTable.add("car", "Cars", GC.Spread.Pivot.PivotTableFieldType.rowField);
    myPivotTable.add("date", "Date", GC.Spread.Pivot.PivotTableFieldType.columnField);
let groupInfo = { originFieldName: "date", dateGroups: [{ by: GC.Pivot.DateGroupType.quarters }] };
    myPivotTable.group(groupInfo);
    myPivotTable.add("total", "Totals", GC.Spread.Pivot.PivotTableFieldType.valueField, GC.Pivot.SubtotalType.sum);
    var panel = new GC.Spread.Pivot.PivotPanel("myPivotPanel", myPivotTable, document.getElementById("panel"));
    panel.sectionVisibility(GC.Spread.Pivot.PivotPanelSection.fields + GC.Spread.Pivot.PivotPanelSection.area);
    myPivotTable.resumeLayout();
    myPivotTable.autoFitColumn();
}
function _getElementById(id) {
    return document.getElementById(id);
}

The plug-in code added to the corresponding html page:

 <head>
    <meta name="spreadjs culture" content="zh-cn" />
    <meta charset="utf-8" />
    <meta name="viewport" content="width=device-width, initial-scale=1.0" />
    <link rel="stylesheet" type="text/css" href="https://demo.grapecity.com.cn/spreadjs/SpreadJSTutorial/zh/purejs/node_modules/@grapecity/spread-sheets/styles/gc.spread.sheets.excel2013white.css">
    <script src="https://demo.grapecity.com.cn/spreadjs/SpreadJSTutorial/zh/purejs/node_modules/@grapecity/spread-sheets/dist/gc.spread.sheets.all.min.js" type="text/javascript"></script>
    <script src="https://demo.grapecity.com.cn/spreadjs/SpreadJSTutorial/zh/purejs/node_modules/@grapecity/spread-sheets-shapes/dist/gc.spread.sheets.shapes.min.js" type="text/javascript"></script>
    <script src="https://demo.grapecity.com.cn/spreadjs/SpreadJSTutorial/zh/purejs/node_modules/@grapecity/spread-sheets-pivot-addon/dist/gc.spread.pivot.pivottables.min.js" type="text/javascript"></script>
    <script src="https://demo.grapecity.com.cn/spreadjs/SpreadJSTutorial/spread/source/data/pivot-data.js" type="text/javascript"></script>
    <script src="https://demo.grapecity.com.cn/spreadjs/SpreadJSTutorial/zh/purejs/node_modules/@grapecity/spread-sheets-resources-zh/dist/gc.spread.sheets.resources.zh.min.js" type="text/javascript"></script>
    <script src="https://demo.grapecity.com.cn/spreadjs/SpreadJSTutorial/spread/source/js/license.js" type="text/javascript"></script>
    <script src="app.js" type="text/javascript"></script>
    <link rel="stylesheet" type="text/css" href="styles.css">
</head>

As you can see, a total of about 60 lines of code can embed the pivot table and pivot table control panel in a web page.

In addition to supporting standard JS code, SpreadJS also supports Angular, Vue and React frameworks. When using Angular, you need to create a separate app.component.html page. The code of the app file for Vue and React will be longer, and the code snippet imported by the plugin should be placed in the app file.

Also, the pivot panel is just a tool to control the pivot table, which is automatically released when using fromJSON. Pivot tables can work without the Pivot Dashboard. So pivot table supports below api to handle the relationship between panel and pivot table.

Attach a pivot panel to a pivot table:

 ///* function attach(pivotTable: GC.Spread.Pivot.PivotTable) :void
/**
 * @description this function will attach to a pivot table
 * @param pivotTable
 * @returns void
 */
attach (pivotTable: IPivotTable): void

Detach the pivot panel from the pivot table:

 ///* function attach(): void
/**
 * @description this function will detach to a pivot table
 * @returns void
 */
 detach (): void

Destroy the pivot panel:

 ///* function destroy (): void
/**
 * @description destroy PivotPanel
 */
destroy (): void

In addition to pivot tables, your front end can do these

The power of SpreadJS doesn't stop at pivot tables. SpreadJS is a pure front-end form control launched by Grape City combining more than 40 years of professional control technology and experience in the field of spreadsheet application. It has developed into an online form control with functions comparable to Excel. SpreadJS is highly similar to Excel in interface and function, and can provide application scenario support for collaborative editing of table documents, data filling and report design of Excel-like reports for enterprise information systems. Using SpreadJS, functions such as efficient template design, online editing, and data binding can be directly implemented in front-end frameworks such as Angular, React, and Vue, providing end users with a highly Excel-like experience.

SpreadJS does not need to be pre-installed with any plug-ins or third-party components. It has a smooth interactive experience and can directly import and export Excel, CSV, JSON and other files in the browser. SpreadJS is compatible with hundreds of Excel calculation formulas, built-in 18 kinds of conditional formats, 32 kinds of charts, 53 cell formats and 182 kinds of shapes, and supports touch operation, with a pure Chinese interface.

It is worth mentioning that SpreadJS does not adopt the traditional DOM splicing method when building the interface, but uses the HTML5 Canvas drawing technology, which improves the performance and breaks the many limitations of DOM element rendering on the UI, and realizes a more accurate UI. Interface rendering effect. The technology has obtained the invention patent certificate issued by the State Intellectual Property Office.

In the latest SpreadJS 15.0 Update 1 version, cross-workbook formula function support, date slicer, Vue3 framework support and other updates have also been added. With the continuous update and development of SpreadJS, this tool has become one of the best options for modern enterprises to implement online Excel functions in various business scenarios and meet data processing, analysis and display needs anytime, anywhere. Click the link below to learn more about the powerful capabilities of SpreadJS and improve enterprise data productivity as soon as possible.

Front-end table pivot table online example:
https://demo.grapecity.com.cn/spreadjs/SpreadJSTutorial/features/pivot-table/pivot-panel/overview/purejs

Grape City front-end form control SpreadJS
https://www.grapecity.com.cn/developer/spreadjs

Excel-like full stack solution: https://www.grapecity.com.cn/developer/grapecitydocuments/solutions