Read and write to spreadsheet in Java

I was writing an algorithm that would run and produce some results. These results should have been stored some place and retrieved during next program run. New values would then be added to the resulting list. The program needed to remember all the results from all run.

Read from spreadsheet

We will be reading from spreadsheet in Java using Apache POI. Since we are going to write to the same file, we are reading from, the file will be placed outside the project resource-folder. The reason for that is that we cannot easily modify files inside the resource-folder inside the JAR package.

The first step is to read the file from the project folder. File path should be provided as an absolute path. We used user.dir, which is the project folder path.

String docName = System.getProperty("user.dir")+"\\Test.xlsx";
File file = new File(docName);
FileInputStream fileInputStream = new FileInputStream(file);

Next step is to open the spreadsheet and read the first sheet:

Workbook wb = WorkbookFactory.create(fileInputStream);
Sheet sheet = wb.getSheetAt(0);

Next, we would like to know how many columns and rows does the spreadsheet hold. This is needed to avoid overflows and NullPointerExceptions during reading of the cell values.

private static int findNumberOfColsAndRows(Sheet sheet) {
    Row row;
    int cols = 0;
    int rows = sheet.getPhysicalNumberOfRows();

    for (int i = 0; i < 10 || i < rows; i++) {
        row = sheet.getRow(i);
        if (row != null) {
            int tmp = sheet.getRow(i).getPhysicalNumberOfCells();
            if (tmp > cols) cols = tmp;
        }
    }
    return rows;
}
 

Last step is to read the values from cells. We read values from two columns and put them into a HashMap. Here is the whole function:

public static Map<String, String> readValuesInTwoColumns(String docName, int keyColumn, int secondColumn) {
    Map<String, String> map = new HashMap<>();
    try {
        Workbook wb = readFile(docName);
        Sheet sheet = wb.getSheetAt(0);
        int rows = findNumberOfColsAndRows(sheet);

        for (int r = 0; r < rows; r++) {
            Row row = sheet.getRow(r);
            if (row != null) {
                String key = findValue(keyColumn, row);
                String value = findValue(secondColumn, row);

                if (key != null && value != null) {
                    map.put(key, value);
                }
            }
        }
    } catch (Exception ioe) {
        ioe.printStackTrace();
    }

    return map;
}

private static String findValue(int keyColumn, Row row) {
    Cell cell;
    cell = row.getCell(keyColumn);
    if (cell != null) {
        String tmpKey = cell.getStringCellValue();

        if (tmpKey != null && !tmpKey.isEmpty()) {
            return tmpKey;
        }
    }

    return null;
}

Write to spreadsheet

In order to write to a spreadsheet file in Java we need first to open the file. We do it in the same way as for reading, extracting the procedure into its own function readFile:

private static Workbook readFile(String docName) throws IOException, InvalidFormatException {
    File file = new File(docName);
    FileInputStream fileInputStream = new FileInputStream(file);
    return WorkbookFactory.create(fileInputStream);
}

Next step is to find how many columns and rows does the file have already. This is if we want to update the file and add the values to the sheet. Updating is always better as reading and writing operations are the most expensive ones in the application so we need to limit them as much as possible.

Now that we know the number of rows in the document we start writing our values to the next row:

int rows = findNumberOfColsAndRows(sheet);

for(int i=rows; i<valuesMap.size(); i++) {
    //some code
}

Here is the function to write the cell value:

private static void writeCellValue(Row row, int col, String value) {
    Cell cell = row.getCell(col);
    if(cell == null){
        cell = row.createCell(col);
    }
    cell.setCellValue(value);
}

At last write the updated Workbook to actual file:

public static void writeToFile(String docName, Workbook wb) {
    FileOutputStream fileOut = null;
    try {
        fileOut = new FileOutputStream(docName);
        wb.write(fileOut);
        fileOut.close();
    } catch (FileNotFoundException e) {
        e.printStackTrace();
    } catch (IOException e) {
        e.printStackTrace();
    }
}

Here is the whole code for reading from and writing to a spreadsheet in Java:

public static void addValuesInTwoColumns(String docName, int keyColumn, int secondColumn, Map<String, String> valuesMap) {
    try {
        Workbook wb = readFile(docName);
        Sheet sheet = wb.getSheetAt(0);
        int rows = findNumberOfColsAndRows(sheet);

        int r = rows;
        for (Map.Entry<String, String> entry : valuesMap.entrySet()) {
            Row row = getOrCreateRow(sheet, r);

            writeCellValue(row, keyColumn, entry.getKey());
            writeCellValue(row, secondColumn, entry.getValue());

            r++;
        }

        writeToFile(docName, wb);
    } catch (Exception ioe) {
        ioe.printStackTrace();
    }
}

private static void writeToFile(String docName, Workbook wb) {
    FileOutputStream fileOut = null;
    try {
        fileOut = new FileOutputStream(docName);
        wb.write(fileOut);
        fileOut.close();
    } catch (FileNotFoundException e) {
        e.printStackTrace();
    } catch (IOException e) {
        e.printStackTrace();
    }
}

private static Row getOrCreateRow(Sheet sheet, int r) {
    Row row = sheet.getRow(r);
    if(row == null){
        row = sheet.createRow(r);
    }
    return row;
}

private static void writeCellValue(Row row, int col, String value) {
    Cell cell = row.getCell(col);
    if(cell == null){
        cell = row.createCell(col);
    }
    cell.setCellValue(value);
}

private static int findNumberOfColsAndRows(Sheet sheet) {
    Row row;
    int cols = 0;
    int rows = sheet.getPhysicalNumberOfRows();

    for (int i = 0; i < 10 || i < rows; i++) {
        row = sheet.getRow(i);
        if (row != null) {
            int tmp = sheet.getRow(i).getPhysicalNumberOfCells();
            if (tmp > cols) cols = tmp;
        }
    }
    return rows;
}
private static Workbook readFile(String docName) throws IOException, InvalidFormatException {
    File file = new File(docName);
    FileInputStream fileInputStream = new FileInputStream(file);
    return WorkbookFactory.create(fileInputStream);
}