We're sorry Aspose doesn't work properply without JavaScript enabled.

Free Support Forum - aspose.com

Issues with Smart Markers and grouping data

Hi:

I have done a bit of extensive testing related to Smart Markers and I found some issues related to grouping.

This is the program I used (I referenced 7.5.0.1):

static void Main(string[] args)
{

TestGrouping();
Console.WriteLine(“Done!”);
Console.ReadKey();
}

public static void TestGrouping()
{
WorkbookDesigner designer = new WorkbookDesigner();
int num = 1;
//Open a designer spreadsheet containing smart markers
designer.Workbook = new Workbook(String.Format(@“C:\temp\spreadsheets\TestSmartMarkersDesigner{0}.xlsx”, num.ToString(“D2”)));

//Set the data source for the designer spreadsheet
designer.SetDataSource(CreateDataTable());

//Process the smart markers
designer.Process();

designer.Workbook.Save(String.Format(@“C:\temp\spreadsheets\TestSmartMarkersOutput{0}.xlsx”, num.ToString(“D2”)));
}

Basically change the value of num to change the example.

The names have the patterns: TestSmartMarkersDesigner.xlsx and the output spreadsheet TestSmartMarkersOutput.xlsx where num is a two digit number: 01, 02, …

So here is the list:

01.

a. If I add a top border to the row that contains the group subtotal, somehow the top border gets replicated on row 7. Not too sure why. I only wanted that border to separate the subtotal line from the group rows.

b. I wanted to add a grand total for the Total Price column, however as you can see, the total field in cell E4 (the designer spreadsheet) disappeared from the output. There is something funky going on here because the rows that belong to the group don’t seem to push down any content below the line with the group subtotal (I did all sorts of experimentation with the insert, shift cells down options - they don’t seem to have any effect). The interesting thing is that, if I want a cell placed below the grouping rows to appear in the output (for instance in the Test Price column), I have to place it far enough from the subtotal line. In this example, if I place any value in column E between rows 4 and 8 (in the designer spreadsheet), that value will be wiped out. If I place the value in E9 it will show up in the output just below the last group subtotal. I haven’t been able to figure out a pattern here, but it seems to depend on the number of rows in the DataTable. I think this is a bug.

c. How can I add a grand total for the Total Price column using the smart markers? Using just =SUBTOTAL(9, E2:E2) doesn’t expand properly (it expands to =SUBTOTAL(9,E2:E6) ) in the final spreadsheet to perform the subtotal only on the groups rows and not their subtotal rows.

02. In the Smart Markers documentation there is one syntax that I haven’t been able understand what it does, because it doesn’t seem to do anything useful.

It is this one:

&=[Order Details].Quantity(subtotal9:Order Details.OrderID),

Please take a look at TestSmartMarkersDesigner02.xlsx. I tried different permutations but I am not too sure how I am supposed to use this. Could you please provide a meaningful sample, and, if it turns out that I didn’t miss something obvious, could you please change the documentation to clarify its usage?

Thank you




Hi,

Thanks for providing template files.

We have analyzed your issues a bit. Well, you are mixing lot of things in smart markers which might not be supported or valid for Smart Markers at the moment. For example, "&=&=C{r}*D{r}~(shift cells down)", in this marker, there is no such smart options there in the Smart markers (please see the topic for your reference about parameters and other examples for smart markers: http://www.aspose.com/docs/display/cellsnet/Smart+Markers). Also, we might not support "subtotal9...." options after using dynamic formulas in Grouping data, e.g "&=TestGrouping.Quantity(subtotal9:TestGrouping.Price)".

We also request you to kindly provide us your excepted files here, it may help us a bit.

Any ways, for the sake of further/complete evaluation or investigation, I have logged a ticket with an id "CELLSNET-41780" for your issues/requirements. Our concerned developer will look into it soon. Once we have any update on it, we will let you know here.


Thank you.

Hi:

Thank you for looking into this.

Just a few points:

1. About ‘shift cells down’, I am quoting from your own documentation (http://www.aspose.com/docs/display/cellsnet/Smart+Markers):


shift - Shift down or right, creating extra rows or
columns to fit data. The shift parameter works the same way as in
Microsoft Excel, when you select a range of cells, right-click and
select Insert. Specify shift cells down, shift cells right and other options. The shift parameter fills the same function for vertical/normal (top to bottom) or horizontal (left to right) smart markers.


The documentation says: ‘Specify shift cells down’. This is what I did in my sample. I tried to force the aspose smart markers engine to push the rows down instead of overwriting them (it overwrites the first rows below the subtotal line). If this was not the intended use please clarify in the documentation whether these parameters are available or not. I did a fair bit of testing and ‘shift cells down’ or ‘shift cells right’ don’t have any effect.

I attached another simpler sample here in TestSmartMarkersDesigner03.xlsx. If ‘shift cells right’ worked in the same way as Shift Cells Right works in Excel (as your documentation states!) the content of Cell A2 with the Cell below should be pushed to the right, not pushed down.

One can also ask what does the ‘shift’ parameter do since ‘shift cells right’ and ‘shift cells left’ don’t seem to do anything, and what is the difference between ‘shift’ and ‘insert’?

Also in the attached designer #1 if you remove ~(shift cells down) you get the same behavior.


2. It is not clear for me what you meant when you said:
We also request you to kindly provide us your excepted files here, it may help us a bit.

What files do you need me to send you? I attached the designer spreadsheets as well as the output from them. My tests were manual, i.e. I tried different combinations of parameters in the spreadsheet, then I run the program I posted here. I didn’t write test cases (I don’t have much time), if this is what you wanted. I documented in this post the issues I found.

I think smart markers are a very cool feature and I am just trying to understand exactly how it works to a fine detail to make the best out of it.

Thank you again for your excellent support!



One more thing, about the &=TestGrouping.Quantity(subtotal9:TestGrouping.Price)
syntax.

Take a look at the attached spreadsheets #4. The designer spreadsheet is pretty much the same as your first example in the Grouping section of the smart markers documentation (the exception is the Item column).

In the documentation there is a screenshot of the output from the first designer. If you look carefully, in each group the last row in the group contains totals, the total quantity and the total unit price (total unit price doesn’t make too much sense in my mind).

That was not what was generated in my case. If you look at output #4 there is no row with group totals.

OK, I thought may it’s the Item column. I eliminated it and see designer and output #5.

There are no group total rows inserted in per your example in the documentation.

So, please fix the documentation to reflect what the library does.

Thank you



Hi,


[quote user="costab"]

1. About 'shift cells down', I am quoting from your own documentation (http://www.aspose.com/docs/display/cellsnet/Smart+Markers):


shift - Shift down or right, creating extra rows or columns to fit data. The shift parameter works the same way as in Microsoft Excel, when you select a range of cells, right-click and select Insert. Specify shift cells down, shift cells right and other options. The shift parameter fills the same function for vertical/normal (top to bottom) or horizontal (left to right) smart markers.


The documentation says: 'Specify shift cells down'. This is what I did in my sample. I tried to force the aspose smart markers engine to push the rows down instead of overwriting them (it overwrites the first rows below the subtotal line). If this was not the intended use please clarify in the documentation whether these parameters are available or not. I did a fair bit of testing and 'shift cells down' or 'shift cells right' don't have any effect.

I attached another simpler sample here in TestSmartMarkersDesigner03.xlsx. If 'shift cells right' worked in the same way as Shift Cells Right works in Excel (as your documentation states!) the content of Cell A2 with the Cell below should be pushed to the right, not pushed down.

One can also ask what does the 'shift' parameter do since 'shift cells right' and 'shift cells left' don't seem to do anything, and what is the difference between 'shift' and 'insert'?
[/quote]

Sorry for the confusion. Actually the description mentioned in the document refers to MS Excel option i.e., in MS Excel, right-click on a range of cells, now click Insert... and then you may choose any option e.g Shift cells right, Shift cells left etc. I have updated the document accordingly now, see the updated document for your reference:
http://www.aspose.com/docs/display/cellsnet/Smart+Markers

Just for your information, we have only "shift" parameter which does top to bottom shifting of cells for normal markers and left to right shifting of cells for horizontal markers(markers having "horizontal" parameter).


[quote user="costab"]
2. It is not clear for me what you meant when you said:
We also request you to kindly provide us your excepted files here, it may help us a bit.

What files do you need me to send you? I attached the designer spreadsheets as well as the output from them. My tests were manual, i.e. I tried different combinations of parameters in the spreadsheet, then I run the program I posted here. I didn't write test cases (I don't have much time), if this is what you wanted. I documented in this post the issues I found.
[/quote]

Well, we requested you to kindly create or update the output Excel files with your desired data organized and formatted accordingly for your needs, you may manually update or create your desired files in MS Excel for different cases. It will help us to understand your needs more precisely, so we could look into it.

Thank you.

Thanks for clarifying.

Here is case 1 with three spreadsheets.

‘Some cell’ should always show up after the last row no matter how much data is in the DataTable object.

The border on the second group should not be there in my opinion.

The other questions that I had are:

1. How would you define a cell that computes the grand total for the Total Price column and that would be placed right under the subtotal of the last group ?

2. Please clarify with a sample, if possible, what the &=TestGrouping.Quantity(subtotal9:TestGrouping.Price) syntax does.

Unless I am missing something here, I think the documentation is outdated and it doesn’t reflect what the library does.

Thank you



Hi,


Thanks for providing another set of files including template file, output file and expected output file here.

We have logged your mentioned two problems (‘Some cell’ should always show up after the last row and the border on the second group should not be there ) against your existing issue id “CELLSNET-41780” into our database. Our concerned developer will look into it. Once we have any update on it, we will let you know here.

For your queries:

1) I am not sure if this would be logically possible for grouping data (the cells on which we apply subtotals). The reason is we implement dynamic summary rows based on the data and insert extra rows while processing the markers to fill data into it accordingly. For example, we insert a new row at row number 4 to make the ‘Some cell’ placed on E5 cell. We place a simple formula (i.e…, =SUM(E2:E3)) on E4 cell. Now when the markers are processed (surely there would be added new blank rows based on the source data with grouping attributes accordingly, the formula on E4 cell would also be extended to become E2:E12 that might be placed on E13 cell, but it would also include summary or subtotal rows to count the grand total which is wrong I think (it should not include summary rows). Anyways, this is my assumption and our concerned developer will also look into it as well if we can support it or have some other workaround for it. We will let you know about it as well.

2) "&=TestGrouping.Quantity(subtotal9:TestGrouping.Price)"
Well, this marker denotes that you are grouping data by Price column and are applying subtotals on Quantity column (you have to set the Price field as e.g “&=TestGrouping.Price(group:…, )” when specifying its relevant marker in the template file) etc.

Thank you



Hi,

Thanks for your posting and using Aspose.Cells for .NET.

Please download and try this fix: Aspose.Cells for .NET v7.5.0.4.

And please provide the excepted file for 02.xlsx,we will check it soon.

Hi, I confirm that 1 has been fixed.

I don’t have a file for case #2. Can you please be more specific about what you’d be looking for?

I just wanted to understand what was the effect of those marker expressions(i.e. &=TestGrouping.Quantity(subtotal9:TestGrouping.Price) ). Maybe you can provide a designer sample that I can use to see what output it produces.

The other question was whether it was possible to generate a grand total for a column, but again I don’t have a sample.

Thanks a lot for the great support!

The issues you have found earlier (filed as CELLSNET-41780) have been fixed in this update.


This message was posted using Notification2Forum from Downloads module by Aspose Notifier.