Export to xlsx or xls from client side


#1

Hi guys…ive been struggling with that simple step into my app for 2 days now and either i am dumb or my english is at fault here but i cant seem to get over this little hurdle.

I use apollo client inside my app which fetch some data from the database and brings me an array of objects.I should be able to convert that array into an excel file to download but each of the solutions ive tried have failed.Using babyparse and filesaver js , i’ve been able to generate and download the file only to find it not formatted like a proper table.so ive tried js-xlsx,and many other meteor related wrappers but each one of them throws either the jszip error or just simply dont work.

the query ran by apollo can fetch up to 5000 or more rows making the app very slow to perform.r

For anyone who came across this kind of issue before, id like to solicitate a little hand here. How can i get from my array of objects a proper excel file ready to be downloaded and properly formatted in rows and columns without the commas showing like a txt file ? If i shouldnt handle it on the client how can i do it using Meteor methods call from the client ?

Thank y’all.


#2

I use https://github.com/rikkertkoppes/json2xls on the server. On the client, I form my query, send it to to a method, and return the output of json2xls. This is async, obviously. When I get the res, I do this:

      // Turn our binary result into a blob
      const buf = new ArrayBuffer(res.length);
      const view = new Uint8Array(buf);
      for (let i = 0; i != res.length; ++i) view[i] = res.charCodeAt(i) & 0xFF;
      const blob = new Blob([buf], {
        type: 'application/octet-stream'
      });

      // Automatically download the file by appending an a element,
      // 'clicking' it, and removing the element
      const a = window.document.createElement('a');
      a.href = window.URL.createObjectURL(blob, {
        type: 'data:attachment/xlsx'
      });
      a.download = 'yourFileName.xlsx';
      document.body.appendChild(a);
      a.click();
      document.body.removeChild(a);

My strong suggestion would be to try it with a hardcoded json block on the server first and ensure that your method and client-side stuff is working. Then convert your desired data into the json block that json2xls is looking for.


#3

I had errors exporting to xls until I just switched to CSV.


#4

Thank you for your quick answer. As im not in the office right now i cant really test it for now but if i understood well here is how i should proceed:

here i show code from my actual file on the client.

 <RaisedButton
                                    label="exporter pour excel" 
                                    labelColor="white"
                                    backgroundColor="#141e51"
                                    className="inAppBtnForm"
                                    onClick={()=>{
                                        if(fullEXL){//if array to export
                                            console.dir(fullEXL);
                                            let EXL=JSON.stringify(fullEXL);//Baby.unparse(JSON.stringify(fullEXL),{header:true});
                                            let filename=prompt("Veuillez entrez le npm du fichier");//entering filename as user wants it to be
                                            let xls=json2xls(EXL);//thanks to your answer i noticed that this is just wrong

                                            Meteor.call("exportToExcel",EXL,filename,(err,res)=>{
                                                if(res){
                                                    FileSaver.saveAs(res,[filename+'.xls']);
                                                }
                                            });
                                           
                                        }
                                    }}
                                    />

If you follow that piece of code above which is taken from the client,the meteor call should call a method implementing the same kind of code you’ve written above and create the automatic download right? So there is no need to use Filesaver js as i did?


#5

I think your file-saving implementation probably depends on your user base. It can be tricky in some browsers to force a download like that, and my implementation reflects what I’ve found worked best for the browsers we were testing against.

Also, I do the actual conversion using json2xls on the server. As you noted, when you get in to the thousands or tens of thousands of records, it’s pretty easy to lock the browser.


#6

Indeed i experimented it first hand this afternoon the browser was locked and i couldnt do a thing.Ok so ill use json2xls on the server inside the method and trigger the download from there like you did in the snippet above. One last question with your method where does the file download itself ? In the user’s(browser’s default) download folder or i should do something else ?

Thank you .


#7

Using that method should trigger a download in the browser, with the default settings.


#8

Thank you pal, you ve been a great help…ill let you know in a few hours by PM if i fixed my code.