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

How to add a sum of column at the end of that column
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,


What if any of the cells in between is blank, then this will not work. Like we have data in A1,A2 and A4, but not in the A3. Then it will show the sum of A1 and A2 in A3 and A4 is left out.

Regards,
Nikhil


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,


Moreover, you may utilize Cells.EndCellInColumn() method to find out the last cell (which contains data) in the column, see the sample code below:
e.g
Sample code:


//Get the last cell in the first column (A)
Aspose.Cells.Cell endCell = cells.EndCellInColumn(0);

Hope, this helps a bit.

Thank you.

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”);