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.
Created attachment 29208 [details] Original sheet using conditional formatting
Created attachment 29209 [details] Example output causing error in Excel
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
Hello, 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 Ralph
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++) { cf.addConditionalFormatting(scf.getConditionalFormattingAt(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. Thanks, --Corey
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.