How to group rows and then calculate Total- Sub Total and Grand Total

Aspose.Cells Product Family
1

How to do i group the rows by multiple columns and then calculate Total, Sub Total and then Grand Total. I'm using Smart Marker approach. I'm getting the rows from the database. I have attached two excel files.

The "Sample Data.xslx" shows the incomming data from the database, while the "Expected Result.xslx" shows the exepected output after processing the data. How do i set the Special Markars to get the expected result
Thanks

2

Hi,

I think, that’s not possible. However, I have created a task for it in a database. We will investigate and if possible, will provide you a code example.

This issue has been logged as CELLSNET-30544.

3

Hi,

Please check try the following code with the attached file:

C#


Workbook workbook = new Workbook(@“D:\FileTemp\Sample+Data.xlsx”);


DataTable dt = workbook.Worksheets[0].Cells.ExportDataTable(0, 0, 11, 5,true);

WorkbookDesigner d = new WorkbookDesigner();

d.Workbook = workbook;

dt.TableName = “Report”;

d.SetDataSource(dt);

d.Process();

workbook.Save(@“D:\FileTemp\dest.xlsx”);
4

I tried the template you have given and it does not work. I have attached the complete project here
The project has "Template.xlsx" file and the file's "Copy to Output Directory" is set to "Copy Always" so it will always copy to bin folder.
The "Output.xlsx" file will be created in bin folder too.

Its a simple console app, and just run project and verify the output.xlsx file.

NOTE: Before running the project please verify the output.xlsx file that i have generated otherwise it will get overwritten after running the project

5

Hi,

We will provide a fix for it soon.

6

any updates on this??

i would also like to point out that. when we do the grouping and calculate the subtotal, the resulting excel spreadsheet does not keep the Text in other columns.
For example if in A3 i have "SubTotal" text and in D3 i have the formula as "&=subtotal9:Report.Year&Report.[Asset Class]" then resulting worksheet does not keep
"subtotal" text for each group.

7

Hi<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" />

We would like to know what would be the turnaround time to solve this issue. Days, Months or Year? so that we can plan accordingly and have temporary solution for the time being.

Do we have to wait till the new release of Aspose.Cells framework?

Thanks

8

Hi,


We will try to fix the issue in the next week, we will keep you posted. If we need more time, we will let you know then.

Thanks for your understanding.
9

Hi,


Please try the fixed version v7.0.1.3 with following code and attached template file for your complete reference:<o:p></o:p>

Workbook workbook = new Workbook(Constants.sourcePath + “CellsNet30672.xlsx”);<o:p></o:p>

DataTable dt = workbook.Worksheets[0].Cells.ExportDataTable(0, 0, 11, 5, true);<o:p></o:p>

WorkbookDesigner d = new WorkbookDesigner();<o:p></o:p>

d.Workbook = workbook;<o:p></o:p>

dt.TableName = “Report”;<o:p></o:p>

d.SetDataSource(dt);<o:p></o:p>

d.Process();<o:p></o:p>

workbook.Save(Constants.destPath + “CellsNet30672.xlsx”);<o:p></o:p>

<o:p> </o:p>


10

I tried the example you have attached but its not working as expected. here is what i found

1>we are grouping here by two columns, AssetClass and Year. The total by year appears properly after each Grouped Year, however, the total by Asset Class does not appear after each Grouped Asset Class, infact it appear after each Grouped Year. So the same total appears again.

2>The code exepect to export the table from certain rows and column. In reality we would not know where rows will start. Becuase we may have other information that goes above the table.

11

Hi,


Thanks for your feedback.

1) We will look into it further if we can adjust the data according to your desired “Expected Data.xlsx” file.

2) Yes, surely you need to know where to extract data, you need to know the cells range (regarding certain rows/columns indices). Moreover, how could we come to know that the data above is other information and not the starting of the table or actual data, I am not sure if there is some logic which can be set to differentiate b/w information and actual table’s data. Smart markers feature depends on the source data either in the form of back-end data table (database), variables/arrays or lists, custom objects/types, so you need to sort the data source by your self.

Thank you.
12

Hi,

Please download and try the latest version:
Aspose.Cells for .NET v7.0.1.4

13

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

This message was posted using Notification2Forum from Downloads module by aspose.notifier.
(1)