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
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.
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
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:
[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]
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,
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.