1 Reply Latest reply on Mar 11, 2014 1:51 PM by vishu#13

    cfspreadsheet password

    anodynepres

      Adding a password to cfspreadsheet and saving the file has no effect.  I can still open the file without dialogs and edit anything.  This is true even if I use spreadsheetformatcell to change the lock status.  Does this actually work?  Anybody have an example that works?

        • 1. Re: cfspreadsheet password
          vishu#13 Level 3

          The password attribute action is for update and write : http://help.adobe.com/en_US/ColdFusion/9.0/CFMLRef/WSc3ff6d0ea77859461172e0811cbec17cba-7f 87.html

           

          Run the code mentioned below and then open the updatedFile.xls in excel. It will open in a protected view where you cannot make any modifications.

           

          CODE

           

          <!--- Read data from two datasource tables. --->

          <cfquery

                 name="courses" datasource="cfdocexamples"

                 cachedwithin="#CreateTimeSpan(0, 6, 0, 0)#">

                 SELECT CORNUMBER,DEPT_ID,COURSE_ID,CORNAME

                 FROM COURSELIST

          </cfquery>

           

          <cfquery

                 name="centers" datasource="cfdocexamples"

                 cachedwithin="#CreateTimeSpan(0, 6, 0, 0)#">

                 SELECT *

                 FROM CENTERS

          </cfquery>

           

          <cfscript>

              //Use an absolute path for the files. --->

                 theDir=GetDirectoryFromPath(GetCurrentTemplatePath());

              theFile=theDir & "courses.xls";

              //Create two empty ColdFusion spreadsheet objects. --->

              theSheet = SpreadsheetNew("CourseData");

              theSecondSheet = SpreadsheetNew("CentersData");

              //Populate each object with a query. --->

              SpreadsheetAddRows(theSheet,courses);

              SpreadsheetAddRows(theSecondSheet,centers);

          </cfscript>

           

          <!--- Write the two sheets to a single file --->

          <cfspreadsheet action="write" filename="#theFile#" name="theSheet" 

              sheetname="courses" overwrite=true>

          <cfspreadsheet action="update" filename="#theFile#" name="theSecondSheet"

              sheetname="centers">

           

          <!--- Read all or part of the file into a spreadsheet object, CSV string,

                HTML string, and query. --->

          <cfspreadsheet action="read" src="#theFile#" sheetname="courses" name="spreadsheetData">

          <cfspreadsheet action="read" src="#theFile#" sheet=1 rows="3,4" format="csv" name="csvData">

          <cfspreadsheet action="read" src="#theFile#" format="html" rows="5-10" name="htmlData">

          <cfspreadsheet action="read" src="#theFile#" sheetname="centers" query="queryData">

           

          <h3>First sheet row 3 read as a CSV variable</h3>

          <cfdump var="#csvData#">

           

          <h3>Second sheet rows 5-10 read as an HTML variable</h3>

          <cfdump var="#htmlData#">

           

          <h3>Second sheet read as a query variable</h3>

          <cfdump var="#queryData#">

           

          <!--- Modify the courses sheet. --->

          <cfscript>

              SpreadsheetAddRow(spreadsheetData,"03,ENGL,230,Poetry 1",8,1);

              SpreadsheetAddColumn(spreadsheetData,

              "Basic,Intermediate,Advanced,Basic,Intermediate,Advanced,Basic,Intermediate,Advanced",

              3,2,true);

          </cfscript>

           

          <!--- Write the updated Courses sheet to a new XLS file   This one has the password--->

          <cfspreadsheet action="write" filename="#theDir#updatedFile.xls" name="spreadsheetData" 

              sheetname="courses" overwrite=true password="test">

           

           

           

          <!--- Write an XLS file containing the data in the CSV variable.--->    

          <cfspreadsheet action="write" filename="#theDir#dataFromCSV.xls" name="CSVData" 

              format="csv" sheetname="courses" overwrite=true>