Protection: Allow users to edit ranges

Aspose.Cells Product Family
1

Hello,

I have a template.xls file that I upload into my application, populate cells with values and then spit it back out. In the template file I've specified ranges on each tab that my client can edit and I have locked down everything else. I've specified these protected ranges in excel while creating the template and not in code. For some reason after I run the template through my process and download it with my updated values the unprotected ranges that I specifed when I created the template are now protected. The whole thing is protected now. Any ideas?

Thanks,

Nate

2

Please specify the generated file format as FileFormatType.ExcelXP or FileFormatType.Excel2003.

3

Thanks for the reply. Unfortunately i tried both file format types and neither one worked. I originally had the file format type at default. Here is my code. I'm currently running Excel 2003.

I tried both of these:

objExcel.Save("Report.xls",FileFormatType.ExcelXP,SaveType.OpenInExcel,Response);

objExcel.Save("Report.xls",FileFormatType.Excel2003,SaveType.OpenInExcel,Response);

Thanks,

Nate

4

Could you please post your template file and your sample code here? I will check it right now.

5

Hello Laurence. I've posted my template file. I'm not sure the best way to post my code though. I'm passing the Aspose.excel object all over my my object model. All I do is import this excel template and drop some numbers in certain cells and then spit it back out to the user. The columns in red are the cell ranges that I let my users edit in. All other areas are protected. Here is my method that does the meat of the work. Thanks

private void btnDownLoad_Click(object sender, System.EventArgs e)

{

try

{

Cache.Remove(Session.SessionID + "_ExcelViewer");

}

catch{}

try

{

objExcel = new Aspose.Excel.Excel();

objExcel.Open(System.Configuration.ConfigurationSettings.AppSettings["ExcelLocation"]);

GTAccounting.Data.Guarantor objG = new Guarantor();

DataTable dtGuarantor = objG.GetGuarantorData(gtUser._userKey,Convert.ToInt32(((GTAccounting.controls.periodSelector)PeriodSelector1).ddlMonth.SelectedValue),Convert.ToInt32(((GTAccounting.controls.periodSelector)PeriodSelector1).ddlYear.SelectedValue),"");

AccountsProcessed oAP = new AccountsProcessed();

DataTable dtProcessed = oAP.GetProcessedAccounts(gtUser._userKey,Convert.ToInt32(((GTAccounting.controls.periodSelector)PeriodSelector1).ddlMonth.SelectedValue),Convert.ToInt32(((GTAccounting.controls.periodSelector)PeriodSelector1).ddlYear.SelectedValue));

if(dtGuarantor.Rows.Count > 0)

{

OTT objT = new OTT(dtProcessed);

objSheets = objExcel.Worksheets;

objT.Sheets = objSheets;

RoundedYTD objR = new RoundedYTD(dtProcessed);

objSheets = objExcel.Worksheets;

objR.Sheets = objSheets;

PandL objPandL = new PandL(dtGuarantor,dtProcessed);

objSheets = objExcel.Worksheets;

objPandL.Sheets = objSheets;

PandLAllied objPandLAllied = new PandLAllied(dtGuarantor,dtProcessed);

objSheets = objExcel.Worksheets;

objPandLAllied.Sheets = objSheets;

RoundedBalance oRB = new RoundedBalance(dtProcessed);

objSheets = objExcel.Worksheets;

oRB.Sheets = objSheets;

Reclasses oR = new Reclasses(dtProcessed);

objSheets = objExcel.Worksheets;

oR.Sheets = objSheets;

BalanceSheet objBalanceSheet = new BalanceSheet(dtGuarantor,dtProcessed);

objSheets = objExcel.Worksheets;

objBalanceSheet.Sheets = objSheets;

//objExcel.CalculateFormula(true);

objExcel.Save("Report.xls",FileFormatType.Excel2003,SaveType.OpenInExcel,Response);

}

else

{

sFrameLoad = "";

_CurrentButton = 0;

this.litFrame.Text = sFrameLoad;

}

}

catch(Exception error)

{

WiredViews.Exceptions.ExceptionsLog objE = new WiredViews.Exceptions.ExceptionsLog(error);

sFrameLoad = "";

_CurrentButton = 0;

this.litFrame.Text = sFrameLoad;

}

}

6

My mistake. “Allow users to edit ranges” settings in template file is not supported in previous version. I added this feature to this attached fix. Please try it.

7

That dll didn't seem to do the trick for me either. I tried exporting in both ExcelXP and Excel2003. Still would not let me edit unprotected ranges.

Thanks.

8

Please check if you deploy the new dll in your project and rebuild it.

This attached file includes:

1. The new dll. It's v3.1.6.2.

2. My test sample code.

3. Output file.

9

Hi

I have exactly the same problem whereas I'm working with v4.8.

How can I use the "Allow User To Edit Ranges" with this version ?

Thanks

10

Hi,

Well, the API of the feature ““Allow User To Edit Ranges”” are recently supported in one of the latest fixes under v4.8.2.x version’s series.

So, I am afraid, there is no way but to use/upgrade to latest version/fix. I have attached the latest fix for you here. Here is my sample code:

Workbook book = new Workbook();
book.Open(“e:\test\ProtectSheetTemplate.xlsm”);
Worksheet sheet = book.Worksheets[0];
AllowEditRanges allowRanges = sheet.AllowEditRanges;
AllowEditRange range;
if (allowRanges.Count > 0)
{
range = allowRanges[0];
MessageBox.Show(range.Name);
MessageBox.Show(" " + range.CellArea.StartRow + “,” + range.CellArea.StartColumn + “,”
+ range.CellArea.EndRow + “,” + range.CellArea.EndColumn);
}
//Add another range.
int idx = allowRanges.Add(“r2”, 1, 1, 3, 3);
range = allowRanges[idx];
range.Password = “1”;

book.Save(“e:\test\outProtectSheetTemplate.xlsm”);


Thank you.

11

Hi, thanks for your response.

I have tried with this version and it works fine with ".xltm" templates files. The trouble is that I'm working with ".xlt" (Excel 2003) templates files because I have to be compatible with Excel 2003 version.

The problem are :

1. If my template contains Edit Ranges I don't see them in code using the worksheet.AllowEditRanges collection.

2. If I create those edit ranges in my code and then I save the document, they don't appear in the final "xls" document. So, in Excel Application, I can not edit ranges and I don't see them anymore in the "Allow Users to Edit Ranges" dialog box.

12

Hi,

Could you post your template xlt file containing Edit Ranges. We will look into it soon.

Thank you.

13

Hi,

My template contains 1 edit ranges (Name : Range1, Columns D and E are "allow to edit ranges" enabled.

This is my code:

Workbook workbook = new Workbook();

string templateFileName = Environment.CurrentDirectory + "\\ProtectSheetTemplate.xls";

workbook.Open(templateFileName);

Worksheet worksheet = workbook.Worksheets[0];

foreach (AllowEditRange range in worksheet.AllowEditRanges)

{

Console.WriteLine(range.Name);

Console.WriteLine(range.Password);

Console.WriteLine(range.CellArea.StartRow);

Console.WriteLine(range.CellArea.StartColumn);

Console.WriteLine(range.CellArea.EndRow);

Console.WriteLine(range.CellArea.EndColumn);

}

// Add range

int idx = worksheet.AllowEditRanges.Add("MyEditRange", 1, 1, 3, 3);

AllowEditRange myEditRange = worksheet.AllowEditRanges[idx];

myEditRange.Password = "edit";

foreach (AllowEditRange range in worksheet.AllowEditRanges)

{

Console.WriteLine(range.Name);

Console.WriteLine(range.Password);

Console.WriteLine(range.CellArea.StartRow);

Console.WriteLine(range.CellArea.StartColumn);

Console.WriteLine(range.CellArea.EndRow);

Console.WriteLine(range.CellArea.EndColumn);

}

worksheet.Protection.Password = "sheet";

worksheet.Protection.IsSelectingLockedCellsAllowed = true;

worksheet.Protection.IsSelectingUnlockedCellsAllowed = true;

worksheet.Protection.IsDeletingColumnsAllowed = false;

worksheet.Protection.IsDeletingRowsAllowed = false;

worksheet.Protection.IsEditingContentsAllowed = false;

worksheet.Protection.IsEditingObjectsAllowed = true;

worksheet.Protection.IsEditingScenariosAllowed = true;

worksheet.Protection.IsFormattingCellsAllowed = true;

worksheet.Protection.IsFormattingColumnsAllowed = true;

worksheet.Protection.IsFormattingRowsAllowed = true;

worksheet.Protection.IsInsertingColumnsAllowed = false;

worksheet.Protection.IsInsertingHyperlinksAllowed = false;

worksheet.Protection.IsInsertingRowsAllowed = false;

worksheet.Protection.IsFilteringAllowed = true;

worksheet.Protection.IsSortingAllowed = true;

worksheet.Protection.IsUsingPivotTablesAllowed = false;

workbook.Save(Environment.CurrentDirectory + "\\ProtectSheet.xls", FileFormatType.Excel97To2003);

It does not enter in the first foreach loop.

The output file does not contain anymore "Edit Ranges" (in Excel Application)

Thanks

14

Hi,

Thanks for sharing your template file and code.

We have found the issue and will figure it out soon. We confirmed the feature works for Excel 2007 formats but not with Excel 2003 formats.

We have logged your issue into our issue tracking system with an issue id: CELLSNET-15233.

Thank you.

15

Hi,

We need this feature to put our application into production.

How long do you think will it take to deliver this feature ?

Reagrds.

16

Hi,

We try to provide you an eta for the feature.

We will get back to you soon.

Thank you.

17

Hi,

Hopefully we can provide you the supported version in 2-3 days.

Thank you.

18

Hi,

Please try the attached version.

<span style=“font-size: 11pt; font-family: “Arial”,“sans-serif”;”>We
have supported to R/W allow edit ranges.


Kindly let us know if it works fine now.

Thank you.