Lock Specific Cells in Excel Worksheets using Java

In Excel, locking all cells is normally straightforward and you simply need to protect the worksheet, because the Lock attribute is automatically selected for all cells. Occasionally, you may only want to protect specific cells from overwriting, deleting, or editing. This article will demonstrate how to programmatically lock specific cells in an Excel worksheet using Java codes.

DEPENDENCY

Before running codes, you need to add Spire.Xls.jar to your Java program as a dependency. You can download the product package from this link, and then find it in the lib folder. Or if you use Maven, just write down the following code in the pom.xml file and click the button “Import Changes”.

<repositories>
<repository>
<id>com.e-iceblue</id>
<name>e-iceblue</name>
<url>https://repo.e-iceblue.com/nexus/content/groups/public/</url>
</repository>
</repositories>
<dependencies>
<dependency>
<groupId>e-iceblue</groupId>
<artifactId>spire.xls.free</artifactId>
<version>5.1.0</version>
</dependency>
</dependencies>

USING THE CODE

Free Spire.XLS for Java supports locking specific cells in an Excel worksheet using XlsRange.getStyle().setLocked() method. It’s mentioned that you need to unlock all cells before performing this operation, and finally set a password for protecting the worksheet so that the result will take effect. The following are detailed steps.

l  Create a Workbook instance.

l  Load an Excel sample document using Workbook.loadFromFile() method.

l  Get a specific worksheet using Workbook.getWorksheets().get(sheetIndex) method.

l  Access the used range in the worksheet and then unlock all cells in the range using XlsRange.getStyle().setLocked() method.

l  Access specific cells and then lock them using XlsRange.getStyle().setLocked() method.

l  Set a password to protect the worksheet using XlsWorksheetBase.protect() method.

l  Save to another file using Workbook.saveToFile() method.

import com.spire.xls.*;
import java.util.EnumSet;

public class LockCells {
    public static void main(String[] args) {
        //Create a Workbook instance
        Workbook workbook = new Workbook();

        //Load an Excel sample file
        workbook.loadFromFile("sample.xlsx");

        //Get the first worksheet
        Worksheet sheet = workbook.getWorksheets().get(0);

        //Unlock all cells in the used range of the worksheet
        CellRange usedRange = sheet.getRange();
        usedRange.getStyle().setLocked(false);

        //Lock specific cells
        CellRange cells = sheet.getRange().get("A2:B5");
        cells.getStyle().setLocked(true);

        //Protect the worksheet with a password
        sheet.protect("123456", EnumSet.of(SheetProtectionType.All));

        //Save the result file
        workbook.saveToFile("output/LockCells.xlsx", ExcelVersion.Version2016);
    }
}