How to build big data table components based on AntV S2

Previously: AntV released its new product, Multidimensional cross analysis table S2 in 2021, you can click here for details.

takeaway

In ant’s big data research and development platform, data table is an important business component. We need to smoothly display tens of thousands of SQL query results, and the results of screening, sorting, search, copy, box selection, aggregation analysis and other operations. At the same time, there are also scenarios of manual data entry, which requires the table to be able to be edited. So what we ultimately need is a JavaScript version of a spreadsheet, similar to a simple Excel workbook.

In this paper, we recorded how to implement React big data table component with many functions based on AntV S2, the open source Canvas table rendering library, when the open source software is not enough to meet the requirements and the commercial software is expensive and difficult to customize, and solved the performance problems and scalability problems of the previous commercial software versions.

Service Scenario Introduction

Dataphin is ant’s internal big data research and development platform, and also has a cloud version for sale. Dataphin provides one-stop big data capabilities for data acquisition, construction, management and use in the full life cycle. There are many scenarios in Dataphin that use big data tables. The most typical is in the R&D module, which is used to show the running results of computing tasks:

The following is a demonstration of the common functions of data tables:

Technology selection: Why AntVS2 ?

For overlapping spreadsheet class components, a Canvas based implementation will have greater advantages over a DOM implementation. In terms of performance, DOM rendering needs to go through the following rendering process, with all UI changes involving the construction of internal data structures, style parsing, and layout calculation. Finally render:

However, Canvas’s rendering pipeline is relatively simple. Canvas’s API directly corresponds to the low-level 2D graph call like Skia, which avoids DOM parsing and layout process. Therefore, graphics applications based on Canvas have a higher performance upper limit. Especially in the case of rich interaction and large data volume. It is also easier to write complex graphical interactions for Canvas based applications because graphics can be drawn arbitrarily instead of using rectangles as their smallest unit. Therefore, we mainly consider products based on Canvas in selection. Here is some analysis of the existing Excel – like front-end table components on the market:

The product name	Technology stack	developers	cost	conclusion
SpreadJS	Canvas	The company	charge	The most powerful Excel component on the market, but requires relatively high licensing fees, and in the case of multi-instance and large data volume, the performance is poor, and it is difficult to expand and customize UI
LuckySheet,x-spreadsheet	Canvas	personal	Open source	Open source Excel class components. They are all personal works, maintained by the community, which cannot guarantee the continuity and stability of investment. They are not as good as SpreadJS in terms of product detail experience
Handsontable	DOM	The company	charge	DOM implementation. There is also a charge.
Form services such as Sparrow, Nail, etc	Canvas	The company	/	It provides front-end and back-end integrated services through the servitization SDK, rather than a simple front-end component. It is also less scalable than open source products.

After failing to find a suitable option in the market, our Ant Data Intelligent front end team chose to incubate our internal crosstab component into the open source AntV S2 table rendering engine.

S2 is the data table visualization engine launched by AntV team, which aims to provide high performance, easy to expand, beautiful and easy to use multidimensional tables. Not only has the rich analysis table form, but also built-in rich interaction ability, to help users better look at the number and make decisions.

The S comes from SpredSheet’s two S’s, and the 2 also represents the row and column dimensions in the PivotTable. S2 not only has crosstab mode, but also supports detail mode.

A list is just a regular table. Similar to Antd Table component. Suitable for the display of detailed data. For example, the preview of SQL query results that we talked about at the beginning and so on. On the basis of supporting 10W + big data display, detailed table components also support rich interaction: single selection, swipe selection, copy to Excel, shortcut key multiple selection, column width and height drag adjustment and so on. It is convenient for users to select and operate data quickly.

Table function realization

The schedule of S2 provides a good base for us. You can refer to the documentation for the function of the schedule. Next, we’ll talk about how to implement a feature-rich React big data table component based on the detailed tables and the highly extensible design of S2. Some of the usual features, such as row freezing and replication, are built into S2. It also reveals some of S2’s internal implementations, such as how virtual scrolling works.

Features a larger version

Let’s take a look at the big picture of the big data table component:

The capabilities in the figure above include core capabilities such as data display, retrieval, export and editing. Analysis of the formula, aggregation and other capabilities are planned for the future. The design and implementation of these core functions will be discussed next.

interaction

Cell editing

Cell editing is the core of big data table interaction. Users can directly edit data and input data. It is widely used in scenarios such as manual dimension tables, debugging and typing Mock data, etc. As S2 is positioned as the core library of analysis tables and lists, the ability to edit cells is not built in. This capability needs to be extended by the big data table component.

The first question that needs to be considered is the technical implementation. Should the editing ability be implemented using DOM or Canvas? Before the feature development, we investigated various Canvas based spreadsheet libraries in the industry, and the results were as follows:

library	Cell editing implementation
SpreadJS	DOM
Language finches form	DOM
x-spreadsheet	DOM

As you can see, the mainstream implementations have chosen DOM as the carrier of cell editing. Considering that cursor drawing, text selection, line breaking logic and a series of other things will be implemented by the browser in DOM, but need to be re-implemented in Canvas, using Canvas to cover DOM nodes for text editing is the most cost-effective solution. So we finally decided to use DOM, which looks like this:

The following is a brief introduction to the implementation process:

1. Calculate the DOM mask size and coordinates

Editing is actually a Modality. The user’s Focus is required to block interaction with the bottom table during editing. So we need to mount a DOM mask container node of the same size as the Canvas to hold the TextArea. The element is positioned using the current window scroll Offset and Canvas container coordinate values.

const EditableMask = () = > {
  const { left, top, width, height } = useMemo(() = > {
    constrect = (spreadsheet? .container.cfg.containeras HTMLElement).getBoundingClientRect();
    const modified = {
      left: window.scrollX + rect.left,
      top: window.scrollY + rect.top,
      width: rect.width,
      height: rect.height,
    };

    return modified;
  }, [spreadsheet?.container.cfg.container]);
  return (
      <div
        className="editable-mask"
        style={{
          zIndex: 500.position: 'absolute',
          left.top.width.height,
        }}
      />
  );
};
Copy the code

The EditableMask component is rendered into the body by reactdom.render as a child of the top layer:

2. Register the event and render the TextArea element

Editing is typically triggered by a double click, and S2 provides us with the DATA_CELL_DOUBLE_CLICK event. Then to look at the rendering logic of the TextArea, we need to get the location of the cell and the current data value. The core data x/y/width/height/value can be retrieved from the DataCell object when the event is triggered. Need to pay attention to, the x and y here is for the whole table, not the current ViewPort, so we need to use the spreadsheet. The facet. GetScrollOffset forms () to obtain the internal state of rolling and targeted to the x/y be amended.

The core logic is as follows:

// Retrieve the Cell object from the event callback
const cell: S2Cell = event.target.cfg.parent;

// Calculate the positioning and width
const {
  x: cellLeft,
  y: cellTop,
  width: cellWidth,
  height: cellHeight,
} = useMemo(() = > {
  const scroll = spreadsheet.facet.getScrollOffset();

  const cellMeta = _.pick(cell.getMeta(), ['x'.'y'.'width'.'height']);

  // Subtract the scroll value to get the position relative to ViewPort
  cellMeta.x -= scroll.scrollX || 0;
  cellMeta.y -=
    (scroll.scrollY || 0) -
    (spreadsheet.getColumnNodes()[0) | | {height: 0 }).height;

  return cellMeta;
}, [cell, spreadsheet]);

// Get the current cell value and store it in state
const [inputVal, setinputVal] = useState(cell.getMeta().fieldValue);
Copy the code

We then render the TextArea into the DOM mask using x/y/width/height, fill in the current value of the cell, and manually.focus() so that the user can start editing directly.

3. Clear the logic after editing

When the user fires the onBlur event or enters enter, we destroy the TextArea and backfill the modified values into spreadsheet.originData. The corresponding event is thrown to notify the consumer.

Brush to choose

In Excel, swiping is an important interaction. The user can freely select cells in batches, and when the mouse moves outside the canvas, the canvas will automatically scroll and update the box selection area:

S2 has a built-in swiping interaction. Let’s look at how this interaction is implemented. Let’s get some concepts straight:

• StartBrushPoint Select the starting point of the brush. The MouseDown event was recorded.
• EndBrushPoint Select the end point of the brush. Updated during MouseMove events.
• BrushRange Brush selection range. Contains the row Index of the start point and row Index of the end point.

Brush is to listen for mouse drag events and highlight the grid between the start and end points. But this is just the beginning, we need to support the automatic scrolling of the swipe selection, the core process is as follows:

First, in the scrolling trigger phase, when listening to MouseMove events, you need to add judgment. If the mouse is not within the scope of the canvas, the automatic scrolling process will enter, and limit EndBrushPoint to the canvas. As shown in figure:

The coordinates of the point triggered by the MouseMove event are outside of the canvas, so we’re going to project this point vertically to the edge of the canvas. I get the EndBrushPoint.

And in the process, we can also get the direction of the roll. We split scroll directions into Leading and Trailing (head and tail). It is also divided into X and Y directions. So there are eight possible directions of roll. Based on where the MouseMove coordinates are, you can calculate the direction you want to scroll. As shown below:

After knowing the scrolling direction, automatic scrolling can be triggered.

There are some details to consider. For example, in rolling, the width and height of each grid may be different, if the distance of rolling is a constant value, it will appear in the encounter of a very high or very wide grid, there will be a turtle speed rolling several times to roll a grid. So the distance of each scroll must be a dynamic value. The actual landing scheme looks like this: If you scroll to the right, for example, the Offset of the scroll will be positioned to the right edge of the next grid. Like this:

Make sure that after scrolling, the next grid is completely inside the viewport.

The frequency of the scroll is also a detail to pay attention to when rolling in a loop. Different users have different demands for scrolling frequency. So the rolling frequency should not be constant. One solution is to correlate the scrolling speed with the distance the scroll leaves the canvas. The more you pull down, the faster you scroll until you reach a maximum. For example, the implementation in Excel:

Rough calculation logic for rolling duration:

const MAX_SCROLL_DURATION = 300;
const MIN_SCROLL_DURATION = 16;
let ratio = 3;
// The X-axis scrolls slowly
if (config.x.scroll) {
  ratio = 1;
}
this.spreadsheet.facet.scrollWithAnimation(
  offsetCfg,
  Math.max(MIN_SCROLL_DURATION, MAX_SCROLL_DURATION - this.mouseMoveDistanceFromCanvas * ratio),
  this.onScrollAnimationComplete,
);
Copy the code

Some of the parameters are determined according to the actual user’s sense of body. Need to go through continuous optimization iteration, to achieve a more ideal state.

Apply colours to a drawing

Virtual rolling

How to render a large amount of data with high performance has always been one of the most important problems in the big data table scene. We can think of a big data table as a long list, for which the most common optimization strategy is to render only the visible area. After the scroll event is triggered, adjust the corresponding rendered content according to the scroll Offset. From the user’s point of view, it’s a long, complete list.

DOM’s virtual list implementations have a lot of things to think about, and there are also Reactive -Virtualized libraries to play on. Canvas – based long list optimization is called virtual scrolling. Because Canvas based applications redraw the entire interface every time they render. We use requestAnimationFrame to Schedule a render so that frequent scrolling events fall into the browser’s render cycle. Then, in each animationFrame callback, it simply calculates the range of elements in the current viewport and renders those cells.

Let’s talk about the specific implementation process:

The first step: calculate the coordinate range of visual area, and get the grid list of visual area

First, calculate the range of the visible area according to the row and column information and the current scroll Offset, and obtain an array, including [xMin, xMax, yMin, yMax]. That’s the index range of rows and columns. As shown below:

Step 2: Compare with the previous grid list to get Diff

In this step, we Diff the last rendered viewable area Index and the current one to get the new and removed cells:

export const diffPanelIndexes = (
  sourceIndexes: PanelIndexes,
  targetIndexes: PanelIndexes,
): Diff= > {
  const allAdd = [];
  const allRemove = [];

  Object.keys(targetIndexes).forEach((key) = > {
    const{ add, remove } = diffIndexes( sourceIndexes? .[key] || [], targetIndexes[key], ); allAdd.push(... add); allRemove.push(... remove); });return {
    add: allAdd,
    remove: allRemove,
  };
};
Copy the code

Step 3: Add and remove the Diff results respectively

In this step, we operate the Canvas object tree through AntV/G API to instantiate and add/delete the grid. The actual effect is similar to the animation below (the add operation is delayed to make it more obvious) :

This allows virtual scrolling so that the rendering time for each scroll is dependent only on the viewport size, rather than a linear increase.

This example from the official website shows a smooth rendering of the sheet at 100W.

Custom column headers

In our business, in addition to the column header of the data, we need to implement an Excel like ordinal column header, like this:

The data column also needs to support screening, sorting, desensitization display, with many different states:

For such a requirement, S2 can easily meet it. Since the bottom drawing engine of S2 is AntV/G, we do not need to use the bottom Canvas API, which reduces the threshold and maintenance cost. Most importantly, S2 supports custom cells. We simply inherit the built-in Cell Class, override the corresponding methods, and pass the new Class to S2.

Options for S2 provides a number of apis to customize cell rendering:

For the above scenario, we can customize the dataCell. Fully customizable Icon drawing by overriding the drawActionIcon method:

import { TableDataCell } from '@antv/s2';

class S2Cell extends TableDataCell {
  
  private drawActionIcon() {
    // Draw logic, since TableDataCell inherits AntV/G Group objects
    // We can use addShape to draw the Canvas}}export default S2Cell;
Copy the code

In the above code, the user can draw the Icon through S2’s built-in renderIcon tool method, and control the style of the Icon according to the filtering and sorting status. You can also register custom events to define the behavior of Icon clicks. The underlying drawing apis are AntV/G. Therefore, when we customize S2 cells, we only need to learn THE API of G to get started.

By customizing the corner head, you can also achieve Excel like triangle corner head effect:

import { TableCornerCell } from '@antv/s2';

export default class CornerCell extends TableCornerCell {
  protected drawTextShape() {
    this.textShape = this.addShape('polygon', {
       // Graphics properties}); }}Copy the code

layout

The ranks of frozen

Front-end tables generally support a fixed number of columns on the left or right, such as Antd. In addition to column fixation, Canvas SpreadJS also supports row fixation. This is also one of the most common features of Excel:

Fixed columns and columns are usually important reference information, such as IDS, names, and so on. When the number of columns is large, information fixation enables users to quickly understand the context of each column while viewing the information.

Frozen means that no matter how the contents of the table are scrolled, frozen rows will always be displayed above or below the view, and frozen columns will always be displayed to the left or right of the view. In other words, the y coordinate of frozen rows will remain the same as the scroll, and the X coordinate of frozen columns will remain unchanged as the scroll.

In S2, we can freeze rows and columns by setting these Options:

Let’s talk about how to freeze rows and columns, because we need to do something special for the frozen part of the scroll. So first we need to group the content areas of the list:

Firstly, we classify the frozenRowGroup, frozenColGroup, frozenTrailingRowGroup and frozenTrailingColGroup to correspond to the four freezing directions respectively, and put the cells to be frozen in each direction into these groups. Then, a panelScrollGroup is also needed to store ordinary grids, that is, grids that can scroll freely with x and Y directions.

In addition to these conventional groupings, we also noticed that the four frozen groupings are crossed at the four corners, and the grids in these crossed areas do not need to be rolled in the X and Y directions, so these grids need to be placed in a special group, and special positioning processing similar to postion: fixed should be done. For these cells, we put two groups, frozenTopGroup and frozenBottomGroup.

At the same time, for column fixation, not only the data grid needs to be fixed, column headers also need to be fixed. Therefore, we divide the column header area into three groups, namely frozenColGroup, scrollGroup and frozenTrailingColGroup.

So the next idea is to first determine which region the grid belongs to during rendering, and then add the corresponding group. Then translate for different groups. For example, fixed columns only need to scroll in the y direction, fixed rows only need to scroll in the x direction.

Next we need to modify the render link:

Step 1: Render the cross-freeze area grid

For cross freeze on the grid, that is, on the four corners area: frozenTopGroup/frozenBottomGroup two groups. These cells only need to be added once in the render cycle of the table, so the nodes can be inserted when S2 initializes, just like the headers.

Step 2: calculate the grid coordinate range of visual area

S2 uses the virtual scrolling described above, so scrollX and scrollY are used to calculate the current grid size in the viewable area during rendering. Before, there was only one group in the content area, and the coordinate range of the calculated grid was structured like this:

export type Indexes = [number.number.number.number]; // x Min, x Max, y Min, y Max
Copy the code

Grids with coordinates within this rectangle are rendered.

Once row freezing is supported, this logic needs to be modified to calculate not only the cell range of the scroll region, but also the cell range of the four frozen regions: frozenRow, frozenCol, frozenTrailingRow, and frozenTrailingCol. Because the row and column freeze area supports one-way scrolling, you also need to do virtual scrolling. The calculation results of visual area coordinates need to be changed to the following structure:

export type PanelIndexes = {
  center: Indexes;
  frozenRow: Indexes;
  frozenCol: Indexes;
  frozenTrailingRow: Indexes;
  frozenTrailingCol: Indexes;
};
Copy the code

Step 3: Render the grid groups

This step is very simple, for each cell in the viewable area, determine which group the cell belongs to, and then add the cell to this group.

Step 4: Translate while scrolling

When translate, unidirectional scrolling is required for groups with frozen columns and columns.

• FrozenRow and frozenTrailingRow need to follow the scroll in the X direction.
• FrozenCol and frozenTrailingCol need to follow the scroll in the Y direction.

ColHeader transformation

As with Panel, ColHeader should also be rendered in groups. In layout, ColHeader is divided into frozenColGroup (left fixed column head), scrollGroup (unfixed common column head), and frozenTrailingColGroup (right fixed column head). Then, when doing offset with ColHeader, only the scrollGroup is offset.

Note that column headers can be resized. Therefore, Resize hot zones are also drawn. For frozen columns, we also need to fix the drawing position of the hot zone. Therefore, for the frozen column Resizer, it is necessary to draw it into a special group, similar to the scrollGroup of column head grid. In addition, you need to perform a Clip operation on the unfrozen hot zone to prevent the hot zone from overflowing into the frozen column.

Toolbar Operation

Filter & Sort

Filtering is one of the most frequently used operations by users. At present, we have implemented two filtering modes: numerical filtering and expression filtering. Let’s talk about their design and implementation:

Numerical filtering

Value filter through S2 DataCfg filterParams parameter Settings:

export interface FilterParam {
  filterKey: string;  // The key of the column to filterfilteredValues? : unknown[];// The filtered value
}
Copy the code

The main implementation in big data tables is to help users visually edit the values to be filtered. The difficulties are as follows:

First, the filtering detail logic needs to be aligned with Excel to ensure that the user habits remain unchanged

• Search text filtering: If the search box has a filter value, the base is the value in the filter box when the point is determined (all values outside the filter range will be filtered out).
• Filter value linkage: When other columns already have filter criteria and the current entry does not have filter criteria, the check box for value filter hides the value that has been filtered by other row filters. If you filter again, the filter values of all columns with filter parameters set will work together. Similar to a multi-level linkage filter.

Second, the problem of filter list running down under large data volume

• Because Checkbox is rendered by DOM, and the big data table component will often carry 1W + data, which will cause the user to lag in screening and affect the user experience. The solution is to perform a virtualized roll on the UI components that are selected by the values, for example, on React – Virtualized mode. Only DOM elements within the viewable area are rendered.

Expression filtering

In expression filtering, you can configure forms to filter expressions consisting of fields, operators, and values. At the same time, two expressions can be combined by two logical conditions: AND AND OR.

Expressions are a process of building DSLS. Without compromising future extensibility, we support unary and binary operators in our implementation:

interface Serializable<T> {
  // serialize, support the reconstruction method from string
  serialize(): ExpressionOf<T>;
}

// Combination type
export enum JoinType {
  OR = 'OR',
  AND = 'AND',}// Unary operator
type UnitExpressionOf<T> = {
  key: FilterFunctionTypes;
  operand: T;
};

// Binary operator
type JoinExpressionOf<T> = {
  type: JoinType;
  funcA: ExpressionOf<T>;
  funcB: ExpressionOf<T>;
};

export type ExpressionOf<T> = UnitExpressionOf<T> | JoinExpressionOf<T>;

export abstract class FilterFunction<T> implements Serializable<T>, Filterable {
  filter(value: unknown): boolean {
    throw new Error('Method not implemented.');
  }
  
  key: FilterFunctionTypes;

  private _operand: T;

  // the other side of the expression: e.g., value > 2, then 2 is the value
  public get operand() :T {
    return this._operand;
  }
  
  public set operand(value: T) {
    if (isValidNumber(value)) this._operand = Number(value) as unknown as T;
    else this._operand = value;
  }

  public serialize() {
    return {
      key: this.key,
      operand: this.operand, }; }}Copy the code

By inheriting FilterFunction AND implementing the.filter() method, we have a filter expression type that can be used in business components AND supports arbitrary atomic capabilities of AND AND OR:

The sorting

Sorting is implemented using sortParams of S2’s DataCfg.

Sorting is implemented in much the same way as filtering, with one caveat: filtering can occur simultaneously in multiple data columns, and sorting is exclusive, so setting the sort of the current column when other columns already have a sort will overwrite the sorting conditions of the other columns.

search

Search implementation process:

1. Iterate through the data set to find all the cells that match the keyword
2. The user selects the search results and performs the Focus operation on the next found cell

The difficulty lies in the Focus operation, which requires rolling the grid not in the viewport into the viewport. Like this:

S2 provides facet. ScrollWithAnimation API. The argument is the Offset of the scroll. So the problem ultimately comes down to how to calculate the Offset of scrolling so that the grid fits into the viewport.

The core logic is to use the calculateInViewIndexes mentioned earlier to get the range of the current viewable area. Then determine which direction the current grid is in the current viewport in both X and Y directions, and calculate the corresponding scroll Offset. Another point is that the width and height of the frozen area of the row and column needs to be subtracted from the Offset so that the grid is exposed below the frozen area of the row and column.

Column display control

Column display control allows users to select some columns to display, which helps users to focus on the current several columns when there is a large amount of column data:

In terms of implementation, S2 provides convenient apis to control the state of the table, such as setDataCfg to update data and setOptions to update table options. We can easily control the display and hiding of columns by controlling the values passed in by dataCfg columns. We can add a form component outside S2 that stores the columns that need to be hidden in the hiddenCols array. After the user updates the column display Settings, we only need to perform the following logic to control the column display of S2:

const hiddenCols = []  // The column selected by the user to hide
const cols = [] // All columns
s2.setDataCfg({
  fields: {
    // Filter out the columns in the hiddenCols array
    columns: cols.map((col) = > hiddenCols.indexOf(col) === -1)}})Copy the code

Just modify hiddenCols and re-render to hide columns under external UI control.

copy

S2 implements the function of copying page tables to reserved cells of Excel. Here are a few things to note about implementing this feature:

1 Acquisition of data content

S2 uses virtual scroll mode, so we can’t get data directly from DataCell instances. For example, when the entire row or column is selected, the cells in the invisible area are actually unrendered, so data cannot be retrieved from this area. To solve this problem, we modified S2 to provide Cell Meta (Cell metadata, such as Cell type, column and row index) when selecting interaction. When copying, we finally select the information of the selected grid from the source data through Cell Meta, and then reassemble it into the content to be copied.

2 Data content stitching

Once we have the data content, we need to piece it together to make it fit the table copy specification. We know that the data in the clipboard is ultimately a string, but how does Excel recognize different cells?

The answer is the TAB ‘\t’ and the newline ‘\r\n’. Each line of data is distinguished by the newline character ‘\r\n’. A TAB character ‘\t’ is required after the data in each grid in each row.

One particular case to consider is line breaks inside cells. Both inline and whole line breaks are marked ‘\r\n’, so how do you distinguish the two cases? In practice, you simply surround the contents of a cell with double quotes, telling the table that this is the contents of a cell, and you can implement inline newlines. The actual code is as follows:

Export const convertString = (v: string) => {if (/\n/.test(v)) {return '"' + v + '"'; } return v; };Copy the code

3 Selection of replication API

The previous mainstream replication API was Document.execcommand (‘copy’). However, in the case of a large amount of data (tens of thousands), this API will be stuck. So in conditional browsers, we use the Navigator.clipboard API for replication. The general logic of the utility function copyToClipboard is as follows:

export const copyToClipboard = (str: string, sync = false) :Promise<void> = > {if(! navigator.clipboard || sync) {return copyToClipboardByExecCommand(str);
  }
  return copyToClipboardByClipboard(str);
};

export const copyToClipboardByClipboard = (str: string) :Promise<void> = > {return navigator.clipboard.writeText(str).catch(() = > {
    return copyToClipboardByExecCommand(str);
  });
};

export const copyToClipboardByExecCommand = (str: string) :Promise<void> = > {return new Promise((resolve, reject) = > {
    const textarea = document.createElement('textarea');
    textarea.value = str;
    document.body.appendChild(textarea);
    textarea.focus();
    textarea.select();

    const success = document.execCommand('copy');
    document.body.removeChild(textarea);

    if (success) {
      resolve();
    } else{ reject(); }}); };Copy the code

---

Results: Doubled performance improvement, perfect custom UI, smooth replacement of commercial form products

Above, we introduced how to realize full-function big data table based on AntV/S2. Now let’s take a look at what the new table solves. Previously, our online big data tables used SpreadJS (V13), which had the following problems:

Problems with old Spreadjs-based components

1. Built-in component styles are difficult to customize

SpreadJS’s built-in filtering, sorting and other UI components are written in native JS. The React component cannot be extended or replaced. You can’t customize the DOM structure. So the built-in ICONS and layouts are hard to change. It’s basically a one-shot deal. At the same time, the location of Modal can not be customized, limited to the inside of the table, often block the content of the table.

2. Cell rendering is difficult to customize

SpreadJS cells are theoretically extensible, but can only be drawn using the Canvas native API. There is also a lack of documentation and source support. Overall rendering customization is very difficult. Adding a desensitized icon, for example, would take days and would be difficult to implement.

3. Use is complex and often has unknown bugs

When SpreadJS is used with React, it needs to encapsulate the React component itself. That’s the extra cost. At the same time in the process of use we also found some occasional render exception, but there is no complete source code, so the investigation is more difficult.

4. Large volume and complex data model lead to poor performance

Open 10 data result tabs (10 table instances) at the same time, each showing 1000 data. At this time the switch has obvious lag. The switching time is about 500ms. User perception is obvious.

In a scenario with a large amount of data, SpreadJS takes eight seconds to display the 50,000 data files uploaded by users.

Based on theS2How does the big data table deal with these problems

Extensibility & componentization: solve built-in component style customization & cell rendering customization and difficult to get started

With the built-in FILTERING and sorting API of S2, as well as the ability to customize cell rendering, we only need to encapsulate the React component and can customize the UI of the component freely. For example, in business, we use Antd as the BASIC UI library to encapsulate filter & sort Modal that conforms to business style. We can use Antd capability instead of building Modal capability repeatedly. At the same time, the overall layout and content of Modal are customized, with great freedom:

S2 also provides built-in SheetComponent components that allow us to encapsulate pure JS libraries into React components. To get started with React, just copy the Demo code and run.

Open source & self research: to solve intractable diseases

In commercial software, it is difficult to Debug purchased code that has been confounded. In open source software, with source code in hand, all difficult problems are not a problem, can look at the source code, troubleshooting problems to be solved. And with the help of the open source community, bugs are quickly fixed. After a period of evolution and discipline, S2 will become more powerful and perfect.

Lightweight Data Model & Virtual Scrolling: Solving performance problems with large data volumes

S2 Positioning is a lightweight table rendering core, so it doesn’t have the complex functionality and data model of Excel/SpreadJS. In the big data table component scenario, there is no need for such a heavy data model, and the lightweight design of S2 is more appropriate. In terms of performance, when switching between 10 tabs, the switching time is 80ms, which is 6 times that of the old scheme. With 50,000 pieces of data, S2 renders and initializes in 1 second. Eight times more than the old scheme.

At the same time, S2 can also be expanded according to the actual situation. In the future, we will also launch an S2-like Excel spreadsheet scenario component, which provides plug-in and scenario-based capabilities. Users can use it on demand or introduce advanced functions such as formulas through plug-in.

---

conclusion

Thank you for watching. The story of how to build a big data table component using AntV/S2 is over. But AntV/S2 is just getting started. Next, we will precipitate the cell editing, search, advanced sorting and other capabilities of big data table scenarios into S2-React, so that the whole community can use them. We will also continue to strengthen the underlying capabilities of S2. Support multi-level column header, aggregation calculation and other capabilities, to optimize the rendering performance under large resolution. We also welcome students from the community to build AntV/S2 together with us to create the strongest open source big data table engine. Star⭐️⭐️⭐ ⭐️ encouragement if you get anything from this article.

Related links to S2:

• Github
• website
• Core layer: @ANTV /s2 V1.11.0
• Component layer: @antv/s2-react V1.11.0