java 使用 POI 创建 XLSX 文件

Create an XLSX file using POI

提问by user3872094

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.

我有两个 Java 程序：一个用于创建数据并将数据写入 XLSX 文件，另一个用于从同一文件读取数据。

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

在我的第一个程序中，我使用以下语句将数据写入 XLSX 文件。

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.

更改路径并访问另一个 XLSX 文件（直接在 Excel 中创建）时，数据显示正确。

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.

此外，当我通过右键单击这两个 Excel 文件来检查它们的属性时，我看到“文件类型”为MS Office Excel OpenXML (.xlsx)，这对于两个文件都是相同的。

回答by Matthias Braun

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

以下代码演示了如何使用数据创建一个新的 XLSX 文件，将其写入磁盘，然后从文件中读回数据。

The example uses a simple Personclass containing a String, a LocalDate, and an Intto produce test data.

该示例使用一个Person包含 a String、 aLocalDate和 an的简单类Int来生成测试数据。

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

这是在我们将测试数据写入其中后，使用 LibreOffice 查看创建的 XLSX 文件：

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

写入文件后，我们读回其内容并创建Person对象：

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 Personobject.

到一个Person对象。

Here's the code taken from the repositorycontaining 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 moreon creating spreadsheets with Apache POI.

以下是有关使用 Apache POI 创建电子表格的更多信息。