2

I am exporting html table as excel with javascript (window.location.href= uri + base64(tableData)) and this is working fine for 1500 rows. Because window.location.href has some limit string length. But i need to export 40000 number of rows.

And i have also tried with an anchor tag href. But it is also not working.

So can we assign the data partially to window.location.href or have any alternate solution?

Ajeet Shah
  • 18,551
  • 8
  • 57
  • 87
Rakesh Varma
  • 151
  • 1
  • 2
  • 14
  • i have also tried with anchor tag href. that also not working. So can we assign the data partially to window.location.href or alternate way? – Rakesh Varma Jun 03 '16 at 07:18
  • No way. That big Data you have to up- and download via post Data to and from a Server. – Steffomio Jun 03 '16 at 07:24
  • I can understand that window.location.href is GET request type. If i use as POST request that may be can handle more data. But here in my code is only a single page so there is JavaScript and Html page. How to send to server then. – Rakesh Varma Jun 03 '16 at 07:28
  • 10,000 rows x 2 columns will work as .csv file export in firefox. But 100,000 will not. – Paul Jun 03 '16 at 07:30
  • Tried this one, I have not -- perhaps it may function differently ? https://www.npmjs.com/package/export-to-excel – Paul Jun 03 '16 at 07:35
  • Thanks Paul. I know this href length is vary browser to browser and i am using Chrome. Here my answer is not concern in numer of rows. what happen if we have 300 rows and 5000 columns then also the base64 encoded code may cross the href length. I am asking, in case of large data can we assign the data partially to href or there is another way. – Rakesh Varma Jun 03 '16 at 07:35
  • Rakesh, you could split it into N files, which can each be exported from the browser. This may not be convenient for the end user, though, who must reassemble or find the one they need. – Paul Jun 03 '16 at 07:37
  • Exactly, and i need to export single file only. So is there no other way? – Rakesh Varma Jun 03 '16 at 07:40
  • This looks interesting, a way to create zip files in the browser: https://stuk.github.io/jszip/documentation/examples.html – Paul Jun 03 '16 at 07:41
  • and the output part is here: https://stuk.github.io/jszip/documentation/howto/write_zip.html – Paul Jun 03 '16 at 07:42
  • Paul, single file means, i need to download single excel file that have all the 40000 rows. – Rakesh Varma Jun 03 '16 at 07:44
  • use a BlobURL instead of a dataURL, they can handle many MB of data – dandavis Jun 03 '16 at 07:54
  • Thanks dandavis. Thats interesting. I dont know about it. Can you please send me links and anything. So i can implement. Please put suggestion to answer instead of comment. Thanks – Rakesh Varma Jun 03 '16 at 07:58

3 Answers3

6

I was assigning the encoded string to window.location.href, thats has limitation of size. It works for small data. For large data, we need to change this way, and i used Blob's, Thanks @dandavis. In Blob's there is no limit of data and we can export large size of files. My previous code is :

var ctx = {worksheet: name || 'Worksheet', table: table.innerHTML}
window.location.href = uri + base64(format(template, ctx))

And now updated code with passing blob is:

var blob = b64toBlob(str, "application/vnd.ms-excel");
var blobUrl = URL.createObjectURL(blob);
window.location = blobUrl;

And the b64toBlob function is here:

function b64toBlob(b64Data, contentType, sliceSize) {
    contentType = contentType || '';
    sliceSize = sliceSize || 512;

    var byteCharacters = atob(b64Data);
    var byteArrays = [];

    for (var offset = 0; offset < byteCharacters.length; offset += sliceSize) {
        var slice = byteCharacters.slice(offset, offset + sliceSize);

        var byteNumbers = new Array(slice.length);
        for (var i = 0; i < slice.length; i++) {
            byteNumbers[i] = slice.charCodeAt(i);
        }

        var byteArray = new Uint8Array(byteNumbers);

        byteArrays.push(byteArray);
    }

    var blob = new Blob(byteArrays, {type: contentType});
    return blob;
}

And its working very fine. I have tested with 55,000 number of rows. Thanks all

Endless
  • 34,080
  • 13
  • 108
  • 131
Rakesh Varma
  • 151
  • 1
  • 2
  • 14
3

One way to do that is to create a Blob out of your data, convert it to object URL and set result URL as an anchor href with download attribute set to a file name. An example:

const blob = new Blob(["{a:1,b:2}"])
const blobURL = window.URL.createObjectURL(blob)
document.querySelector('a').setAttribute('href', blobURL)
<!DOCTYPE html>
<html>
<head>
  <meta charset="utf-8">
  <meta name="viewport" content="width=device-width">
  <title>JS Bin</title>
</head>
<body>
<a download="example.json">Download</a>
</body>
</html>

You obviously should be concerned about whether user browser supports these features.

Update:

Looks like there's a library called excellentexport that does just that.

Klaster_1 Нет войне
  • 11,790
  • 9
  • 61
  • 73
  • Thanks Klaster for answer. So i have one more doubt to converting my table data into blob, it will compress data or some other concept. Because here i also tried this, in which i need data in Array but i have table text and encoding in base64 and assigning to window,location.href. – Rakesh Varma Jun 03 '16 at 07:51
  • How we create blob object to pass table text. I think it will not work. Please tell me if we can do something in it. – Rakesh Varma Jun 03 '16 at 07:52
  • Can you post an example of table data before it's converted to base64? Techincally, Blob should not limit what's stored inside, because it's binary. In fact, I think it's possible to generate a complete excel file, store it in blob and download. This is what you are trying to achieve, right? – Klaster_1 Нет войне Jun 03 '16 at 08:04
1

You can use the excellent FileSaver.js to save data generated on the client side that takes care of browser difference (new tab for safari, msSaveAs for ie/edge and the use of download attribute for those who support it)

saveAs(new Blob([tableData]), 'filename.csv')

There is no need to base64 encode...

Endless
  • 34,080
  • 13
  • 108
  • 131