form1.vb

vb.net编辑插值与拟合软件

Imports System.Text
Imports System.Drawing
Public Class Form1
 
    Dim result As String = "" '存储计算结果集的字符串
    Dim MyExcelApp As New Excel.Application
    Dim MyExcelBook As Excel.Workbook
    Dim MyExcelSheet As Excel.Worksheet
    Dim MyChart As Excel.Chart
    Dim MyRange As Excel.Range
    Dim FilePath As String

    Private Sub Form1_Disposed(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Disposed
        Dim myProcesses() As Process
        Dim instance As Process
        myProcesses = Process.GetProcessesByName("Excel")

        If ExitAllExcelApp = True Then '程序退出时默认关闭所有的EXCEL进程
            For Each instance In myProcesses

                instance.Kill()
            Next

            MyExcelBook = Nothing
            MyExcelApp = Nothing '退出程序时关闭Excel进程
            GC.Collect()
        End If
    End Sub

    Private Sub Form1_KeyDown(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyEventArgs) Handles Me.KeyDown
        '隐藏一个功能，当按下F1时可以设置程序退出时不关闭所有的excel进程。
        If e.KeyValue = System.Windows.Forms.Keys.F1 Then
            ExitAllExcelApp = False
        ElseIf e.KeyValue = System.Windows.Forms.Keys.F2 Then
            MyExcelApp.Visible = True
        End If
    End Sub '文件的路径
    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        MessageBox.Show("在程序关闭时会关闭所有的工作簿，请提前保存好工作簿！")
        Dim username As String = My.User.Name
        username = username.Substring(0, username.IndexOf("\"))
        Me.ToolStripStatusLabel2.Text = "你好，" & username & "！ 欢迎使用！ 作者：陈鋆"

        For i As Integer = 1 To 200
            Me.DataGridView1.Rows.Add()
            Me.DataGridView2.Rows.Add()
            Me.ComboBox1.SelectedIndex = 1
        Next
        For j As Integer = 1 To 6
            Me.DataGridView3.Rows.Add()
        Next
    End Sub

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        CloseWorkBook() '关闭当前隐藏的活动的工作薄



        If Me.ComboBox1.SelectedIndex = 1 Then
            My.Forms.请选择插值多项式的阶数.ShowDialog()

        End If




        '预先创建一个隐藏的工作簿，用户需要时再显示出来。
        Try


            MyExcelApp.Workbooks.Add(True)

            MyExcelSheet = MyExcelApp.ActiveWorkbook.ActiveSheet




            MyExcelApp.Cells(1, 1) = "X"
            MyExcelApp.Cells(1, 2) = "Y"


            For row As Integer = 0 To 200
                MyExcelApp.Cells(row + 2, 1) = Me.DataGridView1.Rows(row).Cells(0).Value
                MyExcelApp.Cells(row + 2, 2) = Me.DataGridView1.Rows(row).Cells(1).Value
            Next

            Dim num As Integer = 0 '记录有效数据的个数 
            For i As Integer = 0 To 200
                If Me.DataGridView1.Rows(i).Cells(0).Value = "" Or Me.DataGridView1.Rows(i).Cells(1).Value = "" Then
                    num = i
                    Exit For
                End If
            Next

            Select Case Me.ComboBox1.SelectedIndex
                Case 0

                    Me.DataGridView3.Visible = False
                    Dim x(200) As Single
                    Dim y(200) As Single

                    Dim mFunction As New MyFunction
                    For i As Integer = 0 To 200
                        If Me.DataGridView1.Rows(i).Cells(0).Value <> "" And Me.DataGridView1.Rows(i).Cells(1).Value <> "" Then
                            x(i) = Convert.ToSingle(Me.DataGridView1.Rows(i).Cells(0).Value)
                            y(i) = Convert.ToSingle(Me.DataGridView1.Rows(i).Cells(1).Value)
                        Else
                            Exit For
                        End If
                    Next


                    ReDim Preserve x(num - 1)
                    ReDim Preserve y(num - 1)
                    '插值计算
                    For j As Integer = 0 To 200

                        If Me.DataGridView2.Rows(j).Cells(0).Value <> "" Then
                            Dim ChaZhiX As Single = Convert.ToSingle(Me.DataGridView2.Rows(j).Cells(0).Value)
                            Me.DataGridView2.Rows(j).Cells(1).Value = mFunction.ZhiXian_ChaZhi(x, y, ChaZhiX).ToString(".###")
                        End If
                    Next
                    '在excel中绘图
                    MyChart = MyExcelApp.Charts.Add
                    MyChart.ChartType = Excel.XlChartType.xlXYScatter

                 

                    Dim mySeriesCollection As Excel.SeriesCollection
                    mySeriesCollection = MyChart.SeriesCollection
                    Dim mySeries As Excel.Series


                    MyChart.PlotArea.Width = 560
                    MyChart.PlotArea.Height = 400
                    MyChart.PlotArea.Top = 10
                    MyChart.PlotArea.Left = 80


                    mySeries = mySeriesCollection.Item(1)
                    mySeries.Border.LineStyle = Excel.XlLineStyle.xlContinuous


                    mySeries.XValues = "=Sheet1!R2C1:R201C1"
                    mySeries.Values = "=Sheet1!R2C2:R201C2"
                    mySeries.Name = "=""原始数据"""


                    Dim x1(200) As String
                    Dim y1(200) As String
                    Dim strx As String = ""
                    Dim stry As String = ""
                    Dim temp As Integer = 0
                    For row As Integer = 0 To 200
                        If Me.DataGridView2.Rows(row).Cells(0).Value <> "" And Me.DataGridView2.Rows(row).Cells(1).Value <> "" Then
                            x1(row) = Me.DataGridView2.Rows(row).Cells(0).Value
                            y1(row) = Me.DataGridView2.Rows(row).Cells(1).Value
                        Else
                            temp = row - 1
                            Exit For
                            
                        End If
                    Next
                    strx = "{" & x1(0)
                    stry = "{" & y1(0)
                    For row1 As Integer = 1 To temp
                        strx = strx & "," & x1(row1)
                        stry = stry & "," & y1(row1)
                    Next
                    strx = strx & "}"
                    stry = stry & "}"
                    If strx <> "{}" And stry <> "{}" Then
                        Dim mySeries1 As Excel.Series
                        mySeries1 = mySeriesCollection.NewSeries
                        mySeries1.Name = "插值点"
                        mySeries1.XValues = strx
                        mySeries1.Values = stry
                    End If
                  
                    MyChart.Legend.Top = 340
                    MyChart.Legend.Left = 490
                    MyChart.Location(Where:=Excel.XlChartLocation.xlLocationAsNewSheet, Name:="插值与拟合")
                    MyExcelApp.ActiveWindow.Zoom = 100
                    MyExcelSheet.Name = "原始数据"

                 
                Case 1
                    '获取多项式的系数。
                    '他妈的，.net里对单元格值的引用要绕个弯子。
                    Dim myString As String = ""
                    Me.DataGridView3.Visible = True
                    Dim rng1 As Excel.Range = MyExcelSheet.Range("c2")

                    Select Case jieshu
                        Case 2
                            For j As Integer = 0 To 6
                                Me.DataGridView3.Rows(j).Cells(0).Value = ""
                            Next

                            myString = "=INDEX(LINEST(R2C2:R" & (num + 1).ToString & "C2,R2C1:R" & (num + 1).ToString & "C1^{1,2},TRUE,TRUE),1,1)"
                            rng1.FormulaR1C1 = myString
                            Dim a As Double = rng1.Value
                            Me.DataGridView3.Rows(0).Cells(0).Value = a

                            myString = "=INDEX(LINEST(R2C2:R" & (num + 1).ToString & "C2,R2C1:R" & (num + 1).ToString & "C1^{1,2},TRUE,TRUE),1,2)"
                            rng1.FormulaR1C1 = myString
                            Dim b As Double = rng1.Value
                            Me.DataGridView3.Rows(1).Cells(0).Value = b

                            myString = "=INDEX(LINEST(R2C2:R" & (num + 1).ToString & "C2,R2C1:R" & (num + 1).ToString & "C1^{1,2},TRUE,TRUE),1,3)"
                            rng1.FormulaR1C1 = myString
                            Dim c As Double = rng1.Value
                            Me.DataGridView3.Rows(2).Cells(0).Value = c
                            For i As Integer = 0 To 200

                                If Me.DataGridView2.Rows(i).Cells(0).Value <> "" Then
                                    Dim x As Double = CDbl(Me.DataGridView2.Rows(i).Cells(0).Value)
                                    Me.DataGridView2.Rows(i).Cells(1).Value = (a * x ^ 2 + b * x + c).ToString(".###")

                                End If
                            Next
                          

                        Case 3
                            For j As Integer = 0 To 6