Quantcast

POI from Excel 2000 to Excel 2010 issues.

classic Classic list List threaded Threaded
9 messages Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

POI from Excel 2000 to Excel 2010 issues.

jamestthomas
We have a website built on Java and WebSphere.  We offer a spread sheet download which contains macros and data retrieved from the site.  I have used POI to populate the sheet with success.  First I used POI 3.5 beta and now I have plugged in 3.7 instead.  Up to now we have supported Excel 2000.  We are moving to Office 2010 and the download no longer works as well.  Now when opening the downloaded spread sheet there is a 4-step process where as before users simply clicked enable macros.  In order Users get
1. Excel has detected a problem with this file.  Opening may be dangerous.  
   Click Open
2. Users see a Red Banner, Protected View - Office has detected a problem...Editing it may harm your computer. Click for more details.
   Click on red bar
3. Document is in protected View.. same warnings, option to Edit Anyway.
   Click Edit Anyway
4. Security warning - some active content has been disabled.  Click for more details.  Enable Content Link
   Click Enable Content
Finally, the sheet can be opened and used.  It works correctly.  The tool Microsoft offers to examine VBA code found issues but they seem to be red herrings as they point to objects we don't use.

I do not want to rewrite this process if it works but I must do something to get rid of the barrage of messages.  I will rewrite it if need be.  AS AN ASIDE, Just downloading the file from web-content one has to only go through the Enable Content
step.  However we have to process the file adding data to it.  It seems this processing and the downloading triggers all these messages.  Any help at all on this would be appreciated.
Is there anything specific I need to change or look out for?  Is there a better API?
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: POI from Excel 2000 to Excel 2010 issues.

Nick Burch-11
On Mon, 31 Jan 2011, jamestthomas wrote:
> Up to now we have supported Excel 2000. We are moving to Office 2010 and
> the download no longer works as well.  Now when opening the downloaded
> spread sheet there is a 4-step process where as before users simply
> clicked enable macros.

Office 2010 is stricter than Office 2000 was. You'll need to identify what
POI is doing which is upsetting this newer excel. First up, see if you can
get Excel to tell you what part of the file it objects to?

Failing that, try:
* open template in poi, save without changes, does excel like this?
* create an empty template in excel (no macros, no/minimal data), then
   populate it with your application, does excel like this?
* what is the smallest/simplest file that excel objects to?
* and what parts do you need to leave out of that simple file to make
   excel like it?

Hopefully the answer to those questions will help you identify which
record(s) poi is writing out in a manner that excel 2010 doesn't like, and
from there it's with any luck a quick bug fix

Nick

---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: POI from Excel 2000 to Excel 2010 issues.

jamestthomas
Thank you Nick, I had just tried your first suggestion before I got your email and still had to go through the multiple warnings.  I like your suggestions and will continue along and report back.  I use the POIFSFileSystem object to copy the template.  I was wondering if there is another object I could try.  Will be digging around and reporting my findings.  Thanks for your response.  - Jim



----- Original Message -----
From: "Nick Burch-11 [via Apache POI]" <[hidden email]>
To: "jamestthomas" <[hidden email]>
Sent: Monday, January 31, 2011 2:40:02 PM
Subject: Re: POI from Excel 2000 to Excel 2010 issues.

On Mon, 31 Jan 2011, jamestthomas wrote:
> Up to now we have supported Excel 2000. We are moving to Office 2010 and
> the download no longer works as well.  Now when opening the downloaded
> spread sheet there is a 4-step process where as before users simply
> clicked enable macros.

Office 2010 is stricter than Office 2000 was. You'll need to identify what
POI is doing which is upsetting this newer excel. First up, see if you can
get Excel to tell you what part of the file it objects to?

Failing that, try:
* open template in poi, save without changes, does excel like this?
* create an empty template in excel (no macros, no/minimal data), then
   populate it with your application, does excel like this?
* what is the smallest/simplest file that excel objects to?
* and what parts do you need to leave out of that simple file to make
   excel like it?

Hopefully the answer to those questions will help you identify which
record(s) poi is writing out in a manner that excel 2010 doesn't like, and
from there it's with any luck a quick bug fix

Nick

---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]




If you reply to this email, your message will be added to the discussion below:
http://apache-poi.1045710.n5.nabble.com/POI-from-Excel-2000-to-Excel-2010-issues-tp3365053p3365100.html
To unsubscribe from POI from Excel 2000 to Excel 2010 issues., click here.
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: POI from Excel 2000 to Excel 2010 issues.

Nick Burch-11
On Mon, 31 Jan 2011, jamestthomas wrote:
> I use the POIFSFileSystem object to copy the template . I was wondering
> if there is another object I could try.

Not sure I follow you there. If you just want to copy one file to another,
simply use something like the File object.

If you mean you create a HSSFWorkbook using a POIFSFileSystem constructor,
then that's your only way, yes. Any problem is likely to be with the low
level records that HSSF writes, rather than anything in the POIFS layer

Nick

---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: POI from Excel 2000 to Excel 2010 issues.

jamestthomas
Nick,
>If you mean you create a HSSFWorkbook using a POIFSFileSystem constructor,
>then that's your only way, yes. Any problem is likely to be with the low
>level records that HSSF writes, rather than anything in the POIFS layer

Yes, this is what I meant.  If there is a problem with those low level records written by HSSF, I am not sure how I can identify the problem.  I have found that by simply downloading the template, which includes sample data and all the macros, from webContent, it opens fine. Currently I have been simplifying the macro to no avail.  Also, on the server side, in the Java,   I removed all code that writes any additional data to the file so I am simply creating it.
POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(RTC_global.templatePath + LibraryData.SBOM_IMPORT_MACRO_TEMPLATE));
            wb = new HSSFWorkbook(fs);
Then  writing it out
bos = new BufferedOutputStream(res.getOutputStream());
       
            wb.write(bos);
            bos.close();
No luck with this.  It seems POI is not fully compatible with Office 2010 and there is an issue with low level differences.  I have a work around for our users whcih is to download to a "trusted Location".  Do you know if adding a certificate of some sort to the template might replicate downloading to a trusted location?  The file, despite all the dire warnings and the obvious difference noticed by Excel, does still work when opened.  We are trying to make this seamless for our users.  Any other thoughts or suggestions are most welcome.

Jim Thomas


----- Original Message -----
From: "Nick Burch-11 [via Apache POI]" <[hidden email]>
To: "jamestthomas" <[hidden email]>
Sent: Tuesday, February 1, 2011 7:04:04 AM
Subject: Re: POI from Excel 2000 to Excel 2010 issues.

On Mon, 31 Jan 2011, jamestthomas wrote:
> I use the POIFSFileSystem object to copy the template . I was wondering
> if there is another object I could try.

Not sure I follow you there. If you just want to copy one file to another,
simply use something like the File object.

If you mean you create a HSSFWorkbook using a POIFSFileSystem constructor,
then that's your only way, yes. Any problem is likely to be with the low
level records that HSSF writes, rather than anything in the POIFS layer

Nick

---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]




If you reply to this email, your message will be added to the discussion below:
http://apache-poi.1045710.n5.nabble.com/POI-from-Excel-2000-to-Excel-2010-issues-tp3365053p3366071.html
To unsubscribe from POI from Excel 2000 to Excel 2010 issues., click here.
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: POI from Excel 2000 to Excel 2010 issues. (Edited out previous)

jamestthomas
In reply to this post by Nick Burch-11
Nick,
>If you mean you create a HSSFWorkbook using a POIFSFileSystem constructor,
>then that's your only way, yes. Any problem is likely to be with the low
>level records that HSSF writes, rather than anything in the POIFS layer

Yes, this is what I meant.  If there is a problem with those low level records written by HSSF, I am not sure how I can identify the problem.  I have found that by simply downloading the template, which includes sample data and all the macros, from webContent, it opens fine. Currently I have been simplifying the macro to no avail.  Also, on the server side, in the Java,   I removed all code that writes any additional data to the file so I am simply creating it.
POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(RTC_global.templatePath + LibraryData.SBOM_IMPORT_MACRO_TEMPLATE));
            wb = new HSSFWorkbook(fs);
Then  writing it out
bos = new BufferedOutputStream(res.getOutputStream());
       
            wb.write(bos);
            bos.close();
No luck with this.  It seems POI is not fully compatible with Office 2010 and there is an issue with low level differences.  I have a work around for our users whcih is to download to a "trusted Location".  Do you know if adding a certificate of some sort to the template might replicate downloading to a trusted location?  The file, despite all the dire warnings and the obvious difference noticed by Excel, does still work when opened.  We are trying to make this seamless for our users.  Any other thoughts or suggestions are most welcome.

Jim Thomas


If you reply to this email, your message will be added to the discussion below:
http://apache-poi.1045710.n5.nabble.com/POI-from-Excel-2000-to-Excel-2010-issues-tp3365053p3366071.html
To unsubscribe from POI from Excel 2000 to Excel 2010 issues., click here.
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: POI from Excel 2000 to Excel 2010 issues. (Edited out previous)

David Fisher
Hi Jim,

>> If you mean you create a HSSFWorkbook using a POIFSFileSystem constructor,
>> then that's your only way, yes. Any problem is likely to be with the low
>> level records that HSSF writes, rather than anything in the POIFS layer
>
> Yes, this is what I meant. If there is a problem with those low level records written by HSSF, I am not sure how I can identify the problem. I have found that by simply downloading the template, which includes sample data and all the macros, from webContent, it opens fine. Currently I have been simplifying the macro to no avail. Also, on the server side, in the Java, I removed all code that writes any additional data to the file so I am simply creating it.
> POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(RTC_global.templatePath + LibraryData.SBOM_IMPORT_MACRO_TEMPLATE));
> wb = new HSSFWorkbook(fs);
> Then writing it out
> bos = new BufferedOutputStream(res.getOutputStream());
>
> wb.write(bos);
> bos.close();
> No luck with this. It seems POI is not fully compatible with Office 2010 and there is an issue with low level differences. I have a work around for our users whcih is to download to a "trusted Location". Do you know if adding a certificate of some sort to the template might replicate downloading to a trusted location? The file, despite all the dire warnings and the obvious difference noticed by Excel, does still work when opened. We are trying to make this seamless for our users. Any other thoughts or suggestions are most welcome.

Yegor and I always use and copy an empty HSSFWorkbook that has a digitally signed Macro in it.

HSSFWorkbook wb = new HSSFWorkbook(new FileInputStream(RTC_global.templatePath + LibraryData.SBOM_IMPORT_MACRO_TEMPLATE));

Does this work for you?

Regards,
Dave


>
> Jim Thomas
>
>
>
>
> If you reply to this email, your message will be added to the discussion below: http://apache-poi.1045710.n5.nabble.com/POI-from-Excel-2000-to-Excel-2010-issues-tp3365053p3366071.html 
> To unsubscribe from POI from Excel 2000 to Excel 2010 issues., click here .
> --
> View this message in context: http://apache-poi.1045710.n5.nabble.com/POI-from-Excel-2000-to-Excel-2010-issues-tp3365053p3366418.html
> Sent from the POI - Dev mailing list archive at Nabble.com.


---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: POI from Excel 2000 to Excel 2010 issues. (Edited out previous)

jamestthomas
>Yegor and I always use and copy an empty HSSFWorkbook that has a digitally signed Macro in it.

>HSSFWorkbook wb = new HSSFWorkbook(new FileInputStream(RTC_global.templatePath + LibraryData.SBOM_IMPORT_MACRO_TEMPLATE));

>Does this work for you?


Hello Dave, That sounds like a home run solution.  I would ask how to digitally sign the macro but that sounds like another question for another forum.  I was thinking that was what I might have to do.  One question, if you happen to know, did you implement the digitally signed macro to alleviate problems like I have mentioned?

Sincerely, James




If you reply to this email, your message will be added to the discussion below:
http://apache-poi.1045710.n5.nabble.com/POI-from-Excel-2000-to-Excel-2010-issues-tp3365053p3366513.html
To unsubscribe from POI from Excel 2000 to Excel 2010 issues., click here.
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: POI from Excel 2000 to Excel 2010 issues. (Edited out previous)

David Fisher
>> Yegor and I always use and copy an empty HSSFWorkbook that has a digitally signed Macro in it.
>
>> HSSFWorkbook wb = new HSSFWorkbook(new FileInputStream(RTC_global.templatePath + LibraryData.SBOM_IMPORT_MACRO_TEMPLATE));
>
>> Does this work for you?
>
>
> Hello Dave, That sounds like a home run solution. I would ask how to digitally sign the macro but that sounds like another question for another forum.

http://www.google.com/search?btnG=1&pws=0&q=signing+excel+macros

> I was thinking that was what I might have to do. One question, if you happen to know, did you implement the digitally signed macro to alleviate problems like I have mentioned?

Yes, it helps with warnings and tight corporate security policies. It may even be necessary for Excel 2010. (I am not sure.) A self-signed certificate may not be enough, one issued by a certificate authority may be required.

Regards,
Dave

>
> Sincerely, James
>
>
>
>
>
>
> If you reply to this email, your message will be added to the discussion below: http://apache-poi.1045710.n5.nabble.com/POI-from-Excel-2000-to-Excel-2010-issues-tp3365053p3366513.html 
> To unsubscribe from POI from Excel 2000 to Excel 2010 issues., click here .
> --
> View this message in context: http://apache-poi.1045710.n5.nabble.com/POI-from-Excel-2000-to-Excel-2010-issues-tp3365053p3366652.html
> Sent from the POI - Dev mailing list archive at Nabble.com.


---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Loading...