Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

ColdFusion How can I add a calculated field to my excel export function?

Posted on 2014-03-18
9
Medium Priority
?
847 Views
Last Modified: 2014-03-21
I have two fields in my excel export:
Qty Ordered & Qty Received

How can I automatically create another column "Amount Missing" with the formula:
Qty Ordered minus Qty Received = Amount Missing

Excel Export Code:

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

	<cfset sObj = SpreadsheetNew("Ship=#discipline1#")>
     <!--- create query param strings ----> 
    <cfset querySummary = "Ship = #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(results.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,"","Material Status Report","")>--->
	<cfset SpreadsheetAddRow(sObj, "Num,ISP,QTY Ordered,QTY REC,I,Part Description,S,Q,Num,,DO,hip,ject")>
      <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("Ship=#discipline1#")>
     <cfset ps = poiSheet.getPrintSetup()>
     <cfset ps.setLandscape(true)>
     <cfset poiSheet.setAutobreaks(true)>
     
     
    <!--- 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 ps.setFitHeight(javacast("short", 200))>
   <cfset ps.setFitWidth(javacast("short", 1))>
   <cfset poisheet.setMargin(POISheet.TopMargin,.10)>
   <cfset poisheet.setMargin(POISheet.BottomMargin,.10)>
   <cfset poisheet.setMargin(POISheet.LeftMargin, .10)>
   <cfset poisheet.setMargin(POISheet.RightMargin,.10)>

     
     
     
     <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
  • 5
  • 4
9 Comments
 
LVL 4

Expert Comment

by:Rodrigo Munera
ID: 39940221
To create the new column, you have to create it into your original query, or in the header row if the column will be at the end of the data.

Once you have the column, and you have populated your entire spreadsheet with your data, You have to put the formula in each cell.

In my code below I'm adding 2 formulas to 2 separate rows, I start from the second row, so my code will skip the header row and start inserting the formula in the second row of the spreadsheet, and I'm setting the "to" field to record count of my query +1 (to account for the header row being skipped)

In the code below "qReturn" is my query object with the data that was passed to the spreadsheet, "sprObj" is the spreadsheet object, my formulas are "IF(H[row]=0,0,I[row]/H[row])" and "IF(H[row]=0,0,J[row]/H[row])".

The syntax for entering a function into a spreadsheet is https://wikidocs.adobe.com/wiki/display/coldfusionen/SpreadsheetSetCellFormula

<cfloop from="2" to="#qReturn.recordcount+1#" index="i">
	<cfset SpreadSheetSetCellFormula(sprObj,"IF(H#i#=0,0,I#i#/H#i#)",i,12)>
	<cfset SpreadSheetSetCellFormula(sprObj,"IF(H#i#=0,0,J#i#/H#i#)",i,13)>
</cfloop>

Open in new window

0
 
LVL 4

Expert Comment

by:Rodrigo Munera
ID: 39940224
I use that loop after I've populated my spreadsheet object with
SpreadsheetAddRows(sprObj,qReturn);

Open in new window

0
 

Author Comment

by:DJPr0
ID: 39940838
Thanks for the reply Rodrigo.

I need some help with the formula:

If column 3 and 4 has a number, perform a minus calculation and output on column 5

Qty Ordered - Qty Received = Qty Missing  (Calculation is performed on each row)
       10                      5                     5
0
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
LVL 4

Assisted Solution

by:Rodrigo Munera
Rodrigo Munera earned 2000 total points
ID: 39940912
Excel formulas are based on the rows and columns where your data resides.

For this example, I'm going to assume:
Qty Ordered is on Column A
Qty Received is on Column B
Qty Missing is on Column C (the position of this column is 3)

Your formula in excel "=IF(AND(ISNUMBER(A1),ISNUMBER(B1)),A1-B1,0)"
In Coldfusion you don't use the "=" at the beginning of the formula, so you would put your formula as: "IF(AND(ISNUMBER(A1),ISNUMBER(B1)),A1-B1,0)"
Also, you want to replace the hard-coded rows with variables


<cfloop from="2" to="#qReturn.recordcount+1#" index="i">
	<cfset SpreadSheetSetCellFormula(sprObj,"IF(AND(ISNUMBER(A#i#),ISNUMBER(B#i#)),A#i#-B#i#,0)",i,3)>
</cfloop>

Open in new window

0
 
LVL 4

Expert Comment

by:Rodrigo Munera
ID: 39940920
Just to clarify, the formulas are not being calculated by coldFusion, cold fusion is only putting the formula in your spreadsheet (I'm assuming your user will have excel), and the actual calculation is made by the spreadsheet once it is opened by the user.
0
 

Author Comment

by:DJPr0
ID: 39941221
The formula seems to be downloading to the excel sheet ; )

Formula in excel sheet:
=IF(AND(ISNUMBER(C3),ISNUMBER(D3)),C3-D3,)

Problem:
All fields have 0

If I put the cursor in the formula and press enter the formula/calculation works.

I tried saving then opening without success - does something need to trigger the formula when the excel sheet opens?
0
 
LVL 4

Accepted Solution

by:
Rodrigo Munera earned 2000 total points
ID: 39941238
Hmm, that seems to be an excel question. In my spreadsheets the formula loads when I open it, but I'm also not using that number of functions. You could try stripping the formula down to just "C[row]-D[row]" and see if that does the trick.
0
 

Author Comment

by:DJPr0
ID: 39941320
Yes, If I strip it down it calculates the one with numbers in both cells and gives me an error with no value:

<cfset SpreadSheetSetCellFormula(sObj,"IF(ISNUMBER(C#i#),C#i#-D#i#, )",i,5)>

Strip down version works fine since one column always has a number.
0
 

Author Closing Comment

by:DJPr0
ID: 39944999
Thanks for your assistance Rodrigo Munera!
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
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…

721 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