Protect cell contents and formatting for the exported excel

Posted by: eager2knw on 8 September 2017, 1:34 pm EST

  • Posted 8 September 2017, 1:34 pm EST

    Hi,


    How can I prevent the user from making any  cell content changes to exported excel.


    I tried using <FONT size=2>


    fpSpread1.ActiveSheet.Protect = </FONT><FONT color=#0000ff size=2>true</FONT><FONT size=2>;


    fpSpread1.SaveExcel(FileName,


    FarPoint.Excel.</FONT><FONT color=#2b91af size=2>ExcelSaveFlags</FONT><FONT size=2>.SaveAsViewed);

    </FONT><FONT size=2>

    </FONT><FONT color=#2b91af size=2>MessageBox</FONT><FONT size=2>.Show(</FONT><FONT color=#a31515 size=2>"Report Saved Successfully"</FONT><FONT size=2>);</FONT>


    <FONT size=2>But even this wont work. The user is able to edit the contents in the exported excel sheet.</FONT>


    <FONT size=2>Thanks,</FONT>


    <FONT size=2>Eager2Knw

    </FONT>
  • Replied 8 September 2017, 1:34 pm EST

    Hi,


    Actually Im cloning a spread and the cloned spread Im exporting to excel. 


    I have noticed that the Cloned spread has certain cells which are not locked at the cell level , row level , and column level.


    So the locking the spread at the sheetlevel doesnot help<img src="/emoticons/emotion-1.gif" alt="Smile" />


    I will have to unlock those cells and then try. Does it make sense.


    Thanks,


    Eager2knw

  • Replied 8 September 2017, 1:34 pm EST

    Setting the Protect property only affects certain aspects of the exported sheet.  You will need to use the Locked property of the cells.
  • Replied 8 September 2017, 1:34 pm EST

    Hi, Im still not able to completely prevent the user from editing the cells in the expoerted excel sheet.


    My Code:

    <FONT size=2>

    </FONT><FONT color=#008000 size=2>//Protect the spread

    </FONT><FONT size=2>

    fpSpread1.ActiveSheet.Protect = </FONT><FONT color=#0000ff size=2>true</FONT><FONT size=2>;


    </FONT><FONT color=#008000 size=2>//Lock the whole spread

    </FONT><FONT size=2>

    fpSpread1.ActiveSheet.DefaultStyle.Locked = </FONT><FONT color=#0000ff size=2>true</FONT><FONT size=2>;


    fpTosReportWithCalculations.SaveExcel(FileName,


    FarPoint.Excel.</FONT><FONT color=#2b91af size=2>ExcelSaveFlags</FONT><FONT size=2>.SaveAsViewed);</FONT>


    <FONT size=2></FONT> 


    <FONT size=2>What can I be doing wrong ?</FONT>


    <FONT size=2>Pls help</FONT>


    <FONT size=2>Thanks , </FONT>


    <FONT size=2>Eager2knw

    </FONT>
  • Replied 8 September 2017, 1:34 pm EST

    This code is working perfectly fine for me.
  • Replied 8 September 2017, 1:34 pm EST

    Yes it does.
  • Replied 8 September 2017, 1:34 pm EST

    But I have a problem here. Unlocking the cells in the cloned spread changes the color of those cells to a dark gray<img src="/emoticons/emotion-6.gif" alt="Sad" />


    I tried setting the <FONT size=2>

    </FONT><FONT color=#008000 size=2><FONT size=2>

    fpSpread1.ActiveSheet.LockBackColor = </FONT><FONT color=#2b91af size=2>Color</FONT><FONT size=2>.Transparent;</FONT>


    <FONT color=#000000>But then, Im not able to see the new backcolor set to these cells. </FONT>

    <FONT size=2><FONT size=2>

    fpSpread1.ActiveSheet.Cells [</FONT><FONT color=#2b91af size=2>rowIndex</FONT><FONT size=2>, colIndex].BackColor = </FONT><FONT color=#2b91af size=2>Color</FONT><FONT size=2>.OldLace;</FONT>


    Pl help


    <FONT color=#000000>Thanks,</FONT>


    <FONT color=#000000>Eager2knw</FONT>


    <FONT size=2> 

    </FONT></FONT></FONT>
  • Replied 8 September 2017, 1:34 pm EST

    Unlocking the cells should not turn their color to dark gray unless you have set the backcolor somewhere.
  • Replied 8 September 2017, 1:34 pm EST

    Hi,


    I found out the cause for the flaw. All along , I had initially set the LockbackColor as LightSteelBlue . I had set the Sheet's Lock property to true , just before exporting to excel.So whenever , I exported to excel, the sheet would inherit the lockbackcolor. Once, I removed the LockBackColor . There was no problem in locking the Sheet, anymore<img src="/emoticons/emotion-1.gif" alt="Smile" />


    Thanks,


    Eager2knw 


     

Need extra support?

Upgrade your support plan and get personal unlimited phone support with our customer engagement team

Learn More

Forum Channels