Getting error when exporting DataTable to excel using ImportDataTable method of IWorksheet

[gaurav singh]

I getting error "Incomplete string, missing "; String started  at position 1. Formula: "Pharmaceutical;medical Devices / Diagnostic Inst, Position: 49 Stack trace"

   at Syncfusion.XlsIO.Implementation.FormulaTokenizer.RaiseException(String msg, Exception ex)

   at Syncfusion.XlsIO.Implementation.FormulaTokenizer.ParseString(Boolean InQuote)

   at Syncfusion.XlsIO.Implementation.FormulaTokenizer.NextToken()

   at Syncfusion.XlsIO.Implementation.FormulaParser.Parse(String formula, Hashtable indexes, Int32 i, ExcelParseFormulaOptions options, ParseParameters arguments)

   at Syncfusion.XlsIO.Implementation.FormulaUtil.ParseString(String strFormula, IWorksheet sheet, Hashtable hashWorksheetNames, Int32 iCellRow, Int32 iCellColumn, Boolean bR1C1)

   at Syncfusion.XlsIO.Implementation.RangeImpl.SetFormula(String value, Hashtable hashWorksheetNames, Boolean bR1C1)

   at Syncfusion.XlsIO.Implementation.RangeImpl.SetFormula(String value)

   at Syncfusion.XlsIO.Implementation.RangeImpl.OnValueChanged(String old, String value)

   at Syncfusion.XlsIO.Implementation.RangeImpl.set_Value(String value)

   at Syncfusion.XlsIO.Implementation.RangeImpl.SetSingleCellValue2(Object value)

   at Syncfusion.XlsIO.Implementation.RangeImpl.set_Value2(Object value)

   at Syncfusion.XlsIO.Implementation.WorksheetImpl.ImportDataTableWithoutCheck(DataTable dataTable, Int32 firstRow, Int32 firstColumn, Int32 maxRows, Int32 maxColumns, DataColumn[] arrColumns)

   at Syncfusion.XlsIO.Implementation.WorksheetImpl.ImportDataTable(DataTable dataTable, Boolean isFieldNameShown, Int32 firstRow, Int32 firstColumn, Int32 maxRows, Int32 maxColumns, DataColumn[] arrColumns, Boolean bPreserveTypes)

   at Syncfusion.XlsIO.Implementation.WorksheetImpl.ImportDataTable(DataTable dataTable, Boolean isFieldNameShown, Int32 firstRow, Int32 firstColumn, Int32 maxRows, Int32 maxColumns)

   at Syncfusion.XlsIO.Implementation.WorksheetImpl.ImportDataTable(DataTable dataTable, Boolean isFieldNameShown, Int32 firstRow, Int32 firstColumn)

   at LRCDataController.ExportExcel() in D:\gaurav git working folder\leadresourcecenter\App_Code\LRCDataController.vb:line 452

   at lambda_method(Closure , Object , Object[] )

   at System.Web.Http.Controllers.ReflectedHttpActionDescriptor.ActionExecutor.<>c__DisplayClass13.<GetExecutor>b__c(Object instance, Object[] methodParameters)

   at System.Web.Http.Controllers.ReflectedHttpActionDescriptor.ActionExecutor.Execute(Object instance, Object[] arguments)

   at System.Web.Http.Controllers.ReflectedHttpActionDescriptor.<>c__DisplayClass5.<ExecuteAsync>b__4()

   at System.Threading.Tasks.TaskHelpers.RunSynchronously[TResult](Func`1 func, CancellationToken cancellationToken) 

and other error Invalid range f43

 Stack Trace "   at Syncfusion.XlsIO.Implementation.FormulaParser.CreateLocalName(Int32 iRefIndex, String strToken, ParseParameters arguments, Hashtable indexes, Int32 i, ExcelParseFormulaOptions options)

   at Syncfusion.XlsIO.Implementation.FormulaParser.CreateNamedRange(String strToken, ParseParameters arguments, Hashtable indexes, Int32 i, ExcelParseFormulaOptions options, Int32 iRefIndex)

   at Syncfusion.XlsIO.Implementation.FormulaParser.ParseIdentifier(String identifier, Hashtable indexes, Int32 i, ExcelParseFormulaOptions options, ParseParameters arguments)

   at Syncfusion.XlsIO.Implementation.FormulaParser.ParseFirstOperand(Priority priority, Hashtable indexes, Int32 i, ExcelParseFormulaOptions& options, ParseParameters arguments)

   at Syncfusion.XlsIO.Implementation.FormulaParser.ParseExpression(Priority priority, Hashtable indexes, Int32 i, ExcelParseFormulaOptions options, ParseParameters arguments)

   at Syncfusion.XlsIO.Implementation.FormulaParser.Parse(String formula, Hashtable indexes, Int32 i, ExcelParseFormulaOptions options, ParseParameters arguments)

   at Syncfusion.XlsIO.Implementation.FormulaUtil.ParseString(String strFormula, IWorksheet sheet, Hashtable hashWorksheetNames, Int32 iCellRow, Int32 iCellColumn, Boolean bR1C1)

   at Syncfusion.XlsIO.Implementation.RangeImpl.SetFormula(String value, Hashtable hashWorksheetNames, Boolean bR1C1)

   at Syncfusion.XlsIO.Implementation.RangeImpl.SetFormula(String value)

   at Syncfusion.XlsIO.Implementation.RangeImpl.OnValueChanged(String old, String value)

   at Syncfusion.XlsIO.Implementation.RangeImpl.set_Value(String value)

   at Syncfusion.XlsIO.Implementation.RangeImpl.SetSingleCellValue2(Object value)

   at Syncfusion.XlsIO.Implementation.RangeImpl.set_Value2(Object value)

   at Syncfusion.XlsIO.Implementation.WorksheetImpl.ImportDataTableWithoutCheck(DataTable dataTable, Int32 firstRow, Int32 firstColumn, Int32 maxRows, Int32 maxColumns, DataColumn[] arrColumns)

   at Syncfusion.XlsIO.Implementation.WorksheetImpl.ImportDataTable(DataTable dataTable, Boolean isFieldNameShown, Int32 firstRow, Int32 firstColumn, Int32 maxRows, Int32 maxColumns, DataColumn[] arrColumns, Boolean bPreserveTypes)

   at Syncfusion.XlsIO.Implementation.WorksheetImpl.ImportDataTable(DataTable dataTable, Boolean isFieldNameShown, Int32 firstRow, Int32 firstColumn, Int32 maxRows, Int32 maxColumns)

   at Syncfusion.XlsIO.Implementation.WorksheetImpl.ImportDataTable(DataTable dataTable, Boolean isFieldNameShown, Int32 firstRow, Int32 firstColumn)

   at LRCDataController.ExportExcel() in D:\gaurav git working folder\leadresourcecenter\App_Code\LRCDataController.vb:line 452

   at lambda_method(Closure , Object , Object[] )

   at System.Web.Http.Controllers.ReflectedHttpActionDescriptor.ActionExecutor.<>c__DisplayClass13.<GetExecutor>b__c(Object instance, Object[] methodParameters)

   at System.Web.Http.Controllers.ReflectedHttpActionDescriptor.ActionExecutor.Execute(Object instance, Object[] arguments)

   at System.Web.Http.Controllers.ReflectedHttpActionDescriptor.<>c__DisplayClass5.<ExecuteAsync>b__4()

   at System.Threading.Tasks.TaskHelpers.RunSynchronously[TResult](Func`1 func, CancellationToken cancellationToken)" 

while exporting Datatable to excel file using ImportDataTable method of IWorksheet. I am tyring to export 50000 rows from datatable to excel file.

Below is my code.

             

                Dim excelEngine = New ExcelEngine()

                Dim application As IApplication = excelEngine.Excel

                'application.DefaultVersion = ExcelVersion.Excel2007

                Dim workbook As IWorkbook = application.Workbooks.Create(1)

                Dim sheet As IWorksheet = workbook.Worksheets(0)

'Here i am getting error                

sheet.ImportDataTable(dtExport, True, 1, 1)

Please help me out.

[Dilli Babu Nandha Gopal]

Hi Gaurav, 

Thank you for contacting Syncfusion support. 

Regarding Incomplete string, missing "; issue: 

            We are unable to reproduce this issue as the given formula “=Pharmaceutical;medical Devices / Diagnostic Inst,” is not accepted in MS Excel. We have shared a simple sample for your reference which can be downloaded from following link. 

Sample link: http://www.syncfusion.com/downloads/support/directtrac/general/ze/ImportDT-1058966339.zip  

So, kindly share us the modified issue reproducing sample which will be helpful for us to provide you the prompt solution at the earliest. 

Regarding Invalid Named Range issue: 

            This exception is thrown while accessing the named range which doesn’t exist in the workbook. This exception can be suppressed by disabling the ThrowOnUnknownNames property of IWorkbook. The following code example illustrates this behavior. 

Code example: 

workbook.ThrowOnUnknownNames = False 

Please let us know if you have any concerns. 

Regards, 

Dilli babu. 

[gaurav singh]

Thank you for your response

"Pharmaceutical;medical Devices / Diagnostic Instru" is value of one  my datatable row, this is not a formula and Syncfusion is considering this as formula while exporting datatable using Syncfusion. So how can i disable formula for such value to entire sheet  using Syncfusion 6.402.0.15 version dll

[Dilli Babu Nandha Gopal]

Hi Gaurav, 

 

We are unable to reproduce the reported issue in our latest version 14.1v. As you are using older Syncfusion assemblies, we strongly recommend you to upgrade to our latest version 14.1v which has lots of improvements and enhancements. You can download the latest version of Syncfusion assemblies in the following link. 

 

http://www.syncfusion.com/forums/123633/essential-studio-2016-volume-1-release-v14-1-0-41-is-available-for-download 

 

If you are still facing this issue in our latest version, then kindly share us the screenshot of DataTable value along with its column type which will be helpful for us to investigate further on this. 

Regards, 

Dilli babu.