Log in to start a topic

I have a question about pasting

Posted by: addio777 on 16 July 2018, 4:35 pm EST

  • addio777

    • Post Options:
    • Link
      Copy

    Posted 16 July 2018, 4:35 pm EST

    Hello.

    If you paste the same data from a Sheet with a hidden column, a DefaultSheetDataModel event is raised. Why would an event occur when pasting the same data?

    Then, after uploading the Excel file, the event will be generated even if you paste the Excel file again.

    private void BaseForm_KeyUp(object sender, KeyEventArgs e)

    {

    if (fps == null) return;

            if (e.Control == true && e.KeyCode == Keys.V || e.Control == false && e.KeyCode == Keys.V)
        {
            fps.ActiveSheet.ClipboardPaste(ClipboardPasteOptions.AsStringSkipHidden);
        }

        if (e.Control == true && e.KeyCode == Keys.C || e.Control == false && e.KeyCode == Keys.C)
        {
            Clipboard.Clear();
            fps.ActiveSheet.ClipboardCopy(ClipboardCopyOptions.AsStringSkipHidden);
        }
    }

  • grapecity

    Posted 16 July 2018, 11:11 pm EST

    Hello,

    I am not able to replicate this issue at my end. I created a Spreadsheet with data similar to Excel and pasted data from excel in it. DefaultSheetDataModel changed event is not fired.

    I used following code to check that:

    DefaultSheetDataModel model;
           model = (FarPoint.Win.Spread.Model.DefaultSheetDataModel)fpSpread1.ActiveSheet.Models.Data;
           model.ColumnCount = 10;
           model.RowCount = 10;

           model.Changed += Model_Changed;
         
       }

       private void Model_Changed(object sender, SheetDataModelEventArgs e)
       {
           MessageBox.Show(e.RowCount.ToString());
           //throw new NotImplementedException();
       }

    Could you please provide me your sample application to debug it further?

    Thanks,

    Deepak Sharma

  • addio777

    Posted 17 July 2018, 11:48 am EST

    Hello.

    It seems to be a problem with hidden columns. If you paste the data in Excel into Sheet after DownLoading the Sheet to Excel, a Change Event occurs.

    I can not attach the file, I upload the source I tested.

    The form has one FpSpread and two buttons.

    using System;

    using System.Collections.Generic;

    using System.ComponentModel;

    using System.Data;

    using System.Drawing;

    using System.Linq;

    using System.Text;

    using System.Threading.Tasks;

    using System.Windows.Forms;

    using FarPoint.Win.Spread;

    using FarPoint.Win.Spread.CellType;

    namespace ClipboardPasteTest

    {

    public partial class Form1 : Form

    {

    public Form1()

    {

    InitializeComponent();

    this.Load += Form1_Load;

    }

        private void Form1_Load(object sender, EventArgs e)
    {
        fpSpread1.KeyUp += FpSpread1_KeyUp;
        btnSearch.Click += BtnSearch_Click;
        btnExcel.Click += BtnExcel_Click;

        FarPoint.Win.Spread.Model.DefaultSheetDataModel defaultSheetDataModel = (FarPoint.Win.Spread.Model.DefaultSheetDataModel)fpSpread1.ActiveSheet.Models.Data;
        defaultSheetDataModel.Changed += new FarPoint.Win.Spread.Model.SheetDataModelEventHandler(defaultSheetDataModel_Changed);
    }


    private void BtnExcel_Click(object sender, EventArgs e)
    {
        try
        {
            Control ctlSpread = null; 
            FpSpread spSpread = new FpSpread();  

            spSpread = (FpSpread)fpSpread1;

            Object[] myarray = new Object[spSpread.Sheets.Count];
            spSpread.Sheets.CopyTo(myarray, 0);

            if (myarray.Length == 0) return;

            FpSpread sp = new FpSpread();
            sp.Sheets.Clear();

            SheetView sv = null;
            SheetView newSheet = null;
            FarPoint.Win.Spread.HideRowFilter f = null;

            int FrozenColumnCount = 0;
            if (spSpread.ActiveSheet.FrozenColumnCount > 0)
                FrozenColumnCount = spSpread.ActiveSheet.FrozenColumnCount - 1;

            string sFrozenColumnName = string.Empty;

            if (spSpread.ActiveSheet.Columns[FrozenColumnCount].Tag == null)
            {
                sFrozenColumnName = spSpread.ActiveSheet.Columns[FrozenColumnCount].DataField.ToString();
            }
            else
            {
                sFrozenColumnName = spSpread.ActiveSheet.Columns[FrozenColumnCount].Tag.ToString();
            }

            bool bFrozenCheck = false;

            for (int iSheetIndex = 0; iSheetIndex < spSpread.Sheets.Count; iSheetIndex++)
            {
                if (spSpread.ActiveSheet.SheetName != ((SheetView)myarray[iSheetIndex]).SheetName) continue;
                sv = (SheetView)myarray[iSheetIndex];
                newSheet = (FarPoint.Win.Spread.SheetView)FarPoint.Win.Serializer.LoadObjectXml(typeof(FarPoint.Win.Spread.SheetView), FarPoint.Win.Serializer.GetObjectXml(sv, "CopySheet"), "CopySheet");
                f = (FarPoint.Win.Spread.HideRowFilter)sv.RowFilter;

                string sText = string.Empty;
                newSheet.ReferenceStyle = FarPoint.Win.Spread.Model.ReferenceStyle.A1;

                for (int i = newSheet.ColumnCount - 1; i >= 0; i--)
                {
                    if (newSheet.Columns[i].Visible == false)
                    {
                        newSheet.Columns.Remove(i, 1);
                    }
                    else
                    {
                        newSheet.ColumnHeader.Columns[i].Border = new FarPoint.Win.LineBorder(Color.Black, 1);

                        if (newSheet.Columns[i].CellType != null)
                        {
                            if (newSheet.Columns[i].CellType.GetType().FullName == "FarPoint.Win.Spread.CellType.ComboBoxCellType")
                            {
                                foreach (Row idx in newSheet.Rows)
                                {
                                    sText = newSheet.Cells[idx.Index, i].Text;

                                    newSheet.Cells[idx.Index, i].CellType = new FarPoint.Win.Spread.CellType.TextCellType();
                                    newSheet.Cells[idx.Index, i].Text = sText;
                                }
                            }
                            else if (newSheet.Columns[i].CellType.GetType().FullName == "FarPoint.Win.Spread.CellType.MultiColumnComboBoxCellType")
                            {
                                foreach (Row idx in newSheet.Rows)
                                {
                                    sText = newSheet.Cells[idx.Index, i].Text;

                                    newSheet.Cells[idx.Index, i].CellType = new FarPoint.Win.Spread.CellType.TextCellType();
                                    newSheet.Cells[idx.Index, i].Text = sText;
                                }
                            }
                            else if (newSheet.Columns[i].CellType.GetType().FullName == "FarPoint.Win.Spread.CellType.CheckBoxCellType")
                            {
                                newSheet.Columns.Remove(i, 1);
                            }
                            else
                            {
                                if (newSheet.Columns[i].Formula != string.Empty)
                                    newSheet.Columns[i].Formula = "";
                            }
                        }
                    }
                }

                for (int i = newSheet.RowCount - 1; i >= 0; i--)
                {
                    if (newSheet.Rows[i].Visible == false || (f != null && f.IsRowFilteredOut(i)))
                    {
                        newSheet.Rows.Remove(i, 1);
                    }
                }

                for (int iCol = newSheet.ColumnCount - 1; iCol >= 0; iCol--)
                {
                    newSheet.Columns[iCol].AllowAutoFilter = false;
                    for (int iRow = newSheet.RowCount - 1; iRow >= 0; iRow--)
                    {
                        newSheet.Cells[iRow, iCol].Border = new FarPoint.Win.LineBorder(Color.Black, 1);
                    }
                }


                for (int iCol = newSheet.ColumnCount - 1; iCol >= 0; iCol--)
                {
                    if (newSheet.Columns[iCol].Tag == null)
                    {
                        if (newSheet.Columns[iCol].DataField.ToString() == sFrozenColumnName)
                        {
                            newSheet.FrozenColumnCount = iCol;
                            bFrozenCheck = true;
                            break;
                        }
                    }
                    else
                    {
                        if (newSheet.Columns[iCol].Tag.ToString() == sFrozenColumnName)
                        {
                            newSheet.FrozenColumnCount = iCol + 1;
                            bFrozenCheck = true;
                            break;
                        }
                    }
                }

                sp.Sheets.Add(newSheet);
            }

            if (!bFrozenCheck)
                newSheet.FrozenColumnCount = 0;

            sp.TabStripPolicy = TabStripPolicy.Always;

            for (int iSheetIndex = 0; iSheetIndex < sp.Sheets.Count; iSheetIndex++)
            {
                sp.Sheets[iSheetIndex].Protect = false;
            }

            string sFrmName = this.Name.ToString();
            string sFileName = string.Empty;
            string sExcelFileName = this.Name.ToString() + "[" + newSheet.SheetName.Replace("/", "") + "]_" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xlsx";

            sFileName = Application.StartupPath + "\\" + sExcelFileName;
            sp.SaveExcel(sFileName, FarPoint.Excel.ExcelSaveFlags.NoFormulas | FarPoint.Excel.ExcelSaveFlags.SaveCustomColumnHeaders | FarPoint.Excel.ExcelSaveFlags.UseOOXMLFormat);

            System.Diagnostics.Process.Start(sFileName);

        }
        catch (Exception ex)
        {
            //MessageHelper.GMessageBox("MSG00003", new object[] { e.Message });
        }
    }

    private void BtnSearch_Click(object sender, EventArgs e)
    {
        InitSpread();

        DataTable dt = new DataTable();
        dt.Columns.Add("TEST01", typeof(Int32));
        dt.Columns.Add("TEST02", typeof(Int32));
        dt.Columns.Add("TEST03", typeof(Int32));
        dt.Columns.Add("TEST04", typeof(Int32));
        dt.Columns.Add("TEST05", typeof(Int32));

        DataRow dr = dt.NewRow();

        dr["TEST01"] = 22222;
        dr["TEST02"] = 10000;
        dr["TEST03"] = 200000;
        dr["TEST04"] = 500000;
        dr["TEST05"] = 0;

        dt.Rows.Add(dr);

        dr = dt.NewRow();

        dr["TEST01"] = 5000;
        dr["TEST02"] = 30000;
        dr["TEST03"] = 4000;
        dr["TEST04"] = 5000;
        dr["TEST05"] = 0;

        dt.Rows.Add(dr);

        DataSet ds = new DataSet();
        ds.Tables.Add(dt);

        fpSpread1.ClipboardPasteToFill = false;
        fpSpread1.AutoClipboard = false;
        fpSpread1.ActiveSheet.DataSource = ds;
        fpSpread1.ActiveSheet.Columns[1].Visible = false;
        fpSpread1.ActiveSheet.Columns[3].Visible = false;
    }

    private void InitSpread()
    {
        fpSpread1.ActiveSheet.RowCount = 0;
        fpSpread1.ActiveSheet.ColumnCount = 0;

        fpSpread1.ActiveSheet.DataSource = null;

        this.AddColumn("TEST01", "TEST01", FPMerge.None, 60, true, FPHAlign.Right, FPVAlign.Center, 0);
        this.AddColumn("TEST02", "TEST02", FPMerge.None, 60, true, FPHAlign.Right, FPVAlign.Center, 0);
        this.AddColumn("TEST03", "TEST03", FPMerge.None, 60, true, FPHAlign.Right, FPVAlign.Center, 0);
        this.AddColumn("TEST04", "TEST04", FPMerge.None, 60, true, FPHAlign.Right, FPVAlign.Center, 0);
        this.AddColumn("TEST05", "TEST05", FPMerge.None, 60, true, FPHAlign.Right, FPVAlign.Center, 0);            
    }

    public enum FPHAlign { General, Left, Center, Right };
    public enum FPVAlign { General, Top, Center, Bottom };

    public enum FPMerge { None, Always, Restricted };
    public enum FPDataType { FPText, FPMultiText, FPNum, FPNumText, FPDate, FPDateText, FPTime, FPDateTime, FTLong, FPDateMonth };

    private void AddColumn(string sHeaderText, string sFieldName, FPMerge merge, float fWidth, bool bVisible
            , FPHAlign fph, FPVAlign fpv, int pDecPlaces)
    {
        AddColumn(new NumberCellType(), sHeaderText, sFieldName, fph, fpv, merge
         , fWidth, bVisible, null, true, FPDataType.FPNum, false, true);
        int iCol = fpSpread1.ActiveSheet.Columns[fpSpread1.ActiveSheet.Columns.Count - 1].Index;
        SetDecimal(iCol, iCol, pDecPlaces, true);
    }

    public void SetDecimal(int iCol1, int iCol2, int pDecPlaces, bool pShowSep)
    {
        //FarPoint.Win.Spread.Cell range1;

        FarPoint.Win.Spread.Column col = fpSpread1.ActiveSheet.Columns[iCol1, iCol2];

        //range1 = sv.Cells[0, iCol, sv.Rows.Count-1, Col2];
        col.CellType = new NumberCellType();
        ((NumberCellType)col.CellType).DecimalPlaces = 10;
        ((NumberCellType)col.CellType).ShowSeparator = pShowSep;
        //((NumberCellType)col.CellType).NegativeRed = true;
        ((NumberCellType)col.CellType).MaximumValue = 99999999999999;
        ((NumberCellType)col.CellType).MinimumValue = -99999999999999;
        ((NumberCellType)col.CellType).LeadingZero = LeadingZero.Yes;
    }

    private void AddColumn(ICellType objCellType, string sHeaderText, string sFieldName, FPHAlign hAlign, FPVAlign vAlign
                            , FPMerge merge, float fWidth, bool bVisible, DataTable dt, bool bCommaVisible
                            , FPDataType dtTime, bool bIsDesc, bool blFixedPoint)
    {
        int iIndex = 0;
        iIndex = fpSpread1.ActiveSheet.Columns.Count;
        fpSpread1.ActiveSheet.Columns.Add(iIndex, 1);
        //this.sv.SetColumnAllowAutoSort(iIndex, true);
        Column col = fpSpread1.ActiveSheet.Columns[fpSpread1.ActiveSheet.Columns.Count - 1];

        col.DataField = sFieldName;
        col.CellType = objCellType;

        //col.HorizontalAlignment = halign;
        //col.MergePolicy = merge;

        col.VerticalAlignment = FarPoint.Win.Spread.CellVerticalAlignment.Center;
        col.Width = fWidth;
        col.Visible = bVisible;
        col.Tag = sFieldName;

        switch (merge)
        {
            case FPMerge.None:
                col.MergePolicy = FarPoint.Win.Spread.Model.MergePolicy.None;
                break;
            case FPMerge.Always:
                col.MergePolicy = FarPoint.Win.Spread.Model.MergePolicy.Always;
                col.VerticalAlignment = FarPoint.Win.Spread.CellVerticalAlignment.Center;
                break;
            case FPMerge.Restricted:
                col.MergePolicy = FarPoint.Win.Spread.Model.MergePolicy.Restricted;
                col.VerticalAlignment = FarPoint.Win.Spread.CellVerticalAlignment.Center;
                break;

        }

        sHeaderText = sHeaderText.Trim();

        // 다국어 처리
        if (sHeaderText.EndsWith("*")) // 필수항목 확인 ( * )
        {
            if (sHeaderText.Length > 0)
            {
                sHeaderText = sHeaderText.Remove(sHeaderText.Length - 1, 1);
            }
        }
        else
        {
        }

        col.Label = sHeaderText;
    }

    private void FpSpread1_KeyUp(object sender, KeyEventArgs e)
    {
        if (e.Control == true && e.KeyCode == Keys.V || e.Control == false && e.KeyCode == Keys.V)
        {
            fpSpread1.ActiveSheet.ClipboardPaste(ClipboardPasteOptions.AsStringSkipHidden);
            fpSpread1.AutoClipboard = true;
        }

        if (e.Control == true && e.KeyCode == Keys.C || e.Control == false && e.KeyCode == Keys.C)
        {
            fpSpread1.AutoClipboard = false;
            Clipboard.Clear();
            fpSpread1.ActiveSheet.ClipboardCopy(ClipboardCopyOptions.AsStringSkipHidden);
        }
    }

    protected void defaultSheetDataModel_Changed(object sender, FarPoint.Win.Spread.Model.SheetDataModelEventArgs e)
    {
        switch (e.Type)
        {
            case FarPoint.Win.Spread.Model.SheetDataModelEventType.CellsUpdated:
                if (e.Column == -1) return;

                FarPoint.Win.Spread.Cell cell;

                cell = fpSpread1.ActiveSheet.Cells[e.Row, e.Column];


                cell.ForeColor = Color.Red;

                break;
        }
    }
}

    }

  • grapecity

    Posted 17 July 2018, 10:53 pm EST

    Hello,

    Above code does not suggest what data are you pasting in Spread. Here are the steps which I followed:

    1. Form is loaded with blank Spread.
    2. I click on BtnSearch to fill data in Spread.
    3. Once data is loaded I click on BtnExcel to Export to excel.
    4. Once the data is exported to Excel I copied the data from there and pasted into Spread.
    5. Spread already has same data and when pasted from Excel,

    I do not see the Data model changed event fired:

     private void DefaultSheetDataModel_Changed(object sender, FarPoint.Win.Spread.Model.SheetDataModelEventArgs e)
        {
            switch (e.Type)
            {
                case FarPoint.Win.Spread.Model.SheetDataModelEventType.CellsUpdated:
                    if (e.Column == -1) return;

                    FarPoint.Win.Spread.Cell cell;

                    cell = fpSpread1.ActiveSheet.Cells[e.Row, e.Column];


                    cell.ForeColor = Color.Red;

                    break;
            }
            //throw new NotImplementedException();
        }

    Please test the attached sample application and Excel file. Let me know if you are able to replicate the issue with this.

    Thanks,

    Deepak Sharma

    SpreadWinDSDMChangedIssue.zip

  • addio777

    Posted 18 July 2018, 2:57 pm EST

    Thank you for answer.

    Try to paste the data in Excel twice after Excel is downloaded.

    Then a Change Event occurs.

    The first time you paste, the Change Event does not occur.

    If you copy and paste again from Excel, a change event occurs.

    Thanks.

  • grapecity

    Posted 18 July 2018, 11:12 pm EST

    Hello,

    I am able to replicate this issue at my end. I have submitted it as a bug to the development team for further investigation.

    The bug id for this issue is 261004, I will let you know as soon as I get an update on this.

    Thanks,

    Deepak Sharma

  • addio777

    Posted 19 July 2018, 3:31 pm EST

    Thank you for answer.

    I am using the Spread 10 version.

    Let me know when the bug is resolved.

    Thanks

  • grapecity

    Posted 19 July 2018, 3:43 pm EST

    Hello,

    This is found to be an issue with the code and not a bug. The explanation given by the development team is as follows:

    In the first time,the ClipboardPaste(…) method will be invoked in FpSpread1_KeyUp of customer code with clipBoardPasteOption is AsStringSkipHidden because fpSpread1.AutoClipboard is false. After that the fpSpread1.AutoClipboard is true, so in the second time spread will invoke ClipboardPaste(…) with clipBoardPasteOption is all. So event is fire because spread will set value to the hidden column.

    You should use inputMap for this case. Please see steps like this:

    Steps:

    1. Remove all code set fpSpread1.AutoClipboard

    2. Remove code in the FpSpread1_KeyUp event

    3. Replace the form_load method by the bellow code:

    
private void Form1_Load(object sender, EventArgs e)
    {      
 
      FarPoint.Win.Spread.Model.DefaultSheetDataModel defaultSheetDataModel = (FarPoint.Win.Spread.Model.DefaultSheetDataModel)fpSpread1.ActiveSheet.Models.Data;
      defaultSheetDataModel.Changed += DefaultSheetDataModel_Changed;
 
      var inputMap = fpSpread1.GetInputMap(InputMapMode.WhenFocused, OperationMode.Normal);
      inputMap.Put(new Keystroke(Keys.V, Keys.Control), SpreadActions.ClipboardPasteAsStringSkipHidden);
      inputMap.Put(new Keystroke(Keys.C, Keys.Control), SpreadActions.ClipboardCopyAsStringSkipHidden);
    }

    I hope it helps!

    Thanks,

    Deepak Sharma

Need extra support?

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

Learn More

Forum Channels

  • © 2023 MESCIUS inc. All Rights Reserved.  ·  1.800.858.2739

    All product and company names herein may be trademarks of their respective owners.