Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
416 views
in Technique[技术] by (71.8m points)

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

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.

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

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());
...

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...