NumberCellType and Pasting from Excel

Posted by: rchu on 8 September 2017, 2:34 pm EST

  • Posted 8 September 2017, 2:34 pm EST

    I have two Excel cells. The first Excel cell is formatted with leading negative sign, thus -123. The second Excel cell is formatted with parenthesis, thus (123).


    I pasted these two Excel cells to two Spread NumberCellType cells.


    When NegativeFormat of CellType is set equal to Parenthesis, the first cell lost its negative sign, while the second cell is pasted correctly.


    When NegativeFormat of CellType is set equal to NegativeSignBefore, the first cell is pasted correctly, while the second cell lost its negative sign.


    I want to get it working for both Excel formats. How can I do this?


    What about a customer cell type? If so, can you please show me an example.

  • Replied 8 September 2017, 2:34 pm EST

    Hello,

    The behavior what you see is correct. You may create two different NumberCellType objects and set the NegativeFormat for both objects individually.Then assign these numbercelltype objects to  cells where you are pasting data to.

            Dim num1, num2 As New FarPoint.Win.Spread.CellType.NumberCellType

            num1.NegativeFormat = FarPoint.Win.Spread.CellType.NegativeFormat.NegativeSignBefore

            num2.NegativeFormat = FarPoint.Win.Spread.CellType.NegativeFormat.Parentheses

     
            FpSpread1.ActiveSheet.Cells(0, 0).CellType = num1

            FpSpread1.ActiveSheet.Cells(0, 1).CellType = num2

    Thanks,

     

  • Replied 8 September 2017, 2:34 pm EST

    Spread imports all the Excel cell formats to the General cell type and assigns the formats as closely as possible by setting the NumberFormat, DateTimeFormat, and FormatString properties. The numberformat you set for your general celltype shouldn't come into it as Spread should reset the Numberformat to match what it is importing. I created a xlsx sheet with your 2 cells one using (100.0) and the other -100.0 for negative 100.0. I then created a new project, pulled a spread onto it and added a line to the Form1_Load event to open my spreadsheet and it opened it displaying my two cells in the format I had set them. I tried it in both version 5 and version 4, is there anything else I need to do to see your issue?

  • Replied 8 September 2017, 2:34 pm EST

    paul1960:

    Spread imports all the Excel cell formats to the General cell type and assigns the formats as closely as possible by setting the NumberFormat, DateTimeFormat, and FormatString properties. The numberformat you set for your general celltype shouldn't come into it as Spread should reset the Numberformat to match what it is importing. I created a xlsx sheet with your 2 cells one using (100.0) and the other -100.0 for negative 100.0. I then created a new project, pulled a spread onto it and added a line to the Form1_Load event to open my spreadsheet and it opened it displaying my two cells in the format I had set them. I tried it in both version 5 and version 4, is there anything else I need to do to see your issue?

    Actually, the above applies to importing XLS/XLSX into Spread, not for pasting data from Excel.  Spread does not support Excel's clipboard format, and data pasted from Excel is pasted like it would be into Notepad -- as a tab-delimited string.  Spread pastes this by parsing the string into individual strings to set into the cells as if a user typed the values.  The cell type for each cell parses the string for the cell into the value, and NumberCellType expects the string to conform to the format settings for the cell type (i.e. NegativeFormat).

    GeneralCellType is the closest to Excel's behavior, and GeneralFormatter will parse the string using IParseFormatSupport to create a format string and format provider.  If you want to support pasting values from Excel into Spread, that is the best cell type to use for the target cells.  Then if you want to process the cell range afterward to change the cell types to restrict input by the user, you can get the FormatString and DateTimeFormat or NumberFormat from the style model using GetParseFormat.

  • Replied 8 September 2017, 2:34 pm EST

    In my application, I need to constraint user in entering numeric data only. So I chose NumberCellType. We had a discussion on this in an earlier discussion thread.
    http://www.clubfarpoint.com/Forums/forums/thread/91509.aspx
    I
    don't want to switch from NumberCellType to GeneralCellType at this stage, because the application is so close to implementation.


    Re your comments... "Spread pastes this by parsing the string into individual strings to set into the cells as if a user typed the values.". I set up a NumberCellType, set NegativeFormat = Parenthesis, and enter -123 interactively. Spread interprets the negative sign properly and displays (123).


    However, if I paste -123 from Excel to the same Spread cell, the result is 123, without a negative. So there seems to be a difference whether the number is entered interactively or paste from Excel.


    Would I be able to fine tune the paste-from-Excel behaviour by creating a custom cell type, and override the method responsible for pasting from Excel. If this feasible, can you please give me some hint on where to start.


    Thanks you for all your efforts.

  • Replied 8 September 2017, 2:34 pm EST

    Hi,

    Sorry, I was not very clear about how the string is parsed and set in Spread when pasting.  Spread splits the string into substrings for each cell, then sets the text to the cell using SetText, which uses the Parse method for the cell formatter to parse the string into the value for the cell.  This is not quite the same as typing the value into the cell unless the cell type is GeneralCellType (which does not support formatted data entry, only free-form data entry).  Pasting to the cells is equivalent to typing into the cells only for GeneralCellType.  For other cell types, typing into the cells will use the editor control's data validation in the keyboard events to restrict data entry and map certain keys (such as '-' in NumberCellType) for convenience of the end-user.

    You can override the Parse method in a custom cell type to change the default parsing (for example, change "-123" to "(123)" before calling MyBase.Parse when NegativeFormat is Parentheses).

  • Replied 8 September 2017, 2:34 pm EST

    The example I gave ealier is just a simulation of the problem that I am facing with. In fact, my Farpoint Spread application has many cells, say 30 x 10, which will accept data pasted in from Excel. I need to get the cell values in correctly whether a number is formatted with leading negative sign, or parenthesis on Excel. That is, I need a single cell type that will accept both Excel formats, and will bring in the negative value correctly. Please advise how this can be achieved.

  • Replied 8 September 2017, 2:35 pm EST

    Thank you for the information. Will try it out.
Need extra support?

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

Learn More

Forum Channels