Re: Struggling with an excel file

Hi Pete,

Excel files are binary documents -- they contain lots of non-text data. With that in mind, is it possible that somewhere along the line the system is trying to translate your Excel document from one CCSID to another?

We commonly expect this behavior with i5/OS applications, because we work so much in EBCDIC text, and the PC wants ASCII text, so we want the operating system to "automatically" translate the document. But in the case of an Excel spreadsheet, it's very important that no translation takes place.

Does the getSpreadSheet() method do any translation? (Any at all... doesn't have to be ASCII to EBCDIC... translating ASCII to Unicode would corrupt the file as well).

Does the BufferedOutputStream do any translation?

Does the HTTP server (I assume you're using Tomcat or WebSphere) do any translation?

Obviously, you want to make sure that nothing gets translated.

If all else fails, maybe you could compare the hex values of the data that the browser receives with the hex values of the data of the file in the folder. If one works and the other fails, then they must be different. If so, what's the difference? That would give you a good idea of where to look for the problem.



Pete Helgren wrote:

This is more generic to web programming so I hope you folks have an idea.

I have a servlet application that has, as one small part, a routine that builds and then downloads an excel spreadsheet. It first calls a routine that builds the sheet using poi to a temporary folder and then calls a second routine that streams that data to the browser, prompting the user for a download or open. This routine *was* working perfectly. However, I was working on another part of the application and doing some testing and discovered that the download routine is no longer working. Well, it works, but the resulting file isn't recognized as an excel spreadsheet. I looked at the old source and there are no differences is this particular method. So it must be an "environment" issue (I think).

The relevant part of the java code is (I think):

if(finishedOK){ // Did the build of the spreadsheet complete OK?
// *output the spreadsheet
outFile = getSpreadSheet(ssd.getSheetFileName(),userContainer); // returns a byte array of the file
response.setContentType("application/vnd.ms-excel");
response.setHeader("Content-Disposition"," inline; filename=mismatched.xls");
try {
output = new BufferedOutputStream(response.getOutputStream());
output.write(outFile);
output.flush();
output.close();
} catch(IOException ioe){
System.err.println("IOException(102) doing fileload: "+ioe.getMessage());
}
}

I can open the spreadsheet that is created by poi in the temporary folder just fine. It is just when it is retrieved from the folder and streamed to the browser, I get an error.

I get the same error whether I deploy to the System i and run it, or run it in debug locally in windows from within Eclipse. For the life of me I can't figure it out. I thought it might have been something locally on my notebook, given the multiple JVM's and environmental stuff I mess with, but even the deployment to the System i is broken.

The files (original and downloaded) look identical. Same size when the properties are viewed, etc.

Ideas? I am stumped.

Pete Helgren