Apache POI读取单元格多文本多样式

需求：读取excel单元格数据（文本和部分样式）,并拼接成html代码。

代码实现

 /**
  * 工作簿
  */
 private Workbook wb;
 /**
  * 导入的excel类型
  */
 private String excelType；
 /**
  * 2003-2007版本
  */
 private static final String EXCEL_TYPE_2003 = "xls";
 /**
  * 2007以后版本
  */
 private static final String EXCEL_TYPE_2007 = "xlsx";

 /**
  * 实现方法
  * 将单元格内容转为html格式
  */
 public String getCellToHtml(Cell cell){
     // 遍历每个文本片段
     StringBuilder htmlCode = new StringBuilder();
     //2003-2007版本的excel文件
     if(ObjectUtil.equals(EXCEL_TYPE_2003, this.excelType)) {
         HSSFRichTextString hssfRichText = (HSSFRichTextString) cell.getRichStringCellValue();
         //多文本多样式
         int nextEnd = 0; //上一次结束索引
         for (int i = 0; i < hssfRichText.numFormattingRuns(); i++) {
             int start = hssfRichText.getIndexOfFormattingRun(i);
             int end = hssfRichText.length();
             String textHead = StringPool.EMPTY;
             if (i + 1 < hssfRichText.numFormattingRuns()) {
                 end = hssfRichText.getIndexOfFormattingRun(i + 1);
             }
             //HSSFRichTextString.numFormattingRuns()获取不到单元格头文本样式，这时候文本样式取单元格样式
             if(start > nextEnd){
                 textHead = hssfRichText.getString().substring(nextEnd, start);
                 Font fontHead = wb.getFontAt(cell.getCellStyle().getFontIndexAsInt());
                 textHead = unionHtmlStyleFormFont(fontHead, textHead, null);
             }
             nextEnd = end;
             // 获取文本片段
             String  textContent = hssfRichText.getString().substring(start, end);
             // 获取文本片段样式
             Font font = wb.getFontAt(hssfRichText.getFontAtIndex(start));
             textContent = unionHtmlStyleFormFont(font, textContent, textHead);
             htmlCode.append(textContent);
         }
     }
     //2007以后的excel版本
     else {
         XSSFRichTextString xssfRichText = (XSSFRichTextString) cell.getRichStringCellValue();
         for (int i = 0; i < xssfRichText.numFormattingRuns(); i++) {
             int start = xssfRichText.getIndexOfFormattingRun(i);
             int end = xssfRichText.length();
             String textHead = StringPool.EMPTY;
             if (i + 1 < xssfRichText.numFormattingRuns()) {
                 end = xssfRichText.getIndexOfFormattingRun(i + 1);
             }
             String  textContent = xssfRichText.getString().substring(start, end);
             //XSSFRichTextString.numFormattingRuns()获取不到富文本头文本样式，这时候文本样式取单元格样式
             if(start == 0){
                 Font font = wb.getFontAt(cell.getCellStyle().getFontIndexAsInt());
                 textContent = unionHtmlStyleFormFont(font, textContent, textHead);
             }else{
                 XSSFFont font = xssfRichText.getFontAtIndex(start);
                 textContent = unionHtmlStyleFormFont(font, textContent, textHead);
             }
             htmlCode.append(textContent);
         }
     }
     //单元格文本只存在一种样式
     if(cell.getRichStringCellValue().numFormattingRuns() == 0){
         Font font = wb.getFontAt(cell.getCellStyle().getFontIndexAsInt());
         String  textContent = cell.getRichStringCellValue().getString();
         textContent = unionHtmlStyleFormFont(font, textContent, null);
         htmlCode.append(textContent);
     }

     if(StringUtils.isNotBlank(htmlCode)) {
         return  "<p>" + htmlCode + "</p>";
     }
     return null;
 }

 /**
  * 拼接html样式（根据具体业务需求实现）
  */
 private String unionHtmlStyleFormFont(Font font, String textContent, String textHead){
     //加粗
     if(font.getBold()){
         textContent = "<strong>" + textContent + "</strong>";
     }
     //斜体
     if(font.getItalic()){
         textContent = "<em>" + textContent + "</em>";
     }
     //上下标
     switch (font.getTypeOffset()){
         case Font.SS_SUPER:
             textContent = "<sup>" + textContent + "</sup>";
             break;
         case Font.SS_SUB:
             textContent = "<sub>" + textContent + "</sub>";
         default:
             break;
     }
     //水平删除斜线
     if(font.getStrikeout()){
         textContent = "<span style=\"text-decoration: line-through;\">" + textContent + "</span>";
     }
     //下划线
     if(font.getUnderline() == Font.U_SINGLE){
         textContent = "<span style=\"text-decoration: underline;\">" + textContent + "</span>";
     }
     if(StringUtils.isNotBlank(textHead)){
         textContent = textHead + textContent;
     }
     return textContent;
 }