Copy link to clipboard
Copied
I am trying to create a locked version of spreadsheet before letting users download it. The password function for cfspreadsheet does not seem to work. the basic operation is this: I read an existing spreadsheet, and then I write it to a new file using the password option. But the file is not protected. I have looked at cfspreadsheet password, but that is not giving me any insight to the issue. I have also tried to use spreadsheetfomatcellrange function to lock all of the cells in the sheet, but that didn't work either.
On a secondary note, I am seeing some behavior that I wasn't expecting. The Excel file that I am trying to recreate has 3 sheets. When I read the file, I am getting all three sheets and not just the first one. Then when I write the sheet, I am getting all three sheets in the new file. I was expecting to have to read each sheet individually then then write them individually as well.
<cfset xfile = '#application.doc_directory_file#' & '#URL.fname#'>
<cfset new_file = '#application.doc_directory_file#' & 'locked' & '#URL.fname#'>
<cfspreadsheet action="read" src = "#xfile#" sheet="1" name="pilot_sheet">
<cfspreadsheet action="write" filename="#new_file#" sheetname="Pilot Instructions" name="pilot_sheet" password="pwd" overwrite="true">
Any help would be much appreciated.
Thanks
DW
Copy link to clipboard
Copied
Your code worked for me. I copied a test Excel file that has 3 sheets to my desktop. I then created a directory named locked, and ran the following code
<cfset xfile = 'C:\Users\BKBK\Desktop\testFile.xlsx'>
<cfset new_file = 'C:\Users\BKBK\Desktop\locked\testFile.xlsx'>
<!---
<cfset xfile = 'C:\Users\BKBK\Desktop\testFile.xls'>
<cfset new_file = 'C:\Users\BKBK\Desktop\locked\testFile.xls'>
--->
<cfspreadsheet action="read" src = "#xfile#" sheet="1" name="pilot_sheet">
<cfspreadsheet action="write" filename="#new_file#" sheetname="Pilot Instructions" name="pilot_sheet" password="pwd" overwrite="true">
Coldfusion duly created a new Excel file in the locked directory. The new file is read-only (check!) and has just one sheet (check!). I get this result for XLS and XLSX test files.
The difference between my code and yours is the path. Check your paths: display tha values of xfile and new_file. Also, just to be sure, empty the locked directory before testing.
Copy link to clipboard
Copied
Thanks for looking at this! All my paths were just fine. The files were being read and written from and to where they were supposed to be.
I have made some progress. I am now able to get the sheets to be read only. The problem is that in order to do this I end up with 5 sheets instead of 3. The read function still is reading in all of the sheets and the initial write function writes all three sheets. In the subsequent read and update call, it seems to be reading and updating the single sheets, but I have to call them different names or I get an error that there is already a sheet with that name. Basically, I am starting with a file that has 3 sheets (first_sheet, second_sheet, third_sheet). In order to get all of the sheets locked, I end up with a file that has 5 sheets (first_sheet, second_sheet, third_sheet, second_sheet1, third_sheet1) with first_sheet, second_sheet1 and third_sheet1 being protected.
Any ideas on why that initial read and write captures all three sheets? Could it be an environmental issue? Below is the code that I"m using now. I'd be glad to share the initial Excel file and processed Excel file if that might help.
<cfset xfile = 'C:\Users\david\Desktop\test.xls'>
<cfset new_file = 'C:\Users\david\Desktop\lockedtest.xls'>
<cfspreadsheet action="read" src = "#xfile#" sheetname="first_sheet" name="pilot_sheet">
<cfspreadsheet action="write" filename="#new_file#" sheetname="first_sheet" name="pilot_sheet" password="pwd" overwrite="true">
<cfspreadsheet action="read" src = "#xfile#" sheetname="second_sheet" name="pilot_sheet2">
<cfspreadsheet action="update" filename="#new_file#" sheetname="second_sheet1" name="pilot_sheet2" password="pwd">
<cfspreadsheet action="read" src = "#xfile#" sheetname="third_sheet" name="pilot_sheet3">
<cfspreadsheet action="update" filename="#new_file#" sheetname="third_sheet1" name="pilot_sheet3" password="pwd">
Copy link to clipboard
Copied
Quick update. It looks like the write function is the culprit. I modified the code above to create a spreadsheet with 1 sheet and then read the three sheets from the file and use the update function to write them to the new file. I end up with a locked file with 4 sheets, the initial dummy sheet that I created the file with and then the 3 sheets that were read from the existing file.
<cfset xfile = 'C:\Users\david\Desktop\test.xls'>
<cfset new_file = 'C:\Users\david\Desktop\lockedtest.xls'>
<!--- Create new file with 1 sheet --->
<cfset sheet = SpreadSheetNew("FirstSheet") />
<cfspreadsheet action="write" filename="#new_file#" sheetname="dummysheet" name="sheet" password="pwd" overwrite="true">
<!--- Read 3 sheets from existing file and save to new file --->
<cfspreadsheet action="read" src = "#xfile#" sheetname="first_sheet" name="pilot_sheet">
<cfspreadsheet action="update" filename="#new_file#" sheetname="first_sheet" name="pilot_sheet" password="pwd">
<cfspreadsheet action="read" src = "#xfile#" sheetname="second_sheet" name="pilot_sheet2">
<cfspreadsheet action="update" filename="#new_file#" sheetname="second_sheet1" name="pilot_sheet2" password="pwd">
<cfspreadsheet action="read" src = "#xfile#" sheetname="third_sheet" name="pilot_sheet3">
<cfspreadsheet action="update" filename="#new_file#" sheetname="third_sheet1" name="pilot_sheet3" password="pwd">