Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1799
  • Last Modified:

ColdFusion Help with Excel creation

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
DJPr0
Asked:
DJPr0
  • 8
  • 6
1 Solution
 
_agx_Commented:
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
 
DJPr0Author Commented:
Do I need to install the POI utility?

Error:
Variable POISHEET is undefined.
0
 
_agx_Commented:
Where did you put the code I posted? It should replace the <cfspreadsheet action="write"> line.
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
DJPr0Author Commented:
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
 
_agx_Commented:
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
 
DJPr0Author Commented:
Thanks _agx_ that was it!

The landscape is working but not size.

Print preview only shows a quarter of the page.
0
 
_agx_Commented:
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
 
DJPr0Author Commented:
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
 
_agx_Commented:
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
 
DJPr0Author Commented:
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
 
_agx_Commented:
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
 
_agx_Commented:
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
 
DJPr0Author Commented:
That was it!

Thanks _agx_!
0
 
_agx_Commented:
Welcome :)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 8
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now