前回の記事「SQL Serverからデータを取得してDataGridViewに表示する(その2)」で抽出したデータをExcel出力する方法を紹介します。
手段の1つとして、VB.NETの参照設定で「Microsoft Excel xx.x オブジェクトライブラリ」を追加して直接書き込む方法があります。ただし、後処理をしっかり対処しないとタスクにEXCELが残ったり、配布先のOfficeバージョンによっては参照設定のオブジェクトライブラリのバージョンが違う為、エラーになる可能性があるので個人的には使いません。
ここでは、環境に依存しにくいOleDbを使った方法を紹介します。
まずは、前回の記事「SQL Serverからデータを取得してDataGridViewに表示する(その2)」ベースにButton2を追加します。
Button2には以下のコードをコピペしてください。
セーブファイルダイアログでxls形式かxlsx形式かを選択します。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
Private Sub Button2_Click(sender As System.Object, e As System.EventArgs) Handles Button2.Click Dim My_SFD As New SaveFileDialog() My_SFD.InitialDirectory = System.Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments) My_SFD.Title = "出力先のファイル名を選択してください" My_SFD.FileName = "エクスポート_" & Now.ToString("yyyy-MM-dd_HHmmss") My_SFD.Filter = "Excel ブック (*.xlsx)|*.xlsx|Excel 97-2003 ブック (*.xls)|*.xls" My_SFD.FilterIndex = 1 My_SFD.RestoreDirectory = True My_SFD.OverwritePrompt = True My_SFD.CheckPathExists = True If My_SFD.ShowDialog() = DialogResult.OK Then Dim My_TBL As String = "TEST_Table_001" Dim My_DST As DataSet = DataSet1 Dataset_To_Excel_1sheet(My_DST, My_TBL, My_SFD.FileName) End If End Sub |
次にDatasetの内容をExcelに出力するコードをコピペします。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 |
Public Sub Dataset_To_Excel_1sheet(ByVal MyDataSet As DataSet, ByVal MyTable As String, ByVal MyFilePath As String) Dim MyBol As Boolean = False For i = 0 To MyDataSet.Tables.Count - 1 If MyDataSet.Tables(i).TableName = MyTable Then MyBol = True Exit For End If Next If MyBol = False Then MsgBox("テーブルがない為、Excel出力できません。", MsgBoxStyle.Critical, "エラー") Exit Sub End If Dim MyColName As String = Nothing Dim MyColType As String = Nothing 'データ型を調べる Debug.Print("データセットのテーブル[" & MyDataSet.Tables(MyTable).TableName & "] データ型と列名") For i = 0 To MyDataSet.Tables(MyTable).Columns.Count - 1 Debug.Print(MyDataSet.Tables(MyTable).Columns(i).DataType.FullName & " = " & MyDataSet.Tables(MyTable).Columns(i).ColumnName) MyColName &= ", [" & MyDataSet.Tables(MyTable).Columns(i).ColumnName & "] " MyColType &= ", [" & MyDataSet.Tables(MyTable).Columns(i).ColumnName & "] " 'CREATEクエリーで型定義 Select Case MyDataSet.Tables(MyTable).Columns(i).DataType.FullName Case "System.Int32", "System.Decimal" MyColType &= "NUMBER " Case "System.String" MyColType &= "TEXT " Case "System.DateTime" MyColType &= "DATE " Case Else MyColType &= "TEXT " End Select Next 'EXCEL作成出力 Dim MyExt As String = Nothing Dim oleDb As String = Nothing If System.IO.Path.GetExtension(MyFilePath) = ".xlsx" Then MyExt = "Excel 12.0 Xml" oleDb = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & MyFilePath & ";Extended Properties=" & MyExt Else MyExt = "Excel 8.0" oleDb = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & MyFilePath & ";Extended Properties=" & MyExt End If Dim oleConn As New OleDb.OleDbConnection(oleDb) Dim oleCmd As New OleDb.OleDbCommand() oleConn.Open() oleCmd.Connection = oleConn 'EXCELファイル作成 ※空データを挿入 oleCmd.CommandText = "SELECT NULL AS Dammy_Col INTO [" & MyExt & ";HDR=YES;Database=" & MyFilePath & ";HDR=YES].[Sheet1] " oleCmd.ExecuteNonQuery() 'ヘッダーを作成 ※データ型を指定 oleCmd.CommandText = "CREATE TABLE [Sheet1$] (" & MyColType.Substring(1) & ")" oleCmd.ExecuteNonQuery() 'データを挿入 Dim MyColValue As String = Nothing For i = 0 To MyDataSet.Tables(MyTable).Rows.Count - 1 MyColValue = Nothing For z = 1 To MyDataSet.Tables(MyTable).Columns.Count - 1 'シングルコーテーションで値を囲むが判断 Select Case MyDataSet.Tables(MyTable).Columns(z).DataType.FullName Case "System.Int32", "System.Decimal" If IsDBNull(MyDataSet.Tables(MyTable).Rows(i).Item(z)) Then MyColValue &= ", NULL" Else MyColValue &= ", " & MyDataSet.Tables(MyTable).Rows(i).Item(z) & "" End If Case Else If IsDBNull(MyDataSet.Tables(MyTable).Rows(i).Item(z)) Then MyColValue &= ", NULL" Else MyColValue &= ", '" & MyDataSet.Tables(MyTable).Rows(i).Item(z) & "'" End If End Select Next oleCmd.CommandText = "INSERT INTO [Sheet1$] (" & MyColName.Replace("'", "").Substring(1) & ") VALUES ('" & MyDataSet.Tables(MyTable).Rows(i).Item(0) & "'" & MyColValue & ")" oleCmd.ExecuteNonQuery() Next oleConn.Close() Beep() End Sub |
エクセルに書き込むデータ型は、ヘッダーを作成する段階で確定させています。
まず、DataSet内のDataTableのデータ型が何になっているか調べてみました。
※下図はデータ型と列名の関係です。
ここでは数値型にしたいのは、System.Int32とSystem.Decimal、日付型にしたいのは、System.DataTime、残りはテキスト型になります。
なお、DataSet側のデータ型を変更したい場合は、クエリーを発行する際にConvert文を使うと型指定できます。
SELECT convert(varchar,SUG_UpdateTime,111) AS SUG_UpdateTime みたいな感じにすると、System.DataTime→System.Stringになります。
エクセルには以下3点のデータ型を指定できます。
ヘッダー情報を書き込んだ後に続いてデータ挿入するとデータ型を継承して書き込みされます。
NUMBER・・・エクセルの特定列を数値型でエクスポートします。
TEXT・・・エクセルの特定列を数字の値であっても文字列としてエクスポートします。
DATE・・・エクセルの特定列を日付型でエクスポートします。
Button2をクリックしてファイル保存すると以下の様にエクセルが生成されます。
エクセルを開いてみると、下図のように日付型、数値型、テキスト型と正しく書き込みされています。
下図を例にみると、EAN列とSUG_Price列の値は数字ですが、EAN列は数字で認識されると困りますが正しく元のテキスト形式を継承しています。
上の例では、シートが1つのみですが、複数シートを作ることも出来ます。
下記では二つのDataTableの内容を2つのシートに書き込みます。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 |
Public Sub Dataset_To_Excel_2sheet(ByVal MyDataSet As DataSet, ByVal MyTable1 As String, ByVal MyTable2 As String, ByVal MyFilePath As String) Dim MyColName As String = Nothing Dim MyColType As String = Nothing '型を調べる For i = 0 To MyDataSet.Tables(MyTable1).Columns.Count - 1 'Debug.Print(MyDataSet.Tables(MyTable).Columns(i).DataType.FullName & " = " & MyDataSet.Tables(MyTable).Columns(i).ColumnName) MyColName &= ", [" & MyDataSet.Tables(MyTable1).Columns(i).ColumnName & "] " MyColType &= ", [" & MyDataSet.Tables(MyTable1).Columns(i).ColumnName & "] " 'CREATEクエリーで型定義 Select Case MyDataSet.Tables(MyTable1).Columns(i).DataType.FullName Case "System.Int32" MyColType &= "NUMBER " Case "System.String" MyColType &= "TEXT " Case "System.DateTime" MyColType &= "DATE " Case Else MyColType &= "TEXT " End Select Next 'xlsx形式で出力 Dim sCS As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & MyFilePath & ";Extended Properties=Excel 12.0 Xml" Dim oleConn As New OleDb.OleDbConnection(sCS) Dim oleCmd As New OleDb.OleDbCommand() oleConn.Open() oleCmd.Connection = oleConn 'EXCELファイル作成 ※空データを挿入 oleCmd.CommandText = "SELECT NULL AS Dammy_Col INTO [Excel 12.0 Xml;HDR=YES;Database=" & MyFilePath & ";HDR=YES].[" & MyTable1 & "] " oleCmd.ExecuteNonQuery() 'ヘッダーを作成 ※データ型を指定 oleCmd.CommandText = "CREATE TABLE [" & MyTable1 & "$] (" & MyColType.Substring(1) & ")" oleCmd.ExecuteNonQuery() 'データ部分を挿入 Dim MyColValue As String = Nothing For i = 0 To MyDataSet.Tables(MyTable1).Rows.Count - 1 MyColValue = Nothing For z = 1 To MyDataSet.Tables(MyTable1).Columns.Count - 1 'シングルコーテーションで値を囲むか判断 Select Case MyDataSet.Tables(MyTable1).Columns(z).DataType.FullName Case "System.Int32", "System.Decimal" If IsDBNull(MyDataSet.Tables(MyTable1).Rows(i).Item(z)) Then MyColValue &= ", NULL" Else MyColValue &= ", " & MyDataSet.Tables(MyTable1).Rows(i).Item(z) & "" End If Case Else If IsDBNull(MyDataSet.Tables(MyTable1).Rows(i).Item(z)) Then MyColValue &= ", NULL" Else MyColValue &= ", '" & MyDataSet.Tables(MyTable1).Rows(i).Item(z) & "'" End If End Select Next oleCmd.CommandText = "INSERT INTO [" & MyTable1 & "$] (" & MyColName.Replace("'", "").Substring(1) & ") VALUES ('" & MyDataSet.Tables(MyTable1).Rows(i).Item(0) & "'" & MyColValue & ")" oleCmd.ExecuteNonQuery() Next MyColName = Nothing MyColType = Nothing '型を調べる For i = 0 To MyDataSet.Tables(MyTable2).Columns.Count - 1 'Debug.Print(MyDataSet.Tables(MyTable).Columns(i).DataType.FullName & " = " & MyDataSet.Tables(MyTable).Columns(i).ColumnName) MyColName &= ", [" & MyDataSet.Tables(MyTable2).Columns(i).ColumnName & "] " MyColType &= ", [" & MyDataSet.Tables(MyTable2).Columns(i).ColumnName & "] " 'CREATEクエリーで型定義 Select Case MyDataSet.Tables(MyTable2).Columns(i).DataType.FullName Case "System.Int32" MyColType &= "NUMBER " Case "System.String" MyColType &= "TEXT " Case "System.DateTime" MyColType &= "DATE " Case Else MyColType &= "TEXT " End Select Next 'EXCELファイル作成 ※空データを挿入 oleCmd.CommandText = "SELECT NULL AS Dammy_Col INTO [Excel 12.0 Xml;HDR=YES;Database=" & MyFilePath & ";HDR=YES].[" & MyTable2 & "] " oleCmd.ExecuteNonQuery() 'ヘッダーを作成 ※データ型を指定 oleCmd.CommandText = "CREATE TABLE [" & MyTable2 & "$] (" & MyColType.Substring(1) & ")" oleCmd.ExecuteNonQuery() 'データ部分を挿入 MyColValue = Nothing For i = 0 To MyDataSet.Tables(MyTable2).Rows.Count - 1 MyColValue = Nothing For z = 1 To MyDataSet.Tables(MyTable2).Columns.Count - 1 'シングルコーテーションで値を囲むが判断 Select Case MyDataSet.Tables(MyTable2).Columns(z).DataType.FullName Case "System.Int32", "System.Decimal" If IsDBNull(MyDataSet.Tables(MyTable2).Rows(i).Item(z)) Then MyColValue &= ", NULL" Else MyColValue &= ", " & MyDataSet.Tables(MyTable2).Rows(i).Item(z) & "" End If Case Else If IsDBNull(MyDataSet.Tables(MyTable2).Rows(i).Item(z)) Then MyColValue &= ", NULL" Else MyColValue &= ", '" & MyDataSet.Tables(MyTable2).Rows(i).Item(z) & "'" End If End Select Next oleCmd.CommandText = "INSERT INTO [" & MyTable2 & "$] (" & MyColName.Replace("'", "").Substring(1) & ") VALUES ('" & MyDataSet.Tables(MyTable2).Rows(i).Item(0) & "'" & MyColValue & ")" oleCmd.ExecuteNonQuery() Next oleConn.Close() Beep() End Sub |