CFWheels and Excel

[Matthew]

Hi All,

I am trying to have my cfwheels app generate a report as an excel spreadsheet. I am getting my query data  and then using the renderWith() method in my controller as well as the provides method with xls in the init method. Fairly basic. I have a view properly namde "myfilename.xls.cfm" and when I call the page a xls file is generated. However there is nothing in the file and I dont understand what I need to put in the view. I have tried using the cfspreadsheet tag in the view but that makes my spreadsheet and saves it to a directory but still does not load the xls file. I want it to work like my PDF generation is working. Where when they click the form submission with the criteria they entered it generates the xls file and loads it in the browser and our starts a download depending on the users browser setup.

Any help would be great, as always THANKS 

[Tom King]

Something like this?

https://gist.github.com/neokoenig/e812d357d2fc85824548eb801b979537

Note "<cfheader name="Content-Disposition" value="inline; filename=#dateFormat(now(), 'yyyymmdd')#_report.xls"> " will try and load it in the browser.

[Tom King]

PS, with the cfspreadsheet library I tend to have this in the controller:

if(params.format EQ "xls"){

spreadsheet = new spreadsheetLibrary.Spreadsheet();

report=spreadsheet.new();

spreadsheet.addRows(workbook=report, data=data, includeQueryColumnNames=true, autoSizeColumns=true);

spreadsheet.download(report, "Report_#dateFormat(now(), 'yyyymmdd')#_#replace(lcase(title), ' ', '_', 'all')#");

} else {

renderWith(data=data, title=title, format=params.format);

[Matthew]

Thanks Tom, I will give it a whirl.

[Matthew]

The Lazy XML Generator code worked great except for getting an error when excel attempt to open the file...

"the file format and extension doesn't match. the file could be corrupted or unsafe"

Is there a way to avoid this error?

[Chris Peters]

I think that anything related to <cfspreadsheet> is view logic, so I put all of that into the view template.

My view templates involving Excel generally look like this:

<!--- Stuff here to build spreadsheet --->
<!--- ... --->
 
<!--- Then send it to the browser and delete it from the server when done --->
<cfheader name="Content-Disposition" value='attachment; filename="yadda-yadda-yadda.xls"'>
<cfcontent file="#tempFilename#" type="application/msexcel" deletefile="true">

--
You received this message because you are subscribed to the Google Groups "CFWheels" group.
To unsubscribe from this group and stop receiving emails from it, send an email to cfwheels+unsubscribe@googlegroups.com.
To post to this group, send email to cfwh...@googlegroups.com.
Visit this group at https://groups.google.com/group/cfwheels.
For more options, visit https://groups.google.com/d/optout.

--

Chris Peters
Web Developer
Liquifusion Studios
chris....@liquifusion.com
Skype: liquifusion.support
www.liquifusion.com

[Tom King]

Basically, you're making an HTML table into an excel file - so you're sort of cheating. You're forcing a different mime type to make excel open it (sorta).

So it's fine for "I just need to open this in an excel NOW" type of thing, but for proper stuff I'd look more closely at cfspreadsheet - for one, the exported formatting of the cells is a LOT more consistent/usable.

[Matthew]

Thanks to all for the help. I ended up not using wheels built in conversion and just did everything in the view with cfspreadsheet and its associated functions. In my cfcontent tag I set the "deletefile" attribute to "yes" which removes the file from the server so things ended up the way I wanted, with no scraps left behind.

Here is my code for anyone future reference...

<cfscript>

  // We need an absolute path, use the wheels file path as the base and the filename variable set in the controller.

  variables.theFile = "#ExpandPath(application.wheels.filePath)#\reports\#variables.filename#";

  // Create a new Excel spreadsheet object and add the header and query data.

  variables.theSheet = SpreadsheetNew("YearEndReport");

  // Header row

  SpreadSheetAddRow(variables.theSheet, "Student Name, Total Cost, Total Payed, New Balance, Total Hours");

  // Query data

  SpreadSheetAddRows(variables.theSheet, variables.data);

  // Formatting of cells
  SpreadSheetFormatColumns(variables.theSheet, {dataformat="$##,####0.00"}, "2-4");

  SpreadSheetFormatRow(variables.theSheet, {font="serif",fontsize=11,color="black",bold=true,alignment="center",fgcolor="pale_blue",fillpattern="solid_foreground"}, 1);

  SpreadSheetSetColumnWidth(variables.theSheet, 1, 25);

  SpreadSheetSetColumnWidth(variables.theSheet, 2, 15);

  SpreadSheetSetColumnWidth(variables.theSheet, 3, 15);

  SpreadSheetSetColumnWidth(variables.theSheet, 4, 15);

  SpreadSheetSetColumnWidth(variables.theSheet, 5, 14);

</cfscript>

<!--- Write the spreadsheet to a file, replacing any existing file. --->

<cfspreadsheet action="write" filename="#variables.theFile#" name="variables.theSheet" sheet=1 sheetname="Report" overwrite=true>

<!--- Open Spreadsheet File --->

<cfheader name="Content-Disposition" value="inline; filename=#variables.filename#">

<cfcontent type="application/csv" file="#variables.theFile#" deletefile="no">

Again many thanks guys.