canvas テスト

Canvas

EXCELをODBC経由でSQLを使って操作する

Excelの編集をODBCを使って、
SQLを編集する用のメモ。

素直に、COMを使えという感じもあるけど、
SQLでExcelを操作できると嬉しい時もあるので...

1.Excel用のODBCドライバの入手

Microsoft Access データベース エンジン 2010 再頒布可能コンポーネント

Access何とかとなってるけど、Excelのドライバも
ついてくるみたいなので、ダウンロードしてインストール

2.ODBCドライバ名の確認

コントロールパネルを開いて、odbcを検索

ODBCデータソースから、Excel用のドライバが
インストールされていることを確認

3.空のExcelの作成

自分の使った範囲では、元ネタのExcelを作成は
odbc経由では作れないので、Excelを使用して
空のExcelを作成する。

4.コマンドラインツールの作成

SQL入力用のコマンラインツールを作成する。
下記のリストをコピーして、
Visual Studio か、コマンドラインでコンパイルする。

Imports System
Imports System.Collections.Generic
Imports System.Data
Imports System.Data.Odbc
Imports System.Text.RegularExpressions
    
Public Class DBAccessor2
    Dim  conn As OdbcConnection
    Dim  Command As OdbcCommand
    Dim  adapter As OdbcDataAdapter
    Dim transaction As OdbcTransaction

    Public Function Open(ByVal driver As string,ByVal dbq As String,ByVal uid As String,ByVal pwd As String) As Boolean
        Dim builder As OdbcConnectionStringBuilder = new OdbcConnectionStringBuilder()
        builder.Driver = driver ';   //ドライバー名
        builder.Add("DBQ", dbq) ';   //リスナー名
        builder.Add("UID", uid) ';   //ユーザ名
        builder.Add("PWD", pwd) ';   //パスワード
        builder.Add("ReadOnly","False") '
        conn = new OdbcConnection(builder.ConnectionString)
        conn.Open()
        command = new OdbcCommand()
        command.Connection = conn
        adapter = new OdbcDataAdapter()
        return true
    End Function

    Public Sub Close() 
        If(conn IsNot Nothing)
            conn.Close()
            conn = Nothing
            command = Nothing
            adapter = Nothing

        End If
    End Sub
    
    Public Function GetData(ByRef sql As String) As DataSet
        Dim dSet As DataSet = New DataSet()
        command = conn.CreateCommand()
        command.Transaction = transaction
        command.CommandText = sql
        adapter.SelectCommand = Command
        adapter.Fill(dSet)
        Return dSet
    End Function

    Public Sub Execute(ByRef sql As String) 
        Command = conn.CreateCommand()
        command.Transaction = transaction
        command.CommandText = sql
        command.ExecuteNonQuery()
    End Sub
    
End Class

Public Module SqlCmd
            
    Sub Main(ByVal args() As String)

        Dim sql,line As String
        Dim dba As New DBAccessor2

        Line = ""
        dba.Open("Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)",args(0),"none","none")
        While(True)
            Try
                sql = ""
                While (True)
                    System.Console.Write(">>")
                    line = System.Console.In.ReadLine()
                    System.Threading.Thread.Sleep(10)
                    If line = "e" Or Line = "go"
                        Exit While
                    Else
                        Line = Regex.Replace(Line,"\/\*.*?\*\/","") 'コメント部分を削除
                        'System.Console.WriteLine(Line)
                        sql += " " + Line + ControlChars.NewLine
                    End If
                    
                End While
                    
                If Line = "e" Then
                    Exit While
                End If
                Dim t As DataTable
                'dba.Execute(sql)
                Dim tables As DataTableCollection
                Dim cList As New List(Of String)
                tables = dba.GetData(sql).Tables
                If(tables.Count >= 1)
                   t = tables(0)
                   For Each c As DataColumn In t.Columns
                       System.Console.Write(c.ColumnName + ControlChars.Tab)
                       cList.Add(c.ColumnName)
                   Next
                   System.Console.WriteLine()

                   For Each row As DataRow In t.Rows
                       For Each Str As String In cList
                           System.Console.Write(row(Str).ToString + ControlChars.Tab)
                       Next
                       System.Console.WriteLine()
                   Next
                End If
            Catch ex As Exception
                System.Console.WriteLine(ex.ToString())
            End Try
        End While

        dba.Close()
        
    End Sub

 End Module

5.SQLでExcelを操作

作成したExcelには、Sheet1とSheet2が
あるものとしてCmdプロンプトで実行

6.SQL実行した後のExcel