Hello,
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,
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#
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”);