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

Question

I am working on a project where an export to Excel functionality is required for a specific HTML table. The tables style needs to be maintained. Also, a metadata section needs to be added to the Excel (not present in the html table) and this section needs to be frozen.

I tried achieving this by parsing the html with JSoup and using Apache POI to create an excel file, but I could not find a way to retain the styling. The values stored in the html style tag do not directly equate to any styles in POI.

Before I started working on it, the application already had a function to export a table to excel by converting it to bytes and writing it with a FileOutPutStream and giving it an xls extension. This allows the style to be maintained, but since it is technically not an xls formatted file, I am not able to read it in with POI and apply the freeze.

Is there any way to either read in the file that the application already creates and apply the freeze, or convert the table into xls while maintaining the style and applying a freeze pane?

Alternately, if there is any way to have the table exported to a valid excel file with the styling maintained, I can then have POI read in that file and apply the freeze.

Answer 1

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

Answer 2

I believe that you are looking for a straight forward method, but the truth is that you can't. Some technologies like Google Spreadsheets can accomplish this by extract data from every cell of a table, and formatting the data to be usable in another application such as Microsoft Office Excel. There is no JS command that will do all this work for you... if you wan't you can study how MSOE is formatted, and figure out on your own how to write a script that will extract the data, and format it so that it can be viewed as an excel document.

A BETTER SOLUTION
[Read the above block first]
Ask the user to copy and paste the table into an excel document... this will will work perfectly. If the user is gonna open up the excel document anyways, why not make them copy paste... to make it more convenient for the user, then first learn how MSOE works.

Update I found a better solution:
http://www.codeproject.com/Tips/755203/Export-HTML-table-to-Excel-With-CSS
Use the JS code and modify to fit your tables needs