Increase decimal places in cells selection

Posted by: lonewolfthe3rd on 8 September 2017, 2:45 pm EST

  • Posted 8 September 2017, 2:45 pm EST

    have a sheet with GeneralCellType cells. Have only numbers in cells.

    Need to increase decimal places in each cell separately in selection.

     Tried with get celltype from cells in selection and change the decimal places, but failed.

    Any idea how to solve it?

     

  • Replied 8 September 2017, 2:45 pm EST

    Hello,

    I would like you to know that the GeneralCellType has no
    property that could allow you to set the decimal places for numbers.
    Since all your values are numbers, you can set the celltype to be
    NumberCellType, which has a "DecimalPlaces" property, that would allow
    you to set different number of decimal places for each cell in the
    selection. I have created a sample depicting the same, which I am
    attaching along with this response. Kindly have a look at the sample.
    Execute the sample, make any selection and click on the "Change decimal
    places" button and it would assign different number of decimal places to
    each cell in the selection. Please let me know if you have any queries
    further. 

    Hope it will help you.

    Thanks,

    Manpreet Kaur.
    2011/09/decimalplacesselection.zip
  • Replied 8 September 2017, 2:45 pm EST

    OK thank you. Checked the code.

    Look on my problem, because I cannot understand:

    Have two buttons. From first have to change in selection cell type from  generic to number in very easy code:

    Dim cr As FarPoint.Win.Spread.Model.CellRange
            Dim Cell_Iter As FarPoint.Win.Spread.Cell
            cr = fp1.ActiveSheet.GetSelection(0)
            Dim cValStr As String
            Dim cVal As String
            Dim NumCellType As New FarPoint.Win.Spread.CellType.NumberCellType
            For i = cr.Row To cr.Row + cr.RowCount - 1
                For j = cr.Column To cr.Column + cr.ColumnCount - 1
                    cValStr = fp1.ActiveSheet.Cells(i, j).Value
                    If IsNumeric(cValStr) = True Then
                        fp1.ActiveSheet.Cells(i, j).CellType = NumCellType
                    End If
                    'fp1.ActiveSheet.Cells(i, j).BackColor = Color.Azure
                Next j
            Next i

     From second button want to increase decimalplaces in the same selection:


    Private Sub tsPlc_MiejscaZerowe_Plus_Click(ByVal sender As System.ObjectByVal e As System.EventArgsHandles tsPlc_MiejscaZerowe_Plus.Click
           Dim cr As FarPoint.Win.Spread.Model.CellRange
            Dim Cell_Iter As FarPoint.Win.Spread.Cell
            cr = fp1.ActiveSheet.GetSelection(0)
          Dim OldDP As Integer
            Dim cVal As String
            Dim NumCellType As New FarPoint.Win.Spread.CellType.NumberCellType
           
    For i = cr.Row To cr.Row + cr.RowCount - 1
                For j = cr.Column To cr.Column + cr.ColumnCount - 1
                    NumCellType = CType(fp1.Sheets(0).GetCellType(i, j), FarPoint.Win.Spread.CellType.NumberCellType)
                    NumCellType.DecimalPlaces += 1
                    fp1.ActiveSheet.Cells(i, j).CellType = NumCellType
                    'fp1.ActiveSheet.Cells(i, j).BackColor = Color.Azure
                Next j
            Next i
        End Sub
     
    Where is the point? why i got the error close to objects type?
    I'm too tired or something wrong? 
  • Replied 8 September 2017, 2:45 pm EST







    pre { font-family: "Liberation Serif"; }p { margin-bottom: 0.21cm; }

    Hello,

    I observed the code provided by you and found that you are creating a single instance of NumberCellType, changing its "DecimalPlaces" property every time and assigning it to each cell. Since you are changing the property in a single instance, hence every cel whose celltype is set to this instance, would reflect the changes, so you are getting the same number of decimal places for each value. If you want each cell to have a different number of decimal places, then you have to create a new instance of NumberCellType for each cell with different number of decimal places. I hve modified your code to do the same, kindly have a look.

    Private Sub tsPlc_MiejscaZerowe_Plus_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles tsPlc_MiejscaZerowe_Plus.Click

    Dim cr As FarPoint.Win.Spread.Model.CellRange

    Dim Cell_Iter As FarPoint.Win.Spread.Cell

    cr = FpSpread1.ActiveSheet.GetSelection(0)

    Dim OldDP As Integer

    Dim cVal As String

    Dim NumCellType As New FarPoint.Win.Spread.CellType.NumberCellType

    For i = cr.Row To cr.Row + cr.RowCount - 1

    For j = cr.Column To cr.Column + cr.ColumnCount - 1

    NumCellType = CType(FpSpread1.Sheets(0).GetCellType(i, j), FarPoint.Win.Spread.CellType.NumberCellType)

    NumCellType.DecimalPlaces += 1

    Dim NumCellType1 As New FarPoint.Win.Spread.CellType.NumberCellType

    NumCellType1.DecimalPlaces = NumCellType.DecimalPlaces

    FpSpread1.ActiveSheet.Cells(i, j).CellType = NumCellType1

    'fp1.ActiveSheet.Cells(i, j).BackColor = Color.Azure

    Next j

    Next i

    End Sub

    Hope it will help you. Please let me know if you have any queries further.



    Thanks,

    Manpreet Kaur
  • Replied 8 September 2017, 2:45 pm EST

    Thank you, a lot!

    First of all - made two mistakes. First was close to instances in my project. In shortly Spread is in child window. I have 5 child's windows, each in differnt function (Data, spreadsheet, Raports etc.). These windowsa are not data conteners. They are 5 "programs/conteners" with isolation memory and data. Why I wrote about? becuse have't got an api for it in Widnows. Made my own and here was first problems - handle of instance was incorrect.

    Second was close to FPSpread. Yours help I had need! Ok change a bit above code and wrote everything in one sub below (maybe someone will need it). If find other cell type then number, check if it's number and change a type to number with decimal places. Maybe somebody has better idea - in my project works fine. Have two buttons: increase/decrease decimal places and thats all.

    fpR - it's handle for direct Raport's instance instance (in "normal" projects fpspread1 instance)
     Private Sub tsPlc_MiejscaZerowe_Plus_Click(ByVal sender As System.ObjectByVal e As System.EventArgsHandles tsPlc_MiejscaZerowe_Plus.Click
            Try
                fpR.SuspendLayout()
                Dim cr As FarPoint.Win.Spread.Model.CellRange
                cr = fpR.Sheets(Tabele).GetSelection(0)
                Dim cValStr As String
                Dim NumCellType As New FarPoint.Win.Spread.CellType.NumberCellType

                For i = cr.Row To cr.Row + cr.RowCount - 1
                    For j = cr.Column To cr.Column + cr.ColumnCount - 1
                        cValStr = fpR.Sheets(Tabele).Cells(i, j).Value
                        If IsNumeric(cValStr) = True Then
                            NumCellType = CType(fpR.Sheets(Tabele).GetCellType(i, j), FarPoint.Win.Spread.CellType.NumberCellType)
                            NumCellType.DecimalPlaces += 1
                            Dim NumCellType1 As New FarPoint.Win.Spread.CellType.NumberCellType
                            NumCellType1.DecimalPlaces = NumCellType.DecimalPlaces
                            fpR.Sheets(Tabele).Cells(i, j).CellType = NumCellType1
                        End If 'If IsNumeric(cValStr) = True Then
                    Next j
                Next i
                fpR.ResumeLayout()

            Catch ex As Exception When Err.Number = 13
                'Log.WriteLn("Wyjatek mes: " & ex.Message & " |err.num: " & Err.Number, Log.TypKomunikatu.Programisty, Log.TypKonsoli.Systemowy)
                Err.Clear()
                fpR.SuspendLayout()
                Dim cr As FarPoint.Win.Spread.Model.CellRange
                cr = fpR.Sheets(Tabele).GetSelection(0)
                Dim cValStr As String
                Dim NumCellType As New FarPoint.Win.Spread.CellType.NumberCellType
                For i = cr.Row To cr.Row + cr.RowCount - 1
                    For j = cr.Column To cr.Column + cr.ColumnCount - 1
                        cValStr = fpR.Sheets(Tabele).Cells(i, j).Value
                        If IsNumeric(cValStr) = True Then
                            NumCellType.DecimalPlaces = 2
                            fpR.Sheets(Tabele).Cells(i, j).CellType = NumCellType
                        End If
                    Next j
                Next i
                fpR.ResumeLayout()
            Finally
                fpR.ResumeLayout()
            End Try
        End Sub
     Once more: thank you Manpreet Kaur

Need extra support?

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

Learn More

Forum Channels