Forums Home / Spread / Spread for WinForms
Posted by: d2.autoservicio on 6 February 2025, 3:00 pm EST
d2.autoservicio
Posted 6 February 2025, 3:00 pm EST
Hi, I was used to setting Locked property to true of cells and blocking user input, but now, when locking a cell, I need to protect the sheet as well. I don’t want column headers to be locked and grayed.
How can I achieve the locking cell only method.
I am using latest Spread.Net version (18.0.20241.0) and programming in Visual Basic.net
aastha.gupta
Posted 7 February 2025, 1:41 am EST
Hi,
You can enable the row/column formatting while a sheet is protect using the following code snippet:
FpSpread1.AsWorkbook().ActiveSheet.Protect(WorksheetLocks.Default And Not (WorksheetLocks.FormatColumns Or WorksheetLocks.FormatRows))To remove the locked cell’s formatting from the header cells, you can customer the header cell type as shown in the code below:
Public Class UserHeaderCellType
Inherits GeneralCellType
Public Overrides Sub PaintCell(g As PdfGraphics, r As RectangleF, appearance As FarPoint.Win.Spread.Appearance, value As Object, isSelected As Boolean, isLocked As Boolean, zoomFactor As Single)
MyBase.PaintCell(g, r, appearance, value, isSelected, False, zoomFactor)
End Sub
End ClassKindly refer to the attached sample for full implementation. See LockedDemo.zip.
Thanks & regards,
Aastha
d2.autoservicio
Posted 7 February 2025, 2:59 pm EST
Hi, I am able to replicate “unlocked” behaviour on Column Headers, I can live with this.
Now, the issue is different.
I have a method to change the backcolor of a row based on cell click and current activerowindex, I paint this in lightblue.
This method is no longer working on locked cells, even if I allow cells and rows to be edited.
aastha.gupta
Posted 10 February 2025, 11:50 pm EST
Hi,
Unfortunately, we could not replicate the issue on our end.
We have attached the updated sample for your reference. See LockedDemo_Mod.zip.
We kindly request you modify the attached sample based on your use-case, so that we can replicate the issue on our end. We also suggest you create a separate ticket if your requirement differs from the initial issue.
Thanks & regards,
Aastha
d2.autoservicio
Posted 27 February 2025, 2:34 pm EST
I have been able to achieve the behaviour I wanted.
For cells, I am using LockForeColor property, now the issue has become when using conditional formatting.
The colors are not showing, as the forecolor is the one changed in the rule, not the lockforecolor.
How can I manage to achieve the conditional formatting on locked cells?
Private Sub Condicionales(ByVal FpSpread As FpSpread, Optional ByVal Col As String = "")
If Col <> "" Then
Dim Cols() As String
Cols = Col.Split(",")
FpSpread.ActiveSheet.ClearConditionalFormatings()
For ColI As Integer = 0 To UBound(Cols)
Dim RuleBarato As New UnaryComparisonConditionalFormattingRule(UnaryComparisonOperator.LessThan, 0, False)
Dim RuleIgual As New UnaryComparisonConditionalFormattingRule(UnaryComparisonOperator.EqualTo, 0.00001, False)
Dim RuleCaro As New UnaryComparisonConditionalFormattingRule(UnaryComparisonOperator.GreaterThan, 0.00001, False)
RuleBarato.ForeColor = Color.Red
RuleIgual.ForeColor = Color.Black
RuleCaro.ForeColor = Color.Red
FpSpread.ActiveSheet.SetConditionalFormatting(0, CInt(Cols(ColI)), FpSpread.ActiveSheet.RowCount, 1, RuleBarato)
FpSpread.ActiveSheet.SetConditionalFormatting(0, CInt(Cols(ColI)), FpSpread.ActiveSheet.RowCount, 1, RuleIgual)
FpSpread.ActiveSheet.SetConditionalFormatting(0, CInt(Cols(ColI)), FpSpread.ActiveSheet.RowCount, 1, RuleCaro)
Next
End If
End Subaastha.gupta
Posted 2 March 2025, 9:22 pm EST
Hi,
Thanks for providing the code snippet.
Unfortunately, we could not replicate the issue on our end. Kindly refer LockedDemo_Mod2.zip to see our implementation.
We request you update the attached sample based on your use-case, so that we can replicate the issue on our end. We also suggest you open a new support ticket if your requirement differs from the initial issue.
Thanks & regards,
Aastha
d2.autoservicio
Posted 3 March 2025, 4:09 pm EST
I have identified the issue.
I have a class to configure the formatting of all Spread controls I use.
For some reason, I need to set the Font Color and Back color in case I remove all rows. If I don’t, all cells and rows will use the previous color.
Public Shared Sub ConfiguraSpread(FpSpread As FpSpread, Optional FpFont As System.Drawing.Font = Nothing, Optional RowHeight As Decimal = 20, Optional HeaderBackColor As Color = Nothing, Optional HeaderBorderColor As Color = Nothing, Optional HeaderForeColor As Color = Nothing, Optional RowColumnBorderColor As Color = Nothing)
If FpFont Is Nothing Then FpFont = New System.Drawing.Font("Arial", 8.2, FontStyle.Regular)
If HeaderBackColor = Nothing Then HeaderBackColor = Color.FromArgb(230, 230, 230)
If HeaderForeColor = Nothing Then HeaderForeColor = System.Drawing.SystemColors.Highlight
If HeaderBorderColor = Nothing Then HeaderBorderColor = Color.LightGray
If RowColumnBorderColor = Nothing Then RowColumnBorderColor = Color.LightGray
If FpSpread.ActiveSheet.ColumnCount > 0 Then
FpSpread.ActiveSheet.Columns(0, FpSpread.ActiveSheet.Columns.Count - 1).Font = FpFont
FpSpread.ActiveSheet.Columns(0, FpSpread.ActiveSheet.Columns.Count - 1).BackColor = Color.White
FpSpread.ActiveSheet.Columns(0, FpSpread.ActiveSheet.Columns.Count - 1).LockBackColor = Color.White
FpSpread.ActiveSheet.Columns(0, FpSpread.ActiveSheet.Columns.Count - 1).ForeColor = Color.Black
FpSpread.ActiveSheet.Columns(0, FpSpread.ActiveSheet.Columns.Count - 1).LockForeColor = Color.Black
For Each col As FarPoint.Win.Spread.Column In FpSpread.ActiveSheet.ColumnHeader.Columns
For Each RO As FarPoint.Win.Spread.Row In FpSpread.ActiveSheet.ColumnHeader.Rows
Dim cell As FarPoint.Win.Spread.Cell = FpSpread.ActiveSheet.ColumnHeader.Cells(RO.Index, col.Index)
cell.Locked = False
' cell.Renderer = New GeneralCellType
cell.LockForeColor = HeaderForeColor
cell.LockBackColor = HeaderBackColor
cell.Border = New ComplexBorder(New ComplexBorderSide(ComplexBorderSideStyle.ThinLine, HeaderBorderColor))
Next
Next
For Each col As FarPoint.Win.Spread.Column In FpSpread.ActiveSheet.Columns
Dim cell As FarPoint.Win.Spread.Column = FpSpread.ActiveSheet.Columns(col.Index)
cell.Border = New ComplexBorder(New ComplexBorderSide(ComplexBorderSideStyle.ThinLine, RowColumnBorderColor))
Next
End If
If FpSpread.ActiveSheet.RowCount > 0 Then
FpSpread.ActiveSheet.Rows(0, FpSpread.ActiveSheet.Rows.Count - 1).Height = RowHeight '20
End If
FpSpread.ActiveSheet.ReferenceStyle = FarPoint.Win.Spread.Model.ReferenceStyle.R1C1
FpSpread.ActiveSheet.OperationMode = FarPoint.Win.Spread.OperationMode.Normal
FpSpread.ActiveSheet.SelectionUnit = SelectionUnit.Cell
FpSpread.ActiveSheet.SelectionPolicy = SelectionPolicy.MultiRange
FpSpread.TextTipDelay = 250
FpSpread.TextTipPolicy = FarPoint.Win.Spread.TextTipPolicy.Floating
FpSpread.ResizeZeroIndicator = FarPoint.Win.Spread.ResizeZeroIndicator.Default
FpSpread.ActiveSheet.Protect = True
FpSpread.AsWorkbook().ActiveSheet.Protect(GrapeCity.Spreadsheet.WorksheetLocks.Default And Not (GrapeCity.Spreadsheet.WorksheetLocks.FormatColumns Or GrapeCity.Spreadsheet.WorksheetLocks.FormatRows))
End SubSpecifically, the setting of LockForeColor, will always keep it Black, even when conditional format exists.
How can I address this issue?
aastha.gupta
Posted 4 March 2025, 7:00 am EST
Hi,
We could replicate the issue on our end. Therefore, we are currently discussing it with our developers. [Internal Tracking ID: SPNET-46535]
We will update you on this as soon as we hear back from them.
Thanks & regards,
Aastha
