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 <FONT size=2>


    fpSpread1.ActiveSheet.LoadTextFile(openCSV.FileName, </FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>false</FONT></FONT><FONT size=2>, FarPoint.Win.Spread.Model.</FONT><FONT color=#2b91af size=2><FONT color=#2b91af size=2>IncludeHeaders</FONT></FONT><FONT size=2>.None, </FONT><FONT color=#a31515 size=2><FONT color=#a31515 size=2>"\n"</FONT></FONT><FONT size=2>, </FONT><FONT color=#a31515 size=2><FONT color=#a31515 size=2>";"</FONT></FONT><FONT size=2>, </FONT><FONT color=#a31515 size=2><FONT color=#a31515 size=2>""</FONT></FONT><FONT size=2>);</FONT>


    <FONT size=2>it also goes very slow!</FONT>


    <FONT size=2>Is there a solution? It is only a file of 16kb!

    </FONT>
    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

    <BLOCKQUOTE><div><img src="/wp-content/uploads/CS_images/icon-quote.gif"> scotts:</div><div>

    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.


    </div></BLOCKQUOTE>


    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:

    <FONT color=#0000ff size=2><FONT color=#0000ff size=2>

    private</FONT></FONT><FONT size=2> </FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>void</FONT></FONT><FONT size=2> radButtonElement11_Click(</FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>object</FONT></FONT><FONT size=2> sender, </FONT><FONT color=#2b91af size=2><FONT color=#2b91af size=2>EventArgs</FONT></FONT><FONT size=2> e)


    {


    </FONT><FONT color=#2b91af size=2><FONT color=#2b91af size=2>OpenFileDialog</FONT></FONT><FONT size=2> openExcel = </FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>new</FONT></FONT><FONT size=2> </FONT><FONT color=#2b91af size=2><FONT color=#2b91af size=2>OpenFileDialog</FONT></FONT><FONT size=2>();


    openExcel.InitialDirectory = </FONT><FONT color=#a31515 size=2><FONT color=#a31515 size=2>"c:\\"</FONT></FONT><FONT size=2>;


    openExcel.Filter = </FONT><FONT color=#a31515 size=2><FONT color=#a31515 size=2>"Excel 2003 bestanden (*.xls)|*.xls|Excel 2007 bestanden (*.xlsx)|*.xlsx"</FONT></FONT><FONT size=2>;


    openExcel.FilterIndex = 2;


    openExcel.RestoreDirectory = </FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>true</FONT></FONT><FONT size=2>;


    </FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>if</FONT></FONT><FONT size=2> (openExcel.ShowDialog() == </FONT><FONT color=#2b91af size=2><FONT color=#2b91af size=2>DialogResult</FONT></FONT><FONT size=2>.OK)


    {


    </FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>string</FONT></FONT><FONT size=2> strBackupSheetName = fpSpread1.ActiveSheet.SheetName.ToString();


    </FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>if</FONT></FONT><FONT size=2> (fpSpread1.ActiveSheet.OpenExcel(openExcel.FileName, 0) == </FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>false</FONT></FONT><FONT size=2>)


    {


    </FONT><FONT color=#2b91af size=2><FONT color=#2b91af size=2>DialogResult</FONT></FONT><FONT size=2> ds;


    ds = Telerik.WinControls.</FONT><FONT color=#2b91af size=2><FONT color=#2b91af size=2>RadMessageBox</FONT></FONT><FONT size=2>.Show(</FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>this</FONT></FONT><FONT size=2>, </FONT><FONT color=#2b91af size=2><FONT color=#2b91af size=2>Environment</FONT></FONT><FONT size=2>.NewLine + </FONT><FONT color=#a31515 size=2><FONT color=#a31515 size=2>"Het geselecteerde Excel-bestand is niet geldig!"</FONT></FONT><FONT size=2> + </FONT><FONT color=#2b91af size=2><FONT color=#2b91af size=2>Environment</FONT></FONT><FONT size=2>.NewLine, </FONT><FONT color=#a31515 size=2><FONT color=#a31515 size=2>"Fout tijdens openen"</FONT></FONT><FONT size=2>, </FONT><FONT color=#2b91af size=2><FONT color=#2b91af size=2>MessageBoxButtons</FONT></FONT><FONT size=2>.OK, Telerik.WinControls.</FONT><FONT color=#2b91af size=2><FONT color=#2b91af size=2>RadMessageIcon</FONT></FONT><FONT size=2>.Exclamation, </FONT><FONT color=#2b91af size=2><FONT color=#2b91af size=2>MessageBoxDefaultButton</FONT></FONT><FONT size=2>.Button1, </FONT><FONT color=#2b91af size=2><FONT color=#2b91af size=2>RightToLeft</FONT></FONT><FONT size=2>.No);


    }


    </FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>else

    </FONT></FONT><FONT size=2>

    {


    FarPoint.Win.Spread.CellType.</FONT><FONT color=#2b91af size=2><FONT color=#2b91af size=2>EnhancedColumnHeaderRenderer</FONT></FONT><FONT size=2> ecr = </FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>new</FONT></FONT><FONT size=2> FarPoint.Win.Spread.CellType.</FONT><FONT color=#2b91af size=2><FONT color=#2b91af size=2>EnhancedColumnHeaderRenderer</FONT></FONT><FONT size=2>();


    ecr.WordWrap = </FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>false</FONT></FONT><FONT size=2>;


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


    fpSpread1.ActiveSheet.SheetName = strBackupSheetName;


    UpdateStatusLabel(</FONT><FONT color=#a31515 size=2><FONT color=#a31515 size=2>"Het openen van het bestand is correct uitgevoerd."</FONT></FONT><FONT size=2>);


    }


    }


    }

    </FONT>
  • 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)

    <FONT size=2>

    fpSpread1.ActiveSheet.Columns[activePosition.Column].BackColor = </FONT><FONT color=#2b91af size=2><FONT color=#2b91af size=2>Color</FONT></FONT><FONT size=2>.DarkBlue;


    </FONT><FONT size=2>fpSpread1.ActiveSheet.Columns[activePosition.Column].Width = fpSpread1.ActiveSheet.GetPreferredColumnWidth(activePosition.Column, </FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>false</FONT></FONT><FONT size=2>);

    </FONT>

  • 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