Bug 51616

Summary: Calling setAutoFilter triggers a problem by Office File Validation security plugin.
Product: POI Reporter: Robert Kish <robert_kish>
Component: HSSFAssignee: POI Developers List <dev>
Status: RESOLVED FIXED    
Severity: normal CC: yo.shady
Priority: P2    
Version: 3.8-dev   
Target Milestone: ---   
Hardware: PC   
OS: Windows XP   
Attachments: Program to create a file with an auto filter.
Updated program to create file with auto filter and HSSFListener record dumps.

Description Robert Kish 2011-08-04 19:22:03 UTC
Created attachment 27349 [details]
Program to create a file with an auto filter.

I create a HSSFWorkbook with a sheet. I want to specify that each of the column headings be in an auto-filter. (I would do this manually in Excel by Selecting Row 1, Data -> Filter -> Auto Filter). I issue the code to set the auto filter.

When I open the resulting XLS file using Excel 2003, the new file validation plugin provides the error message:
Office File Validation detected a problem while trying to open this file. Opening it may be dangerous.

If I ignore the error, and open the document anyway, the filter is valid.

If I rebuild the file and don't include the filter in the new version, it opens without warning message by Excel.

Therefore, there's something that POI is doing with auto filters that the Microsoft Plugin does not like.

(If I do a save as in Excel with the filter and save the document, when I reopen it, the filter is still there and there is no warning).

I have attached a demo program to create a document in XLS and XLSX format. The XLS document makes Excel 2003 complain. The XLSX document opens fine in 2003.
Comment 1 Robert Kish 2011-08-04 19:50:06 UTC
Created attachment 27350 [details]
Updated program to create file with auto filter and HSSFListener record dumps.

I have a program that implements HSSFListener to produce a dump of the records. 

test1 dumped.txt contains file with a filter.
test1x dumped.txt contains file with a filter, after Excel did a save as.

test2 dumped.txt contains file without a filter.
test2x dumped.txt contains file without a filter, after Excel did a save as.

Perhaps comparing test1 dumped.txt vs test2 dumped.txt will show all that POI does when setting auto filter.

Perhaps comparing test1 dumped.txt vs test1x dumped.txt will show what Excel does to rewrite a POI document with the same content. The closer that POI comes to creating this type of document, the more likely it is that Excel's plugin won't complain about the POI document on open.
Comment 2 Yegor Kozlov 2011-08-05 07:33:40 UTC
A similar issue has been recently reported and fixed:
https://issues.apache.org/bugzilla/show_bug.cgi?id=51481

Is the problem reproducible with the latest build from trunk? 

Yegor

(In reply to comment #1)
> Created attachment 27350 [details]
> Updated program to create file with auto filter and HSSFListener record dumps.
> 
> I have a program that implements HSSFListener to produce a dump of the records. 
> 
> test1 dumped.txt contains file with a filter.
> test1x dumped.txt contains file with a filter, after Excel did a save as.
> 
> test2 dumped.txt contains file without a filter.
> test2x dumped.txt contains file without a filter, after Excel did a save as.
> 
> Perhaps comparing test1 dumped.txt vs test2 dumped.txt will show all that POI
> does when setting auto filter.
> 
> Perhaps comparing test1 dumped.txt vs test1x dumped.txt will show what Excel
> does to rewrite a POI document with the same content. The closer that POI comes
> to creating this type of document, the more likely it is that Excel's plugin
> won't complain about the POI document on open.
Comment 3 Robert Kish 2011-08-09 15:52:46 UTC
(In reply to comment #2)
> A similar issue has been recently reported and fixed:
> https://issues.apache.org/bugzilla/show_bug.cgi?id=51481
> 
> Is the problem reproducible with the latest build from trunk? 
> 
> Yegor


I hadn't considered trying the trunk. I learned how to download and compile it today. The issue has been corrected. Please close this ticket then.

Thanks.