none
How to export multiple html table contents into excel multiple sheets - Using Javascript

    Question

  • I am trying to export multiple hidden tables from a webpage to an Excel workbook with one worksheet per table.

    Currently I am using the below html tables (['tbl1','tbl2']) and javascript function, code works to generate excelsheet but only the first table content is being exported instead exporting 'tbl1' data into sheet1 and 'tbl2' data into sheet2.

    <table id="tbl1">
                    <thead>
                        <tr hidden="" style="height:100px"> <th colspan="8"></th></tr>
                        <tr><td colspan="3" style="text-align:center" >Test 1</td></tr>
                    </thead>
                    <tbody>
                        <tr><td>Test 1</td></tr>
                    </tbody>
                    <tfoot>
                        <tr><td colspan="8" style="background-color:#014f7c; align-content:center; vertical-align:middle;"> Test 1 </td></tr>
                    </tfoot>
                </table>
                
    <table id="tbl2">
                    <thead>
                        <tr hidden="" style="height:100px"> <th colspan="8"></th></tr>
                        <tr><td colspan="3" style="text-align:center" >Test 2</td></tr>
                    </thead>
                    <tbody>
                        <tr><td>Test 2</td></tr>
                    </tbody>
                    <tfoot>
                        <tr><td colspan="8" style="background-color:#014f7c; align-content:center; vertical-align:middle;"> Test 2 </td></tr>
                    </tfoot>
                </table>    

    <button onclick="myFunction(['tbl1','tbl2'])">Export to Multiple Excel Sheets</button>
    <script type="text/javascript">
        function myFunction(tables) {
            var uri = 'data:application/vnd.ms-excel;base64,',
            template = '<html xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns="http://www.w3.org/TR/REC-html40"><head><!--[if gte mso 9]><xml><x:ExcelWorkbook><x:ExcelWorksheets><x:ExcelWorksheet><x:Name>{worksheet}</x:Name><x:WorksheetOptions><x:DisplayGridlines/></x:WorksheetOptions></x:ExcelWorksheet></x:ExcelWorksheets></x:ExcelWorkbook></xml><![endif]--></head><body><table>{table}</table></body></html>',
            base64 = function (s) { return window.btoa(unescape(encodeURIComponent(s))); },
            format = function (s, c) { return s.replace(/{(\w+)}/g, function (m, p) { return c[p]; }) };    
            
            var worksheetsData = "";        
            
            for (var i = 0; i < tables.length; i++) {
                var table = document.getElementById(tables[i]);
                
                var dataValue = table.outerHTML;
                
                ctx = { worksheet: 'sheet' + i, table: dataValue };                        
                worksheetsData += format(template, ctx);
            }

            var link = document.createElement("A");
            link.href = uri + base64(worksheetsData);
            link.download = 'ExportData' || 'Workbook.xls';
            link.target = '_blank';
            document.body.appendChild(link);
            link.click();
            document.body.removeChild(link);
        }
    </script>

    Friday, April 26, 2019 6:55 PM

All replies

  • You won't be able to do this with just JavaScript.  The reason it works for 1 table is because Excel can figure that out (and probably gives you an error message stating the format of the file doesn't match the extension).  Use one of the free libraries out there to generate the Excel workbook on the server and download it to the client.

    https://github.com/tonyqus/npoi

    https://github.com/JanKallman/EPPlus

    Friday, April 26, 2019 7:09 PM
  • I am trying to achieve the same. Buf I can’t use any third party libraries. Is there any other way to fix it.
    Saturday, April 27, 2019 2:34 AM
  • Hi MohamedS85,

    Looks like JavaScript is not a proper way to fulfill this requirement. I suggest you to check Open XML for Excel library.

    With the help of that you can create a Workbooks on server side and than you can download it on local machine.

    References:

    (1) Welcome to the Open XML SDK 2.5 for Office

    (2) Spreadsheets (Open XML SDK)

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, April 29, 2019 5:25 AM
    Moderator