html - How to display the Excel Cell content along with its styling in XHTML page?

Question

I am developing a Java Web Application using JSF, Primefaces and XHTML.

In which, I am trying to read the Cell content from Excel using POI. In cell, it contains some styling like (bold, color, line-through, underline and etc) along with the value.

So now, I need to show the value as well all the styles of the cell in XHTML page.

Kindly help me to solve this.

Answer 1

The style is either applied to the whole cell or to parts of the cell content in rich text string content.

If applied to the whole cell, the cell style has a Font applied from which you can get the style.

For getting the styles from rich text string content, you need to get the RichTextString from the cell. This consists of multiple formatting runs, each having a style having a Font applied. So you need looping over all formatting runs to get their styles and their Fonts.

Example:

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.*;

import java.io.FileInputStream;

class ReadExcelRichTextCells {

 static StringBuffer getHTMLFormatted(String textpart, Font font) {

  StringBuffer htmlstring = new StringBuffer();

  boolean wasbold = false;
  boolean wasitalic = false;
  boolean wasunderlined = false;
  boolean wassub = false;
  boolean wassup = false;

  if (font != null) {
   if (font.getBold() ) {
    htmlstring.append("<b>");
    wasbold = true;
   }
   if (font.getItalic()) {
    htmlstring.append("<i>");
    wasitalic = true;
   }
   if (font.getUnderline() == Font.U_SINGLE) {
    htmlstring.append("<u>");
    wasunderlined = true;
   }
   if (font.getTypeOffset() == Font.SS_SUB) {
    htmlstring.append("<sub>");
    wassub = true;
   }
   if (font.getTypeOffset() == Font.SS_SUPER) {
    htmlstring.append("<sup>");
    wassup = true;
   }
  } 

  htmlstring.append(textpart);

  if (wassup) {
   htmlstring.append("</sup>");
  }
  if (wassub) {
   htmlstring.append("</sub>");
  }
  if (wasunderlined) {
   htmlstring.append("</u>");
  }
  if (wasitalic) {
   htmlstring.append("</i>");
  }
  if (wasbold) {
   htmlstring.append("</b>");
  }
  return htmlstring;  
 }

 public static void main(String[] args) throws Exception {

  Workbook wb  = WorkbookFactory.create(new FileInputStream("ExcelRichTextCells.xlsx"));

  Sheet sheet = wb.getSheetAt(0);
  for (Row row : sheet) {
   for (Cell cell : row) {
    switch (cell.getCellTypeEnum()) {
     case STRING: //CellType String
      XSSFRichTextString richtextstring = (XSSFRichTextString)cell.getRichStringCellValue();

      String textstring = richtextstring.getString();

      StringBuffer htmlstring = new StringBuffer();

      if (richtextstring.hasFormatting()) {
       for (int i = 0; i < richtextstring.numFormattingRuns(); i++) {
        int indexofformattingrun = richtextstring.getIndexOfFormattingRun(i);
        String textpart = textstring.substring(indexofformattingrun, 
                                               indexofformattingrun + richtextstring.getLengthOfFormattingRun(i));
        Font font = richtextstring.getFontOfFormattingRun(i);
        // font might be null if no formatting is applied to the specified text run
        // then font of the cell should be used.
        if (font == null) font = wb.getFontAt(cell.getCellStyle().getFontIndex());
        htmlstring.append(getHTMLFormatted(textpart, font));
       }
      } else {
       Font font = wb.getFontAt(cell.getCellStyle().getFontIndex());
       htmlstring.append(getHTMLFormatted(textstring, font));
      } 

      System.out.println(htmlstring);
      break;

     //case ... other CellTypes

     default:
      System.out.println("default cell"); //should never occur
    }
   }
  }

  wb.close();

 }
}

---

This code was tested using apache poi 3.17. For using this code with apache poi 4.0.1 do using CellStyle.getCellType instead of getCellTypeEnumand CellStyle.getFontIndexAsInt instead of getFontIndex.

...
//switch (cell.getCellTypeEnum()) {
switch (cell.getCellType()) {
...
//Font font = wb.getFontAt(cell.getCellStyle().getFontIndex());
Font font = wb.getFontAt(cell.getCellStyle().getFontIndexAsInt());
...
//if (font == null) font = wb.getFontAt(cell.getCellStyle().getFontIndex());
if (font == null) font = wb.getFontAt(cell.getCellStyle().getFontIndexAsInt());
...