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
  1. Home
  2. Forum
  3. ASP.NET Core - EJ 2
  4. I have a issue using the method 'ImportData' from XlsIO

I have a issue using the method 'ImportData' from XlsIO

Hi guys,

I have 2 issues:

1) 

     I Have the following result from my database:
                                      
     When the first row have a null values, the method takes all the values as if they were text, and export the excel in this way:
                               
     But i need this columns on number format, so i have to open the document and modify the format of the cells.
     Is there any way to export it in numerical format even though the first row have null values?

2)
     I Have the following result from my database:
               
     I have a null row in the middle of the result, so when i trying to export this data to excel, i suppose the method try to convert null columns( 5 to 9) to number and i get      the follow error:

     Is there any way to export it that result to excel or i need to always parse the null values to '0'?

Thanks for your help,

Best regards.

15 Replies
KK Konduru Keerthi Konduru Ravichandra Raju Syncfusion Team October 11, 2019 12:51 AM UTC

Hi Kevin, 

Greeting from Syncfusion. 

Issue 1 
When the first row have a null values, the method takes all the values as if they were text, and export the excel in this way: 

But i need this columns on number format, so i have to open the document and modify the format of the cells. 
     Is there any way to export it in numerical format even though the first row have null values? 
We are able to reproduce the issue and validating it currently. We will provide further details on 14th October 2019. 

Issue 2 
I have a null row in the middle of the result, so when i trying to export this data to excel, i suppose the method try to convert null columns( 5 to 9) to number and i get      the follow error: 

 Is there any way to export it that result to excel or i need to always parse the null values to '0'? 
We are unable to reproduce the reported “Object cannot be cast from DBNull to other types” exception, but we are facing “Specified cast is not valid” exception.  


We have prepared simple sample to cover both the issues and the sample can be downloaded from the below link.  

Kindly look into the sample and let us know if your scenario is understood properly. If not, we request you to modify the sample to reproduce the issue and get back to us with issue reproducing sample. This will help us to investigate further and provide prompt solution at the earliest. 

Regards, 
Keerthi. 


KK Konduru Keerthi Konduru Ravichandra Raju Syncfusion Team October 14, 2019 12:32 PM UTC

Hi Kevin, 

We have confirmed the reported issues “Exception is thrown while import data from dynamic objects” and “Support for adding preserve types option in ImportData() method”. The patch for both the issues will be provided on 21st October 2019. 

Regards, 
Keerthi.

KE Kevin October 14, 2019 08:29 PM UTC

Hi Keerthi,

Thanks for your response, with your example i can't reproduce exactly the second error. I'm gonna wait for the patch. 

Really thanks for ur help!.


Regards.

KK Konduru Keerthi Konduru Ravichandra Raju Syncfusion Team October 15, 2019 12:26 PM UTC

Hi Kevin, 

Thanks for the update. As promised earlier, we will provide the patch for both issues on 21st October 2019. 

Regards, 
Keerthi.

KK Konduru Keerthi Konduru Ravichandra Raju Syncfusion Team October 22, 2019 01:01 AM UTC

Hi Kevin, 

Thanks for the patience. 

We have fixed both the issues reported in this forum. We request you to use the following code snippet along with the custom assemblies to fix the issues.  

Code snippet: 

using (ExcelEngine excelEngine = new ExcelEngine()) 
  { 
       IApplication application = excelEngine.Excel; 
       application.DefaultVersion = ExcelVersion.Excel2013; 
       IWorkbook workbook = application.Workbooks.Create(1); 
       IWorksheet worksheet = workbook.Worksheets[0]; 
       ExcelImportDataOptions importDataOptions = new ExcelImportDataOptions(); 
       importDataOptions.FirstRow = 2; 
       importDataOptions.FirstColumn = 1; 
       importDataOptions.IncludeHeader = false; 
       importDataOptions.PreserveTypes = false; 
 
       //Import the data to worksheet 
       IList<object> reports = GetSalesReports1(); 
       worksheet.ImportData(reports, importDataOptions); 
      workbook.SaveAs("ImportFromDT.xlsx"); 
              
  } 


We request you to try these custom assemblies and let you know if the issues are resolved. 

This fix will be included in our upcoming release version 17.3  SP -1 which will be rolled out by end of October 2019. 
            
Regards,            
Keerthi. 


KE Kevin October 23, 2019 08:44 PM UTC

Hi Keerthi,

Thanks for help me to resolve this issues.

I'm testing the custom assemblies and the 2nd issue is totally resolved, but i'm still getting problems with the first Issue:



That's the SQL response:

And that's the code im use to export the data:


I need to do something more?

Thanks for your help,

Best regards.




AV Abirami Varadharajan Syncfusion Team October 24, 2019 01:36 PM UTC

Hi Kevin, 
 
We regret for the inconvenience caused. We have generated new custom assemblies which can be downloaded from the following link. 
 
 
We request you to try these assemblies to check if the reported issues are resolved. If not, please share us the code snippet of how you are importing data, which will be helpful for to investigate and provide prompt solution at the earliest. 
 
Regards, 
Abirami. 


KE Kevin November 6, 2019 05:41 PM UTC

Hi Keerthi,

Sorry for my late answer, i was on vacation, im testing now the new assemblies and the 2 issues is totally resolved!

I´m waiting for the patch!

Really thanks for your help.

Best regards.

KK Konduru Keerthi Konduru Ravichandra Raju Syncfusion Team November 7, 2019 04:43 PM UTC

Hi Kevin, 
  
Thanks for you update and we are glad that the reported issues are resolved at your end. You will provide the patch for release version 17.3.0.26 tomorrow (8th November 2019). 
  
The fix will be included in our release version 17.4 which will be rolled out by end of December 2019. 
  
Regards, 
Keerthi. 


KK Konduru Keerthi Konduru Ravichandra Raju Syncfusion Team November 8, 2019 11:02 AM UTC

Hi Kevin, 

The patch for release version v17.3.0.26 with fix to resolve the reported issues can be downloaded from the following link. 

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.3.0.26 
             
Installation Directions :  
This patch should replace Syncfusion.XlsIO.Portable.dll under the following folder.                    
$system drive:\ Files\Syncfusion\Essential Studio\$Version # \precompiledassemblies\$Version#\4.6 
Eg : $system drive:\Program Files\Syncfusion\Essential Studio\17.3.0.26\precompiledassemblies\17.3.0.26\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.                         

Disclaimer :  
Please note that we have created this patch for version 17.3.0.26 specifically to resolve the issues reported in forum 148190.                         

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

The fix for this issue will be included in our release version 17.4 which will be rolled out end of December 2019. 

Regards, 
Keerthi. 


KE Kevin February 4, 2020 03:06 PM UTC

Hi Guys,

I have downloaded the last version of XlsIO from nuget (Syncfusion.XlsIO.Net.Core 17.4.0.46) and i have the 2 issues again.

For the last reply you guys tell me "The fix for this issue will be included in our release version 17.4 which will be rolled out end of December 2019.", but i'm testing this patch and the fix is not included, so i'm going to use the patch on this link:


Please let me know when the fix was included in some patch, so i can use the last version of XlsIO.

Really thanks for your time.

Best regards,



KK Konduru Keerthi Konduru Ravichandra Raju Syncfusion Team February 6, 2020 12:47 AM UTC

Hi Kevin, 

We deeply regret for the inconvenience caused. 

We have missed out including the fix in our release version 17.4 as promised. We have generated the patch with fix in the version v17.4.0.46 and the same can be downloaded from the following link. 

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.4.0.46 

Installation Directions :        
This patch should replace the files “Syncfusion.XlsIORenderer.Portable.dll” under the following folder.       
$system drive:\ Files\Syncfusion\Essential Studio\$Version # \precompiledassemblies\$Version#\.NETStandard 2.0        
Eg : $system drive:\Program Files\Syncfusion\Essential Studio\17.4.0.46\precompiledassemblies\17.4.0.46\.NETStandard 2.0  
       
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.        
    
Disclaimer :   
Please note that we have created this patch for version 17.4.0.46 specifically to resolve the issues reported in forum 148190.      
              
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 upcoming weekly NuGet release which will be available by end of next week.  

Regards,     
Keerthi. 


KE Kevin February 6, 2020 05:01 PM UTC

Hi Keerthi,

Really thanks for your help, i will wait for the next patch.

Thanks for your excellent job.

Best Regards,

Kevin.

KK Konduru Keerthi Konduru Ravichandra Raju Syncfusion Team February 7, 2020 04:46 AM UTC

Hi Kevin, 

Thanks for the update. 

We are glad that the provided patch helped you. We will let you know once the upcoming weekly NuGet is published. 

Regards, 
Keerthi. 


KK Konduru Keerthi Konduru Ravichandra Raju Syncfusion Team February 12, 2020 08:40 AM UTC

Hi Kevin, 

Thanks for your patience. 

We are glad to inform you that the fix is included in our latest weekly NuGet release version v17.4.0.49 published on 11th February 2020. The XlsIO package can be downloaded from following link. 


Regards, 
Keerthi. 


Loader.
Up arrow icon