Locked/Unlocked cells

xlsgen > overview > Locked/Unlocked cells

Locked/Unlocked cells is an important Excel feature that protects against alteration. This allows one to pass Excel workbooks to peers, let them fill in info in appropriate cells while leaving everything else intact. Or protect an entire worksheet against alteration. By default, all cells are locked, but the locking only takes effect when the lock is activated at the worksheet level. To activate it, the person editing the workbook goes in the Tools / Protect Sheet menu option and selects what to activate. Let's see how this works programmatically with xlsgen.

Protecting a sheet in Excel

The functionality is exposed through the IXlsWorksheetLockProperties interface. You can decide to protect everything in the worksheet, or only the content, or only the rich objects, or only the scenarios/scripts. You can get, set and reset the password.

Checking the locked/unlocked flag of a cell in Excel

A point to keep in mind about locking/unlocking cells is that Excel does not store the information on a per cell basis, it's stored on a per style basis. And what Excel does behind the scene is create a new style each time you change it. In other words, you'll find the flag exposed in the IXlsStyle interface of xlsgen.

Programmatically speaking, the interface is straight forward.

VB code

'
' Excel workbook creation
'

Sub generate()

  Dim engine As CoXlsEngine
  Set engine = CreateObject("ExcelGenerator.ARsTDesign")

  Dim wbk As IXlsWorkbook
  Set wbk = engine.New("myfile.xls")

  Dim wksht001 As IXlsWorksheet
  Set wksht001 = wbk.AddWorksheet("Sheet1")

  '
  ' Worksheet "Sheet1"
  '

  ' lock the content of the worksheet
  wksht001.LockProperties.Content = True

  ' cells are locked by default, so the following cells cannot be edited
  wksht001.Label(5,1) = "Locked"
  wksht001.Number(5,2) = 5

  ' create a style that keeps the unlocked flag
  Dim style As IXlsStyle
  Set style = wksht001.NewStyle
  style.Locked = False
  style.Apply

  ' those cells are unlocked
  wksht001.Label(7,1) = "Unlocked"
  wksht001.Number(7,2) = 9


  '
  ' Excel workbook epilogue
  '

  wbk.Close

  Set engine = Nothing

End Sub

C# code

  //
  // Excel workbook creation
  //

  xlsgen.CoXlsEngine excel = new xlsgen.CoXlsEngine();
  IXlsWorkbook wbk = excel.New( @"myfile.xls" );
  IXlsWorksheet wksht001 = wbk.AddWorksheet( "Sheet1" );


  //
  // Worksheet "Sheet1"
  //

  // lock the content of the worksheet
  wksht001.LockProperties.Content = true;

  // cells are locked by default, so the following cells cannot be edited
  wksht001.set_Label(5,1, "Locked");
  wksht001.set_Number(5,2, 5);

  // create a style that keeps the unlocked flag
  IXlsStyle style = wksht001.NewStyle();
  style.Locked = 0;
  style.Apply();

  // those cells are unlocked
  wksht001.set_Label(7,1, "Unlocked");
  wksht001.set_Number(7,2, 9);


  //
  // Excel workbook epilogue
  //

  wbk.Close();

C++ code

{
  //
  // Excel workbook creation
  //

  xlsgen::IXlsEnginePtr engine( __uuidof(xlsgen::CoXlsEngine) );

  xlsgen::IXlsWorkbookPtr wbk;
  wbk = engine->New( L"myfile.xls" );

  xlsgen::IXlsWorksheetPtr wksht001;
  wksht001 = wbk->AddWorksheet( L"Sheet1" );

  //
  // Worksheet "Sheet1"
  //

  // lock the content of the worksheet
  wksht001->LockProperties->Content = true;

  // cells are locked by default, so the following cells cannot be edited
  wksht001->Label[5][1] = L"Locked";
  wksht001->Number[5][2] = 5;

  // create a style that keeps the unlocked flag
  xlsgen::IXlsStylePtr style = wksht001->NewStyle();
  style->Locked = false;
  style->Apply();

  // those cells are unlocked
  wksht001->Label[7][1] = L"Unlocked";
  wksht001->Number[7][2] = 9;

  //
  // Excel workbook epilogue
  //

  wbk->Close();

}

Working with the password associated to the protection is possible and even quite easy. Something to know that might not be obvious at first sight is, because it is possible to reset the password, you can pass the password of your choice to a protected worksheet in order to overwrite the existing password. Doing so, you can remove the protection of any worksheet.

If that is so easy to overwrite the existing password, why does xlsgen even try to provide the feature in the first place? In fact, the audience matters at this point. xlsgen is used by software developers, thus providing them the most powerful tools is a good thing. Resulting Excel workbooks produced by xlsgen are often viewed by normal users who have no idea what xlsgen is : they only open workbooks in Excel, where the password protection is enforced or not.