Export script to Excel

Question

Export script to Excel

Navigation

#1 by (1 votes)

0

Hello, I have this script, I would like to know how I can modify the title of the file that it generates.

<script type="text/javascript">

 function fnExcelReport()
{
    var tab_text="<table border='2px'><tr bgcolor='#87AFC6'>";
    var textRange; var j=0;
    tab = document.getElementById('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 href[^>]*>|<\/a>/g, "");
    tab_text= tab_text.replace(/<img[^>]*>/gi,""); 
    tab_text= tab_text.replace(/<input[^>]*>|<\/input>/gi, ""); 
 
   
    var ua = window.navigator.userAgent;
    var msie = ua.indexOf("MSIE "); 

    if (msie > 0 || !!navigator.userAgent.match(/Trident.*rv\:11\./))      
    {
        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                 
        sa = window.open('data:application/vnd.ms-excel,' + encodeURIComponent(tab_text));  

    return (sa);
}
</script>

javascript

asked by Iron Man 13.03.2018 в 12:56

source

1 answer

Do not insert duplicate data Return image in array bytes format from URL on the internet?

score 1 · Accepted Answer

Instead of using window.open or putting the content in an iframe in the Internet Explorer old school style, it is better to assign the contents of the table to a a element whose attribute href would declare as:

link.href='data:application/vnd.ms-excel,' + encodeURIComponent(tab_text);

If to that same link you give an attribute download that attribute will cause a file to be downloaded with the name of the attribute:

link.download = 'archivo_ejemplo.xls';

I leave the snippet running

 function fnExcelReport()
{

    var tab_text="<table border='2px'><tr bgcolor='#87AFC6'>";
    var textRange; var j=0;
    tab = document.getElementById('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 href[^>]*>|<\/a>/g, "");
    tab_text= tab_text.replace(/<img[^>]*>/gi,""); 
    tab_text= tab_text.replace(/<input[^>]*>|<\/input>/gi, ""); 
 
    var link = document.getElementById('link'),
        nombre = document.getElementById('nombre');
    
    link.href='data:application/vnd.ms-excel,' + encodeURIComponent(tab_text);
    link.download=nombre.value;
    link.click();
    
    
}

#table {
  border: 1px solid;
  border-collapse: collapse;
  border-spacing: 0;
}
#table tr,
#table td {
  border: 1px solid;
  padding:2px;
}

<input type="text" id="nombre" value="miarchivo.xls"/>
<button id="descarga" onclick="fnExcelReport()">Descargar</button>
<a href="#" id="link" style="display:none"></a>
<table id="table"  style="width:100%">
        <thead>
            <tr>
                <th>Name</th>
                <th>Position</th>
                <th>Office</th>
                <th>Age</th>
                <th>Start date</th>
                <th>Salary</th>
            </tr>
        </thead>
        <tbody>
            <tr>
                <td>Tiger Nixon</td>
                <td>System Architect</td>
                <td>Edinburgh</td>
                <td>61</td>
                <td>2011/04/25</td>
                <td>$320,800</td>
            </tr>
            <tr>
                <td>Garrett Winters</td>
                <td>Accountant</td>
                <td>Tokyo</td>
                <td>63</td>
                <td>2011/07/25</td>
                <td>$170,750</td>
            </tr>
            <tr>
                <td>Ashton Cox</td>
                <td>Junior Technical Author</td>
                <td>San Francisco</td>
                <td>66</td>
                <td>2009/01/12</td>
                <td>$86,000</td>
            </tr>
            <tr>
                <td>Cedric Kelly</td>
                <td>Senior Javascript Developer</td>
                <td>Edinburgh</td>
                <td>22</td>
                <td>2012/03/29</td>
                <td>$433,060</td>
            </tr>
            <tr>
                <td>Airi Satou</td>
                <td>Accountant</td>
                <td>Tokyo</td>
                <td>33</td>
                <td>2008/11/28</td>
                <td>$162,700</td>
            </tr>
</table>

EDIT

If you have problems with the accents and the ñ (or other diacritics of UTF-8) it does not help to use encodeURIComponent since this does not use UTF-8 but UCS-2 . In this case you could use:

link.href='data:application/vnd.ms-excel;base64,' + window.btoa(tab_text);

But I have not tried it in all browsers. The following snippet works for me at least in chrome and firefox:

function fnExcelReport()
{

    var tab_text="<table border='2px'><tr bgcolor='#87AFC6'>";
    var textRange; var j=0;
    tab = document.getElementById('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 href[^>]*>|<\/a>/g, "");
    tab_text= tab_text.replace(/<img[^>]*>/gi,""); 
    tab_text= tab_text.replace(/<input[^>]*>|<\/input>/gi, ""); 
 
    var link = document.getElementById('link'),
        nombre = document.getElementById('nombre');
    
    link.href='data:application/vnd.ms-excel;base64,' + window.btoa(tab_text);
    link.download=nombre.value;
    link.click();
    
    
}

#table {
  border: 1px solid;
  border-collapse: collapse;
  border-spacing: 0;
}
#table tr,
#table td {
  border: 1px solid;
  padding:2px;
}

<input type="text" id="nombre" value="miarchivo.xls"/>
<button id="descarga" onclick="fnExcelReport()">Descargar</button>
<a href="#" id="link" style="display:none"></a>
<table id="table"  style="width:100%">
        <thead>
            <tr>
                <th>Name</th>
                <th>Position</th>
                <th>Office</th>
                <th>Age</th>
                <th>Start date</th>
                <th>Salary</th>
            </tr>
        </thead>
        <tbody>
            <tr>
                <td>Adrián Nixon</td>
                <td>System Architect</td>
                <td>Edinburgh</td>
                <td>61</td>
                <td>2011/04/25</td>
                <td>$320,800</td>
            </tr>
            <tr>
                <td>Ashton Aguiló</td>
                <td>Junior Technical Author</td>
                <td>San Francisco</td>
                <td>66</td>
                <td>2009/01/12</td>
                <td>$86,000</td>
            </tr>
            <tr>
                <td>Cedric Cáñamo</td>
                <td>Senior Javascript Developer</td>
                <td>Edinburgh</td>
                <td>22</td>
                <td>2012/03/29</td>
                <td>$433,060</td>
            </tr>
</table>