Column Sorting?

[ADefWebserver]

I don't see that this component supports sorting.
I am willing to code this and submit a PR.
Before I do so, I wanted to make sure this was not already planned.

My proposed plan:

• Allow an event to be added to headers (the way validators can be added)
• When clicked the Header and the reversible sort direction would be sent
• Custom code can then decide how to handle the sort request (for example reload the sheet with the selected sort)

Note: I am working on pulling data from a database: https://github.com/ADefWebserver/BlazorDatasheet/blob/main/src/BlazorDatasheet.SharedPages/Pages/DataEditor.razor

[anmcgrath]

Hey @ADefWebserver, happy that you're looking into this and what you're doing looks good.

I do want to add sorting at some stage. Ideally by default it would sort a column/range by value similar to excel.

I do like your suggestion of adding custom code to handle the sort request. I can see this being implemented by having a BeforeSortRequest event on the sheet, which can cancel the default sort.

By adding event to headers, do you mean a ColumnHeaderMouseDown event or something similar?

[ADefWebserver]

Yes, I am suggesting to add an event to ColumnHeaderMouseDown. After reading your reply I can see that the it is more complicated than I originally thought:

• How do you handle redo/undo (in some cases someone wants this on and in other cases they don't)
• There is a default sort?

Perhaps I should just wait for you to do it?

[anmcgrath]

I think that exposing a ColumnHeaderMouseDown event is a good start. What may help you (and others) that want to do custom sorting is to allow templating of the column/row headings. Then you can implement a custom click event + show carets for sort direction. What do you think?

And yes the undo/redo of default sorting makes it complicated to implement. We've also got to move formulas and cell formatting around with range sorting, which makes it even more complicated. Sorting is something I'm happy to look at soon.

[ADefWebserver]

@anmcgrath I will wait until you have a chance to look at this. The deeper I get into the code I realize that this is way more complicated than people realize 😊 I plan to spend the next two weeks getting code and a blog post out that allows people to update a database. Just properly implementing that is taking weeks of trial and error. For example I was applying custom validation rules by row rather than column. There is a 1000% percent performance difference.

[anmcgrath]

No problem, I'll hopefully start on it in the next couple of weeks.

It doesn't help that I haven't got any real documentation on how to use certain features - sorry! Glad you are figuring it out and are using it. Thanks for giving feedback.

Yes it's good to be aware that validation, formatting and conditional formatting can be applied to ranges rather than individual cells.

This project is intended to be more of a spreadsheet rather than data viewing but it's good to have the flexibility to do both.

[ADefWebserver]

This project is intended to be more of a spreadsheet rather than data viewing

Having formulas is a very powerful feature and an incredible achievement 👍

With that said, having a Blazor control that allows my end-users to edit 100,000 rows at a time (yes I tested this) and save to Microsoft Fabric database is a feature even Microsoft doesn't offer.

My end users are used to using Excel. The fact that I can now give them a Blazor web page that allows then to copy and paste to Excel and back, and edit any value quickly and easily is something only this control can do.

[anmcgrath]

I've added a default implementation for range sorting. Please have a look in the new example and see what you think.

I've added a BeforeRangeSort event to the sheet where the default sort is cancel-able. This is where you could implement your own sorting logic.

Next step for me is to add some UI to support sorting - I'd like to work on a context menu as well as allowing custom column heading components which should get you to where you first suggested!

[ADefWebserver]

I've added a default implementation for range sorting. Please have a look in the new example and see what you think.

Yes this works perfectly. Thanks!

I've added a BeforeRangeSort event to the sheet where the default sort is cancel-able. This is where you could implement your own sorting logic.

Yes this is a good design.

Next step for me is to add some UI to support sorting - I'd like to work on a context menu as well as allowing custom column heading components which should get you to where you first suggested!

Yes this is a good design because sorting is not as simple and strait forward as I originally thought. There are so many use cases that this will be the only way to adress them all.

[anmcgrath]

I've added ability to add a custom heading template, which you can set with the template on the datasheet.

I've also added a context menu & column menu with some default actions. I'd like to make this easy to customise so interested in ideas on the best way to do this.

[ADefWebserver]

Thanks! I will look at it this weekend.

Also, I am about to post a blog about using AI to update data in this control. If you add Discussions to this Github repo the community can post links :)

[ADefWebserver]

I posed the article here:
Using OpenAI to Update A Blazor Excel Worksheet

[anmcgrath]

@ADefWebserver that is amazing, nice work!

[ADefWebserver]

@anmcgrath - I am able to wire up an event to stop the default sorting (looks great by the way 👍🏽). If I do this sort:

I can get the selected colum from Left:

But how do I get the sort direction?

Thanks!

[anmcgrath]

You can get the sort order from the sort options. Sort options is a list of columns to sort by (you can sort by a column, then by another if two rows match) which specifies the column index (relative to the left of the region) and the sort order (ascending or descending).

[ADefWebserver]

@anmcgrath - For me SortOptions is always null?

ADefWebserver@fb8654e

[anmcgrath]

Yeah you are right it wasn't working as intended. This PR #74 should have fixed it.

[ADefWebserver]

@anmcgrath - Yes it is now fixed! Thank You!