Cannot detect strikeout data from excel using apache poi

Question

For my project, I'm utilizing Java 8, Excel, and Apache POI. I'm interested in utilizing Java to extract certain cell values from Excel. I'm attempting to identify text that has been struck out in excel cells, but I'm having trouble because the text's format is a little different.

Below is how data is laid out in my excel sheet:

 

After extacting this data from excel, I always save it in string arraylist format like this a = [text 1, text 2, text 3]. code is mentioned below if you want to see how I am storing data in this arraylist.

What I want:

I want to ignore all those texts which are strikeout, so in the above case, I expect to have an output like this [text 2, text 3] for the first picture and second picture.

What I tried:

For the sake of just detecting strikeout values, I tried the below code first:

XSSFRichTextString text = new XSSFRichTextString(a.get(0));                             
XSSFFont font = text.getFontAtIndex(0);
Boolean font_striked = font.getStrikeout();

But it's not working properly. Can someone please help me with this?

Answer 1

You must first obtain the RichTextString, then examine all FormattingRuns to see if it has been stroked out, and only, in that case, should you obtain the proper substring and add it to the List:

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

import java.io.FileInputStream;

import java.util.List;
import java.util.ArrayList;

class ReadExcelRichTextCells {

 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:
      XSSFRichTextString richtextstring = (XSSFRichTextString)cell.getRichStringCellValue();
      String textstring = richtextstring.getString();

      List<String> textparts = new ArrayList<String>();

      if (richtextstring.hasFormatting()) {
       for (int i = 0; i < richtextstring.numFormattingRuns(); i++) {

        if (richtextstring.getFontOfFormattingRun(i)==null || !richtextstring.getFontOfFormattingRun(i).getStrikeout()) {

         int indexofformattingrun = richtextstring.getIndexOfFormattingRun(i);
         String textpart = textstring.substring(indexofformattingrun, 
                                                indexofformattingrun + richtextstring.getLengthOfFormattingRun(i));
         String[] textpart_split = textpart.split("-");
         for (int j = 0; j < textpart_split.length; j++){
          String text = textpart_split[j].replace("\n", "").trim();       
          if (!"".equals(text)) textparts.add(text);
         }
        }
       } 
      } else {
       textparts.add(textstring);
      }

      System.out.println(textparts);
      break;

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

  wb.close();

 }
}