Sort order not working correctly

Aspose.Cells Product Family
,
1

Using Aspose.Cells v19.11.0

As an example I have an output (Column D = Row number, Column E = Status, Column F = Country) I want to sort the output first by Status then by Country and the row number is a dynamic formula. When I have this formula &=&=IF(D{-1}="#",1,D{-1}+1) in column D then &=Assets.Status(ascending:1) and
&=Assets.Country(ascending:2) in columns E and F respectively the sort order does not work.

If I remove the formula from column D or move it further along to the right so that it is after the columns I am sorting by (column G for example) it appears to work fine - it only ignores the sort order when there is a dynamic formula in a column to the left of a ā€˜ascending:nā€™ column

Is this a bug in Aspose or has it been fixed in a later version already?

2

@CraigCoy,
You may try the latest version Aspose.Cells for .NET and check if still issue is there as we test and resolve issues in the latest versions only. If issue is observed with the latest version, share your runnable console application, template file, program output file and expected output file for our reference. We will analyze the scenario here and share our feedback.

3

@ahsaniqbalsidiqui,
We have tried the latest version of Aspose.Cells for .NET and found the issue is still present. Can you please confirm what you mean by ā€˜runnable console applicationā€™?

4

@CraigCoy,

Please create a standalone VS.NET console (demo) application using the latest version/fix to reproduce the issue on our end. Please also include template Excel file (if any), program output file and your expected output file for our reference (you may create your expected file manually in MS Excel). We will check your issue soon.

By the way, you may also try data sorting after your have processed the markers and data is filled into Excel sheet, see the document for your reference.

PS. please use dynamic dataset/datatable in code for the markers to remove any inter dependencies to external database/data source, so we could execute your sample code seamlessly. Also, please zip your project and files prior attaching here.

5

Hello,

https://www.dropbox.com/s/n6xldau9lkdyua0/SampleExcelReport.zip?dl=0

On this link is a sample demo application. Once extracted you can see the Template, output and expected files as well as the executable here: SampleExcelReport\SampleExcelReport\bin\Release

Thanks,

6

@CraigCoy,

I checked your project and yes, your issue is due to dynamic formula (Smart Marker) in the first column. Dynamic formula will contradict data sorting ability of markers as it will clash with it. For your needs, you should not use dynamic formula marker for auto-numbers. Instead you may use shared formula feature after you have processed all other markers in the template file. Please remove the dynamic formula in A2 cell and re-save your file and then use the following sample code (just see the line in bold) it will work for your needs:
e.g
Sample code:

ā€¦
using (var workbook = new Workbook(ā€œExcelReportTestingTemplate1.xlsxā€))
{
var designer = new WorkbookDesigner
{
Workbook = workbook
};

            SetDataSources(reportData, designer);

            designer.Process();

workbook.Worksheets[0].Cells[ā€œA2ā€].SetSharedFormula("=IF(A1-1="",1,A1+1)", workbook.Worksheets[0].Cells.MaxDataRow, 1);

            var stream = new MemoryStream();
            var fileFormat = SaveFormat.Xlsx;
            designer.Workbook.Save(stream, fileFormat);
            stream.Position = 0;

            workbook.Save("ExcelTestReport (Output).xlsx", fileFormat);
7

So is it possible to fix the Dynamic Formula? Our use case involves using smart markers to sort the data within the template to reduce the amount of work required from a developer to update the code. If this needs to be specified in the code then it means this is not really viable in the long term.

8

@CraigCoy,

As data sorting might have some conflicts with dynamic formulas (Smart Markers) but still we need to evaluate it. I have logged a ticket with an id ā€œCELLSNET-47708ā€ for your issue. We will look investigate your issue in details.

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

9

@CraigCoy,

This is to inform you that we have fixed your issue now. We will soon provide you the fixed version after performing QA and incorporating other enhancements and fixes.

1 Like
10

The issues you have found earlier (filed as CELLSNET-47708) have been fixed in this update. This message was posted using Bugs notification tool by simon.zhao