locked
export html Table data to Excel Spread Sheet

    Question

  • I have a table in my app and I want to export its data to Excel Spread Sheet. I need a starting point that how can I implement this functionality in my app, when user presses the save button the data of that table transferred to Excel Sheet.

    samEE

    Sunday, July 13, 2014 10:25 AM

Answers

  • Hi samEE

    I cannot figure out how to export file directly from WinJS HTML since it could be a limit. The msSaveOrOpenBlob() does not work with WinJS.

    However to export to a CSV might be a good choice, CSV is a comma based data, in your another post, you use FileSaverPicker, I do think it is correct direction.

    I generate some code and should be works fine with saving file into CSV, I use some test data here but not your HTML, I think you may need to convert your HTML to kind of JSON format for saving:

                var data = [["name1", "city1", "some other info"], ["name2", "city2", "more info"]];
                var csvContent = "";
                data.forEach(function (infoArray, index) {
    
                    dataString = infoArray.join(",");
                    csvContent += index < infoArray.length ? dataString + "\n" : dataString;
    
                });
    
                var savePicker = new Windows.Storage.Pickers.FileSavePicker();
                savePicker.suggestedStartLocation = Windows.Storage.Pickers.PickerLocationId.musicLibrary;
                // Dropdown of file types the user can save the file as
                savePicker.fileTypeChoices.insert("Excel Format", [".csv"]);
                // Default file name if the user does not type one in or select a file to replace
                savePicker.suggestedFileName = "test.csv";
    
                savePicker.pickSaveFileAsync().then(function (file) {
                    if (file) {
                        // Prevent updates to the remote version of the file until we finish making changes and call CompleteUpdatesAsync.
                        Windows.Storage.CachedFileManager.deferUpdates(file);
                        // write to file
                        Windows.Storage.FileIO.writeTextAsync(file, csvContent).done(function () {
                            // Let Windows know that we're finished changing the file so the other app can update the remote version of the file.
                            // Completing updates may require Windows to ask for user input.
                            Windows.Storage.CachedFileManager.completeUpdatesAsync(file).done(function (updateStatus) {
                                if (updateStatus === Windows.Storage.Provider.FileUpdateStatus.complete) {
                                    WinJS.log && WinJS.log("File " + file.name + " was saved.", "sample", "status");
                                } else {
                                    WinJS.log && WinJS.log("File " + file.name + " couldn't be saved.", "sample", "status");
                                }
                            });
                        });
                    } else {
                        WinJS.log && WinJS.log("Operation cancelled.", "sample", "status");
                    }
                });
    Hope helps.

    --James


    <THE CONTENT IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, WHETHER EXPRESS OR IMPLIED>
    Thanks
    MSDN Community Support

    Please remember to "Mark as Answer" the responses that resolved your issue. It is a common way to recognize those who have helped you, and makes it easier for other visitors to find the resolution later.

    • Marked as answer by Sameel Nawaz Thursday, July 17, 2014 4:48 AM
    Thursday, July 17, 2014 3:12 AM
    Moderator
  • You may write your own convert code or find some third party stuff for help, the forum do not official offer a convert code. Use search engine to search how to convert HTML to JSON.

    And for the error, did you include base.js in your html file?

        <script src="//Microsoft.WinJS.2.0/js/base.js"></script>
        <script src="//Microsoft.WinJS.2.0/js/ui.js"></script>

    --James


    <THE CONTENT IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, WHETHER EXPRESS OR IMPLIED>
    Thanks
    MSDN Community Support

    Please remember to "Mark as Answer" the responses that resolved your issue. It is a common way to recognize those who have helped you, and makes it easier for other visitors to find the resolution later.

    • Marked as answer by Sameel Nawaz Thursday, July 17, 2014 4:48 AM
    Thursday, July 17, 2014 4:25 AM
    Moderator

All replies

  • Hi,

    I think that you can esealy create a CSV fil containing all your data and then ask the system to start Excel, specifying your csv file path.

    Or, You can write a real excel fil using custom components and Excel interop.

    Regards


    Kevin BEAUGRAND, Modis FRANCE
    Merci de bien vouloir "Marquer comme réponse", les réponses qui ont résolu votre problème.

    Sunday, July 13, 2014 11:03 AM
  • I am trying to implement the following code. But its not working. What changes I have to made?

    Here is the JavaScript Part..

    <script type="text/javascript">
            var tableToExcel = (function () {
                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]; }) }
                return function (table, name) {
                    if (!table.nodeType) table = document.getElementById(table)
                    var ctx = { worksheet: name || 'Worksheet', table: table.innerHTML }
                    window.location.href = uri + base64(format(template, ctx))
                };
            });
        </script>

    Here is the body Part..

    <body>
    
        <h1>tableToExcel</h1>
        <input type="button" onclick="tableToExcel('testTable', 'W3C Example Table')" value="Export to Excel">
    
        <table id="testTable" summary="Code page support in different versions of MS Windows." rules="groups" frame="hsides" border="2">
        <caption>Attendance Sheet
            </caption>
            <colgroup span="0" ></colgroup>
            <colgroup span="1" ></colgroup>
            
        <thead align="top">
            <tr>
                <th>Name</th>
                <th>Father's Name</th>
                <th>Roll No.</th>       
                <th>Attendance</th>       
            </tr>
        </thead>
        <tbody>
            <tr>
                <td>Abc</td>
                <td>XYZ</td>
                <td>123</td>
                <td>P</td>
            </tr>
            <tr>
                <td>abc</td>
                <td>xyz</td>
                <td>123</td>
                <td>A</td>
            </tr>
        </tbody>
        </table>
    
    </body>
    


    samEE

    Monday, July 14, 2014 5:18 AM
  • Hi samEE ,

    To correct something, Windows Store App does not support Excel interop API.

    And I would suggest you to add a header to the document:

    <meta http-equiv="content-type" content="text/plain; charset=UTF-8"/>

    --James


    <THE CONTENT IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, WHETHER EXPRESS OR IMPLIED>
    Thanks
    MSDN Community Support

    Please remember to "Mark as Answer" the responses that resolved your issue. It is a common way to recognize those who have helped you, and makes it easier for other visitors to find the resolution later.

    Wednesday, July 16, 2014 7:22 AM
    Moderator
  • Hi James..

    Thank you for your suggestion. 

    I have added the header still its not working. No operation is performed when I click the button


    samEE

    Wednesday, July 16, 2014 7:29 AM
  • What kind of operation do you need?

    I rewrite some of your javascript code and it does export the data:

        <script type="text/javascript">
            //var tableToExcel = (function () {
            //    var uri = 'data:application/vnd.ms-excel;base64,'
            //      , template = '<meta http-equiv="content-type" content="text/plain; charset=UTF-8"/><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]; }) }
            //    return function (table, name) {
            //        if (!table.nodeType) table = document.getElementById(table)
            //        var ctx = { worksheet: name || 'Worksheet', table: table.innerHTML }
            //        window.location.href = uri + base64(format(template, ctx))
            //    };
            //});
            var uri = 'data:application/vnd.ms-excel;base64,'
            , template = '<meta http-equiv="content-type" content="text/plain; charset=UTF-8"/><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]; }) }
    
            function tableToExcel(table, name) {
                if (!table.nodeType) table = document.getElementById(table)
                var ctx = { worksheet: name || 'Worksheet', table: table.innerHTML }
                window.location.href = uri + base64(format(template, ctx))
            }
        </script>

    --James


    <THE CONTENT IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, WHETHER EXPRESS OR IMPLIED>
    Thanks
    MSDN Community Support

    Please remember to "Mark as Answer" the responses that resolved your issue. It is a common way to recognize those who have helped you, and makes it easier for other visitors to find the resolution later.

    Wednesday, July 16, 2014 7:56 AM
    Moderator

  • This popup appears .. What is the solution to that as I have Ms-Excel Installed in my PC




    Wednesday, July 16, 2014 8:16 AM
  • That's expected, since your stream exported (I would prefer call it the link you navigate) from HTML page is kind of data, as you can see from the dialog message, cannot be successfully recognized.

    And I just did some research, the code you are using is not workable with IE which has been proved by many guys. Actually window.navigator.msSaveOrOpenBlob() is the correct one in IE, but not work with Windows Store App. For this reason I would not suggest you to use these code.

    I will update you later for how to export excel.

    --James


    <THE CONTENT IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, WHETHER EXPRESS OR IMPLIED>
    Thanks
    MSDN Community Support

    Please remember to "Mark as Answer" the responses that resolved your issue. It is a common way to recognize those who have helped you, and makes it easier for other visitors to find the resolution later.

    Wednesday, July 16, 2014 10:05 AM
    Moderator
  • I am also trying to convert it into .csv file using File Save Picker .The link of which is given below

    http://social.msdn.microsoft.com/Forums/en-US/96d8f280-5b1e-4113-ac2e-9c2cb8a51adb/how-to-save-html-table-data-in-csv-file-using-filesavepicker?forum=winappswithhtml5

    help in any case will be appreciated by me thank you sir. 


    samEE

    Wednesday, July 16, 2014 10:11 AM
  • Hi samEE

    I cannot figure out how to export file directly from WinJS HTML since it could be a limit. The msSaveOrOpenBlob() does not work with WinJS.

    However to export to a CSV might be a good choice, CSV is a comma based data, in your another post, you use FileSaverPicker, I do think it is correct direction.

    I generate some code and should be works fine with saving file into CSV, I use some test data here but not your HTML, I think you may need to convert your HTML to kind of JSON format for saving:

                var data = [["name1", "city1", "some other info"], ["name2", "city2", "more info"]];
                var csvContent = "";
                data.forEach(function (infoArray, index) {
    
                    dataString = infoArray.join(",");
                    csvContent += index < infoArray.length ? dataString + "\n" : dataString;
    
                });
    
                var savePicker = new Windows.Storage.Pickers.FileSavePicker();
                savePicker.suggestedStartLocation = Windows.Storage.Pickers.PickerLocationId.musicLibrary;
                // Dropdown of file types the user can save the file as
                savePicker.fileTypeChoices.insert("Excel Format", [".csv"]);
                // Default file name if the user does not type one in or select a file to replace
                savePicker.suggestedFileName = "test.csv";
    
                savePicker.pickSaveFileAsync().then(function (file) {
                    if (file) {
                        // Prevent updates to the remote version of the file until we finish making changes and call CompleteUpdatesAsync.
                        Windows.Storage.CachedFileManager.deferUpdates(file);
                        // write to file
                        Windows.Storage.FileIO.writeTextAsync(file, csvContent).done(function () {
                            // Let Windows know that we're finished changing the file so the other app can update the remote version of the file.
                            // Completing updates may require Windows to ask for user input.
                            Windows.Storage.CachedFileManager.completeUpdatesAsync(file).done(function (updateStatus) {
                                if (updateStatus === Windows.Storage.Provider.FileUpdateStatus.complete) {
                                    WinJS.log && WinJS.log("File " + file.name + " was saved.", "sample", "status");
                                } else {
                                    WinJS.log && WinJS.log("File " + file.name + " couldn't be saved.", "sample", "status");
                                }
                            });
                        });
                    } else {
                        WinJS.log && WinJS.log("Operation cancelled.", "sample", "status");
                    }
                });
    Hope helps.

    --James


    <THE CONTENT IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, WHETHER EXPRESS OR IMPLIED>
    Thanks
    MSDN Community Support

    Please remember to "Mark as Answer" the responses that resolved your issue. It is a common way to recognize those who have helped you, and makes it easier for other visitors to find the resolution later.

    • Marked as answer by Sameel Nawaz Thursday, July 17, 2014 4:48 AM
    Thursday, July 17, 2014 3:12 AM
    Moderator
  • This Exception occurred .. And how can I convert my table data into json

    samEE

    Thursday, July 17, 2014 3:35 AM
  • You may write your own convert code or find some third party stuff for help, the forum do not official offer a convert code. Use search engine to search how to convert HTML to JSON.

    And for the error, did you include base.js in your html file?

        <script src="//Microsoft.WinJS.2.0/js/base.js"></script>
        <script src="//Microsoft.WinJS.2.0/js/ui.js"></script>

    --James


    <THE CONTENT IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, WHETHER EXPRESS OR IMPLIED>
    Thanks
    MSDN Community Support

    Please remember to "Mark as Answer" the responses that resolved your issue. It is a common way to recognize those who have helped you, and makes it easier for other visitors to find the resolution later.

    • Marked as answer by Sameel Nawaz Thursday, July 17, 2014 4:48 AM
    Thursday, July 17, 2014 4:25 AM
    Moderator
  • Ok I will try to convert it into Json . Thankx a lot sir

    samEE

    Thursday, July 17, 2014 4:46 AM