Bug 66436 - After the .xlsx file is encrypted by poi and then decrypted by POI, an error will be reported when it is opened with Microsoft Excel, and an error will also be reported when it is decompressed with commons-compress
Summary: After the .xlsx file is encrypted by poi and then decrypted by POI, an error ...
Status: NEW
Alias: None
Product: POI
Classification: Unclassified
Component: POIFS (show other bugs)
Version: 5.2.2-FINAL
Hardware: PC All
: P2 normal (vote)
Target Milestone: ---
Assignee: POI Developers List
URL:
Keywords:
Depends on:
Blocks:
 
Reported: 2023-01-20 06:59 UTC by Tom Xin
Modified: 2023-01-21 15:06 UTC (History)
0 users


Attachments
Test code to demonstrate the issue (3.25 KB, text/plain)
2023-01-20 06:59 UTC, Tom Xin 		Details
Excel file created by POI (3.22 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2023-01-20 12:06 UTC, Tom Xin 		Details
Excel file created by POI then encrypted by POI.Passwoword:foobaa (8.00 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2023-01-20 12:09 UTC, Tom Xin 		Details
Excel file created by POI, then encrypted by POI, then decrypted by POI (3.22 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2023-01-20 12:11 UTC, Tom Xin 		Details
Excel file created and encrypted by Microsoft Excel.Password:foobaa (15.00 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2023-01-20 12:31 UTC, Tom Xin 		Details
Excel file created and encrypted by Microsoft Excel,then decrypted by POI (8.67 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2023-01-20 12:33 UTC, Tom Xin 		Details
Test code2 to demonstrate the issue (4.39 KB, text/plain)
2023-01-20 12:34 UTC, Tom Xin 		Details
Test code3 (4.10 KB, text/plain)
2023-01-20 15:59 UTC, Tom Xin 		Details
Exception stacktrace (8.17 KB, text/plain)
2023-01-20 16:14 UTC, Tom Xin 		Details
Excel file hexadecimal comparison using BinEd (164.82 KB, image/png)
2023-01-21 04:27 UTC, Tom Xin 		Details
View All Add an attachment (proposed patch, testcase, etc.)

Note You need to log in before you can comment on or make changes to this bug.
Description Tom Xin 2023-01-20 06:59:05 UTC 
Created attachment 38474 [details]
Test code to demonstrate the issue

The encrypted excel created by Microsoft Excel can be opened normally after being decrypted by POI. However, the encrypted Excel created by POI cannot be opened normally by Microsoft Excel after being decrypted by POI.
Comment 1 PJ Fanning 2023-01-20 11:46:30 UTC 
If this was just broken, we would have loads of people reporting it.

There must be something specific in your use case.

* could you provide details of which Excel versions you are testing with (including the OS)?
* could you provide the original file used in your test code?
Comment 2 Tom Xin 2023-01-20 12:06:59 UTC 
Created attachment 38475 [details]
Excel file created by POI
Comment 3 Tom Xin 2023-01-20 12:09:10 UTC 
Created attachment 38476 [details]
Excel file created by POI then encrypted by POI.Passwoword:foobaa
Comment 4 Tom Xin 2023-01-20 12:11:31 UTC 
Created attachment 38477 [details]
Excel file created by POI, then encrypted by POI, then decrypted by POI
Comment 5 Tom Xin 2023-01-20 12:16:49 UTC 
(In reply to PJ Fanning from comment #1)
> If this was just broken, we would have loads of people reporting it.
> 
> There must be something specific in your use case.
> 
> * could you provide details of which Excel versions you are testing with
> (including the OS)?
> * could you provide the original file used in your test code?

OS:Windows 10 Home Edition
JDK：azul-1.8
Office:Microsoft Office LTSC Professional Plus 2021
POI:I have tested all versions of POI from 5.0.0 to 5.2.3 and have this problem
Comment 6 Tom Xin 2023-01-20 12:31:55 UTC 
Created attachment 38478 [details]
Excel file created and encrypted by Microsoft Excel.Password:foobaa
Comment 7 Tom Xin 2023-01-20 12:33:10 UTC 
Created attachment 38479 [details]
Excel file created and encrypted by Microsoft Excel,then decrypted by POI
Comment 8 Tom Xin 2023-01-20 12:34:23 UTC 
Created attachment 38480 [details]
Test code2 to demonstrate the issue
Comment 9 Tom Xin 2023-01-20 12:40:39 UTC 
I created new test code to better reproduce the issue, see the attached file titled 'Test code2 to demonstrate the issue'. I also uploaded all attachments used/produced in the code.
Comment 10 PJ Fanning 2023-01-20 13:57:41 UTC 
I used Excel on office.com and xtemp_excel_microsoftoffice_encrypted_poi_decrypted.xlsx opens fine.

I don't have any other access to Excel.
Comment 11 Tom Xin 2023-01-20 14:12:16 UTC 
(In reply to PJ Fanning from comment #10)
> I used Excel on office.com and
> xtemp_excel_microsoftoffice_encrypted_poi_decrypted.xlsx opens fine.
> 
> I don't have any other access to Excel.

Maybe you didn't understand the problem correctly, the file that can't be opened normally is the fourth attachment titled "Excel file created by POI, then encrypted by POI, then decrypted by POI "
Comment 12 PJ Fanning 2023-01-20 14:19:23 UTC 
that is the file I tested - successfully
Comment 13 Tom Xin 2023-01-20 15:59:11 UTC 
Created attachment 38481 [details]
Test code3
Comment 14 Tom Xin 2023-01-20 16:13:05 UTC 
(In reply to Tom Xin from comment #13)
> Created attachment 38481 [details]
> Test code3

In addition, when parsing the document encrypted and decrypted by POI through commoms-compress, an exception will be thrown: 
java.io.IOException: Error on ZipFile D:\temp_excel_poi_decrypt.xlsx 

See the attached "Test code3" for the relevant code, and the attached "Exception stacktrace" for the error stack information
Comment 15 Tom Xin 2023-01-20 16:14:34 UTC 
Created attachment 38482 [details]
Exception stacktrace
Comment 16 Tom Xin 2023-01-21 04:27:20 UTC 
Created attachment 38483 [details]
Excel file hexadecimal comparison using BinEd
Comment 17 Tom Xin 2023-01-21 04:33:23 UTC 
Comment on attachment 38483 [details]
Excel file hexadecimal comparison using BinEd

I used BinEd to compare the Excel files and found that the data in the last row of them is different. So there is reason to suspect that there is an error in POI's handling of the data stream when decrypting the file.