Solved

ColdFusion Help with Excel creation

Posted on 2013-01-10
14
1,621 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
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 

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

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

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

The technique is by far very Simple! How we can export the ColdFusion query results to DOC file?  Well before writing this I researched a lot in Internet but did not found a good Answer anyways!  So i thought now i should share my small snippet w…
Hi. There are several upload tutorials using jquery and coldfusion. I found a very interesting one here Upload Your Files using Jquery & ColdFusion and Preview them (http://www.randhawaworld.com/) . I did keep the main js functions but made sever…
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
As a trusted technology advisor to your customers you are likely getting the daily question of, ‘should I put this in the cloud?’ As customer demands for cloud services increases, companies will see a shift from traditional buying patterns to new…

867 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

21 Experts available now in Live!

Get 1:1 Help Now