Exporting HTML table into excel using javascript

Question

I have table with 4 columns where every column includes text field and button and at the end of every row consists of edit and delete button. I want to export the table into excel format but when I do the text field and button at the column header and edit and delete button are also getting exported into excel file which I dont want. Can any one tell me where I am making the mistake in javascript, please.

Here is my jquery code which I got it from net (http://jsfiddle.net/insin/cmewv/)

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

my HTML code as follows

<TABLE  id="table_id" class="display" align="Center" border="1px" width="80%">
        <thead>
        <tr>
        <th> <b>User_ID </th></b>
        <form action="SearchId" method="post">
        <input type="hidden" name="hiddenname" value="hidden_uid" >
        <input type="text" name="uid" id="uid">
        <input type="submit" value="Search">
        </form>

        <th><b>User_Name </th></b>
        <form action="SearchId" method="post">
        <input type="text" name="uname" id="uname">
        <input type="hidden" name="hiddenname" value="hidden_uname" >
        <input type="submit"  value="Search">
        </form>

        <th><b>Password</th></b>
        <form action="SearchId" method="post">
        <input type="text" name="pass" id="pass">
        <input type="hidden" name="hiddenname" value="hidden_pass" >
        <input type="submit" value="Search">
        </form>

        <th><b>Designation</th></b>
        <form action="SearchId" method="post">
        <input type="text" name="desig" id="desig">
        <input type="hidden" name="hiddenname" value="hidden_desig" >
        <input type="submit" value="Search">
        </form>
        </thead>

        <tbody >
        <%Iterator itr;%>
        <%List data=(List) request.getAttribute("UserData");
        for(itr=data.iterator();itr.hasNext();)
        {%>
            <tr>
                <% String s= (String) itr.next(); %> 
                <td><%=s %></td>
                <td><%=itr.next() %></td>
                <td><%=itr.next() %></td>
                <td><%=itr.next() %></td>

                <form id="edit" action="EditRecord" method="post" >
                <td><input type="hidden" name="hidden_edit" id="edit_id" value="<%=s %>"/> 
                <input type="submit" id="myButton" value="Edit" name="edit" onclick="toggleVisibility('');"> </td>
                </form>

                <td><form id="delete" action="DeleteRecord" method="post" >
                <td><input type="hidden" name="hidden_delete" id="delete_id" value="<%=s %>"/>
                <input type="submit" value="delete" name="delete"> </td>
                </form></td>    
        <%} %>
            </tr>   

            </tbody>
    </TABLE>

that code is really hard to look at. Can you clean it up? What are all these floating commas, ','? Are those supposed to be ';'? You should terminate javascript statements with semi-colons, ';'. — akgill
– akgill, Commented May 3, 2014 at 22:32
@akgill Thanks for your reply. i got this code from net. this is the link "jsfiddle.net/insin/cmewv". It works absolutely fine except it also exports buttons and tex filed..How I can eliminate this..guide me please — Dinesh
– Dinesh, Commented May 3, 2014 at 22:37
Your code grabs the entire innerHTML for your table, so of course it shows up in Excel with the same content (at least as linked by the different display in Excel). If you don't want that content in your Excel file, then you need either to create a separate table without that content, or delete that content before creating the Excel export. — Tim Williams
– Tim Williams, Commented May 4, 2014 at 5:34

Samuel · Accepted Answer · 2014-05-04 17:56:49Z

Try set tag that have a buttons and a edit text with a class to indentify this dom´s ex.: Class='ignore'. After this clone this yout table and in table cloned remove all dom's that have class that you seted. After removed this dom´s pass the cloned table to function that will export to excel.

Edit -------

Try this.

js script

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 cln=table.cloneNode(true);
            var paras = cln.getElementsByClassName('ignore');

            while(paras[0]) {
                paras[0].parentNode.removeChild(paras[0]);
            }
            var ctx = {
                worksheet : name || 'Worksheet',
                table : cln.innerHTML
            }
            window.location.href = uri + base64(format(template, ctx))
        }
    })();

HTML example

<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>CODE-PAGE SUPPORT IN MICROSOFT WINDOWS</caption>
        <colgroup align="center"></colgroup>
        <colgroup align="left"></colgroup>
        <colgroup span="2" align="center"></colgroup>
        <colgroup span="3" align="center"></colgroup>
        <thead valign="top">
            <tr>
                <th>Code-Page<br>ID
                </th>
                <th>Name</th>
                <th>ACP</th>
                <th>OEMCP</th>
                <th>Windows<br>NT 3.1
                </th>
                <th>Windows<br>NT 3.51
                </th>
                <th>Windows<br>95
                </th>
            </tr>
        </thead>
        <tbody>
            <tr>
                <td>1200</td>
                <td style="background-color: #00f; color: #fff">Unicode (BMP of
                    ISO/IEC-10646)</td>
                <td></td>
                <td></td>
                <td>X</td>
                <td>X</td>
                <td>*</td>
            </tr>
            <tr>
                <td>1250</td>
                <td style="font-weight: bold">Windows 3.1 Eastern European</td>
                <td>X</td>
                <td></td>
                <td>X</td>
                <td>X</td>
                <td>X</td>
            </tr>
            <tr>
                <td>1251</td>
                <td>Windows 3.1 Cyrillic</td>
                <td>X</td>
                <td></td>
                <td>X</td>
                <td>X</td>
                <td>X</td>
            </tr>
            <tr>
                <td>1252</td>
                <td>Windows 3.1 US (ANSI)</td>
                <td>X</td>
                <td></td>
                <td>X</td>
                <td>X</td>
                <td>X</td>
            </tr>
            <tr>
                <td>1253</td>
                <td>Windows 3.1 Greek</td>
                <td>X</td>
                <td></td>
                <td>X</td>
                <td>X</td>
                <td>X</td>
            </tr>
            <tr>
                <td>1254</td>
                <td>Windows 3.1 Turkish</td>
                <td>X</td>
                <td></td>
                <td>X</td>
                <td>X</td>
                <td>X</td>
            </tr>
            <tr>
                <td>1255</td>
                <td>Hebrew</td>
                <td>X</td>
                <td></td>
                <td></td>
                <td></td>
                <td>X</td>
            </tr>
            <tr>
                <td>1256</td>
                <td>Arabic</td>
                <td>X</td>
                <td></td>
                <td></td>
                <td></td>
                <td>X</td>
            </tr>
            <tr>
                <td>1257</td>
                <td>Baltic</td>
                <td>X</td>
                <td></td>
                <td></td>
                <td></td>
                <td>X</td>
            </tr>
            <tr>
                <td>1361</td>
                <td>Korean (Johab)</td>
                <td>X</td>
                <td></td>
                <td></td>
                <td>**</td>
                <td>X</td>
            </tr>
        </tbody>
        <tbody>
            <tr>
                <td>437</td>
                <td>MS-DOS United States</td>
                <td></td>
                <td>X</td>
                <td>X</td>
                <td>X</td>
                <td>X</td>
                <td class="ignore"><button>teste</button></td>
            </tr>
            <tr>
                <td>708</td>
                <td>Arabic (ASMO 708)</td>
                <td></td>
                <td>X</td>
                <td></td>
                <td></td>
                <td>X</td>
                <td class="ignore"><button>teste</button></td>
            </tr>
            <tr>
                <td>709</td>
                <td>Arabic (ASMO 449+, BCON V4)</td>
                <td></td>
                <td>X</td>
                <td></td>
                <td></td>
                <td>X</td>
                <td class="ignore"><button>teste</button></td>
            </tr>
            <tr>
                <td>710</td>
                <td>Arabic (Transparent Arabic)</td>
                <td></td>
                <td>X</td>
                <td></td>
                <td></td>
                <td>X</td>
                <td class="ignore"><button>teste</button></td>
            </tr>
            <tr>
                <td>720</td>
                <td>Arabic (Transparent ASMO)</td>
                <td></td>
                <td>X</td>
                <td></td>
                <td></td>
                <td>X</td>
                <td class="ignore"><button>teste</button></td>
            </tr>
        </tbody>
    </table>

i am all so using this in my application but it is not working in my system i am using ubuntu
it works in one ubuntu system but not works in another system how can i find reason for that
To help you I need more information. What browser are you using? There are any message in browser console? Can you use browser's develop tools to debug the code and show me where are the error?
@Samuel I am curious, Do we need to pass only tables into the function? I am trying this logic out with Divs instead but it does not retain my formatting, any suggestions on what might be the reason and how to fix that?

Collectives™ on Stack Overflow

Exporting HTML table into excel using javascript

1 Answer 1

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related