CODEWITHSUNDEEP
htmlapache-poi
Alexander Mills
Alexander Mills

Reputation: 99959

Apache POI - formatting output to HTML

I am writing to an Excel file using Apache POI, but I want my output to be formatted as HTML not as literal text.

SXSSFWorkbook workbook = new SXSSFWorkbook();
Sheet sheet0 = workbook.createSheet("sheet0");
Row row0 = sheet0.createRow(2);
Cell cell0 = row0.createCell(2);

cell0.setCellValue("<html><b>blah blah blah</b></html>");

What appears when I open the Excel file is:

"<html><b>blah blah blah</b></html>"

but I want:

"blah blah blah"

essentially I am looking for a piece of code along the lines of:

cell0.setCellFormat(CellFormat.HTML);

Except, that doesn't exist.

here is some info on this topic

http://svn.apache.org/repos/asf/poi/trunk/src/examples/src/org/apache/poi/ss/examples/html/ToHtml.java

I will try this for now:

public void printPage() throws IOException {
    try {
        ensureOut();
        if (completeHTML) {
            out.format(
                    "<?xml version=\"1.0\" encoding=\"iso-8859-1\" ?>%n");
            out.format("<html>%n");
            out.format("<head>%n");
            out.format("</head>%n");
            out.format("<body>%n");
        }

        print();

        if (completeHTML) {
            out.format("</body>%n");
            out.format("</html>%n");
        }
    } finally {
        if (out != null)
            out.close();
        if (output instanceof Closeable) {
            Closeable closeable = (Closeable) output;
            closeable.close();
        }
    }
}

Upvotes: 4

Views: 7587

Answers (1)

kiwiwings
kiwiwings

Reputation: 3446

Based on my version for DocX, here is the adapted version for Hssf. As with the other version, you'll have to debug and extend the loop for the various css styles.

Update: I've overlooked yesterday, that you wanted to have a streaming XSSF solution, so I fiddled around, if it's possible to just use the usermodel classes (not really, when it comes to font colors), furthermore I wondered why SXSSF didn't use any of my font setting until I found out, that's currently by design (see Bug 52484)

import java.awt.Color;
import java.io.FileOutputStream;
import java.lang.reflect.Field;
import java.util.Enumeration;
import javax.swing.text.*;
import javax.swing.text.html.*;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.*;

public class StyledTextXls {
    public static void main(String[] args) throws Exception {
        HTMLEditorKit kit = new HTMLEditorKit();
        HTMLDocument doc = (HTMLDocument)kit.createDefaultDocument();
        kit.insertHTML(doc, doc.getLength(), "<p>paragraph <b>1</b></p>", 0, 0, null);
        kit.insertHTML(doc, doc.getLength(), "<p>paragraph <span style=\"color:red\">2</span></p>", 0, 0, null);

        Workbook wb = new XSSFWorkbook();
//      Workbook wb = new HSSFWorkbook();
//      Workbook wb = new SXSSFWorkbook(100); // doesn't work yet - see Bug 52484
        Sheet sheet = wb.createSheet();
        Row row = sheet.createRow(0);
        Cell cell = row.createCell(0);

        StringBuffer sb = new StringBuffer();
        for (int lines=0, lastPos=-1; lastPos < doc.getLength(); lines++) {
            if (lines > 0) sb.append("\n");
            Element line = doc.getParagraphElement(lastPos+1);
            lastPos = line.getEndOffset();
            for (int elIdx=0; elIdx < line.getElementCount(); elIdx++) {
                final Element frag = line.getElement(elIdx);
                String subtext = doc.getText(frag.getStartOffset(), frag.getEndOffset()-frag.getStartOffset());
                sb.append(subtext);
            }           
        }

        CreationHelper ch = wb.getCreationHelper();
        RichTextString rt = ch.createRichTextString(sb.toString());
        for (int lines=0, lastPos=-1; lastPos < doc.getLength(); lines++) {
            Element line = doc.getParagraphElement(lastPos+1);
            lastPos = line.getEndOffset();
            for (int elIdx=0; elIdx < line.getElementCount(); elIdx++) {
                final Element frag = line.getElement(elIdx);
                Font font = getFontFromFragment(wb, frag);
                rt.applyFont(frag.getStartOffset()+lines, frag.getEndOffset()+lines, font);

            }           
        }       

        cell.setCellValue(rt);
        cell.getCellStyle().setWrapText(true);
        row.setHeightInPoints((6*sheet.getDefaultRowHeightInPoints()));
        sheet.autoSizeColumn((short)0);

        FileOutputStream fos = new FileOutputStream("richtext"+(wb instanceof HSSFWorkbook ? ".xls" : ".xlsx"));
        wb.write(fos);
        fos.close();
    }

    static Font getFontFromFragment(Workbook wb, Element frag) {
        // creating a font on each is call is not very efficient
        // but should be ok for this exercise ...
        Font font = wb.createFont();

        final AttributeSet as = frag.getAttributes();
        final Enumeration<?> ae = as.getAttributeNames();

        while (ae.hasMoreElements()) {
            final Object attrib = ae.nextElement();

            try {
                if (CSS.Attribute.COLOR.equals(attrib)) {
                    // I don't know how to really work with the CSS-swing class ...
                    Field f = as.getAttribute(attrib).getClass().getDeclaredField("c");
                    f.setAccessible(true);
                    Color c = (Color)f.get(as.getAttribute(attrib));
                    if (font instanceof XSSFFont) {
                        ((XSSFFont)font).setColor(new XSSFColor(c));
                    } else if (font instanceof HSSFFont && wb instanceof HSSFWorkbook) {
                        HSSFPalette pal = ((HSSFWorkbook)wb).getCustomPalette();
                        HSSFColor col = pal.findSimilarColor(c.getRed(), c.getGreen(), c.getBlue());
                        ((HSSFFont)font).setColor(col.getIndex());
                    }
                } else if (CSS.Attribute.FONT_WEIGHT.equals(attrib)) {
                    if ("bold".equals(as.getAttribute(attrib).toString())) {
                        font.setBoldweight(Font.BOLDWEIGHT_BOLD);
                    }
                }
            } catch (Exception e) {
                System.out.println(attrib.getClass().getCanonicalName()+" can't be handled.");
            }
        }               

        return font;
    }
}

Upvotes: 4

Related Questions