Solved

ColdFusion Help with Excel creation

Posted on 2013-01-10
14
1,689 Views
Last Modified: 2013-01-12
How can I add to my excel creation code, the following features:

-Print in landscape
-Reduce size to fit all columns on one page (I.E. Ready to print) set print area?

Code:

<cfif structKeyExists(FORM, "export") AND structKeyExists(VARIABLES, "Results")>

	<cfset sObj = SpreadsheetNew("Ship=#discipline1#")>
     <!--- create query param strings ----> 
    <cfset querySummary = "Sp = #discipline1#, Project = #discipline4#, Contract = #discipline7#, Records Found ("& Results.RecordCount &")">
    <cfset recordsSummary = "("& Results.RecordCount &") Records Found">

    <!--- add query parmas in 1st row and center align --->
    <cfset columnsInQuery = listLen(msrresults.columnList)>
    <cfset SpreadsheetMergeCells(sObj, 1, 1, 1, columnsInQuery)>
    <cfset SpreadSheetSetCellValue(sObj, querySummary, 1, 1)>
    <cfset SpreadsheetFormatRow(sObj, {alignment="center"}, 1)> 

    <!--- add record count in 2nd row and center align --->
   <!--- <cfset SpreadsheetMergeCells(sObj, 2, 2, 1, columnsInQuery)>
    <cfset SpreadSheetSetCellValue(sObj, recordsSummary, 2, 1)>
    <cfset SpreadsheetFormatRow(sObj, {alignment="center"}, 2)> --->
  	<!--- Create header row --->
     <cfset spreadsheetSetHeader(SObj,"","Status Report","")>
	<cfset SpreadsheetAddRow(sObj, "Num,D,TY Q,QTY C,UI,Part Desc,NN,RQ,Nm,,P/O,Sp,Pro")>
      <cfset SpreadsheetFormatRows(sObj, {bold=TRUE,fgcolor="grey_25_percent", alignment="center"},1 )> 
      <cfset SpreadsheetFormatRow(sObj, {bold=TRUE,fgcolor="light_yellow", alignment="center"},2 )> 
   <cfset SpreadsheetFormatColumn(sObj, {alignment="right", dataformat="mm/dd/yyyy"}, 15)>
     <cfset SpreadsheetAddRows(sObj, Results)>
     <cfset SpreadsheetFormatColumns(sObj, {fontsize="14",verticalalignment = "vertical_center",leftborder="thin",rightborder="thin",bottomborder="thin"},"1-13")>
   
   <cfset SpreadsheetFormatColumns(sObj, {alignment="center"}, "1-4")>
   
   <cfset SpreadSheetSetColumnWidth(sObj, 3, 3)> 
<cfset SpreadsheetFormatCell(sObj, {textwrap=true,verticalalignment = "vertical_justify"}, 2, 3)>
<cfset cellValue = "QTY"& chr(10) & "REQ"& chr(10) >
<cfset SpreadsheetSetCellValue(sObj, cellValue, 2, 3)>
   
    <cfset SpreadSheetSetColumnWidth(sObj, 3, 4)> 
<cfset SpreadsheetFormatCell(sObj, {textwrap=true,verticalalignment = "vertical_justify"}, 2, 4)>
<cfset cellValue = "QTY"& chr(10) & "REC"& chr(10) >
<cfset SpreadsheetSetCellValue(sObj, cellValue, 2, 4)>
   
     <cfset SpreadsheetSetRowHeight(sObj,1, (40))>
     
     <cfset SpreadsheetFormatCell(sObj, {textwrap=true}, 2, 3)>
  <cfset SpreadSheetSetColumnWidth(sObj, 3, 3)>
     
     <cfset maxRow = Results.recordCount +  (2)>
     <cfloop from="1" to="#maxRow#" index="rowNum">
         <cfset SpreadsheetSetRowHeight(sObj, rowNum, (40))>
     </cfloop>
     
      <cfset SpreadsheetFormatRow(sObj, {topborder="thin"},3)>
     
   <cfset SpreadsheetFormatColumn(sObj, {alignment="left", dataformat="mm/dd/yyyy"}, 14)>
     <!--- file name MUST be unique --->
     <cfset saveToFile = "C:\Report#createUUID()#.xls">
     <cfspreadsheet action="write" name="sObj" filename="#saveToFile#" overwrite="true">

     <cfheader name="Content-Disposition" value="inline; filename=Report.xls">
     <cfcontent type="application/excel" file="#saveToFile#" deletefile="yes">
     <!---- we're finished so exit --->
     <cfabort />
</cfif>

Open in new window

0
Comment
Question by:DJPr0
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 6
14 Comments
 
LVL 52

Expert Comment

by:_agx_
ID: 38765360
I don't see that functionality in the docs, but this thread shows a workaround.  Apply it just before saving the file.

<!--- get the underlying poi sheet --->
<cfset poiSheet = sObj.getWorkBook().getSheet("TheSheetName")>
<cfset ps = poiSheet.getPrintSetup()>
<cfset ps.setLandscape(true)>
<!--- fit to one page --->
<cfset ps.setFitHeight(1)>
<cfset ps.setFitWidth(1)>

<cfspreadsheet action="write" name="sObj" filename="#saveToFile#" overwrite="true">

Open in new window

0
 

Author Comment

by:DJPr0
ID: 38765447
Do I need to install the POI utility?

Error:
Variable POISHEET is undefined.
0
 
LVL 52

Expert Comment

by:_agx_
ID: 38765542
Where did you put the code I posted? It should replace the <cfspreadsheet action="write"> line.
0
The Orion Papers

Are you interested in becoming an AWS Certified Solutions Architect?

Discover a new interactive way of training for the exam.

 

Author Comment

by:DJPr0
ID: 38765571
Code location below.

Error:

 Variable POISHEET is undefined.
 
The error occurred in E:/SR.cfm: line 168

166 :      <!--- get the underlying poi sheet --->
167 :      <cfset poiSheet = sObj.getWorkBook().getSheet("#saveToFile#")>
168 :      <cfset ps = poiSheet.getPrintSetup()>
169 :      <cfset ps.setLandscape(true)>
170 :      <!--- fit to one page --->



<cfif structKeyExists(FORM, "export") AND structKeyExists(VARIABLES, "Results")>

	<cfset sObj = SpreadsheetNew("Ship=#discipline1#")>
     <!--- create query param strings ----> 
    <cfset querySummary = "Sp = #discipline1#, Project = #discipline4#, Contract = #discipline7#, Records Found ("& Results.RecordCount &")">
    <cfset recordsSummary = "("& Results.RecordCount &") Records Found">

    <!--- add query parmas in 1st row and center align --->
    <cfset columnsInQuery = listLen(msrresults.columnList)>
    <cfset SpreadsheetMergeCells(sObj, 1, 1, 1, columnsInQuery)>
    <cfset SpreadSheetSetCellValue(sObj, querySummary, 1, 1)>
    <cfset SpreadsheetFormatRow(sObj, {alignment="center"}, 1)> 

    <!--- add record count in 2nd row and center align --->
   <!--- <cfset SpreadsheetMergeCells(sObj, 2, 2, 1, columnsInQuery)>
    <cfset SpreadSheetSetCellValue(sObj, recordsSummary, 2, 1)>
    <cfset SpreadsheetFormatRow(sObj, {alignment="center"}, 2)> --->
  	<!--- Create header row --->
     <cfset spreadsheetSetHeader(SObj,"","Status Report","")>
	<cfset SpreadsheetAddRow(sObj, "Num,D,TY Q,QTY C,UI,Part Desc,NN,RQ,Nm,,P/O,Sp,Pro")>
      <cfset SpreadsheetFormatRows(sObj, {bold=TRUE,fgcolor="grey_25_percent", alignment="center"},1 )> 
      <cfset SpreadsheetFormatRow(sObj, {bold=TRUE,fgcolor="light_yellow", alignment="center"},2 )> 
   <cfset SpreadsheetFormatColumn(sObj, {alignment="right", dataformat="mm/dd/yyyy"}, 15)>
     <cfset SpreadsheetAddRows(sObj, Results)>
     <cfset SpreadsheetFormatColumns(sObj, {fontsize="14",verticalalignment = "vertical_center",leftborder="thin",rightborder="thin",bottomborder="thin"},"1-13")>
   
   <cfset SpreadsheetFormatColumns(sObj, {alignment="center"}, "1-4")>
   
   <cfset SpreadSheetSetColumnWidth(sObj, 3, 3)> 
<cfset SpreadsheetFormatCell(sObj, {textwrap=true,verticalalignment = "vertical_justify"}, 2, 3)>
<cfset cellValue = "QTY"& chr(10) & "REQ"& chr(10) >
<cfset SpreadsheetSetCellValue(sObj, cellValue, 2, 3)>
   
    <cfset SpreadSheetSetColumnWidth(sObj, 3, 4)> 
<cfset SpreadsheetFormatCell(sObj, {textwrap=true,verticalalignment = "vertical_justify"}, 2, 4)>
<cfset cellValue = "QTY"& chr(10) & "REC"& chr(10) >
<cfset SpreadsheetSetCellValue(sObj, cellValue, 2, 4)>
   
     <cfset SpreadsheetSetRowHeight(sObj,1, (40))>
     
     <cfset SpreadsheetFormatCell(sObj, {textwrap=true}, 2, 3)>
  <cfset SpreadSheetSetColumnWidth(sObj, 3, 3)>
     
     <cfset maxRow = Results.recordCount +  (2)>
     <cfloop from="1" to="#maxRow#" index="rowNum">
         <cfset SpreadsheetSetRowHeight(sObj, rowNum, (40))>
     </cfloop>
     
      <cfset SpreadsheetFormatRow(sObj, {topborder="thin"},3)>
     
   <cfset SpreadsheetFormatColumn(sObj, {alignment="left", dataformat="mm/dd/yyyy"}, 14)>
     <!--- file name MUST be unique --->
     <cfset saveToFile = "C:\Report#createUUID()#.xls">




<!--- get the underlying poi sheet --->
     <cfset poiSheet = sObj.getWorkBook().getSheet("TheSheetName")>
     <cfset ps = poiSheet.getPrintSetup()>
     <cfset ps.setLandscape(true)>
     <!--- fit to one page --->
     <cfset ps.setFitHeight(1)>
     <cfset ps.setFitWidth(1)>
     
     



     
     <!---<cfspreadsheet action="write" name="sObj" filename="#saveToFile#" overwrite="true">--->




     <cfheader name="Content-Disposition" value="inline; filename=Report.xls">
     <cfcontent type="application/excel" file="#saveToFile#" deletefile="yes">
     <!---- we're finished so exit --->
     <cfabort />
</cfif>

Open in new window

0
 
LVL 52

Expert Comment

by:_agx_
ID: 38765675
I see the problem. You need to replace "TheSheetName" with the name of the sheet in your workbook - in this line:

         <cfset poiSheet = sObj.getWorkBook().getSheet("TheSheetName")>

Looks like you're calling it "Ship=#discipline1#", so try:

      <cfset poiSheet = sObj.getWorkBook().getSheet("Ship=#discipline1#")>


          > Do I need to install the POI utility?

Sorry, didn't see that question.  No, it'll work w/just CF9.
0
 

Author Comment

by:DJPr0
ID: 38766719
Thanks _agx_ that was it!

The landscape is working but not size.

Print preview only shows a quarter of the page.
0
 
LVL 52

Expert Comment

by:_agx_
ID: 38767864
Hm.. seems the same as their example. Except for setting setAutobreaks maybe?   I can't test this, but try doing this:

     <cfset poiSheet = sObj.getWorkBook().getSheet("Ship=#discipline1#")>
     <cfset ps = poiSheet.getPrintSetup()>
     <cfset ps.setLandscape(true)>
     <cfset sObj.setAutobreaks(true) >

     <!--- fit to one page --->
     <cfset ps.setFitHeight(javacast("short", 1))>
     <cfset ps.setFitWidth(javacast("short", 1) )>

If there's no change, could be a bug...
0
 

Author Comment

by:DJPr0
ID: 38769118
Error received:

 The setAutobreaks method was not found.
Either there are no methods with the specified method name and argument types or the setAutobreaks method is overloaded with argument types that ColdFusion cannot decipher reliably. ColdFusion found 0 methods that match the provided arguments. If this is a Java object and you verified that the method exists, use the javacast function to reduce ambiguity.
 
The error occurred in E:/SR.cfm: line 170
Called from E:/SR.cfm: line 112
Called from E:/SR.cfm: line 1

168 :      <cfset ps = poiSheet.getPrintSetup()>
169 :      <cfset ps.setLandscape(true)>
170 :      <cfset sObj.setAutobreaks(true)>
171 :      <!--- fit to one page --->
172 :       <cfset ps.setFitHeight(javacast("short", 1))>
0
 
LVL 52

Expert Comment

by:_agx_
ID: 38769235
I guess it was added in a later version than the one in CF.  Saw a few threads suggesting setAutobreaks is needed to make autoFit work.  Not sure if that's correct, but if it is - then unfortunately you probably can't do this from CF.  Not without upgrading POI.
0
 

Author Comment

by:DJPr0
ID: 38769254
Could I use the POIUtility.cfc shown here:

http://www.bennadel.com/projects/poi-utility.htm

Not sure how to implement this:

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
0
 
LVL 52

Expert Comment

by:_agx_
ID: 38769294
If it says it supports fitToPage, you can try it.  BUT .. it uses the same POI  stuff that cfspreadsheet does (same version). So its very likely you'll get the same results.

Edit
> as well as setup a default print area to fit layout in 1 page width to the POIUtility.CFC

Hmm.. maybe they figured out a trick I don't know.  Let me look and see how they do it.
0
 
LVL 52

Accepted Solution

by:
_agx_ earned 500 total points
ID: 38769298
I can't test this, but try doing this:

     <cfset poiSheet = sObj.getWorkBook().getSheet("Ship=#discipline1#")>
     <cfset ps = poiSheet.getPrintSetup()>
     <cfset ps.setLandscape(true)>
    <cfset sObj.setAutobreaks(true) >
      ....

Ugh... sorry my bad.  They're doing the same thing, but one of the variables in my example was wrong.  It should be "poiSheet" not "sObj", doh!

       <cfset poiSheet.setAutobreaks(true) >
0
 

Author Closing Comment

by:DJPr0
ID: 38770378
That was it!

Thanks _agx_!
0
 
LVL 52

Expert Comment

by:_agx_
ID: 38770829
Welcome :)
0

Featured Post

Congratulations! You’re Certified – Now What?

Starting a new career can be overwhelming. Becoming certified in your field of expertise is a great start, but where do you go from here?  Here are some tips to help you on your career journey.

Question has a verified solution.

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

Today, I was working on some optimization and spam-stopping techniques when I encountered Ben Nadel's post to reduce spam feature using Math (http://www.bennadel.com/blog/197-How-I-Stop-Spammers-On-My-ColdFusion-Blog.htm). While this method is not o…
CFGRID Custom Functionality Series -  Part 1 Hi Guys, I was once asked how it is possible to to add a hyperlink in the cfgrid and open the window to show the data. Now this is quite simple, I have to use the EXT JS library for this and I achiev…
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

623 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