指定したExcelをSQLテーブルに呼び込む方法

質問

• VB.Netを使用して、指定したExcelファイルを指定したSQLテーブルに呼び込む方法を教えてください。
• AccessのVBAでは、DoCmd.TransferSpreadsheetというメソッドを使用してExcelファイルをテーブルに呼び込むことができましたが、VB.Netで同様の処理を行う方法がわかりません。
• VB.Net初心者ですので、詳しい手順やコードの説明をお願いします。

回答No.7

NoMusicNoLife49

こんにちは。

前回のソースの
>Fill(DT)
>End With
の直下に以下のソースをコピーしてください。(＊最後に重要な説明が書きましたので必ず読んでください。

'↓ここから
'変数宣言
Dim ＤＢ接続クラス As System.Data.SqlClient.SqlConnection = Nothing
Dim ＳＱＬ実行クラス As System.Data.SqlClient.SqlCommand = Nothing
Dim トランザクション As System.Data.SqlClient.SqlTransaction = Nothing

'※データベースに接続するためのUser IDとPasswordを指定してください。
Dim strDB接続情報 As String = "Data Source=WS70;" & _
"Initial Catalog=勤怠管理;" & _
"Persist Security Info=True;" & _
"User ID=ID;" & _
"Password=Pass;"

Try
'DB接続クラスインスタンス作成
ＤＢ接続クラス = New SqlConnection(strDB接続情報)

'DB接続
ＤＢ接続クラス.Open()

'トランザクション開始
トランザクション = ＤＢ接続クラス.BeginTransaction

'SQLコマンド実行クラス作成
ＳＱＬ実行クラス = New SqlCommand

With ＳＱＬ実行クラス
'パラメータ初期化
.Parameters.Clear()

'DB接続情報設定
.Connection = ＤＢ接続クラス

'トランザクション指定
.Transaction = トランザクション

'コマンドタイプ設定
.CommandType = CommandType.Text

'SQLコマンド設定
.CommandText = "INSERT INTO SELECT @No,@氏名,@国語,@数学,@英語,@合計点"

'I/Oパラメータ定義
.Parameters.Add("@No", SqlDbType.Int).Direction = ParameterDirection.Input
.Parameters.Add("@氏名", SqlDbType.NVarChar, 8).Direction = ParameterDirection.Input
.Parameters.Add("@国語", SqlDbType.Int).Direction = ParameterDirection.Input
.Parameters.Add("@数学", SqlDbType.Int).Direction = ParameterDirection.Input
.Parameters.Add("@英語", SqlDbType.Int).Direction = ParameterDirection.Input
.Parameters.Add("@合計点", SqlDbType.Int).Direction = ParameterDirection.Input

'SQL実行ループ
For Each DR As DataRow In DT.Rows
Try
'エクセルのデータをSQL実行パラメータに設定
.Parameters.Item("@No").Value = DR(0)
.Parameters.Item("@氏名").Value = Convert.ToString(DR(1))
.Parameters.Item("@国語").Value = DR(2)
.Parameters.Item("@数学").Value = DR(3)
.Parameters.Item("@英語").Value = DR(4)
.Parameters.Item("@合計点").Value = DR(5)

'SQL実行
.ExecuteNonQuery()

Catch ex As SqlException
'SQL例外処理部
'SQL実行時にエラーが発生した場合、このブロックに飛びます。

'エラーメッセージ出力
MsgBox(ex.Message & ex.StackTrace, MsgBoxStyle.Critical)
End Try
Next
End With

'コッミト
トランザクション.Commit()

Catch ex As Exception
'例外処理部

'ロールバック
トランザクション.Rollback()

'エラーメッセージ出力
MsgBox(ex.Message & ex.StackTrace, MsgBoxStyle.Critical)
Finally
If ＤＢ接続クラス IsNot Nothing AndAlso ＤＢ接続クラス.State <> ConnectionState.Closed Then
'DB接続切断
ＤＢ接続クラス.Close()
End If
End Try
'↑ここまで

※-------------------------------- ※
※----------- 重要 ---------------- ※
※-------------------------------- ※

ソースの前半で

>'※データベースに接続するためのUser IDとPasswordを指定してください。
> Dim strDB接続情報 As String = "Data Source=WS70;" & _
> "Initial Catalog=勤怠管理;" & _
> "Persist Security Info=True;" & _
>"User ID=ID;" & _
>"Password=Pass;"

の
>"User ID=ID;" & _
>"Password=Pass;"
の
「ID」と「Pass」をデータベース「勤怠管理」に接続するためのIDとPassに書き換えてください。

お礼

NoMusicNoLife49　さん続いてありがとうございます。
本当に細かく教えていただいてありがとうございます。

私も私なりに考えてNoMusicNoLife49のプログラムを下敷きに作ってみました。
<--- 追加　という所が追加したプログラムです。
勤怠管理DataSetをフォームに貼付けテーブルとつながった状況で
With OleAdapter
.SelectCommand = New OleDb.OleDbCommand
.SelectCommand.Connection = OleConnection
.SelectCommand.CommandType = CommandType.Text
.SelectCommand.CommandText = "SELECT * FROM [sheet1$]"

'エクセルファイルのデータをメモリーテーブルに読込む
.Fill(DT)
Dim str確認メッセージ As String = String.Empty
If DT.Rows.Count = 0 Then
MsgBox("エクセルのデータを取得できませんでした。")
Else
'テーブルデーター削除
Call テーブルデーター削除()　<---追加
'データーを取込
For Each DR As DataRow In DT.Rows
str確認メッセージ = String.Empty
Dim 氏名 As String = ""
順番 = 1
For Each obect As Object In DR.ItemArray
If obect IsNot DBNull.Value = True Then
DataPub(順番) = "'" & Convert.ToString(obect) & "'"　　<--- 追加
'MsgBox(順番 & Data(順番))
str確認メッセージ += "|" + Convert.ToString(obect)
End If
順番 = 順番 + 1
Next
MsgBox(str確認メッセージ)
'テーブルデーター書込
Call テーブルデーター書込()　<---追加
Next
End If
End With

追加したプロシージャーは2つあります。

1つ目のプロシージャー

Private Sub テーブルデーター削除()
'コネクション指定
Using Connection As New _
SqlClient.SqlConnection(My.Settings.処理数ConnectionString)
'コマンド定義
Dim command As SqlClient.SqlCommand = Connection.CreateCommand()
'コネクションを開く
Connection.Open()
'コマンド定義と実行
command.CommandText = _
"TRUNCATE TABLE dbo.test"
'commandを実行
command.ExecuteNonQuery()
'コネクションをclose
Connection.Close()
End Using
End Sub

2つ目のプロシージャー

Private Sub テーブルデーター書込()
'コネクション指定
Using Connection As New _
SqlClient.SqlConnection(My.Settings.処理数ConnectionString)
'コマンド定義
Dim command As SqlClient.SqlCommand = Connection.CreateCommand()
'コネクションを開く
Connection.Open()
'コマンド定義と実行
command.CommandText = _
"INSERT INTO dbo.test(NO,氏名,国語,数学,英語,合格点) SELECT" & DataPub(1) & "," & DataPub(2) & "," & DataPub(3) & "," & DataPub(4) & "," & DataPub(5) & "," & DataPub(6)
'commandを実行
command.ExecuteNonQuery()
'コネクションをclose
Connection.Close()
End Using
End Sub

これで何とか書き込めました。

このプログラムでどこかアドバイスなりありましたらお願いいたします。
本当に細かく教えていただいてありがとうございます。
よろしくお願いいたします。