How to calculate Total of column and place at the end?

Aspose.Cells Product Family
1

I'm trying to insert rows from the SQL database in the predefined Excel Template. The template is attached herewith. The template has four columns including the “Price” column. How do i calculate total of “Price” column? Requirement is that the total should be placed at the end of all the rows under the “Price” column. Here are the issues I encountered when I tried the following three options:<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" />

1> I can put the SUM formula in D3 which is the template’s “Total” cell and then call the WorkBook.CalculateFormulas() method. But I would not know the range in advance. The number rows may not be always the same.

2> There is CelI.PutValue() function. I can calculate the total of “Price” column somehow in the C# code. But I would not know in which cell “Total” will go because the number rows returned from the database vary. So the “Total” cell’s location keeps changing. Any alternate solution if I already have the total calculated?

3> Let Aspose calculate the total using Cells.SubTotal() function. This funtion also takes CellArea, but again in this case also i need to know the CellArea in advance.

I think there should be some in built solution which i don't know becuase this very common senario in excel.

2

Hi,

Please try this code example, it uses smart marker features. You can add as many values as you want and in the end, it will show you the sum.

Please see both the template.xlsx and template.xlsx.out.xlsx files.

C#


string filePath = @“F:\Shak-Data-RW\Downloads\Template.xlsx”;



WorkbookDesigner designer = new WorkbookDesigner();


designer.Workbook = new Workbook(filePath);



DataTable dt = new DataTable(“data”);


dt.Columns.Add(new DataColumn(“price”, typeof(int)));



DataRow dr;



for (int i = 0; i < 5; i++)

{


dr = dt.NewRow();


dr[“Price”] = i*200;



dt.Rows.Add(dr);


}



designer.SetDataSource(dt);


designer.Process(true);


designer.Workbook.CalculateFormula();


designer.Workbook.Save(filePath + “.out.xlsx”, SaveFormat.Xlsx);

3

Thanks this is perfect.

One more thing. If have several data tables in one dataset and if i set the data source of the designer to DataSet.
designer.SetDataSource(dataset)
then so how the smart markers should set. Is the below setting correct?

&=DataTable1.FieldName1
&=DataTable1.FieldName2

&=DataTable2.FieldName1
&=DataTable2.FieldName2

&=DataTable3.FieldName1
&=DataTable3.FieldName2
&=DataTable3.FieldName3

4

Hi,


Yes, your understanding is correct.

Thank you.