I know almost nothing about threads, synchronization, …

That being said, I am working on a record handler, that hits Excel sheet to pull/push data. There exist, in my code base, a BaseRecordHandler, which use the entire spreadsheet to read from/write to. The game plan with it is as follows:

• create singleton instance of the record handler
• upon creation, we open the file, which does the following:
  • initializes the spreadsheet on the disk if not exists
  • extracts the records from the spreadsheet to a Collection (by default, this is the list usedRecords)
  • in case of any exceptions, we close everything
• when we handle() a record, we simply write to the usedRecords
• on close() we do the following:
  • write the newly-added usedRecords to the spreadsheet
  • close everything

Now I need to come up with a way to write record handler that works on just one Sheet, of which I can have multiple types, that work on the same containing spreadsheet…

BaseRecordHandler looks something like this:

package com.xxx.utils

// imports

public abstract class BaseRecordHandler<T> implements AutoCloseable {
    protected final String dataSourceFilename;

    protected final int firstRowNumber = 1;

    protected List<T> usedRecords = [];

    private FileInputStream inputStream;
    protected Workbook excelFile;
    protected XSSFSheet sheet;

    protected BaseRecordHandler() {
        this.init();
    }

    protected BaseRecordHandler(String dataSourceFilename) {
        this.dataSourceFilename = dataSourceFilename;
        this.init();
    }

    protected void init() {
        this.open();
    }

    public void open() {
        try {
            this.initDataSourceFile();

            this.inputStream = new FileInputStream(this.dataSourceFilename);
            this.excelFile = WorkbookFactory.create(this.inputStream);

            this.sheet = this.excelFile.getSheetAt(this.excelFile.getActiveSheetIndex());

            if (this.usedRecords.isEmpty()) {
                for (int rowNum = this.firstRowNumber; rowNum <= this.sheet.getLastRowNum(); rowNum++) {
                    this.extractRecordFromSheet(rowNum);
                }
            }
        } catch (IOException ex) {
            this.close();
        }
    }

    protected void initDataSourceFile() throws IOException {
        File file = new File(this.dataSourceFilename);
        this.excelFile = this.getWorkbook(file);

        if (!file.exists()) {
            file.getParentFile().mkdirs();
        }

        if (this.spreadsheetNeedsSetUp()) {
            FileOutputStream outputStream = new FileOutputStream(file);

            this.setupSpreadsheet({ Sheet sheet ->
                Row firstRow = sheet.createRow(0);

                this.setupFirstRow(firstRow);

                sheet.createFreezePane(0, 1);
            });

            this.excelFile.write(outputStream);
            outputStream.close();
            if (this.inputStream != null) {
                this.inputStream.close();
                this.inputStream = null;
            }
            this.excelFile.close();
        }
    }

    private Workbook getWorkbook(File file) {
        if (file.exists()) {
            this.inputStream = new FileInputStream(file);
            return WorkbookFactory.create(this.inputStream);
        }
        // NOTE: this is here only because the version of POI that Katalon Studio uses by default, doesn't support factory creation of Excel file from scratch...
        return new XSSFWorkbook();
    }

    protected boolean spreadsheetNeedsSetUp() {
        return !(new File(this.dataSourceFilename).exists());
    }

    /**
     * @param Closure onSetupSheet
     */
    protected void setupSpreadsheet(Closure onSetupSheet) {
        this.createSheets()
                .forEach(onSetupSheet);
    }

    protected List<Sheet> createSheets() {
        if (this.excelFile.getNumberOfSheets() == 0)
            this.excelFile.createSheet();
        return [this.excelFile.getSheetAt(0)];
    }

    protected abstract void setupFirstRow(Row firstRow);

    protected abstract void extractRecordFromSheet(int rowNum);

    public void handle(T record) {
        this.usedRecords.push(record);
    }

    protected void writeToFile() {
        if (!new File(this.dataSourceFilename).exists())
            return;

        if (this.excelFile == null)
            this.open();

        this.writeRecordsToFile();

        FileOutputStream outputStream = new FileOutputStream(this.dataSourceFilename);
        try {
            this.excelFile.write(outputStream);
        } finally {
            outputStream.close();
        }
    }

    protected void writeRecordsToFile() {
        for (int j = this.sheet.getLastRowNum(); j < this.usedRecords.size(); j++) {
            Row row = this.sheet.createRow(j + this.firstRowNumber);
            this.fillInRow(row, this.usedRecords[j]);
        }
    }

    @Override
    public void close() throws Exception {
        this.writeToFile();
        this.closeStreams();
    }

    protected void closeStreams() {
        this.inputStream.close();
        this.excelFile.close();

        // resetting state of streams for next use
        this.excelFile = null;
        this.inputStream = null;
    }

    protected abstract void fillInRow(Row row, T record);
}

So far, so good. But what about that BaseSheetHandler ?

It is implemneted as follows:

package com.xxx.utils

// imports

@InheritConstructors
public abstract class BaseSheetHandler<T> extends BaseRecordHandler<T> {
    protected abstract String getSheetName();

    @Override
    protected void initDataSourceFile() throws IOException {
        super.initDataSourceFile();
        new File(this.dataSourceFilename).withInputStream({ is ->
            this.excelFile = WorkbookFactory.create(is);
            this.setupSpreadsheet { Sheet sheet -> excelFile.setActiveSheet(excelFile.getSheetIndex(sheet)) }
            this.excelFile.close();
        })
    }

    @Override
    protected void setupSpreadsheet(Closure onSetupSheet) {
        Sheet sheet = this.excelFile.getSheet(this.getSheetName());
        if (sheet == null)
            sheet = this.excelFile.createSheet(this.getSheetName());

        onSetupSheet(sheet);
    }

    @Override
    protected boolean spreadsheetNeedsSetUp() {
        return super.spreadsheetNeedsSetUp() ||
                (this.excelFile.getSheet(this.getSheetName()) == null);
    }
}

I go to test it, by creating some PracticeURLHandler and OrganizationNameHandler from the BaseSheetHandler that points to the same excel filename and simply implements the abstract methods, both handle some PracticeProfile that has practiceURL, practiceName, and organizationName, test it like so:

PracticeProfile profile1 = new PracticeProfile(someOrganizationName, somePracticeName, somePracticeURL);

PracticeURLHandler.GetInstance().handle(profile1);
OrganizationNameHandler.GetInstance().handle(profile1);

PracticeURLHandler.GetInstance().close();

WebUI.verifyEqual(OrganizationNameHandler.GetInstance().sheet.getSheetName(), "Practice Organization Names")
WebUI.verifyEqual(OrganizationNameHandler.GetInstance().excelFile.getSheet("Practice URLs").getRow(1).getCell(1).getStringCellValue(), 
        "example.com")

OrganizationNameHandler.GetInstance().close();

The verification seems to fail because, by the time the PracticeURLHandler closes, the OrganizationNameHandler doesn’t have that updated excelFile, nor any of the streams thereof reflecting what the practice URL handler just did!

I try to address this on the BaseSheetHandler, via this attempt to “sync” the excelFile:

@Override
        // TODO: I don't think this is working
        protected void writeToFile() {
            this.syncExcelFile();
            super.writeToFile();
        }
    
        // TODO: I don't think this is working
        protected void syncExcelFile() {
            this.closeStreams();
            this.open();
        }

I go to re-run the test, from clean slate. The second assert fails this time, too, for the same reason! Indeed, when I go to check the excel spreadsheet manually, I see that the practice URL record got written to the practice organization names spreadsheet, and not the other one. Somehow, the practice URL handler’s excel file is out of sync!!

What are the best strategies/design patterns/tools/… to make sure that any modification we do on one instance of the BaseSheetHandler, modify only the up-to-date version of the excelFile (and get updated sheet, inputStream, …)?