how to lock excle file cells in C#

Naeem Khan

Hello friends,

right now i m facing a very big problem.. actually i have export data into excel from data grid view.. its done perfectly .. but i want to lock cells like first column and second column of Excel file.. and also validate locked cells.. only enter numeric value or decimal and it can not be greater then max values.. so hope u understand what i want.. if you have any solution regarding for this issues the reply me ASAP..

thanks in Advance

15 Replies

Know the answer? Post it — somebody with the same question will find it here.

Sunny Sharma13y ago
Accepted answer
Hi Naeem,

You just need to set the EntireColumn as Locked.

Here is a sample code for demo:
------------------------------------------------------

Missing mv = Missing.Value;
Microsoft.Office.Interop.Excel.Application excelApp = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbook wkb = excelApp.Workbooks.Add(mv);
Microsoft.Office.Interop.Excel.Worksheet wks = (Microsoft.Office.Interop.Excel.Worksheet)wkb.Sheets[1];

wks.Columns.Locked = false;
((Microsoft.Office.Interop.Excel.Range)wks.get_Range((object)wks.Cells[1, 1], (object)wks.Cells[1, 3])).EntireColumn.Locked = true;
wks.EnableSelection = Microsoft.Office.Interop.Excel.XlEnableSelection.xlUnlockedCells;
wks.Protect(mv, mv, mv, mv, mv, mv, mv, mv, mv, mv, mv, mv, mv, mv, mv, mv);

wks.SaveAs("D:\\myFile.xls", Microsoft.Office.Interop.Excel.XlFileFormat.xlExcel8, mv, mv, mv, mv, mv, mv, mv, mv);
wks = null;
wkb = null;
excelApp.Quit();
-----------------------------------------------------

Cheers !!
+1
Tapan Parida8y ago
Hi Sunny,

i read your post but i had some dout. I have data with in datagridview . I want to export it with password protected some cells value.
0
santhosh PB11y ago
Hi Naeem please send me a code that, How u export data into excel from datagrid view and later how u freeze the excel file columns??
0
Sunny Sharma13y ago
Hi Naeem,

I'm really glad I could be of help, thanks for the compliment :)

Like I already have mentioned on that but to elaborate on that I'm gonna explain it in detail. I think you're not clear on this point.

Of course you can do produce the grades in C# itself using switch... case statement and the output would be same but, that would not dynamically change. It means, after you generate the file and somebody modifies the marks, then the Grade is supposed to be automatically calculated and reflect the appropriate Grade. It would not happen if you just write the grades from within C#. Excel will not know it has to do something if marks change. When you put a Formula in a cell, it gets calculated automatically on every change in sheet. All the formulas in a worksheet gets re-calculated by default to reflect the change made.

This is why i suggested you to keep the criteria in the sheet and use a VLOOKUP, so that the grade changes automatically if marks is changes.

Yes, I know, you might have a thought of hiding the criteria/logic behind the Grades. If that is the case, you can set the ForeGround and BackGround Color of those cells as White or any same color, Lock it up so nobody can see it or check it.

Hope I answered your question.

Happy Learning :)
0
Naeem Khan13y ago
thank you so much sunny... thanks a lot dear.. i really appreciate your knowledge.... one thing you tell me .. can we do a same thing with programmatic in C#.. if its done then would be help full for me.. once again thank you so much..
0
Sunny Sharma13y ago
Hi Naeem,

You can do it in either way.

First is to do it using switch case from within C#, but this would not change the Grades dynamically, they will be static. It means, changing the values in sheet wouldn't change the grade.

Second one is to place a criteria on worksheet itself and let Lookup formula do this work.

See the modified sheet on how to lookup the data using VLOOKUP in excel for a range.
Steps would be as follows:

1. Add the criteria to the sheet.
2. Write data in cells.
3. Put Formulas to first cell.
4. Drag Down the formula to the end of row.

That's it :)
0
Naeem Khan13y ago
thanks for reply ,
i m sending you excel file please download it..we have locked 2 columns now what i want that column no 3 would be accept marks from 10 . if user puts marks in column no 3 such as 5 then grade should be come autometicaly .. now where would be define it programically.. if its possible then try to come online on Gmail.. my id is [email protected]

thank u so much for helping me..
0
Sunny Sharma13y ago
I'm glad I could Help :)

For the current scenario, I don't see any point in calculating it using C# if It is handled by Excel Automatically. You can just set the formula in other cells and they will get calculated automatically. No need to perform validation using C# at all.

If you're still not clear on this point, show me what is your plan for doing this in C# and I'll help you with that.
0
Naeem Khan13y ago
thank you so much sunny.. its working.. one more thing that how would i validate for numreric or decimal values in columns and how would i retrive value based on number such as if i select 5.5 marks then grad will come autometicly in excle file.. is it possible to validate using c#
0
Naeem Khan13y ago
hi sunny, its only working with cells . i want to lock columns not only cells... if u have any code or demo for this thing then plz give to me.. it will help full for me...
0
Prashant Srivastava13y ago
Hi Naeem,

You can do this by setting the excel worksheet to protected via a password.
It will help in such that --

If you will normally set it to protected then that can be changed by changing the Protected setting mentioned under review TAB of Excel sheet.
But if you will protect it with password no body will be able to unprotect it (i.e. probability of making modification to excel will be less).

Regards,
Prashant Srivastava
0
Sunny Sharma13y ago
OK.

So here's how it's done.

By Default every cell is locked, so you can do it in two ways:

1. Either you choose to Lock only the cells that you do not want to get Edited, OR
2. you choose to Unlock only the cells that you want to get edited, all other cells will remain locked.

try this:

Here, I'm going with the first approach:
--------------------------------------------------------
...
xlsWorksheet.Cells.Locked = false; // this will unlock all the cells
xlsWorksheet.get_Range("A2", "B2").Locked = true; //select a range and set locked=true.
xlsWorksheet.Protect(missing, missing, missing, missing, missing, missing, missing,
missing, missing, missing, missing, missing, missing, missing, missing, missing); //activate the protection and that's it.
...
--------------------------------------------------------

Happy Coding :)
0
Naeem Khan13y ago
thank u for reply sunny.. yes i want to lock cell that nobody can modify or delete..
0
Veena Sarda13y ago
did you try sheet.Range["A5"].Style.Locked = true; Replace "A5" with whatever is your range. As for validation of locked cells - once the cells are locked - user cannot edit it. So whatever validation has to happen before the sheet is populated.
0
Sunny Sharma13y ago
Hi Naeem,

what do you mean by locking? Do you want to Freeze a column or you want it to lock so nobody can modify the values?
0