How to lock columns in NAV 2018 using excel Buffer

davmac1

I need to lock (protect) all columns except one.
In Classic, the automation routines used, have protect and lock. In NAV 2018, the dotnet routines used do not seem to have these functions.
Name DataType Subtype Length
XlWrkSht DotNet Microsoft.Office.Interop.Excel.Worksheet.'Microsoft.Office.Interop.Excel, Version=15.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c'.Microsoft.Office.Interop.Excel.Worksheet provides them, but how to implement them is unclear.
I have to create spreadsheets to send to outsiders where they can only enter one column and send them back and then I use the modified column to update NAV.

AlexDen

Hi,

I added functions to protect sheet, you can find updated version on GitHub.

Below is an example how to use it:

xlBuf.AddColumnPr('Column Header 1',FALSE,xlBuf."Cell Type"::Text,'');
xlBuf.AddColumnPr('Column Header 2',FALSE,xlBuf."Cell Type"::Text,'');
xlBuf.AddColumnPr('Column Header 3',FALSE,xlBuf."Cell Type"::Text,'');

xlBuf.NewRow;
xlBuf.AddColumnPr(100,FALSE,xlBuf."Cell Type"::Number,'');
xlBuf.AddColumnPr(200,FALSE,xlBuf."Cell Type"::Number,'Unlocked'); // Allow edit cell
xlBuf.AddColumnPr('=A2+B2',TRUE,xlBuf."Cell Type"::Number,'Formula Hidden'); // Hide formula
      
// Password is Pass
xlBuf.ProtectSheet(
  'HEdftSyT8KPWhGzOmzHYTecQnB0jjcqSiJ+BT4dVSAO84CSydiTxV8Ko7KnqHmIWz0ofd1i9qSnqwibCYhNx8g==',
  'rrvL3+SaHgL5shjuB9BePg==',
  100000);

xlBuf.CreateBookAndOpenExcel('','Sheet1','',COMPANYNAME,USERID);

OpenXML stores password in encrypted strings - HashValue and saltValue.

Here is a function to convert password string to these values:
https://www.codeproject.com/Questions/5369775/Openxml-excel-lock-cells

I didn't implement it in this version.

But there is another way to get these values - create new workbook, protect sheet with password and save it.
Then change file extension from xlsx to zip and open it.
In the folder xl\worksheets will be sheet1.xml, need to open it and find sheetProtection node.
Here you can find HashValue, SaltValue and SpinCount - it is your encrypted password, which you have to pass into ProtectSheet function.

</sheetData>
<sheetProtection 
	algorithmName="SHA-512" 
	hashValue="cSeSDt4Mhl1M12H8JGJ/JZ1XZ4oWSqqJfrq0Hh3ggPRZS7d/bu2S4lld0JlwYqTx8wK9X6tYKZi55fmtzT8Agw=="
	saltValue="IzuINz/i6RcBz6ECeI1Q1g=="
	spinCount="100000"
	sheet="1"
	objects="1"
	scenarios="1"/>

DenSter

Unfortunately there's no Excel objects in AL, and Excel Buffer is not much use when you want to do formatting beyond making a cell bold

davmac1

This is C/AL not al.
Excel buffer looks the same, but we have a lot of Excel objects available.
Since it already uses openxml, I am hoping to find a lock function using it.
Otherwise, it looks like I need to use interop which I lack some working examples on how to use in C/AL.

AlexDen

Hi,

I did it in Nav 2017, you can try to transfer it to your system.

https://forum.mibuso.com/discussion/73152/

You will have to transfer all functions where global variables FreezePaneRowNo and FreezePaneColNo are used.
And call AddFreezePane() before WritheSheet().

davmac1

Alex - I loaded up your code in NAV2018 and tested it successfully.
I need to add protect sheet and lock cells - neither of which are clear to me how to do with NAV functions.
If you have time, can you tell me how to add these functions please.

AlexDen

Hi,

I added functions to protect sheet, you can find updated version on GitHub.

Below is an example how to use it:

xlBuf.AddColumnPr('Column Header 1',FALSE,xlBuf."Cell Type"::Text,'');
xlBuf.AddColumnPr('Column Header 2',FALSE,xlBuf."Cell Type"::Text,'');
xlBuf.AddColumnPr('Column Header 3',FALSE,xlBuf."Cell Type"::Text,'');

xlBuf.NewRow;
xlBuf.AddColumnPr(100,FALSE,xlBuf."Cell Type"::Number,'');
xlBuf.AddColumnPr(200,FALSE,xlBuf."Cell Type"::Number,'Unlocked'); // Allow edit cell
xlBuf.AddColumnPr('=A2+B2',TRUE,xlBuf."Cell Type"::Number,'Formula Hidden'); // Hide formula
      
// Password is Pass
xlBuf.ProtectSheet(
  'HEdftSyT8KPWhGzOmzHYTecQnB0jjcqSiJ+BT4dVSAO84CSydiTxV8Ko7KnqHmIWz0ofd1i9qSnqwibCYhNx8g==',
  'rrvL3+SaHgL5shjuB9BePg==',
  100000);

xlBuf.CreateBookAndOpenExcel('','Sheet1','',COMPANYNAME,USERID);

OpenXML stores password in encrypted strings - HashValue and saltValue.

Here is a function to convert password string to these values:
https://www.codeproject.com/Questions/5369775/Openxml-excel-lock-cells

I didn't implement it in this version.

But there is another way to get these values - create new workbook, protect sheet with password and save it.
Then change file extension from xlsx to zip and open it.
In the folder xl\worksheets will be sheet1.xml, need to open it and find sheetProtection node.
Here you can find HashValue, SaltValue and SpinCount - it is your encrypted password, which you have to pass into ProtectSheet function.

</sheetData>
<sheetProtection 
	algorithmName="SHA-512" 
	hashValue="cSeSDt4Mhl1M12H8JGJ/JZ1XZ4oWSqqJfrq0Hh3ggPRZS7d/bu2S4lld0JlwYqTx8wK9X6tYKZi55fmtzT8Agw=="
	saltValue="IzuINz/i6RcBz6ECeI1Q1g=="
	spinCount="100000"
	sheet="1"
	objects="1"
	scenarios="1"/>

davmac1

Thanks - I have been able to test this finally.
(I am probably 2 years away from their move to BC.)