Dyncamically set column in sum formmulla

Aspose.Cells Product Family
1

Hi Team,

I am use aspose excel export. I am setting formulla in .net.

I want sum of two cells value in third cell. like

excelbook.Worksheets[0].Cells["A2"].R1C1Formula = "=SUM(R1C1,R1C2)";

but i want to set the column dynamically. like in above formulla column C1,C2 are fixed.

now i want pick the column name dynamically , i want to build my formulla as below

excelbook.Worksheets[0].Cells["A2"].R1C1Formula = "=SUM(R1C(Value of Cell B1),R1C(Value of Cell B2))";

so if i enter 1 in B1 cell and 3 in B2 cell then it should sum A1 and C1.i can enter any value in B1 and B2 cell and excel should calculate the sum based upon entered values.

Please suggest how to build this type of formulla.

directly in excel we can do by this way:-

=INDIRECT("A"&B1)+INDIRECT("A"&B2)

so please suggest can i use INDIRECT in r1c1 formulla or suggest some other way.

2

Hi,


I think you may use some CellsHelper class’s static conversion methods accordingly, e.g ColumnNameToIndex, ColumnIndexToName etc.

Thank you.
3

hi Amjad Sahi /team,

directly in excel we can do by this way:-

=INDIRECT(B1&1)+INDIRECT(B2&1)

so please suggest can i use INDIRECT in r1c1 formulla or suggest some other way.

if u can give me example using cellhelper then it will be a great help.

4

Hi,


"=INDIRECT(B1&1)+INDIRECT(B2&1)
so please suggest can i use INDIRECT in r1c1 formulla or suggest some other way.“

Yes, I think you may use it in R1C1 format using Aspose.Cells for .NET. If you have any issue, could you give us a sample Excel file having your desired formula, we can provide details how to set/calculate using Aspose.Cells for NET. See an example code below.

“if u can give me example using cellhelper then it will be a great help.“

See the example code:

Aspose.Cells.Workbook xls = new Aspose.Cells.Workbook();
Aspose.Cells.Worksheet wk = xls.Worksheets[0];
wk.Cells[0, 0].PutValue(32.2);
wk.Cells[0, 1].PutValue(48);
//Summing A1:B1 in R1C1 format
wk.Cells[1, 0].R1C1Formula = “=SUM(R1C” + (CellsHelper.ColumnNameToIndex(“A”) + 1) +”:R1C” + (CellsHelper.ColumnNameToIndex(“B”)+ 1) +”)";
xls.CalculateFormula();
xls.Save(“e:\test2\out.xlsx”);


Thank you.
5

Hi Amjad,

I mailed u the sample excel file. please suggest