All geek questions in one place

How to load in Excel?

I would like to offer the “upload to excel” functionality for several different datasets available on different parts of my coldfusion site. I use coldfusion and would like to use freely available custom tags / libraries that can help me accomplish this and not encode it all from scratch. I pointed to cflib.org , but I'm not sure how to get started. Anyone have any ideas, comments, or resources about downloading coldfusion website data?

+4
source share
6 answers

In the end, we used the Query2Excel function from cflib.org and tweaked it a bit to suit our specific needs.

+1
source

The approach I used in the past for an intranet with loading tabular statistics is to take advantage of the fact that Excel can read HTML.

On the pages where I am going to provide links to Excel, I write out the main table as HTML in a file and include a link to this file on the displayed page. You can then serve this page with the .xls extension, use meta headers, or even use the mediation page with CFCONTENT so that your browser launches Excel to display the content.

This approach can be extended to an arbitrary number of tables per page. You will need to write a tag containing a table, which will then take care of writing the file and provide a download link for all the HTML content contained inside.

Sorry for the lack of sample code. If my description of the process left you scratching your head, I will give a few examples for you.

+5
source

You can also use the html table, as in:

<cfquery name="data" datasource="whatever"> ... </cfquery> <cfheader name="Content-Disposition" value="inline; filename=fileName.xls"> <cfcontent type="application/x-msexcel" reset="true"> <table border="0" cellspacing="0" cellpadding="0"> <thead> <tr> <td>Col1</td> <td>Col2</td> </tr> </thead> <tbody> <cfoutput query="data"> <tr> <td>#data.Col1#</td> <td>#data.Col2#</td> </tr> </cfoutput> </tbody> </table>

Hth,

Larry

+4
source

Based on @Ciaran Archer's answer, I usually use CSV for this, which Excel will open from the Internet if it is installed on the client PC. Assuming that the data you are working with is coming out of the database, use QueryToCSV () on Ben Nadel's website as such:

 <cfquery name="data" datasource=""> ... </cfquery> <cfinclude template="fun_queryToCSV.cfm"> <cfheader name="Content-Disposition" value="filename=my-file-name.xls"> <cfcontent type="test/csv" reset="true"> <cfoutput>#querytoCSV(data)#</cfoutput>

"reset" on cfcontent clears the response buffer, so the only thing inside it is what happens. Alternatively, if you already have data as a file, cfcontent has a file attribute that will directly serve its contents.

+1
source

For those who work in Coldfusion 9+ and can use cfspreadsheet, you can use SpreadsheetReadBinary () to make the spreadsheet loadable without first having to write the spreadsheet to a file.

0
source

Source: https://habr.com/ru/post/1333017/

More articles:


All Articles