So looking further into the documentation, SpreadSheetRemoveSheet was added in 9.01 so that does explain why my version is not recognizing the function. I should've noticed that but I was in hour 13 of my work day and it slipped past.
Any ideas on why the "update" isn't working? Thanks again, Jeff On Fri, Mar 14, 2014 at 11:22 AM, Jeff Howard <jeh...@gmail.com> wrote: > 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> >> ------------------------------------------------------------- > > >
