Error while importing DataTable using worksheet.ImportDataTable

[gaurav singh]

Hello

I got an error when importing DataTable using ImortDataTable. I know what causes this error, Actually one of my data row containing "="at the beginning of the string. And we are importing DataTable like "worksheet.ImportDataTable(dtExport, True, 1, 1, False)"( Making preserve type "false") because we wanted that data type of column decided based on value because we are applying date time format and we are storing the date in a varchar field in a database. If we change preserve type false to true, It is working fine. But the date format is not applied. 

So is there any way to handle this situation?

Here is the detail of Exception.

"Message":"An error has occurred.","ExceptionMessage":"This is not a valid name. Name should not be same as the cell name.","ExceptionType":"System.ArgumentException","StackTrace":"   at Syncfusion.XlsIO.Implementation.Collections.WorkbookNamesCollection.Add(String name)\r   at Syncfusion.XlsIO.Implementation.FormulaParser.CreateLocalName(Int32 iRefIndex, String strToken, ParseParameters arguments, Dictionary`2 indexes, Int32 i, ExcelParseFormulaOptions options)\r   at Syncfusion.XlsIO.Implementation.FormulaParser.CreateNamedRange(String strToken, ParseParameters arguments, Dictionary`2 indexes, Int32 i, ExcelParseFormulaOptions options, Int32 iRefIndex)\r   at Syncfusion.XlsIO.Implementation.FormulaParser.ParseIdentifier(String identifier, Dictionary`2 indexes, Int32 i, ExcelParseFormulaOptions options)\r   at Syncfusion.XlsIO.Implementation.FormulaParser.CreateNamedRange(String strToken, ParseParameters arguments, Dictionary`2 indexes, Int32 i, ExcelParseFormulaOptions options, Int32 iRefIndex)\r   at Syncfusion.XlsIO.Implementation.FormulaParser.ParseIdentifier(String identifier, Dictionary`2 indexes, Int32 i, ExcelParseFormulaOptions options, ParseParameters arguments)\r   at Syncfusion.XlsIO.Implementation.FormulaParser.ParseFirstOperand(Priority priority, Dictionary`2 indexes, Int32 i, ExcelParseFormulaOptions& options, ParseParameters arguments)\r   at Syncfusion.XlsIO.Implementation.FormulaParser.ParseExpression(Priority priority, Dictionary`2 indexes, Int32 i, ExcelParseFormulaOptions options, ParseParameters arguments)\r   at Syncfusion.XlsIO.Implementation.FormulaParser.ParseExpression(Priority priority, Dictionary`2 indexes, Int32 i, ExcelParseFormulaOptions options, ParseParameters arguments)\r   at ...

[Prakash Kumar D]

Hi Gaurav, 

 

Thank you for contacting Syncfusion support. 

 

We can reproduce the reported exception, when the data row contains a string in R1C1 notation like ( ”=R5C5” ). Kindly confirm us whether this your scenario. If not, kindly share us the string that causes exception which will be helpful for us to provide you the prompt solution at the earliest. 

 

Regards, 

Prakash Kumar 

[gaurav singh]

Hi

Thanks for your relpy. Here is full string

"=10-4-18: Will follow up. AW 7-23-18: This has been " that cause exception.

[Prakash Kumar D]

Hi Gaurav, 

 

We have tried to reproduce the reported issue with the given string value, but it works properly. We have shared the sample that we tried to reproduce the issue at our end which can be downloaded from the following link. 

 

Sample link : http://www.syncfusion.com/downloads/support/forum/140585/ze/XlsIOSample-462989765  

 

Kindly modify the above sample to reproduce the issue as well as share the generated output document by making the preserve types as true in the IWorksheet.ImportDataTable(). This will be helpful for us to analyze further on this and provide you the solution at earliest. 

 

Regards, 

Prakash Kumar 

[gaurav singh]

Hi,

Thanks for your reply.

Actually, we are saving DateTime in a varchar field in our database. And that datetime column consider as string in datatable. So we are importing data table by setting preserve type to false (in ImportDataTable method) so that data type is determined based on the value in datatable, So we are able to apply format on datetime and the numeric field. If we change preserve type false to true then export is working fine but  date time format is not applied. 

I also modified the sample project given by you. Please have a look.

Thanks 

Attachment: XlsIOSample_f71ab2a1.zip

[Prakash Kumar D]

Hi Gaurav, 

 

Thank you for sharing the sample. 

 

On further analysis, we have found that the given string is considered as formula (when the preserve type is false). Since the formula is an invalid which leads to an exception. The given formula throws an error in Microsoft Excel too and so XlsIO does the same. This is not an issue in XlsIO. 

 

To resolve this issue, we suggest to remove “=” at the beginning in string or surround the string with double quotes to consider the value as string. 

 

Example: "\"=10-4-18:AW 7-23-18: \"" 

 

Regards, 

Prakash Kumar