How to trigger a Javascript function that changes a html table before exporting to excel?

Question

I have an html table that I am exporting to an excel file, which I can create, load and export to to a .xls file format.

Once I have finished adding values to the table through various SQL statements, I call up a javascript function that I use to check the table for null values, if the 'td' of the tables(respectively) have null values it enters a 0 to that 'td'.

Now the problem is when I call the function on window.onload after clicking on export to excel, on a different page, its like if it just loads the table without running the script and the excel file still has the empty(null) values in them.

But if I take out the code that writes the information to the excel file and just display it as a normal html page it calls the function and sets the null values to 0.

So my think is that the function is been left out completely and I don't know how to include it in the part where I write to the excel file.

Below is my code example of the javascript first and the code I use to write to the excel file:

    <script>
    window.onload = function change() {
    var count='0';
    var TDs=document.getElementsByTagName('td')
    var length=TDs.length;

        i='0';
        while(i<length){
        if(TDs[i].innerHTML==''){
        count++;
        TDs[i].innerHTML = "0";
        }
        i++;
        }
    }
        </script>
 <%   
    Response.Clear
            Response.Buffer = true
            Response.ContentType = "application/vnd.ms-excel"
            if ModuleName = "*ALL*" then
                Response.AddHeader "Content-disposition", "attachment;filename=" & CourseName & ".xls"
            else
                Response.AddHeader "Content-disposition", "attachment;filename=" & ModuleName & ".xls"
            end if
            Response.Charset = ""
    !!!the code for the creating of the tables is in between here!!!
            Response.End
%>

here is the table where I place the -1 and 0. *Note, this is in between 4 while loops and 3 other tables.

<tr>
                        <td width="50%"><font face="Arial" size="1"><%=rsUserAnswers("FileText")%></td>
                        <td width="50%"><font face="Arial" size="1"><%=rsUserAnswers("VoiceFile")%></td>
                        <%if rsUserModules1("LinkType") = "Submit Answer Multi" then
                        readyList1 = Split(rsUserModules1("LinkAction"),",")
                        %>
                            <td width="50%" style="font-family: Arial; font-size: x-small;">

                                <%For i = 0 TO UBound(readyList1) %>

                                    <%if InStr(readyList1(i),"A")>0 then%>
                                        <%output=rsUserAnswers("Score1")
                                            if output <> "-1" then %>
                                            0
                                            <%else%>
                                            <%=output%>
                                            <%end if%>
                                    <%end if%>

                                    <%if InStr(readyList1(i),"B")>0 then%>
                                    <%output=rsUserAnswers("Score2")
                                            if output<> "-1" then %>
                                            0
                                            <%else%>
                                            <%=output%>
                                            <%end if%>
                                    <%end if%>

                                    <%if InStr(readyList1(i),"C")>0 then%>
                                    <%output=rsUserAnswers("Score3")
                                            if output <> "-1" then %>
                                            0
                                            <%else%>
                                            <%=output%>
                                            <%end if%>
                                    <%end if%>

                                    <%if InStr(readyList1(i),"D")>0 then%>
                                    <%output=rsUserAnswers("Score4")
                                            if output <> "-1" then %>
                                            0
                                            <%else%>
                                            <%=output%>
                                            <%end if%> 
                                    <%end if%>

                                    <%if InStr(readyList1(i),"E")>0 then%>
                                    <%output=rsUserAnswers("Score5")
                                            if output <> "-1" then %>
                                            0
                                            <%else%>
                                            <%=output%>
                                            <%end if%>
                                    <%end if%>

                                    <%if InStr(readyList1(i),"F")>0 then%>
                                    <%output=rsUserAnswers("Score6")
                                            if output <> "-1" then %>
                                            0
                                            <%else%>
                                            <%=output%>
                                            <%end if%> 
                                    <%end if%>

                                    <%if InStr(readyList1(i),"G")>0 then%>
                                    <%output=rsUserAnswers("Score7")
                                            if output <> "-1" then %>
                                            0
                                            <%else%>
                                            <%=output%>
                                            <%end if%>
                                    <%end if%>

                                    <%if InStr(readyList1(i),"H")>0 then%>
                                        <%output=rsUserAnswers("Score8")
                                        if output <> "-1" then %>
                                            0
                                            <%else%>
                                            <%=output%>
                                            <%end if%>
                                    <%end if%>

                                <%next%>

                            </td>
                        <%else%>
                            <td width="50%"><font face="Arial" size="1"><%=rsUserAnswers("Score")%></td>
                        <%end if%>

                    </tr>

The rsUserAnswer(Score1,2,3,4,5,6,7,8) are individual columns created in a case statement in SQL.

the result I get from the above table is like so:

AnswerText    VoiceFile     Score
right           none          -1
wrong           none          'blank'
wrong           none          'blank'
right           none          -1

but that is not correct I need it like this:

AnswerText    VoiceFile     Score
right           none          -1
wrong           none           0
wrong           none           0
right           none          -1

but yours(@Shadow Wizard) just gives me the last entry like so:

AnswerText    VoiceFile     Score
right           none           0 <--- needs to be -1
wrong           none           0
wrong           none           0
right           none          -1

Answer 1

You can't.

You are sending the browser something you (falsely) claim is an Excel document.

The browser will see that you are sending it an Excel document and pass it on to Excel.

Excel will open it, discover it isn't an Excel document but recognise that it is an HTML document and try to convert it to an Excel document.

Excel will not execute JavaScript embedded in the HTML.

If you want to modify the HTML, then do it with server side code.

Answer 2

This might be more simple than you think.

There are only three instances where the table cell might end up empty, so in those three check for this server side and assign "0" in such case.

For this, first have such function:

<%
Function DefaultWhenEmpty(sValue, sDefaultValue)
    If IsNull(sValue) Or sValue="" Then
        DefaultWhenEmpty = sDefaultValue
    Else  
        DefaultWhenEmpty = sValue
    End If
End Function
%>

Then change those parts of the code to use the function:

<td width="50%"><font face="Arial" size="1"><%=DefaultWhenEmpty(rsUserAnswers("FileText"), "0")%></td>
<td width="50%"><font face="Arial" size="1"><%=DefaultWhenEmpty(rsUserAnswers("VoiceFile"), "0")%></td>
...
<td width="50%"><font face="Arial" size="1"><%=DefaultWhenEmpty(rsUserAnswers("Score"), "0"%></td>

This would cause cells to have "0" instead of just being blank.

Answer 3

I solved my question! And Thank you to Shadow Wizard

What I did was, instead of adding code to write the tables to excel using response, use a javascript itself to send the tables over to excel. My initial thinking was to load and render the HTML table first, then once it was done give the user a button to select to export(hence the javascript function provided in the link below).

So if you have jquery or javascript that exports your table to excel and you have empty or null values in your cells of your table or nested tables you can add this javascript using a body onload tag to execute the function to add zeros or the function Shadow Wizard gave me to default to a value you set if it is null or empty.

<script language="JavaScript">
    function change() {
    var count='0';
    var TDs=document.getElementsByTagName('td')
    var length=TDs.length;

        i='0';
        while(i<length){
        if(TDs[i].innerHTML==''){
        count++;
        TDs[i].innerHTML = "0";
        }
        i++;
        }
    }
</script>

and for an example of exporting to excel using javascript then follow this link.