美文网首页
软件总结整理(1)

软件总结整理(1)

作者: 貌大侠 | 来源:发表于2015-10-29 17:41 被阅读0次

    软件总结整理(1)

    标签(空格分隔): 程序猿


    源代码

    Private Sub Command1_Click()
    
    Dim cn As New ADODB.Connection
    Dim rs As New ADODB.Recordset
    Dim sql As String
    Dim text1text As String
    Dim text2text As String
    Dim temp As String
    Dim count As Integer
    Dim count2 As Integer
    
    Dim newxls As New excel.Application
    Dim newbook As New excel.Workbook
    Dim newsheet As New excel.Worksheet
    Set newxls = CreateObject("Excel.Application")
    Set newbook = newxls.Workbooks.Open("" & App.Path & "\金具统计.xlsx")  '创建工作簿
    Set newsheet = newbook.Worksheets(1) '创建工作表
    newsheet.Range("A1:IV65536").Clear
    
    Dim aa() As Integer '单个图号,每个金具的数量,最后叠加成bb
    Dim bb() As Integer '所有图号,每个金具的数量
    Dim ee() As Integer '单个图号,每个金具的数量(未乘个数)
    Dim cc() As String '与aa对应,表示相应金具的型号
    Dim dd() As String '与aa对应,表示相应金具的名称
    Dim temppp() As String
    ReDim temppp(0 To DataCombo1.UBound)
        
    cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source= " & App.Path & "\jjk.mdb;Persist Security Info=False"
    sql = "select * FROM jjk"
    rs.Open sql, cn
    count = rs.Fields.count
    ReDim aa(count)
    ReDim bb(count)
    ReDim cc(count)
    ReDim ee(count)
    ReDim dd(count)
    ReDim jj(count)
    cn.Close
      
    For i = 0 To DataCombo1.UBound  '所有复合框循环,实现累加
        temp = Trim(DataCombo1(i).Text)
        If temp <> "" Then
            cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source= " & App.Path & "\jjk.mdb;Persist Security Info=False"
            sql = "select * FROM jjk where sno='" & temp & "'"
            rs.Open sql, cn
            count = rs.Fields.count
                If rs.EOF <> True Then
                    For ii = 2 To count - 1 '所有字段循环,计算每个金具的数量
                        aa(ii) = 0
                        aa(ii) = rs.Fields(ii) * Val(Text1(i).Text)
                        cc(ii) = rs(ii).Name
                        ee(ii) = rs.Fields(ii)
                            If ee(ii) <> 0 Then
                                temppp(i) = temppp(i) & cc(ii) & "=" & ee(ii) & vbCrLf
                            End If
                        bb(ii) = bb(ii) + aa(ii)
                    Next ii
                    If DataCombo1(i).Text <> "" Then
                        text2text = text2text & vbCrLf & "第" & i + 1 & "个串号:" & DataCombo1(i).Text & "  共计" & Text1(i) & "串,每串对应金具及数量如下:" & vbCrLf & temppp(i)
                    End If
                Else
                    newbook.Close (True)
                    newxls.Quit
                    Set newxls = Nothing
                    MsgBox "第" & i + 1 & "串图号不存在,或者该串图号输入错误,请检查!"
                    Exit Sub
                End If
                cn.Close
        End If
    Next i
           
    For ii = 2 To count - 1
    cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source= " & App.Path & "\jjk.mdb;Persist Security Info=False"
    sql = "select [" & cc(ii) & "] FROM jjm"
    rs.Open sql, cn
    On Error Resume Next
        If rs.EOF <> True Then
            dd(ii) = rs(0)
        End If
    cn.Close
    Next ii
        
    ss = 3
    Text2.Text = ""
    For h = 1 To count '循环所有字段,如果总数不等于0,就在excel中记录下来
        If bb(h) <> 0 Then
            newsheet.Cells(ss, 1) = ss - 2
            newsheet.Cells(ss, 2) = dd(h)
            newsheet.Cells(ss, 3) = cc(h)
            newsheet.Cells(ss, 4) = bb(h)
            text1text = text1text & dd(h) & "  " & cc(h) & "=" & bb(h) & vbCrLf
            ss = ss + 1
        End If
    Next h
    
    newsheet.Cells(1, 1) = "金具数量统计表"
    newsheet.Cells(2, 1) = "序号"
    newsheet.Cells(2, 2) = "金具名称"
    newsheet.Cells(2, 3) = "金具型号"
    newsheet.Cells(2, 4) = "金具数量"
    newsheet.Cells(1, 1).Font.Bold = True
    newsheet.Range(newsheet.Cells(1, 1), newsheet.Cells(1, 4)).Merge
    newsheet.Range(Cells(2, 2), Cells(100, 3)).Columns.EntireColumn.AutoFit
    Range(newsheet.Cells(1, 1), newsheet.Cells(1, 4)).HorizontalAlignment = xlCenter
    Range(newsheet.Cells(1, 1), newsheet.Cells(1, 4)).VerticalAlignment = xlCenter
    
    Text2.Text = "金具统计报告" & vbCrLf & "金具统计情况如下:" & vbCrLf & text1text & vbCrLf & vbCrLf & "请仔细核对串号对应金具及数量是否正确!" & text2text
    
    newbook.Close (True)
    newxls.Quit
    Set newxls = Nothing
    
    End Sub
    

    技巧总结

    新建并引用EXCEL表

    打开和关闭具体参见代码
    打开之后记得关闭,先关闭文件,再关闭程序,再释放内存

    redim的使用

    Dim aa() As Integer
    ReDim aa(count)

    重新定义会刷新数组的值,如果原值不变则需要:

    Redim Preserve MyArray(15)

    数据集的操作

    数据集的值:rs.fields(i)或者rs(i)或者rs("U-10")
    数据集的字段名:rs(i).name
    数据集的字段总数:rs.fields.count

    几个SQL语句

    sql = "select * FROM jjk where sno='" & temp & "'"
    sql = "select [" & cc(ii) & "] FROM jjm"

    On Error Resume Next的使用

    jjk里面有个字段,但是jjm里面没有,在运行sql = "select [" & cc(ii) & "] FROM jjm"时出错,此时可以运用这个语句,忽略这个问题,不会对结果造成影响。

    exit sub 的使用

    要是运行到一半想退出,可运行这条语句

    在EXCEL中的操作语言

    • 写入excel
    ss = 3
    For h = 1 To count 
        If bb(h) <> 0 Then
            newsheet.Cells(ss, 1) = ss - 2
            newsheet.Cells(ss, 2) = dd(h)
            newsheet.Cells(ss, 3) = cc(h)
            newsheet.Cells(ss, 4) = bb(h)
            ss = ss + 1
        End If
    Next h
    
    • 合并、加粗、居中、自动变化大小等
    newsheet.Cells(1, 1).Font.Bold = True
    newsheet.Range(newsheet.Cells(1, 1), newsheet.Cells(1, 4)).Merge
    newsheet.Range(Cells(2, 2), Cells(100, 3)).Columns.EntireColumn.AutoFit
    Range(newsheet.Cells(1, 1), newsheet.Cells(1, 4)).HorizontalAlignment = xlCenter
    Range(newsheet.Cells(1, 1), newsheet.Cells(1, 4)).VerticalAlignment = xlCenter
    

    控件数组的个数

    控件数组的个数:DataCombo1.UBound

    Dim temppp() As String
    ReDim temppp(0 To DataCombo1.UBound)
    

    相关文章

      网友评论

          本文标题:软件总结整理(1)

          本文链接:https://www.haomeiwen.com/subject/wkhfhttx.html