Apache POI - 读取修改excel文件

问题描述

每当我使用Apatche POI打开excel文件时，文件都会被修改，即使我只是在阅读文件而没有进行任何修改。

Whenever I open a excel file using the Apatche POI the file gets modified, even though I'm just reading the file and not making any modification.

实例这样的测试代码。

public class ApachePoiTest {

    @Test
    public void readingShouldNotModifyFile() throws Exception {
        final File testFile = new File("C:/work/src/test/resources/Book2.xlsx");
        final byte[] originalChecksum = calculateChecksum(testFile);
        Assert.assertTrue("Calculating checksum modified file",
            MessageDigest.isEqual(originalChecksum, calculateChecksum(testFile)));
        try (Workbook wb = WorkbookFactory.create(testFile)) {
            Assert.assertNotNull("Reading file with Apache POI", wb);
        }
        Assert.assertTrue("Reading file with Apache POI modified file",
            MessageDigest.isEqual(originalChecksum, calculateChecksum(testFile)));
    }

    @Test
    public void readingInputStreamShouldNotModifyFile() throws Exception {
        final File testFile = new File("C:/work/src/test/resources/Book2.xlsx");
        final byte[] originalChecksum = calculateChecksum(testFile);
        Assert.assertTrue("Calculating checksum modified file",
            MessageDigest.isEqual(originalChecksum, calculateChecksum(testFile)));
        try (InputStream is = new FileInputStream(testFile); Workbook wb = WorkbookFactory.create(is)) {
            Assert.assertNotNull("Reading file with Apache POI", wb);
        }
        Assert.assertTrue("Reading file with Apache POI modified file",
            MessageDigest.isEqual(originalChecksum, calculateChecksum(testFile)));
    }

    private byte[] calculateChecksum(final File file) throws Exception {
        final MessageDigest md = MessageDigest.getInstance("MD5");
        md.reset();
        try (InputStream is = new FileInputStream(file)) {
            final byte[] bytes = new byte[2048];
            int numBytes;
            while ((numBytes = is.read(bytes)) != -1) {
                md.update(bytes, 0, numBytes);
            }
            return md.digest();
        }
    }
}

测试 readingShouldNotModifyFile 总是失败，因为该文件总是被Apache POI修改。在使用MS Office新创建的空白excel文件上进行测试时，Apache POI会将文件从8.1 kb切换到6.2 kb并破坏文件。

Test readingShouldNotModifyFile always fails, because the file gets always modified by Apache POI. More to it when testing on a blank excel file freshly created with MS Office, Apache POI cuts the file from 8.1 kb to 6.2 kb and corrupts the file.

测试用：

<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>3.15</version>
</dependency>

以及版本3.12

可以我阻止Apache POI通过其他方式修改我的文件，然后传递 InputStream 而不是文件。我不想传递 InputStream ，因为我担心Apache会警告它需要更多内存并且对 InputStream有一些特定要求。

Can I prevent Apache POI from modifying my files by other means then passing InputStream instead of File. I don't want to pass InputStream because I'm concerned about Apache's warning that it takes more memory and has some specific requirements to the InputStream.

推荐答案

你的问题是你没有传入readonly标志，所以Apache POI是默认的打开文件读/写。

Your problem is that you're not passing in the readonly flag, so Apache POI is defaulting to opening the file read/write.

你需要使用 + set readonly flag to true

You need to use the overloaded WorkbookFactory.create method which takes a readonly flag + set that readonly flag to true

更改行

try (InputStream is = new FileInputStream(testFile); Workbook wb = WorkbookFactory.create(is)) {

to

try (IWorkbook wb = WorkbookFactory.create(testFile,null,true)) {

和你的文件将以只读方式打开，无需更改

and your file will be opened read-only with no changes

这篇关于Apache POI - 读取修改excel文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！