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");
Cookie Notice
To provide you with the best experience, we use cookies for personalization, analytics, and ads. By using our site, you agree to our cookie policy.
Enables storage, such as cookies, related to analytics.
Sets consent for sending user data to Google for online advertising purposes.
Sets consent for personalized advertising.
Cookie Notice
To provide you with the best experience, we use cookies for personalization, analytics, and ads. By using our site, you agree to our cookie policy.
More info
Enables storage, such as cookies, related to analytics.
Enables storage, such as cookies, related to advertising.
Sets consent for sending user data to Google for online advertising purposes.
Sets consent for personalized advertising.
Cookie Notice
To provide you with the best experience, we use cookies for personalization, analytics, and ads. By using our site, you agree to our cookie policy.
More info
Enables storage, such as cookies, related to analytics.
Enables storage, such as cookies, related to advertising.
Sets consent for sending user data to Google for online advertising purposes.