Basic Example HTML > To > Excel : Download?

dev support
1

Hey Folks… First let me apologize for not contributing much here… I do check in at least once a week but all the questions are over my head as a noob (and I don’t take blind stabs at things like people in other forums. I’m of the position of : If I don’t know I don’t speak and confuse the situation)…

That aside… I could really use some help with a Very basic HTML to Excel (.xlsx) download

The CFCs appear to be Very complete… But I cannot find even 1 working example either within the cloned library or on google

I’m hoping someone will have an quick example off the top of their head… (using this library or any other)

Just HTML-TABLE >> OUTPUT/DOWNLOAD in Excel.xlxs format…

  • I have a basic one working that will open in Libre office…
  • But it won’t open in Microsoft Excel : Formatting Error

Any help would be appreciated
Many Thanks

2

I mainly use writeFileFromQuery(). I have had no problems with ver 2.x. I haven’t tried the newer 3.0 yet.

3

Thanks for the pointer… will give it a try…
Thanks

4

Looking at some examples, I do have one that I did more in. I do remember having problems with FormatColumns(). I just had to edit the dataformat string to something POI could understand. Here is a small example.

spreadsheet = New spreadsheet();
filepath = ExpandPath("test.xls");
workbook = spreadsheet.new();
spreadsheet.addRow(workbook, ["Head1","Head2"]);
spreadsheet.addRow(workbook, ["#query.col1#","#query.col2#"]);
spreadsheet.formatColumn(workbook, {dataformat="xxx"}, 1);
spreadsheet.write(workbook, filepath, true);
6

Nice… That worked on the first shot… Thanks!

Looks like CFSpreadSheet IS supported in Lucee in Script ( the Lucee Docs indicate the Tag is not supported. Must be script only and I’m good with that for sure )

Working Example Writing to Disk:

<cfscript>
	spreadsheet = New spreadsheet();
	filepath = ExpandPath("test.xls");
	workbook = spreadsheet.new();
	spreadsheet.addRow(workbook, ["Head1","Head2"]);
	spreadsheet.addRow(workbook, ["#GETItems.Name#","#GETItems.Name#"]);
	spreadsheet.formatColumn(workbook, {dataformat="xxx"}, 1);
	spreadsheet.write(workbook, filepath, true);
</cfscript>

Result is : test.xls - Opens in Excel with out error
.
.
.
I’m working on pushing straight to the browser for Download : Not working with or without conversion to binary ( where as reading file as binary does work)

Many Thanks for your Help!

7

Hi Jay

You’ll find plenty of examples in the documentation: every single method has at least one. I’m happy to provide any specific examples if you can’t find what you’re after.

Lucee doesn’t provide any spreadsheet functionality out of the box, but you can use the Spreadsheet Library or the Extension (which supports the ACF syntax including tags), depending on your needs.

Check out the downloadFileFromQuery() method which will convert a query into a downloadable spreadsheet in one line of code.

8

Hey, Just wanted to say Thanks for all the pointers…

I’m trying them out now and post here again with a complete solution to help others

9

If you’re not invested in Excel and simplicity is your goal, this example might help. We often make raw data from our system available as a CSV file download, which clients can open in their default spreadsheet tool. Don’t forget to remove any unwanted commas from your data.

  <cfsetting enablecfoutputonly="true">
  <cfheader name="Content-Disposition" value="attachment; filename=data.csv">
  <cfcontent reset="true" type="text/csv">
  <cfoutput>field1,field2,field3#chr(10)#</cfoutput>
  <cfloop query="mydata">
    <cfoutput>#field1#,#dateformat(field2,"yyyy-mm-dd")#,#replace(field3,",",".","all")##chr(10)#</cfoutput>
  </cfloop>