Excel-like behaviour of numeric-cell in a databound-spread possible ?

Posted by: peter07 on 8 September 2017, 2:14 pm EST

  • Posted 8 September 2017, 2:14 pm EST

    Hi all,


    in my VB2008-Project, a Spread5 is bound to a DataSet. The DataSet get the data from a sql-database (table) and contains some numeric-fields.


    The project works fine, I can store numeric data to the database in this format: 1000.333333


    My question: Is there an easy way
    - to display these numeric-cells with thousand-separator to the user in a format: 1,000.33 (the cell.text property ?)
    - to edit these cells (or paste data from clipboard) in a format 1,000.333333 and store this value to the dataset/database (the cell.value property ?)

    Like the behaviour in Excel: The cell could contain the value 1000.333333  but I can format the cell to display the value to the user like 1,000.33


    Thank's for your feedback in advance,


    Peter_S

  • Replied 8 September 2017, 2:14 pm EST

    Hello Peter,


    You can apply a NumberCellType to that particular column with the ShowSeparator Property set to true which specifies whether to display the thousands separator string. However, you can set the other properties of that cell type as well.



                FarPoint.Win.Spread.CellType.NumberCellType num = new FarPoint.Win.Spread.CellType.NumberCellType();
                num.DecimalPlaces = 5;
                num.ShowSeparator = true;
                fpSpread1.ActiveSheet.Columns[5].CellType = num;


    Hope this will help you. Thanks.

  • Replied 8 September 2017, 2:14 pm EST

    Hi Reeva,


    sorry, but this does not work first.


    Only when I'm setting the property .DataAutoCellTypes = False, the decimal places are visible in the column.


    But that solves not exactly the wish of my customer.


    What I want to do is: When the cell is not in edit-mode, the cell-content should only have two decimal-places.
    If the cell is in edit-mode, the cell-content should have 5 (or more) decimal-places.


    Like Excel: If I format the cell, the cell could have 1,000.00 as the text-property, but the value-property could be 1,000.0012132


    Best Regards,


    Peter

  • Replied 8 September 2017, 2:14 pm EST

    Hello Peter,


    You can try setting the editor and renderer of that particular column with NumberCellType having DecimalPlaces set accordingly. Code for the same is given below:



                fpSpread1.ActiveSheet.DataAutoCellTypes = false;
                FarPoint.Win.Spread.CellType.NumberCellType num = new FarPoint.Win.Spread.CellType.NumberCellType();
                num.DecimalPlaces = 5;
                num.ShowSeparator = true;
                fpSpread1.ActiveSheet.Columns[5].Editor = num;


                FarPoint.Win.Spread.CellType.NumberCellType num1 = new FarPoint.Win.Spread.CellType.NumberCellType();
                num1.DecimalPlaces = 2;
                num1.ShowSeparator = true;
                fpSpread1.ActiveSheet.Columns[5].Renderer = num1;


    Hope this will help you. Thanks.

  • Replied 8 September 2017, 2:14 pm EST

    Hello Reeva,


    yes, that's it !


    Thank you very much.


    Peter

Need extra support?

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

Learn More

Forum Channels