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

[Duc Tran]

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]

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]

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]

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]

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]

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]

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]

 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:                       

http://syncfusion.com/Installs/support/patch/17.1.0.38/1144327/F143764/SyncfusionPatch_17.1.0.38_1144327_6152019124040675_F143764.exe 

   

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:                 

http://syncfusion.com/Installs/support/patch/17.1.0.38/1144327/F143764/SyncfusionPatch_17.1.0.38_1144327_6152019124040675_F143764.zip 

  

Nuget Link: http://syncfusion.com/Installs/support/patch/17.1.0.38/1144327/F143764/SyncfusionNuget_17.1.0.38_1144327_6152019124040675_F143764.zip 

  

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