Not able to set R1C1Formula

Aspose.Cells Product Family
1

I am not able to set R1C1Formual to cell, please see below details and complete stack trace. I am using Aspose.Cells(21.5.0).

_workBook.Worksheets[0].Cells[cell.Row, cell.Column]
{Aspose.Cells.Cell [ K9; ValueType : IsString; Value : $SUM(K{0}:K{1}) ]}
BoolValue: ‘_workBook.Worksheets[0].Cells[cell.Row, cell.Column].BoolValue’ threw an exception of type ‘Aspose.Cells.CellsException’
Column: 10
Comment: null
ContainsExternalLink: false
DateTimeValue: ‘_workBook.Worksheets[0].Cells[cell.Row, cell.Column].DateTimeValue’ threw an exception of type ‘Aspose.Cells.CellsException’
DisplayStringValue: “$SUM(K{0}:K{1})”
DoubleValue: ‘_workBook.Worksheets[0].Cells[cell.Row, cell.Column].DoubleValue’ threw an exception of type ‘Aspose.Cells.CellsException’
FloatValue: ‘_workBook.Worksheets[0].Cells[cell.Row, cell.Column].FloatValue’ threw an exception of type ‘Aspose.Cells.CellsException’
Formula: null
FormulaLocal: null
HtmlString: “<Font Style=“FONT-WEIGHT: bold;FONT-FAMILY: Segoe;FONT-SIZE: 9pt;COLOR: #000000;TEXT-ALIGN: right;Background-Color: #d8e4bc;”>$SUM(K{0}:K{1})”
IntValue: ‘_workBook.Worksheets[0].Cells[cell.Row, cell.Column].IntValue’ threw an exception of type ‘Aspose.Cells.CellsException’
IsArrayFormula: false
IsArrayHeader: false
IsErrorValue: false
IsFormula: false
IsInArray: false
IsInTable: false
IsMerged: true
IsSharedFormula: false
IsStyleSet: true
IsTableFormula: false
Name: “K9”
NumberCategoryType: Number
R1C1Formula: null
Row: 8
SharedStyleIndex: 78
StringValue: “$SUM(K{0}:K{1})”
StringValueWithoutFormat: “$SUM(K{0}:K{1})”
Type: IsString
Value: “$SUM(K{0}:K{1})”
Worksheet: {Aspose.Cells.Worksheet[ Section 1 - Product Details ]}

_workBook.Worksheets[0].Cells[cell.Row, cell.Column].R1C1Formula = "=SUM(K7:K8)"
‘_workBook.Worksheets[0].Cells[cell.Row, cell.Column].R1C1Formula = “=SUM(K7:K8)”’ threw an exception of type ‘Aspose.Cells.CellsException’
Code: Formula
Data: {System.Collections.ListDictionaryInternal}
HResult: -2146232832
HelpLink: null
InnerException: null
Message: “Invalid text(cell reference) for the defined name.(Based on cell Section 1 - Product Details!K9)”
Source: “Aspose.Cells”
StackTrace: " at \u0003 .\u000e (StringBuilder \u0002)\r\n at \u0003 .\u0003 (String \u0002)\r\n at \u000e .\b (\u0005 \u0002, String \u0003, \u0003 \u0005)\r\n at \u000e .\u0006 \u000e(Char[] \u0002, \u0003 \u0003)\r\n at \u0002 .\b .\u0002(Char[] \u0002, \u0003 \u0003, Boolean \u0005)\r\n at \u0002 .\b .\u0006 \u000e(Char[] \u0002, \u0003 \u0003)\r\n at \u0002 .\b .\u0006 \u000f(Char[] \u0002, \u0003 \u0003, \u0005 \u0005)\r\n at \u0006 .\b .\u0006 \u000f(Char[] \u0002, \u0003 \u0003, \u0005 \u0005)\r\n at \u0006 .\u0003 .\u0006 \u000f(Char[] \u0002, \u0003 \u0003, \u0005 \u0005)\r\n at \u0003 .\u0005 (Char[] \u0002, Int32 \u0003, Int32 \u0005, Boolean \b)\r\n at \u0003 .\u0003 (String \u0002, Boolean \u0003)\r\n at \u0003 .\u0002 (String \u0002)\r\n at Aspose.Cells.Cell.set_R1C1Formula(String value)"
TargetSite: {Void (System.Text.StringBuilder)}

2

@PramodHegde,

Your line of code is not right. You are actually setting A1Formula string for R1C1Formula which is wrong. See the following sample code for your complete reference. Check the last line also (you may even convert your simple A1Formula to R1C1Formula string using CellsHelper static method):
e.g.
Sample code:

Workbook _workBook = new Workbook();
_workBook.Worksheets[0].Cells[0, 0].R1C1Formula = "=SUM(R[6]C[10]:R[7]C[10])";
Console.WriteLine(_workBook.Worksheets[0].Cells[0, 0].R1C1Formula);
Console.WriteLine(_workBook.Worksheets[0].Cells[0, 0].Formula);
Console.WriteLine(CellsHelper.ConvertA1FormulaToR1C1("=SUM(K7:K8)",0,0));

Let us know if you still find any issue or confusion.

3

Thanks for the details, but it not working, please find below details:

One place it set as =SUM(#REF!) and other places it set to wrong values such as =SUM(U86:U87)

image.png (46.7 KB)

4

@PramodHegde,

If you could just use my code in a separate project and run it, you will notice, it works as expected. We request you to create a separate VS.NET console application (runnable), zip the project with all the resource files and post us to reproduce the issue on our end, we will check it soon.

PS. we also recommend you to kindly try our latest version/fix: Aspose.Cells for .NET v21.7.