Smart Marker: Multiple groups with skip overlaps grand total row

[Abhijeetk]

HI

I want to get the subtotal based on two columns & after each subtotal want to add a blank row. To achieve this i use (group:repeat,skip:1) for two column but then it won’t shows the grand total row.
I am using Aspose.cells for .net 17,2,0,0 (able to replicate on latest version),

Replication Steps:

1. Use the Northwind.mdb Microsoft Access database and extract data from the table named “Order Details”.

2. Place the following smart markers:
   &=[Order Details].Order ID(group:repeat,skip:1),
   &=[Order Details].Product Name,
   &=[Order Details].Quantity,
   &=[Order Details].Unit Price,
   &=&=C{r}*D{r}
   &=[Order Details].Order ID,
   &=[Order Details].[Product Name]
   &=&=IF(A{-1}="",“Total For “&G{-3},””),
   &=&=IF(H{-1}<>"",“Total For “&H{-1},””),
   &=subtotal9:Order Details.Order ID&[Order Details].[Product Name] into D6 & D6
   “Grand Total”,
   and =SUBTOTAL(9,E5:E5)
   into A5, B5, C5, D5, E5, G5, H5, A6, B6, (D6 & E6), A7 and E7 respectively.
   worksheet of the SmartMarker_Designer.xls file (13.9 KB)

3. Use the code as per aspose document of “subtotalN” topic (https://docs.aspose.com/pages/viewpage.action?pageId=5018144)
   and command as
   Dim cmd As OleDbCommand = New OleDbCommand(“Select [Order Details].[Order ID], Products.[Product Name], [Order Details].Quantity, [Order Details].[Unit Price] from [Order Details] inner join Products on Products.ID = [Order Details].[Product ID]”, con)

The grand total line position was wrong and overlap by total line. (Check the text ‘GT’ in column ‘G’)
Output (15.7 KB)

Any Idea?

[amjad.sahi]

@Abhijeetk,

Thanks for the screenshots and details.

Well, when the Smart Markers are processed, Aspose.Cells will first insert blank rows and then put data into the respective cells. Also, since you are mixing the Grouping data (formula) markers with your standard formula, i.e., “=SUBTOTAL(9,E5:E5)”, so I am not sure if you could escape from overlapping issue.

Anyways, could you provide us template Excel file (containing Smart Markers), output file by Aspose.Cells APIs and your expected file with data (you may manually edit the output file (by Aspose.Cells APIs) in MS Excel and re-save it), we will check it soon.

Thank you.

[Abhijeetk]

Please find attachmentsTemplate.zip (28.6 KB)

[amjad.sahi]

@Abhijeetk,

Thanks for the template file and output file (by Aspose.Cells).

Could you also provide us your expected file here, it will help us to evaluate your issue precisely.
By the way, I spotted the following issues on row 218:

1. Cell C218 has unnecessary shading color of the Grand total row.
2. The constant “GT” is also pasted on G218 cell.

Could you confirm the above issues or you find other issue, kindly elaborate.

Thank you.

[Abhijeetk]

Expected file is same as output file. The grand total should be displayed at the last row.
I think you caught the issue, row 218 is showing as a grand total row hence having shading and constant value GT. Which is wrong this grand total row should display in last.
Please check your row calculation logic.

[amjad.sahi]

@Abhijeetk,

Thanks for your confirmation.

Please share the source MS Access database file (e.g .mdb) used in your code. We will log a ticket for your issue with the artifacts and details to be fixed in the APIs.

Thank you.

[Abhijeetk]

As i mention in replication steps it is a standard “Northwind.mdb” access database. Same as you used in your examples.
Please find attached the same.Northwind.zip (646.9 KB)

[amjad.sahi]

@Abhijeetk,

Thanks for the database file.

After an initial test, I am able to reproduce the issue as you have mentioned. But as you are evaluating the Grouping data (formula) markers with your standard formula for Grand total row at the bottom, i.e., “=SUBTOTAL(9,E5:E5)”, so I am not sure if you could even use it with Smart Markers. It is best that you could put that formula row manually in the code (Grand total row) after processing the Smart markers and data is pasted into the cells.
e.g
Sample code:

 //Create a connection object, specify the provider info and set the data source.
            OleDbConnection con = new OleDbConnection("provider=microsoft.jet.oledb.4.0;data source=e:\\test2\\Northwind.mdb");
            //Open the connection object.
            con.Open();
            //Create a command object and specify the SQL query.
            OleDbCommand cmd = new OleDbCommand();
            cmd.CommandText = "Select [Order Details].[Order ID], Products.[Product Name], [Order Details].Quantity, [Order Details].[Unit Price] from [Order Details] inner join Products on Products.ID = [Order Details].[Product ID]";
            cmd.Connection = con;
            DataSet ds = new DataSet();
            //Create a data adapter object.
            OleDbDataAdapter da = new OleDbDataAdapter(cmd.CommandText, con);
            //Specify the command.
            da.SelectCommand = cmd;
            da.Fill(ds, "Order Details");
            //Create WorkbookDesigner object.
            WorkbookDesigner wd = new WorkbookDesigner();
            //Open the template file (which contains smart markers).
            wd.Workbook = new Workbook("e:\\test2\\SmartMarker_Designer.xlsx");
            //Set the datatable as the data source.
            wd.SetDataSource(ds);
            //Process the smart markers to fill the data into the worksheets.
            wd.Process();
            //Save the excel file.
            wd.Workbook.Save("e:\\test2\\out1.xlsx");

Anyways, I have logged a ticket with an id “CELLSNET-45598” for your issue. We will look into it soon.

Once we have an update on it, we will let you know here.

Thank you.

[Abhijeetk]

Amjad, The source file design by end user so i can’t add the grand total row after process.
Thanks for logging ticket. Where i can track this ticket status?

[amjad.sahi]

@Abhijeetk,

You cannot check the status or track issues as it is our internal system. You always have to ask us to check the update or status of the issue. Please spare us sometime to evaluate your issue thoroughly, then we could update you here.

Thank you.

[amjad.sahi]

@Abhijeetk,

Please check attached modified template file and generated file. Does it fit your need? Please confirm. We think we should add subtotal rows together without skipping empty rows.

After your confirmation, we will make amendments in Smart Markers feature.
smart.zip (26.1 KB)

Thank you.

[Abhijeetk]

@Amjad_Sahi

Please share the source MS Access database fil

HI Amjad,

Our clients are having huge reports so they want to add one empty row after each subtotal for readability. It will be great if you manage to add blank rows after each total/subtotal
In you "dest.xlsx’ fine i have add expected gap marked as yellow. dest.zip (23.8 KB)

One more request, for safer side please check for grouping with skip on 3 or 4 columns will also work well.

[amjad.sahi]

@Abhijeetk,

Thanks for sharing your desired file.

I have logged your concerns against your issue “CELLSNET-45598” into our database for product team considerations.

Once we have any new information, we will share it with you.

Thank you.

[amjad.sahi]

@Abhijeetk,

Please try our latest version/fix: Aspose.Cells for .NET v17.8.2:
Aspose.Cells for .NET v17.8.1 (.NET 2.0)
Aspose.Cells for .NET v17.8.1 (.NET 4.0)

Your issue should be fixed in it.

Let us know your feedback.

Thank you.

[Abhijeetk]

Thanks a lot Amjad, Excellent, it works as we expected.

[amjad.sahi]

@Abhijeetk,

Thanks for your feedback.

Good to know that your issue is sorted out by the new fix/version. Feel free contact us any time if you have further queries or issue, we will be happy to assist you soon.

Thank you.

[amjad.sahi]

The issues you have found earlier (filed as CELLSNET-45598) have been fixed in Aspose.Cells for .NET 17.9.