Group rows using smart marker

Aspose.Cells Product Family
1

Dear Aspose.Cell,


I’m having a problem with grouping rows using smart marker. I don’t know to group row and calculate subtotal.

Please, You can help me.

Best regards.
2

Hi,


Please see the document/ article on Smart Markers for your reference:
http://www.aspose.com/docs/display/cellsnet/Smart+Markers

Note: Please see the description and examples under sub-topic “Grouping Data” for your complete reference on how to group data in Smart Markers for your needs.

Thank you.

3

Hi Amjad Sahi,


Thank for your response.

I saw Aspose.Cells’s document. I have just seen that data have grouped vertically, but i have expected that it will be grouped horizontally.

I’m having a problem that i want to subtract subtotal after data fill smart marker but it hasn’t done.

Can you provide me a solution, please ?

Best regards.
4

Hi,


Well, you got to insert/update the Subtotal formulas in the cells by yourself accordingly (e.g you may try to use Cell.Formula attribute to insert/ update the formulas) after your markers are processed and data is filled into the cells. I am afraid, Smart Markers feature might not support custom needs as it follows certain standards and has its own architecture and implementation (please see the document suggested for complete details).

Thank you.
5

Hi Amjad Sahi,


Thank for your response!

Might you confuse my mind?

Let me explain you again. I calculated subtotals formular using smart marker(&=subtotal9). I want to execute subtraction for that subtotals (ex: =G5-E5).

I saw the Aspose.Cells document but all reports of my project that data have grouped horizontally. That’s also my expectation.

I hope you can help me resolve that problem.

Thank and regards.
6

Hi,


Well, the summary rows are generated dynamically when the markers are processed, so we cannot specify Smart markers dynamic/repeat formulas. Anyways, as I said, we can input formulas (as per your needs) to subtract values from summary records later on when the markers are processed and cells are filled with data. Could you provide us a simple console application (you may use dynamic DataTable in your codes) that you are trying to accomplish your task, we will try to adjust your codes (via Aspose.Cells APIs) to suit your needs. Also provide your template Excel file, output file by Aspose.Cells. Moreover, you may provide us your expected file that you may manually create in MS Excel. All this will help us to understand you and figure out your issue soon.

Thank you.
7

Dear Amjad Sahi,


I give you three files that include ExcelTemplate, AsposeOutput, MyOutput(expectation) for issues about groupping data by smart marker.

I hope you will provide me a solution.

Best regards


8

Hi,


Thanks for the template files.


Well, I have tested your scenario/ case using the following sample code with your template file a bit. I have checked your template files as well. I think it would be difficult to do exactly the same output as per your “myOutput.xlsx” file. Smart Markers’ grouping data feature has its own syntax and architecture to place data/ information in the cells. In your desired output file, under your each grouping row/field “PaymentType” you need data from “StockSale” field. I did try to insert a new row above in the template file and place/ move the first grouping marker there above all but this won’t work either.

&=Model.PaymentType(Group:normal)
&=Model.StockSale &=Model.OpeningBalance &=Model.Arised &=Model.Payment &=&=TEXT(D{r}+E{r}-F{r}, "#,###.000")
&=subtotal9:Model.Order(Label:"Tổng", LabelPosition:-2) &=subtotal9:Model.Order &=subtotal9:Model.Order &=subtotal9:Model.Order

I think you may try to use the marker "&=Model.PaymentType(Group:normal, skip:1)" instead but then it will still not provide you the exact formatting as you need your each grouping record should be placed in one complete row and its details records should be placed under it accordingly.
e.g
Sample code:

Workbook workbook = new Workbook(@"e:\test2\ExcelTemplate.xlsx");


WorkbookDesigner designer = new WorkbookDesigner(workbook);

DataTable dt = workbook.Worksheets[1].Cells.ExportDataTable(1, 1, workbook.Worksheets[1].Cells.MaxRow + 1, workbook.Worksheets[1].Cells.MaxColumn + 1, true);

dt.TableName = "Model";

designer.SetDataSource(dt);

designer.Process();


designer.Workbook.Save(@"e:\test2\out1.xlsx");



Smart Markers have different style and formatting regarding the grouping records and placements of summary rows etc.as you may get reference from the document under "Grouping data" sub-heading:
http://www.aspose.com/docs/display/cellsnet/Smart+Markers

I think may be you need to adopt manual approach to accomplish your task instead of using only smart markers where you have to insert new rows at certain places and format them as per your needs. Then, you need to move each grouping record to it or something like that etc..

We will further check if we could find some good way or better workaround to do that. And if we found, we could get back to you.

Thank you.