ConvertFormula

Aspose.Cells Product Family
1



Can I perform the following task by using Aspose.Cells?
The input reference type can be absolute, relative or arbitrary.


f = "=SUM($B2:E$5)"

f = Application.ConvertFormula( _
Formula:=f, _
fromReferenceStyle:=xlA1, _
toReferenceStyle:=xlR1C1, _
toAbsolute:=xlRelative, _
relativeTo:=Range(“C4”))

MsgBox f
’ message is “=SUM(R[-2]C[-1]:R[1]C[2])”


2

Hi,

Well, Aspose.Cells provides two simple properties i.e. Formula and R1C1Formula of Aspose.Cells.Cell class. You can set / get any type of formula using the two properties. Here is a sample code for your reference if it could help you:

Sample code:

Workbook workbook = new Workbook();
Worksheet worksheet = workbook.Worksheets[0];
Cells cells = worksheet.Cells;
//Input some values into the cells.
cells[“B2”].PutValue(1);
cells[“D2”].PutValue(4);

//Set formula
cells[“A1”].Formula = “=SUM($B2:D$2)”;

workbook.CalculateFormula();
//It will automatically convert to R1C1 formula type based on the A1 Formula
MessageBox.Show("Formula String: " + cells[“A1”].R1C1Formula);
MessageBox.Show("Formula String: " + cells[“A1”].Formula);
//Get the calculated value
MessageBox.Show("Value: " + cells[“A1”].StringValue);
//Save the Excel file
workbook.Save(“e:\test2\outputFile.xlsx”);


For further reference, see the document:
https://docs.aspose.com/display/cellsnet/Using+Formulas+or+Functions+to+Process+Data

Thank you.

3

Thanks for your reply.

Is it possible to convert an absolute reference to a relative reference?

4

Hi,

Well, I think you may easily convert an absolute formula to relative formula reference while specifying a formula.
e.g
//Absolute reference formula
cells[“A1”].Formula = “=SUM($B2:D$2)”;
//Relative reference formula - all you need to remove the ‘$’ while specifying formula
cells[“A1”].Formula = cells[“A1”].Formula.Replace("$", string.Empty);


thank you.

5


OK!

What if the formula contains string which has dollar signs? e.g:


f = "= “” $um o$ do$$ar : “” & SUM($B2:E$5)"

f = Application.ConvertFormula( _
Formula:=f, _
fromReferenceStyle:=xlA1, _
toReferenceStyle:=xlR1C1, _
toAbsolute:=xlRelative, _
relativeTo:=Range(“C4”))

MsgBox f
’ message = " $um o$ do$$ar : " & SUM(R[-2]C[-1]:R[1]C[2])


6

Hi.

Well, you should not replace the $ sign if it is inside quotation mark “”. Replace all other $ signs

7

Are you telling me that I should parse the formula by myself?

8

Hi,

It should be a simple code. I will provide you a code example how to do it.

9

Hi,

Please see this code and its output. You can use this function to remove all $ signs which lie outside quotation marks.

Output:


=“Some Text with $ and $ and $$$ Signs” & $A$1 & A$2 & $B3 &“Some Text with $ and $ and $$$ Signs” & $A$1 & A$2 & $B3 &“Some Text with $ and $ and $$$ Signs” & $A$1 & A$2 & $B3 &“Some Text with $ and $ and $$$ Signs” & $A$1 & A$2 & $B3
=“Some Text with $ and $ and $$$ Signs” & A1 & A2 & B3 &“Some Text with $ and $ and $$$ Signs” & A1 & A2 & B3 &“Some Text with $ and $ and $$$ Signs” & A1 & A2 & B3 &“Some Text with $ and $ and $$$ Signs” & A1 & A2 & B3


[C#]


void Test()

{

string inText = “=“Some Text with $ and $ and $$$ Signs” & $A$1 & A$2 & $B3 &“Some Text with $ and $ and $$$ Signs” & $A$1 & A$2 & $B3 &“Some Text with $ and $ and $$$ Signs” & $A$1 & A$2 & $B3 &“Some Text with $ and $ and $$$ Signs” & $A$1 & A$2 & $B3 “;


string outText = ReplaceDollarSignOutsideQuotationMarks(inText);


System.Diagnostics.Debug.WriteLine(inText);

System.Diagnostics.Debug.WriteLine(outText);

}


string ReplaceDollarSignOutsideQuotationMarks(string inputText)

{

string markerQuote = “XQUOTE”;

string markerDollar = “XDOLLAR”;


//first replace all quotes with some marker

inputText = inputText.Replace(””", “”" + markerQuote);


//Splite on "

string[] tokens = inputText.Split(new string[] { “”" }, StringSplitOptions.None);


string outputText = “”;


for (int i = 0; i < tokens.Length; i++)

{

if (i % 2 == 1) //if i is odd

{

outputText = outputText + tokens[i].Replace("$", markerDollar);

}

else

{

outputText = outputText + tokens[i];

}

}


outputText = outputText.Replace("$", “”).Replace(markerDollar, “$”).Replace(markerQuote, “”");


return outputText;

}



10

So, how to convert relative reference in a A1 style formula to absolute reference?

11

Hi,

Just send the text to the above function, it will remove all $ signs which are outside the quotation marks. Are you getting any problem with the above code? Please clarify.

For example you can call the function like this.

cl.Formula = ReplaceDollarSignOutsideQuotationMarks(cl.Formula);