Create an XLSX file using POI

Question

I have two programs in Java: one to create and write data to an XLSX file and the other to read data from the same file.

In my first program, I used the statements below to write data to the XLSX file.

FileOutputStream prelimOut = new FileOutputStream(new File("D:\\News\\Prelim.xlsx"));
XSSFWorkbook out = new XSSFWorkbook();
XSSFSheet spreadSheet = out.createSheet("ResultSheet");

and on my drive, I've the file created as expected.

When I'm trying to read the same file from a different program with this code

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.util.Iterator;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class GetCellCount {
    public static void main(String[] args) throws IOException {
        FileInputStream input_document = new FileInputStream(new File("D:\\News\\Prelim.xlsx"));
        XSSFWorkbook my_xlsx_workbook = new XSSFWorkbook(input_document);
        XSSFSheet my_worksheet = my_xlsx_workbook.getSheetAt(0);
        Iterator<Row> rowIterator = my_worksheet.iterator();
        while (rowIterator.hasNext()) {
            Row row = rowIterator.next();
            Iterator<Cell> cellIterator = row.cellIterator();
            while (cellIterator.hasNext()) {
                Cell cell = cellIterator.next();
                switch (cell.getCellType()) {
                case Cell.CELL_TYPE_NUMERIC:
                    System.out.print(cell.getNumericCellValue() + "\t\t");
                    break;
                case Cell.CELL_TYPE_STRING:
                    System.out.print(cell.getStringCellValue() + "\t\t");
                    break;
                }
            }
            System.out.println("");
        }
        my_xlsx_workbook.close();
        input_document.close();
    }
}

it throws the below error

Exception in thread "main" org.apache.poi.POIXMLException: org.apache.poi.openxml4j.exceptions.InvalidFormatException: Package should contain a content type part [M1.13]
    at org.apache.poi.util.PackageHelper.open(PackageHelper.java:39)
    at org.apache.poi.xssf.usermodel.XSSFWorkbook.<init>(XSSFWorkbook.java:258)
    at GetCellCount.main(GetCellCount.java:14)
Caused by: org.apache.poi.openxml4j.exceptions.InvalidFormatException: Package should contain a content type part [M1.13]
    at org.apache.poi.openxml4j.opc.ZipPackage.getPartsImpl(ZipPackage.java:203)
    at org.apache.poi.openxml4j.opc.OPCPackage.getParts(OPCPackage.java:673)
    at org.apache.poi.openxml4j.opc.OPCPackage.open(OPCPackage.java:274)
    at org.apache.poi.util.PackageHelper.open(PackageHelper.java:37)
    ... 2 more

When changing the path and accessing another XLSX file (created directly in Excel), the data appears correctly.

Also, when I checked the properties of both these Excel files by right-clicking on them, I see the "Type of File" as MS Office Excel OpenXML (.xlsx), which is the same for both files.

Answer 1

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.