3

I'm trying to export HTML table content to excel. I saw this solution which worked but not as I expected it (because I can't choose which columns to copy, and it doesn't works with big tables).

And another solution to copy by js and manually paste to excel file, which didn't work as well, and I don't really fancy this method.

Shortly what I want is, export customized view of the table, not all columns. to show you an example of what I mean:

Here is the normal table view:

enter image description here

and Here is how what I want to show in excel:

enter image description here

But because I have hidden fields, the first method didn't work:

enter image description here

I would like a client side, cross-browser, workaround/solution, considering that I have around 2,500 lines in the table.

Community
  • 1
  • 1
user2517028
  • 784
  • 1
  • 11
  • 25
  • 1
    I think it would be better if you build the excel sheet in server side , so you could customize it properly. – Mohamed Farrag Mar 04 '14 at 11:39
  • will you use any framework like struts,spring etc – Kanhu Bhol Mar 04 '14 at 11:39
  • @KanhuBhol Actually, I'm stuck with Classical ASP!! – user2517028 Mar 04 '14 at 11:42
  • @MohamedFarrag, that would be my last resort! – user2517028 Mar 04 '14 at 11:44
  • 1
    If you changed your mind, so I recommend to use Office Open https://excelpackage.codeplex.com/ it is a great tool to build excel sheet from scratch. – Mohamed Farrag Mar 04 '14 at 11:51
  • As far as I know, you've got to make the export happen on the server, because you've got to set MIME-type headers for your exported Excel document (which Javascript can't do). If you're using ASP, the following links might help you: http://stackoverflow.com/questions/440892/how-to-output-an-excel-xls-file-from-classic-asp http://www.crydust.be/blog/2009/03/02/generate-excel-files-in-asp-classic/ http://www.theukwebdesigncompany.com/articles/article.php?article=730 – Agi Hammerthief Mar 06 '14 at 11:54
  • Thank you! Though, I already have my server side excel generation now. I wanted the client side because the table has information from different queries which I don't want to invoke each time someone wants an excel file! by excel I mean either xsl or CSV ... – user2517028 Mar 06 '14 at 16:43
  • Have you tried converting the data to a csv? – Kami Mar 06 '14 at 16:46
  • I don't think, it would be a problem to convert the data to CSV, rather than how to save it to the clients' machines? – user2517028 Mar 06 '14 at 16:48

2 Answers2

1

I made something very similar and use it every day in my office following a tutorial found on the web. You can use this template for PHP:

<?
   $filename="sheet.xls";
   header ("Content-Type: application/vnd.ms-excel");
   header ("Content-Disposition: inline; filename=$filename");
?>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html lang=it><head>
<title>Titolo</title></head>
<body>
<table border="1">
<?
for ($i=1;$i < 11; $i++)
{
   echo "<tr>";
   for ($j=1; $j<11;$j++)
   {
      $a = $i * $j;
      echo "<td>$a</td>";
   }
   echo "</tr>";
}
?>
</table>
</body></html>

You can write something similar with JS, just generate a simple table with HTML tags and be sure to write the correct code in the header.

Nicolaesse
  • 2,554
  • 12
  • 46
  • 71
1
Excel Export Script works on IE7+ , Firefox and Chrome
===========================================================



function fnExcelReport()
    {
             var tab_text="<table border='2px'><tr bgcolor='#87AFC6'>";
             var textRange; var j=0;
          tab = document.getElementById('headerTable'); // id of table


          for(j = 0 ; j < tab.rows.length ; j++) 
          {     
                tab_text=tab_text+tab.rows[j].innerHTML+"</tr>";
                //tab_text=tab_text+"</tr>";
          }

          tab_text=tab_text+"</table>";
          tab_text= tab_text.replace(/<A[^>]*>|<\/A>/g, "");//remove if u want links in your table
          tab_text= tab_text.replace(/<img[^>]*>/gi,""); // remove if u want images in your table
                      tab_text= tab_text.replace(/<input[^>]*>|<\/input>/gi, ""); // reomves input params

               var ua = window.navigator.userAgent;
              var msie = ua.indexOf("MSIE "); 

                 if (msie > 0 || !!navigator.userAgent.match(/Trident.*rv\:11\./))      // If Internet Explorer
                    {
                           txtArea1.document.open("txt/html","replace");
                           txtArea1.document.write(tab_text);
                           txtArea1.document.close();
                           txtArea1.focus(); 
                            sa=txtArea1.document.execCommand("SaveAs",true,"Say Thanks to Sumit.xls");
                          }  
                  else                 //other browser not tested on IE 11
                      sa = window.open('data:application/vnd.ms-excel,' + encodeURIComponent(tab_text));  


                      return (sa);
                            }

    Just Create a blank iframe

        <iframe id="txtArea1" style="display:none"></iframe>

    Call this function on

        <button id="btnExport" onclick="fnExcelReport();"> EXPORT 
        </button>
sampopes
  • 2,646
  • 1
  • 22
  • 34