How to read the first n lines of a HUGE excel file

Question

So, I am trying to write a program that scans a specific pattern in an excel file line. Namely, for N followed by any letter, then S or T (each letter occupying one cell).

The problem is that the excel file I'm using is absolutely massive, with about 3,000 rows and almost 1,000 columns. I am trying to find this pattern only in the first 60 lines in order to reduce java heap space. How can I find my algorithm for this? I am still getting exceptions from memory.

My code is as follows:

import java.awt.List; import java.io.File; import java.io.FileInputStream; import java.io.IOException; import java.util.ArrayList; import org.apache.poi.EncryptedDocumentException; import org.apache.poi.openxml4j.exceptions.InvalidFormatException; import org.apache.poi.ss.usermodel.Workbook; import org.apache.poi.ss.usermodel.WorkbookFactory; import org.apache.poi.xssf.usermodel.XSSFCell; import org.apache.poi.xssf.usermodel.XSSFRow; import org.apache.poi.xssf.usermodel.XSSFSheet; import org.apache.poi.xssf.usermodel.XSSFWorkbook; public class ExcelReader { public int Reader(File file) throws IOException, EncryptedDocumentException, InvalidFormatException { FileInputStream fis = new FileInputStream(file); String filepath = file.getPath(); Workbook wb = WorkbookFactory.create(new File(filepath)); XSSFSheet sheet = (XSSFSheet) wb.getSheetAt(0); XSSFRow row; XSSFCell cell; ArrayList<Integer> list = new ArrayList<Integer>(); int rows; int cols = 0; int temp = 0; rows = sheet.getPhysicalNumberOfRows(); for (int i = 0; i < 10 || i < 60; i++) { row = sheet.getRow(i); if (row != null) { temp = sheet.getRow(i).getPhysicalNumberOfCells(); if (temp > cols) cols = temp; } } for (int r = 0; r <= 60; r++) { row = sheet.getRow(r); if (row != null) { for (int c = 0; c <= cols; c++) { int numblanks = 0; cell = row.getCell((short) c); if (cell != null) { //System.out.print(cell + "\t\t"); } else { //System.out.print("\t\t"); } if (cell != null && cell.getCellType() == XSSFCell.CELL_TYPE_STRING) { if ("N".equals(cell.getStringCellValue())) { for (int k = c; k <= cols; k++) { if ("-".equals(row.getCell(k).getStringCellValue())) { numblanks++; continue; } if ("S".equals(row.getCell(c + 2 + numblanks).getStringCellValue()) || "T".equals(row.getCell(c + 2 + numblanks).getStringCellValue())) { list.add((int) sheet.getRow(1).getCell(c).getNumericCellValue()); break; } } } } } System.out.println(); } } System.out.println(); System.out.println("Rows: " + rows); System.out.println("Columns: " + cols); System.out.println(list); return temp; } } 

Answer 1

Convert to a CSV file, which is easy enough to make. If possible, I would insert data into a database table and use the procedure to search and find what you are looking for. This can be done using Spring Batch and Java.