Enable Hyper Link from a Locked Sheet

Hi Team,

Our requirement is to Lock the Whole Excel and enable only Hyper Link with text as NASDAQ Report Disclaimer .I achieved through the following piece of Code.

Cell RptCell= sheet.Cells.Find(“NASDAQ Report Disclaimer”, null, findOptions);



if (RptCell!= null)

{

int Rptnumber=Cell.Row;

mktstyle = sheet.Cells.Rows[Rptnumber].Style;

mktstyle.IsLocked = false;

flag.Locked = true;

sheet.Cells.ApplyRowStyle((Rptnumber), mktstyle, flag);





}



Up on Single Click the Link gets activated and redirects to static Page but afterwards if you click any where on the Excel sheet it is redirecting to Disclaimer this should not happen



Please find the attached Excel sheet for reference

Hi Darshan,


Thank you for contacting Aspose support.

I have evaluated your presented scenario while using the latest major revision of Aspose.Cells for .NET 8.7.1 and following piece of code against a sample of my own. I am afraid, I am not able to replicate the behaviour as discussed in your post, however, your shared spreadsheet shows the said problem. Please give a try to the latest revision on your side as well. In case the problem persists, please provide us an executable sample application along with your input spreadsheet for our testing.

C#

var workbook = new Workbook(dir + “book1.xlsx”);
var sheet = workbook.Worksheets[0];
Cell RptCell = sheet.Cells.Find(“Some Text”, null, new FindOptions() { CaseSensitive = false, LookAtType = LookAtType.EntireContent, LookInType = LookInType.Values });
if (RptCell != null)
{
int Rptnumber = RptCell.Row;
var mktstyle = sheet.Cells.Rows[Rptnumber].Style;
mktstyle.IsLocked = false;
sheet.Cells.ApplyRowStyle((Rptnumber), mktstyle, new StyleFlag() { Locked = true });
}
sheet.Protect(ProtectionType.All);
workbook.Save(dir + “output.xlsx”);

Hi Team,

Please find sample Code and input File.I have tried with 8.6.3 and 8.7.1 version dlls still same issue is existing

Hi Team,
Is there any update on the issue we reported?

Hi Darshan,


We are currently looking into the scenario as per your provided sample and we will shortly get back with updates in this regard.

Hi Darshan,


Thank you for your patience.

As per your original message, your requirement is to allow clicks on only one cell, that is; cell containing the text “NASDAQ Report Disclaimer” whereas all other cells should be locked. In order to accomplish this task, I have opted a slightly different approach as elaborated below. Also attached is the resultant spreadsheet for your reference.

C#

var book = new Workbook(dir + “IntradayPriceChart.xlsx”);
var sheet = book.Worksheets[0];
var cells = sheet.Cells;

//Identifying the selectable cell
FindOptions findOptions = new FindOptions();
findOptions.LookAtType = LookAtType.Contains;
findOptions.CaseSensitive = false;
var cell = cells.Find(“NASDAQ Report Disclaimer”, null, findOptions);
if (cell != null)
{
//Get the Allow Edit Ranges
ProtectedRangeCollection allowRanges = sheet.AllowEditRanges;
//Define ProtectedRange
ProtectedRange proteced_range;

//Create the range
int idx = allowRanges.Add(“NASDAQ Report Disclaimer”, cell.Row, cell.Column, cell.Row, cell.Column);
proteced_range = allowRanges[idx];
//Set password
proteced_range.Password = “Sample”;
}

sheet.Protect(ProtectionType.All);
book.Save(dir + “output.xlsx”);

Thanks Babar this Works Only for One of the case.

There is One more scenario where we need to enable Some of the Excel rows based on (“Sample Commentary Text”).In that case HyperLink is still disabled ,Can you please help us out on this issue?

Hi Darshan,


Please note, the source code provided in my previous response is for demonstration purposes therefore you should amend it to meet your application requirements. Regarding your recent concerns, I am not able to find any hyperlinks in your previously shared spreadsheet IntradayPriceChart.xlsx with text “Sample Commentary Text” so I have updated the spreadsheet and made all cells as hyperlinks which contains value ending with the word “Commentary” and used it to test the scenario. Please check the following piece of code as well as input & output spreadsheets (attached).

C#

var book = new Workbook(dir + “IntradayPriceChart.xlsx”);
var sheet = book.Worksheets[0];
var cells = sheet.Cells;
//Get the Allow Edit Ranges
ProtectedRangeCollection allowRanges = sheet.AllowEditRanges;
//Define Protected Range
ProtectedRange proteced_range;

//Identifying the selectable cell
FindOptions findOptions = new FindOptions();
findOptions.LookAtType = LookAtType.EntireContent;
findOptions.CaseSensitive = false;
Cell cell = cells.Find(“NASDAQ Report Disclaimer”, null, findOptions);
if (cell != null)
{
//Create the range
int idx = allowRanges.Add(cell.Value.ToString(), cell.Row, cell.Column, cell.Row, cell.Column);
proteced_range = allowRanges[idx];
//Set password
proteced_range.Password = “Sample”;
}

//Find text containing Commentary
findOptions = new FindOptions();
findOptions.LookAtType = LookAtType.EndWith;
findOptions.CaseSensitive = false;
cell = null;
do
{
cell = cells.Find(“Commentary”, cell, findOptions);
if (cell != null)
{
//Create the range
int idx = allowRanges.Add(cell.Value.ToString(), cell.Row, cell.Column, cell.Row, cell.Column);
proteced_range = allowRanges[idx];
//Set password
proteced_range.Password = “Sample”;
}
}
while (cell != null);
sheet.Protect(ProtectionType.All);

book.Save(dir + “output.xlsx”);