I have a very large excel sheet (about 30,000+) records of house prices and their location (a latitude and longitude value). At the moment I can parse parts of the excel sheet into the console of Eclipse using Apache POI. However is it possible to return only certain rows and their house prices if they were within a radius of x KM of a given latitude and longitude by a user?
I'm lead to believe the haversine formula could be used to calculate something like this but I've hit a brick wall as to how I would begin implementing that into a program like this.
public class ExcelReader {
public static final String SAMPLE_XLSX_FILE_PATH = "./LatLongPrice.xlsx";
public static void main(String[] args) throws IOException, InvalidFormatException {
Workbook workbook = WorkbookFactory.create(new File(SAMPLE_XLSX_FILE_PATH));
// Retrieving the number of sheets in the Workbook
System.out.println("Workbook has " + workbook.getNumberOfSheets() + " Sheets : ");
// obtain a sheetIterator and iterate over it
Iterator<Sheet> sheetIterator = workbook.sheetIterator();
System.out.println("Retrieving Sheets using Iterator");
while (sheetIterator.hasNext()) {
Sheet sheet = sheetIterator.next();
System.out.println("=> " + sheet.getSheetName());
}
// Getting the Sheet at index zero
Sheet sheet = workbook.getSheetAt(0);
// Create a DataFormatter to format and get each cell's value as String
DataFormatter dataFormatter = new DataFormatter();
// Obtain a rowIterator and columnIterator and iterate over them
System.out.println("\n\nIterating over Rows and Columns using Iterator\n");
Iterator<Row> rowIterator = sheet.rowIterator();
while (rowIterator.hasNext()) {
Row row = rowIterator.next();
// Iteratating over the columns of the current row
Iterator<Cell> cellIterator = row.cellIterator();
while (cellIterator.hasNext()) {
Cell cell = cellIterator.next();
String cellValue = dataFormatter.formatCellValue(cell);
System.out.print(cellValue + "\t");
}
System.out.println();
}
});
// Closing the workbook
workbook.close();
}