Issue with EOMONTH in older format on Russian locale

Aspose.Cells Product Family
1

Hi. We have found, that if workbook saved in legacy format MS Excel 2003, then Aspose could not calculate this function. Please check the following test

in.zip (6.9 KB)

Best regards. Alexey

2

@makarovalv,

It looks like you are using some other locale/regional settings as the formula name is not in english, see the screenshot (attached) for reference.
sc_shot1.png (57.0 KB)

Please provide a template file containing the formula (in English) which should be opened and calculated in our environment. We will check it soon.

3

Hi. Yes. We are using Russian locale for our books. But it calculates for us. So it, looks like Excel does not saved it as Excel’s multilanguage formulain.zip (6.6 KB)
There is this file, that saved in xlsx format

Best regards. Alexey

4

@makarovalv,

I guess this might be the limitation of older XLS file format itself as it works ok if we use XLSX file format in Russian locale. To cope with it, could you may try the following line to set the region to Russia if it works for your needs:
e.g
Sample code:

var workbook = new Workbook(“e:\test2\in.xls”);

workbook.Settings.Region = CountryCode.Russia;

        var sheet = workbook.Worksheets[0];
        Cell cell = sheet.Cells["B2"];
        var value = cell.StringValue;

        workbook.CalculateFormula();
        value = cell.StringValue;

Hope, this helps a bit.

5

Hi.

It does not worked for me.

Best regards. Alexey

6

@makarovalv,

Please notice, we are able to reproduce the issue after specifying the regional settings to Russia. I am using the following sample code with your template in.xls file.
e.g
Sample code:

var workbook = new Workbook(“e:\test2\in.xls”);

//This does not work either.
//workbook.Settings.Region = CountryCode.Russia;

        var sheet = workbook.Worksheets[0];
        Cell cell = sheet.Cells["B2"];
        var value = cell.StringValue;

        workbook.CalculateFormula();
        value = cell.StringValue;//error value.

I have logged a separate ticket with an id “CELLSJAVA-43306” for your issue. We will look into it soon.

Once we have an update on it, we will let you know.

7

@makarovalv,

We are afraid we cannot support those locale-dependent function names while loading formulas from template file. Even in MS Excel with other locales, such as en_US, the formula in this template file cannot be recognized as EOMONTH function either.

To solve such kind of issue, the best way should be that you save the file in MS Excel with “standard” regional settings such as, en_US. For existing template files with such kind of issue, to process it with Aspose.Cells, you may try below workaround:
e.g
Sample code:

    workbook.getSettings().setGlobalizationSettings(new GlobalizationSettings()
    {
        public String getStandardFunctionName(String localName)
        {
            //user may handle any other functions with locale-dependent name
            if(localName.equals("КОНМЕСЯЦА"))
            {
                return "EOMONTH";
            }
            return localName;
        }
    });
    FormulaParseOptions copts = new FormulaParseOptions();
    copts.setParse(false);
    copts.setLocaleDependent(true);
    for(int i=0; i < workbook.getWorksheets().getCount(); i++)
    {
        Worksheet st = workbook.getWorksheets().get(i);
        Iterator iter = st.getCells().iterator();
        while(iter.hasNext())
        {
            Cell c = (Cell)iter.next();
            if(c.isFormula())
            {
                c.setFormula(c.getFormula(), copts, c.getValue());
            }
        }
    }
    workbook.parseFormulas(false);
    workbook.calculateFormula(false);
    ...

Hope, this helps a bit.

8

@makarovalv,

In case you need parallel .NET code, see the following sample code for your reference:
e.g.
Sample code:

......
public class MyGlobalSettings1 : GlobalizationSettings
    {
        public override string GetStandardFunctionName(string localName)
        {
            //user may handle any other functions with locale-dependent name
            if(localName.Equals("КОНМЕСЯЦА"))
            {
                return "EOMONTH";
            }
            return localName;
        }
    }


......

 MyGlobalSettings1 myGlobalSettings1 = new MyGlobalSettings1();
            
            var workbook = new Workbook("e:\\test2\\in.xls");

            var sheet = workbook.Worksheets[0];
            Cell cell = sheet.Cells["B2"];
            var value = cell.StringValue;

            workbook.Settings.GlobalizationSettings = myGlobalSettings1;
            
            FormulaParseOptions copts = new FormulaParseOptions();
            copts.Parse = false;
            copts.LocaleDependent = true;

            for (int i = 0; i < workbook.Worksheets.Count; i++)
            {
                Worksheet st = workbook.Worksheets[i];

                IEnumerator iter = st.Cells.GetEnumerator();
                while (iter.MoveNext())
                {
                    Cell c = iter.Current as Aspose.Cells.Cell;
                    if (c.IsFormula)
                    {
                      c.SetFormula(c.Formula, copts, c.Value);
                    }
                    
                }
                
            }

            workbook.ParseFormulas(false);
            workbook.CalculateFormula(false);
            value = cell.StringValue;