Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Sorting by 1 or more Columns #78

Closed
BritishFan opened this issue Dec 6, 2017 · 7 comments

Comments

Projects
None yet
5 participants
@BritishFan
Copy link

commented Dec 6, 2017

I see this has not been updated since couple of years here : https://epplus.codeplex.com/workitem/14791

Wish to know any plans for implementing Sorting since for files already having data with some formatting its difficult, I want to sort few data based on 3 columns please guide?

Also the present Sort in place in EPPLUS does not work for me. when I try adding r.Sort(...) ;

Error
CS1061 'ExcelRange' does not contain a definition for 'Sort' and no extension method 'Sort' accepting a first argument of type 'ExcelRange' could be found (are you missing a using directive or an assembly reference?)

@JanKallman

This comment has been minimized.

Copy link
Owner

commented Dec 6, 2017

There's an implementation in version 4.5 beta, so try it out and see if it works for you.

@BritishFan

This comment has been minimized.

Copy link
Author

commented Dec 6, 2017

thanks forthe quick response I got the reference for sort. I started using it but wonder how to use it for more than 1 columns like below:
r.Sort(Key1: r[1, 3], Order1: Excel.XlSortOrder.xlDescending, Key2: r[1, r.Columns.Count], Order2: Excel.XlSortOrder.xlDescending, Key3: r[1, 2], Order3: Excel.XlSortOrder.xlDescending, Header: Excel.XlYesNoGuess.xlNo, OrderCustom: 1, MatchCase: false, Orientation: Excel.XlSortOrientation.xlSortColumns, DataOption1: Excel.XlSortDataOption.xlSortNormal, DataOption2: Excel.XlSortDataOption.xlSortNormal, DataOption3: Excel.XlSortDataOption.xlSortNormal);

Is it possible to implement this interop code to epplus?

Thanks JanKallman

@JanKallman

This comment has been minimized.

Copy link
Owner

commented Dec 6, 2017

The syntax for more than one column is like this...

       /// <summary>
        /// Sort the range by value
        /// </summary>
        /// <param name="columns">The column(s) to sort by within the range. Zerobased</param>
        /// <param name="descending">Descending if true, otherwise Ascending. Default Ascending. Zerobased</param>
        /// <param name="culture">The CultureInfo used to compare values. A null value means CurrentCulture</param>
        /// <param name="compareOptions">String compare option</param>
        public void Sort(int[] columns, bool[] descending=null, CultureInfo culture=null, CompareOptions compareOptions=CompareOptions.None)

So,

                //Sort column B, descending then A ascending
                ws.Cells["A:C"].Sort(new int[] { 1,0 }, new bool[] { true,false });

                //Sort column A only, descending
                ws.Cells["A:C"].Sort(0, true);

                //Sort column B, Ascending
                ws.Cells["A:C"].Sort(1);

                //Sort column A, ascending
                ws.Cells["A:C"].Sort();

Let me know if you have any problems

@JanKallman

This comment has been minimized.

Copy link
Owner

commented Mar 30, 2018

Added to 4.5.1

@kiquenet

This comment has been minimized.

Copy link

commented Jul 24, 2018

not same order if I do manually in Excel (order by a Column Index, Expand the selection) and sort it using code (Sort).

@LeslieMurphy

This comment has been minimized.

Copy link

commented Dec 3, 2018

Any chance to add support for custom sort order? Excel supports a custom sort list, which controls the order of the field being sorted. My particular use case is to sort on Employee ID as column 1, then Relationship as column 2 with the order {"Employee", "Spouse", "Child"}. I can easily do the sort in my program logic before populating the worksheet, so I have an easy workaround.

@JacobTBralish

This comment has been minimized.

Copy link

commented May 20, 2019

The order I am getting from using sort is different then the order I get while sorting manually on a worksheet. Select cells to sort > Sort > Sort by column x. Is there any solution to this issue? @kiquenet did you ever solve this issue?

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.