Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

ColdFusion Help with Excel creation

Posted on 2013-01-10
14
Medium Priority
?
1,752 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
[Webinar] Cloud Security

In this webinar you will learn:

-Why existing firewall and DMZ architectures are not suited for securing cloud applications
-How to make your enterprise “Cloud Ready”, and fix your aging DMZ architecture
-How to transform your enterprise and become a Cloud Enabler

 

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 2000 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

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

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

Hi, I will be creating today a basic tutorial on how we can create a Mail Custom Function and use it where ever we want. The main advantage about creating a custom function is that we can accommodate a range of arguments to pass to the Function and …
Recently while working on a project I got a very annoying cfdocument has no body error message. I had never seen this error before. So I checked the code. The code was pretty simple; it was Just showing me the cfdocumnt tag and inside that tag a …
In response to a need for security and privacy, and to continue fostering an environment members can turn to for support, solutions, and education, Experts Exchange has created anonymous question capabilities. This new feature is available to our Pr…
Is your data getting by on basic protection measures? In today’s climate of debilitating malware and ransomware—like WannaCry—that may not be enough. You need to establish more than basics, like a recovery plan that protects both data and endpoints.…
Suggested Courses

972 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