在保留标题行的同时对 ClosedXML 中的表格进行排序 [英] Sort a table in ClosedXML while keeping header row

问题描述

我在 ClosedXML 中尝试了几种排序方法，但它们都有相同的结果:第一行数据成为 Excel 中的新标题.

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 分数是一个数据表

dtChanged scores is a datatable

如何在对数据进行排序时保留原始标题行?

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

推荐答案

如果您可以在将数据导入 Excel 之前对其进行排序，那么以下代码应该可以做到.我显示了数据，以便您可以看到它是如何工作的.

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

如果您需要在 Excel 中执行此操作.在 Excel 中工作一下，看看它是如何完成的.您将看到您选择了除标题行之外的所有数据.也许录制一个宏然后翻译然后将其翻译成 vb.net.

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.

这篇关于在保留标题行的同时对 ClosedXML 中的表格进行排序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！