R1C1Formula acting weird


#1

Hi.

We’ve upgraded from ASPOSE.CELLS 8.6.2.0 to 19.2.0.0 and I’ve just discovered everything is not ok. The R1C1Formula property in particular is not placing the values we’re passing into the spreadsheet.

For instance, in code, we’re passing : “=(A505-A8)/365”, what we’re getting on the spreadsheet instead is “=(‘A505’-‘A8’)/365”

What we’re passing: “=(1+L5)^($A$6)-1”, what we’re getting: “Invalid name: $A$6(Based on cell Cash flow!L6)”

What we’re passing: “=N505”, what we’re getting: “=‘N505’”

And probably one of the weirdest errors of all:
What we’re passing: “=History!N4”, what we’re getting: “=‘PerformanceFeeComposition_be427d30-9d57-4ed3-9026-43d59fb00d6a.xls’!‘N4’”

We still have the old code before we upgraded, with the old assemblies, and if we connect with it on the exact same database, everything works as expected. The only thing that changed was the ASPOSE.Cells assemblies.

We release in less than 2 weeks, so this discovery is less than pleasant. Can you please look into your code what would cause R1C1Formula to alter the values it receives? Is there some kind of property which we can set to prevent if from acting this way? We just want the property to set the value as we’ve defined it, and not try to adjust it for whatever reason.

Thanks.

EDIT:
It appears the first place where it becomes a problem, is the following formula “=(A505-A8)/365”, where Column A is a date field, expressed as “DD MMM YYYY”. It seems like R1C1Formula is expecting it to be a text field. From there, everything goes down hill.

EDIT2:
It appears if the R1C1Formula property has been changed to no longer accept formulas with absolute cell addresses anymore. Whenever it receives an absolute cell address, it puts it in quotation marks. When it gets something like “$A$6”, it throws an error. If my analysis is correct, then that is going to cause a major refactoring headache for us. Why was this done? It seems very unnecessary and restrictive.

EDIT3:
I think I found the solution. If I replace the property with the SetFormula method, it works much better.


#2

@Hannodb,

It looks like you have sorted it out now. In case you still find any issue regarding formulas or calculated values, kindly do create a simple console demo application (runnable) using latest version v19.5, zip the project and post us here to reproduce the issue, we will check it soon. Also attach your template file(s) if any, so we could evaluate your issue precisely to consequently figure it out (if found).


#3

Nope, sorry. I must have looked wrong. This morning, I came in again, and it’s still broken :slightly_frowning_face:

For instance. I pass:

_workbook.Worksheets[3].Cells[5, 0].SetFormula("=(A505-A8)/365", true, true, null);

where column A is a range of dates between A8 to A505

Instead of placing the formula as specified, I get “=(‘A505’-‘A8’)/365”

Even worse,

_workbook.Worksheets[3].Cells[5, 11].SetFormula("=(1+R[-1]C[0])^($A$6)-1", true, true, null);

where A6 references the resultant of the previous formula, results in the following error:
“Invalid name: $A$6(Based on cell Cash flow!L6)”

If I remove the dollar signs, I it writes the following:

“=(1+R[-1]C[0])^(‘A6’)-1”

This is code that still works perfectly fine on the old ASPOSE assemblies, it is beyond me as to why this was changed to add this unwanted behavior.


#4

ExcelError.png (89.9 KB)
Ok, so I’ve created a test method in my project to reproduce the issue. I hope you can get it to run. I’ve also upgraded from ASPOSE Cells 19.2 to 19.5 - with no benefit.

Please provide us with an answer ASAP as we’re releasing next Friday, and this is a part of the system that hasn’t changed. I reiterate: This works PERFECTLY on the old ASPOSE assemblies, it was upgrading to the newest version of ASPOSE.CELLS that caused this to now malfunction. Having broken ASPOSE assemblies is not expected and hugely inconvenient. PLEASE TREAT THIS AS HIGH PRIORITY.

The code is as follow, and the result is attached:

public static Workbook CreateWorkbook()
    {
        Workbook workbook = new Workbook();
        workbook.ChangePalette(_headerColor, 55);
        workbook.ChangePalette(_level1Color, 54);
        workbook.ChangePalette(_level2Color, 53);
        workbook.ChangePalette(_level3Color, 52);
        workbook.ChangePalette(_growthColor, 51);
        workbook.ChangePalette(_prudentColor, 50);
        workbook.ChangePalette(_stableColor, 49);
        workbook.ChangePalette(_problemColor, 48);
        workbook.ChangePalette(_correctColor, 47);
        workbook.ChangePalette(_highlightColor, 46);
        return workbook;
    }


private void AddTestPage()
    {
        if (_workbook == null)
        {
            _workbook = CreateWorkbook();
        }
        int index = _workbook.Worksheets.Add();
        Worksheet testSheet = _workbook.Worksheets[index];
        testSheet.Name = "TestSheet";
        DateTime date = new DateTime(2019,01,01);
        int maxRow = 900;
        for (int i = 5; i < maxRow; i++)
        {
            PutValue(index, i, 0, "Arial", 10, false,false,FontUnderlineType.None,TextAlignmentType.Left,TextAlignmentType.Top, "dd MMM yyyy", date, false);
            date = date.AddDays(1);
        }
        string percentageFormat = GlobalSettings.PercentageFormat; //
        PutFormula(index, 5, 0, null, 10, true, FontUnderlineType.None, TextAlignmentType.Right, percentageFormat, "=(A" + maxRow  + "- A6)/365", true);

        PutValue(index, 4, 11, "Arial", 10, false, false, FontUnderlineType.None, TextAlignmentType.Left, TextAlignmentType.Top, percentageFormat + " %", 0.0701, false);
        PutFormula(index, 5, 11, null, 10, true, FontUnderlineType.None, TextAlignmentType.Right, percentageFormat + " %", "=(1+R[-1]C[0])^($A$6)-1", true);

    string workBookFile = "C\:PerformanceFeeComposition_" + Guid.NewGuid().ToString() + ".xls";
        _workbook.Save(workBookFile, SaveFormat.Excel97To2003);
        System.Diagnostics.Process.Start(workBookFile);

    }

 public void PutValue(int worksheetIndex, int rowIndex, int columnIndex, string fontName, int fontSize, bool isBold, bool isItalic, FontUnderlineType underlineType, TextAlignmentType horizontalAlignment, TextAlignmentType verticalAlignment, string customStyle, object value, bool isTextWrapped = false)
	{
		if (_leaveZeroValuesEmpty && (value is int && (int)value == 0 || value is decimal && (decimal)value == 0) ||
			_leaveNullDatesEmpty && value is DateTime && (DateTime)value == GlobalSettings.NullDate)
		{
			value = string.Empty;
		}
		_workbook.Worksheets[worksheetIndex].Cells[rowIndex, columnIndex].PutValue(value);
        
        Style style = _workbook.Worksheets[worksheetIndex].Cells[rowIndex, columnIndex].GetStyle();
        style.Font.Name = string.IsNullOrEmpty(fontName) ? FontNameGeorgia : fontName;
		style.Font.Size = fontSize;
		style.Font.IsBold = isBold;
		style.Font.IsItalic = isItalic;
		style.Font.Underline = underlineType;
		style.HorizontalAlignment = horizontalAlignment;
		style.VerticalAlignment = verticalAlignment;
		style.Custom = string.IsNullOrEmpty(customStyle) ? string.Empty : customStyle;
        style.IsTextWrapped = style.IsTextWrapped ? style.IsTextWrapped : isTextWrapped;
        _workbook.Worksheets[worksheetIndex].Cells[rowIndex, columnIndex].SetStyle(style);
	}

protected void PutFormula(int worksheetIndex, int rowIndex, int columnIndex, string fontName, int fontSize, bool isBold, FontUnderlineType underlineType, TextAlignmentType horizontalAlignment, string customStyle, string formula, bool isR1C1)
    {
        try
        {
            
            _workbook.Worksheets[worksheetIndex].Cells[rowIndex, columnIndex].SetFormula(formula, isR1C1, true, null);
            /*
            if (isR1C1)
            {                    
                _workbook.Worksheets[worksheetIndex].Cells[rowIndex, columnIndex].R1C1Formula = formula;
            }
            else
            {
                _workbook.Worksheets[worksheetIndex].Cells[rowIndex, columnIndex].Formula = formula;
            }*/
            Style style = _workbook.Worksheets[worksheetIndex].Cells[rowIndex, columnIndex].GetStyle();
            style.Font.Name = string.IsNullOrEmpty(fontName) ? FontNameGeorgia : fontName;
            style.Font.Size = fontSize;
            style.Font.IsBold = isBold;
            style.Font.Underline = underlineType;
            style.HorizontalAlignment = horizontalAlignment;
            style.Custom = string.IsNullOrEmpty(customStyle) ? string.Empty : customStyle;
            _workbook.Worksheets[worksheetIndex].Cells[rowIndex, columnIndex].SetStyle(style);
        }
        catch (Exception ex)
        {
            Aspose.Cells.Cell cell = _workbook.Worksheets[worksheetIndex].Cells[rowIndex, columnIndex];
            Style style = cell.GetStyle();               
            cell.PutValue(ex.Message);
            style.Font.Color = Color.Red;
            style.Font.IsBold = true;
            cell.SetStyle(style);
        }
    }

#5

@Hannodb,

Thanks for the code segment and screenshot.

I tried to evaluate your issue using your code segment, tried a few adjustments but there are certain objects, variables and their attributes/values for which I am not sure. So, kindly create a simple console demo application (runnable), zip the project and post us here to reproduce the issue, we will check it soon.


#6

ExcellError.zip (4.6 MB)

Please find the sample project attached.


#7

@Hannodb,

Thanks for the sample project.

After an initial test, I am able to observe the issue as you mentioned by using your sample project. I found some formulas are not correctly applied in newer versions. I have logged an investigation ticket with an id “CELLSNET-46771” for your issue. We will look into it soon.

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


#8

Ok, but we have a release this next Friday, and we need a solution before then. What do you recommend we do in the mean time?


#9

@Hannodb,

Could you spare us a little time (1-2 days or so), so we could evaluate your issue precisely before we could comment on it. If this is a bug, we will surely try to fix it soon. Also, we will check if there is some other way around (if it is an issue and fix takes more time) to cope with it, we will share it with you.


#10

I’ll Appreciate that. Thanks.


#11

@Hannodb,
We have investigated it a bit more and found it to be incorrect way of using R1C1 formula with references in A1 format.

In old versions, we simply convert some special expressions of R1C1 formula to A1 format before parsing the input R1C1 formula. This way is easier for processing R1C1 formulas, but it can only handle some simple scenarios correctly.

In new versions we improved our formula engine and support much more complicated R1C1 formulas. On the other hand, this enhancement requires more restrict on the input R1C1 expression (just as what is required by MS Excel when you input formula in R1C1 format).

So, with the new versions, you must input R1C1 formula in the correct format (same with what you should input in MS Excel with R1C1 fashion). Otherwise you may get exception or unexpected/weird result.

One more thing about the SetFormula() method, the bool parameters denote whether the input formula is R1C1 format and whether the formula is in locale depended format separately. For your example formula, they all should be false.

Let us know your feedback.


#12

I’m sorry, but I find your answer disappointing: If your code simply left the A1 cell addresses as is, instead of unnecessarily putting them in quotation marks, it would still have worked perfectly. I was hoping that there would be some property I could set that would prevent this behavior. In future, please keep backward comparability in mind when you make such dramatic changes to such core functionality. Some of your clients works on very large systems, and changes like this could have a massive impact.

Be that as it may, the fact is we’re releasing in a weeks time, and refactoring all our formulas is not an option at this time. Would it be safe to only revert ASPOSE.Cells to the old version, while keeping the other ASPOSE assemblies updated? As far as I can see, these assemblies are completely isolated, and therefore, this should not be a problem?


#13

@Hannodb,
The generated formulas of old versions are inconsistent with the behavior of ms excel. If you input such kind of reference in MS Excel with R1C1 format, such as A505, you will find the formula becomes #NAME?. That is because the A505 has been taken as a defined name for R1C1 formula. And it is just the behavior of new versions of our product. So the changes between versions are not only enhancements but also bug fixes. We cannot roll the fix back to make it compatible with the incorrect behavior of old versions.

Hope it clarifies the scenario.