How to lock the specific range of cells while exporting grid to Excel?
In order to lock the columns when you export WinForms GridControl to excel, you can use QueryImportExportCellInfo event of GridExcelConverterControl class. The Protect() method can be used for protecting all the cells in sheet with specified password. To Lock the specific range of cells, all other cells should be unlocked by setting the Locked property as false.
Code Snippet
C#
ExcelEngine engine = new ExcelEngine();
IApplication app = engine.Excel.Application;
IWorkbook book = app.Workbooks.Create(1);
Syncfusion.GridExcelConverter.GridExcelConverterControl gecc = new Syncfusion.GridExcelConverter.GridExcelConverterControl();
//Triggering the event
gecc.QueryImportExportCellInfo += new Syncfusion.GridExcelConverter.GridImportExportCellInfoEventHandler(Gecc_QueryImportExportCellInfo);
SaveFileDialog saveFileDialog = new SaveFileDialog();
saveFileDialog.Filter = "Files(*.xlsx)|*.xlsx|Files(*.xls)|*.xls";
saveFileDialog.DefaultExt = ".xlsx";
saveFileDialog.FileName = "Sample";
gecc.GridToExcel(this.gridControl1.Model, book.Worksheets[0]);
//Protect the cells with password.
book.Worksheets[0].Protect("Password", ExcelSheetProtection.LockedCells | ExcelSheetProtection.UnLockedCells);
book.SaveAs(saveFileDialog.FileName);
//Event Customization.
private void Gecc_QueryImportExportCellInfo(object sender, Syncfusion.GridExcelConverter.GridImportExportCellInfoEventArgs e)
{
//Check whether the cell was locked in Grid or not.
if (! e.GridCell.ReadOnly)
{
e.ExcelCell.Value = e.GridCell.CellValue.ToString();
//Unlock the other cells.
e.ExcelCell.CellStyle.Locked = false;
e.Handled = true;
}
}
VB
Dim engine As New ExcelEngine()
Dim app As IApplication = engine.Excel.Application
Dim book As IWorkbook = app.Workbooks.Create(1)
Dim gecc As New Syncfusion.GridExcelConverter.GridExcelConverterControl()
'Triggering the event
AddHandler gecc.QueryImportExportCellInfo, AddressOf Gecc_QueryImportExportCellInfo
Dim saveFileDialog As New SaveFileDialog()
saveFileDialog.Filter = "Files(*.xlsx)|*.xlsx|Files(*.xls)|*.xls"
saveFileDialog.DefaultExt = ".xlsx"
saveFileDialog.FileName = "Sample"
gecc.GridToExcel(Me.gridControl1.Model, book.Worksheets(0))
'Protect the cells with password.
book.Worksheets(0).Protect("Password", ExcelSheetProtection.LockedCells Or ExcelSheetProtection.UnLockedCells)
book.SaveAs(saveFileDialog.FileName)
'Event Customization.
private void Gecc_QueryImportExportCellInfo(Object sender, Syncfusion.GridExcelConverter.GridImportExportCellInfoEventArgs e)
'Check wheather the cell was locked in Grid or not.
If Not e.GridCell.ReadOnly Then
e.ExcelCell.Value = e.GridCell.CellValue.ToString()
'Unlock the other cells.
e.ExcelCell.CellStyle.Locked = False
e.Handled = True
End If
Screenshot
Sample Links:
C# Excel Export with Locked Cells CS
VB Excel Export with Locked Cells VB
Conclusion
Hope you enjoyed learning about how to lock the specific range of cells while exporting grid to Excel.
You can refer to our WinForms GridControl feature tour page to learn about its other groundbreaking feature representations. You can explore our WinForms GridControl documentation to understand how to present and manipulate data.
For current customers, you can check out our WinForms components from the License and Downloads page. If you are new to Syncfusion, you can try our 30-day free trial to check out our WinForms GridControl and other WinForms components.
If you have any queries or require clarifications, please let us know in the comments section below. You can also contact us through our support forums, Direct-Trac, or feedback portal. We are always happy to assist you!
