Excel Template Column Sorting

Aspose.Cells Product Family
1

Hello,

I'm using an Excel template with smart markers to generate Excel files.

In my template, I have defined an ascending sort on one of my columns.

Problem: The Excel files which are generated are not taking into account my sort criteria.

Any hint will be appreciated.

Thanks

This message was posted using Aspose.Live 2 Forum
2

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

Please share your sample file and template code to show the issue. We will check it soon.

Thank You & Best Regards,

3

Please find attached my Excel template file and an extract of my code.

Thanks for your help.

4

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

Thank you for sharing the template file and sample code.

We have added your issue in our internal issue tracking system with issue id: CELLSNET-16882. We will investigate your issue and get back to you soon.

Thank You & Best Regards,

5

Hi,

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

In MS Excel, if you input the data to the cell, the sorting will not automatically work, you have to sort them manually. Please sort the data after processing the smart markers.<?xml:namespace prefix = u1 /> Please see the following code:

designer.SetDataSource(table);

designer.Process(true);

Cells cells = designer.Workbook.Worksheets[0].Cells;

CellArea ca = new CellArea();

ca.StartRow = 3;

ca.StartColumn = 0;

ca.EndRow = cells.MaxRow;

ca.EndColumn = cells.MaxColumn;

DataSorter sorter = designer.Workbook.DataSorter;

sorter.Key1 = 2;

sorter.Order1 = SortOrder.Ascending;

sorter.Sort(cells, ca);

Thank You & Best Regards,

6

Hi Aslam,

Thank you for your reply.

I wanted to avoid sorting in code as we are likely to have one Excel template per customer. In fact, each customer could possibly have his own sort criteria. Consequently, sorting in code may not be a flexible solution to us.

Do you have any other suggestions ?

Thanks in advance.
Regards.

7

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

The template file does not store any useful info about sorters. So we cannot automatically sort the data. We think maybe we can support a few sorter smart markers:

1: Ascending:N

2: Descending:N

It means if N is 1, this column is the first key of the sorter. Then we can sort data after processing data source. Will it fit your need?

Thank You & Best Regards,

8

Hi,

Yes it could help us solve our Excel template sorting problem.

We are using Aspose Cells version 4.3.0.0. Will it be possible for Aspose to provide us an update comprising this new template sorting feature for our current version of Aspose Cells ?

Thanks a lot.

Kind regards.

9

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

Well, I am afraid, as per our corporate policy, we will add the new feature in the latest released version. You will have to upgrade to the new version to use your desired feature. I have added your feature request to our internal issue tracking system with issue id: CELLSNET-16928. We will get back to you when the feature gets supported.

Thank You & Best Regards,

10

Hi,

Please try the attached version.

We have supported “Ascending:N” and “Descending:N” for Smart Markers. For example: &=Rows.Field3(ascending:1)

Thank you.

11

Hello,

I’ve tried using your attached version of Aspose.Cells.

I’ve added (Ascending:N) in the sortable columns of my excel template. It seems to me that the sorting is not working.

You will find attached the excel template I’m using.

Any help will be highly appreciated.

Thanks
Sh@h

12

Hi,

Thanks for providing us the template file with sample code.

We will look into your issue soon. We have re-opened your issue again.

Thank you.

13

Hi,

I had attached my template file and an extract of my code in my previous post.

Regards,
Shah

14

Hi,

We have fixed your issue, kindly try the attached version.

Thank you.

15

Hi Amjad,

We have tested the template sorting feature with the version you provided.

The sorting works fine when there is not a mix of sort types (ascending or descending). We tried using the ascending and descending tokens in the same template and this gives us a FormatException. You will find attached my sample template file.

Test cases:
case 1: Template file contains ascending:1 and ascending:2 --> this works
case 2: Template file contains descending:1 and descending:2 --> this
doesn’t work
case 3: Template file contains ascending:1 and descending:1 --> this doesn’t work
case 4: Template file contains ascending:1 and descending:2 --> this doesn’t work

We would be grateful if you could check into this issue.

Thank you in advance for your feedback.

Regards.

16

Hi,

Thanks for your feedback.

We will look into your mentioned issue and get back to you soon.

Thank you.

17

Hi,

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

Please try the attached latest version of Aspose.Cells. We have fixed the issue you mentioned.

Thank You & Best Regards,

18

Hi,

Please download: Aspose.Cells for Java (Latest Version)

We have ported the smart marker feature from .Net version. Now Java version have supported the group and sorting features. You may have a look at the following article:
Smart Markers

19

Need help Sorting alpha numeric data in a column in excel spread sheet???


ex : 1234567a
1234568b
1234569c
1234567b