Excel-窗体项目-学习成绩查询-09-ListView排序及格式化显示

最新推荐文章于 2020-12-22 20:49:31 发布

Coding的巴扎黑

最新推荐文章于 2020-12-22 20:49:31 发布

阅读量688

点赞数 1

文章标签： Excel vba 窗体

本文链接：https://blog.csdn.net/zishendianxia/article/details/106105971

版权

微信公众号原文

系统：Windows 7
软件：Excel 2016

本系列讲讲Excel中使用窗体实现一个简单的小项目：学习成绩查询
今天继续介绍控件ListView，介绍如何按列排序和格式化显示

Part 1：实现功能

根据成绩进行排序
新增日期/实数/百分数/货币，格式化显示

按成绩排序

Part 2：代码

主代码

Private Sub outputSearchV_Click()
    Set ctrlStudent = Me.Controls("outputStudentNameV")
    studentName = ctrlStudent.Value
    
    Set ctrlCourse = Me.Controls("outputCourseNameV")
    courseName = ctrlCourse.Value
    
    Set ctrlExam = Me.Controls("outputWhichExamV")
    exam = ctrlExam.Value
    
    If studentName = "" And courseName = "" And exam = "" Then
        MsgBox "请输入查询条件"
        Exit Sub
    End If
    
    Set ctrl = Me.Controls("outputListView")
    
    ' 清空原标题
    ctrl.ColumnHeaders.Clear
    ' 加上标题
    ctrl.ColumnHeaders.Add , , "序号", 30, lvwColumnLeft
    ctrl.ColumnHeaders.Add , , "姓名", 50, lvwColumnLeft
    ctrl.ColumnHeaders.Add , , "科目", 60, lvwColumnCenter
    ctrl.ColumnHeaders.Add , , "第几次模拟考", 30, lvwColumnRight
    ctrl.ColumnHeaders.Add , , "成绩"
    ctrl.ColumnHeaders.Add , , "日期"
    ctrl.ColumnHeaders.Add , , "实数"
    ctrl.ColumnHeaders.Add , , "百分数"
    ctrl.ColumnHeaders.Add , , "货币"

    ctrl.View = lvwReport
    ctrl.FullRowSelect = True
    ctrl.Gridlines = True
    
    '清空其它数据
    ctrl.ListItems.Clear

    Set shtDb = ThisWorkbook.Worksheets("学生成绩db")
    maxRow = shtDb.Cells(Rows.Count, "B").End(xlUp).Row
    
    inputNum = 1
    flag = 0
    arr1 = Array("2020-01-05", "2020-02-05", "2020-03-05", "2020-04-05", "2020-05-05")
    arr2 = Array(0.563, 0.1, 0.63932, 0.5862, 2)

    For i = 2 To maxRow Step 1
        existStudent = shtDb.Cells(i, "B")
        existCourse = shtDb.Cells(i, "C")
        existExam = CInt(shtDb.Cells(i, "D"))
        
        check = 条件检测(existStudent, existCourse, existExam, studentName, courseName, exam)
        
        If check = True Then
            existNote = shtDb.Cells(i, "E")
            
            Set Item = ctrl.ListItems.Add()
            Item.Text = inputNum
            Item.SubItems(1) = existStudent
            Item.SubItems(2) = existCourse
            Item.SubItems(3) = existExam
            Item.SubItems(4) = existNote

            Item.SubItems(5) = Format(arr1(inputNum - 1), "YYYY-MM-DD")
            Item.SubItems(6) = Format(arr2(inputNum - 1), "#0.000")
            Item.SubItems(7) = Format(arr2(inputNum - 1), "0.00%")
            Item.SubItems(8) = Format(arr2(inputNum - 1), "Currency")
            x = Format(arr2(inputNum - 1), "Currency")
            Debug.Print (x)
            inputNum = inputNum + 1

            flag = 1
        End If
    Next i
    
    
    If flag = 1 Then
        ctrl.Sorted = True
        ctrl.SortKey = 4
        MsgBox "查询完毕，请从下表查看结果"
    Else
        MsgBox "未查询到满足条件的结果"
    End If
End Sub

代码截图

Debug.print输出结果

条件检测函数

Function 条件检测(existStudent, existCourse, existExam, studentName, courseName, exam)
    result = True

    If studentName <> "" Then
        If studentName <> existStudent Then
            result = False
        End If
    End If
    
    If courseName <> "" Then
        If courseName <> existCourse Then
            result = False
        End If
    End If
    
    If exam <> "" Then
        If CInt(exam) <> existExam Then
            result = False
        End If
    End If
    
    条件检测 = result
    
End Function

代码截图

Part 3：部分代码解读

Item.SubItems(5) = Format(arr1(inputNum - 1), "YYYY-MM-DD") ，控制输出的格式为年-月-日
Item.SubItems(8) = Format(arr2(inputNum - 1), "Currency")，输出货币格式。格式化输出的核心在于Format函数，Debug.Print的输出效果是一致的