How to Auto sum the Column

Hello,


I need to Auto sum the column named Amount and want to display its total at the end of the column.The column number is 4. Please help me with the syntax.

As I am new with Aspose, I have tried different syntax’s but none is working. I do not want to create any list. Just display the total of the column.

Any help will be really appreciated.
Thanks.

Hi,

Thanks for your posting and considering Aspose.

Are you working with Microsoft Excel formats and using Aspose.Cells or are you using some other Aspose APIs like Aspose.Words, Aspose.Slides or Aspose.Pdf etc.

Do you want to get the sum of your column? For example if your column A has some values from A1:A6, then you want to get their sum in A7 like =Sum(A1:A6) etc.

Please share with us your requirements in detail. We will look into it and help you asap.

Hello,


I am working on Microsoft Excel format using Aspose.cells. I am getting the data from a stored procedure and I need to get the sum of the last Column in my Worksheet. Since its from stored procedure, I have no idea how many rows it will have so I cannot use the sum() function.

Number of rows depends on the parameters I passed in the stored procedure. The output of the query is my resulting EXCEL file where I need to sum a column.

Hoping to hear from you soon.

Thanks.

Hi,

Thanks for your posting and considering Aspose.Cells.

First you will extract your data from database inside your data tables, then you will import your data into your worksheets as shown in the following article.

( Import Data into Worksheet|Documentation )

Once you have your excel file ready, you can use the following code to create sums of the columns at the end of columns. To simplify everything, I am loading sample excel file from hard drive but you will not do it like this, you will use your existing workbook object after importing your data into worksheet from data table.

I have also attached the 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”);