• Global community
    • Language:
      • Deutsch
      • English
      • Español
      • Français
      • Português
  • 日本語コミュニティ
    Dedicated community for Japanese speakers
  • 한국 커뮤니티
    Dedicated community for Korean speakers
Exit
0

Protect spreadsheet with cfspreadsheet

New Here ,
Feb 12, 2016 Feb 12, 2016

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

Views

865

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Expert ,
Feb 14, 2016 Feb 14, 2016

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.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
New Here ,
Feb 17, 2016 Feb 17, 2016

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">

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
New Here ,
Feb 17, 2016 Feb 17, 2016

Copy link to clipboard

Copied

LATEST

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">

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Resources
Documentation