I need to calculate the grandtotal for every row with expanded column. When I used “&=&=SUBTOTAL(9,C{r}:C{r})”，the result was not expected:
(1) the range didn’t dynamically adjust，only the first expanded column was summed.
(2) the subtotal and grandtotal rows were not calculated.
Here are the files and code.columnGrandtotal.zip (326.3 KB)
@xuanniao,
Thank you for your query.
I have checked your project but it contains lot of code which is not helpful to us. Please share a simple console application which can be compiled and executed here without any missing reference. Also provide us an expected output file generated by MS Excel for our reference.
columnTotal.zip (173.6 KB)
Here is the simplified code, it can be ran with the command : mvn quarkus:dev.
@xuanniao,
We have understood the requirement but we need to look into it more. We have logged the issue in our database for investigation and for a fix. Once, we will have some news for you, we will update you in this topic.
This issue has been logged as
CELLSJAVA-43186 – Calculate the grandtotal for every row with expanded column
Thank you very much!
I have defined a custom formula, calculated the area for the row grandtotal dynamically, but the subtotal and column grandtotal rows still haven’t been calculated.When calculate subtotals, other subtotals are ignored default, are there some settings can change them to not be ignored.
Here is the output file.output.jpeg (116.2 KB)
@xuanniao,
Thank you for providing more information. We will analyze it and provide our feedback accordingly.
Our product is going to be released, this is the last question before being released, and my colleague is buying your license.So can give me feedback as soon as possible?Thank you very much!
@xuanniao,
Thank you for contacting Aspose support again. This issue is logged too recently and is still in queue for analysis. We have noted your concern and will share our feedback soon.
Please change your smart marker:
i.e,
&=&=SUBTOTAL(9,C{r}:C{r}) in cell “E3”
to:
&=&=SUBTOTAL(9,C{r}:{-1}{r})
and let us know your feedback.
I tried, and it worked. But it still didn’t calculate the grandtotals for the subtotal rows.
I have more complex scenes, and I can use custom formula with enough information to calculate the dynamic range then replace the custom formula with your dynamic formula. But the problem was that the grandtotals for the subtotal rows weren’t been calculated.
Do you have any solution?This is the last question before our production being released, and we have bought your license already.
There are the outputs.
1.jpeg (356.5 KB)
2.jpeg (375.0 KB)
3.jpeg (409.5 KB)
We used the following sample code with the template file (attached), we could get your excepted file (results).
e.g
Sample code:
Workbook workbook = new Workbook(dir + "data.xlsx");
DataTable dt = workbook.Worksheets0.Cells.ExportDataTable(0, 0, 9, 7, true);
dt.TableName = "products1";
Workbook tem = new Workbook(dir + "template.xlsx");
WorkbookDesigner designer = new WorkbookDesigner(tem);
designer.SetDataSource(dt);
designer.Process();
tem.Save(dir + "dest.xlsx");
It seems that you have to process your template file first, please save the processed file and compare with attached template file, if you still have any issue, please post your processed middle file. We will check it further.
files1.zip (20.8 KB)
sample.zip (204 KB)
Here are files and code.
The simpleSample folder contains simplified code in AsposeCellsExample folder which can be ran with the command “sudo mvn quarkus:dev” then visit 127.0.0.1:8080 in browser, you can select the template.xlsx and get the output result.The simpleSample folder also contains the template file, middle file, output file and expected file.
The complexMiddle contains three middle sheets for three different complex scenes. Of course the code is different from simplified code, if you need it, tell me.
Thanks for providing us sample code, template file, middle file, output file and expected file.
We will evaluate it and get back to you soon.
In the upcoming fix, we will add a new method, i.e., WorkbookDesigner.setRepreatFormulasWithSubtotal() method to indicate whether repeating formulas with subtotal row. And, please check the attached template file, the function in E3 should be SUM because subtotal will ignore cells which formula contains subtotal. The dest.xlsx file in the zipped archive is generated by the upcoming next fix (which will be published in the next few days).
Hope, the (upcoming) new fix will suit your needs.
files1.zip (14.0 KB)
I tried with SUM, it also ignored cells which formula contained subtotal. Or do you mean that I should use SUM instead of SUBTOTAL in the upcoming next fix?And what’s the planned date for the the upcoming new fix to be released?
Yes, we meant that.
The date is not final as new fixes are published (in the forums) when ready (after we have completed the QA and incorporated relevant enhancements). Anyways, you may expect the fix within next 3-5 days or so.
Thank you very much! Other functions like AVERAGE will also calculate the subtotal row, only SUBTOTAL function ignores the subtotal row, right?
Please try our latest version/fix: Aspose.Cells for Java v20.5.3 (attached)
aspose-cells-20.5.3-java.zip (7.1 MB)
Your issue should be fixed in it. Please use designer.setRepreatFormulasWithSubtotal(true); with the fix.
Let us know your feedback.