Using Excel SubTotals with aspose smart marker


#1

I am populating a spreadsheet with data from a datatable in C# - i.e., I am using the Smart Marker for the datatable like - &=CapGainsDetailsCurrent.SalePrice. This data is categorized by several type of Capital Gains which is determined by another column - &=CapGainsDetailsCurrent.CategoryDescription. I can generate subtotals for the SalePrice based on the CategoryDescription - after the spreadsheet is generated - but I really want to put this into the spreadsheet template so it is generated when the spreadsheet is generated by Aspose. I don’t want to put this in code - i.e., in the C# code - we would rather have the subtotals happen via the template at run time so that we keep the flexibility. Does anyone know how to do this??

We also had problems doing sums of column values - but I realized that all we had to do was put in an extra row in the template sum and than it would generate the sum correctly at run time.


#2

Have you tried the dynamic formula feature in smart markers?

I am not very clear about your need. Could you send me your current template? And please send me a file to show your expected result file. Then I can investigate how to serve your need.


#3

Attached is the current result - from the template which I will send next - in addition to what is here we also want subtotals by the type column.


#4

here is the current template without the subtotals.


#5

I am still not very clear about your need.

I created a template for you. Please try it to see if it can serve your need? If not, please elaborate it.


#6

Please look at the attached spreadsheet - but look at the PreviousYear & CurrentYear tabs. I changed this end result spreadsheet to have the subtotals - compare this one to the first end result spreadsheet that I sent (i.e., CapitalGains.xls) - for the tabs mentioned and you will see the difference/the subtotals. We want to put this into the template so this will dynamically happen when the spreadsheet is generated.


#7

Now I understand your need. I create a new template for you. Please try it to see if it could server your need.

Please look at the PreviousYear tab. When using this template, you have to split your CapGainsDetailsPrior datatable to two datatables according to the “Gain Type”.


#8

This definately works - but is there a way to not split the one datatable into multiple - this would save us a lot of coding (i.e., we could have potentially 30 different gain types say). It works after the spreadsheet is generated but I can’t get it to work dynamically.


#9

If you don't want to split the datatable, I think the above method cannot serve your need. But I think it's easy to use C# code to generate them dynamically.

For example,

int rowCount = dtCapGainsDetailsPrior.Rows.Count;
string priorGainType = null;
int priorIndex = 0;
int startRowIndex = 6;
for(int i = 0; i < rowCount; i ++)
{
DataRow dataRow = dataTable.RowsIdea;
if(i == 0)
{
priorGainType = dataRow["CategoryDescription"];
}
else if(dataRow["CategoryDescription"] != priorGainType)
{
cells.InsertRow(i + startRowIndex);
string startCell = Cells.CellIndexToName(priorIndex + startRowIndex, 6);
string endCell = Cells.CellIndexToName( i + startRowIndex - 1, 6);
cells[i + startRowIndex, 6].Formula = "=SUBTOTAL(9," + startCell + ":" + endCell + ")";

priorGainType = dataRow["CategoryDescription"];
priorIndex += i + 1;
startRowIndex += i + 1;
}
}