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:

    <span style="color:blue;">Dim</span> cr <span style="color:blue;">As</span> FarPoint.Win.Spread.Model.<span>CellRange</span>
            <span style="color:blue;">Dim</span> Cell_Iter <span style="color:blue;">As</span> FarPoint.Win.Spread.<span>Cell</span>
            cr = fp1.ActiveSheet.GetSelection(0)
            <span style="color:blue;">Dim</span> cValStr <span style="color:blue;">As</span> <span style="color:blue;">String</span>
            <span style="color:blue;">Dim</span> cVal <span style="color:blue;">As</span> <span style="color:blue;">String</span>
            <span style="color:blue;">Dim</span> NumCellType <span style="color:blue;">As</span> <span style="color:blue;">New</span> FarPoint.Win.Spread.CellType.<span>NumberCellType</span>
    </pre>
            <span style="color:blue;">For</span> i = cr.Row <span style="color:blue;">To</span> cr.Row + cr.RowCount - 1
                <span style="color:blue;">For</span> j = cr.Column <span style="color:blue;">To</span> cr.Column + cr.ColumnCount - 1
                    cValStr = fp1.ActiveSheet.Cells(i, j).Value
                    <span style="color:blue;">If</span> IsNumeric(cValStr) = <span style="color:blue;">True</span> <span style="color:blue;">Then</span>
                        fp1.ActiveSheet.Cells(i, j).CellType = NumCellType
                    <span style="color:blue;">End</span> <span style="color:blue;">If</span>
                    <span style="color:green;">'fp1.ActiveSheet.Cells(i, j).BackColor = Color.Azure</span>
                <span style="color:blue;">Next</span> j
            <span style="color:blue;">Next</span> i
    <p> From second button want to increase decimalplaces in the same selection:


    <span style="color:blue;">Private</span> <span style="color:blue;">Sub</span> tsPlc_MiejscaZerowe_Plus_Click(<span style="color:blue;">ByVal</span> sender <span style="color:blue;">As</span> System.<span>Object</span>, <span style="color:blue;">ByVal</span> e <span style="color:blue;">As</span> System.<span>EventArgs</span>) <span style="color:blue;">Handles</span> tsPlc_MiejscaZerowe_Plus.Click
           <span style="color:blue;">Dim</span> cr <span style="color:blue;">As</span> FarPoint.Win.Spread.Model.<span>CellRange</span>
            <span style="color:blue;">Dim</span> Cell_Iter <span style="color:blue;">As</span> FarPoint.Win.Spread.<span>Cell</span>
            cr = fp1.ActiveSheet.GetSelection(0)
          <span style="color:blue;">Dim</span> OldDP <span style="color:blue;">As</span> <span style="color:blue;">Integer</span>
            <span style="color:blue;">Dim</span> cVal <span style="color:blue;">As</span> <span style="color:blue;">String</span>
            <span style="color:blue;">Dim</span> NumCellType <span style="color:blue;">As</span> <span style="color:blue;">New</span> FarPoint.Win.Spread.CellType.<span>NumberCellType</span>
           
    <span style="color:blue;">For</span> i = cr.Row <span style="color:blue;">To</span> cr.Row + cr.RowCount - 1
                <span style="color:blue;">For</span> j = cr.Column <span style="color:blue;">To</span> cr.Column + cr.ColumnCount - 1
                    NumCellType = <span style="color:blue;">CType</span>(fp1.Sheets(0).GetCellType(i, j), FarPoint.Win.Spread.CellType.<span>NumberCellType</span>)
                    NumCellType.DecimalPlaces += 1
                    fp1.ActiveSheet.Cells(i, j).CellType = NumCellType
                    <span style="color:green;">'fp1.ActiveSheet.Cells(i, j).BackColor = Color.Azure</span>
                <span style="color:blue;">Next</span> j
            <span style="color:blue;">Next</span> i
        <span style="color:blue;">End</span> <span style="color:blue;">Sub</span>
     
    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; }

    <font style="font-size:10pt;" size="2">Hello,</font>

    <font style="font-size:10pt;" size="2">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.</font>

    <font style="font-size:10pt;" size="2">Private Sub tsPlc_MiejscaZerowe_Plus_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles tsPlc_MiejscaZerowe_Plus.Click</font>

    <font style="font-size:10pt;" size="2">Dim cr As FarPoint.Win.Spread.Model.CellRange</font>

    <font style="font-size:10pt;" size="2">Dim Cell_Iter As FarPoint.Win.Spread.Cell</font>

    <font style="font-size:10pt;" size="2">cr = FpSpread1.ActiveSheet.GetSelection(0)</font>

    <font style="font-size:10pt;" size="2">Dim OldDP As Integer</font>

    <font style="font-size:10pt;" size="2">Dim cVal As String</font>

    <font style="font-size:10pt;" size="2">Dim NumCellType As New FarPoint.Win.Spread.CellType.NumberCellType</font>

    <font style="font-size:10pt;" size="2">For i = cr.Row To cr.Row + cr.RowCount - 1</font>

    <font style="font-size:10pt;" size="2">For j = cr.Column To cr.Column + cr.ColumnCount - 1</font>

    <font style="font-size:10pt;" size="2">NumCellType = CType(FpSpread1.Sheets(0).GetCellType(i, j), FarPoint.Win.Spread.CellType.NumberCellType)</font>

    <font style="font-size:10pt;" size="2">NumCellType.DecimalPlaces += 1</font>

    <font style="font-size:10pt;" size="2">Dim NumCellType1 As New FarPoint.Win.Spread.CellType.NumberCellType</font>

    <font style="font-size:10pt;" size="2">NumCellType1.DecimalPlaces = NumCellType.DecimalPlaces</font>

    <font style="font-size:10pt;" size="2">FpSpread1.ActiveSheet.Cells(i, j).CellType = NumCellType1</font>

    <font style="font-size:10pt;" size="2">'fp1.ActiveSheet.Cells(i, j).BackColor = Color.Azure</font>

    <font style="font-size:10pt;" size="2">Next j</font>

    <font style="font-size:10pt;" size="2">Next i</font>

    <font style="font-size:10pt;" size="2">End Sub</font>

    <font style="font-size:10pt;" size="2">Hope it will help you. Please let me know if you have any queries further.</font>



    <font style="font-size:10pt;" size="2">Thanks,</font>

    <font style="font-size:10pt;" size="2">Manpreet Kaur</font>
  • 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)
     <span style="color:blue;">Private</span> <span style="color:blue;">Sub</span> tsPlc_MiejscaZerowe_Plus_Click(<span style="color:blue;">ByVal</span> sender <span style="color:blue;">As</span> System.<span style="color:#2b91af;">Object</span>, <span style="color:blue;">ByVal</span> e <span style="color:blue;">As</span> System.<span style="color:#2b91af;">EventArgs</span>) <span style="color:blue;">Handles</span> tsPlc_MiejscaZerowe_Plus.Click
            <span style="color:blue;">Try</span>
                fpR.SuspendLayout()
                <span style="color:blue;">Dim</span> cr <span style="color:blue;">As</span> FarPoint.Win.Spread.Model.<span style="color:#2b91af;">CellRange</span>
                cr = fpR.Sheets(Tabele).GetSelection(0)
                <span style="color:blue;">Dim</span> cValStr <span style="color:blue;">As</span> <span style="color:blue;">String</span>
                <span style="color:blue;">Dim</span> NumCellType <span style="color:blue;">As</span> <span style="color:blue;">New</span> FarPoint.Win.Spread.CellType.<span style="color:#2b91af;">NumberCellType</span>

                <span style="color:blue;">For</span> i = cr.Row <span style="color:blue;">To</span> cr.Row + cr.RowCount - 1
                    <span style="color:blue;">For</span> j = cr.Column <span style="color:blue;">To</span> cr.Column + cr.ColumnCount - 1
                        cValStr = fpR.Sheets(Tabele).Cells(i, j).Value
                        <span style="color:blue;">If</span> IsNumeric(cValStr) = <span style="color:blue;">True</span> <span style="color:blue;">Then</span>
                            NumCellType = <span style="color:blue;">CType</span>(fpR.Sheets(Tabele).GetCellType(i, j), FarPoint.Win.Spread.CellType.<span style="color:#2b91af;">NumberCellType</span>)
                            NumCellType.DecimalPlaces += 1
                            <span style="color:blue;">Dim</span> NumCellType1 <span style="color:blue;">As</span> <span style="color:blue;">New</span> FarPoint.Win.Spread.CellType.<span style="color:#2b91af;">NumberCellType</span>
                            NumCellType1.DecimalPlaces = NumCellType.DecimalPlaces
                            fpR.Sheets(Tabele).Cells(i, j).CellType = NumCellType1
                        <span style="color:blue;">End</span> <span style="color:blue;">If</span> <span style="color:green;">'If IsNumeric(cValStr) = True Then</span>
                    <span style="color:blue;">Next</span> j
                <span style="color:blue;">Next</span> i
                fpR.ResumeLayout()

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

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

Learn More

Forum Channels