How to create a cfspreadsheet with protected cells

cfspreadsheet, coldfusion

Solution

Locking a cell does nothing unless the sheet is protected ie using cfspreadsheet's `password` attribute. But doing so has some negative side effects ...

Protecting the sheet locks all cells. That means you essentially have to "unlock" everything else by applying a format. In theory you could just unlock the entire sheet:

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

However, that has the nasty effect of populating every single cell in the sheet. So if you read the file into a query, the query would contain ~65,536 rows and 256 columns. Even if you only populated a few cells explicitly.

The lock feature is better suited to cases where you want everything to be locked except a few cells (not the reverse). Unless that is 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" />

Problem

I am creating a spreadsheet object using cfspreadsheet. Would like to make some of the individual cells as protected (read-only). Please let me know if anybody has tried this before. I did try putting cell format as locked but it did not seems to work. Here is the 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.

Original source