POI ColdFusion Custom Tags First Release

Posted March 25, 2008 at 9:02 AM by Ben Nadel

Tags: ColdFusion

Reader Comments

Dude, these tools rock. Thanks. Great posts in general by the way.

@Ryan,

Thanks man. Glad to know someone is finding it exciting. I am definitely looking forward to using this in my projects.

Soon, I want to add things like freezing areas and adding images and graphics and what not (fingers crossed).

Ben,

Thank you so much for updating this. I've been using the CFC for a while for processing XLS file uploads and importing data into a database.

This tool has saved me countless hours.

Great work.

@Mario,

Sounds like a cool process you have going on there.

Ben,

This looks really cool. Of course I have a fetaure request... Are you planning to add the ability to edit an existing excel file?

In case you need landscape mode for a sheet (and I might have missed it if it was built in already in the code somewhere else), add this to the sheet.cfm:

<cfparam name="ATTRIBUTES.Landscape"
type="boolean"
default="false"
/>

<cfset VARIABLES.printSetup = VARIABLES.Sheet.getPrintSetup() />
<cfset VARIABLES.printSetup.setLandscape(attributes.landscape) />

Then in your poi tags, do something like so:

<poi:sheet name="#reportTitle#" landscape="true">

The important part being to set landscape="true".

@Ed,

I am not sure how I will do that with the ColdFusion Custom Tags; I think just maybe an [action="insert" index="2"] on the sheet or something.

@Dan,

Thanks for the quality suggestion. I will try to integrate this.

Ben, This POI Utility for CF totally rocks. I have been getting pretty frustrated will some of the other cf2excel solutions out there.

FYI: I was digging around in your POI customtags for the XML based version trying to solve a problem I was having with Dollar Formating. Specifically I was in the "cell.cfm" file where I found a reference to "ATTRIBUTES.DateFormat" in the "numeric" cfswith/case where you are checking for bad NumberFormats being passed in. It looks like it needs to be "ATTRIBUTES.NumberFormat"... Is my thinking correct?

Thanks for all your work on this!!!
Greg

@Greg,

When I started writing that, I thought I could separate out date and numeric formatting. However, after further reading, it looks like date and numeric formatting are handled in the same way in th Excel document.

I have to make another round of updates, especially to handle the FONT problem. I will clean that stuff up.

How can I use this with my server. I don't have ColdFusion admin rights but I do have admin rights to my space.

Ben,
I like what you are doing here. I have tried using XML, but I am not having much success. I thought I'd try this code.
What I need is a way to take a HTML page that displays data and export it out to an excel file. Yes this is easy on a basic level. However, I need the excel file to print out in landscape. I have been looking at this POI stuff, and it seems promising. Maybe I am missing something in how to export HTML data into an excel file...if I am can you give me an explanation. If there is not an easy way to get the data into a landscape excel file, is there a way you could update your custom tags to add the print orientation variable?

thanks
dan

Ben,

This tag is amazing and is something that should have been created years ago. I can recall trying to get this in-depth with Excel yrs ago via Coldfusion and nothing was available. On another note, it is nice to see someone we can now turn to with CF help/ideas. The CF community I felt has shrunk a great deal over the past year or two.

Quick quesiton. I am trying to develop a new number mask which seems to be in the 'Custom' mask settings in Excel when formatting a cell. Essentially I would like to use the percentage mask '0.00%' but add a comma to it for percentages which are large like 333,434%.

I have taken the steps of adding a new mask, "(##,####0.00%);[Red](-##,####0.00%)", to the document.cfm variables page and then applied it to the numbermask in the actual page itself. When I run the query I do not get the % at the end of the number. Am I missing something?

Thanks,

Jeremy

@Jeremy,

I am not 100% if / how custom masks get added. When I have some time to looking into it, I will. Sorry I could not be more help.

Hi Ben,

Is there any way to retrieve data from .xls sheet to query object without using POI components ?

Hey Ben

This poi utitlity is really great and I have been able to do a lot with it.

My only issue is that on some of my longer pages i like to have output to the page saying which company we are processing or a percentage of where we are in the page, etc. It seems when invoking the poi you can not do a cfflush and see any output in that same page

Any ideas how to change that so we can have some output status via scfflush while using the poi custom tag on the page?

Thanks again
Matt

@Matt,

I am not sure that we can gain any insight into the process while the custom tags are processing since ColdFusion doesn't allow custom tag content to be flushed (as the end-tag has a change to reset it). Sorry :(

@Ben,

How about if the majority of the page was created in a cfsavecontent and then output at the end of the page so that we could output progress as the page is running before calling poi:document. Do you think that would work to start with the first poi:Sheet call in a savecontent through the last </poi:sheet> call and then at the end of the page do the <poi:document> <poi:sheet> <cfoutput>#thesavecontentvariable#</cfoutput></poi:sheets></poi:document>

Thanks again,
Matt

@Matt,

Nothing can actually be flushed to the client from within a custom tag; ColdFusion just won't let you do it. If you wanted to get really tricky, what you could probably do is launch the POI stuff in a CFThread tag and then post updates to "VARIABLES" scope of the main page as the document is processing (and have the main page - outside of the CFThread - communicate back to the client.

The biggest problem with this is that the file cannot be returned with that request; once output is committed to the client, you can no longer overwrite the output with the XLS binary. However, you could write the file to disk and then re-direct the user to it once it was completed.

... that's actually an interesting idea. Perhaps I can play around with the concept a bit.

@Ben,

Nice - i knew if you wrote out how one couldn't do it you'd probably stumble on how one actually can do it. I have not used cfthread before so will need to research and play with that some but let me know if you come up with something as I will not be able to get to this for a minute and you'll probably nail it much quicker than i would!

Thanks again,
Matt

@Matt,

Hey my man, check this out:

http://www.bennadel.com/blog/2019-Communicating-With-The-Client-Whilst-Inside-A-ColdFusion-Custom-Tag.htm

A little proof of concept allowing a ColdFusion custom tag to push updates to the client.

Ah mate thanks a lot. I've been looking for a lot of jQuery tutorials regarding charts and i stumbled upon this.Just wanted to say thank you for the help.

@Ik,

No problem my man - glad you liked it.

Hey Ben

I had added the ability to pass headers and footers for printing, as well as setup a default print area to fit layout in 1 page width to the POIUtility.CFC

I just went ahead and added them into the sheet.cfm in case any one searches for print area, printer header and footer, etc. You can use left, center and right in header and footer.

There may be a better way to pass it but I pass it as 6 comma separated strings. Haven't really played with or tested maximums, but it gives nice control and in one usage I have an account name and address with no issue in the header left section.

Feel free to include it in an update and/or improve upon it!

<!---List of up to 6 comma separated strings for header left,header center, header right,footer left,footer center,footer right --->
<cfparam name="ATTRIBUTES.HeaderFooter"
type="string"
default=""
/>

then in the "End" section after the FreezeRow logic and before the landscape/portrait logic, first we make the page work on 8.5" x 11" print out with basic functionality (that could be expanded and made an argument)..

<!--- Fit to an 8" wide page...for a small level of auto formatting pre-Excel... this could be made dynamic for different paper sizes--->
<cfset VARIABLES.Sheet.setAutobreaks(
JavaCast( "boolean", true )
) />
<cfset VARIABLES.Sheet.GetPrintSetup().setFitHeight(
JavaCast( "int", 7 )
) />
<cfset VARIABLES.Sheet.GetPrintSetup().setFitWidth(
JavaCast( "int", 1 )
) />

Finally the code to set the up to 6 sections of header and footer...

<cfset printHeaderFooter = ListToArray(
ATTRIBUTES.HeaderFooter,
","
)>
<cfloop from="1" to="#listlen(printHeaderFooter)#" index="j">
<cfswitch expression="#j#">
<cfcase value=1>
<cfset VARIABLES.Sheet.getHeader().setLeft(
JavaCast(
"string",
printHeaderFooter[1]
)
) />
</cfcase>
<cfcase value=2>
<cfset VARIABLES.Sheet.getHeader().setCenter(
JavaCast(
"string",
printHeaderFooter[2]
)
) />
</cfcase>
<cfcase value=3>
<cfset VARIABLES.Sheet.getHeader().setRight(
JavaCast(
"string",
printHeaderFooter[3]
)
) />
</cfcase>
<cfcase value=4>
<cfset VARIABLES.Sheet.getFooter().setLeft(
JavaCast(
"string",
printHeaderFooter[4]
)
) />
</cfcase>
<cfcase value=5>
<cfset VARIABLES.Sheet.getFooter().setCenter(
JavaCast(
"string",
printHeaderFooter[5]
)
) />
</cfcase>
<cfcase value=6>
<cfset VARIABLES.Sheet.getFooter().setRight(
JavaCast(
"string",
printHeaderFooter[6]
)
) />
</cfcase>
</cfswitch>
</cfloop>

Anyways hope it helps some folks.

Matt

Sorry the loop in the paste above is not correct it should be

<cfloop from="1" to="#listlen(ATTRIBUTES.HeaderFooter)#" index="j">

Matt

How we can format for one decimal point if for a cell we are using formula.

Post A Comment

Comment Etiquette: Please do not post spam. Please keep the comments on-topic. Please do not post unrelated questions or large chunks of code. And, above all, please be nice to each other - we're trying to have a good conversation here.

Please review the following issues:

Author Name:
Author Email:
Author Website:
Comment:	Supported HTML tags for formatting: <strong>bold</strong>   <em>italic</em>   <code>code</code>
	Remember my information
	Subscribe to comments
	Send me a copy of this comment