Create an XLSX file using POI

The following code demonstrates creating a new XLSX file with data, writing it to disk, and reading the data back from the file.

The example uses a simple Person class containing a String, a LocalDate, and an Int to produce test data.

This is the created XLSX file viewed with LibreOffice after we've written that test data to it:

After writing the file, we read its contents back in and create Person objects:

Could not parse row 0 to person
Person from file: Khaled, born 1988-03-26, pets: 1
Person from file: Hans, born 1998-09-20, pets: 2
Person from file: Alena, born 1977-01-12, pets: 0

The warning in the first line occurs since we can't convert the header row

Name     Date of Birth    Nr of Pets

to a Person object.

Here's the code taken from the repository containing the complete project:

/**
* Writes person data to an XLSX file and reads it back from the file.
*/
public class ReadWriteTests {

    public static void main(String... args) {
        var people = List.of(
                new Person("Khaled", LocalDate.of(1988, 3, 26), 1),
                new Person("Hans", LocalDate.of(1998, 9, 20), 2),
                new Person("Alena", LocalDate.of(1977, 1, 12), 0)
        );
        String xlsxFileName = System.getenv("HOME") + "/people.xlsx";
        writeToXlsxFile(people, xlsxFileName);

        List<Person> peopleFromFile = readFromXlsxFile(xlsxFileName);

        peopleFromFile.forEach(person ->
                System.out.println("Person from file: " + person));
    }

    private static List<Person> readFromXlsxFile(String xlsxFileName) {
        return getRows(new File(xlsxFileName)).stream()
                .map(row -> rowToPerson(row))
                // Remove empty Optionals
                .flatMap(Optional::stream)
                .collect(Collectors.toList());
    }

    private static Optional<Person> rowToPerson(Row row) {
        Optional<Person> personMaybe;
        try {
            String name = row.getCell(0).getStringCellValue();

            Date date = row.getCell(1).getDateCellValue();
            // Convert from Date to LocalDate
            LocalDate dateOfBirth = LocalDate.ofInstant(
                    date.toInstant(), ZoneId.systemDefault());

            int nrOfPets = (int) row.getCell(2).getNumericCellValue();

            personMaybe = Optional.of(new Person(name, dateOfBirth, nrOfPets));
        } catch (IllegalStateException ex) {
            System.err.println("Could not parse row " + row.getRowNum()
                    + " to person");
            personMaybe = Optional.empty();
        }
        return personMaybe;
    }

    private static List<Row> getRows(File xlsx) {

        var rows = new ArrayList<Row>();

        try (var workbook = new XSSFWorkbook(xlsx)) {
            // Get each row from each sheet
            workbook.forEach(sheet -> sheet.forEach(rows::add));

            // If Apache POI tries to open a non-existent file it will throw
            // an InvalidOperationException, if it's an unrecognized format
            // it will throw a NotOfficeXmlFileException.
            // We catch them all to be safe.
        } catch (Exception e) {
            System.err.println("Could not get rows from "
                    + xlsx.getAbsolutePath());
            e.printStackTrace();
        }
        return rows;
    }

    private static void writeToXlsxFile(List<Person> people, String fileName) {

        try (var fileStream = new FileOutputStream(fileName);
            var workbook = new XSSFWorkbook()
        ) {
            var sheet = workbook.createSheet("Test People Sheet");

            // Create a header row describing what the columns mean
            CellStyle boldStyle = workbook.createCellStyle();
            var font = workbook.createFont();
            font.setBold(true);
            boldStyle.setFont(font);

            var headerRow = sheet.createRow(0);
            addStringCells(headerRow,
                    List.of("Name", "Date of Birth", "Nr of Pets"),
                    boldStyle);

            // Define how a cell containing a date is displayed
            CellStyle dateCellStyle = workbook.createCellStyle();
            dateCellStyle.setDataFormat(workbook.getCreationHelper()
                    .createDataFormat()
                    .getFormat("yyyy/m/d"));

            // Add the person data as rows
            for (int i = 0; i < people.size(); i++) {
                // Add one due to the header row
                var row = sheet.createRow(i + 1);
                var person = people.get(i);
                addCells(person, row, dateCellStyle);
            }

            workbook.write(fileStream);
        } catch (IOException e) {
            System.err.println("Could not create XLSX file at " + fileName);
            e.printStackTrace();
        }
    }

    private static void addCells(Person person, Row row,
                                CellStyle dateCellStyle) {

        var classCell = row.createCell(0, CellType.STRING);
        classCell.setCellValue(person.getName());

        var dateOfBirthCell = row.createCell(1, CellType.NUMERIC);
        // Convert LocalDate to a legacy Date object
        Date dateOfBirth = Date.from(person.getDateOfBirth()
                .atStartOfDay(ZoneId.systemDefault()).toInstant());
        dateOfBirthCell.setCellValue(dateOfBirth);
        dateOfBirthCell.setCellStyle(dateCellStyle);

        var petCell = row.createCell(2, CellType.NUMERIC);
        petCell.setCellValue(person.getNrOfPets());
    }

    // Adds strings as styled cells to a row
    private static void addStringCells(Row row, List<String> strings,
                                      CellStyle style) {
        for (int i = 0; i < strings.size(); i++) {
            var cell = row.createCell(i, CellType.STRING);
            cell.setCellValue(strings.get(i));
            cell.setCellStyle(style);
        }
    }

    static class Person {
        private final String name;
        private final LocalDate dateOfBirth;
        private final int nrOfPets;

        Person(String name, LocalDate dateOfBirth, int nrOfPets) {
            this.name = name;
            this.dateOfBirth = dateOfBirth;
            this.nrOfPets = nrOfPets;
        }

        String getName() {
            return name;
        }

        LocalDate getDateOfBirth() {
            return dateOfBirth;
        }

        int getNrOfPets() {
            return nrOfPets;
        }

        @Override
        public String toString() {
            return name + ", born " + dateOfBirth + ", pets: " + nrOfPets;
        }
    }
}

Here's more on creating spreadsheets with Apache POI.