Protect cells on a worksheet

Answered (Verified) This post has 1 verified answer | 11 Replies | 2 Followers Thread's RSS feed.

dtnixon
Points 1,923
Replied On: Wed, Mar 11 2009 12:14 AM Reply

I am trying to set the protection for a worksheet so that the cells are not editable, but then allow editing of certain cells. This is to help the user when working with the exported excel file.

I guess this means protection at the cell level? I can't seem to figure it out...

  • Post Points: 20

Verified Answer

Answered (Verified) Replied On: Fri, Mar 20 2009 12:20 PM Reply
Verified by dtnixon

If the sheet is protected and you want to make some cells editable, you can set the cells' CellFormat.Locked properties to False.

  • Post Points: 40

All Replies

Answered (Verified) Replied On: Fri, Mar 20 2009 12:20 PM Reply
Verified by dtnixon

If the sheet is protected and you want to make some cells editable, you can set the cells' CellFormat.Locked properties to False.

  • Post Points: 40
manissg
Points 90
Replied On: Mon, Apr 6 2009 3:11 PM Reply

I am not able to protect the worksheet. Can you please post the snippet.

Thanks,

Mani.

 

  • Post Points: 5
manissg
Points 90
Replied On: Mon, Apr 6 2009 3:15 PM Reply

 

 

 

 

 

 

 

 

 

 

 

 

 

I am trying like following snippet

 

Infragistics.Excel.

Workbook wb = new Infragistics.Excel.Workbook();

Infragistics.Excel.Worksheet sheet = wb.Worksheets.Add("ExpenseAllocation");

sheet.Protected =

true;

 

sheet.DisplayOptions.PanesAreFrozen =

false;

sheet.Rows[0].CellFormat.Font.Bold = Infragistics.Excel.ExcelDefaultableBoolean.True;

sheet.Rows[0].CellFormat.FillPattern = Infragistics.Excel.

FillPatternStyle.Solid;

sheet.Rows[0].CellFormat.FillPatternForegroundColor = System.Drawing.

Color.FromArgb(238, 243,255);

Infragistics.Excel.

WorksheetRow  headersRow = sheet.Rows[0];

headersRow.Cells[0].Value =

"Order ID";

headersRow.Cells[1].Value =

"Product";

headersRow.Cells[2].Value =

"Unit Price";

headersRow.Cells[3].Value =

"Quantity";

headersRow.Cells[4].Value =

"Discount";

headersRow.Cells[5].Value =

"Order Total";

  • Post Points: 20
Replied On: Wed, Apr 8 2009 1:37 PM Reply

I ran the code you have posted and it works for me when I open the workbook in Excel 2007. You can also try making the workbook protected by setting wb.Protected to True.

  • Post Points: 20
manissg
Points 90
Replied On: Wed, Apr 8 2009 1:58 PM Reply

Is it something to do with the version and I am using Infragistics2.Excel.v7.3?

I am not able to protect worksheet.

  • Post Points: 20
Replied On: Wed, Apr 8 2009 2:57 PM Reply

I don't believe the version should matter. The ability to protect worksheets has been in the Excel assembly since it was introduced. 

In what version of Excel are you opening the workbook?

  • Post Points: 20
manissg
Points 90
Replied On: Wed, Apr 8 2009 3:02 PM Reply

I am using Excel 2000 and also I am using memorystream to allow the user to save the excel file.

            MemoryStream ms = new MemoryStream();           
            wb.Save(ms);
            Byte[] byteArr = (Byte[])Array.CreateInstance(typeof(Byte),ms.Length);
            ms.Position  = 0;
            ms.Read(byteArr,0,(int)ms.Length);
            ms.Close();
            Response.Clear();
            Response.ContentType = "application/vnd.ms-excel";
            Response.AddHeader("Content-Disposition", "attachment;filename=DocTypeInfo.xls");
            Response.BinaryWrite(byteArr);          
            Response.End();

  • Post Points: 5
manissg
Points 90
Replied On: Wed, Apr 8 2009 3:11 PM Reply

Following are the snippet I am using:

            Infragistics.Excel.Workbook wb = new Infragistics.Excel.Workbook();
            Infragistics.Excel.Worksheet sheet = wb.Worksheets.Add("ExpenseAllocation");
            sheet.Protected = true;
            wb.Protected = true;

            sheet.DisplayOptions.PanesAreFrozen = false;
            sheet.Rows[0].CellFormat.Font.Bold = Infragistics.Excel.ExcelDefaultableBoolean.True;
            sheet.Rows[0].CellFormat.FillPattern = Infragistics.Excel.FillPatternStyle.Solid;
            sheet.Rows[0].CellFormat.FillPatternForegroundColor = System.Drawing.Color.FromArgb(238, 243, 255);

            Infragistics.Excel.WorksheetRow headersRow = sheet.Rows[0];
            headersRow.Cells[0].Value = "Order ID";
            headersRow.Cells[1].Value = "Product";
            headersRow.Cells[2].Value = "Unit Price";
            headersRow.Cells[3].Value = "Quantity";
            headersRow.Cells[4].Value = "Discount";
            headersRow.Cells[5].Value = "Order Total";

            // The "Unit Price" column should display its values as dollars
            sheet.Columns[2].CellFormat.FormatString = "\"$\"#,##0.00_);(\"$\"#,##0.00)";
            // The "Discount" column should display its values as percentages
            sheet.Columns[4].CellFormat.FormatString = "0.00%";
            // The "Order Total" column should display its values as dollars
            sheet.Columns[5].CellFormat.FormatString = "\"$\"#,##0.00_);(\"$\"#,##0;00)";
            // Allow enough room to display the totals
            sheet.Columns[5].Width = 3000;

            Infragistics.Excel.WorksheetRow currentRow;

            currentRow = sheet.Rows[1];
            currentRow.Cells[0].Value = 10248;
            currentRow.Cells[1].Value = 42;
            currentRow.Cells[2].Value = 9.80;
            currentRow.Cells[3].Value = 10;
            currentRow.Cells[4].Value = 0.075;

            currentRow = sheet.Rows[2];
            currentRow.Cells[0].Value = 10248;
            currentRow.Cells[1].Value = 72;
            currentRow.Cells[2].Value = 34.80;
            currentRow.Cells[3].Value = 5;
            currentRow.Cells[4].Value = 0.075;

            currentRow = sheet.Rows[3];
            currentRow.Cells[0].Value = 10249;
            currentRow.Cells[1].Value = 14;
            currentRow.Cells[2].Value = 18.60;
            currentRow.Cells[3].Value = 9;
            currentRow.Cells[4].Value = 0.1;

            currentRow = sheet.Rows[4];
            currentRow.Cells[0].Value = 10249;
            currentRow.Cells[1].Value = 51;
            currentRow.Cells[2].Value = 42.40;
            currentRow.Cells[3].Value = 40;
            currentRow.Cells[4].Value = 0.1;

            MemoryStream ms = new MemoryStream();
            //wb.Save(@"c:\mani\test.xls");
            wb.Save(ms);
            Byte[] byteArr = (Byte[])Array.CreateInstance(typeof(Byte), ms.Length);
            ms.Position = 0;
            ms.Read(byteArr, 0, (int)ms.Length);
            ms.Close();
            Response.Clear();
            Response.ContentType = "application/vnd.ms-excel";
            Response.AddHeader("Content-Disposition", "attachment;filename=DocTypeInfo.xls");
            Response.BinaryWrite(byteArr);
            Response.End();

  • Post Points: 20
Replied On: Wed, Apr 8 2009 3:52 PM Reply

I tried using your code and the saved workbook file is correctly protected when it is opened in Excel 2000, Excel 2003, and Excel 2007. it's possible I have misunderstood the problem here. When I try to edit the cells with data, a message box appears for me telling me that the data cannot be edited. Are you not seeing this message or is there another problem you are experiencing?

  • Post Points: 20
manissg
Points 90
Replied On: Thu, Apr 9 2009 1:15 PM Reply

Looks like it is something to do with the Infragistics.Excel version. Can you please send me your working copy of your sample. (entrie sample project)?

Thanks.

 

  • Post Points: 20
Replied On: Mon, May 4 2009 5:34 PM Reply

I have attached the sample, but version 7.3 is no longer supported so it is built with 8.1. You can remove that reference though and add the reference to 7.3 and it should work. If it does not work, I would recommend upgrading to the latest service release or a later version.

  • Post Points: 5
Page 1 of 1 (12 items) | RSS