[SOLVED] Sort a table in ClosedXML while keeping header row

Sort a table in ClosedXML while keeping header row

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?

Solution

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.