We're sorry Aspose doesn't work properply without JavaScript enabled.

Free Support Forum - aspose.com

Restrict users to add rows and column in Excel worksheet

I am generating an excel file from our .NET application using Aspose Cells and there is a need for some restrictions on the generated excel:

  1. We do not want to let user insert columns or rows as it’s a fixed template

i have tried with fallowing code :
objworksheet.Protection.AllowDeletingRow = false;
objworksheet.Protection.AllowDeletingColumn = false;
objworksheet.Protection.AllowInsertingColumn = false;
objworksheet.Protection.AllowInsertingRow = false;

but when i download excel its allowing to add and delete of rows and columns

note i am using office 365 excel

What are the Aspose cell features we can leverage here for adding these restrictions?

@kiranmurkal,

Thanks for the details.

Does it work fine If you use MS Excel (2010/2013/2015, etc.) to open the generated file into it?

Also, could you paste complete sample code (runnable) and sample file to reproduce the issue, we will check it soon.

I am not fetching excel file from local path ,i am creating file on the fly and saving in local path here is the sample code

Note Meta data is my data as class structure

public byte[] ExportSurvey(Meta data, string AsposeLicensePath, byte[] logoByteArray)
{
byte[] result = null;
int tempsheet = 0;
string fileName = “”;
byte[] buffer = null;
License _license = new License();
_license.SetLicense(AsposeLicensePath);
List<SurveyDAL.ViewModels.Survey.Section> sections = new List<SurveyDAL.ViewModels.Survey.Section>();
MemoryStream ms = new MemoryStream();
if (logoByteArray != null)
{
ms.Write(logoByteArray, 0, logoByteArray.Length);
}
sections = data.metaBody.sections.Where(x => x.type != “Documents”).OrderBy(n => n.order).ToList();
if (sections.Count != 0)
{
Workbook workbook = new Workbook();
Worksheet objtempworksheet = null;
workbook.Worksheets.Add();
workbook.Worksheets[0].Name = “temp”;
objtempworksheet = workbook.Worksheets[0];
for (int i = 0; i < sections.Count; i++)
{
tempsheet = sections.Count;
int row = 1; int column = 1;

                Worksheet objworksheet = null;
                workbook.Worksheets.Add();
                workbook.Worksheets[i + 1].Name = sections[i].name;
                objworksheet = workbook.Worksheets[i + 1];
                objworksheet.Cells.StandardWidth = 25;
                objworksheet.Cells.SetColumnWidth(0, 3);
                objworksheet.Cells.SetColumnWidth(1, 43);
                //objworksheet.Cells.a
                objworksheet.Protection.AllowDeletingRow = false;
                objworksheet.Protection.AllowDeletingColumn = false;
                objworksheet.Protection.AllowInsertingColumn = false;
                objworksheet.Protection.AllowInsertingRow = false;
                //objworksheet.Protect(ProtectionType.Structure);

                objworksheet.IsGridlinesVisible = false;

                objworksheet.Cells.SetRowHeight(0, 47);
                objworksheet.Pictures.Add(0, 1, ms, 190, 143);


                Cell surveynamecell = objworksheet.Cells[0, 2];
                StyleFlag flag = new StyleFlag();
                flag.Font = true;
                flag.HorizontalAlignment = true;
                flag.VerticalAlignment = true;
                flag.FontName = true;
                flag.FontSize = true;
                flag.FontColor = true;
                flag.FontBold = true;
                flag.Locked = true;
                Style headerstyle = workbook.CreateStyle();
                surveynamecell.PutValue(data.surveytitle);
                headerstyle.Font.Color = Color.White;
                headerstyle.Font.Size = 20;
                headerstyle.Pattern = BackgroundType.Solid;
                headerstyle.ForegroundColor = Color.FromArgb(31, 72, 124);
                headerstyle.HorizontalAlignment = TextAlignmentType.Left;
                headerstyle.VerticalAlignment = TextAlignmentType.Center;
                objworksheet.Cells[0, 2].SetStyle(headerstyle);

                Row temp = objworksheet.Cells.Rows[0];
                temp.ApplyStyle(headerstyle, flag);





                objworksheet.Cells[0, 2].PutValue(data.surveytitle);
                objworksheet.Cells.Merge(0, 2, 1, 7);
                if (sections[i].description != "")
                {
                    Style decriptionstyle = workbook.CreateStyle();
                    decriptionstyle.HorizontalAlignment = TextAlignmentType.Justify;
                    decriptionstyle.VerticalAlignment = TextAlignmentType.Justify;
                    objworksheet.Cells[row, column].HtmlString = sections[i].description;
                    string cellvalue = objworksheet.Cells[row, column].StringValue.Trim();
                    objworksheet.Cells[row, column].HtmlString = cellvalue;
                    objworksheet.Cells[row, column].SetStyle(decriptionstyle);
                    objworksheet.Cells.Merge(1, 1, 1, 7);
                    objworksheet.Cells.SetRowHeight(1, 143);
                    row++;
                }
                if (sections[i].questions != null && sections[i].questions.Count > 0)
                {
                    row++;
                    BuildQuestions(sections[i].questions, objworksheet, row, column, objtempworksheet, workbook);
                }
            }

            fileName = System.IO.Path.GetTempPath() + Guid.NewGuid().ToString() + "report.xlsx";
            workbook.Save(fileName, SaveFormat.Xlsx);


            using (Stream stream = new System.IO.FileStream(fileName, FileMode.Open))
            {
                buffer = new byte[stream.Length];
                stream.Read(buffer, 0, buffer.Length);
            }
            File.Delete(fileName);
            Thread.EndCriticalRegion();

        }
        return buffer;
    }

@kiranmurkal,

Thanks for the code segment.

I could not compile your code segment precisely. Please create a sample console application (standalone), zip the project and provide here, we will check it soon.

By the way, can you insert data to the sheet or not in the output file? Could you try to add the following lines to your code segment (see the lines in bold) if it makes any difference:
e.g
Sample code:


objworksheet.Cells.StandardWidth = 25;
objworksheet.Cells.SetColumnWidth(0, 3);
objworksheet.Cells.SetColumnWidth(1, 43);
//…objworksheet.Cells.a
objworksheet.Protection.AllowEditingContent = false;
objworksheet.Protection.AllowEditingObject = false;
objworksheet.Protection.AllowEditingScenario = false;
objworksheet.Protection.AllowDeletingRow = false;
objworksheet.Protection.AllowDeletingColumn = false;
objworksheet.Protection.AllowInsertingColumn = false;
objworksheet.Protection.AllowInsertingRow = false;