Exception on Inserting Row in Excel

[Thilo Ruppert]

Hi,

When I try to insert row in the excel file, I get the following exception.

I use

sheet.InsertRow(14, 3,

ExcelInsertOptions.FormatAsBefore);

where sheet is IWorksheet.Please tell me where I am making mistake.

Unexpected token.Unexpected token type: tInteger, string value: at position 2. Formula: 3.5, Position: 2

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: Syncfusion.XlsIO.Implementation.Exceptions.ParseException: Unexpected token.Unexpected token type: tInteger, string value: at position 2. Formula: 3.5, Position: 2

Source Error:

An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.

Stack Trace:

 

[ParseException: Unexpected token.Unexpected token type: tInteger, string value:   at position 2. Formula: 3.5, Position: 2]

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

   Syncfusion.XlsIO.Implementation.FormulaTokenizer.RaiseUnexpectedToken(String msg) +72

   Syncfusion.XlsIO.Implementation.FormulaParser.ParseExpression(Priority priority, Dictionary`2 indexes, Int32 i, ExcelParseFormulaOptions options, ParseParameters arguments) +954

   Syncfusion.XlsIO.Implementation.FormulaParser.Parse(String formula, Dictionary`2 indexes, Int32 i, ExcelParseFormulaOptions options, ParseParameters arguments) +215

   Syncfusion.XlsIO.Implementation.FormulaUtil.ParseString(String strFormula, IWorksheet sheet, Dictionary`2 indexes, Int32 i, Dictionary`2 hashWorksheetNames, ExcelParseFormulaOptions options, Int32 iCellRow, Int32 iCellColumn) +87

   Syncfusion.XlsIO.Implementation.ConditionalFormatImpl.SetFirstSecondFormula(FormulaUtil formulaUtil, String strFormula, Boolean bIsFirstFormula) +31

   Syncfusion.XlsIO.Implementation.XmlReaders.Excel2007Parser.ParseCFFormulas(XmlReader reader, ConditionalFormatImpl cFormat) +96

   Syncfusion.XlsIO.Implementation.XmlReaders.Excel2007Parser.ParseConditionFormatRule(XmlReader reader, IInternalConditionalFormat conFormat, List`1 lstDxfs) +342

   Syncfusion.XlsIO.Implementation.XmlReaders.Excel2007Parser.ParseCFRuleTag(XmlReader reader, ConditionalFormats conditionalFormats, List`1 lstDxfs) +267

   Syncfusion.XlsIO.Implementation.XmlReaders.Excel2007Parser.ParseConditionalFormatting(XmlReader reader, ConditionalFormats conditionalFormats, List`1 lstDxfs) +541

   Syncfusion.XlsIO.Implementation.XmlReaders.Excel2007Parser.ParseSheetConditionalFormatting(XmlReader reader, WorksheetConditionalFormats sheetConditionalFormats, List`1 lstDxfs) +152

   Syncfusion.XlsIO.Implementation.XmlSerialization.WorksheetDataHolder.ParseConditionalFormatting(List`1 dxfStyles, WorksheetImpl sheet) +245

   Syncfusion.XlsIO.Implementation.WorksheetImpl.ParseCFFromExcel2007(FileDataHolder dataHolder) +33

   Syncfusion.XlsIO.Implementation.WorksheetImpl.ParseSheetCF() +26

   Syncfusion.XlsIO.Implementation.WorksheetImpl.CopyMoveConditionalFormatting(Int32 iSourceRow, Int32 iSourceColumn, Int32 iRowCount, Int32 iColumnCount, Int32 iDestRow, Int32 iDestColumn, WorksheetImpl destSheet, Boolean bIsMove) +33

   Syncfusion.XlsIO.Implementation.WorksheetImpl.MoveRange(IRange destination, IRange source, ExcelCopyRangeOptions options, Boolean bUpdateRowRecords, IOperation beforeMove) +908

   Syncfusion.XlsIO.Implementation.WorksheetImpl.InsertRow(Int32 iRowIndex, Int32 iRowCount, ExcelInsertOptions insertOptions) +305

   Platinion.MyHRMS.Web.Pages.ExcelGenerators.CareerStepCdDossier.CopyAndInsertRow(IWorksheet sheet, String rangeToCopy) in C:\svn\PlaAppDev\Code\HrmsNet\MyHRMS\Pages\ExcelGenerators\CareerStepCDDossier.aspx.cs:867

   Platinion.MyHRMS.Web.Pages.ExcelGenerators.CareerStepCdDossier.GenerateSheet_BackgroundInformation(IWorksheet sheet, Int32 actualCdMonth, Int32 actualCdYear, Int32 month3period, Int32 year3period) in C:\svn\PlaAppDev\Code\HrmsNet\MyHRMS\Pages\ExcelGenerators\CareerStepCDDossier.aspx.cs:282

   Platinion.MyHRMS.Web.Pages.ExcelGenerators.CareerStepCdDossier.GenerateAndUploadExcelFile(Boolean showRestricted) in C:\svn\PlaAppDev\Code\HrmsNet\MyHRMS\Pages\ExcelGenerators\CareerStepCDDossier.aspx.cs:180

   Platinion.MyHRMS.Web.Pages.ExcelGenerators.CareerStepCdDossier.GenerateCareerCdSheet(Boolean showRestricted) in C:\svn\PlaAppDev\Code\HrmsNet\MyHRMS\Pages\ExcelGenerators\CareerStepCDDossier.aspx.cs:139

   Platinion.MyHRMS.Web.Pages.ExcelGenerators.CareerStepCdDossier.Page_Load(Object sender, EventArgs e) in C:\svn\PlaAppDev\Code\HrmsNet\MyHRMS\Pages\ExcelGenerators\CareerStepCDDossier.aspx.cs:94

   System.Web.Util.CalliHelper.EventArgFunctionCaller(IntPtr fp, Object o, Object t, EventArgs e) +14

   System.Web.Util.CalliEventHandlerDelegateProxy.Callback(Object sender, EventArgs e) +35

   System.Web.UI.Control.OnLoad(EventArgs e) +91

   System.Web.UI.Control.LoadRecursive() +74

   System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +2207

 

[Sridhar]

Hi Thilo Ruppert,

 

Thank you for using Syncfusion products.

 

We have tried the sample excel file by inserting the rows which copies the conditional formatting in the previous rows. We are unable to reproduce the above reported exception at our side. We have attached the sample file and placed the code in the following for your reference.

 

Source Code [C#]:

 

IWorksheet sheet = workbook.Worksheets[0];
sheet.InsertRow(7, 3, ExcelInsertOptions.FormatAsBefore);

 

Please try the attached excel file with the above code and let us know if this helps you. Also, please get back to us with the following to proceed further on this.

 

1.The input template excel file which causes the above exception

2.The Essential Version using at your side.

3.Simplified sample with input excel file which reproduces the issue.

 

Note:

We have tried the attached sample file with our latest version.

 

Please let me know if you have any further clarifications.

 

Thanks,

Sridhar.S

ConditionalFormatting_894caa9b.zip

[Thilo Ruppert]

Hi There,

Thanks for your timely answer. I have copied the code where I am inserting rows in the excel sheet. I have attached the excel file with this post.

IWorkbook book = app.Workbooks.Open(Server.MapPath(@"../../globals/templates/CareerStepCDDossier.xlsx"));

IWorksheet sheet = book.Worksheets[0];

string rangeToCopy = "BI_Row"; // Range from rows 7,8,9 to column AM in the attached excel file.

var firstRowNumber = sheet.Range[rangeToCopy].Row;

int firstColumnNumber = sheet.Range[rangeToCopy].Column;

int lastRowNumber = sheet.Range[rangeToCopy].Rows.Last().Row;

int rowCount = sheet.Range[rangeToCopy].Rows.Count();

sheet.InsertRow(lastRowNumber + 1, rowCount,

ExcelInsertOptions.FormatAsBefore);

 

CareerStepCDDossier_6bf48a9e.rar

[Thilo Ruppert]

We are using latest version of syncfusion which is 10.404.0.53. Runtime version is v4.0.30319.

[Sridhar]

Hi Thilo Ruppert,

 

Thank you so much for the update.

 

We afraid that, we are unable to reproduce the above reported issue at our side. We have tried to insert row for above reported excel file with XlsIO in the version 10.4.0.53 and we could not see any issues at our side. Also, we could not see any conditional formatting avilable with the worksheets for the attached excel file. Please ensure the excel file which reproduces the above exception is attached properly.

 

We have placed the sample in the following link for your reference. We have created the screen video of testing done at our side and attached the video with this mail.

 

Sample Link:

 

http://www.syncfusion.com/downloads/Support/DirectTrac/102232/InsertRow-1941803739.zip

 

Please take a look at the video , try the sample at your side and let us know if this helps you.

 

If you still face the issue please perform the following tasks solving the issue.

 

1.Select Build-> Clean solution to clean up already build assemblies.

2.Remove the references and add it again with the specified framework. Check the version of the assemblies once you have added.

3.If you still face the issue, remove the assemblies from GAC using Syncfusion Assembly Manager which is available in the Syncfusion Dashboard and then install the assemblies again using the Syncfusion Assembly Manager.

 

Please let me know if you require any further clarifications.        

 

Thanks,

Sridhar.S

InsertRow_video_bc10602f.zip