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. (Last updated on: November 16, 2018).
Unfortunately, activation email could not send to your email. Please try again.
Syncfusion Feedback

Error while importing DataTable using worksheet.ImportDataTable

Thread ID:

Created:

Updated:

Platform:

Replies:

140585 Oct 26,2018 10:12 AM UTC Oct 30,2018 11:13 AM UTC ASP.NET Web Forms (Classic) 5
loading
Tags: XlsIO
gaurav singh
Asked On October 26, 2018 10:12 AM UTC

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\n   at Syncfusion.XlsIO.Implementation.FormulaParser.CreateLocalName(Int32 iRefIndex, String strToken, ParseParameters arguments, Dictionary`2 indexes, Int32 i, ExcelParseFormulaOptions options)\r\n   at Syncfusion.XlsIO.Implementation.FormulaParser.CreateNamedRange(String strToken, ParseParameters arguments, Dictionary`2 indexes, Int32 i, ExcelParseFormulaOptions options, Int32 iRefIndex)\r\n   at Syncfusion.XlsIO.Implementation.FormulaParser.ParseIdentifier(String identifier, Dictionary`2 indexes, Int32 i, ExcelParseFormulaOptions options)\r\n   at Syncfusion.XlsIO.Implementation.FormulaParser.CreateNamedRange(String strToken, ParseParameters arguments, Dictionary`2 indexes, Int32 i, ExcelParseFormulaOptions options, Int32 iRefIndex)\r\n   at Syncfusion.XlsIO.Implementation.FormulaParser.ParseIdentifier(String identifier, Dictionary`2 indexes, Int32 i, ExcelParseFormulaOptions options, ParseParameters arguments)\r\n   at Syncfusion.XlsIO.Implementation.FormulaParser.ParseFirstOperand(Priority priority, Dictionary`2 indexes, Int32 i, ExcelParseFormulaOptions& options, ParseParameters arguments)\r\n   at Syncfusion.XlsIO.Implementation.FormulaParser.ParseExpression(Priority priority, Dictionary`2 indexes, Int32 i, ExcelParseFormulaOptions options, ParseParameters arguments)\r\n   at Syncfusion.XlsIO.Implementation.FormulaParser.ParseExpression(Priority priority, Dictionary`2 indexes, Int32 i, ExcelParseFormulaOptions options, ParseParameters arguments)\r\n   at ...

Prakash Kumar D [Syncfusion]
Replied On October 26, 2018 12:50 PM UTC

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
Replied On October 26, 2018 03:28 PM UTC

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 [Syncfusion]
Replied On October 29, 2018 11:35 AM UTC

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. 
 
 
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
Replied On October 30, 2018 08:34 AM UTC

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 [Syncfusion]
Replied On October 30, 2018 11:13 AM UTC

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 


CONFIRMATION

This post will be permanently deleted. Are you sure you want to continue?

Sorry, An error occured while processing your request. Please try again later.

Please sign in to access our forum

or the page will be automatically redirected to sign-in page in 10 seconds.

Warning Icon You are using an outdated version of Internet Explorer that may not display all features of this and other websites. Upgrade to Internet Explorer 8 or newer for a better experience.Close Icon

;