We use cookies to give you the best experience on our website. If you continue to browse, then you agree to our privacy policy and cookie policy. (Last updated on: June 24, 2019).
Unfortunately, activation email could not send to your email. Please try again.
Syncfusion Feedback

EntireRow.Copy function is very slow and not keeping all format

Thread ID:

Created:

Updated:

Platform:

Replies:

143764 Apr 4,2019 09:32 AM UTC Jun 15,2019 01:39 PM UTC ASP.NET Core - EJ 2 7
loading
Tags: XlsIO
Duc Tran
Asked On April 4, 2019 09:32 AM UTC

I have a template sheet to copy to a Report sheet. Clone many data in the template sheet area that including the shapes.
A part of my code:
pasteRangeFrom = tempWorkSheet.Range[1, 1, iTempRows, tempWorkSheet.UsedRange.LastColumn];
pasteRangeTo = reportWorkSheet.Range[iTempRows * (iPageNumber - 1) + 1, 1];
pasteRangeFrom.EntireRow.CopyTo(pasteRangeTo);
pasteRangeFrom.CopyTo(pasteRangeTo, ExcelCopyRangeOptions.CopyValueAndSourceFormatting);

I want to keep the format as in template sheet so I use EntireRow.Copy and use CopyTo withExcelCopyRangeOptions.CopyValueAndSourceFormattingsetting to copy value and replace the values in thecells with formula inside.
My Problem is
- Performance is very slow when using function CopyTo
- Some formats do not keep in thereport sheet. The height of the cell is expanding. (view the details in the attached file).
Sorry for any spell mistakes. English is not my mother tongue.

Attachment: 20190404_e700b870.rar

Abirami Varadharajan [Syncfusion]
Replied On April 6, 2019 06:53 AM UTC

Hi Duc, 
 
Thank you for contacting Syncfusion support. 
 
You have used the IRange.EntireRow to copy the format of the template worksheet. This will copy the data from the first column to maximum number of column in the worksheet for all the rows. So, it will take some more time to complete. We have modified your code snippet to increase the performance of this CopyTo process which will also keeps the source worksheet formatting. Now, it takes less than 2 seconds to complete the copy to operation for the given template. 
 
Code snippet: 
IRange pasteRangeFrom = tempWorkSheet.Range[1, 1, tempWorkSheet.UsedRange.LastRow, tempWorkSheet.UsedRange.LastColumn]; 
IRange pasteRangeTo = reportWorkSheet.Range[reportWorkSheet.UsedRange.LastRow + 1, 1]; 
//pasteRangeFrom.EntireRow.CopyTo(pasteRangeTo, ExcelCopyRangeOptions.None); 
//pasteRangeFrom.EntireColumn.CopyTo(pasteRangeTo, ExcelCopyRangeOptions.None); 
reportWorkSheet.StandardHeight = tempWorkSheet.StandardHeight; 
int startCol = pasteRangeTo.Column; 
for (int col = startCol; col <= reportWorkSheet.Workbook.MaxColumnCount; col++) 
{ 
    reportWorkSheet.SetColumnWidth(startCol, tempWorkSheet.GetColumnWidth(col)); 
    startCol++; 
} 
 
pasteRangeFrom.CopyTo(pasteRangeTo, ExcelCopyRangeOptions.All); 
 
Please let us know if you have any queries. 

Regards, 
Abirami 


Duc Tran
Replied On May 29, 2019 02:14 AM UTC

Hi Abirami!
Thank you for your support!
This is my code snippet after tuning:

 reportWorkSheet.InsertRow(iTempRows * (iPageNumber - 1) + 1, pasteRangeFrom.Rows.Count(), ExcelInsertOptions.FormatAsBefore);
 for (int y = 1; y < pasteRangeFrom.Rows.Count(); y++)
 {
     reportWorkSheet.SetRowHeight(iTempRows * (iPageNumber - 1) + y, pasteRangeFrom.Rows[y-1].RowHeight);
 }
 pasteRangeFrom.EntireRow.CopyTo(pasteRangeTo, ExcelCopyRangeOptions.All);
 pasteRangeFrom.EntireRow.CopyTo(pasteRangeTo, ExcelCopyRangeOptions.None);

It helped me to improve performance but an error raised when operating with the xls extension files.
At this time I'm using Syncfusion version is 16.4450.0.54.
The InertRow function cannot insert many rows. After 21st row it raised the following Error:
System.ArgumentOutOfRangeException: The specified argument is not in the range of valid values.

I tried to update the latest version and at The latest version, the InsertRow function works normally
But the image in the xls file couldn't copy into new Paste Range.
Could you please explain why InsertRow did not work at 16.4450.0.54 version
and How to copy image when you copy and paste to new Paste Range.
View details in the attached file.
Regards,
Duc

Attachment: MyReport_c292306f.rar

Abirami Varadharajan [Syncfusion]
Replied On May 29, 2019 03:52 PM UTC

Hi Duc, 

Issue 
Details 
At this time I'm using Syncfusion version is 16.4450.0.54. 
The InertRow function cannot insert many rows. After 21st row it raised the following Error: 
System.ArgumentOutOfRangeException: The specified argument is not in the range of valid values. 
 
I tried to update the latest version and at The latest version, the InsertRow function works normally 

We are able to reproduce the issue while inserting more than 19 rows at index 21. This issue is reproduced in v16.4 as well as in our latest version 17.1. We will validate and update further details by 31st May 2019. 
But the image in the xls file couldn't copy into new Paste Range. 
Could you please explain why InsertRow did not work at 16.4450.0.54 version 
and How to copy image when you copy and paste to new Paste Range. 

We are able to reproduce the issue and validating it currently. We will update further details by 31st May 2019. 

Please let us know if you have any concerns. 

Regards, 
Abirami 


Duc Tran
Replied On May 30, 2019 02:37 AM UTC

Hi Abinami!
Exception:
System.ArgumentOutOfRangeException: The specified argument is not in the range of valid values. 
The cause is there is conditional formatting at the 21st row in the sheet Template of the excel file.
Regards,
Duc

Abirami Varadharajan [Syncfusion]
Replied On May 30, 2019 04:10 PM UTC

Hi Duc, 

Thank you for sharing your findings. 

As promised earlier, we will update further details regarding reported issues by tomorrow (i.e., 31st May 2019). 

Regards, 
Abirami 


Abirami Varadharajan [Syncfusion]
Replied On June 2, 2019 02:53 PM UTC

Hi Duc, 
 
Please find details below. 
 
System.ArgumentOutOfRangeException: The specified argument is not in the range of valid values.  
  
I tried to update the latest version and at The latest version, the InsertRow function works normally  
  
We have validated the issue. The issue is reproduced while inserting with conditional formatting. We have logged an issue report for “Exception throws while inserting Rows with conditional formatting” and the patch for the fix will be available on June 14, 2019. 
But the image in the xls file couldn't copy into new Paste Range.  
Could you please explain why InsertRow did not work at 16.4450.0.54 version  
and How to copy image when you copy and paste to new Paste Range.  
  
The reported image is not as picture. It’s as group shape. We don’t have a group shape support for binary format(xls file). 
  
  
Regards, 
Abirami 


Abirami Varadharajan [Syncfusion]
Replied On June 15, 2019 01:39 PM UTC

 Hi Duc, 
 
We have fixed an issue Exception throws while inserting Rows with conditional formatting”. The patch can be downloaded from the following locations.      
  
Recommended approach - exe will perform automatic configuration          

Please find the patch setup from below location:                     
  
   
Advanced approach – use only if you have specific needs and can directly replace existing assemblies for your build environment          
Please find the patch assemblies alone from below location:                 
  
  
Assembly Version: 17.1.0.38 
Installation Directions : 
This patch should replace Syncfusion.XlsIO.Portable.dll, Syncfusion.Compression.Portable  under the following folder.          
 
$system drive:\ Files\Syncfusion\Essential Studio\$Version # \precompiledassemblies\$Version#\4.6
Eg : $system drive:\Program Files\Syncfusion\Essential Studio\ 17.1.0.38\precompiledassemblies\17.1.0.38\4.6

To automatically run the Assembly Manager, please check the Run assembly manager checkbox option while installing the patch. If this option is unchecked, the patch will replace the assemblies in precompiled assemblies’ folder only. Then, you will have to manually copy and paste them to the preferred location or you will have to run the Syncfusion Assembly Manager application (available from the Syncfusion Dashboard, installed as a shortcut in the Application menu) to re-install assemblies. 
 

Note:                  
 
You can change how you receive bug fixes by navigating to the following link and updating your preferences.                
https://www.syncfusion.com/support/directtrac/patches                  

Disclaimer : 
Please note that we have created this patch for version 17.1.0.38 specifically to resolve the issues reported in this Forum 144806.               
 

If you have received other patches for the same version for other products, please apply all patches in the order received.               
 

This fix will be included in our future release 2019 Volume 2 service pack 1 which will be rolled out at the end of July 2019. 
 

Regards, 
Abirami 


CONFIRMATION

This post will be permanently deleted. Are you sure you want to continue?

Sorry, An error occured while processing your request. Please try again later.

Please sign in to access our forum

This page will automatically be redirected to the sign-in page in 10 seconds.

Warning Icon You are using an outdated version of Internet Explorer that may not display all features of this and other websites. Upgrade to Internet Explorer 8 or newer for a better experience.Close Icon

Live Chat Icon For mobile
Live Chat Icon