How to export datatable with huge rows to excel file ? #70

cungtienkien · 2016-03-29T08:50:47Z

Dear,
I have a dataTable with 3288 rows, the export to excel is ok with dataTable arround 1000 rows but when the number of rows too many, it can't export to excel.
Please help me is there anyway to fix it?
Thank you.

hhurz · 2016-03-29T19:58:11Z

I can't reproduce your problem. Here is a working example for a table with 3288+1 ;) rows:

<!DOCTYPE html>
<html>

<head>
  <meta charset="UTF-8">
  <title>HTML table Export</title>
  <script type="text/javascript" src="http://code.jquery.com/jquery-latest.min.js"></script>
  <script type="text/javascript" src="../libs/FileSaver/FileSaver.min.js"></script>
  <script type="text/javascript" src="../tableExport.js"></script>
  <script type="text/javascript">
    $(document).ready(function() {
      var Table = document.getElementById ( 'container' );
      var T = [];
      var r = 0;
      var rmax = 3289;

      T.push('<table id="grid">');
      T.push('<thead>');
      T.push('<tr>');
      T.push('<th>col 1</th>');
      T.push('<th>col 2</th>');
      T.push('<th>col 3</th>');
      T.push('<th>col 4</th>');
      T.push('</tr>');
      T.push('</thead>');
      T.push('<tbody>');

      while (r++ < rmax) {
        T.push('<tr>');
        T.push('<td>' + r + '</td>');
        T.push('<td>' + getRandomInt(100,10000) + '</td>');
        T.push('<td>' + getRandomInt(100,10000) + '</td>');
        T.push('<td>' + getRandomInt(100,10000) + '</td>');
        T.push('</tr>');
      }

      T.push('</tbody>');
      T.push('</table>');

      Table.innerHTML = T.join ("");

      $('#export').click(function() {
        $('#grid').tableExport({type:'excel'});
      });
    });

    function getRandomInt(min, max) {
        return Math.floor(Math.random() * (max - min + 1)) + min;
    }
</script>

</head>
  <body>
    <button id="export">Export to Excel</button>
    <div id="container">
    </div>
  </body>
</html>

Are you using my fork of the plugin? I'm asking this, because you also opened the same issue here, which differs a lot from my version.

cungtienkien · 2016-03-31T03:30:50Z

Sorry,
Do you mean your tableExport.js still work normally with 3288+1 rows?
And i'm using your fork of the plugin.
Don't know why i can't export table with huge rows, or maybe because of the file's size is too big isn't it ??
Thank you for answering me

hhurz · 2016-03-31T09:02:49Z

That "3288+1" thing was just ironic. And yes my tableExport.js works with that amount of rows (and more) as the example above shows. You didn't tell anything about the content of your table. Without more details I only can speculate on the reasons for your problem.

cungtienkien · 2016-03-31T09:47:16Z

I think i found the reason, maybe it because of Chrome, cause when i use Firefox it can export nomarlly. Maybe cause Chrome limited file size or something...Can't sure

dontboyle · 2016-04-04T21:49:52Z

I'd like to way in here.

Chrome and Safari BOTH have an issue when exporting > 2.5 mb excel files.
The other browsers work great!

In my example:
Rows: 833
Cols: 26

dontboyle · 2016-04-05T14:22:09Z

Based on
http://stackoverflow.com/questions/16761927/aw-snap-when-data-uri-is-too-large
and
http://stackoverflow.com/questions/23301467/javascript-exporting-large-text-csv-file-crashes-google-chrome

Off this forum: astrojs/fitsjs#13
"This is an issue with Safari 5. Upgrading to Safari 6 will resolve this issue. Despite supporting Blobs, Safari 5 does not support the Blob constructor."

PS: Exl file in Other browsers fixed and corrected once I Used FileSaver plugin. ~"noob"

hhurz · 2016-04-05T21:12:57Z

Hi, first thank you for digging into this! The stackoverflow links gave me a hint why I didn't watch that problem. My mass data tests with chrome and firefox produced complete xls files larger than 2.5 mb. As you noticed in your last comment "Blob" is the solution.
As I wrote in the first lines of the Readme.md file Blobs will be used to save an export file if you include Filesaver.js. So do you have an include for this plugin in your sources? I think you do not, as your first suggestion for a fix proves. The downloadFile() function in tableExport.js will only be called if the creation of a Blob object fails. You find this behavior in line 419 of tableExport.js.
So please give FileSaver.js a try.

dontboyle · 2016-04-05T21:38:09Z

@hhurz - Thanks for responding!

So as you stated - the Filesaver.js does work!

However - unfortunately Safari 5.1.7 which is the latest Window release (deprecated now) will never work with BLOB. BLOB's exists but the BLOB constructor does not. I wasn't able to test in Safari > 5.1.7. And none of my findings have gotten 5.1.7 Safari to work otherwise (may be completely unsupportable)

Hope this info helps!

hhurz · 2016-04-06T11:39:17Z

That might sound a bit arrogant, but I don't intend to support Safari for Windows. The browser support is limited to those mentioned in the readme of Filesaver.js

ggobbe · 2016-04-27T11:46:13Z

@hhurz I have noticed the same problem on Google Chrome, the issue seems to be related with the amount of data that is being exported and not the amount of rows.

For example, I can export 1744 rows with a few columns in nearly no time (i.e. less than 10 seconds with the file size being a bit more than 1 Mb), then if I add an additional column that is containing lots of anchors, then the export takes much longer (i.e. more than 5 minutes) and at the end it just opens a new blank window instead of downloading the new file.

Edit : After further investigation, I think that this bit of code is limited to the browser maximum URL's length

var base64data = "base64," + $.base64.encode(excelFile);
window.open('data:application/vnd.ms-excel;filename=export.xls;' + base64data);

I haven't tested but something like this could probably solve the issue although I am not sure that the base64 encoding would still be needed in that case.

var newWindow = window.open();
newWindow.document.write('data:application/vnd.ms-excel;filename=export.xls;' + base64data);

hhurz · 2016-04-28T08:25:40Z

@ggobbe Thanks for digging into this. I'm confused a bit, but neither tabelExport.js nor FileSaver.js use window.open to save a file. So where did you find this call?

ggobbe · 2016-04-28T12:03:31Z

@hhurz Sorry I didn't notice but the project I have been debugging, that I didn't wrote, is using bootstrap-table which is using some sort of version of your library. After further investigation it turns out that it is in the bootstrap-table-export.js exportTable function that this code is whereas you are using Blob and saveAs() to save the file. Sorry for the confusion and thanks for your help I'll go raise an issue on that project.

wushuang5112 · 2016-12-05T09:22:00Z

@hhurz
thank you tell me the 70-issues, but I push the solution in 127-issues, why it can't relate with? in other words, the 127-issues can't see in this 70-issues page, why?

I found the *.csv code is ANSI, but i can tranform it to utf8, do you having another method?

hhurz added the Q&A label Apr 8, 2016

hhurz closed this as completed Jun 16, 2016

hhurz mentioned this issue Dec 5, 2016

Download the amount of data for the 10000 row *9 column *8 bytes download failed #127

Closed

hhurz mentioned this issue Jan 15, 2018

For csv type,what's the max recrods for tableExport? #218

Closed

hhurz mentioned this issue Oct 9, 2018

When I export more than 10000 pieces of data, it will be stuck and the browser will crash. How to solve this problem #250

Closed

hhurz mentioned this issue Jan 12, 2019

export html table with more then 1000 rows #256

Closed

hhurz mentioned this issue Mar 26, 2020

Export csv or xlsx file performance is so slowly via tableExport jquery plugin #291

Closed

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

How to export datatable with huge rows to excel file ? #70

How to export datatable with huge rows to excel file ? #70

cungtienkien commented Mar 29, 2016

hhurz commented Mar 29, 2016

cungtienkien commented Mar 31, 2016

hhurz commented Mar 31, 2016

cungtienkien commented Mar 31, 2016

dontboyle commented Apr 4, 2016

dontboyle commented Apr 5, 2016

hhurz commented Apr 5, 2016

dontboyle commented Apr 5, 2016

hhurz commented Apr 6, 2016

ggobbe commented Apr 27, 2016 •

edited

hhurz commented Apr 28, 2016

ggobbe commented Apr 28, 2016 •

edited

wushuang5112 commented Dec 5, 2016

How to export datatable with huge rows to excel file ? #70

How to export datatable with huge rows to excel file ? #70

Comments

cungtienkien commented Mar 29, 2016

hhurz commented Mar 29, 2016

cungtienkien commented Mar 31, 2016

hhurz commented Mar 31, 2016

cungtienkien commented Mar 31, 2016

dontboyle commented Apr 4, 2016

dontboyle commented Apr 5, 2016

hhurz commented Apr 5, 2016

dontboyle commented Apr 5, 2016

hhurz commented Apr 6, 2016

ggobbe commented Apr 27, 2016 • edited

hhurz commented Apr 28, 2016

ggobbe commented Apr 28, 2016 • edited

wushuang5112 commented Dec 5, 2016

ggobbe commented Apr 27, 2016 •

edited

ggobbe commented Apr 28, 2016 •

edited