CODEWITHSUNDEEP
javacoldfusioncoldfusion-9coldfusion-10cfml
A DEv
A DEv

Reputation: 340

cfspreadhseet - how to delete/update a comment in the cell?

I am using cfspreadsheet to update the content of xl file, also am interested in adding comment for the user, so that he gets more information for the cell.

Am getting below error when I try to add a comment to a particular cell which has an existing comment, so in that case in the docs, I didn't see anyway to delete an existing comment or update an existing comment or clearing whole cell with data and comment....not sure how to manage comment in a cell ...(I fixed the error by adding anchor tag as per the reply I got from below, but now while i download the same file, I am not able to see the overwritten comment, and am getting xl exception for corrupted data)

The CF docs gives us one get and set function for comment(SpreadsheetSetCellComment & SpreadsheetGetCellComment) ....and the set function doesn't have overwrite attribute too...

Any thoughts??

Initial error without adding anchor tag: error :java.lang.IllegalArgumentException: Multiple cell comments in one cell are not allowed,

Error while opening xl file (after overwriting for comment) when we try to overwrite an existing comment, am getting below error from xl while opening the file.

enter image description here

logg: error357560_01.xmlErrors were detected in file 'C:\Users\username\Downloads\dtStatistics_870.xls'Removed Records: Comments from /xl/comments1.xml part (Comments)

Upvotes: 2

Views: 86

Answers (1)

CfSimplicity
CfSimplicity

Reputation: 2363

Try specifying the comment anchor (the position of the comments box) using the row and column numbers of the cell which contains the comment:

<cfscript>
path = ExpandPath( "test.xlsx" );
workbook = SpreadsheetNew( "sheet1", true ); //xlsx
// cell location
row = 1;
column = 1;
// use these values to position the comments box
anchor = [ row, column, row+2, column+2 ];
// create the cell
SpreadsheetSetCellValue( workbook, "test", row, column );
// create the initial comment
comment = {
    author: "cfsimplicity"
    ,comment: "a comment"
    ,anchor: ArrayToList( anchor )
};
SpreadsheetSetCellComment( workbook, comment, row, column );
WriteDump( SpreadsheetGetCellComment( workbook, row, column ) );
//Now update the comment
comment.comment = "an updated comment";
SpreadsheetSetCellComment( workbook, comment, row, column );
WriteDump( SpreadsheetGetCellComment( workbook, row, column ) );
SpreadsheetWrite( workbook, path, true );
</cfscript>

This should prevent temporary or default anchor values being used which I believe can lead to this issue.

Upvotes: 0

Related Questions