java - HTML Formatted Cell value from Excel using Apache POI

Question

Welcome To Ask or Share your Answers For Others

java - HTML Formatted Cell value from Excel using Apache POI

posted Oct 24, 2021 in Technique[技术] by 深蓝 (71.8m points)

java - HTML Formatted Cell value from Excel using Apache POI

I am using apache POI to read an excel document. To say the least, it is able to serve my purpose as of now. But one thing where I am getting struck is extracting the value of cell as HTML.

I have one cell wherein user will enter some string and apply some formatting(like bullets/numbers/bold/italic) etc.

SO when I read it the content should be in HTML format and not a plain string format as given by POI.

I have almost gone through the entire POI API but not able to find anyone. I want to remain the formatting of just one particular column and not the entire excel. By column I mean, the text which is entered in that column. I want that text as HTML text.

Explored and used Apache Tika also. However as I understand it can only get me the text but not the formatting of the text.

Please someone guide me. I am running out of options.

Suppose I wrote My name is Angel and Demon in Excel.

The output I should get in Java is My name is <b>Angel</b> and <i>Demon</i>

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙；凝视深渊过久,深渊将回以凝视…

1 Reply

深蓝 · Answer 1 · 2021-10-23T19:24:59+0000

I've paste this as unicode to cell A1 of xls file:

<html><p>This is a test. Will this text be <b>bold</b> or <i>italic</i></p></html>

This html line produce this:

This is a test. Will this text be bold or italic

My code:

public class ExcelWithHtml {
    // <html><p>This is a test. Will this text be <b>bold</b> or
    // <i>italic</i></p></html>

    public static void main(String[] args) throws FileNotFoundException,
            IOException {
        new ExcelWithHtml()
                .readFirstCellOfXSSF("/Users/rcacheira/testeHtml.xlsx");
    }

    boolean inBold = false;
    boolean inItalic = false;

    public void readFirstCellOfXSSF(String filePathName)
            throws FileNotFoundException, IOException {
        FileInputStream fis = new FileInputStream(filePathName);
        XSSFWorkbook wb = new XSSFWorkbook(fis);
        XSSFSheet sheet = wb.getSheetAt(0);

        String cellHtml = getHtmlFormatedCellValueFromSheet(sheet, "A1");

        System.out.println(cellHtml);

        fis.close();
    }

    public String getHtmlFormatedCellValueFromSheet(XSSFSheet sheet,
            String cellName) {

        CellReference cellReference = new CellReference(cellName);
        XSSFRow row = sheet.getRow(cellReference.getRow());
        XSSFCell cell = row.getCell(cellReference.getCol());

        XSSFRichTextString cellText = cell.getRichStringCellValue();

        String htmlCode = "";
        // htmlCode = "<html>";

        for (int i = 0; i < cellText.numFormattingRuns(); i++) {
            try {
                htmlCode += getFormatFromFont(cellText.getFontAtIndex(i));
            } catch (NullPointerException ex) {
            }
            try {
                htmlCode += getFormatFromFont(cellText
                        .getFontOfFormattingRun(i));
            } catch (NullPointerException ex) {
            }

            int indexStart = cellText.getIndexOfFormattingRun(i);
            int indexEnd = indexStart + cellText.getLengthOfFormattingRun(i);

            htmlCode += cellText.getString().substring(indexStart, indexEnd);
        }

        if (inItalic) {
            htmlCode += "</i>";
            inItalic = false;
        }
        if (inBold) {
            htmlCode += "</b>";
            inBold = false;
        }

        // htmlCode += "</html>";
        return htmlCode;

    }

    private String getFormatFromFont(XSSFFont font) {
        String formatHtmlCode = "";
        if (font.getItalic() && !inItalic) {
            formatHtmlCode += "<i>";
            inItalic = true;
        } else if (!font.getItalic() && inItalic) {
            formatHtmlCode += "</i>";
            inItalic = false;
        }

        if (font.getBold() && !inBold) {
            formatHtmlCode += "<b>";
            inBold = true;
        } else if (!font.getBold() && inBold) {
            formatHtmlCode += "</b>";
            inBold = false;
        }

        return formatHtmlCode;
    }

}

My output:

This is a test. Will this text be <b>bold</b> or <i>italic</i>

I think it is what you want, i'm only show you the possibilities, i'm not using the best code practices, i'm just programming fast to produce an output.

Categories

java - HTML Formatted Cell value from Excel using Apache POI

java - HTML Formatted Cell value from Excel using Apache POI

Please log in or register to add a comment.

Please log in or register to reply this article.

1 Reply

Please log in or register to add a comment.

Just Browsing Browsing

Most popular tags