How to extract data from Excel sheet using Apache POI in java(lookup framing)

Question

public class Array_Learn {
    public static void main(String[] args) {
        try {
            FileInputStream ExcelFile = new FileInputStream(new File("C:\\Users\\Anbu.B\\Desktop\\POI-Test\\mediTask.xlsx"));
            XSSFWorkbook book1 = new XSSFWorkbook(ExcelFile);
            XSSFSheet sheet = book1.getSheetAt(0);
            Iterator<Row> rowiter = sheet.iterator();
            while (rowiter.hasNext()) {
                XSSFRow row = (XSSFRow) rowiter.next();
                if (row.getRowNum() == 2) {
                    Iterator cellIterator = row.cellIterator();
                    while (cellIterator.hasNext()) {
                        XSSFCell cell = (XSSFCell) cellIterator.next();
                        if (cell.getStringCellValue().contains("|")) {
                            String split[] = cell.getStringCellValue().split("\\|");
                        }
                    }
                }
            }
        } catch (Exception e) {
            System.out.println(e);
        }
    }
}

I need this output:

chest&&pain=J90
lung&&pneumonia=J54.9
lungs&&pneumonia=J54.9
bronchi&&pneumonia=J54.9
bronchus&&pneumonia=J54.9
colon&&ascending&tumor=D12.5
colon&&ascending&carcinoma=D12.5
colon&&ascending&cancer=D12.5
colon&&ascending&&tumor&&resection=D12.6
colon&&descending&&tumor&&resection=D12.6
colon&&ascending&&carcinoma&&resection=D12.6
colon&&descending&&carcinoma&&resection=D12.6
colon&&ascending&&cancer&&resection=D12.6
colon&&descending&&cancer&&resection=D12.6

The above code is doing read row and iterate each cell and check cell contains | symbol condition is true the split statement is working but, I need the above exact output. What I did in the above code:

Read the excel file.
Read sheet from the excel file.
then create row iterator.
create cell iterator.
check cell contains | symbol then split that cell strings and store into the string array.

The image is the exact input excel file i need the above output from the excel file.

You know what you need to do, so what's stopping you from writing the code? — Gilbert Le Blanc, Jul 07 '21 at 15:05
I cant be able to find the logic for printing that format of output dynamically. and also i am a new learner in java. — ANBU RAJA, Jul 07 '21 at 17:23
The [StringBuilder](https://docs.oracle.com/javase/tutorial/java/data/buffers.html) class allows you to build Strings. — Gilbert Le Blanc, Jul 07 '21 at 17:32
you could possibly instantiate a number of arraylists for each column. then once you getValue from a cell, put into appropriate column arraylist. once you iterated 5 cells, based on arraylist, build a string by checking if arraylist is empty or has many items — experiment unit 1998X, Jul 08 '21 at 01:01
@GilbertLeBlanc can you give a short explain how to do with string builder — ANBU RAJA, Jul 08 '21 at 01:25

score 1 · Accepted Answer · answered Jul 08 '21 at 10:25

You almost finished your task. The key here to use one of the algorithms to generate combinations. You could find a general description of such algorithms there, or more close examples with strings on java there.

Full code example (recursive algorithm):

The ParsedRow class for calculating of different combinations:

class ParsedRow {
    private final List<List<String>> combinations;
    private final String suffix;

    public ParsedRow(List<List<String>> combinations, String suffix) {
        this.combinations = combinations;
        this.suffix = suffix;
    }

    public List<String> combine() {
        List<String> res = new ArrayList<>();
        combine(res, 0, "");
        return res;
    }


    public void combine(List<String> res, int depth, String current) {
        if (combinations.size() == depth) {
            res.add(current + "=" + suffix);
            return;
        }
        String delimiter = current.isEmpty() ? "" : "&&";
        for (int i = 0; i < combinations.get(depth).size(); i++) {
            combine(res, depth + 1, current + delimiter + combinations.get(depth).get(i));
        }
    }
}

The Main class for reading the xlsx file and printing results

public class Main {
    public static void main(String[] args) throws IOException {
        try (final FileInputStream file = new FileInputStream("diseases.xlsx");
             XSSFWorkbook workbook = new XSSFWorkbook(file)) {
            List<String> finalResult = new ArrayList<>();
            for (Row row : workbook.getSheetAt(0)) {
                List<List<String>> combinations = new ArrayList<>();
                String suffix = "";
                for (Cell cell : row) {
                    if (cell.getColumnIndex() != 4) {
                        final List<String> strings = Arrays.asList(cell.getStringCellValue().split("\\|"));
                        combinations.add(strings);
                    } else {
                        suffix = cell.getStringCellValue();
                    }
                }
                ParsedRow parsedRow = new ParsedRow(combinations, suffix);
                finalResult.addAll(parsedRow.combine());
            }
            for (String line : finalResult) {
                System.out.println(line);
            }
        }
    }
}

How to extract data from Excel sheet using Apache POI in java(lookup framing)

1 Answers1