apply password in cfspreadsheet with multiples sheets

Question

how to protect the two sheets? currently the code only protects the first sheet.

var plan = spreadsheetNew("TEST", true);
SpreadsheetCreateSheet(plan , "TEST2" );
cfspreadsheet(
  action="write", fileName="temp.xslx", name="plan", 
  overwrite=true, sheetname="TEST", password="123"
);

I also tested omitting the sheet name in cfspreadsheet

cfspreadsheet(action="write", fileName="temp.xslx", name="plan", overwrite=true, password="123");

Answer 1

UPDATE(based on comments)

If you want to be able to change selected sheet I would revert back to your code and then use the internal Java libraries to set passwords.

plan = spreadsheetNew("TEST", true);
SpreadsheetCreateSheet(plan , "TEST2" );
workbookJavaObj = plan.getWorkBook();
workbookJavaObj.getSheetAt(0).protectSheet('123');
workbookJavaObj.getSheetAt(1).protectSheet('123');
spreadsheetSetActiveSheet(plan, "TEST2");
cfspreadsheet(
  action="write", fileName="temp.xlsx", name="plan", overwrite=true
);

Coldfusion solution

I think this is how it should be done. Create a new spreadsheet object and utilize the action="update" to add that sheet in to the file which was written earlier.

plan = spreadsheetNew("TEST", true);
cfspreadsheet(action="write", fileName="temp.xlsx", name="plan", overwrite=true, sheetname="TEST", password="123");
plan2 = spreadsheetNew("TEST2", true);
cfspreadsheet(action="update", fileName="temp.xlsx", name="plan2", sheetname="TEST2", password="123");