We set some cells as locked, and want to set formattings to the unlocked cells.
Here attached the code snippet:
// Styles for locked cells
styleIndex = excel.Styles.Add();
style = excel.Styles[styleIndex];
style.IsLocked = true;
style.Font.Color = System.Drawing.Color.White;
style.Name = “HiddenLocked”;
…
…
sheet.Protection.IsFormattingCellsAllowed = true;
sheet.Protection.IsFormattingColumnsAllowed = true;
sheet.Protection.IsFormattingRowsAllowed = true;
sheet.Protect(ProtectionType.All);
How to only apply Formattings on unlocked cells , except locked cells?
Hi,
Thanks for your inquiry.
Well, I have written a sample code for your need. I used a template file (attached), locked only three cells in it i.e…, A1,B1 and C1 in the first worksheet, I also apply some formattings to an unlocked cell i.e…, A5 for test.
Sample code:
// Create a new workbook.
Workbook wb = new Workbook();
//Open a template file.
wb.Open(“f:\test\ProtestBook2.xls”);
// Create a worksheet object and obtain the first sheet.
Worksheet sheet = wb.Worksheets[0];
// Define the style object.
Style style;
//Define the styleflag object
StyleFlag styleflag;
// Loop through all the columns in the worksheet and unlock them.
for (int i = 0; i <= 255; i++)
{
style = sheet.Cells.Columns[(byte)i].Style;
style.IsLocked = false;
styleflag = new StyleFlag();
styleflag.Locked = true;
sheet.Cells.Columns[(byte)i].ApplyStyle(style, styleflag);
}
// Lock only three cells…i.e. A1, B1, C1.
Style style1 = sheet.Cells[“A1”].Style;
style1.IsLocked = true;
style1.Font.Color = System.Drawing.Color.White;
style1.Name = “HiddenLocked1”;
sheet.Cells[“A1”].Style = style1;
Style style2 = sheet.Cells[“B1”].Style;
style2.IsLocked = true;
style2.Font.Color = System.Drawing.Color.White;
style2.Name = “HiddenLocked2”;
sheet.Cells[“B1”].Style = style2;
Style style3 = sheet.Cells[“C1”].Style;
style3.IsLocked = true;
style3.Font.Color = System.Drawing.Color.White;
style3.Name = “HiddenLocked3”;
sheet.Cells[“C1”].Style = style3;
//Apply Style to some unlocked cell i.e., A5.
Style nstyle = sheet.Cells[“A5”].Style;
nstyle.Font.Color = System.Drawing.Color.Red;
nstyle.ForegroundColor = Color.Yellow;
nstyle.Pattern = BackgroundType.Solid;
sheet.Cells[“A5”].Style = nstyle;
// Finally, Protect the sheet now.
sheet.Protect(ProtectionType.Contents);
// Save the excel file.
wb.Save(“f:\test\outProtestBook2_t.xls”);
Please do let us know if it gives you some insight for your need.
For further reference please check the doc topics:
Protecting Worksheets
Advanced Protection Settings since Excel XP
Thank you.
Save Edit
Thanks a lot.
I think I haven’t describe my requirement clearly.
In C# code, we set some cells locked, some cells unlocked.
After exported to a excel file, users can only set format to the unlocked cells.
Please see the attachment, that is format options user want to set.
but we don’t allow user to set format on the locked cells.
Hi,
How could you do this in MS Excel, could you provide us the template file in which you have manually implemented your requirement, we will check it how to implement it using the APIs. Well, if you allow Cells, Rows and Columns Formattings options while protecting a sheet, this would be implemented to the whole worksheet cells including locked and locked cells, so, one can format cells/rows/columns in the whole worksheet (for locked and unlocked cells).
Following is my enhanced sample code if it fills your requirement a bit.
Sample code:
// Create a new workbook.
Workbook wb = new Workbook();
//Open a template file.
wb.Open("f:\\test\\ProtestBook2.xls");
// Create a worksheet object and obtain the first sheet.
Worksheet sheet = wb.Worksheets[0];
// Define the style object.
Style style;
//Define the styleflag object
StyleFlag styleflag;
// Loop through all the columns in the worksheet and unlock them.
for (int i = 0; i <= 255; i++)
{
style = sheet.Cells.Columns[(byte)i].Style;
style.IsLocked = false;
styleflag = new StyleFlag();
styleflag.Locked = true;
sheet.Cells.Columns[(byte)i].ApplyStyle(style, styleflag);
}
// Lock only three cells...i.e. A1, B1, C1.
Style style1 = sheet.Cells["A1"].Style;
style1.IsLocked = true;
style1.Font.Color = System.Drawing.Color.White;
style1.Name = "HiddenLocked1";
sheet.Cells["A1"].Style = style1;
Style style2 = sheet.Cells["B1"].Style;
style2.IsLocked = true;
style2.Font.Color = System.Drawing.Color.White;
style2.Name = "HiddenLocked2";
sheet.Cells["B1"].Style = style2;
Style style3 = sheet.Cells["C1"].Style;
style3.IsLocked = true;
style3.Font.Color = System.Drawing.Color.White;
style3.Name = "HiddenLocked3";
sheet.Cells["C1"].Style = style3;
// Finally, Protect the sheet now.
sheet.Protect(ProtectionType.Contents);
sheet.Protection.IsFormattingCellsAllowed = true;
sheet.Protection.IsFormattingColumnsAllowed = true;
sheet.Protection.IsFormattingRowsAllowed = true;
sheet.Protection.IsSelectingLockedCellsAllowed = true;
sheet.Protection.IsSelectingUnlockedCellsAllowed = true;
// Save the excel file.
wb.Save("f:\\test\\ProtestBook2_t.xls");
Thank you.
The whole work flow is, in our application, user can export database data into excel, then after user do some update and format on the excel file, then by our application, import the excel data into database.
so in the locked cells, we save the key ID information, if user can set format on the locked cells, such as ID is 1234, after format as Date type, it will be changed into “5/18/1903”, it is horrible, the whole excel data cannot import into database anymore.
It’s true that one can format cells/rows/columns in the whole worksheet (for locked and unlocked cells), then we can not solve this problem by locking cells.
Any suggestions are appreciated.
Hi,
Well, I think you can disallow the selection on the locked cells for the users. In this way the users cannot select the locked cells and apply formattings on them.
See the following codes and create your own for your need :):
// Create a new workbook.
Workbook wb = new Workbook();
//Open a template file.
wb.Open("f:\\test\\ProtestBook2.xls");
// Create a worksheet object and obtain the first sheet.
Worksheet sheet = wb.Worksheets[0];
// Define the style object.
Style style;
//Define the styleflag object
StyleFlag styleflag;
// Loop through all the columns in the worksheet and unlock them.
for (int i = 0; i <= 255; i++)
{
style = sheet.Cells.Columns[(byte)i].Style;
style.IsLocked = false;
styleflag = new StyleFlag();
styleflag.Locked = true;
sheet.Cells.Columns[(byte)i].ApplyStyle(style, styleflag);
}
// Lock only three cells...i.e. A1, B1, C1.
Style style1 = sheet.Cells["A1"].Style;
style1.IsLocked = true;
style1.Font.Color = System.Drawing.Color.White;
style1.Name = "HiddenLocked1";
sheet.Cells["A1"].Style = style1;
Style style2 = sheet.Cells["B1"].Style;
style2.IsLocked = true;
style2.Font.Color = System.Drawing.Color.White;
style2.Name = "HiddenLocked2";
sheet.Cells["B1"].Style = style2;
Style style3 = sheet.Cells["C1"].Style;
style3.IsLocked = true;
style3.Font.Color = System.Drawing.Color.White;
style3.Name = "HiddenLocked3";
sheet.Cells["C1"].Style = style3;
sheet.Protection.IsFormattingCellsAllowed = true;
sheet.Protection.IsFormattingColumnsAllowed = true;
sheet.Protection.IsFormattingRowsAllowed = true;
sheet.Protection.IsSelectingLockedCellsAllowed = false;
sheet.Protection.IsSelectingUnlockedCellsAllowed = true;
// Finally, Protect the sheet now.
sheet.Protect(ProtectionType.All);
// Save the excel file.
wb.Save("f:\\test\\nProtestBook2.xls");
Thank you.
Sahi,
Thanks a lot !
We tried this solution, but user still can set format for a region of cells, include locked cells and unlocked cells.
I think it is a natural feature for Excel, so we can’t have it both ways.
Hi,
No, I cannot select the locked cells to format them any longer after executing my code in my previous post. Please try to consult and implement my code for your scenario/need. I have run my code and attached is the output file (generated). Please open the attached (output) file into Ms Excel and you will see that you cannot select the locked cells (A1,B1,C1), so, consequently, you cannot apply formattings to those locked cells any more.
Do you have different results, could you simply run my code using a simple template file and post the generated file here, we will check it soon.
Thank you.
Hi Sahi,
I’m Apple, thanks for your patience.
I changed some codes in blue below, in the two attachments, one is the excel file generated by these codes, the other is the steps to set format to unlocked cells.
// Create a new workbook.
Workbook wb = new Workbook();
//Open a template file.
wb.Open(“f:\test\ProtestBook2.xls”);
// Create a worksheet object and obtain the first sheet.
Worksheet sheet = wb.Worksheets[0];
// Define the style object.
Style style;
//Define the styleflag object
StyleFlag styleflag;
// Loop through all the columns in the worksheet and unlock them.
for (int i = 0; i <= 255; i++)
{
style = sheet.Cells.Columns[(byte)i].Style;
style.IsLocked = false;
styleflag = new StyleFlag();
styleflag.Locked = true;
sheet.Cells.Columns[(byte)i].ApplyStyle(style, styleflag);
}
// Lock only three cells…i.e. A3, B5, C7.
Style style1 = sheet.Cells[“A3”].Style;
style1.IsLocked = true;
style1.Font.Color = System.Drawing.Color.Red;
style1.Name = “HiddenLocked1”;
sheet.Cells[“A3”].PutValue(123456);
sheet.Cells[“A3”].Style = style1;
Style style2 = sheet.Cells[“B5”].Style;
style2.IsLocked = true;
style2.Font.Color = System.Drawing.Color.Red;
style2.Name = “HiddenLocked2”;
sheet.Cells[“B5”].Style = style2;
sheet.Cells[“B5”].PutValue(23456);
Style style3 = sheet.Cells[“C7”].Style;
style3.IsLocked = true;
style3.Font.Color = System.Drawing.Color.Red;
style3.Name = “HiddenLocked3”;
sheet.Cells[“C7”].Style = style3;
sheet.Cells[“C7”].PutValue(34567);
sheet.Protection.IsFormattingCellsAllowed = true;
sheet.Protection.IsFormattingColumnsAllowed = true;
sheet.Protection.IsFormattingRowsAllowed = true;
sheet.Protection.IsSelectingLockedCellsAllowed = false;
sheet.Protection.IsSelectingUnlockedCellsAllowed = true;
// Finally, Protect the sheet now.
sheet.Protect(ProtectionType.All);
// Save the excel file.
wb.Save(“f:\test\nProtestBook2.xls”);
Hi,
Yes, you are right. MS Excel allows this feasibility and we cannot go beyond it. Although we cannot select the locked cells individually but MS Excel provides an option to select the rectangular area (range of cells) to apply formattings and consequently the formattings are applied to the locked cells as well.
Thank you.