WorkSheet to DataTable - not able to preserve the actual datatype when adding a different value (string) on a column (with date format)

[Souvik Bhanja]

I have created an export through Syncfusion where I have explicitly added Date format on one of the columns.

It is getting reflected on the excel file.

 Now when the user is adding a random string on any of the cell in that column and when I'm converting the worksheet to datatable, it is changing the type on ALL the datarows of that column to string. (If there are no string values, it is preserving the Date Format).

On earlier version this was throwing an exception 

var workSheetAsDataTable = workSheet.ExportDataTable(sheet.UsedRange, ExcelExportDataTableOptions.ColumnNames | ExcelExportDataTableOptions.DetectColumnTypes)

But it is fixed on the newer versions with this enum added “DetectMixedValueType”.

Even though it is not throwing exception, the issue is that it is not preserving the original date format if user enters any string (which is not date) on any cell of the same column (where the Date format is added).

I want to keep the datatype as DateTime format even if the user enters string on any cell of that column. How can I achieve it? 

[Konduru Keerthi Konduru Ravichandra Raju]

Hi Souvik, 

Greetings from Syncfusion. 

We are able to reproduce the reported issue and validating it currently. We will share the validation details in two business days, on August 31^st,2020. 

Regards, 

Keerthi. 

[Konduru Keerthi Konduru Ravichandra Raju]

Hi Souvik, 

We deeply regret for the delay. 

We are facing complexity in finding the root cause of the issue. We will share the validation details on September 1^st,2020. 

Regards, 

Keerthi. 

[Konduru Keerthi Konduru Ravichandra Raju]

Hi Souvik, 

We appreciate your patience. 

We have confirmed the issue with Mixed data type values are exported as text while exporting worksheet to data table and logged a defect report. We will provide the patch for this issue on September 15^th, 2020, in the version 18.2.0.44 of Syncfusion XlsIO. 

Kindly let us know if your working version is different. So that we will provide the patch in that version.  

You can track the status of defect report through following feedback link. 

Track Status: https://www.syncfusion.com/feedback/17481/mixed-data-type-values-are-exported-as-text-while-exporting-worksheet-to-data  

Regards, 

Keerthi. 

[Konduru Keerthi Konduru Ravichandra Raju]

Hi Souvik, 

 

Thanks for your patience. 

 

We have fixed the issue Mixed data type values are exported as text while exporting worksheet to data table and generated patch in the version v18.2.0.44. The patch with fix to resolve the reported issue can be downloaded from the following link.  

 

Feedback Link: https://www.syncfusion.com/feedback/17481/mixed-data-type-values-are-exported-as-text-while-exporting-worksheet-to-data  

 

Recommended approach - exe will perform automatic configuration       

   

Please find the patch setup from below location:       

http://syncfusion.com/Installs/support/patch/18.2.0.44/1280278/F157299/SyncfusionPatch_18.2.0.44_1280278_9152020092346424_F157299.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/18.2.0.44/1280278/F157299/SyncfusionPatch_18.2.0.44_1280278_9152020092346424_F157299.zip  

NuGet : http://syncfusion.com/Installs/support/patch/18.2.0.44/1280278/F157299/SyncfusionNuget_18.2.0.44_1280278_9152020092346424_F157299.zip  

     

Assembly Version: 18.2.0.44 

Installation Directions :        

This patch should replace the files “Syncfusion.XlsIO.Base.dll” under the following folder.       

$system drive:\ Files\Syncfusion\Essential Studio\$Version # \precompiledassemblies\$Version#\4.6       

Eg : $system drive:\Program Files\Syncfusion\Essential Studio\18.2.0.44\precompiledassemblies\18.2.0.44\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 18.2.0.44 specifically to resolve the issues reported in forum 157299.      

              

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 release version 18.3 which will be rolled out by end of September 2020.   

Regards,     

Keerthi.