Cannot detect strikeout data from excel using apache poi

0 votes

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:

enter image description here enter image description here

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?

Dec 9, 2022 in Others by Kithuzzz
• 38,000 points
853 views

1 answer to this question.

0 votes

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

 }
}
answered Dec 10, 2022 by narikkadan
• 63,600 points

Related Questions In Others

0 votes
1 answer

How to convert data from txt files to Excel files using python

Hi , there are few steps to ...READ MORE

answered Feb 16, 2022 in Others by Edureka
• 13,690 points
14,028 views
0 votes
1 answer

Runtime error 438 while importing data in excel from secured website using VBA

Replace With ieDoc.forms(0) .userType.Value = "1" ...READ MORE

answered Sep 23, 2022 in Others by narikkadan
• 63,600 points
897 views
0 votes
1 answer

Is there a way to hide excel formula using Apache POI

In Excel, hiding formulae is a feature ...READ MORE

answered Oct 11, 2022 in Others by narikkadan
• 63,600 points
1,210 views
0 votes
1 answer

Formatting Excel Spreadsheet using Apache POI in JAVA

Try this: CellRangeAddress range= new CellRangeAddress(firstrow, lastrow, firstcol, ...READ MORE

answered Oct 27, 2022 in Others by narikkadan
• 63,600 points
2,237 views
0 votes
1 answer
0 votes
1 answer

What is the better API to Reading Excel sheets in java - JXL or Apache POI

Here are the things where both APIs ...READ MORE

answered Oct 9, 2022 in Others by narikkadan
• 63,600 points
3,400 views
0 votes
1 answer

Apache POI. Setup data filters in Excel

It's already enabled in Apache POI 3.7. ...READ MORE

answered Oct 21, 2022 in Others by narikkadan
• 63,600 points
2,062 views
0 votes
1 answer

How to get columns from Excel files using Apache POI?

The only way to see all the ...READ MORE

answered Oct 18, 2022 in Others by narikkadan
• 63,600 points
5,247 views
0 votes
1 answer

Unable to import data in excel from another website using VB code

Replace : Set ieTable = ieDoc.all.Item("report-table") With: Set ieTable = ...READ MORE

answered Sep 21, 2022 in Others by narikkadan
• 63,600 points
790 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP