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 Font
s.
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 getCellTypeEnum
and 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());
...
与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…