ClosedXML formula support for parsing Chinese

[shangrics]

Read and complete the full issue template

Do you want to request a feature or report a bug?

• Bug
• Feature

Version of ClosedXML

e.g. 0.93.1

What is the current behavior?

My Demo.xlsx includes two Sheets, which are all Chinese Sheet name.

I set my formula in the cell, and it refers to another Sheet.

But when I get cell value , the value shows Exception.

demo.xlsx

Did this work in previous versions of our tool? Which versions?

I have tested it with versions: 0.93.1

Code to reproduce problem:

using (var objXLWorkbook = new XLWorkbook(src))
        {
            var objWorksheet = objXLWorkbook.Worksheet("測試二");

            objWorksheet.Cell("A1").FormulaA1 = "=IF(測試一!A1=10,1,2)";

            var objxLCell = objWorksheet.Cell("A1").Value;
        }

exception Message: Identifier expected.
exception StackTrace:
at ClosedXML.Excel.CalcEngine.CalcEngine.GetToken()
at ClosedXML.Excel.CalcEngine.CalcEngine.GetParameters()
at ClosedXML.Excel.CalcEngine.CalcEngine.ParseAtom()
at ClosedXML.Excel.CalcEngine.CalcEngine.ParseUnary()
at ClosedXML.Excel.CalcEngine.CalcEngine.ParsePower()
at ClosedXML.Excel.CalcEngine.CalcEngine.ParseMulDiv()
at ClosedXML.Excel.CalcEngine.CalcEngine.ParseAddSub()
at ClosedXML.Excel.CalcEngine.CalcEngine.ParseCompare()
at ClosedXML.Excel.CalcEngine.CalcEngine.Parse(String expression)
at ClosedXML.Excel.CalcEngine.ExpressionCache.get_Item(String expression)
at ClosedXML.Excel.CalcEngine.CalcEngine.Evaluate(String expression)
at ClosedXML.Excel.XLCell.RecalculateFormula(String fA1)
at ClosedXML.Excel.XLCell.Evaluate(Boolean force)
at ClosedXML.Excel.XLCell.get_Value()

=========================================

I found ClosedXML.Excel.CalcEngine.CalcEngine.GetToken() ,
have a piece of code :

var isLetter = (c >= 'a' && c <= 'z') || (c >= 'A' && c <= 'Z');

it's seems to only identify English

[Pankraty]

Try to escape the worksheet name. E.g. =IF('測試一'!A1=10,1,2)

[shangrics]

That is ok but i wonder if you can increase the range of characters' identification
E.g.
var isLetter = (c >= 'a' && c <= 'z') || (c >= 'A' && c <= 'Z') || (c >= Convert.ToInt32("4e00", 16) && c <= Convert.ToInt32("9fff", 16));

4e00~9fff: Chinese Unicode range

[igitur]

We should rather use the Char.IsLetter method. Btw, the expression parser seems to be a derivative work of https://github.com/kahanu/System.Linq.Dynamic/blob/master/Src/System.Linq.Dynamic/DynamicLinq.cs . And it seems they now also use Char.IsLetter.

[shangrics]

OK.
With regard to revising this issue, which versions would you consider to launch in?
Thanks.

[igitur]

Please try with v0.94.2 and let me know if it works.