none
Locking specific cells by cell address programmatically

    Question

  • Hi,

    we generate Excel successfully, but not able to lock specific cells in the Excel?

    Actually requirement is to lock all the headers and few other total columns.

    Any pointers/samples appreciated.

    Wednesday, April 07, 2010 10:32 AM

All replies

  • Hi venkatMreddy,

    Thanks for your question.

    To solve the problem, I suggest you using the Productivity Tool to see the generated sample code. You may follow these steps:

    1.      Create an empty Excel document and save it, such as “source.xlsx”.

    2.      Lock the rows and columns you need in Excel UI and save it as “target.xlsx”.

    3.      Open the Tool; click "Compare Files" to compare the “source.xlsx” and “target.xlsx” files.

    4.      In the "File Comparison" tab, click "View Package Code" to see how to protect/lock specific cells programmatically.

    You may also see from the "View Part Diff" that the main differences are in /xl/worksheets/sheet1.xml (the sheet where you lock the cells) and /xl/styles.xml. The former defines the cell range and the latter defines the protection style. You could change the row/column index as you want.

    Regarding how to protect cells in Excel UI, you could find it in the Protection tab of the Format Cells by right clicking the cells. You should uncheck the "locked" box and go to "Review->Protect Sheet" to protect the sheet. Then you select the rows/columns and check the "locked" box.

    Hope this helps. If you have any question, please let me know.

    Thanks,

    Lu

    Thursday, April 08, 2010 5:21 AM
  • Thanks a lot buddy..that has helped but there s an error while opening the excel generated.

    I have applied the SheetProtection property to the worksheet after the stylesSheet is set with all the required Styles as below :

    SheetProtection

     

    sheetProtection1 = new SheetProtection() { Sheet = true, Objects = true, Scenarios = true };

    worksheetPart.Worksheet.Append(sheetProtection1);

     

    i am applying the CellFormats as shown below:

    Note: if i dint add Protection as property to CellFormat, its working fine and all the formats are being set perfectly.

    new

     

    CellFormat(

     

    //new Protection(){Locked = false},

     

    new Alignment() { Horizontal = HorizontalAlignmentValues.General, Vertical = VerticalAlignmentValues.Bottom, WrapText = true }

    ) { NumberFormatId = (

    UInt32Value)0U, FontId = (UInt32Value)2U, FillId = (UInt32Value)1U, BorderId = (UInt32Value)1U, ApplyFont = true, ApplyFill = true, ApplyBorder = true, ApplyProtection = true, ApplyAlignment = true },

    (FYI) For eg. a Fill object is like this :

    new

     

    Fill(new PatternFill(new ForegroundColor() { Indexed = (UInt32Value)9U }, new BackgroundColor() { Indexed = (UInt32Value)9U }) { PatternType = PatternValues.Solid }),

    when i use the Protection property, i am getting the following error while opening the excel. Its removing the Styles.xml and trying to retrieve the worksheet devoid of the styles,

    "Replaced Part: /xl/worksheets/sheet1.xml part with XML error.  Load error. Line 1, column 6447."

     

    Any help would be greatly appreciated.

    Thanks.

    Thursday, April 08, 2010 1:05 PM
  • Hi venkatMreddy,

    Thanks for your reply.

    From your error information, we could see that it is an XML error. I guess there may be something against XML in your sheet1.xml file, which is not related to the Protection class. So have you checked the content of the sheet1.xml file at the location error information indicates? Could you describe how you generate the source file that needs to be locked a bit more? This may help better figure out your problem.

    Thanks,

    Lu

    Tuesday, April 13, 2010 7:47 AM
  • We are looking for help in how to create a format style index programmatically with the required format, and then we may attach the styleindex to cells, rows, or column using codes we are already familiar with.

    Tuesday, March 12, 2013 12:29 PM