Calculate sum for a column dynamically?

[PrathapSV]

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.

How to add a sum of column at the end of that column

Hi, Thanks for your posting and using Aspose.Cells. First, you will have to determine the last cell of the column. You can iterate all cells in a column and find the first cell of type null. This will be the last cell of the column. You can then add SUM formula in that cell. Please see the following code for your reference. I have attached the source xlsx file used in this code and the output xlsx file generated by it and screenshot showing the output for your reference. C# //Create work…

Please suggest a solution as soon as you can.

Regards,
Prathap

[ahsaniqbalsidiqui]

@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");