Exception Argument.OutOfRange Performance Issue

[Gurpreet Singh]

Hello everyone,

I have an exception while importing Formula from Excel sheet , Exception occurred while calculating value based on formula.

I have one more Exception System.InvalidCastException while calculating Percentage based on Percentage columns

Thanks in advance.

[Neelakandan Kannan]

Hi Gurpreet,

 

Thank you for your interest in Syncfusion products.

 

We regret to let you know that we could not able to reproduce your reported scenario at our end. And, It is hard to trace why this issue is arising without seeing the sample. So that we need some more details regarding cases in which this exception occurred[ Excel sheet with data]. So that please provide us the simple excel sheet with data that reproduces your scenario. And also please provide us  Stack race details. Here we have provided the simple sample. you can also try with our below sample. It would be easier us to provide better solution as soon as possible.

 

Please let me know if you have any concerns.

 

Regards,

Neelakandan

Attachment: Sample_Import_Demo_80f75e4c.zip

[Gurpreet Singh]

Hello,

Thanks for consideration

I have checked your file,, these are working fine, I want t make it clear

I have a problem at run time calculating value based on formula and display only value in Excel sheet not the formula.

I have used the in built method EnableSheetCalculations for calculating value in the engine based on formula.

When I transfer the CalculatedValue I got some of exceptions listed below:

Argument.OutOfRange Exception

InvalidCast Exception

NullReference Exception

All Exception comes in Syncfusion.dll Package

[Gurpreet Singh]

Hello,

One more thing if i calculate the formula based on the same Row on which row formula exist It's working fine,,

But when we calculate based on same Column on which the formula exists it creates exception

Moreover if we calculate based on different column & row It also creates exception.

I am screwed up ,,, any help???

Thanks

Gurpreet

[Neelakandan Kannan]

Hi Gurpreet,

 

Thank you for your update.

 

We regret to let you know that we were not able to reproduce the mentioned issue at my end. And, It is hard to trace why this issue is arising without seeing the sample. We have searched our support database for any such similar issues, and found no similar issues. Please provide us following details,

 

1.Essential studio version that you are using.

2.Replication procedure to reproduce your scenario. In our previous update we have provided the sample that illustrates importing of data from excel. In your previous update you mentioned that calculating the values and transferring CalculatedValue. In this case we are not clear with your scenario. So please provide us simple sample with replication procedure. It will be easier for us to have a closer look into the problem and resolve it. Appreciate additional information, if any.

 

Please let me know if you have any concerns.

 

Regards,

Neelakandan

[Gurpreet Singh]

Hello,

It's hard to reproduce a sample file,, But I tried.

Attachment file is a sample file & below is the Snippet for generating value from formula.

Moreover It works fine for row calculations but Throws exception on column calculations

Exception : (Argument.outofrange,,Formatcast,, NullReference,, & so on..)

worksheet.EnableSheetCalculations();
            for(var i = 0; i < worksheet.Rows.Length; i++)
            {
                var cell = worksheet[i+1, 2];
                var formula = cell.Formula;
                if (formula != null)
                {
                   cell.Value2 = cell.CalculatedValue;
                }
            }
worksheet.DisableSheetCalculations();
        }

Attachment: Sample_7ce15886.zip

[Neelakandan Kannan]

Hi Gurpreet,

 

Thank you for your update and cooperation.

 

We regret to let you know that we still could not able to reproduce your reported scenario at our end. We have modified the sample as per your code that you have provided in your previous update. The reported scenario cannot be reproduced at our end. The formula result from Excel sheet has been successfully imported to Grid. We have attached sample video file and sample. Please find the attached Video and sample file. Meanwhile you can use QueryImportExportCellInfo to customize your scenario that only importing the result of the formula in Excel sheet.

 

Code Snippet:

Syncfusion.GridExcelConverter.GridExcelConverterControl gecc = new Syncfusion.GridExcelConverter.GridExcelConverterControl();

gecc.QueryImportExportCellInfo += new Syncfusion.GridExcelConverter.GridImportExportCellInfoEventHandler(gecc_QueryImportExportCellInfo);

void gecc_QueryImportExportCellInfo(object sender, Syncfusion.GridExcelConverter.GridImportExportCellInfoEventArgs e)

{

if (e.ExcelCell.Formula != null)

{

e.GridCell.Text = e.ExcelCell.CalculatedValue;

e.Handled = true;

}

}

 

Note:

If you have still an  issue, please modify the sample to reproduce your scenario. It would be helpful us to provide better solution as soon as possible.

 

Please let me know if you have any concerns.

 

Regards,

Neelakandan

Attachment: Video_and_Sample_c46f1bc9.zip

[Gurpreet Singh]

Hello,

Yeah this code also have the same issue.

Let me tell You If You Enable your Common Language Run time Exceptions in your studio,, You will get that exceptions.

It is not an error, It decrease the performance the value is coming there,,but not performance.

Performance decrease when the calculation is column based.

[Gurpreet Singh]

Hello,,

If you calculation mostly on column based like summary stuff & you have cells in thousand,,

You will be screwed up by the performance issue,, Takes a lot of time to load.

Thanks 

Gurpreet

[Gurpreet Singh]

Moreover Thanks for the consideration.

[Neelakandan Kannan]

Hi Gurpreet,

 

Thank you for your update and cooperation.

 

We analyzed all your scenarios at our end. And we enabled Common Language Runtime exceptions and the exception has not been occurred at our end. In our previous update we requested you to provide simple sample and also details on whether you are Importing excel sheet or writing data into excel sheet. So that please provide us details on below,

 

1.       Essential studio version you are using.

2.       whether you are trying to import the excel sheet to grid or XlsIo engine to write data to excel sheet

3.       Simple sample to reproduce your scenario(sample and Excel sheet(sample data)).

4.       If possible please modify the sample that provided by us.

5.       Simple replication procedure to reproduce.

 

Since we still could not able to reproduce your reported scenario, we requested above details. It would be more helpful us to provide better solution as soon as possible. Awaiting for your response.

 

Please let me know if you have any concern.

 

Regards,

Neelakandan

[Gurpreet Singh]

Hello Neelakandan,

Thanks for updating.

Attach file is the zip file of the sample project with sample Excel file.

If you run this Project by enabling common language run time exceptions You will see 2 Exceptions:

Argument.OutOfRange Exception

System.FormatException

It reduce the performance of my application .

Thanks

Gurpreet

Attachment: SyncfusionException_d2b6adfb.zip

[Neelakandan Kannan]

Hi Gurpreet,

 

Thank you for your cooperation.

 

We analyzed all your reported scenario and sample at our end. The reported issue with “Argument exception” has been fixed in our latest version[12.3.0.36]. For this, Please create a DT incident for your query so that we can update the patch for this issue.

 

You can create the DT incident from the following link.

<http://www.syncfusion.com/account/dashboard>

 

In the interests of maintaining confidentiality, we have a policy of not sharing patches or otherwise proprietary customer specific information in the public Forum.

 

Please let me know if you have any concerns.

 

Regards,

Neelakandan