VB.NET通过Microsoft.Office.Interop.Excel实现Excel告警按日期时间倒序排序

核心问题说明 #

你当前代码存在两个主要问题：

• 仅对日期列（A列）设置了倒序排序，未补充时间列（B列）的排序规则，同日期的告警无法按时间倒序排列
• 使用了带宏的xlsm格式，OpenOffice打开带宏的Excel文件会出现兼容性报错，且你本身不需要宏功能，无需使用该格式

优化方案 #

1. 格式兼容调整 #

将文件存储格式从xlsm改为普通xlsx格式，完全不需要宏支持，OpenOffice可直接打开无报错。

2. 排序逻辑修正 #

补充时间列的倒序排序规则，优先按日期倒序，同日期下按时间倒序，确保最新录入的告警排在最前。

3. 代码稳定性优化 #

• 修正行数计算逻辑，避免新行插入位置错误
• 替换粗暴Kill所有Excel进程的逻辑，改用标准COM对象释放方式，不会影响用户其他打开的Excel文件
• 动态获取排序范围，无需写死最大行号

修正后完整代码 #

Imports System.Runtime.InteropServices
Imports Microsoft.Office.Interop.Excel

Public Sub GuardarAlarmas()
    Dim fileTest As String = "C:\Users\DT SCADA\Desktop\Historicos\Alarmas\Año 2021\Alarmas 2021_11_noviembre.xlsx"
    Dim oExcel As Microsoft.Office.Interop.Excel.Application = Nothing
    Dim oBook As Workbook = Nothing
    Dim oSheet As Worksheet = Nothing
    Dim newRowRange As Range = Nothing
    Dim sortRange As Range = Nothing

    Try
        oExcel = New Microsoft.Office.Interop.Excel.Application()
        oExcel.DisplayAlerts = False

        If File.Exists(fileTest) Then
            oBook = oExcel.Workbooks.Open(Filename:=fileTest, ReadOnly:=False)
        Else
            ' 文件不存在则新建工作簿
            oBook = oExcel.Workbooks.Add()
        End If
        oSheet = CType(oBook.Worksheets(1), Worksheet)

        ' 计算新行插入位置：从A6开始的已有数据行数 + 5（因为A6是第6行，行数从1开始）
        Dim usedRows = oSheet.Range("A6").CurrentRegion.Rows.Count
        Dim newRowIndex As Integer = 6 + usedRows
        If usedRows = 1 AndAlso IsDBNull(oSheet.Range("A6").Value) Then
            ' 首次写入没有数据的情况
            newRowIndex = 6
        End If

        ' 写入告警数据
        oSheet.Range("A" & newRowIndex).Value = Date.Today
        oSheet.Range("B" & newRowIndex).Value = TimeOfDay
        oSheet.Range("C" & newRowIndex).Value = "ACTIVA"
        oSheet.Range("D" & newRowIndex).Value = "DESCRIPCIÓN"

        ' 设置边框
        newRowRange = oSheet.Range("A" & newRowIndex & ":D" & newRowIndex)
        newRowRange.Borders.Color = Color.Black

        ' 排序逻辑
        oSheet.Sort.SortFields.Clear()
        ' 第一排序字段：日期列A倒序
        oSheet.Sort.SortFields.Add(Key:=oSheet.Range("A:A"), SortOn:=XlSortOn.xlSortOnValues, Order:=XlSortOrder.xlDescending, DataOption:=XlSortDataOption.xlSortNormal)
        ' 第二排序字段：时间列B倒序
        oSheet.Sort.SortFields.Add(Key:=oSheet.Range("B:B"), SortOn:=XlSortOn.xlSortOnValues, Order:=XlSortOrder.xlDescending, DataOption:=XlSortDataOption.xlSortNormal)
        
        ' 动态设置排序范围：从A6到D列最后一行有数据的位置
        Dim lastRow As Integer = oSheet.Cells(oSheet.Rows.Count, "A").End(XlDirection.xlUp).Row
        sortRange = oSheet.Range("A6:D" & lastRow)
        
        With oSheet.Sort
            .SetRange(sortRange)
            .Header = XlYesNoGuess.xlYes
            .MatchCase = False
            .Orientation = XlSortOrientation.xlSortColumns
            .SortMethod = XlSortMethod.xlPinYin
            .Apply()
        End With

        ' 保存文件为普通xlsx格式
        oBook.SaveAs(Filename:=fileTest, FileFormat:=XlFileFormat.xlOpenXMLWorkbook, AccessMode:=3, ConflictResolution:=2, Local:=True)
        oBook.Close(SaveChanges:=False)
        oExcel.Quit()
    Catch ex As Exception
        ' 异常情况下释放资源
        If Not oBook Is Nothing Then
            oBook.Close(SaveChanges:=False)
        End If
        If Not oExcel Is Nothing Then
            oExcel.Quit()
        End If
    Finally
        ' 释放所有COM对象，避免Excel进程驻留
        If Not newRowRange Is Nothing Then Marshal.ReleaseComObject(newRowRange)
        If Not sortRange Is Nothing Then Marshal.ReleaseComObject(sortRange)
        If Not oSheet Is Nothing Then Marshal.ReleaseComObject(oSheet)
        If Not oBook Is Nothing Then Marshal.ReleaseComObject(oBook)
        If Not oExcel Is Nothing Then Marshal.ReleaseComObject(oExcel)
    End Try
End Sub

注意事项 #

• 若需要按月自动生成文件，可补充日期拼接逻辑替换固定的文件路径
• 生成的xlsx文件可直接在OpenOffice中打开编辑，无兼容性问题

内容的提问来源于stack exchange，提问作者Samus200