Solved

ColdFusion Help with Excel creation

Posted on 2013-01-10
14
1,612 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
  • 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
 

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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

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

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

This article  is about submitting  form through  ColdFusion.Ajax.submitForm to the action page and send a response back in JSON format which later can be decoded using ColdFusion.JSON.decode. By this way you can avoid the usual page refresh for subm…
I spent nearly three days trying to figure out how incorporate OAuth in Coldfusion for the Eventful API. Hopefully, this article will allow Coldfusion Programmers to buzz through the API when they need to. Basically, what this script does is authori…
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

744 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now