Day37 - 小型应用系统(2)

今日学习目标

  • 掌握数据增删改查操作
  • 实现系统设置存储
  • 完成用户偏好管理

知识点

### 1. CRUD操作
- Create：插入新记录
- Read：查询和检索
- Update：更新数据
- Delete：删除记录
### 2. 设置存储技术
- 工作表存储：专用设置表
- 文件存储：INI/JSON配置文件
- 注册表存储：系统级设置
### 3. 用户偏好管理
- 默认值设置
- 界面主题切换
- 快捷方式配置

今日代码

' 数据查询
Function QueryData(keyword As String) As Collection
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim i As Long
    Dim result As New Collection

    Set ws = Worksheets("数据")
    lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row

    For i = 2 To lastRow
        If InStr(1, ws.Cells(i, 1).Value, keyword) > 0 Then
            result.Add ws.Rows(i).Value
        End If
    Next i

    Set QueryData = result
End Function

' 数据更新
Sub UpdateData(rowNum As Long, fieldName As String, newValue As Variant)
    Dim ws As Worksheet
    Dim colNum As Long

    Set ws = Worksheets("数据")
    colNum = GetColumnNumber(fieldName)

    ws.Cells(rowNum, colNum).Value = newValue
End Sub

' 数据删除
Sub DeleteData(rowNum As Long)
    Dim ws As Worksheet
    Set ws = Worksheets("数据")
    ws.Rows(rowNum).Delete
End Sub

' 保存设置到工作表
Sub SaveSettings()
    Dim ws As Worksheet
    Set ws = Worksheets("设置")

    ws.Range("A1").Value = "DefaultDept"
    ws.Range("B1").Value = Me.cmbDefaultDept.Value

    ws.Range("A2").Value = "AutoSave"
    ws.Range("B2").Value = Me.chkAutoSave.Value

    ws.Range("A3").Value = "Theme"
    ws.Range("B3").Value = Me.cmbTheme.Value
End Sub

' 加载设置
Sub LoadSettings()
    Dim ws As Worksheet
    Set ws = Worksheets("设置")

    If ws.Range("B1").Value <> "" Then _
        Me.cmbDefaultDept.Value = ws.Range("B1").Value

    Me.chkAutoSave.Value = ws.Range("B2").Value
    Me.cmbTheme.Value = ws.Range("B3").Value
End Sub

练习题

  1. 实现按多条件筛选数据
  2. 添加数据导出功能
  3. 实现设置导入导出