POI 无法打开在 Excel 中打开的工作簿 [英] POI cannot open a workbook that opens in Excel

问题描述

当我尝试在 POI 中打开 .xlsx 文件时，出现异常:

java.lang.IllegalArgumentException:提供的POIFSFileSystem 不包含BIFF8工作簿"条目.真的是excel文件吗?在 org.apache.poi.hssf.usermodel.HSSFWorkbook.getWorkbookDirEntryName(HSSFWorkbook.java:223)在 org.apache.poi.hssf.usermodel.HSSFWorkbook.(HSSFWorkbook.java:245)在 org.apache.poi.hssf.usermodel.HSSFWorkbook.(HSSFWorkbook.java:188)

我注意到代码将其视为 .xls 文件，即使名称是 .xlsx，我正在使用 WorkbookFactory.create(fileInputStream); 打开文件.

我尝试将文件重命名为 .zip 并在 WinZip 中打开，但出现错误 - zip 文件无效.

文件确实在 Excel 中打开，如果我保存它(不做任何更改)，那么它也会在 POI 中正确打开.

解决方案

根据你所说的，你有一个 .xlsx 文件，但它在没有工作簿条目的 HSSF 中出现，我将推断您有一个加密的 .xlsx 文件

基本上有 4 种处理 .xls 或 .xlsx 文件的方法:

• 未加密的 .xls 文件 - OLE2 存储，适用于 HSSF
• 加密的 .xlsx 文件 - OLE2 存储，某些记录已加密，如果您提供密码，则可与 HSSF 一起使用
• 未加密的 .xlsx 文件 - OOXML(xml 压缩文件)存储，适用于 XSSF
• 加密的 .xlsx 文件 - 存储在 OLE2 中，其中包含加密部分，它包装了 OOXML，它......最终与 XSSF 一起使用(很讨厌)

所以，我认为正在发生的是您正在检查文件的类型，看到它是 OLE2，然后将其传递给 HSSF.HSSF 寻找它所使用的位，看不到它们，然后放弃

您需要做的是按照 POI 加密文档页面上的说明操作.基本上，您的代码需要类似于:

EncryptionInfo info = new EncryptionInfo(filesystem);解密器 d = Decryptor.getInstance(info);尝试 {如果(！d.verifyPassword(密码)){throw new RuntimeException("无法处理:文档已加密");}InputStream dataStream = d.getDataStream(filesystem);XSSFWorkbook wb = new XSSFWorkbook(dataStream);//在这里处理 XLSX 文件} catch (GeneralSecurityException ex) {throw new RuntimeException("无法处理加密文档", ex);}

如果您使用最新版本的 POI，如果您将加密的 .xlsx 文件提供给 HSSF，它现在会抛出 EncryptedDocumentException，这样您就可以更轻松地找出出错的地方>

When I try to open a .xlsx file in POI, I get an exception:

java.lang.IllegalArgumentException: The supplied POIFSFileSystem does not contain a BIFF8 'Workbook' entry. Is it really an excel file?
    at org.apache.poi.hssf.usermodel.HSSFWorkbook.getWorkbookDirEntryName(HSSFWorkbook.java:223)
    at org.apache.poi.hssf.usermodel.HSSFWorkbook.<init>(HSSFWorkbook.java:245)
    at org.apache.poi.hssf.usermodel.HSSFWorkbook.<init>(HSSFWorkbook.java:188)

I notice that the code is considering it a .xls file even though the name is .xlsx, and I am using WorkbookFactory.create(fileInputStream); to open the file.

I tried renaming the file to .zip and opening in WinZip and I get an error - invalid zip file.

The file does open in Excel, and if I save it (without making a single change), then it opens correctly in POI as well.

解决方案

From what you say, which is that you have a .xlsx file but it's turning up in HSSF with no Workbook entry, I'm going to deduce that you have an Encrypted .xlsx file

There are basically 4 ways of handling a .xls or .xlsx file:

• Unencrypted .xls file - OLE2 storage, works with HSSF
• Encrypted .xlsx file - OLE2 storage, some records encrypted, works with HSSF if you supply the password
• Unencrypted .xlsx file - OOXML (zip of xml) storage, works with XSSF
• Encrypted .xlsx file - Stored in OLE2, which holds encrypted part, which wraps OOXML, which ..... which finally works with XSSF (it's nasty)

So, what I think is happening is that you're checking the type of the file, seeing it's OLE2, then passing that to HSSF. HSSF looks for the bits it works with, can't see them, and gives up

What you need to do is follow the instructions on the POI Encrypted Documents page. Basically, your code needs to be something like:

EncryptionInfo info = new EncryptionInfo(filesystem);
Decryptor d = Decryptor.getInstance(info);

try {
    if (!d.verifyPassword(password)) {
        throw new RuntimeException("Unable to process: document is encrypted");
    }

    InputStream dataStream = d.getDataStream(filesystem);
    XSSFWorkbook wb = new XSSFWorkbook(dataStream);

    // Process XLSX file here
} catch (GeneralSecurityException ex) {
    throw new RuntimeException("Unable to process encrypted document", ex);
}

If you use the latest version of POI, it'll now throw an EncryptedDocumentException if you give an encrypted .xlsx file to HSSF, so you can work out more easily where you went wrong

这篇关于POI 无法打开在 Excel 中打开的工作簿的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！