Vb.net操作excel实例

1、我做的是成绩统计软件，一个窗体。俩控件：菜单和打开文件对话框。三个菜单。

2、菜单一：

基本操作。

3、菜单二：

模拟运算。

4、菜单三：

计算成绩。

5、子程序目录：

1、算成绩（m）。2、成绩册（m）。3、横排（h，l）。

4、纵排（h，l）。5、单科统计表（m，n）。6、算分栏（h）。

7、单科汇总（i）。8、填数据（m）。9、清数据（m）。

10、上报表（m）。11、工作表命名（）。12、过成绩（m）。

13、打开（）。14、建空表（m）。15、无英语学校汇总（m）。

16、有英语学校汇总（m）。17、计算器。

编码主要分为主程序和子程序两个模块。

6、Public Class 统计成绩

    Public 调用 = New 子程序()

    Public 电子表格 As Excel.Application  '定义变量

    Public 工作簿 As Excel.Workbook

    Public 工作表 As Excel.Worksheet

    Public 单元格 As Excel.Range

Private Sub 一年级ToolStripMenuItem3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles 一年级ToolStripMenuItem3.Click

        调用.打开()

        调用.算成绩(1)

        调用.过成绩(1)

        调用.单科汇总()

        调用.无英语学校汇总(1)

    End Sub

Private Sub 六年级ToolStripMenuItem3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles 六年级ToolStripMenuItem3.Click

        调用.打开()

        调用.算成绩(6)

        调用.过成绩(6)

        调用.单科汇总()

        调用.有英语学校汇总(6)

    End Sub

End Class

7、Public Class 子程序

    Public 电子表格 As Excel.Application  '定义变量

    Public 工作簿 As Excel.Workbook

    Public 工作表 As Excel.Worksheet

    Public 单元格 As Excel.Range

    Dim gbrs(6, 8) As Integer

    Dim hgrs(6, 8) As Integer

    Public Structure banji

        Dim xkrs As Integer  '学科人数

        Dim xkzf As Integer   '学科总分

        Dim xkjgr As Integer  '学科及格人数

        Dim xkyxr As Integer  '学科优秀人数

        Dim xkjunf As Long   '学科均分

        Dim xkjf As Long     '学科积分

    End Structure

    Public Structure chji

        Dim nji As String  '年级

        Dim xm As String    '校名

        Dim kh As String    '考号

        Dim xs As String    '学生姓名

        Dim ywcj As String   '语文成绩

        Dim sxcj As String   '数学成绩

        Dim yycj As String   '英语成绩

    End Structure

    Dim xx() As String = {"南村1", "南村2", "兴中", "东风", "尧场", "峪头", "普乐塬", "西沟"}

    Dim kmb() As String = {"语文", "数学", "英语"}

    Dim nj() As String = {"", "一年级", "二年级", "三年级", "四年级", "五年级", "六年级"}

    Dim chj(300) As chji   '全局变量

Public Function junf(ByRef rs As Double, ByRef zf As Double) As Double   '算均分的函数

        Return Math.Round(zf / rs, 2)

    End Function

    Public Function jf(ByRef rs As Double, ByRef zf As Double, ByRef jgr As Double, ByRef yxr As Double)  '算积分的函数

        Return Math.Round(zf / rs * 0.6 + jgr / rs * 25 + yxr / rs * 15, 2)

    End Function

    Dim bj(6, 8, 3) As banji

    Dim path As String

    Dim chjjs = 0

Sub 打开()

        Dim openFileDialog1 = New OpenFileDialog()

        openFileDialog1.Title = "请选择文件："

        openFileDialog1.Filter = "所有文件(*.*)|*.*|所有文件(*.*)|*.*"

        openFileDialog1.FilterIndex = 1

        openFileDialog1.RestoreDirectory = False

        If (openFileDialog1.ShowDialog() = DialogResult.OK) Then

            path = openFileDialog1.FileName

            电子表格 = CreateObject("Excel.Application")  '创建EXCEL对象

            工作簿 = 电子表格.Workbooks.Open(path)

            电子表格.Visible = True  '设置EXCEL对象可见

        End If

    End Sub

Sub 算成绩(ByRef m As Integer)

        Dim bglx As String

        Dim q = 0

        For Each gzb In 工作簿.Worksheets

            gzb.activate()

            Dim gzbm = gzb.name

            q = q + 1

            If Left(gzbm, 2) = "语文" Or Left(gzbm, 2) = "数学" Or Left(gzbm, 2) = "英语" Then

                bglx = "单科统计表"

            Else

                bglx = "成绩册"

            End If

            If bglx = "成绩册" Then

                Dim n = 0     '考试人数

                Dim zfl = gzb.Application.WorksheetFunction.Match("总分", gzb.Range(gzb.Cells(2, 1), gzb.Cells(2, 6)), 0)  '总分列

                Dim n2 = gzb.Application.WorksheetFunction.Match("合格人数", gzb.Range(gzb.Cells(2, zfl), gzb.Cells(60, zfl)), 0)  '合格人数所在行数

                Dim n1 = gzb.Application.WorksheetFunction.CountA(gzb.Range(gzb.Cells(3, 1), gzb.Cells(60, 1)))   '非空单元格个数

                If n2 = 0 And n1 > 0 Then n = n1

                If n2 > 0 Then n = n2 - 2

                Dim l = gzb.Application.WorksheetFunction.Match("语文", gzb.Range(gzb.Cells(2, 1), gzb.Cells(2, 5)), 0)  '"语文"所在列数

                bj(m - 1, q - 1, 0).xkrs = n   '语文人数

                bj(m - 1, q - 1, 1).xkrs = n   '数学人数

                bj(m - 1, q - 1, 0).xkzf = gzb.Application.WorksheetFunction.Sum(gzb.Range(gzb.Cells(3, l), gzb.Cells(bj(m - 1, q - 1, 0).xkrs + 2, l)))     '语文总分

                bj(m - 1, q - 1, 1).xkzf = gzb.Application.WorksheetFunction.Sum(gzb.Range(gzb.Cells(3, l + 1), gzb.Cells(bj(m - 1, q - 1, 0).xkrs + 2, l + 1)))  '数学总分

                bj(m - 1, q - 1, 0).xkjgr = gzb.Application.WorksheetFunction.CountIf(gzb.Range(gzb.Cells(3, l), gzb.Cells(bj(m - 1, q - 1, 0).xkrs + 2, l)), ">59")  '语文及格人数

                bj(m - 1, q - 1, 1).xkjgr = gzb.Application.WorksheetFunction.CountIf(gzb.Range(gzb.Cells(3, l + 1), gzb.Cells(bj(m - 1, q - 1, 0).xkrs + 2, l + 1)), ">59")  '数学及格人数

                bj(m - 1, q - 1, 0).xkyxr = gzb.Application.WorksheetFunction.CountIf(gzb.Range(gzb.Cells(3, l), gzb.Cells(bj(m - 1, q - 1, 0).xkrs + 2, l)), ">79")  '语文优秀人数

                bj(m - 1, q - 1, 1).xkyxr = gzb.Application.WorksheetFunction.CountIf(gzb.Range(gzb.Cells(3, l + 1), gzb.Cells(bj(m - 1, q - 1, 0).xkrs + 2, l + 1)), ">79")  '数学优秀人数

                gzb.Cells(bj(m - 1, q - 1, 0).xkrs + 3, l) = bj(m - 1, q - 1, 0).xkzf   '在成绩单上输出成绩

                gzb.Cells(bj(m - 1, q - 1, 1).xkrs + 3, l + 1) = bj(m - 1, q - 1, 1).xkzf                    '语文、数学总分

                gzb.Cells(bj(m - 1, q - 1, 0).xkrs + 4, l) = bj(m - 1, q - 1, 0).xkjgr

                gzb.Cells(bj(m - 1, q - 1, 1).xkrs + 4, l + 1) = bj(m - 1, q - 1, 1).xkjgr                 '语文、数学及格人数

                gzb.Cells(bj(m - 1, q - 1, 0).xkrs + 5, l) = bj(m - 1, q - 1, 0).xkyxr

                gzb.Cells(bj(m - 1, q - 1, 1).xkrs + 5, l + 1) = bj(m - 1, q - 1, 1).xkyxr                  '语文、数学优秀人数

                bj(m - 1, q - 1, 0).xkjf = jf(bj(m - 1, q - 1, 0).xkrs, bj(m - 1, q - 1, 0).xkzf, bj(m - 1, q - 1, 0).xkjgr, bj(m - 1, q - 1, 0).xkyxr)

                bj(m - 1, q - 1, 0).xkjunf = junf(bj(m - 1, q - 1, 0).xkrs, bj(m - 1, q - 1, 0).xkzf)

                gzb.Cells(bj(m - 1, q - 1, 0).xkrs + 6, l) = bj(m - 1, q - 1, 0).xkjf  '语文积分

                bj(m - 1, q - 1, 1).xkjf = jf(bj(m - 1, q - 1, 1).xkrs, bj(m - 1, q - 1, 1).xkzf, bj(m - 1, q - 1, 1).xkjgr, bj(m - 1, q - 1, 1).xkyxr)

                bj(m - 1, q - 1, 1).xkjunf = junf(bj(m - 1, q - 1, 1).xkrs, bj(m - 1, q - 1, 1).xkzf)

                gzb.Cells(bj(m - 1, q - 1, 1).xkrs + 6, l + 1) = bj(m - 1, q - 1, 1).xkjf   '数学积分

                If m > 2 Then

                    bj(m - 1, q - 1, 2).xkzf = gzb.Application.WorksheetFunction.Sum(gzb.Range(gzb.Cells(3, l + 2), gzb.Cells(bj(m - 1, q - 1, 0).xkrs + 2, l + 2)))  '英语总分

                    bj(m - 1, q - 1, 2).xkjgr = gzb.Application.WorksheetFunction.CountIf(gzb.Range(gzb.Cells(3, l + 2), gzb.Cells(bj(m - 1, q - 1, 0).xkrs + 2, l + 2)), ">59")  '英语及格人数

                    bj(m - 1, q - 1, 2).xkyxr = gzb.Application.WorksheetFunction.CountIf(gzb.Range(gzb.Cells(3, l + 2), gzb.Cells(bj(m - 1, q - 1, 0).xkrs + 2, l + 2)), ">79")  '英语优秀人数

                    gzb.Cells(bj(m - 1, q - 1, 0).xkrs + 3, l + 2) = bj(m - 1, q - 1, 2).xkzf

                    gzb.Cells(bj(m - 1, q - 1, 0).xkrs + 4, l + 2) = bj(m - 1, q - 1, 2).xkjgr

                    gzb.Cells(bj(m - 1, q - 1, 0).xkrs + 5, l + 2) = bj(m - 1, q - 1, 2).xkyxr

                    bj(m - 1, q - 1, 2).xkrs = bj(m - 1, q - 1, 0).xkrs

                    bj(m - 1, q - 1, 2).xkjf = jf(bj(m - 1, q - 1, 2).xkrs, bj(m - 1, q - 1, 2).xkzf, bj(m - 1, q - 1, 2).xkjgr, bj(m - 1, q - 1, 2).xkyxr)

                    bj(m - 1, q - 1, 2).xkjunf = junf(bj(m - 1, q - 1, 2).xkrs, bj(m - 1, q - 1, 2).xkzf)

                    gzb.Cells(bj(m - 1, q - 1, 0).xkrs + 6, l + 2) = bj(m - 1, q - 1, 2).xkjf   '英语积分

                End If

                Dim hg = 0               '计算个人总分,统计合格人数

                For i = 3 To bj(m - 1, q - 1, 0).xkrs + 2

                    Dim yuwen = gzb.Application.WorksheetFunction.Sum(gzb.Cells(i, l), 0)

                    Dim shuxue = gzb.Application.WorksheetFunction.Sum(gzb.Cells(i, l + 1), 0)

                    Dim yingyu = gzb.Application.WorksheetFunction.Sum(gzb.Cells(i, l + 2), 0)

                    If m < 3 Then gzb.Cells(i, l + 2) = yuwen + shuxue

                    If (m > 2) Then gzb.Cells(i, l + 3) = yuwen + shuxue + yingyu

                    If m < 3 And yuwen > 59 And shuxue > 59 Then hg = hg + 1 '统计及格人数

                    If m > 2 And yuwen > 59 And shuxue > 59 And yingyu > 59 Then hg = hg + 1

                Next

                If m < 3 Then

                    gzb.Cells(bj(m - 1, q - 1, 0).xkrs + 3, l + 2) = "合格人数"

                    gzb.Cells(bj(m - 1, q - 1, 0).xkrs + 4, l + 2) = hg

                    hgrs(m - 1, q - 1) = hg

                End If

                If m > 2 Then

                    gzb.Cells(bj(m - 1, q - 1, 0).xkrs + 3, l + 3) = "合格人数"

                    gzb.Cells(bj(m - 1, q - 1, 0).xkrs + 4, l + 3) = hg

                    hgrs(m - 1, q - 1 + 1) = hg

                End If

                算分栏(n + 3, zfl)

                gzb.range(gzb.range("a1"), gzb.cells(n + 6, zfl)).HorizontalAlignment = Excel.Constants.xlCenter '水平居中

                纵排(n + 6, zfl)

            End If

        Next

    End Sub

End Class