1

I am attempting to write into an excel sheet some data and after writing in the necessary data, I would like to protect the cells against editing by making them non editable. After this operation, some data will be updated on the unprotected cells by the sheet users which I will then use and read from to carry out some processing activity.

  1. Can someone help me out with specifically, how do I lock a column from editing in excel?
  2. How do you write using C# into a protected column?
  3. How do you read using C# from a protected column?

Simple references to examples would help me out greatly. PS: If you are more comfortable with VB.NET even the references of how to do this on that would be helpful.

Shouvik
  • 11,350
  • 16
  • 58
  • 89
  • From what I see you can't do that in plain Excel... so there won't be a way to do it via C# or VB.NET... IMHO the concept of "expecting that data put into an Excel Worksheet will come back unmodified whil other parts are editable by the user" seems risky at best... – Yahia May 13 '13 at 09:10
  • Risky how? I am allowing users to edit some values in the sheet while I protect other values from them editing. I have seen excels where cells which act as the header and tags are non editable while other cells the user can enter values. So I am supposing something on similar lines should work here as well. – Shouvik May 13 '13 at 09:15
  • It is indeed possible to modify an Excel file despite such "protection". ANY data you give out to a user and then comes back (with some data added) should be verified before you process it further (users are of very different types with different skillsets). – Yahia May 13 '13 at 09:21

1 Answers1

1

For Excel cell to be in non-editable mode two things have to be taken care of:

1) The Excel cell should be Locked

ws.get_Range("Location", Type.Missing).Locked = true;

2) The Excel worksheet should also be locked

ws.Protect("SecurityCode", true, true, true,
            Type.Missing, Type.Missing, true, true, Type.Missing,
            Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
            Type.Missing, Type.Missing);
//where ws is the worksheet object

For your Second question to read through an Protected cell, this can be done directly

string CellValue = ws.get_Range("Location", Type.Missing).Value2.ToString();
//Now depending upon the CellValue you can write your own Logic.
George Duckett
  • 31,770
  • 9
  • 95
  • 162
Rohit Agrawal
  • 483
  • 3
  • 12
  • 1
    Yup..If you wish to make the entire column non-editable, just include ws.get_Range("Location", Type.Missing).EntireColumn.Locked = true; – Rohit Agrawal May 13 '13 at 09:14