How to create cfspreadsheet with secure cells

Question

I am creating a spreadsheet object using cfspreadsheet. I would like to make some of the individual cells protected (read-only). Please let me know if anyone has tried this before.

I tried to put the cell format as locked, but it does not seem to work. Here is a sample code:

<cfset a = spreadsheetnew()> <cfset format1 = structNew()> <cfset format1.locked=true> <cfset SpreadsheetFormatCell(a,format1,1,1)> <cfspreadsheet action="write" filename="#expandpath('.')#/test.xls" name="a" overwrite="true"> 

Thanks.

Answer 1

Locking a cell does nothing if the sheet is not protected , that is, using the cfspreadsheet password attribute. But it also has some negative side effects ...

Sheet protection blocks all cells. This means that you need to “unlock” everything else using the format. Theoretically, you can simply unlock the whole sheet:

 <cfset SpreadsheetFormatCellRange (sheet, {locked=false}, 1, 1, maxRow, maxCol)> 

However, this has the unpleasant effect of populating each individual cell on the sheet. Therefore, if you read the file in the query, the query will contain ~ 65,536 rows and 256 columns. Even if you just filled in a few cells explicitly.

The lock function is better suited for cases when you want everything to be locked, except for a few cells (and not vice versa). If this is not what you are doing, I probably would not bother with it, given all the negative side effects.

Side effect example

  <cfset testFile = "c:/test.xls"> <cfset sheet = spreadsheetNew()> <!--- only unlocking 100 rows to demonstrate ---> <cfset SpreadsheetFormatCellRange (sheet, {locked=false}, 1, 1, 100, 10)> <!--- populate two cells ---> <cfset SpreadsheetSetCellValue(sheet,"LOCKED",1,1)> <cfset SpreadsheetSetCellValue(sheet,"UNLOCKED",2,1)> <!--- make one cell locked ---> <cfset SpreadsheetFormatCell(sheet, {locked=true}, 1, 1)> <cfspreadsheet action="write" name="sheet" fileName="#testFile#" password="" overwrite="true" > <!--- now see it is filled with empty cells ---> <cfspreadsheet action="read" query="sheetData" src="#testFile#" > <cfdump var="#sheetData#" label="Lots of empty cells" />