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. Image for the cookie policy date

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

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

7 Replies

AV Abirami Varadharajan Syncfusion Team 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 



DT Duc Tran 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


AV Abirami Varadharajan Syncfusion Team 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 



DT Duc Tran 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


AV Abirami Varadharajan Syncfusion Team 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 



AV Abirami Varadharajan Syncfusion Team 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 



AV Abirami Varadharajan Syncfusion Team 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 


Loader.
Live Chat Icon For mobile
Up arrow icon