Why Excel treating cells as protected even if they

问题:

I have provided protection and also hide the sheet 'MasterData' out of which I have created some named range which i have used in second sheet 'customerAssets'. Here is code snippet :

    workbook.setSheetHidden(0,  true);  //to hides masterData
    workbook.setActiveSheet(1);          // sets active sheet as Customer Assets Sheet
    masterDataSheet.protectSheet("12345");  // protect MasterData sheet

But after opening excel: enter image description here

1. It doesnt allow to edit "Customer Assets" sheet saying its protected.
2. But if I open "Sheet1" and then "Customer Assets" it allows to edit.

What could have been wrong with it?

回答1:

A sheet in Excel or Calc can be both, active (the sheet in front of you in the view) and selected (more than one sheet can be selected as a group).

The first created sheet will always be both active and selected. So if your MasterData sheet is the first created sheet, then it is active and selected. The workbook.setActiveSheet changes the active state but not the selected state. So your MasterData sheet stays selected. Changes in the active cell will always be applied to all selected sheets. So you do really try to change a protected cell, since the protected MasterData sheet is selected also.

If you select single sheets with a mouse click, then the selection will be changed and the selected group of sheets will no more be selected.

We need at least to unselect the MasterData sheet. But we should also select another sheet too.

import java.io.*;

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.*;

public class CreateSheets {

 public static void main(String[] args) throws Exception {
  Workbook workbook = new XSSFWorkbook();
  Sheet masterDataSheet = workbook.createSheet("MasterData"); //first sheeet will be both active and selected
  Sheet customerAssetsSheet = workbook.createSheet("Customer Assets");
  Sheet sheet1 = workbook.createSheet("Sheet1");

  workbook.setSheetHidden(0,  true);       //hide masterDataSheet
  masterDataSheet.setSelected(false);      //unselect masterDataSheet

  workbook.setActiveSheet(1);              //sets active sheet as Customer Assets Sheet
  //customerAssetsSheet.setSelected(true); //not necessary but recommended: set Customer Assets Sheet selected

  masterDataSheet.protectSheet("12345");   // protect MasterData sheet

  FileOutputStream fileOut = new FileOutputStream("CreateSheets.xlsx");
  workbook.write(fileOut);
  fileOut.close();
  workbook.close();
 }
}