Imports System.Data.OracleClient
Public Class excel_orcal
Inherits System.Windows.Forms.Form
Dim app As Excel.Application
Dim ws As Excel.Worksheet
Dim wb As Excel.Workbook
Dim j As Integer
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Me.OpenFileDialog1.ShowDialog()
Me.TextBox1.Text = Me.OpenFileDialog1.FileName
app = CreateObject("Excel.Application")
app.Visible = True
wb = app.Workbooks.Open(TextBox1.Text)
ws = app.Worksheets(1)
ws.Activate()
System.Runtime.InteropServices.Marshal.ReleaseComObject(app)
app = Nothing
GC.Collect()
'sqlstr = ws.Cells(1, 2).value
'sqlstr = app.WorksheetFunction.Max(ws.Range("B2:F10"))
' Me.Label1.Text = sqlstr
End Sub
Private Sub create_table()
For j = 1 To 100
sqlstr = sqlstr + ws.Cells(1, j).value & " " & "VARCHAR2(1000),"
If ws.Cells(1, j).value = "" Then
Exit For
End If
Next
Dim a As String
Dim b As Integer
input = InputBox("请输入需要键的表名", , ws.Name)
b = sqlstr.Length - 17
a = sqlstr.Substring(0, b)
sqlstr = "select * from user_all_tables where table_name='" & input & "'"
If Get_ds(sqlstr).Tables(0).Rows.Count = 0 Then
MsgBox("该表存在,不需要创建?", MsgBoxStyle.YesNo, "提示")
sqlstr = "create table" + " " & input & "(" & a & " )"
MsgBox("生成其他的表")
edit_data(sqlstr)
End If
Me.RichTextBox1.Text = sqlstr
End Sub
Private Function ms(ByVal i As Integer) As String
For x = 1 To j - 1
c = c + "'" + CStr(ws.Cells(i, x).value) + "',"
Next
b = c.Length - 1
a = c.Substring(0, b)
Return a
End Function
Private Function y() As Integer
For d = 1 To 1000
If ws.Cells(d, 1).value = "" And ws.Cells(d + 10, 1).value = "" Then
Return d
Exit For
End If
Next
End Function
Dim input As String
Dim i As Integer
Dim x As Integer
Dim c As String
Dim a As String
Dim d As Integer
Dim b As Integer
Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
sqlstr = ""
create_table()
Try
Me.TextBox2.Text = y() - 1
Me.RichTextBox1.Text = ""
For i = 2 To y() - 1
c = ""
sqlstr = "insert into" & " " & input & " values( " + ms(i) + ")"
edit_data(sqlstr)
Me.RichTextBox1.Text = Me.RichTextBox1.Text + sqlstr + ";------插入成功" + vbCrLf
Next
Catch ex As Exception
End Try
End Sub
End Class
Public Function edit_data(ByVal sqlstr As String) As Boolean
Dim cmd As OracleCommand = New OracleCommand
Try
conn.Open()
cmd.Connection = conn
cmd.CommandText = sqlstr
cmd.CommandType = CommandType.Text
cmd.ExecuteNonQuery()
conn.Close()
Return True
Catch ex As Exception
MsgBox("操作失败,连接数据库失败或无相关数据!")
Return False
End Try
End Function