how to lock excle file cells in C#

Question

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

Sunny Sharma · 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 !!

santhosh PB · Answer

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??

Sunny Sharma · Answer

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 :)

Naeem Khan · Answer

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..

Sunny Sharma · Answer

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 :)

Naeem Khan · Answer

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 khan.naeem1987@gmail.com thank u so much for helping me..

Sunny Sharma · Answer

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.

Naeem Khan · Answer

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#

Naeem Khan · Answer

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...

Prashant Srivastava · Answer

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

Sunny Sharma · Answer

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 :)

Naeem Khan · Answer

thank u for reply sunny.. yes i want to lock cell that nobody can modify or delete..

Veena Sarda · Answer

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.

Sunny Sharma · Answer

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?