Sort a table in ClosedXML while keeping header row

Question

I've tried a few methods of sorting in ClosedXML but they've all had the same results: the first row of data becomes the new header in excel.

Here's the code I've used so far:

Dim dtChangedScores As New DataTable
dtChangedScores.Columns.Add("Name")
dtChangedScores.Columns.Add("Old Score")
dtChangedScores.Columns.Add("New Score")

Dim dr As DataRow = dtChangedScores.NewRow
dr("Name") = "aaa"
dr("Old Score") = "bbb"
dr("New Score") = "bbb"
dtChangedScores.Rows.Add(dr)
'etc....

Dim wb = New XLWorkbook
Dim ws = wb.Worksheets.Add(dtChangedScores, "Scores")
Dim rangeTable = ws.Table(0).RangeUsed()
rangeTable.Sort()

dtChanged scores is a datatable

How can I keep the original header row while sorting the data?

The column names you are adding are not the same as when you are filling in the rows. You have "Name" as one, and then "CU Name" as the other. — JohnPete22, Oct 28 '19 at 17:37
@JohnPete22 Edited. My data table is building fine, this is just edited and simplified code. — rgorr, Oct 28 '19 at 17:41
What does this have to do with closedxml? Please correct your tags. — Mary, Oct 28 '19 at 21:46
@Mary this is using ClosedXML. That's where XLWorkbook comes from, and that's what I'm sorting using ClosedXML's methods — rgorr, Oct 29 '19 at 00:48
@rgorr Sorry, displaying my ignorance. I see now it is available as Nuget pkg. — Mary, Oct 29 '19 at 02:44

score 2 · Accepted Answer · answered Oct 28 '19 at 21:43

If you can sort your data before you take it to Excel then the following code should do it. I displayed the data so you can see how it worked.

Private Sub OPCode()
    'Create a DataTable
    Dim dtChangedScores As New DataTable
    dtChangedScores.Columns.Add("Name")
    dtChangedScores.Columns.Add("Old Score")
    dtChangedScores.Columns.Add("New Score")
    'Add some data
    dtChangedScores.Rows.Add("Joe", 22, 30)
    dtChangedScores.Rows.Add("Pete", 19, 20)
    dtChangedScores.Rows.Add("Bob", 17, 20)
    'You can use the .Sort method of the DataView
    dtChangedScores.DefaultView.Sort = "Name"
    DataGridView1.DataSource = dtChangedScores
End Sub

If you need to do this in Excel. Work in Excel for a bit to see how it is done. You will see that you select all the data except the header row. Maybe record a macro then translate then translate it to vb.net.

Sort a table in ClosedXML while keeping header row

1 Answers1

Linked