cfspreadsheet and modifing tab contents, getting tab exists on update

Question

I am attempting to read a 'named tab' of a spreadsheet into a table, update the values, then save them back to the same tab I am trying to see if its just me or something odd in the spreadsheet functions, pseudo code follows. ( I don't think I am having a coding problem but a cf limitation? specific cold withheld (may add later) )

• cfspreadsheet read sheetname query
• dump query -- ok
• update query and dump -- ok
• cfspreadsheet update sheetname query -- fail, already has sheet with sheetname -duh-
• (also cfspreadsheet write -- fails)

to get this to work I had resorted to

• read tab
• modify tab
• • read workbook -- new var
• • delete sheet -- new var
• • saveworkbook -- new var -- disk edition now sans tab..
• update workbook add sheet -- using var holding just the modified tab

-- this seems to be horribly resource intensive ... and just plain silly

• Forta example seems to be for single sheet workbooks only.

Thanks in advance.

Gary

-- update: Rather than using the tags I switched to functions in script. No use of query object. all cf implementation of POI Cleaner solution follows ... actual code

<cfscript>
// read workbood; set active sheet
sObj = SpreadsheetRead(expectedLocWName);
    SpreadsheetSetActiveSheet(sObj, 'Version');

    // internal code removed spreadsheet getcellvalue

// update
spreadsheetsetcellvalue(sObj, fileVersionNext, 5, 2 );

var overWriteMe = true;
spreadsheetwrite(sObj, expectedLocWName, overWriteMe);
</cfscript> 

Answer 1

The action is just poorly named. Per the docs: update - adds a new sheet to an existing XLS file. You cannot use the uppdate [sic] action to change an existing sheet in a file.

to get this to work I had resorted to

If you do not limit yourself to cfspreadsheet only, you should be able to "update" a sheet by reading in the file, with SpreadsheetRead. Then delete the sheet, and add/recreate it.

Technically you could skip the delete/recreate process and simply modify the existing sheet in place. Just read in the file, set the sheet you want to modify as active, then make your changes. However, depending on the modifications, it is often simpler to just delete the sheet and insert a new one.