This message was posted using Banckle Live Chat 2 Forum
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 workbook object
Workbook workbook = new Workbook(“source.xlsx”);
//Access the first worksheet
Worksheet worksheet = workbook.Worksheets[0];
//Iterate all the data columns
for (int c = 0; c <= worksheet.Cells.MaxDataColumn; c++)
{
Cell lastCellInColumn = null;
//Find the last cell in this column
for (int r = 0; r <= worksheet.Cells.MaxDataRow + 1; r++)
{
if (worksheet.Cells[r, c].Type == CellValueType.IsNull)
{
lastCellInColumn = worksheet.Cells[r, c];
break;
}
}
//Add SUM formula in last cell of the column
string colName = CellsHelper.ColumnIndexToName(lastCellInColumn.Column);
string sumFormula = string.Format("=Sum({0}1:{0}{1})", colName, lastCellInColumn.Row );
lastCellInColumn.Formula = sumFormula;
//Make the last cell bold and fill color yellow
Style style = lastCellInColumn.GetStyle();
style.Font.IsBold = true;
style.Pattern = BackgroundType.Solid;
style.ForegroundColor = Color.Yellow;
lastCellInColumn.SetStyle(style);
}
//Save the workbook
workbook.Save(“output.xlsx”);
Hi,
Hi Nikhil,
Thanks for your posting and using Aspose.Cells.
If you do not want to consider the blank cell as last cell, then you will have to iterate from the bottom toward top. For example, the last row is 1048576, you will have to move upward from this row and then find the first cell that is not empty, this will be the last cell. Once you find the last cell of the column, you can then add a sum formula as shown in my above code.
Hi,
Hi Nikhil,
Thanks for using Aspose.Cells.
After using Cells.EndCellInColumn() method, the above code will be simplified as the following and it will take care of spaces in between the cells of the column.
I have attached the modified source xlsx file and output xlsx file with screenshot for your reference.
C#
//Create workbook object
Workbook workbook = new Workbook(“source.xlsx”);
//Access the first worksheet
Worksheet worksheet = workbook.Worksheets[0];
//Iterate all the data columns
for (int c = 0; c <= worksheet.Cells.MaxDataColumn; c++)
{
//Find the last cell of the column
Cell endCellInColumn = worksheet.Cells.EndCellInColumn((short)c);
Cell lastCellInColumn = worksheet.Cells[endCellInColumn.Row + 1, endCellInColumn.Column];
//Add SUM formula in last cell of the column
string colName = CellsHelper.ColumnIndexToName(lastCellInColumn.Column);
string sumFormula = string.Format("=Sum({0}1:{0}{1})", colName, lastCellInColumn.Row);
lastCellInColumn.Formula = sumFormula;
//Make the last cell bold and fill color yellow
Style style = lastCellInColumn.GetStyle();
style.Font.IsBold = true;
style.Pattern = BackgroundType.Solid;
style.ForegroundColor = Color.Yellow;
lastCellInColumn.SetStyle(style);
}
//Save the workbook
workbook.Save(“output.xlsx”);