Thanks Charlie.
The real solution would be sfspreadsheet = "update" to work as posted in my
post/email just prior to this. The idea to delete the spreadsheet was me
trying to figure out a work around for the update not working.
Any help with that (from anyone in the know) would be greatly appreciated.
The version of CF is 9,0,0,251028. I got an email from the hosting company
(Hostek) about updating my server to a CF10 server this morning. So if this
is an issue with CF 9,0,0,251028 and updating to a CF10 server would solve
the problem then I'll go that route. If there is just something I'm not
seeing in the code that is a quicker fix, I'll do that and the
upgrade/update the server over the weekend.
Once again on this is the error I'm getting:
An exception occurred while using action=update.
java.lang.IllegalArgumentException: The workbook already contains a
sheet
of this name
When I run this code that is reading a blank xls template, plugging in data
and trying to save it. The problem seems to be that the first spreadsheet
read is reading both sheets and then subsequently saving both sheets and
then when I try to do the update it says that the sheet already exists.
<cfspreadsheet action="read"
src="#application.rootDir#assets\C3d\IgG_Single_Template.xlsm"
name="Patient_Output" sheet="1">
<cfspreadsheet action="read"
src="#application.rootDir#assets\C3d\IgG_Single_Template.xlsm"
name="newCalc_P1" sheet="2">
<cfset
SpreadsheetSetCellValue(Patient_Output,patientResults.patientName,7,5)>
<cfset SpreadsheetSetCellValue(Patient_Output,patientResults.clinic,8,5)>
<cfset
SpreadsheetSetCellValue(Patient_Output,DateFormat(patientResults.patientDOB,"mm/dd/yyyy"),9,5)>
<cfset
SpreadsheetSetCellValue(Patient_Output,DateFormat(patientResults.sampleDate,"mm/dd/yyyy"),10,5)>
<cfset
SpreadsheetSetCellValue(Patient_Output,DateFormat(patientResults.reportDate,"mm/dd/yyyy"),11,5)>
<cfset SpreadsheetSetCellValue(Patient_Output,patientResults.doctor,12,5)>
<cfset SpreadsheetSetCellValue(Patient_Output,"link goes here",13,3)>
<!--- remove columns that should not be displayed in the charts --->
<cfset assayList = ListDeleteAt(getIgG.columnList,
ListFindNoCase(getIgG.columnList,"IgGID"))>
<cfset assayList = ListDeleteAt(assayList,
ListFindNoCase(assayList,"IgGValuesID"))>
<cfset assayList = ListDeleteAt(assayList,
ListFindNoCase(assayList,"patientID"))>
<cfset writeRow = 1>
<cfset writeColAdd = 0>
<cfloop list="#assayList#" index="curAssay">
<!--- Determine what row to write the value to. --->
<cfset writeRow = ++writeRow>
<cfif writeRow eq 46>
<cfset writeRow = 2>
<cfset writeColAdd = writeColAdd + 7>
</cfif>
<cfset curAssayValue = "getIgG.#curAssay#">
<!--- Determine what column to write the value to. --->
<cfif Evaluate(curAssayValue) lt 1>
<cfset writeCol = 2 + writeColAdd>
<cfelseif Evaluate(curAssayValue) gte 1 AND Evaluate(curAssayValue) lt
2>
<cfset writeCol = 3 + writeColAdd>
<cfelseif Evaluate(curAssayValue) gte 2 AND Evaluate(curAssayValue) lt
3>
<cfset writeCol = 4 + writeColAdd>
<cfelseif Evaluate(curAssayValue) gte 3 AND Evaluate(curAssayValue) lt
4>
<cfset writeCol = 5 + writeColAdd>
<cfelseif Evaluate(curAssayValue) gte 4>
<cfset writeCol = 6 + writeColAdd>
</cfif>
<!--- Write the value to cell. --->
<cfset
SpreadsheetSetCellValue(newCalc_P1,Evaluate(curAssayValue),writeRow,writeCol)>
<!--- --->
<cfset theValue = SpreadsheetGetCellValue(newCalc_P1,writeRow,writeCol)>
</cfloop>
<!--- Write both sheets to the new file. --->
<cfspreadsheet action="write" filename="#theFile#" name="Patient_Output"
overwrite="true">
<cfspreadsheet action="update" filename="#theFile#" name="newCalc_P1"
sheetname="newCalc_P1">
<!--- Redirect user to the patient spreadsheet. --->
<cflocation url="/C3d/individual_results/#patientResults.lname#.xlsm">
On Fri, Mar 14, 2014 at 9:55 AM, Charlie Arehart <char...@carehart.org>wrote:
> Jeff, I have never used any of that functionality, but since you say you
> are on CF9, have you applied any/all updates? There may be bugs that were
> long-since fixed, but I find that many people (or their server admin's)
> never bother to update CF, especially 9 and earlier since it was often
> quite a bear to do.
>
> If you want to know what version you have (9.0, 9.0.1, or 9.0.2) and what
> updates you have, I have a blog entry helping with that:
>
>
> http://www.carehart.org/blog/client/index.cfm/2012/6/18/what_hotfixes_have_been_applied
>
>
> And if you may decide to proceed with some update(s), do proceed with
> caution. I share some warnings here:
>
> http://www.carehart.org/blog/client/index.cfm/2011/10/21/why_chfs_may_break
>
> Note as well that if you think that downloading an installer from Adobe
> should mean it has all the updates already applied, that's not so, as I
> discuss here:
>
>
> http://www.carehart.org/blog/client/index.cfm/2010/12/11/cfmyths_latest_installer_includes_all_updates
>
> And if you may wonder how to find available hotfixes, I provide links to
> them in that last blog entry.
>
> Finally, if you may be tempted to go to 9.0.2 (not saying that's your
> solution necessarily), consider the info I share here:
>
>
> http://www.carehart.org/blog/client/index.cfm/2013/8/19/understanding_ColdFusion_9.0.2_a_FAQ
>
> But I appreciate that none of this may help solve your problem. Maybe
> there's something obvious you're doing or not that someone more familiar
> with the spreadsheet features may help with. I just wanted to offer this as
> something to consider in the meantime.
>
> /charlie
>
>
>
> *From:* ad...@acfug.org [mailto:ad...@acfug.org] *On Behalf Of *Jeff
> Howard
> *Sent:* Friday, March 14, 2014 1:15 AM
> *To:* discussion@acfug.org
> *Subject:* [ACFUG Discuss] does SPREADSHEETREMOVESHEET not work?
>
>
>
> I copied the code straight from Adobe's site and rewrote it using
> cfscript, cfset, cfset temp =, cfset obj = and all of them threw the same
> error.
>
>
> Variable SPREADSHEETREMOVESHEET is undefined.
>
>
>
> Here is the various code I tried in CF 9:
>
> <cfset spreadsheetRemoveSheet(Patient_Output,"newCalc_P1")>
>
> <cfset temp = spreadsheetRemoveSheet(Patient_Output,"newCalc_P1")>
>
> <cfset Patient_Output
> = spreadsheetRemoveSheet(Patient_Output,"newCalc_P1")>
>
>
>
>
> <cfscript>
> Patient_Output = spreadsheetRemoveSheet(Patient_Output,"newCalc_P1");
> </cfscript>
>
>
>
> I thought that with all the spreadsheet functionality that this project
> would be fairly quick and need it tomorrow. I was trying to find a work
> around for the duplicate sheet error in the last message I posted so
> thought maybe if I deleted the sheet first and then saved it I would have a
> work around and instead I just got another error message.
>
>
>
> Help!!!
>
>
>
> Thanks in advance for any and all help,
>
> Jeff
>
> -------------------------------------------------------------
> To unsubscribe from this list, manage your profile @
> http://www.acfug.org?fa=login.edituserform
>
> For more info, see http://www.acfug.org/mailinglists
> Archive @ http://www.mail-archive.com/discussion%40acfug.org/
> List hosted by FusionLink <http://www.fusionlink.com>
> -------------------------------------------------------------
