Bug 53691 - Workbook corruption on cloneSheet when conditional formatting is used
Summary: Workbook corruption on cloneSheet when conditional formatting is used
Alias: None
Product: POI
Classification: Unclassified
Component: HSSF (show other bugs)
Version: 3.8-FINAL
Hardware: PC All
: P2 normal (vote)
Target Milestone: ---
Assignee: POI Developers List
Depends on:
Reported: 2012-08-10 11:18 UTC by rra
Modified: 2014-05-20 14:03 UTC (History)
1 user (show)

Sample Code (1.03 KB, application/octet-stream)
2012-08-10 11:18 UTC, rra
Original sheet using conditional formatting (24.00 KB, application/vnd.ms-excel)
2012-08-10 11:19 UTC, rra
Example output causing error in Excel (18.50 KB, application/vnd.ms-excel)
2012-08-10 11:20 UTC, rra

Note You need to log in before you can comment on or make changes to this bug.
Description rra 2012-08-10 11:18:06 UTC
Created attachment 29207 [details]
Sample Code

I'm getting an error when opening a spreadsheet processed by POI in MS Excel "Protected View Office has detected a problem with this file. Editing it may harm your computer". 

This happens whenever I try to use the HSSFWorkBook's cloneSheet method on a sheet that's using conditional formatting.

Please find sample code that reproduces this behaviour attached. This code opens the orig.xls, clones the first sheet and then saves to another file. The orig.xls consists of only 2 cells that have conditional formatting defined. When removing the conditional formatting, the cloning does not cause any problems.
Comment 1 rra 2012-08-10 11:19:21 UTC
Created attachment 29208 [details]
Original sheet using conditional formatting
Comment 2 rra 2012-08-10 11:20:01 UTC
Created attachment 29209 [details]
Example output causing error in Excel
Comment 3 Evgeniy Berlog 2012-08-12 19:08:02 UTC
Hi, I have checked your output file in MS Excel 2007. I have got no errors when I opened this file. Please, say what version of MS Excel you use.

Regards, Evgeniy
Comment 4 rra 2012-08-13 08:48:49 UTC

I'm using Excel 2010 Home and Business (Version 14.0.6112.5000, 32bit). 

I've tried on a Excel 2010 Starter editon as well which produces the same error.

Best Regards
Comment 5 Corey Teffetalor 2012-09-28 23:59:41 UTC
I can confirm this problem with some additional info:

I believe Excel does not alert you to this problem prior to Excel 2010 unless the following (Optional?) security update is installed: http://support.microsoft.com/kb/2501584

You can click through the error, and the conditional formatting still appears to work properly.

The issue is specifically with how copying conditional formatting is done, if you clone the sheet with conditional formatting, and then remove all conditional formatting on the cloned sheet using HSSFSheetConditionalFormatting.removeConditionalFormatting(int) before writing the result. The resulting spreadsheet does not cause any Excel alerts, and all other data is copied.

Then, if you attempt to re-copy the conditional formatting using:

HSSFSheetConditionalFormatting cf = sheet.getSheetConditionalFormatting();
for (int j = 0; j < scf.getNumConditionalFormattings(); j++) {

The error when loading the spreadsheet returns.

Tested on POI 3.7 3.8 and the latest binary nightly of 3.9beta1

Possibly an issue with how the copy is done in addConditionalFormatting?

Don't have this boiled down to a small test case yet, but can update with one if more test-cases are desired.

Comment 6 Dominik Stadler 2014-05-20 14:03:16 UTC
I took some time to investigate this, still happens on latest trunk (post 3.10-FINAL).

After some digging, I found the actual cause, unfortunately BiffViewer did not show details for exactly the one record that was the cause of the problem. 

After all it turned out to be a simple copy/paste error in CFRuleRecord that is easily fixed.

So this is finally fixed now via r1596251 together with some enhancements to toString() to make BiffViewer print out more information for these records.