Thursday 26 November 2020

Client Side export csv or xlsx

 CSV or XSLX export on client requires a lot of memory, it must uses BLOB(Binary large object) to avoid browser crash.


Blob means “Binary Large Object” and it’s an opaque representation of a chunk of bytes. Web Browsers implement a Blob object, which is responsible for holding data.

https://medium.com/javascript-in-plain-english/javascript-blob-why-is-it-useful-20c372dfca00


BLOB size and implementation  is different via browser :

CHROME :

Blobs are created in a renderer process, where their data is temporarily held for the browser (while Javascript execution can continue). When the browser has enough memory quota for the blob, it requests the data from the renderer. All blob data is transported from the renderer to the browser. Once complete, any pending reads for the blob are allowed to complete. Blobs can be huge (GBs), so quota is necessary.

If the in-memory space for blobs is getting full, or a new blob is too large to be in-memory, then the blob system uses the disk. This can either be paging old blobs to disk, or saving the new too-large blob straight to disk.

https://chromium.googlesource.com/chromium/src/+/master/storage/browser/blob/README.md#:~:text=Blobs%20are%20created%20in%20a,the%20renderer%20to%20the%20browser.



Export csv using JS and BLOB:

From array of arrays input [ [header1, header2,header3], [data1,data2,data3]] (Rows)

exportToCsv(filename: string, rows) {

    if (!rows || !rows.length) {

        return;

    }


    const separator = ',';

    const keys = Object.keys(rows[0]);


    const csvContent =

        rows.map(row => {

            return keys.map(k => {

                let cell = row[k] === null || row[k] === undefined ? '' : row[k];

                cell = cell instanceof Date

                    ? cell.toLocaleString()

                    : cell.toString().replace(/"/g, '""');

                if (cell.search(/("|,|\n)/g) >= 0) {

                    cell = `"${cell}"`;

                }

                return cell;

            }).join(separator);

        }).join('\n');

    const blob = new Blob([csvContent], { type: 'text/csv;charset=utf-8;' });


    if (navigator.msSaveBlob) { // IE 10+

        navigator.msSaveBlob(blob, filename);

    } else {

        const link = document.createElement('a');

        if (link.download !== undefined) {

            // Browsers that support HTML5 download attribute

            const url = URL.createObjectURL(blob);

            link.setAttribute('href', url);

            link.setAttribute('download', filename);

            link.style.visibility = 'hidden';

            document.body.appendChild(link);

            link.click();

            document.body.removeChild(link);

        }

    }

}


The join() method returns the array as a string.

The elements will be separated by a specified separator. The default separator is comma (,).



Export array to xlsx is more diffcult(convert string to utf-16 binary needed), can be done using sheetjs plugin as done below:

https://redstapler.co/sheetjs-tutorial-create-xlsx/

https://github.com/SheetJS/sheetjs/blob/master/xlsx.js


Creating a Workbook

Now let’s start with creating a new workbook by calling book_new() utility function which will return an empty workbook object.

var wb = XLSX.utils.book_new();

You can update the workbook properties such as title, subject, author with wb.Props.


wb.Props = {
Title: "SheetJS Tutorial",
Subject: "Test",
Author: "Red Stapler",
CreatedDate: new Date(2017,12,19)
};

Now we have the workbook, the next step is to create a worksheet and add it to the workbook. First, you’ll need to assign a new sheet name and push it to the SheetNames array.

wb.SheetNames.push("Test Sheet");

Then, for the content inside the sheet, you have several options. You have create a sheet from array of array, JSON or html table. For this tutorial, I’m going to use array of array. The structure is quite straightforward. Each array represent the row data and the members are the cell content.

var ws_data = [['hello' , 'world']]; //a row with 2 columns

Now create the sheet from this array by using aoa_to_sheet()

var ws = XLSX.utils.aoa_to_sheet(ws_data);

And assign the sheet object to the workbook Sheets array.

wb.Sheets["Test Sheet"] = ws;

Congratulation, now you have created a workbook and a worksheet with first row of data. The next step is to generate an xlsx file.

Exporting Workbook for Download

We need to export the workbook as xlsx binary. Use write function then pass the bookType as xlsx and output Type as binary

var wbout = XLSX.write(wb, {bookType:'xlsx', type: 'binary'});

We now have our xlsx binary data on wbout var. However, the correct content type for excel file is octet stream so you’ll need to convert the binary data into octet. We can achieve that by using arrayBuffer, UInt8Array and bit operation like this.

function s2ab(s) {
var buf = new ArrayBuffer(s.length); //convert s to arrayBuffer
var view = new Uint8Array(buf); //create uint8array as viewer
for (var i=0; i<s.length; i++) view[i] = s.charCodeAt(i) & 0xFF; //convert to octet
return buf;
}

We’re going to utilize Filesaver.js and Blob to handle the file saving for cross browser support. Use saveAs() function and create a new Blob object from octet array. Set the content type as octet-stream. follow by excel file naming that you would like.

$("#button-a").click(function(){
saveAs(new Blob([s2ab(wbout)],{type:"application/octet-stream"}), 'test.xlsx');
});

We’re ready; Let’s test it!

No comments:

Post a Comment