wpis

Large amounts of data in tables? Not a problem!

Most of the new projects we embark on with our clients at BrandOriented make it clear that Excel is commonly used in organisations as a tool for virtually anything, including databases, scheduling, analytical and statistical systems and even process monitoring. This demonstrates not only the universality of the programme, but also the attachment of users to this solution, despite its rather questionable effectiveness in comparison to various dedicated tools. Regardless of the implemented ERP systems (e.g. ORACLE or SAP 4Hana), Excel has dominated the market uninterruptedly for many years, with users preparing reports manually by collating and formatting data, and sometimes even cleaning data structures from different years in order to be able to create a summary. Although the level of effectiveness of this solution varies, users often indicate a major advantage of Excel – the ability to display large amounts of data in a single view. Our goal is to make sure that the Effiana system, designed to streamline the daily work of such users, is not inferior in this regard.

This is where the real challenge begins – how to achieve a situation where the user who works on gigantic tables, often containing several hundred columns and several thousand rows, does not have to worry about the app “jumping” or more data being constantly loaded. To that end, the BrandOriented team responsible for the Effiana system explored many possibilities and solutions. Unfortunately, each of them had limitations of some kind, both minor and major. For this reason, we have decided to combine various elements of such analysed alternatives and to design a comprehensive solution that ensures efficiency and stability.

Without going into greater detail at the outset, let’s cut to the chase – as an example, we will use a table with 1,000 columns and 60,000 records.

If we wanted to display the entire table in the traditional way, we would come across the “Aw, Snap!” error (in Google Chrome) or – at best – major performance problems when scrolling.

So what can be done to address these performance issues?

This is where Canvas (or, simply put – “curves”) comes to our aid. By drawing with Canvas, we offload the CPU and transfer the rendering operations to the graphics card. Of course, even Canvas has its performance limits, so to handle such large amounts of data, we need to add a limit to the scope of rendering of elements in the viewport. In addition, we have to split our input data (columns, rows) into smaller sets.

Now, it is time to move from theory to practice.

Assumptions

The table is built of two sets: columns (layout) and rows/records (objects).
During the data loading process, calculations are performed for the entire table (columns, rows).
The coordinates of the cells are calculated in real time during scrolling.

Please note! The examples below contain simplified code (intended to illustrate the solution mechanism, and not the finished app code), based on calculations for columns.

Functions used:


function sumArrayValues(items, itemGetter = (value) => value) { 
    let sum = 0; 

    for (let i = 0; i < items.length; i += 1) { 

        sum += itemGetter(items[i]); 

    }

    return sum; 
}

Preparing the structure and dimensions

At the beginning, we need to prepare an appropriate data structure, which in this case has been simplified as much as possible in order not to create any distractions from solving the problem in question.

Example structure:


# Columns: 
const columns = [ 
    { 
        name: 'columnName1', 
        label: 'Kolumna 1', 
        width: 120 
    }, 
    { 
        name: 'columnName2', 
        label: 'Kolumna 2', 
        width: 120 
    }, 
    ... 
    { 
        name: 'columnName3', 
        label: 'Kolumna 3', 
        width: 120 
    }, 
];

# Single row: 
const rows = [ 
    { 
        columnName1: 'value 1', 
        columnName1: 'value 2', 
        columnName1: 'value 3' 
    } 
];

Calculating the size of the table

To make things simpler, let’s assume that each row has a fixed height of 40px. Ultimately, the rows can vary in height, which will allow you to adjust them according to the content of the cells. You may also wish to add a mechanism for both dynamic and manual height adjustment, but it will have only a minimal impact on the efficiency of the solution.


const dimensions = { 
    contentHeight: 0, 
    contentWidth: 0
};

dimensions.contentWidth = sumArrayValues(columns, (item) => item.width);

dimensions.contentHeight = sumArrayValues(columns, (item) => 40);

Dividing the structure into chunks:

In order to accelerate iteration over columns/rows, the sets should be divided into smaller components – chunks.

The structure of chunks with respect to columns:


const columnChunks = { 
    columnChunks: [], 
    indexChunks: [], 
    offsetChunks: [0], 
    widthChunks: [] 
}; 

let counter = 0; 
let indexCounter = 0; 

while (columns.length > 0) { 
    const itemsChunk = columns.splice(0, 100); 
    const offset = sumArrayValues(itemsChunk, (item) => item.width); 
    const offsetPrevious = columnChunks.offsetChunks[counter] || 0; 
    const chunkIndexes = itemsChunk.map((item, index) => indexCounter + index); 

    columnChunks.widthChunks.push(itemsChunk); 
    columnChunks.indexChunks.push(chunkIndexes); 
    columnChunks.columnChunks.push(itemsChunk); 
    columnChunks.offsetChunks.push(offset + offsetPrevious); 

    indexCounter += itemsChunk.length; 

    counter += 1; 
}

Table

Once the chunks have been prepared, we can proceed with the calculations and programme a mechanism that will only display the part of the table that is to be seen in the viewport based on the current position of the scrollbar. It is actually the most important element, as this is where the optimisation takes place.

The visible part of the table


const visibleRange = { 
    columnIndexList: [], 
    columnList: [], 
    columnOffsetList: [], 
    columnWidthList: [] 
};

Let's assume that scrollState.left contains the current position of the scrollbar.


for (let chunkIndex = 0; chunkIndex < columnsChunks.offsetChunks.length; chunkIndex += 1) { 
    const chunkOffset = columnsChunks.offsetChunks[chunkIndex]; 

    for (let colIndex = 0; colIndex <= columnsChunks.widthChunks[chunkIndex]?.length; colIndex += 1) { 
        const horizontalPosition = sumArrayValues(columnsChunks.widthChunks[chunkIndex].slice(0, colIndex) || []); 
        const horizontalOffset = chunkOffset + horizontalPosition; 
        const colIndexMapped = columnsChunks.indexChunks[chunkIndex][colIndex]; 
        
        if (colIndexMapped <= frozenColumnsAmount || (scrollState.left - toleranceMargin <= horizontalOffset && scrollState.left + dimensions.viewportWidth + toleranceMargin >= horizontalOffset)) { 
          visibleRange.columnIndexList.push(colIndexMapped); 
          visibleRange.columnWidthList.push(columnsChunks.widthChunks[chunkIndex][colIndex]?.width); 
          visibleRange.columnList.push(columnsChunks.columnChunks[chunkIndex][colIndex]); 
          visibleRange.columnOffsetList.push(horizontalOffset); 
        }
    }
}

Calculating the positions and sizes of the cells

Based on the “visibleRange” value, we are able to calculate the coordinates for the visible cells.


const cells = {}; 

for (let rowIndex = 0; rowIndex < visibleRange.rowHeightList.length; rowIndex += 1) { 
    const rowPosition = visibleRange.rowOffsetList[rowIndex]; 
    const rowIndexMapped = visibleRange.rowIndexList[rowIndex]; 

    for (let colIndex = 0; colIndex < visibleRange.columnWidthList.length; colIndex += 1) { 
        const columnPosition = visibleRange.columnOffsetList[colIndex]; 
        const colIndexMapped = visibleRange.columnIndexList[colIndex]; 
        
        cells[colIndexMapped:rowIndexMapped] = [ 
            columnPosition, 
            rowPosition, 
            visibleRange.columnWidthList[colIndex], 
            visibleRange.rowHeightList[rowIndex], 
            columns[colIndexMapped]?.name 
        ]; 
    } 
}

Rendering

On the basis of the previous calculations in “visibleRange”, we can generate vertical/horizontal lines and apply text within the cells.


const tableGridCanvas = document.querySelctor('#table').getContext('2d'); 
const TABLE_RENDER_OFFSET = 0.5; 

const offsetX = -scrollState.left; 
const offsetY = -scrollState.top; 
const height = dimensions.viewportHeight; 
const width = dimensions.viewportWidth; 

// columns - vertical lines
for (let colIndex = 0; colIndex <= visibleRange.columnWidthList.length; colIndex += 1) { 
    const horizontalOffset = visibleRange.columnOffsetList[colIndex]; 

    tableGridCanvas.beginPath(); 
    tableGridCanvas.lineWidth = 1; 
    tableGridCanvas.strokeStyle = '#000'; 
    tableGridCanvas.moveTo(horizontalOffset + TABLE_RENDER_OFFSET + offsetX, TABLE_RENDER_OFFSET + offsetY); 
    tableGridCanvas.lineTo(horizontalOffset + TABLE_RENDER_OFFSET + offsetX, height + TABLE_RENDER_OFFSET + offsetY); 
    tableGridCanvas.stroke(); 
    tableGridCanvas.closePath(); 
} 

// rows - horizontal lines
for (let rowIndex = 0; rowIndex <= visibleRange.rowHeightList.length; rowIndex += 1) { 
    const verticalOffset = visibleRange.rowOffsetList[rowIndex]; 

    tableGridCanvas.beginPath(); 
    tableGridCanvas.lineWidth = 1; 
    tableGridCanvas.strokeStyle = '#000'; 
    tableGridCanvas.moveTo(TABLE_RENDER_OFFSET, verticalOffset + TABLE_RENDER_OFFSET + offsetY); 
    tableGridCanvas.lineTo(width + TABLE_RENDER_OFFSET, verticalOffset + TABLE_RENDER_OFFSET + offsetY); 
    tableGridCanvas.stroke(); 
    tableGridCanvas.closePath(); 
} 

// content 
for (let rowIndex = 0; rowIndex < visibleRange.rowHeightList.length; rowIndex += 1) { 
    for (let colIndex = 0; colIndex < visibleRange.columnWidthList.length; colIndex += 1) { 
        const colIndexMapped = visibleRange.columnIndexList[colIndex]; 
        const rowIndexMapped = visibleRange.rowIndexList[rowIndex]; 

        const [x, y, width, height] = cells[colIndexMapped:rowIndexMapped] || []; 
        const columnName = columns[colIndex].name; 
        const content = (rows[rowIndex] && rows[rowIndex][columnName]?.toString()) || ''; 
        const paddingHorizontal = 5; 
        const paddingVertical = 1; 
        const positionVertical = paddingVertical + height / 2; 

        tableGridCanvas.beginPath(); 
        tableGridCanvas.font = '13px Arial'; 
        tableGridCanvas.fillStyle = '#000'; 
        tableGridCanvas.textBaseline = 'middle'; 
        tableGridCanvas.fillText( 
            content, 
            x + TABLE_RENDER_OFFSET + offsetX + positionHorizontal, 
            y + TABLE_RENDER_OFFSET + offsetY + positionVertical, 
            width 
        ); 

        tableGridCanvas.closePath(); 
    } 
}

Events

In order to be able to detect something, for example, a click on a cell, we add a “click” event to the entire Canvas object.


let currentCell = { 
    dimensions: [0, 0, 0, 0], 
    id: '0:0' 
}; 

tableGridCanvas.addEventListener('click', (event) => { 
    const keyList = Object.keys(cells); 
    const valueList = Object.values(cells); 

    for (let j = 0; j < valueList.length; j += 1) { 
        const current = [...cells[j]]; 
        const [x, y, width, height] = current; 
        
        let cursorX = event.pageX; 
        let cursorY = event.pageY; 

        if (x <= cursorX && cursorX <= x + width - 1 && y <= cursorY && cursorY <= y + height - 1) { 
            currentCell = { 
                dimensions: current, 
                id: keyList[j] 
            }); 
        } 
    } 
});

Conclusion

By using Canvas and, at the same time, rendering content limited to the size of the viewport and splitting the data into smaller sets, it is possible to quickly and efficiently work on large numbers of elements. What is more, extracting the cell logic outside Canvas enables reducing the load during the table generation process and thus significantly speeds up the operation of the entire app.

As a result, we are able to increase the amount of data presented in the app without overloading the user’s browser.

Originally published on LinkedIn.

back to publications