javascript excellentexport blob issue

2019-07-31 08:53发布

问题:

I have a web page that generates a report with about 1000 - 5000 records (14 columns). The code:

excel: function(anchor, table, name) {
        table = get(table);
        var ctx = {worksheet: name || 'Worksheet', table: table.innerHTML};
        var hrefvalue = uri.excel + base64(format(template.excel, ctx));
        anchor.href = hrefvalue;
        // Return true to allow the link to work
        return true;
    },

This works if the records are about 1500 but if its more than 2000, it doesnt. I saw this post how to export table as excel with 10000 to 40000 rows and tried to incorporate it from my code:

var ctx = {worksheet: name || 'Worksheet', table: table.innerHTML};

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

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;
}

I can now export records with more than 2000 records but when I open the spreadsheet, it also exports the menu, report buttons, textbox into the excel and the start of the report I need will be somewhere in row 150 with all the extra stuff that was exported.

Is there a way to remove the UIs being exported also in the excel?

回答1:

I'm just going to take a shoot in the dark - don't really know much about how your code works... get(), format(), template.excel is unknown to many that it's becomes harder for us to help you...

But Here is what i think you have to do:

excel: function(anchor, table, name) {
  table = get(table);
  var ctx = {worksheet: name || 'Worksheet', table: table.innerHTML};
  var data = format(template.excel, ctx)
  var blob = new Blob([data], {type: "application/vnd.ms-excel"})
  var url = URL.createObjectURL(blob)
  anchor.href = url;
  anchor.download = 'file.xlsx'
  // Return true to allow the link to work
  return true;
}

btw try to screw base64 completely - it's bad practice

Another thing i recommend is FileSaver.js