Export HTML Table to Excel and Keep CSS Styles

How to Export an HTML table as an Excel while maintaining style and applying freeze pane

I ended up achieving this by adding to my original class that created the xls file using jsoup and apache POI. I was able to retrieve the contents of the 'style' tags for all html elements as Strings using jsoup and then parse those Strings looking for font-family, font-size, etc, anything that could be applied to the Excel as formatting. For each attribute I then had to figure out how that specific html style could be transferred to a cellStyle in POI. Here is an example for font-color using rgb style:

private HSSFFont setFontColor(HSSFWorkbook wb, HSSFFont font, String colorCode) {
    HSSFPalette palette = wb.getCustomPalette();

    // if format is rgb(x,y,z) form, retrieve the 3 numbers within the
    // parentheses
    colorCode = colorCode.trim();
    if (colorCode.toLowerCase().startsWith("rgb")) {
        String rgbNumString = colorCode.substring(3, colorCode.length()).trim();
        rgbNumString = rgbNumString.substring(1, rgbNumString.length()-1).trim();
        String[] rgbNums = StringUtils.split(rgbNumString, ",");
        int[] rgbInts = { Integer.parseInt(rgbNums[0].trim()),
                Integer.parseInt(rgbNums[1].trim()),
                Integer.parseInt(rgbNums[2].trim()) };
        HSSFColor color = palette.findSimilarColor(rgbInts[0], rgbInts[1], rgbInts[2]);
        short palIndex = color.getIndex();
        font.setColor(palIndex);
        return font;
    } 
    return font;
}

Export html table data to Excel using JavaScript / JQuery is not working properly in chrome browser

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>

Export HTML table to Excel file

Managed to fix it. I initially suspected that not putting it inside the ready function would be a problem and thought it would fix the issue, but that was only part of the problem. I also forgot a comma. The finished result is as follows.

<script>
    $(document).ready(function() {

        //activate footable jquery plugin (this is the dynamic table on the report page with search and sorting functions)
        $('.footable').footable();

        //Prepare table2excel plugin (clicking the export button will send the main table to an Excel spreadsheet)
        $("button.excelexport").click(function(){
            $("#footable").table2excel({
                //Exclude CSS class specific to this plugin
                exclude: ".noExl",
                name: "Merchandising Report",
                filename: "merchReportTable"
            });
        });

    });            

</script>

How to export an HTML table as a .xlsx file

You won't be able to export it as XLSX without going back to the server. A XLSX file is a collection of XML files, zipped together. This means you do need to create multiple files. This is impossible to do with JS, client-side.

Instead, you should create a function retrieving the data from your HTML table and send that to you server. The server can then create the XLSX file for you (there are a bunch of libs available for that!) and send it back to the client for download.

If you expect to have a huge dataset, the XLSX creation on the server should be done as an async process, where you notify the user when it's done (instead of having the user waiting for the file to be created).

Let us know which language you use on your server, and we'll be able to recommend you some good libraries.

Load html table side by side when saving to Excel

So basically this can be achieved using a html trick..adding a master table with inner td for containing tables.

<table> 
   <tr> 
      <td>table 1 code with all the html</td>
      <td>table 2 code with all the html</td>
   </tr>
</table>

---

Related Topics