Copy link to clipboard
Copied
Hi,
We have code that is creating an excel workbook with the POI library and then using CFHeader to set the filename with content-disposition as shown in the code below. After setting the content-disposition,we write the Excel workbook to the browser. The code is below.
This works wonderfully in CF 9 but when we upgraded to CF 11 we found that this is no longer working. The same code works fine in CF 9 but not in CF 11. The filename is being ignored and the file is now being given the name of the cfm script being called with the correct xls extension.
Any suggestions as to what is causing this in CF 11 and not in CF 9 would be appreciated.
Code:
<cfheader name="Content-Disposition" value='attachment; filename="#arguments.fileName#" ' >
<cfscript>
// Write the workbook to the ByteArrayOutputStream to obtain the ContentLength
local.workBook.write(local.outStream);
local.len = arrayLen(local.outStream.toByteArray());
local.outStream.flush();
local.outStream.close();
// Now work on displaying to browser output stream
local.context = getPageContext();
local.context.setFlushOutput(false);
// The getResponse call twice (2nd getResponse) gets the binary response of the character response (1st getResponse)
local.response = local.context.getResponse().getResponse();
// retrieve the output stream of the response to the browser.
local.out = local.response.getOutputStream();
local.response.reset();
//Set the content type and the contentLength
// if both are not provided, then the data will not display correctly
local.response.setContentType("application/vnd.ms-excel");
local.response.setContentLength(local.len);
// write the workbook to the http response binary output stream
local.workBook.write(local.out);
// clean things up and close the output stream
local.out.flush();
local.response.flushBuffer();
local.out.close();
</cfscript>
Regards,
Leslie
I found the problem. It was not related to using the POI library at all. Also we are migrating to CF 11 currently and just trying to get things migrated as is. After our migration, we will look into CFSpreadsheet to see if it can now meet the formatting demands of our users.
It appears that the the call to local.response.reset() in CF 11 now clears the response properties including the header items. In CF 9, it must not have cleared them as thoroughly. By setting the response header Content-Dispo
...Copy link to clipboard
Copied
Where do you bring in the workbook? Without it, I don't expect the code to work in ColdFusion 9 either.
Copy link to clipboard
Copied
I was going to say the same, BKBK. I see a lot of flush and flushbuffer, but I don't see anything actually workbook related. But it would appear that the first line after <cfscript> holds the key. Apparently, we are expected to take OP's word that the Excel object exists.
Personally, I love the granular control of SpreadsheetNew() and other functions. And then setting the object to ReadAsBinary(), then use CFHEADER and CFCONTENT to stream it to the browser.
Just my two cents.
V/r,
^_^
Copy link to clipboard
Copied
Here is the full method used to display a POI library excel workbook to the browser.
Please note that this workbook is created with the POI java library and not the ColdFusion excel generation tags. The POI library had more functionality than the ColdFusion tags which is why we went with it.
The workbook displays correctly and is correctly of type xls, but the filename is not correct. Our big issue is with the filename not being the one that we have put in the <cfheader> tag call for Content-Disposition:
<cfheader name="Content-Disposition" value='attachment; filename="#arguments.fileName#" ' >
Code:
<cffunction name="displayWorkbookAsExcelFile" access="public" output="true" hint="I send the workbook to a browser response output stream.">
<cfargument name="workbook" required="true" Hint="The workbook">
<cfargument name="fileName" required="true" Hint="the name of the file. This is not a fully qualified file name.">
<cftry>
<!--- Set default values --->
<cfset local.context = "" />
<cfset local.response = "" />
<cfset local.out = "" />
<cfset local.len = 0 />
<cfset local.outStream = createObject("java","java.io.ByteArrayOutputStream").init()/>
<!--- Retrieve the populated workbook--->
<cfset local.workBook = arguments.workbook/>
<cfheader name="Content-Disposition" value='attachment; filename="#arguments.fileName#" ' >
<cfscript>
// Write the workbook to the ByteArrayOutputStream to obtain the ContentLength
local.workBook.write(local.outStream);
local.len = arrayLen(local.outStream.toByteArray());
local.outStream.flush();
local.outStream.close();
// Now work on displaying to browser output stream
local.context = getPageContext();
local.context.setFlushOutput(false);
// The getResponse call twice (2nd getResponse) gets the binary response of the character response (1st getResponse)
local.response = local.context.getResponse().getResponse();
// retrieve the output stream of the response to the browser.
local.out = local.response.getOutputStream();
local.response.reset();
//Set the content type and the contentLength
// if both are not provided, then the data will not display correctly
local.response.setContentType("application/vnd.ms-excel");
local.response.setContentLength(local.len);
// write the workbook to the http response binary output stream
local.workBook.write(local.out);
// clean things up and close the output stream
local.out.flush();
local.response.flushBuffer();
local.out.close();
</cfscript>
<cfcatch type="any">
<cfrethrow>
</cfcatch>
</cftry>
</cffunction>
Regards,
Leslie
Copy link to clipboard
Copied
POI was required to do spreadsheets in ColdFusion versions prior to 9. CF9 may not have great spreadsheet-generating ability, but CF11 _does_ have great spreadsheet-generating abilities.
I hate to say it, but in comparison, POI _may_ have had benefits over CF9; but it is slow and clunky and is inferior to CF11 generated spreadsheets.
However, in order to answer your question as to why POI isn't working in CF11 - I'd have to guess, but it probably has to do with the fact that CF9 uses JRUN, and CF10+ uses Tomcat.
According to this post on Adobe forums, CF10 shipped with POI beta 3.6, so it may be a version issue.
I can also tell you that unless you download and install the CF11 server that already has Java 8, there are issues because of some default network accesses that were shut down in Java updates (starting with Java 7 u 31). What version of Java is your CF running?
^_^
Copy link to clipboard
Copied
Hi Wolfgang,
I think you misunderstand. I am not having issues with POI in CF 11. The spreadsheet is created correctly with all of the needed functionality. The issue is that the filename defined in the cfheader call to set the Content-Disposition is not being used. It uses the name of the cfm file which calls the POI library for its name.
So if index.cfm is called, the filename for the excel file becomes index.xls. The cfheader call I am using is:
<cfheader name="Content-Disposition" value='attachment; filename="#arguments.fileName#" ' > or with data
<cfheader name="Content-Disposition" value='attachment; filename="myExcelFileName.xls" ' >
Copy link to clipboard
Copied
There could still be an issue with the filename related to Java, or Tomcat.
You could remove the double-quotes from the filename. They are not required, and could be screwing things up.
Also, I've never seen any successful file streaming (attachment or inline) without using CFCONTENT (which, I believe, should go after CFHEADER.)
HTH,
^_^
Copy link to clipboard
Copied
WolfShade wrote:
Also, I've never seen any successful file streaming (attachment or inline) without using CFCONTENT (which, I believe, should go after CFHEADER.)
He did use the equivalent of cfcontent, namely, local.response.setContentType().
Copy link to clipboard
Copied
I found the problem. It was not related to using the POI library at all. Also we are migrating to CF 11 currently and just trying to get things migrated as is. After our migration, we will look into CFSpreadsheet to see if it can now meet the formatting demands of our users.
It appears that the the call to local.response.reset() in CF 11 now clears the response properties including the header items. In CF 9, it must not have cleared them as thoroughly. By setting the response header Content-Disposition after this point, the value was available for the browser to use.
So the default filename for a generated file must be the name of the script creating the filename plus the file extension appropriate for the file.
The updated code is below, note the bolded code lines.
<cffunction name="displayWorkbookAsExcelFile" access="public" output="true" hint="I send the workbook to a browser response output stream.">
<cfargument name="workbook" required="true" Hint="The workbook">
<cfargument name="fileName" required="true" Hint="the name of the file. This is not a fully qualified file name.">
<cftry>
<!--- Set default values --->
<cfset local.context = "" />
<cfset local.response = "" />
<cfset local.out = "" />
<cfset local.len = 0 />
<cfset local.outStream = createObject("java","java.io.ByteArrayOutputStream").init()/>
<!--- Retrieve the populated workbook--->
<cfset local.workBook = arguments.workbook/>
<cfscript>
// Write the workbook to the ByteArrayOutputStream to obtain the ContentLength
local.workBook.write(local.outStream);
local.len = arrayLen(local.outStream.toByteArray());
local.outStream.flush();
local.outStream.close();
// Now work on displaying to browser output stream
local.context = getPageContext();
local.context.setFlushOutput(false);
// The getResponse call twice (2nd getResponse) gets the binary response of the character response (1st getResponse)
local.response = local.context.getResponse().getResponse();
// retrieve the output stream of the response to the browser.
local.out = local.response.getOutputStream();
local.response.reset();
//Send the browser the correct content disposition
local.resp = local.context.getResponse();
local.resp.setHeader(javacast("string","Content-Disposition"),javacast("string","attachment;filename=#arguments.fileName#"));
//Set the content type and the contentLength
// if both are not provided, then the data will not display correctly
local.response.setContentType("application/vnd.ms-excel");
local.response.setContentLength(local.len);
// write the workbook to the http response binary output stream
local.workBook.write(local.out);
// clean things up and close the output stream
local.out.flush();
local.response.flushBuffer();
local.out.close();
</cfscript>
<cfcatch type="any">
<cfrethrow>
</cfcatch>
</cftry>
</cffunction>
Copy link to clipboard
Copied
@LASUI
Quite an intricate one to find. Thanks for sharing it with us.
Copy link to clipboard
Copied
LASUI wrote:
Please note that this workbook is created with the POI java library and not the ColdFusion excel generation tags. The POI library had more functionality than the ColdFusion tags which is why we went with it.
The workbook displays correctly and is correctly of type xls, but the filename is not correct. Our big issue is with the filename not being the one that we have put in the <cfheader> tag call for Content-Disposition:
<cfheader name="Content-Disposition" value='attachment; filename="#arguments.fileName#" ' >
Your code looks OK. What happens when you change that line to:
<cfheader name="Content-Disposition" value="attachment; filename=#arguments.fileName#" >