Naming the Exported Excel Worksheet 1

[jl8789]

OK, I am able to export to excel in the browser, or it's own application if I change my explorer settings. Is there a way to code this so you don't need to tell the user to change their windows explorer settings?

Also, the name of the .cfm file that exports to excel shows up as the name of the .xls spreadheet instead of the filename I give to the .xls file. If you choose save instead of open, you can save it as the named .xls file. If you open the file, it opens as the name of the .cfm file with the .xls extension? Is there a way to name the worksheet the same as the filename you give to it and NOT the name of the .cfm file?

Thanks!

 

[Sarky78]

this is what i have used in the past to force the name of a filename

<CFHEADER NAME="Content-Disposition" VALUE="filename=Report.xls">
<CFCONTENT TYPE="Application/Unknown" FILE="#ExpandPath('.')#Report.xls" DELETEFILE="Yes">

This will just prompt the user to download the file, as I have told it not to open it automatically in Excel.

Hope this helps!

Tony

 

[jl8789]

It just opens in the browser with page not found.

I am looking for a way to be able to name the worksheet when it opens in excel. Right now it is naming the actual worksheet the same name as the .cfm file I am doing the content-type of excel in.

 

[SidianConsulting]

Try:

<cfheader name="Content-Disposition" VALUE="attachment; filename=myexcelfile.xls">
<cfcontent type="Application/vnd.ms-excel" file="x:\myexcelfile.xls">

If you still have problems, it might be the browser you are using. Some do not play well with filenames.

-Tek

 

[jl8789]

I am dynamically created the excel file, so it does not exist until it is opened in the browser\excel.

 

[SidianConsulting]

You might end up having to write it to a temp file, then, and use the deletefile attribute of the cfcontent tag for easy cleanup. Can you paste the code you are using to push the data to the user?

-Tek

 

[jl8789]

Yes, please show me how I could write this to a temp file, that may work for what I am trying to do.

Here is the code. This is all in a file called ApplicationAccessReportExp.cfm. That is what the worksheet is being named when I open it.

<cfinvoke component="iHubDataSetup" method="isLoggedInPopup"/>
<CFINCLUDE TEMPLATE="TalosSecurityUser.cfm">

<cfset selectedApp = "">
<cfif isDefined("URL.appCd")>
<cfinvoke component="iHubDataSetup" method="getApplications" returnvariable="getApplicationsQuery">
<cfinvokeargument name="appCd" value=#URL.appCd#>
</cfinvoke>
<cfoutput query="getApplicationsQuery"><cfset selectedApp = "#TC_DISPLAY_TXT#"></cfoutput>
<cfinvoke component="talos.reportSelect" method="getApplicationAccess" returnvariable="getApplicationAccessQuery">
<cfinvokeargument name="appId" value=#URL.appCd#>
</cfinvoke>
</cfif>

<cfset dtStamp = DateFormat(NOW(), "MMDDYYYY")>
<cfset tmStamp = TimeFormat(NOW(), "hmmtt")>
<cfoutput>
<cfset Stamp = "#dtStamp#_#tmStamp#">
<cfset filename = "filename="&"#selectedApp#"&"AppAccess_"&"#Stamp#"&".xls">
<cfheader name="Content-Disposition" value=#filename#>
<cfcontent type="application/excel">
</cfoutput>

<!--- This will run and display the ApplicationAccessReport in Excel Format --->
<table width="100%" cellspacing="2" cellpadding="2" border="0">
<tr>
<td width="25%" align="center">Manager</td>
<td width="25%" align="center">Employee</td>
<td width="10%" align="center">Access</td>
</tr>
</table>

<cfoutput query="getApplicationAccessQuery">
<table width="100%" cellspacing="2" cellpadding="2" border="0">
<tr>
<td width="25%" align="left" class="reportData">#Manager#</td>
<td width="25%" align="left" class="reportData">#Employee#</td>
<td width="10%" align="center" class="reportData">#Access#</td>
</tr>
</table>
</cfoutput>

<!--- Record Count --->
<cfoutput>
<br>
<FONT class="redheader">Records: #getApplicationAccessQuery.recordcount#</FONT>
</cfoutput>

</BODY>
</HTML>

 

[SidianConsulting]

Check out the cfsavecontent tag. Wrap all the data that gets output to Excel in this tag. Then use the CFFILE tag to write that content to a file. After this you can use the code I posted a few messages back to push the file to the browser. Just remember to add the DELETEFILE attribute to the CFCONTENT tag.

-Tek

 

[jl8789]

Thanks for your help.

Here is what I did. It saves the file as the name I wanted it, and then opens it up in excel. Perfect.

<!--- This will run and display the ApplicationAccessReport in Excel Format --->
<cfsavecontent variable="report">
<table width="100%" cellspacing="2" cellpadding="2" border="0">
<tr>
<td width="25%" align="center">Manager</td>
<td width="25%" align="center">Employee</td>
<td width="10%" align="center">Access</td>
</tr>
</table>

<cfoutput query="getApplicationAccessQuery">
<table width="100%" cellspacing="2" cellpadding="2" border="0">
<tr>
<td width="25%" align="left" class="reportData">#Manager#</td>
<td width="25%" align="left" class="reportData">#Employee#</td>
<td width="10%" align="center" class="reportData">#Access#</td>
</tr>
</table>
</cfoutput>

<!--- Record Count --->
<cfoutput>
<br>
<FONT class="redheader">Records: #getApplicationAccessQuery.recordcount#</FONT>
</cfoutput>
</cfsavecontent>

<cfoutput>
<cfset dtStamp = DateFormat(NOW(), "MMDDYYYY")>
<cfset tmStamp = TimeFormat(NOW(), "hmmtt")>
<cfset Stamp = "#dtStamp#_#tmStamp#">
<cfset filename = "#selectedApp#"&"AppAccess_"&"#Stamp#"&".xls">

<cffile action="write" file = "C:\#filename#" output="#report#">
<cfheader name="Content-Disposition" VALUE="attachment; filename=#filename#">
<cfcontent type="Application/vnd.ms-excel" file="C:\#filename#" deletefile="yes">
</cfoutput>