Calculate sum for a column dynamically in Excel using Aspose.Cells for .NET in C#

Aspose.Cells Product Family
1

Hi,

Please find the attached excel sheet(template6.zip (9.7 KB)).
I want to calculate the Sum and Display it in the third row
right after Total column. But i am not sure how many records user may add in this case.
It may be 10 or 3000 not sure. Also, in between blank cells may come as well.

For ex: Year 2070 will have 10 values and 2065 will have 3000 values.
I have entered some sample data for those two columns for your reference.

So hope, here i need to write a sum formula/macro, which calculates dynamically, as and when he adds a value in a cell for a particular column.

I have referred the below link and still not able to find a way.

Please suggest a solution as soon as you can.

Regards,
Prathap

2

@PrathapSV,
You may give a try to the following sample code and share the feedback.

Workbook workbook = new Workbook(path + "template6.xlsm");
//Access the first worksheet
Worksheet worksheet = workbook.Worksheets[0];
//Iterate all the data columns
for (int c = 4; c <= worksheet.Cells.MaxDataColumn; c++)
{
    //Find the last cell of the column
    Cell endCellInColumn = worksheet.Cells.EndCellInColumn((short)c);
    if (endCellInColumn.Row == 1)    continue;
    //Add SUM formula in third row of the column
    string colName = CellsHelper.ColumnIndexToName(endCellInColumn.Column);
    string sumFormula = string.Format("=Sum({0}4:{0}{1})", colName, endCellInColumn.Row + 1);
    Cell sumCell = worksheet.Cells[colName + "3"];
    sumCell.Formula = sumFormula;
    //Make the sum cell bold
    Aspose.Cells.Style style = sumCell.GetStyle();
    style.Font.IsBold = true;
    style.Pattern = Aspose.Cells.BackgroundType.Solid;
    style.Font.ArgbColor = System.Drawing.Color.Black.ToArgb();
    sumCell.SetStyle(style);
}
//Save the workbook
workbook.Save(path + "output_peyton.xlsx");