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.RangePrivate Sub 一年级ToolStripMenuItem3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles 一年级ToolStripMenuItem3.Click 调用.打开() 调用.算成绩(1) 调用.过成绩(1) 调用.单科汇总() 调用.无英语学校汇总(1) End SubPrivate Sub 六年级ToolStripMenuItem3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles 六年级ToolStripMenuItem3.Click 调用.打开() 调用.算成绩(6) 调用.过成绩(6) 调用.单科汇总() 调用.有英语学校汇总(6) End SubEnd Class

7、Public Class 子程序 Public 电子表格 As E垓矗梅吒xcel.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 = 0Sub 打开() 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 SubSub 算成绩(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 SubEnd Class