Re: [CF-Dev] Download a returned recordset

Tomo Thu, 21 Apr 2005 01:49:58 -0700

- see footer for list info -<

Neil,

Thanks for that. I didn't realise it was just a class... if I knew that (a bit of looking on my part would have helped and a little more explanation on yours), I would have recommended this route instead of csv. I'll be looking at this as were creating shed loads of xcel docs each month:)

Cheers,

Tom

Robertson-Ravo, Neil (RX) wrote:

- see footer for list info -<
Hey Tom,
Well it is a Java Tag which you register in the Class Path so no 'real'
install needed.   We just registered it and we were all set.  This will do
the reading from Excel (using POI):
http://www.emerle.net/programming/display.cfm/t/cfx_excel2query
To write to Excel you need to do a little bit of work which we did here.
This was for an internal app but you will get the idea....
<cfscript> // Just to make this more complex, but very flexible, I'll store an array of structures containing details // of the columns that I want to include in the report, and in the correct order aColumns = ArrayNew(1); ArrayAppend(aColumns, StructNew()); aColumns[ArrayLen(aColumns)].ColumnName = "vcPriorityCode"; aColumns[ArrayLen(aColumns)].ColumnDesc = Session.stCampaign.stTextContent.CampaignCode;
        ArrayAppend(aColumns, StructNew());
        aColumns[ArrayLen(aColumns)].ColumnName = "vcCampaignName";
        aColumns[ArrayLen(aColumns)].ColumnDesc = "Campaign Name";
        ArrayAppend(aColumns, StructNew());
        aColumns[ArrayLen(aColumns)].ColumnName = "dDeploymentDate";
        aColumns[ArrayLen(aColumns)].ColumnDesc = "Deployment Date";
        ArrayAppend(aColumns, StructNew());
        aColumns[ArrayLen(aColumns)].ColumnName = "vcMediaTypeName";
        aColumns[ArrayLen(aColumns)].ColumnDesc = "Media Type";
        ArrayAppend(aColumns, StructNew());
        aColumns[ArrayLen(aColumns)].ColumnName = "vcVanityName";
        aColumns[ArrayLen(aColumns)].ColumnDesc = "Vanity";
        ArrayAppend(aColumns, StructNew());
        aColumns[ArrayLen(aColumns)].ColumnName = "iQuantity";
        aColumns[ArrayLen(aColumns)].ColumnDesc = "Quantity";
        ArrayAppend(aColumns, StructNew());
        aColumns[ArrayLen(aColumns)].ColumnName = "iCampaignAccessCount";
        aColumns[ArrayLen(aColumns)].ColumnDesc = "Website Visits";
        ArrayAppend(aColumns, StructNew());
        aColumns[ArrayLen(aColumns)].ColumnName =
"iCampaignRegistrationAccessCount";
        aColumns[ArrayLen(aColumns)].ColumnDesc = "Registration Visits";
        ArrayAppend(aColumns, StructNew());
        aColumns[ArrayLen(aColumns)].ColumnName =
"iCampaignRegistrationCompletionCount";
        aColumns[ArrayLen(aColumns)].ColumnDesc = "Registration Completed";
    // Create the Excel object
        workBook =
createObject("java","org.apache.poi.hssf.usermodel.HSSFWorkbook").init();
        workSheet = workBook.createSheet();
        workBook.setSheetName(0, "Campaigns");
        // Put in nice friendly description of search results, merged cell
across top, bold
        row = workSheet.createRow(0);
        cell = row.createCell(0);
        cell.setCellType(cell.CELL_TYPE_STRING);
        cell.setCellvalue(Session.stCampaign.stSearch.vcResultsDescription);
        font = workBook.createFont();
        font.setBoldweight(font.BOLDWEIGHT_BOLD);
        style = workBook.createCellStyle();
        style.setFont(font);
        style.setWrapText(true);
        cell.setCellStyle(style);
        // create a region(rowFrom,cellFrom,rowTo,cellTo)
        region =
createObject("java","org.apache.poi.hssf.util.Region").init(0,0,0,Evaluate(A
rrayLen(aColumns)-1));
        workSheet.addMergedRegion(region);
        row = workSheet.createRow(1);
        
        // Add headings row, bold, underline
        row = workSheet.createRow(2);
        font.setUnderline(true);
        style.setFont(font);
        for (i=1; i LTE ArrayLen(aColumns); i=i+1) {
                cell = row.createCell(Evaluate(i-1));
                cell.setCellvalue(aColumns[i].ColumnDesc);
                cell.setCellStyle(style);
                // set width, supposedly in units of 1/256 of character
width
                workSheet.setColumnWidth(Evaluate(i-1),
Evaluate((Len(ListFirst(aColumns[i].ColumnDesc, " ")) + 2) * 256));
        }
        // Add data from query in session
        for (j=1; j LTE
Session.stCampaign.stSearch.rsSearchResults.RecordCount; j=j+1) {
                row = workSheet.createRow(Evaluate(j+2));
                for (i=1; i LTE ArrayLen(aColumns); i=i+1) {
                        cell = row.createCell(Evaluate(i-1));
                        // special case for priority code
                        if (aColumns[i].ColumnName EQ "vcPriorityCode") {
                                value =
Session.stCampaign.stSearch.rsSearchResults[aColumns[i].ColumnName][j] &
Session.stCampaign.stSearch.rsSearchResults["vcLandingPageCode"][j];
                        }
                        else {
                                // Output date in usual format
                                // (can't seem to set custom date format in
Excel itself, which would have been nice, so this realy becomes text)
                                if
(IsDate(Session.stCampaign.stSearch.rsSearchResults[aColumns[i].ColumnName][
j])) {
                                        value =
DateFormat(Session.stCampaign.stSearch.rsSearchResults[aColumns[i].ColumnNam
e][j], Session.stCampaign.stSettings.vcDateMask);
                                }
                                else {
                                        value =
Session.stCampaign.stSearch.rsSearchResults[aColumns[i].ColumnName][j];
                                }
                        }
                        cell.setCellvalue(value);
                }
        }
        
        filename = GetDirectoryFromPath(GetCurrentTemplatePath()) &
"\..\documents\" & CreateUUID() & ".xls";
        fileOutStream =
createObject("java","java.io.FileOutputStream").init(filename);
        workBook.write(fileOutStream);
        fileOutStream.close();
</cfscript>
-----Original Message-----
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Tomo
Sent: 21 April 2005 09:03
To: Coldfusion Development
Subject: Re: [CF-Dev] Download a returned recordset
- see footer for list info -<
Neil,
you said it was fast and that, but you didn't answer my question about the set up and configuration time... that would certainly have a baring when installing to test on a dev box, and then installing to a live box.

Also, did it cause issues on the server(s) you installed it on? And what operating system you installed it on.
thanks,
Tom
Robertson-Ravo, Neil (RX) wrote:
- see footer for list info -<
No, really....?
<sarcasm/>
-----Original Message-----
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Paul Fennell
Sent: 20 April 2005 17:41
To: 'Coldfusion Development'
Subject: RE: [CF-Dev] Download a returned recordset
- see footer for list info -<
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
Repeating yourself doesn't make it correct.
- -----Original Message-----
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of
Robertson-Ravo, Neil (RX)
Sent: 20 April 2005 17:04
To: Simon Baynes; Coldfusion Development
Subject: RE: [CF-Dev] Download a returned recordset
- see footer for list info -<
http://jakarta.apache.org/poi/index.html
- -----Original Message-----
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Simon Baynes
Sent: 20 April 2005 16:34
To: Coldfusion Development
Subject: Re: [CF-Dev] Download a returned recordset
- see footer for list info -<
Pardon my ignorance but what is POI?
On 4/20/05, Tomo <[EMAIL PROTECTED]> wrote:
- see footer for list info -<
or you could go the vile route of generating HTML tables and
renaming the file xls...
how long does poi take to install and configure?
Robertson-Ravo, Neil (RX) wrote:
- see footer for list info -<
What if the data contains commas already...I know you can escape them etc..but seems like such a pain in the arse...POI ....fast and
effective..
-----Original Message-----
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Tomo
Sent: 20 April 2005 16:09
To: Coldfusion Development
Subject: Re: [CF-Dev] Download a returned recordset
- see footer for list info -<
what's wrong with a CSV?
Robertson-Ravo, Neil (RX) wrote:
- see footer for list info -<
Depending on what it is you can get strange results when outputting to Excel. We use POI for Query to Excel and Excel to Query but we are on MX
- -
you may be able to use it on CF5 though just a little more
convoluted I would think.
http://jakarta.apache.org/poi/index.html
This is very fast, very reliable.
-----Original Message-----
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Simon
Baynes Sent: 20 April 2005 15:50
To: Coldfusion Development
Subject: Re: [CF-Dev] Download a returned recordset
- see footer for list info -<
Why are you using a structure? Just output the results form the
query to the page.
Does your query change much? If not the cache it and that will
save database hits.
On 4/20/05, Stephen Adams <[EMAIL PROTECTED]> wrote:
- see footer for list info -<
Hi,
I have a query which displays a large number of result, anywhere
between
500
to 1800. Now not only do I want to display the results in my page. I
want
to
allow the user to download this result set into an Excel file.
The idea I am thinking of using is placing the results into a structure
and
then using this structure to loop through in one page to display the results, and then when the user decides they wish to download this
result
set, loop through the structure again in another file creating the
download
excel file, using cfhttp and cfcontent.
But I am worried about the performance of this, will this method be to
slow,
is there another way I could do this. Unfortunately the site is running
on
CF5, no CF7, bugger!
Stephen
_______________________________________________
For details on ALL mailing lists and for joining or leaving lists, go
to
http://list.cfdeveloper.co.uk/mailman/listinfo
--
CFDeveloper Sponsors:-
- Hosting provided by www.cfmxhosting.co.uk -< - Forum provided by www.fusetalk.com -< - DHTML Menus provided by www.APYCOM.com -< - Lists hosted by www.Gradwell.com -< - CFdeveloper is run by Russ Michaels, feel free to volunteer your
help
-<
_______________________________________________
For details on ALL mailing lists and for joining or leaving
lists, go to http://list.cfdeveloper.co.uk/mailman/listinfo
--
CFDeveloper Sponsors:-
- Hosting provided by www.cfmxhosting.co.uk -< - Forum provided by www.fusetalk.com -< - DHTML Menus provided by www.APYCOM.com -< - Lists hosted by www.Gradwell.com -< - CFdeveloper is run by Russ Michaels, feel free to volunteer your help
- -<
This e-mail is from Reed Exhibitions (Oriel House, 26 The Quadrant, Richmond, Surrey, TW9 1DL, United Kingdom), a division of Reed Business, Registered in England, Number 678540. It contains information which is confidential and may also be privileged. It is for the exclusive use of
the
intended recipient(s). If you are not the intended recipient(s) please
note
that any form of distribution, copying or use of this communication or
the
information in it is strictly prohibited and may be unlawful. If you
have
received this communication in error please return it to the sender or
call
our switchboard on +44 (0) 20 89107910. The opinions expressed within
this
communication are not necessarily those expressed by Reed
Exhibitions. Visit our website at http://www.reedexpo.com
_______________________________________________
For details on ALL mailing lists and for joining or leaving lists, go to
http://list.cfdeveloper.co.uk/mailman/listinfo
--
CFDeveloper Sponsors:-
- Hosting provided by www.cfmxhosting.co.uk -< - Forum provided by www.fusetalk.com -< - DHTML Menus provided by www.APYCOM.com -< - Lists hosted by www.Gradwell.com -< - CFdeveloper is run by Russ Michaels, feel free to volunteer your help
- -<
_______________________________________________
For details on ALL mailing lists and for joining or leaving
lists, go to http://list.cfdeveloper.co.uk/mailman/listinfo
--
CFDeveloper Sponsors:-
- Hosting provided by www.cfmxhosting.co.uk -< - Forum provided by www.fusetalk.com -< - DHTML Menus provided by www.APYCOM.com -< - Lists hosted by www.Gradwell.com -< - CFdeveloper is run by Russ Michaels, feel free to volunteer your help
- -<
This e-mail is from Reed Exhibitions (Oriel House, 26 The Quadrant, Richmond, Surrey, TW9 1DL, United Kingdom), a division of Reed Business, Registered in England, Number 678540. It contains information which is confidential and may also be privileged. It is for the exclusive use of
the
intended recipient(s). If you are not the intended recipient(s) please
note
that any form of distribution, copying or use of this communication or
the
information in it is strictly prohibited and may be unlawful. If you
have
received this communication in error please return it to the sender or
call
our switchboard on +44 (0) 20 89107910. The opinions expressed within
this
communication are not necessarily those expressed by Reed
Exhibitions. Visit our website at http://www.reedexpo.com
_______________________________________________
For details on ALL mailing lists and for joining or leaving lists, go to
http://list.cfdeveloper.co.uk/mailman/listinfo
--
CFDeveloper Sponsors:-
- Hosting provided by www.cfmxhosting.co.uk -< - Forum provided by www.fusetalk.com -< - DHTML Menus provided by www.APYCOM.com -< - Lists hosted by www.Gradwell.com -< - CFdeveloper is run by Russ Michaels, feel free to volunteer your help
- -<
_______________________________________________
For details on ALL mailing lists and for joining or leaving lists, go to
http://list.cfdeveloper.co.uk/mailman/listinfo
--
CFDeveloper Sponsors:-
- Hosting provided by www.cfmxhosting.co.uk -< - Forum provided by www.fusetalk.com -< - DHTML Menus provided by www.APYCOM.com -< - Lists hosted by www.Gradwell.com -< - CFdeveloper is run by Russ Michaels, feel free to volunteer your help
- -<
_______________________________________________
For details on ALL mailing lists and for joining or leaving lists, go
to
http://list.cfdeveloper.co.uk/mailman/listinfo
- --
CFDeveloper Sponsors:-
- Hosting provided by www.cfmxhosting.co.uk -< - Forum provided by www.fusetalk.com -< - DHTML Menus provided by www.APYCOM.com -< - Lists hosted by www.Gradwell.com -< - CFdeveloper is run by Russ Michaels, feel free to volunteer your help -<
This e-mail is from Reed Exhibitions (Oriel House, 26 The Quadrant,
Richmond, Surrey, TW9 1DL, United Kingdom), a division of Reed
Business,
Registered in England, Number 678540.  It contains information which
is
confidential and may also be privileged.  It is for the exclusive use
of the
intended recipient(s).  If you are not the intended recipient(s)
please note
that any form of distribution, copying or use of this communication
or the
information in it is strictly prohibited and may be unlawful.  If you
have
received this communication in error please return it to the sender
or call
our switchboard on +44 (0) 20 89107910.  The opinions expressed
within this
communication are not necessarily those expressed by Reed
Exhibitions.
Visit our website at http://www.reedexpo.com
_______________________________________________
For details on ALL mailing lists and for joining or leaving lists, go
to http://list.cfdeveloper.co.uk/mailman/listinfo
- --
CFDeveloper Sponsors:-
- Hosting provided by www.cfmxhosting.co.uk -< - Forum provided by www.fusetalk.com -< - DHTML Menus provided by www.APYCOM.com -< - Lists hosted by www.Gradwell.com -< - CFdeveloper is run by Russ Michaels, feel free to volunteer your help -<
- -- No virus found in this incoming message. Checked by AVG Anti-Virus. Version: 7.0.308 / Virus Database: 266.9.16 - Release Date: 18/04/2005

- -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.308 / Virus Database: 266.9.16 - Release Date: 18/04/2005
-----BEGIN PGP SIGNATURE-----
Version: PGP Personal Privacy 6.5.3
iQA/AwUBQmaGElA768D4gVAEEQLbzgCeNMW8wW8hY7GFUeSkWdz2tAcofH0An3tK
wMPvtouehXaqxiyep2CFkgQ0
=GDHo
-----END PGP SIGNATURE-----
_______________________________________________
For details on ALL mailing lists and for joining or leaving lists, go to
http://list.cfdeveloper.co.uk/mailman/listinfo
--
CFDeveloper Sponsors:-
- Hosting provided by www.cfmxhosting.co.uk -<
- Forum provided by www.fusetalk.com -<
- DHTML Menus provided by www.APYCOM.com -<
- Lists hosted by www.Gradwell.com -<
- CFdeveloper is run by Russ Michaels, feel free to volunteer your help -<
This e-mail is from Reed Exhibitions (Oriel House, 26 The Quadrant,
Richmond, Surrey, TW9 1DL, United Kingdom), a division of Reed Business,
Registered in England, Number 678540.  It contains information which is
confidential and may also be privileged.  It is for the exclusive use of
the
intended recipient(s). If you are not the intended recipient(s) please
note
that any form of distribution, copying or use of this communication or the
information in it is strictly prohibited and may be unlawful.  If you have
received this communication in error please return it to the sender or
call
our switchboard on +44 (0) 20 89107910. The opinions expressed within
this
communication are not necessarily those expressed by Reed Exhibitions.
Visit our website at http://www.reedexpo.com
_______________________________________________
For details on ALL mailing lists and for joining or leaving lists, go to
http://list.cfdeveloper.co.uk/mailman/listinfo
--
CFDeveloper Sponsors:-
- Hosting provided by www.cfmxhosting.co.uk -<
- Forum provided by www.fusetalk.com -<
- DHTML Menus provided by www.APYCOM.com -<
- Lists hosted by www.Gradwell.com -<
- CFdeveloper is run by Russ Michaels, feel free to volunteer your help -<
_______________________________________________
For details on ALL mailing lists and for joining or leaving lists, go to
http://list.cfdeveloper.co.uk/mailman/listinfo
--
CFDeveloper Sponsors:-
- Hosting provided by www.cfmxhosting.co.uk -<
- Forum provided by www.fusetalk.com -<
- DHTML Menus provided by www.APYCOM.com -<
- Lists hosted by www.Gradwell.com -<
- CFdeveloper is run by Russ Michaels, feel free to volunteer your help -<
This e-mail is from Reed Exhibitions (Oriel House, 26 The Quadrant,
Richmond, Surrey, TW9 1DL, United Kingdom), a division of Reed Business,
Registered in England, Number 678540.  It contains information which is
confidential and may also be privileged.  It is for the exclusive use of the
intended recipient(s).  If you are not the intended recipient(s) please note
that any form of distribution, copying or use of this communication or the
information in it is strictly prohibited and may be unlawful.  If you have
received this communication in error please return it to the sender or call
our switchboard on +44 (0) 20 89107910.  The opinions expressed within this
communication are not necessarily those expressed by Reed Exhibitions.
Visit our website at http://www.reedexpo.com
_______________________________________________
For details on ALL mailing lists and for joining or leaving lists, go to 
http://list.cfdeveloper.co.uk/mailman/listinfo
--
CFDeveloper Sponsors:-
- Hosting provided by www.cfmxhosting.co.uk -<
- Forum provided by www.fusetalk.com -<
- DHTML Menus provided by www.APYCOM.com -<
- Lists hosted by www.Gradwell.com -<
- CFdeveloper is run by Russ Michaels, feel free to volunteer your help -<

_______________________________________________

For details on ALL mailing lists and for joining or leaving lists, go to 
http://list.cfdeveloper.co.uk/mailman/listinfo

--
CFDeveloper Sponsors:-

- Hosting provided by www.cfmxhosting.co.uk -<
- Forum provided by www.fusetalk.com -<
- DHTML Menus provided by www.APYCOM.com -<
- Lists hosted by www.Gradwell.com -<
- CFdeveloper is run by Russ Michaels, feel free to volunteer your help -<

Re: [CF-Dev] Download a returned recordset

Reply via email to