Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

ColdFusion to Excel - a solution to Extension Hardening

Posted on 2011-05-09
25
Medium Priority
?
1,386 Views
Last Modified: 2012-05-11
With Excel's 2007 Extension Hardening, all of the output files using <cfcontent type="application/msexcel"> generates a warning, and often the warning doesn't come to the forefront. My users have demanded a new solution.

The POIUtility.cfc worked, except the limited CSS (row-level only) isn't acceptable. Is there another, simpler, more  powerful utility out there? Even a paid solution would work.

Did I just happen, after the last few years, to encounter the first group unwilling to click "Yes" to an extra prompt?!
0
Comment
Question by:coldchillin
  • 12
  • 12
25 Comments
 
LVL 25

Expert Comment

by:dgrafx
ID: 35728248
You could use the cfspreadsheet tag - IF you are on CF 9

<cfquery datasource="#DSN#" name="getReport">
select some columns
from some table
where some thing is true
</cfquery>
<cfspreadsheet  
    action="write"
    filename="#filelocation##filename#"
    query="getReport"
    overwrite="true"
    sheetname="Data">
0
 
LVL 53

Expert Comment

by:_agx_
ID: 35730874
(no points...)

Did I just happen, after the last few years, to encounter the first group unwilling to click "Yes" to an extra prompt?!


I've had users grumble and ask why, but none refused to do it if that was the only way to get their data.  Granted the old html trick is - and always was - a hack.

The POIUtility.cfc worked, except the limited CSS (row-level only) isn't acceptable.

If you have advanced styling needs, I don't know that you'll find anything "simpler".  Since it's open source, have you thought of customizing it?

Aside from the row level css comment, what exactly are your needs? How much and what kind of styles, data volume, number of sheets, ...? The reason for asking is most options have limitations.  Excel itself has limitations w/formatting. cfspreadsheet (and others) can have performance issues w/high data volumes.  
0
 
LVL 1

Author Comment

by:coldchillin
ID: 35730990
Unfortunately we're on CF8, so no CFSPREADSHEET.

Yes, but what a wonderful hack it was.

I'm mainly needing column alignment, and another thing I noticed was that although I have SQL returning the correct format "40.00", the POIUtility zero suppresses (although it may be Excel itself).

I know I could convert the data into varchar, which could display in Excel correctly, but it would also give the "Number stored as text" warning, which is very likely going to be unacceptable.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 1

Author Comment

by:coldchillin
ID: 35731021
Also, I need custom column widths instead of using the default size applied to all cells.

My user is asking that I format the sheet to fit an 8 x 11 print, which would work if I sized (individual columns and fonts) for every excel report, however, when I print from Excel, it is resized to fit automatically.
0
 
LVL 1

Author Comment

by:coldchillin
ID: 35731113
Here's how I'm retrieving the data (sql returns trailing zeroes, but they are suppressed either through the utility, or excel itself).

CONVERT(DECIMAL(9,2), variable) AS column1
0
 
LVL 53

Expert Comment

by:_agx_
ID: 35731200
the POIUtility zero suppresses (although it may be Excel itself).

It's probably whatever default format the utility is using for numbers. IIRC POI is customizable ie the utility must be too.  Do you have a snippet of the code you're using now?

Also, I need custom column widths instead of using the default size applied to all cells.

I'm not sure if POIUtility supports that, but you could definitely add it.

however, when I print from Excel, it is resized to fit automatically

Yeah, since it's an Excel file (not a pdf) that should be done by users in Excel IMO.

0
 
LVL 53

Expert Comment

by:_agx_
ID: 35731630
>> Yeah, since it's an Excel file (not a pdf) that should be done by users in Excel IMO. <<
ie Client side printing/print settings should be done ... on the client side ;-)
0
 
LVL 1

Author Comment

by:coldchillin
ID: 35731654
I really don't see where, or how to tap in. For instance:

LOCAL.Sheet.SetDefaultColumnWidth(
                        JavaCast( "int", 15 )
                        );

Do I override this, or let it be and add a logic to set width. And if I do, how? Is there a method list that is available to me? LOCAL.Sheet.SetColumnWidth??
<cfset objPOI.WriteExcel(
		FilePath = filepath),
		Sheets = objSheet,
		HeaderCSS = "font-weight:bold; background: lime ; color: white ; border-bottom: 3px solid green ;",
		RowCSS = "border-bottom: 1px solid gold ;",
		AltRowCSS = "background-color: lemon_chiffon ;"
	) />

Open in new window

0
 
LVL 53

Expert Comment

by:_agx_
ID: 35731968
Do I override this, or let it be and add a logic to set width.

Either depending on what you want.  I'd probably avoid mucking w/the CSS and do it directly.  Unless your versions really old, it should also support auto-fitting the content

ie
LOCAL.Sheet.autoSizeColumn( JavaCast( "short", 0) );  // column 1
LOCAL.Sheet.autoSizeColumn( JavaCast( "short", 1) );  // column 2 ....
...
0
 
LVL 53

Expert Comment

by:_agx_
ID: 35732049
Is there a method list that is available to me? LOCAL.Sheet.SetColumnWidth??

Yeah, in POI's documentation
       http://poi.apache.org/apidocs/index.html

or by dumping the sheet object.  
<cfdump var="#Local.sheet#">
0
 
LVL 1

Author Comment

by:coldchillin
ID: 35732275
Great, documentation without implementation examples.
0
 
LVL 53

Expert Comment

by:_agx_
ID: 35732400
You asked for a list of methods and that's what the any java api gives you. Code examples right are on their site, if you'd looked.  They're in java of course. You do realize POI it's an open source java library ... right?  ;-) Not a commercial product or an element of the CF language.
0
 
LVL 1

Author Comment

by:coldchillin
ID: 35732946
Yeah. Just interested in the answer.



0
 
LVL 53

Expert Comment

by:_agx_
ID: 35733104
Which part, setting the column width? If you want columns to auto-fit the contents, then sheet.autoSizeColumn(colIndex) should work.

     // colIndex is 0 based
     LOCAL.Sheet.autoSizeColumn( JavaCast( "short", 0) );  

For a specific width, use sheet.setColumnWidth(colIndex, colWidth)

     // colIndex is 0 based. colWidth unit is 1/256th of a character width
     LOCAL.Sheet.autoSizeColumn( JavaCast( "int",  0),  JavaCast( "int",  6000) );  

http://poi.apache.org/apidocs/org/apache/poi/hssf/usermodel/HSSFSheet.html#setColumnWidth(int,%20int)

I don't know what version of POI you're running. So if you have issues, cfdump the sheet object to verify those methods are available in your version.
0
 
LVL 1

Accepted Solution

by:
coldchillin earned 0 total points
ID: 35733541
I appreciate all of your help...but this was enough to get me where I need to be:


POI Custom Tags - ColdFusion

http://www.bennadel.com/blog/1186-POI-ColdFusion-Custom-Tags-Almost-There.htm
0
 
LVL 53

Expert Comment

by:_agx_
ID: 35733578
*lol* I was going to mention it, but assumed you'd tried and rejected that one already.  Glad you found something that worked. Don't forget to close the question (just select your comment as the answer).
0
 
LVL 1

Author Comment

by:coldchillin
ID: 35733741
Thanks! Yeah, I pulled down the zip and tried the cfc version, and after that didn't work, I wanted to go for a paid, canned-solution. The custom tag solution works...but there's a lot of overhead in terms of maintenance and upkeep. Not ideal but I've spent far too much time trying to avoid a prompt!
0
 
LVL 53

Expert Comment

by:_agx_
ID: 35733830
Yeah, normally I'd say go with a paid tool. But when it comes to real Excel files there's really no "easy" method. Not that I've found. Despite what they tell you, once your needs go beyond basic b/w tables ... they all have painful/complex sides to them.  

I've spent far too much time trying to avoid a prompt!

Don't you just love users ;-)
0
 
LVL 1

Author Comment

by:coldchillin
ID: 35739992
b/w tables?

Isn't there a way...

Exporting data through SQL...could that be automated and triggered from an application through a stored procedure?
0
 
LVL 53

Expert Comment

by:_agx_
ID: 35740254
b/w tables?

black and white tables  ie A simple data export with no formatting ;-)

Exporting data through SQL...could that be automated and triggered from an application through a stored procedure?

Sure. Ms sql can do it.  Not sure about "formatting" though. I haven't done it in a while. I'd ask over in the MS SQL zones. One of the gurus there will know how to do it.
0
 
LVL 1

Author Comment

by:coldchillin
ID: 35748329
Yeah...that's what I'd gathered from doing research - the loss of formatting.

I'd read something about using templates and then inserting xml...but for now the custom tag solution should be adequate.
0
 
LVL 53

Expert Comment

by:_agx_
ID: 35748482
Yep, a template sounds about right.  Though overall, it's probably on par with the custom tag, in terms of complexity.  It's funny ... once you add in "formatting" it usually ends up being the most complex/time consuming part of the export.
0
 
LVL 1

Author Comment

by:coldchillin
ID: 35748498
First I had to implement an entirely new, hand-coded solution to avoid "The Prompt". Then, I needed to implement the custom tag solution to center a column.

I still cannot format a column displaying the dollar sign and comma without converting it to a varchar first...so hopefully I won't need ANOTHER solution!
0
 
LVL 53

Expert Comment

by:_agx_
ID: 35748553
I know you can do it with poi. So it's possible in the tag too. Though you might need to poke around in it's internals a bit.
0
 
LVL 1

Author Closing Comment

by:coldchillin
ID: 35763248
Not ideal, but gets the job done.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Enter Foreign and Special Characters Enter characters you can't find on a keyboard using its ASCII code ... and learn how to make a handy reference for yourself using Excel ~ Use these codes in any Windows application! ... whether it is a Micr…

580 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question