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