Syncfusion.XlsIO.Implementation.Exceptions.ParseException: Unexpected token.Unexpected token type:

[Paul Meems]

I'm trying to add a formula to a cell using:

worksheet.Range[subtotalRows + 2, firstDateColumn, subtotalRows + 2, lastDateColumn].Formula = $"=IF(Y{subtotalRows + 1}=8; 3; 4)";

This fails with this error:

Syncfusion.XlsIO.Implementation.Exceptions.ParseException: Unexpected token.Unexpected token type: Identifier, string value:   at position 9. Formula: IF(Y14=8; 3; 4), Position: 9

Pasting the generated formula (=IF(Y14=8; 3; 4)) directly in my Excel sheet works fine.

Simplifying the formula to 

worksheet.Range[subtotalRows + 2, firstDateColumn, subtotalRows + 2, lastDateColumn].Formula = $"Y{subtotalRows + 1}+10";

works fine as well.

Other settings I use:

                var application = excelEngine.Excel;

                application.DefaultVersion = ExcelVersion.Excel2007;

                application.EnableIncrementalFormula = true;

                excelEngine.ThrowNotSavedOnDestroy = true;

               var workbook = application.Workbooks.Open(new MemoryStream(Resource.Template, false));

                // Template was made with Dutch Excel:

                workbook.SetSeparators(',', ';');

            //Formula calculation is enabled for the sheet.

            worksheet.EnableSheetCalculations();

How to set the formula using code?

The complete formula I'm trying to add is:

=IF(P13="a";MIN(P12;156)*P$1;MIN(P12;P13/P$2*156)*P$1)/156

Thanks,

Paul

[Mohan Chandran]

Hi Paul, 

  

Thank you for contacting Syncfusion support.  

  

This is a usage level issue. You have used semi-colon as a separator in your formulas but, set comma as a separator in workbook.SetSeparators method. So, we request you to change the argument separator as semi-comma to resolve the issue as shown below. 

 

Code snippet: 

 

workbook.SetSeparators(';', ';'); 

 

Please try this and let us know if it helps. 

  

Regards, 

Mohan Chandran. 

[Paul Meems]

Thanks Mohan,

I'm still doing something wrong because it is not working.

I'm using now workbook.SetSeparators(';', ';'); And below you can see that it is set properly:

But still I get this error:

Syncfusion.XlsIO.Implementation.Exceptions.ParseException: Unexpected token.Unexpected token type: Identifier, string value:   at position 8. Formula: MIN(Y13;156)*Y$7, Position: 8

[Mohan Chandran]

Hi Paul, 

  

We have tried in different possible scenarios to reproduce the issue and are unable to reproduce it. We have shared the video screenshot for your reference which can be downloaded from the following link. 

 

Video Screenshot link : http://www.syncfusion.com/downloads/support/forum/133690/ze/ScreenCapture_14-11-2017_14.45.53.wmv481898820 

 

We have shared a sample which we have tried to reproduce the issue at our end which can be downloaded from the following location. 

  

Sample link: http://www.syncfusion.com/downloads/support/forum/133690/ze/CreateSpreadsheet-1503614125151567540-1319063040  

 

Kindly modify the sample to reproduce the issue and share us the modified sample along with the issue reproducing input files which will be helpful for us to give you a prompt solution at the earliest. 

  

Regards, 

Mohan Chandran. 

[Paul Meems]

Thanks again for looking into this.

I managed to get it working. The problem was with the separator.

I use 

workbook.SetSeparators(';', ';'); 

But in the formula, I need to use a comma as the separator and when I open the Excel file the semicolon is used and the formula is working.

My working code is:

worksheet.Range[subtotalRows + 2, firstDateColumn, subtotalRows + 2, lastDateColumn].Formula = $"=IF(Y{subtotalRows + 1}=\"a\",MIN(Y{subtotalRows},156)*Y$7,MIN(Y{subtotalRows},Y{subtotalRows+1}/Y$8*156)*Y$7)/156";

Thanks for all the help.

This question can be closed.

[Mohan Chandran]

Hi Paul, 

  

We are glad that the issue is resolved at your end. Please let us know if you have any other queries. 

  

Regards, 

Mohan Chandran. 

[DineshKannan]

Hi Mohan, Hope you are doing well. Can you help me to clarify the below thing.

Scenario: User has to enter the date in a cell at column A in a sheet with a format of DD.MM.YYYY(02.12.2024)

In order to set the conditional formatting, I have added the below piece of code.

   ExcelEngine excelEngine = new ExcelEngine();

    IApplication application = excelEngine.Excel;

    application.DefaultVersion = ExcelVersion.Excel2013;

    IWorkbook workbook = application.Workbooks.Create(new string[] { "Students"});

    workbook.SetSeparators(';', ',');

    IWorksheet sheet = workbook.Worksheets["Students"];

    workbook.Allow3DRangesInDataValidation = true;

    private void Validation(IWorksheet sheet)

      {

     IConditionalFormats condition = sheet.Range["A3:A10000"].ConditionalFormats;

     condition.BeginUpdate();

     //Empty cell Rule

     IConditionalFormat condition1 = condition.AddCondition();

     condition1.StopIfTrue = true;

     condition1.FormatType = ExcelCFType.Formula;

     condition1.Operator = ExcelComparisonOperator.Equal;

     condition1.FirstFormula = "=A3=\"\"";

     //Rule set for . positions

     IConditionalFormat condition2 = condition.AddCondition();

     condition2.StopIfTrue = false;

     condition2.FormatType = ExcelCFType.Formula;

     condition2.Operator = ExcelComparisonOperator.Equal;

     condition2.FirstFormula = "=RIGHT(LEFT(A3,3),1)<>\".\"";

     condition2.BackColorRGB = Color.FromArgb(255, 204, 204);

     condition2.FontColorRGB = Color.FromArgb(255, 0, 0);

     IConditionalFormat condition3 = condition.AddCondition();

     condition3.StopIfTrue = false;

     condition3.FormatType = ExcelCFType.Formula;

     condition3.Operator = ExcelComparisonOperator.Equal;

     condition3.FirstFormula = "=RIGHT(LEFT(A3,6),1)<>\".\"";

     condition3.BackColorRGB = Color.FromArgb(255, 204, 204);

     condition3.FontColorRGB = Color.FromArgb(255, 0, 0);

  condition.EndUpdate();

  }

When i deploy this, am gettting below error

 Syncfusion.XlsIO.Implementation.Exceptions.ParseException: Unexpected token.Unexpected token type: Identifier, string value: at position 14. Formula: RIGHT(LEFT(A3,3),1)<>".", Position: 14

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

   at Syncfusion.XlsIO.Implementation.FormulaTokenizer.RaiseUnexpectedToken(String msg)

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

   at Syncfusion.XlsIO.Implementation.FormulaParser.ExtractOperands(ExcelParseFormulaOptions options, ParseParameters arguments, ExcelFunction functionId)

   at Syncfusion.XlsIO.Implementation.FormulaParser.CreateFunction(ExcelFunction functionId, Dictionary`2 indexes, Int32 i, ExcelParseFormulaOptions options, ParseParameters arguments)

   at Syncfusion.XlsIO.Implementation.FormulaParser.ParseFunction(Dictionary`2 indexes, Int32 i, ExcelParseFormulaOptions options, ParseParameters arguments)

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

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

   at Syncfusion.XlsIO.Implementation.FormulaParser.ExtractOperands(ExcelParseFormulaOptions options, ParseParameters arguments, ExcelFunction functionId)

   at Syncfusion.XlsIO.Implementation.FormulaParser.CreateFunction(ExcelFunction functionId, Dictionary`2 indexes, Int32 i, ExcelParseFormulaOptions options, ParseParameters arguments)

   at Syncfusion.XlsIO.Implementation.FormulaParser.ParseFunction(Dictionary`2 indexes, Int32 i, ExcelParseFormulaOptions options, ParseParameters arguments)

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

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

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

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

   at Syncfusion.XlsIO.Implementation.ConditionalFormatImpl.set_FirstFormula(String value)

   at Syncfusion.XlsIO.Implementation.ConditionalFormatWrapper.set_FirstFormula(String value)

Additionally, When I generate excel file in development it works fine and in deployed site it didnt.

   Thanks.