parse-excel-by-first-cell.java

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

import java.io.File;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;

public class ReadExcel {

    public static void main(String[] args) throws IOException {
        String f = "target/test.xlsx";
        Workbook workbook = WorkbookFactory.create(new File(f));



        Sheet sheet = workbook.getSheetAt(1);

        int currentRowNumber = 0;
        while (currentRowNumber <= sheet.getLastRowNum()) {
            Row r = sheet.getRow(currentRowNumber);
            if (r == null) {
                System.out.println("[row:" + currentRowNumber + "] is null (outside of an entry)");
                currentRowNumber++;
                continue;
            }

            Cell firstCell = r.getCell(0, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK);
            if ("Type".equals(firstCell.toString())) {
                System.out.println("..........new entry starts.........");
                Row firstRow = sheet.getRow(currentRowNumber);
                System.out.println(beautifyRow(firstRow) + " - first row");

                while (true) {
                    Row nextRow = sheet.getRow(currentRowNumber + 1);

                    if (nextRow == null) {
                        currentRowNumber++;
                        System.out.println("[row:" + currentRowNumber + "] is null (end of an entry - inside of an entry)");
                        break;
                    }
                    Cell firstCellInNextRow = nextRow.getCell(0, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK);
                    if ("Type".equals(firstCellInNextRow.toString())) {
                        // currentRowNumber++;
                        System.out.println("ending the current entry, next entry detected.");
                        break;
                    } else {
                        currentRowNumber++;
                        Row currentRow = sheet.getRow(currentRowNumber);
                        if (currentRow.getLastCellNum() > 1) {
                            System.out.println(beautifyRow(currentRow));
                           // process actual data here...
                        } else {
                            System.out.println(beautifyRow(currentRow) + " - empty row");
                        }
                    }
                }
            } else {
                throw new RuntimeException(beautifyRow(r) + " - invalid row.... skipped.");
            }
            
            currentRowNumber++;
        }

    }

    private static String beautifyRow(Row row) {
        List<String> values = new ArrayList<>();
        for (int i = 0; i < row.getLastCellNum(); i++) {
            values.add(row.getCell(i, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK).toString());
        }

        return "[row:" + row.getRowNum() + "] = " + String.join(",", values);

    }
}