How do I write a unit test to my class by reading a file and writing it to another file?

I need to read an xlsx file and wirte the output in combination with a SQL statement to a file.

The following are the requirements:

1. The file is located in a specific folder on the C drive.
2. in the file, the 1st and 3rd column should be read without the headers.
3. a SQL statement is to be written to a file in combination with the previous output.

This works so far. I had to implement this with JAVA8 Springboot. Enclosed I have uploaded my MainClass, the ExcelReader Class, the aapplication.properties and the pom.xml

I don't understand now how I create my unit test for it. Do I have to split my class into several methods, because in one method readInputFileWriteSQLStatements(), the file is read, the columns are filtered and a new file is filled.

Which parameters should I write into these methods? How does the unit test look like?

FIRST TRY:

First I set the path property in the Mainclass, and put the parameter in Class ExcelReader to readInputFileWriteSQLStatements(String filePath); but the result is NULL. Then i tried to write a TestClass with a path vom the test/resource/ directory but this didnt, work beacut of the NULL Pointer exception

@Value("${filepath}")

private static String filePath;

public static void main(String[] args) throws IOException, InvalidFormatException {

    SpringApplication sa = new SpringApplication(MainClass.class);

    ApplicationContext c = sa.run(args);
    ExcelReader bean = c.getBean(ExcelReader.class);
    bean.readInputFileWriteSQLStatements(filePath);
}

@PropertySource("classpath:application.properties")

@Configuration

public class ExcelReader {

public static final String SQL_FILE_NAME = "SQL_insert_statements_for_LBU.txt";

private static final String FILE_PATTERN = ".xlsx";

private static final String INVALID_VALUE_IN_ROW = "invalid values in row ";
private static final String INVALID_SOU_CODE = ": length of sou code should be less than 10";
private static final String INVALID_LOCATION_NUMBER = ": length of location number should be less than 15";
private static final String INSERT_WHEN_EXIST = "INSERT WHEN EXISTS ";
private static final String SELECT_ID_FROM_M_S_WHERE_CODE = "(SELECT ID from M_S  WHERE CODE = '";
private static final String INTO_M_S_LOC = "') THEN INTO M_S_LBU_LOC (LBU_ID, GEO_SCOPE_LOC_NUM) SELECT ";
private static final String FROM_DUAL = "' from dual;";
private String codes;
private String locNumbers;
private String insertStatement;
@Value("${filepath}")
private String filePath;

@Bean
public static PropertySourcesPlaceholderConfigurer propertyConfigInDev() {
    return new PropertySourcesPlaceholderConfigurer();
}

public void readInputFileWriteSQLStatements() throws IOException, InvalidFormatException, IllegalArgumentException {

    File directoryPath = new File(filePath);
    File[] files = directoryPath.listFiles(new FilenameFilter() {
        @Override
        public boolean accept(File dir, String name) {
            return name.endsWith(FILE_PATTERN);
        }
    });

    File file = files[0];
    // Creating a Workbook from an Excel file (.xls or .xlsx)
    Workbook workbook = WorkbookFactory.create(new File(filePath + file.getName()));

    System.out.println("NAME ===" + filePath + file.getName());

    BufferedWriter outStream = new BufferedWriter(new FileWriter(filePath + SQL_FILE_NAME));

    // Getting the sheet at index zero
    Sheet sheet = workbook.getSheetAt(0);
    int totalRows = sheet.getPhysicalNumberOfRows();

    //for iterating columns ( 1. & 3. column)
    Row r = sheet.getRow(0);
    int lastColumnUsed = r.getLastCellNum();

    IntStream.range(1, totalRows).forEach(
            iteratedRows -> {

                Row rows = sheet.getRow(iteratedRows);

                IntStream.range(0, lastColumnUsed - 1).forEach(
                        iteratedColumns -> {
                            if (iteratedColumns == 0 || iteratedColumns == 2) {
                                codes = rows.getCell(0).getStringCellValue();

                                //get the location number
                                DataFormatter formatter = new DataFormatter();
                                Cell locNumbers = rows.getCell(2);
                                locationNumbers = formatter.formatCellValue(locNumbers);
                                insertStatement = .....
                            }
                        }

                );

                int lengthCodes = codes.length();
                int lengthLocationNumber = locationNumbers.length();

                try {
                    //the value of location number could be invalid and formatted like "1,000E16" -> 10000000000007800
                    //lenght ist more than 15, so I get the last two chars of location number and check if its less than 15
                    String formatLocationNumbers = locationNumbers.substring(locationNumbers.length() - 2);

                    if (lengthCodes <= 10 && lengthLocationNumber <= 15 && !locationNumbers.contains("+")) {
                        outStream.write(insertStatement);
                        outStream.newLine();
                    }
                    if ((lengthCodes <= 10) &&
                            (locationNumbers.contains("+") && Integer.parseInt(formatLocationNumbers) < 15)) {
                        outStream.write(insertStatement);
                        outStream.newLine();
                    }
                    if ((lengthCodes > 10) && (
                            (locationNumbers.contains("+") && Integer.parseInt(formatLocationNumbers) > 15)
                                    || lengthLocationNumber > 15)
                    ) {
                        throw new IllegalArgumentException(INVALID_VALUE_IN_ROW + iteratedRows +
                                                                   INVALID_SOU_CODE +
                                                                   INVALID_LOCATION_NUMBER);
                    }
                    if (lengthCodes > 10) {

                        throw new IllegalArgumentException(INVALID_VALUE_IN_ROW + iteratedRows
                                                                   + INVALID_SOU_CODE);
                    }
                    if ((lengthLocationNumber > 15) ||
                            (locationNumbers.contains("+") && Integer.parseInt(formatLocationNumbers) >= 15)) {
                        throw new IllegalArgumentException(INVALID_VALUE_IN_ROW + iteratedRows
                                                                   + INVALID_LOCATION_NUMBER);
                    }

                } catch (IllegalArgumentException e) {
                    System.out.println(e.getMessage());
                } catch (IOException e) {
                    e.printStackTrace();
                }


            });

    workbook.close();
    outStream.flush();
}

}