ClipboardCopy slow

Posted by: tranthao on 8 September 2017, 3:23 pm EST

  • Posted 8 September 2017, 3:23 pm EST

    Hello, i want to copy some sheet in a spread to a new spread (both copy/paste Vales and Formulas, no formatting), i use ClipboardCopy and ClipboardPaste, but it so slow, how to copy/paste quickly
    Thanks!
    Private Sub KtToolStripMenuItem_Click(sender As Object, e As EventArgs) Handles KtToolStripMenuItem.Click
    Dim i As Integer
    Dim fptemp As FarPoint.Win.Spread.FpSpread = New FarPoint.Win.Spread.FpSpread
    For i = 0 To fpSpread1.Sheets.Count - 1
    Select Case fpSpread1.Sheets(i).SheetName
    Case "CVDuToan"
    Dim cr As New FarPoint.Win.Spread.Model.CellRange(0, 0, fpSpread1.Sheets(i).RowCount, fpSpread1.Sheets(i).ColumnCount)
    Dim temp As FarPoint.Win.Spread.SheetView = New FarPoint.Win.Spread.SheetView
    temp.RowCount = fpSpread1.Sheets(i).RowCount
    temp. ColumnCount = fpSpread1.Sheets(i). ColumnCount
    fpSpread1.Sheets(i).ClipboardCopy(cr, FarPoint.Win.Spread.ClipboardCopyOptions.Values)
    temp.ClipboardPaste(FarPoint.Win.Spread.ClipboardPasteOptions.Values)
    fpSpread1.Sheets(i).ClipboardCopy(cr, FarPoint.Win.Spread.ClipboardCopyOptions.Formulas)
    temp.ClipboardPaste(FarPoint.Win.Spread.ClipboardPasteOptions.Formulas)
    temp.SheetName = "CVDuToan"
    fptemp.Sheets.Add(temp)
    Case "CuocOto"
    Dim cr As New FarPoint.Win.Spread.Model.CellRange(0, 0, fpSpread1.Sheets(i).RowCount, fpSpread1.Sheets(i).ColumnCount)
    Dim temp As FarPoint.Win.Spread.SheetView = New FarPoint.Win.Spread.SheetView
    temp.RowCount = fpSpread1.Sheets(i).RowCount
    temp. ColumnCount = fpSpread1.Sheets(i). ColumnCount
    fpSpread1.Sheets(i).ClipboardCopy(cr, FarPoint.Win.Spread.ClipboardCopyOptions.Values)
    temp.ClipboardPaste(FarPoint.Win.Spread.ClipboardPasteOptions.Values)
    fpSpread1.Sheets(i).ClipboardCopy(cr, FarPoint.Win.Spread.ClipboardCopyOptions.Formulas)
    temp.ClipboardPaste(FarPoint.Win.Spread.ClipboardPasteOptions.Formulas)
    temp.SheetName = "CuocOto"
    fptemp.Sheets.Add(temp)
    Case "CuocBo"
    Dim cr As New FarPoint.Win.Spread.Model.CellRange(0, 0, fpSpread1.Sheets(i).RowCount, fpSpread1.Sheets(i).ColumnCount)
    Dim temp As FarPoint.Win.Spread.SheetView = New FarPoint.Win.Spread.SheetView
    temp.RowCount = fpSpread1.Sheets(i).RowCount
    temp. ColumnCount = fpSpread1.Sheets(i). ColumnCount
    fpSpread1.Sheets(i).ClipboardCopy(cr, FarPoint.Win.Spread.ClipboardCopyOptions.Values)
    temp.ClipboardPaste(FarPoint.Win.Spread.ClipboardPasteOptions.Values)
    fpSpread1.Sheets(i).ClipboardCopy(cr, FarPoint.Win.Spread.ClipboardCopyOptions.Formulas)
    temp.ClipboardPaste(FarPoint.Win.Spread.ClipboardPasteOptions.Formulas)
    temp.SheetName = "Cuocbo"
    fptemp.Sheets.Add(temp)
    End Select
    Next
    End Sub
  • Marked as Answer

    Replied 8 September 2017, 3:23 pm EST

    Hi,

    GetArray/SetArray on the sheet data models would be faster for copying ranges of values and formulas like that:
    http://sphelp.grapecity.com/WebHelp/SpreadNet9/WF/webframe.html#FarPoint.Win.Spread~FarPoint.Win.Spread.Model.DefaultSheetDataModel~GetArray(Int32,Int32,Int32,Int32,Boolean).html
    http://sphelp.grapecity.com/WebHelp/SpreadNet9/WF/webframe.html#FarPoint.Win.Spread~FarPoint.Win.Spread.Model.DefaultSheetDataModel~SetArray(Int32,Int32,Object[,],Boolean,ReferenceStyle).html
    Another option, since you are copying all the rows, is to use XML serialization to save the data model to XML and load that into the target sheet:
    Private Sub KtToolStripMenuItem_Click(sender As Object, e As EventArgs) Handles KtToolStripMenuItem.Click
    Dim i As Integer
    Dim fptemp As FarPoint.Win.Spread.FpSpread = New FarPoint.Win.Spread.FpSpread
    For i = 0 To fpSpread1.Sheets.Count – 1
    Select Case fpSpread1.Sheets(i).SheetName
    Case “CVDuToan”
    Dim temp As FarPoint.Win.Spread.SheetView = New FarPoint.Win.Spread.SheetView
    FarPoint.Win.Serializer.SetObjectXml(CType(temp.Models.Data, FarPoint.Win.ISerializeSupport), FarPoint.Win.Serializer.GetObjectXml(CType(fpSpread1.Sheets(i).Models.Data, FarPoint.Win.ISerializeSupport), "root"), "root")
    temp.LoadFormulas(false);
    fptemp.Sheets.Add(temp)
    Case “CuocOto”
    Dim temp As FarPoint.Win.Spread.SheetView = New FarPoint.Win.Spread.SheetView
    FarPoint.Win.Serializer.SetObjectXml(CType(temp.Models.Data, FarPoint.Win.ISerializeSupport), FarPoint.Win.Serializer.GetObjectXml(CType(fpSpread1.Sheets(i).Models.Data, FarPoint.Win.ISerializeSupport), "root"), "root")
    temp.LoadFormulas(false);
    fptemp.Sheets.Add(temp)
    Case “CuocBo”
    Dim temp As FarPoint.Win.Spread.SheetView = New FarPoint.Win.Spread.SheetView
    FarPoint.Win.Serializer.SetObjectXml(CType(temp.Models.Data, FarPoint.Win.ISerializeSupport), FarPoint.Win.Serializer.GetObjectXml(CType(fpSpread1.Sheets(i).Models.Data, FarPoint.Win.ISerializeSupport), "root"), "root")
    temp.LoadFormulas(false);
    fptemp.Sheets.Add(temp)
    End Select
    Next
    End Sub

    The call to SheetView.LoadFormulas is necessary to parse the formulas loaded as strings from the XML into Expression objects in the data model after manually loading XML to the sheet from your code. If you use GetArray/SetArray, then the formulas would be parsed when you set the formulas to the data model with SetArray.

    Regards,
    -Sean
  • Replied 8 September 2017, 3:23 pm EST

    Thank you so much @Sean Lawyer. I can do it :)
  • Replied 8 September 2017, 3:23 pm EST

    Hello,

    We are glad to know that your issue has been resolved now.

    Thanks,
    Reeva
Need extra support?

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

Learn More

Forum Channels