Copy from Excel Slow

Posted by: geert on 8 September 2017, 1:51 pm EST

  • Posted 8 September 2017, 1:51 pm EST

    Hello,


    I'm testing with the Windows Spread control the import functionality from Excel.


     When I copy same values from Excel and paste them into the spread control, it takes a lot of time.


    The Excel file is in attachment. When I open it with the code openExcel(..,..) it goes very fast but then I cannot change the backgroundcolor etc...


     When I save the Excel as csv, and use the code


    fpSpread1.ActiveSheet.LoadTextFile(openCSV.FileName, false, FarPoint.Win.Spread.Model.IncludeHeaders.None, "\n", ";", "");


    it also goes very slow!


    Is there a solution? It is only a file of 16kb!


    2009/04/leerlingencvo_gids_200901.zip
  • Replied 8 September 2017, 1:51 pm EST

    Geert,


    To expound on Suresh's explanation, the default CellType of Spread is the GeneralCellType, which will format numbers and dates into their appropriate types, as Excel does in their default cell. This action is taking a long time to process because of the amount of these cells that are needing to be formatted at once. If you were to change the CellType of these cells to NumberCellType, DateTimeCellType, or TextCellType, then the copy and paste would be zippy as you expect.

  • Replied 8 September 2017, 1:51 pm EST

    Geert,

    The exhibited copy/paste behavior is intended behavior as all data is to be formatted correctly first, before pasting the same into the underlying data model.We have been able to change the background color as well. Therefore, I request you to please send across a sample projct which replicates the said behavior at our side.

    Thanks

  • Replied 8 September 2017, 1:51 pm EST

    scotts:

    Geert,


    To expound on Suresh's explanation, the default CellType of Spread is the GeneralCellType, which will format numbers and dates into their appropriate types, as Excel does in their default cell. This action is taking a long time to process because of the amount of these cells that are needing to be formatted at once. If you were to change the CellType of these cells to NumberCellType, DateTimeCellType, or TextCellType, then the copy and paste would be zippy as you expect.



    What you say Scotts is not an option. I have to write an application where the user can gently import an Excel or CSV Sheet. This is the code that I use for opening an Excel File (.xlsx). This code works very fast an the simple copy paste from Excel doesn't. Is there any way that I can accelerate the process?


    CODE:


    private void radButtonElement11_Click(object sender, EventArgs e)


    {


    OpenFileDialog openExcel = new OpenFileDialog();


    openExcel.InitialDirectory = "c:\\";


    openExcel.Filter = "Excel 2003 bestanden (*.xls)|*.xls|Excel 2007 bestanden (*.xlsx)|*.xlsx";


    openExcel.FilterIndex = 2;


    openExcel.RestoreDirectory = true;


    if (openExcel.ShowDialog() == DialogResult.OK)


    {


    string strBackupSheetName = fpSpread1.ActiveSheet.SheetName.ToString();


    if (fpSpread1.ActiveSheet.OpenExcel(openExcel.FileName, 0) == false)


    {


    DialogResult ds;


    ds = Telerik.WinControls.RadMessageBox.Show(this, Environment.NewLine + "Het geselecteerde Excel-bestand is niet geldig!" + Environment.NewLine, "Fout tijdens openen", MessageBoxButtons.OK, Telerik.WinControls.RadMessageIcon.Exclamation, MessageBoxDefaultButton.Button1, RightToLeft.No);


    }


    else


    {


    FarPoint.Win.Spread.CellType.EnhancedColumnHeaderRenderer ecr = new FarPoint.Win.Spread.CellType.EnhancedColumnHeaderRenderer();


    ecr.WordWrap = false;


    fpSpread1.ActiveSheet.ColumnHeader.DefaultStyle.Renderer = ecr;


    fpSpread1.ActiveSheet.SheetName = strBackupSheetName;


    UpdateStatusLabel("Het openen van het bestand is correct uitgevoerd.");


    }


    }


    }

  • Replied 8 September 2017, 1:51 pm EST

    another problem was that when i want to change the backcolor of an imported Excel Sheets, the code is not working.
    The cells of the colomn below the excel sheet do work properly, but the cells of the columns don't... (Printscreen attached)


    fpSpread1.ActiveSheet.Columns[activePosition.Column].BackColor = Color.DarkBlue;


    fpSpread1.ActiveSheet.Columns[activePosition.Column].Width = fpSpread1.ActiveSheet.GetPreferredColumnWidth(activePosition.Column, false);



  • Replied 8 September 2017, 1:51 pm EST

    I found the problem. When I save the Excel 2007 file as Excel 2003, with extention .xls the import is very fast.


    Is that a know issue? 

  • Replied 8 September 2017, 1:51 pm EST

    Hello,


    This is correct behavior. When you load an Excel file, the BackColor is being set for individual cells. So, setting a BackColor for an entire column will be overwritten by the setting made at the cell level. You will need to change your code to set the BackColor for the cell instead of the column.


    Loading an Excel 2003 file may be a little faster depending on what is set in the Excel file. Parsing the binary of the BIFF8 is faster and cleaner than the new Excel XML format. There should not be too much of a difference in load times. If there is, could you send us your Excel file to test?

Need extra support?

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

Learn More

Forum Channels