Sheetjs: Corrupt XLSX file after downloading

[nikolaifischer]

Hi,
I know there is already a very similar issue here but it does not provide a solution, because the author of this issue just uses a different approach to download the file in the end.
https://github.com/SheetJS/js-xlsx/issues/122

I'm trying to download a file which is generated in the nodejs backend from the angular frontend, but when I try to open it, it's always corrupted. Of course I have seen the example in the readme of this project and tried it, but it also came out corrupted. I can write the output to a file and then point the browser to it by using window.location() and the file is fine. Sadly I could not get this approach working with my authorization code.
I read somewhere that it's better to send buffer objects when working with expressjs, so here is my code right now:

// Backend
var wopts = { bookType:'xlsx', bookSST:false, type:'buffer' };

        var wbout = XLSX.write(workbook,wopts);

        res.writeHead(200, [['Content-Type',  'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet']]);
        res.end( wbout );

// Frontend
// This triggers the backend function:
 $http.get("/abrechnung/exportToExcel/" + JSON.stringify(pageAndFilter.filterByFields)).
             success(function(data, status, headers, config) {  
              // The data object is the buffer
        saveAs(new Blob([data],{type:"application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"}), "test.xlsx");
        }).
  error(function(data, status, headers, config) {
  });

Any help would be greatly appreciated!

[SheetJSDev]

@nikolaifischer Let's start by making sure the data on the backend is valid. After generating the workbook, can you write to some file on the server side (for example, fs.writeFileSync("test.xlsx", wbout)), copy it to your client computer and open it in Excel?

[nikolaifischer]

No, it's not corrupted when writing it to a file on the server. Strange!

[remi-lebigre]

Hello,

I have the same corruption issue when writing my document.
I am editing the cells according to the documentation, and my Excel files output can be read, but I get almost every time the corrupt message error.
Here is what i found : if i console log wbout, i get wrong characters that may the cause of the corruption

        var wopts = {bookType: 'xlsx', bookSST: true, type: 'binary', cellStyles: true};
        var wbout = XLSX.write(Email.workbook, wopts);

<PK �����T¥F^¡¾ÕY��Y�����docProps/core.xml
 <cp:coreProperties xmlns:cp="http://schemas.openxmlformats.org/package/2006/metadata/core-properties" xmlns:dc="http://purl.org/dc/elements/1.1/" xmlns:dcterms="http://purl.org/dc/terms/" xmlns:dcmitype="http://purl.org/dc/dcmitype/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"><dcterms:created xsi:type="dcterms:W3CDTF">2015-04-27T09:18:16Z</dcterms:created><dcterms:modified xsi:type="dcterms:W3CDTF">2015-04-27T14:42:06Z</dcterms:modified>
...

...
<a:font script="Jpan" typeface="ＭＳ Ｐゴシック"/>
...

<t>Unité</t></si><si><t>Quantité</t>

The same goes if i write in base64. The console log of atob(wbout) shoes the same wrong symbols

[nikolaifischer]

I think the problem is not the actual writing of the file but the sending to the client. When I open the downloaded file in a texteditor and compare it to the written on the server, I can see that they have different hex codes.
So, after all this might not be an Issue with js-xlsx but with expressjs or what ever. I read alot about disabeling the live-reload component but that did not do the trick for me.
Still, I would be very thankful for any help on this matter!

[nikolaifischer]

Ok. Sorry for the monologue but I solved it: In the frontend when making the GET Request, the Response Type has to be set to arraybuffer like this:

$http({method: 'GET', url: "/abrechnung/exportToExcel/" + JSON.stringify(pageAndFilter.filterByFields) ,
        headers: {'Content-Type': "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"}, responseType: "arraybuffer"}).             
             success(function(data, status, headers, config) {  
             // continue like before...

I think this is actually an issue in angularJS and has nothing to do with this project. Thank you anyways!

[SheetJSDev]

@nikolaifischer thanks for looking into this further! Similar issues have come up before. If you don't mind, can you put together a very short and simple example using Angular that we can reference later?

[nikolaifischer]

I hope this helps!
Feel free to use or edit it as you wish

// Backend:
// Be sure to write the workbook in the type:'buffer'
var wopts = { bookType:'xlsx', bookSST:false, type:'buffer' };
var wbout = XLSX.write(workbook,wopts);     
// Send the buffer:
res.end( wbout );

// AngularJS Frontend:
exportToExcel: function() {

    // You have to use http GET, otherwise browsers will not download the excel
    $http({method: 'GET', url: "/yourAPI/exportToExcel/",
        // This is important! Tell it to expect an arraybuffer
        responseType: "arraybuffer"}).             
        success(function(data, status, headers, config) {  
            // use the saveAs library to save the buffer as a blob on the user's machine. Use the correct MIME type!
            saveAs(new Blob([data],{type:"application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"}), "excel.xlsx");
        }).
        error(function(data, status, headers, config) {

        });  
}

[solidevolution]

Big thx for the responseType hint!

My long debug line ends in google. now I know much more about http ;)

[abhinavnigam2207]

keep the responseType as 'arraybuffer'. It will solve your problem.

$http({
        url: 'www.example-api-url.com/download',
        method: "GET",
        headers: {'Content-type': 'application/json'},
        data: {a:1,b:2},
        responseType: 'arraybuffer'
});

[unseen1980]

@nikolaifischer Thanks for posting the code snippet. Really helped me today 👍

[priyadarshini2111]

responseType: "arraybuffer" works...

[pablosistemas]

Working fine with ResponseType as arraybuffer in Angular 5 for a xlsx file. My final code:
return this.httpClient
.get(environment.apiUrl + 'Agenda/ObterPlanilhaExcel', {
params: params,
headers: new HttpHeaders()
.set('Content-type', 'application/json')
.set('Accept-Language', 'pt-BR,pt;q=0.8,en-US;q=0.5,en;q=0.3')
.set('Accept-Encoding', 'gzip, deflate'),
responseType: 'arraybuffer'
}).subscribe(
retorno => {
const blob = new Blob([retorno], {type: 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'});
const urlPlanilha = window.URL.createObjectURL(blob);
this.urlPlanilha = this.sanitizer.bypassSecurityTrustResourceUrl(urlPlanilha);
},
erro => this.errorHandler(erro)
);

[reviewher]

The angular 1.x and angular 2+ demos specifically show client-side workflows: FileReader to read data + client side export. Is a http client example necessary or is the info in this issue sufficient?

[dfloresgonz]

@pablosistemas What is this.sanitizer? and how does the file gets downloaded?

[reviewher]

@dfloresgonz it's using the Angular 2+ DomSanitizer service: https://angular.io/api/platform-browser/DomSanitizer

[damaimrs]

@nikolaifischer thanks for ur solution. it works for me! 👍 , im using axios and vuejs

[vahniteja]

@nikolaifischer it's not working, i'm using angularJS can you help me out
this is code written
$http({
method: 'GET',
url: 'jobOrder/downloadJobOrdersTemplate',
responseType: 'arraybuffer'
}).success(function (data, status, headers) {
var blob = new Blob([data], {
type: "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"
});
saveAs(blob, "excel.xlsx");
})
.error(function(data, status, headers, config){
if(status == constants.FORBIDDEN){
location.href = 'login.html';
}else{
$state.transitionTo("ErrorPage",{statusvalue : status});
}

});

I'm able to download the file but unable open it, because of the corruption in document

[arvindnagar15]

Hi Nikolaifischer,
This is not working for me. I am still getting a corrupted file. I am using reactjs and below is the piece of code :
server.js :
var wb = XLSX.utils.book_new();
wb.Props = {
Tittle : "SheetJs Tutorial",
Subject : "Test file",
Author : "Arvind Nagar"
};
wb.SheetNames.push("Test-Sheet");
var ws_data = [["hello", "world"]];
var ws = XLSX.utils.aoa_to_sheet(ws_data);
wb.Sheets["Test Sheet"] = ws;

var wbout = XLSX.write(wb, {bookType:'xlsx', type:'binary'});// if using  buffer, able to downlaod the file but no data. // rather than binary
console.log('wbout : ', wbout);
fs.writeFileSync("test.xlsx", wbout);
res.send(wbout);

client.jsx
axios({
method: 'get',
url: ${this.state.url}/exportAll,
headers: {'Content-Type': "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"},
responseType: "arraybuffer"
}).then((response) => {
var blob = new Blob([convetBinaryIntoOctet(response.data)], {type:'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'});
filesaver.saveAs(blob, "excel.xlsx");

    }).catch((response) => {
        console.error("CheckStatus handleSubmit() :: Error : ", response);
    })

function convetBinaryIntoOctet(data){
console.log('data : ', data);
var buf = new ArrayBuffer(data.length); //convert s to arrayBuffer
var view = new Uint8Array(buf); //create uint8array as viewer
for (var i=0; i console.log('inserting : ', data[i]);
view[i] = data.charCodeAt(i) & 0xFF; //convert to octet
}
return buf;
}
,
I have tried with both ways by passing binary and buffer as type in server.js, but no luck.

Any help would be appreciated... thanks.

[pablosistemas]

We've faced an issue that looks like the one you are fighting now. I
couldn't explain why this worked exactly, but in backend (.NETCore) we send
an FIleResultContent with the byte array from excel sheet and two
additional headers:

   HttpContext.Response.Headers.Add("Set-Cookie", "fileDownload=true;

path=/");
HttpContext.Response.Headers.Add("Cache-Control", "no-cache,
no-store, must-revalidate");
return File(excelbytearray, "application/vnd.ms-excel;",
"excelsheet.xlsx");

In the frontend (Angular 5), we received the HTTP response like this:
return this.httpClient
.get(environment.apiUrl + route, {
params: params,
headers: headers,
responseType: 'arraybuffer'
})
.map(responseObj => {
const blob = new Blob([responseObj], { type:
'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet' });
const urlExcelSheet = window.URL.createObjectURL(blob);
return this.sanitizer.bypassSecurityTrustResourceUrl(urlExcelSheet);
});
I hope it could help you!

Pablo Goulart Silva
Engenharia de Sistemas - Universidade Federal de Minas Gerais

Em qua, 2 de jan de 2019 às 03:08, indiarocks [e-mail]
escreveu:

Hi Nikolaifischer,
This is not working for me. I am still getting a corrupted file. I am
using reactjs and below is the piece of code :
server.js :
var wb = XLSX.utils.book_new();
wb.Props = {
Tittle : "SheetJs Tutorial",
Subject : "Test file",
Author : "Arvind Nagar"
};
wb.SheetNames.push("Test-Sheet");
var ws_data = [["hello", "world"]];
var ws = XLSX.utils.aoa_to_sheet(ws_data);
wb.Sheets["Test Sheet"] = ws;

var wbout = XLSX.write(wb, {bookType:'xlsx', type:'binary'});// if using buffer, able to downlaod the file but no data. // rather than binary
console.log('wbout : ', wbout);
fs.writeFileSync("test.xlsx", wbout);
res.send(wbout);

client.jsx
axios({
method: 'get',
url: ${this.state.url}/exportAll,
headers: {'Content-Type':
"application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"},
responseType: "arraybuffer"
}).then((response) => {
var blob = new Blob([convetBinaryIntoOctet(response.data)],
{type:'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'});
filesaver.saveAs(blob, "excel.xlsx");

}).catch((response) => {
    console.error("CheckStatus handleSubmit() :: Error : ", response);
})

function convetBinaryIntoOctet(data){
console.log('data : ', data);
var buf = new ArrayBuffer(data.length); //convert s to arrayBuffer
var view = new Uint8Array(buf); //create uint8array as viewer
for (var i=0; i console.log('inserting : ', data[i]);
view[i] = data.charCodeAt(i) & 0xFF; //convert to octet
}
return buf;
}
,
I have tried with both ways by passing binary and buffer as type in
server.js, but no luck.

[image: response]
https://user-images.githubusercontent.com/13284027/50580914-83894880-0e22-11e9-92a7-7b4edd201944.JPG

Any help would be appreciated... thanks.

—
You are receiving this because you were mentioned.
Reply to this email directly, view it on GitHub
https://github.com/SheetJS/js-xlsx/issues/217#issuecomment-450787980,
or mute the thread
https://github.com/notifications/unsubscribe-auth/AIQqiibsR4XbqZ2-q0olM1M0Nhd4ucGEks5u_D7pgaJpZM4ELgXU
.

[ItsTarik]

this was the trick responseType: 'arraybuffer' ! nice catch folks

[pablosistemas]

Cheers, man!

Pablo Goulart Silva
Engenharia de Sistemas - Universidade Federal de Minas Gerais

Em ter, 15 de jan de 2019 às 13:56, Tarik Lefi [e-mail]
escreveu:

this was the trick responseType: 'arraybuffer' !

—
You are receiving this because you were mentioned.
Reply to this email directly, view it on GitHub
https://github.com/SheetJS/js-xlsx/issues/217#issuecomment-454443837,
or mute the thread
https://github.com/notifications/unsubscribe-auth/AIQqigYv_4_Qv4Pt1C1qX27j7FUn2G1fks5vDfpHgaJpZM4ELgXU
.