Identify cell range when copy from excel and paste in Spread

Posted by: shinsid on 8 September 2017, 3:20 pm EST

  • Posted 8 September 2017, 3:20 pm EST

    Hello,

    I have to copy some values from excel to spread. But during this process i want to know the cell range where it is going to paste the data in spread.

    I want to do this in the following event -

    private void SpreadClipboardPasting(object sender, ClipboardPastingEventArgs e)
    {
    //Identify cell range where data is going to be pasted to carry out few operations on cell formatting before pasting the data.
    }


    Please help me with this problem.

    Thanks & regards,
    Siddharth
  • Replied 8 September 2017, 3:20 pm EST

    Hi Siddharth,

    You could get the range if you used the ClipboardPastedEvent instead of ClipboardPastingEvent, but since it is required by you to use the Pasting event, then you could just get the selection on the current sheet (where you are pasting to) using:

    FpSpread.ActiveSheet.GetSelection(0)

    Let me know if that helps.

    Regards,
    Kevin
  • Replied 8 September 2017, 3:20 pm EST

    Hi Kevin,

    Thanks for immediate reply. But i want to know the cell range before data is pasted onto it. So i really want to use Clipboard pasting instead of Clipboard pasted event. And the above solution which u suggested doesn't suits to my requirements. I can only get the active cell from it where I am trying to paste the data.

    Please help me with some other solution.

    Thanks in advance.
  • Replied 8 September 2017, 3:20 pm EST

    Hi Siddharth,

    I apologize for the misspell, I meant to say that you could use the GetSelection solution in the Pasting event. When you are pasting from Excel to Spread, the active cell(s) where you are going to paste into in Spread should be what you get from FpSpread.ActiveSheet.GetSelection(0). You can apply formatting on this selection before it is pasted into the Spread component. Please clarify what the issue is.

    Regards,
    Kevin
  • Replied 8 September 2017, 3:20 pm EST

    Hi Siddharth,

    It appears that essentially you would like to get the values from the clipboard and perform the paste manually in Spread.

    You could use the ClipboardPasting event handler to override the default paste behavior using e.Cancel = true. Then you can implement the logic to get the data object from the clipboard and do the paste manually.

    The code to do this is not trivial, as you would need to parse the tab-delimited text from Excel, extract the values, and put them in the correct cells with SetValue.

    Regards,
    Kevin
  • Replied 8 September 2017, 3:20 pm EST

    Hello Kevin,

    Can you please suggest some code if you already have or done it in the past to parse the tab-delimited text from excel to spread cell range ?

    Thanks & regards,
    Siddharth
  • Marked as Answer

    Replied 8 September 2017, 3:20 pm EST

    Hi Siddharth,

    Here is a blog I wrote a little while ago about doing the same in SpreadASP: http://sphelp.grapecity.com/2015/10/05/spreadasp-excel-copypaste/

    Here, you would do something similar to that example except in Winforms. You could take a look at the String.Split method, which could help you with writing code to parse through the string and set the values in the spreadsheet: https://msdn.microsoft.com/en-us/library/system.string.split(v=vs.110).aspx

    Let me know if that helps.

    Regards,
    Kevin
  • Replied 8 September 2017, 3:20 pm EST

    Hi Kevin,

    Unfortunately it doesn't look very straightforward to me and its difficult to understand. It would be really helpful if you can give some short example(in winforms and not in ASP.net) demonstrating how to get cell range from the tab-delimited text from excel.

    Thanks in advance.

    SIddharth
  • Replied 8 September 2017, 3:20 pm EST

    Ok.. Ill explain you the exact case.

    I have a spread sheet in which there are lot of cells with number type. I have set the decimal places for these cell to 2 points so "0.00" is allowed. Other thing is that when I enter a value in the cell and done with editing, this value is passed to a object behind. So if I enter "3.34" in the cell, it will be assigned to the object.

    But now when I copy some random value like "1.1234"(with more decimal points) from some excel sheet, the cell only takes "1.12" and not "1.1234" and assigns it to my object. So my object finally has the value "1.12".

    My requirements is - I want to store the actual value in the object which is copied from the excel but still want to show only two decimal points on the spread.

    It sounds bit ugly and complex. Sorry for that. But any help would be appreciated.

    Please let me know if u need further description.

    Best regards,
    Siddharth
  • Replied 8 September 2017, 3:20 pm EST

    I have one idea in my mind.

    But for that i need to convert the IDataObject from clipboard to a cell range.

    When I copy data from the excel, I get some string format of IDataObject like shown in the picture attached with this post, which I am not able to convert into FarPoint.Win.Spread.CellRange.

    If somehow I can convert this into a cell range then I can achieve what I want.


  • Replied 8 September 2017, 3:20 pm EST

    Hello Kevin,

    Just an update.

    I explored your solution a little bit further and I think I achieved what I wanted upto a certain extent. Still if you can give a better and easy solution, I would be grateful.

    Thanks alot.

    Regards,
    Siddharth
Need extra support?

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

Learn More

Forum Channels