如何在XHTML页面中显示Excel单元格内容及其样式? [英] How to display the Excel Cell content along with its styling in XHTML page?

问题描述

我正在使用 JSF，Primefaces和XHTML 开发 Java Web应用程序.

我试图使用 POI 从 Excel 中读取单元格内容.在单元格中，它包含一些样式(如粗体，颜色，直通，下划线等)以及该值.

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.

现在，我需要在 XHTML 页面中显示该值以及该单元格的所有样式.

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.

推荐答案

样式将应用于富文本字符串内容中的整个单元格或单元格内容的一部分.

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.

要从富文本字符串内容中获取样式，您需要获取 RichTextString .这由多个格式化运行组成，每个运行都具有应用了Font的样式.因此，您需要遍历所有格式化运行以获取其样式和它们的Font.

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.

示例:

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

 }
}

---

此代码已使用apache poi 3.17进行了测试. 对于将此代码与apache poi 4.0.1一起使用，请使用CellStyle.getCellType代替getCellTypeEnum，并使用CellStyle.getFontIndexAsInt代替getFontIndex.

---

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

这篇关于如何在XHTML页面中显示Excel单元格内容及其样式?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！