Protect Excel sheet in C#, VB.NET, Java, C++, PHP

Protect Excel sheet

EasyXLS™ library enables you to protect and lock Excel cells and Excel sheet objects (images, charts, comments, etc.) in order to prevent editing or deleting the sheet elements.

The component also allows setting a sheet password for worksheets and chart sheets when protecting the Excel sheet elements. The password is optional. If set, the user cannot edit the sheet elements until the valid password is supplied.

EasyXLS also permits to protect formulas and hide formulas expressions.

Source code sample
Locked cells
Hidden formulas
Protect sheet with password
Protect Excel sheet objects
Protect locked cells and unlocked cells selection
Protect cells, rows and columns formatting
Protect rows and columns insertion
Protect hyperlink insertion
Protect rows and columns deletion
Protect sort and autofilter usage
Protect pivot table and pivot chart usage
Protect worksheet scenarios
Protect chart sheet content

Source code sample

The below example shows how to export data to Excel, protect Excel sheet and lock cells.

C++

// Create an instance of the class that exports Excel file
EasyXLS::IExcelDocumentPtr workbook;
hr = CoCreateInstance(__uuidof(EasyXLS::ExcelDocument),
		      NULL,
		      CLSCTX_ALL,
		      __uuidof(EasyXLS::IExcelDocument),
		      (void**) &workbook);

// Create two sheets
workbook->easy_addWorksheet_2("First tab");
workbook->easy_addWorksheet_2("Second tab");

// Protect Excel sheet
workbook->easy_getSheetAt(0)->setSheetProtected(true);

// Get the table of data for the first worksheet that will keep the data
EasyXLS::IExcelWorksheetPtr xlsFirstTab = 
        (EasyXLS::IExcelWorksheetPtr)workbook->easy_getSheetAt(0);
EasyXLS::IExcelTablePtr xlsFirstTable = xlsFirstTab->easy_getExcelTable();

...

// Create a formatting style for cells
EasyXLS::IExcelStylePtr xlsStyleData;
hr = CoCreateInstance(__uuidof(EasyXLS::ExcelStyle),
		      NULL,
		      CLSCTX_ALL,
		      __uuidof(EasyXLS::IExcelStyle),
		      (void**) &xlsStyleData) ;

xlsStyleData->setHorizontalAlignment(ALIGNMENT_ALIGNMENT_LEFT);
xlsStyleData->setForeground(COLOR_DARKGRAY);
xlsStyleData->setWrap(false);
// Apply locked cells property to the formatting style
xlsStyleData->setLocked(true);
xlsStyleData->setDataType(DATATYPE_STRING);

// Add data to cells for report values
char*  rowNumber = (char*)malloc(sizeof(char));
for (int row=0; row<100; row++)
{
    for (int column=0; column<5; column++)
    {
	    strcpy(cellValue, "Data ");	
	    _itoa(column+ 1, columnNumber , 10);
	    _itoa(row + 1, rowNumber , 10);

	    strcat(cellValue, rowNumber);
	    strcat(cellValue, ", ");
	    strcat(cellValue, columnNumber);

	    xlsFirstTable->easy_getCell(row+1,column)->setValue(cellValue); 
	    xlsFirstTable->easy_getCell(row+1,column)->setStyle(xlsStyleData);
    }
}

// Export Excel file
workbook->easy_WriteXLSXFile("C:\\Samples\\Protected Excel sheet.xlsx");

Click here to see Continuous Code Listing


C++.NET

// Create an instance of the class that exports Excel file, having two sheets
ExcelDocument ^workbook = gcnew ExcelDocument(2);
    
// Set the sheet names
workbook->easy_getSheetAt(0)->setSheetName("First tab");
workbook->easy_getSheetAt(1)->setSheetName("Second tab");

// Protect Excel sheet
workbook->easy_getSheetAt(0)->setSheetProtected(true);

// Get the table of data for the first worksheet that will keep the data
ExcelWorksheet ^xlsFirstTab = 
        safe_cast<ExcelWorksheet^>(workbook->easy_getSheetAt(0));
ExcelTable ^xlsFirstTable = xlsFirstTab->easy_getExcelTable();

...

// Add data to cells for report values
for (int row=0; row<100; row++)
{
    for (int column=0; column<5; column++)
    {
        xlsFirstTable->easy_getCell(row+1,column)->setValue(String::Concat(
            "Data ", (row + 1).ToString(), ", ", (column + 1).ToString()));
    }
}

// Create a formatting style for cells
ExcelStyle ^xlsStyleData = gcnew ExcelStyle();
xlsStyleData->setHorizontalAlignment(Alignment::ALIGNMENT_LEFT);
xlsStyleData->setForeground(Color::DarkGray);
xlsStyleData->setWrap(false);
xlsStyleData->setDataType(DataType::STRING);
// Apply locked cells property to the formatting style
xlsStyleData->setLocked(true);
xlsFirstTable->easy_setRangeStyle("A2:E101", xlsStyleData);

// Export Excel file
workbook->easy_WriteXLSXFile("C:\\Samples\\Protected Excel sheet.xlsx");

Click here to see Continuous Code Listing

.NET:

// Create an instance of the class that exports Excel file
$workbook = new COM("EasyXLS.ExcelDocument");

// Create two sheets
$workbook->easy_addWorksheet_2("First tab");
$workbook->easy_addWorksheet_2("Second tab");

// Protect Excel sheet
$workbook->easy_getSheetAt(0)->setSheetProtected(true);

// Get the table of data for the first worksheet that will keep the data
$xlsFirstTable = $workbook->easy_getSheetAt(0)->easy_getExcelTable();

...

// Create a formatting style for cells
$xlsStyleData = new COM("EasyXLS.ExcelStyle");
$xlsStyleData->setHorizontalAlignment($ALIGNMENT_ALIGNMENT_LEFT);
$xlsStyleData->setForeground((int)$COLOR_DARKGRAY);
$xlsStyleData->setWrap(false);
// Apply locked cells property to the formatting style
$xlsStyleData->setLocked(true);
$xlsStyleData->setDataType($DATATYPE_STRING);

// Add data to cells for report values
for ($row=0; $row<100; $row++)
{
    for ($column=0; $column<5; $column++)
    {
        $xlsFirstTable->easy_getCell($row+1,$column)->setValue(
            "Data " . ($row + 1) . ", " . ($column + 1));
        $xlsFirstTable->easy_getCell($row+1,$column)->setStyle($xlsStyleData);
    }
}

// Export Excel file
$workbook->easy_WriteXLSXFile("C:\Samples\Protected Excel sheet.xlsx");

Click here to see Continuous Code Listing


Java:

// Create an instance of the class that creates Excel files
$workbook = new java("EasyXLS.ExcelDocument");
	
// Create two worksheets
$workbook->easy_addWorksheet("First tab");
$workbook->easy_addWorksheet("Second tab");
	
// Protect first sheet
$workbook->easy_getSheetAt(0)->setSheetProtected(true);

// Get the table of data for the first worksheet
$xlsFirstTable = $workbook->easy_getSheetAt(0)->easy_getExcelTable();

...

// Create a formatting style for cells
$xlsStyleData = new java("EasyXLS.ExcelStyle");
$xlsStyleData->setHorizontalAlignment($ALIGNMENT_ALIGNMENT_LEFT);
$xlsStyleData->setForeground(java("java.awt.Color")->LIGHT_GRAY);
$xlsStyleData->setWrap(false);
// Protect cells
$xlsStyleData->setLocked(true);
$xlsStyleData->setDataType($DATATYPE_STRING);
	
// Add data in cells for report values
for ($row=0; $row<100; $row++)
{
    for ($column=0; $column<5; $column++)
    {
        $xlsFirstTable->easy_getCell($row+1,$column)->setValue("Data " . ($row + 1) .
                                                               ", " . ($column + 1));
        $xlsFirstTable->easy_getCell($row+1,$column)->setStyle($xlsStyleData);
    }
}

// Export Excel file
$workbook->easy_WriteXLSXFile("C:\Samples\Protected Excel sheet.xlsx");

Click here to see Continuous Code Listing

.NET:

# Create an instance of the class that creates Excel files, having two sheets
workbook = ExcelDocument(2)

# Set the sheet names
workbook.easy_getSheetAt(0).setSheetName("First tab")
workbook.easy_getSheetAt(1).setSheetName("Second tab")

# Protect first sheet
workbook.easy_getSheetAt(0).setSheetProtected(True)

# Get the table of data for the first worksheet
xlsFirstTable = workbook.easy_getSheetAt(0).easy_getExcelTable()

...

# Add data in cells for report values
for row in range(100):
    for column in range(5):
        xlsFirstTable.easy_getCell(row+1, column).setValue("Data " + str(row + 1) + ", " + str(column + 1))

# Create a formatting style for cells
xlsStyleData = ExcelStyle()
xlsStyleData.setHorizontalAlignment(Alignment.ALIGNMENT_LEFT)
xlsStyleData.setForeground(Color.DarkGray)
xlsStyleData.setWrap(False)
xlsStyleData.setDataType(DataType.STRING)
xlsStyleData.setLocked(True)
xlsFirstTable.easy_setRangeStyle("A2:E101", xlsStyleData)

# Create Excel file
workbook.easy_WriteXLSXFile("C:\\Samples\\Protected Excel sheet.xlsx")

Click here to see Continuous Code Listing


Java:

# Create an instance of the class that creates Excel files, having two sheets
workbook = gateway.jvm.ExcelDocument(2)

# Set the sheet names
workbook.easy_getSheetAt(0).setSheetName("First tab")
workbook.easy_getSheetAt(1).setSheetName("Second tab")

# Protect first sheet
workbook.easy_getSheetAt(0).setSheetProtected(True)

# Get the table of data for the first worksheet
xlsFirstTable = workbook.easy_getSheetAt(0).easy_getExcelTable()

...

# Add data in cells for report values
for row in range(100):
    for column in range(5):
        xlsFirstTable.easy_getCell(row+1, column).setValue("Data " + str(row + 1) + ", " + str(column + 1))

# Create a formatting style for cells
xlsStyleData = gateway.jvm.ExcelStyle()
xlsStyleData.setHorizontalAlignment(gateway.jvm.Alignment.ALIGNMENT_LEFT)
xlsStyleData.setForeground(gateway.jvm.Color.LIGHT_GRAY)
xlsStyleData.setWrap(False)
xlsStyleData.setDataType(gateway.jvm.DataType.STRING)
xlsStyleData.setLocked(True)
xlsFirstTable.easy_setRangeStyle("A2:E101", xlsStyleData)

# Create Excel file
workbook.easy_WriteXLSXFile("C:\\Samples\\Protected Excel sheet.xlsx")

Click here to see Continuous Code Listing

The screen shot provides an example of an exported Excel file that has the sheet protected.

Locked cells

EasyXLS allows you to lock the Excel cells and prevent cell content and cell formatting to be edited using ExcelStyle.setLocked, ExcelCell.setLocked, ExcelColumn.setLocked and ExcelRow.setLocked methods.

Hidden formulas

EasyXLS allows you to hide the Excel formulas expressions using ExcelStyle.setHiddenFormula, ExcelCell.setHiddenFormula, ExcelColumn.setHiddenFormula and ExcelRow.setHiddenFormula methods.

The below source code sample shows how to hide formula expression from "A6 " cell. The sheet that contains the formula must be also protected.

C++

// Create an instance of the class that exports Excel files
EasyXLS::IExcelDocumentPtr workbook;
hr = CoCreateInstance(__uuidof(EasyXLS::ExcelDocument),
		      NULL,
		      CLSCTX_ALL,
		      __uuidof(EasyXLS::IExcelDocument),
		      (void**) &workbook) ;

// Add a sheet
workbook->easy_addWorksheet_2("Formula");

// Get the table of data for the sheet
EasyXLS::IExcelWorksheetPtr xlsFirstTab =
	 (EasyXLS::IExcelWorksheetPtr)workbook->easy_getSheet("Formula");
EasyXLS::IExcelTablePtr xlsTable = xlsFirstTab->easy_getExcelTable();

// Add data in cells and set the formula
xlsTable->easy_getCell_2("A1")->setValue("1");
xlsTable->easy_getCell_2("A2")->setValue("2");
xlsTable->easy_getCell_2("A3")->setValue("3");
xlsTable->easy_getCell_2("A4")->setValue("4");
xlsTable->easy_getCell_2("A6")->setValue("=SUM(A1:A4)");
xlsTable->easy_getCell_2("A6")->setHiddenFormula(true);

// Protect Excel sheet
workbook->easy_getSheet("Formula")->setSheetProtected(true);

// Export Excel file
workbook->easy_WriteXLSXFile("C:\\Samples\\Hidden formula.xlsx");


C++.NET

// Create an instance of the class that exports Excel files
ExcelDocument ^workbook = gcnew ExcelDocument();

// Add a sheet
workbook->easy_addWorksheet("Formula");

// Get the table of data for the sheet
ExcelWorksheet^ xlsFirstTab = 
        safe_cast<ExcelWorksheet^>(workbook->easy_getSheet("Formula"));
ExcelTable^ xlsTable = xlsFirstTab->easy_getExcelTable();
	
// Add data in cells and set the formula
xlsTable->easy_getCell("A1")->setValue("1");
xlsTable->easy_getCell("A2")->setValue("2");
xlsTable->easy_getCell("A3")->setValue("3");
xlsTable->easy_getCell("A4")->setValue("4");
xlsTable->easy_getCell("A6")->setValue("=SUM(A1:A4)");
xlsTable->easy_getCell("A6")->setHiddenFormula(true);

// Protect Excel sheet
workbook->easy_getSheet("Formula")->setSheetProtected(true);

// Export Excel file
workbook->easy_WriteXLSXFile("C:\\Samples\\Hidden formula.xlsx");

Protect sheet with password

EasyXLS allows you to protect the sheet with an optional password using ExcelSheet.setProtectionPassword method.

Protect Excel sheet objects

EasyXLS automatically protects the Excel sheet objects like images, charts, comments and other objects when the sheet is protected using ExcelSheet.setSheetProtected method.

In order to change this behavior use ExcelWorksheet.setAllowEditObjects method for worksheets or ExcelChartSheet.setProtectObjects for chart sheets.

Protect locked cells and unlocked cells selection

EasyXLS automatically allows the selection of cells when the sheet is protected using ExcelSheet.setSheetProtected method.

In order to change this behavior use ExcelWorksheet.setAllowSelectLockedCells and ExcelWorksheet.setAllowSelectUnlockedCells methods.

Protect cells, rows and columns formatting

EasyXLS does not automatically allow to format cells, rows or columns when the sheet is protected using ExcelSheet.setSheetProtected method.

In order to change this behavior use ExcelWorksheet.setAllowFormatCells, ExcelWorksheet.setAllowFormatRows or ExcelWorksheet.setAllowFormatColumns methods.

Protect rows and columns insertion

EasyXLS does not automatically allow to insert rows or columns when the sheet is protected using ExcelSheet.setSheetProtected method.

In order to change this behavior use ExcelWorksheet.setAllowInsertColumns or ExcelWorksheet.setAllowInsertRows methods.

Protect hyperlink insertion

EasyXLS does not automatically allow to insert hyperlinks when the sheet is protected using ExcelSheet.setSheetProtected method.

In order to change this behavior use ExcelWorksheet.setAllowInsertHyperlinks method.

Protect rows and columns deletion

EasyXLS does not automatically allow to delete rows or columns when the sheet is protected using ExcelSheet.setSheetProtected method.

In order to change this behavior use ExcelWorksheet.setAllowDeleteColumns or ExcelWorksheet.setAllowDeleteRows methods.

Protect sort and autofilter usage

EasyXLS does not automatically allow sorting and filtering worksheet data when the sheet is protected using ExcelSheet.setSheetProtected method.

In order to change this behavior use ExcelWorksheet.setAllowSort or ExcelWorksheet.setAllowUseAutofilter methods.

Protect pivot table and pivot chart usage

EasyXLS does not automatically allow to use pivot tables or pivot charts when the sheet is protected using ExcelSheet.setSheetProtected method.

In order to change this behavior use ExcelWorksheet.setAllowUsePivots method.

Protect worksheet scenarios

EasyXLS automatically protects the Excel worksheet scenarios when the sheet is protected using ExcelSheet.setSheetProtected method.

In order to change this behavior use ExcelWorksheet.setAllowEditScenarios method.

Protect chart sheet content

EasyXLS automatically protects the content of the chart sheet when the sheet is protected using ExcelSheet.setSheetProtected method.

In order to change this behavior use ExcelChartSheet.setProtectContents method.

Getting started with EasyXLS Excel library

To download the trial version of EasyXLS Excel Library, press the below button:

If you already own a license key, you may login and download EasyXLS from your account.

Available for: Professional, Excel Writer, Excel Reader

EasyXLS Excel libraries:
.NET Excel Library
full .NET version to import, export or convert Excel files
COM+ Excel Library
full COM+ version to import, export or convert Excel files


Java Excel Library
full Java version to import, export or convert Excel files

File formats:
MS Excel 97 - 2003 MS Excel 2007 - 2019 MS Excel 2021 Office 365

Protect Excel sheet

Table of contents

Source code sample

Locked cells

Hidden formulas

Protect sheet with password

See also:

How to create a password protected Excel file?

Protect Excel sheet objects

Protect locked cells and unlocked cells selection

Protect cells, rows and columns formatting

Protect rows and columns insertion

Protect hyperlink insertion

Protect rows and columns deletion

Protect sort and autofilter usage

Protect pivot table and pivot chart usage

Protect worksheet scenarios

Protect chart sheet content

Getting started with EasyXLS Excel library

See also:

Getting started with EasyXLS - .NET Excel Library

Getting started with EasyXLS - Java Excel Library

Getting started with EasyXLS - COM+ Excel Component

Related methods:

EasyXLS Excel libraries:

.NET Excel Library

COM+ Excel Library

Java Excel Library

File formats: