package nl.eveoh.qrgenerator.service;

import nl.eveoh.qrgenerator.model.Location;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.*;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Component;

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

/**
 * @author Erik van Paassen
 */
@Component
public class ExcelLocationServiceImpl implements LocationService {

    @Value("${Excel.HostKeyColumnHeader}")
    private String hostKeyColumnHeader;
	@Value("${Excel.FileNameColumnHeader}")
	private String fileNameColumnHeader;


    @Override
    public ArrayList<Location> getLocations(String inputFilePath) {
        ArrayList<Location> locations = new ArrayList<>();

        Workbook wb;
        try {
            wb = WorkbookFactory.create(new File(inputFilePath));
        } catch (InvalidFormatException | IOException e) {
            throw new RuntimeException(e);
        }

        Sheet sheet = wb.getSheetAt(0);
        int rowIndex = 0;
        int hostKeyColumn = 0;
		int fileNameColumn = 0;
        for (Row row : sheet) {
            if (rowIndex == 0) {
                hostKeyColumn = getHostKeyCell(row);
				fileNameColumn = getFileNameCell(row);
            } else {
                Location location = parseRow(row, hostKeyColumn, fileNameColumn);

                if (location != null) {
                    locations.add(location);
                }
            }

            rowIndex++;
        }

        return locations;
    }

    private int getHostKeyCell(Row row) {
        int cellIndex = 0;
        for (Cell cell : row) {
            if (hostKeyColumnHeader.equalsIgnoreCase(cell.getStringCellValue())) {
                return cellIndex;
            }

            cellIndex++;
        }

        return -1;
    }
    private int getFileNameCell(Row row) {
        int cellIndex = 0;
        for (Cell cell : row) {
            if (fileNameColumnHeader.equalsIgnoreCase(cell.getStringCellValue())) {
                return cellIndex;
            }

            cellIndex++;
        }

        return -1;
    }


    private Location parseRow(Row row, int hostKeyColumn, int locationNameColumn) {
        Location location = new Location();

        Cell hostKeyCell = row.getCell(hostKeyColumn);
		Cell locationNameCell = row.getCell(locationNameColumn);
        if (hostKeyCell == null||locationNameCell==null) {
            return null;
        }

        location.setHostKey(hostKeyCell.getStringCellValue());
		location.setFileName(locationNameCell.getStringCellValue());

        return location;
    }
}
@@ -1230,66 +1230,108 @@
  get
-HostKeyCell(row);%0A%09%09%09%09fileNameColumn = getFileNameCell(row
+CellIndexOf(row, hostKeyColumnHeader);%0A%09%09%09%09fileNameColumn = getCellIndexOf(row, fileNameColumnHeader
 );%0A 
@@ -1642,322 +1642,49 @@
  get
-HostKeyCell(Row row) %7B%0A        int cellIndex = 0;%0A        for (Cell cell : row) %7B%0A            if (hostKeyColumnHeader.equalsIgnoreCase(cell.getStringCellValue())) %7B%0A                return cellIndex;%0A            %7D%0A%0A            cellIndex++;%0A        %7D%0A%0A        return -1;%0A    %7D%0A    private int getFileNameCell(Row row
+CellIndexOf(Row row, String stringToMatch
 ) %7B%0A
@@ -1758,36 +1758,29 @@
     if (
-fileNameColumnHeader
+stringToMatch
 .equalsI
@@ -1930,17 +1930,16 @@
 %0A    %7D%0A%0A
-%0A
     priv

Remove duplicate code.