Excel の使い方 (VB.NET)

投稿日 2015/08/13

[Home]  | [Index]

概要

VB.NET から Microsoft Excel を制御することができます。Excel と OLE オートメーションという機能を使ってExcel の制御を行うので、Excel がインストールされている必要があります。


Visual Studio で Excel を参照できるようにするには

Visual Studio で Excel を使うためには、2つの DLL を参照可能にする必要があります。

  1. ソリューションエクスプローラでプロジェクトの参照を選び、右クリックで「参照の追加」コンテキストメニューを実行する。
  2. 「参照マネージャ」ダイアログが開くので、「COM - タイプライブラリ」を選択する。
  3. "Microsoft Office xx.x Object Library" と "Microsoft Excel xx.x Object Library" にチェックして OK ボタンをクリックする。
  4. ソリューションエクスプローラの「参照」ツリーに "Microsoft.Office.Core" と "Microsoft.Office.Interop.Excel" が追加されたことを確認する。

Excel のオブジェクトモデル

Excel を操作するためには、Excel のオブジェクトモデルを知っている必要があります。

Excel オブジェクト モデルの概要 (MSDN)

オブジェクトモデルを図で描くと下のような感じになっています。


プログラミング

Excel を操作するためのプログラミングは、前述の Excel の各オブジェクトを作成(取得)して、それらのプロパティの操作やメソッドの呼び出しを行うことです。

Imports 文

まず、オブジェクトを参照するため、次の Imports 文をコードの先頭に追加します。

Imports Microsoft.Office.Core
Imports Microsoft.Office.Interop
Imports System.Data.OleDb  ' クエリを行う場合のみ

Excel Application オブジェクトの作成

Excel オブジェクトモデルの先頭にある Excel Application オブジェクト を構築します。

Public Property ExcelObj As Excel.Application

Public Sub New()
  ExcelObj = New Excel.Application()
  ExcelObj.Visible = True  ' Excel を表示する場合のみ
  ExcelObj.DisplayAlerts = False  ' 保存時の確認メッセージボックスを表示しない場合のみ
End Sub

ワークブックの作成

Excel Application オブジェクトが作成できたら、これを利用してワークブックを作成します。Excel Application はワークブックを複数持つことができ、これをワークブックコレクションと呼びます。ワークブックコレクションは、プログラム上では Excel Application オブジェクトの Workbooks プロパティとして定義されています。

既存の Excel ファイルを開くと、Workbook オブジェクトが作成され、Workbooks プロパティに追加されます。Excel ファイルを開には、Workbooks コレクションの Open メソッドを使用します。Open メソッドは開いたブックの Workbook オブジェクトを返します。

''' <summary>
''' 現在のワークブック
''' </summary>
''' <remarks></remarks>
Public Property Book As Excel.Workbook

''' <summary>
''' ブックを開く
''' </summary>
''' <param name="fileName">Excelファイル</param>
Public Sub Open(ByVal fileName As String)
    Book = ExcelObj.Workbooks.Open(fileName)
    CurrentSheet = 1
    Me.fileName = fileName
End Sub

新規にワークブックを作成するには、Workbooks コレクションの Add メソッドを使用します。これも作成した Workbook オブジェクトを返します。このとき、ワークシートコレクション (Worksheets プロパティ) と3つの Worksheet オブジェクトも同時に作成されます。これは、Excel を開いたとき空のワークシートが3枚表示されることに相当します。

ワークブックコレクション (Workbooks) もワークシートコレクション (Worksheets) も<span style="color:red;">インデックスは 1 から始まることに注意してください。下のサンプルで Sheet は先頭のワークシートになります。

''' <summary>
''' 現在のワークシート
''' </summary>
''' <remarks></remarks>
Public Property Sheet As Excel.Worksheet

''' <summary>
''' 新しいブックを作成する
''' </summary>
Public Sub CreateBook()
    Book = ExcelObj.Workbooks.Add()
    Sheet = Book.Worksheets(1)
End Sub

ワークシートを追加するには

ワークシートは新規でワークブックを作成したときも3枚ほど自動的に追加されていますが、Worksheets コレクションの Add メソッドを使えば、さらに追加することもできます。このメソッドは、追加されたワークシートを返します。

''' <summary>
''' 現在のワークブック
''' </summary>
''' <remarks></remarks>
Public Property Book As Excel.Workbook
''' <summary>
''' 現在のワークシート
''' </summary>
''' <remarks></remarks>
Public Property Sheet As Excel.Worksheet

''' <summary>
''' 新しいワークシートを追加する。
''' </summary>
''' <remarks></remarks>
Public Sub AddSheet()
  Sheet = Book.Worksheets.Add()
End Sub

レンジを操作するには

ワークブックオブジェクトにはレンジ (Range) オブジェクトがあります。レンジにはセルのコレクションが含まれます。レンジはワークシートの領域を表します。例えば、"A1:D10" はワークシートの A 列 1 行 ~ D 列 10 行に当たる領域を表します。1つのセルだけを含むレンジは、"A1" のように表すことができます。

レンジを特に指定しなければ、ワークシート全体がレンジとなります。レンジ内のセルは Cells コレクションで取得または設定できます。これは Cells(row, col) のようにしてアクセスします。セルもオブジェクトであり、セルの値は Value プロパティで取得、設定できます。

Cells(row, col) で与える行番号 row、列番号 col は 1 から始まることに注意してください。

''' <summary>
''' セルの値を得る
''' </summary>
''' <param name="row">行番号(1から始まる)</param>
''' <param name="col">列番号(1から始まる)</param>
''' <returns>セルの値</returns>
Public Function GetCellValue(ByVal row As Integer, ByVal col As Integer) As Object
    Return Sheet.Cells(row, col).Value
End Function

''' <summary>
''' セルに値を書く
''' </summary>
''' <param name="row">行番号(1から始まる)</param>
''' <param name="col">列番号(1から始まる)</param>
''' <param name="val">セルの値</param>
Public Sub SetCellValue(ByVal row As Integer, ByVal col As Integer, ByVal val As Object)
    Sheet.Cells(row, col).Value = val
End Sub

''' <summary>
''' レンジを読む
''' </summary>
''' <param name="rng">レンジ</param>
''' <returns>データテーブル</returns>
Public Function GetRange(ByVal rng As String) As DataTable
    Dim dr As DataRow
    Dim cells As String() = rng.Split(CChar(":"))
    ' Range オブジェクトを作成する。
    Dim r As Excel.Range
    If cells.Length = 1 Then
        r = Sheet.Range(cells(0))
    Else
        r = Sheet.Range(cells(0), cells(1))
    End If
    Dim dt As New DataTable
    Dim cols As Integer = 1
    Dim rows As Integer = 1
    ' レンジのサイズを得る。
    If cells.Length > 1 Then
        cols = Asc(cells(1).Substring(0, 1)) - Asc(cells(0).Substring(0, 1)) + 1
        rows = Asc(cells(1).Substring(1, 1)) - Asc(cells(0).Substring(1, 1)) + 1
    End If
    ' カラムを追加する。
    For i = 1 To cols
        dt.Columns.Add()
    Next
    ' DataTable を作成する。
    For j = 1 To rows
        dr = dt.NewRow()
        For i = 1 To cols
            dr(i - 1) = r.Cells(j, i).Value
        Next
        dt.Rows.Add(dr)
    Next
    Return dt
End Function

ワークブックを閉じるには

ワークブックを閉じるには、Close メソッドを使います。これだけでは、Excel Application オブジェクトは閉じません。Excel 全体も閉じるには Quit メソッドを使用します。

''' <summary>
''' ブックを閉じる
''' </summary>
Public Sub Close()
    If Book IsNot Nothing Then
        Book.Close()
    End If
End Sub

''' <summary>
''' Excel を閉じる
''' </summary>
Public Sub Quit()
    ExcelObj.Quit()
End Sub

ワークブックをファイル保存するには

ワークブックをファイルに保存するには、SaveAs または Save メソッドを使います。ファイルに名前を付けて保存する場合は SaveAs、上書き保存する場合は Save メソッドを使います。

''' <summary>
''' ブックをファイル保存する
''' </summary>
''' <param name="fileName">Excelワークブックのファイル名</param>
Public Sub SaveAs(ByVal fileName As String)
    Book.SaveAs(fileName)
End Sub

''' <summary>
''' ブックを上書き保存する
''' </summary>
Public Sub Save()
    Book.Save()
End Sub

OleDb を利用してクエリーを行うには

クエリーを行うための準備

OleDb (System.Data.OleDb) を使うと、レンジに対して SQL を利用してクエリーを行うことができます。

レンジにクエリーを行うためには、レンジに名前を付ける必要があります。レンジに名前を付けるには、レンジの範囲を選びワークシート左上のレンジが表示されるテキストボックスにレンジ名を入力します。

また、レンジの先頭行はフィールド名として使用されます。下の例は、レンジ名を ITEMS としています。


接続文字列

接続文字列は、Excel 2003 以前のバージョンと Excel 2007 以後のバージョンではことなります。Excel 2003 以前では、Provider=Microsoft.JET.OLE.4.0 を使用します。Excel 2007 以後では、Provider=Microsoft.ACE.OLE.12.0 を使用します。また、Extended Properties=Excel 12.0 などの指定が必要です。Excel ファイルの場所は、Data Source で指定します。

"Provider=Microsoft.JET.OLE.4.0;Data Source=C:\temp\Book1.xls;Extended Properties=Excel 8.0"
"Provider=Microsoft.ACE.OLE.12.0;Data Source=C:\temp\Book2.xlsx;Extended Properties=Excel 12.0"

プログラミング

Excel のレンジに対するクエリーも Access などのテーブルへのクエリー手順と同じです。違いは、テーブルの代わりにレンジ名を使用することです。以下にサンプルを示します。

''' <summary>
''' 名前を付けた領域に対してクエリーを行う。
''' </summary>
''' <param name="sql">SELECT 文</param>
''' <returns>クエリー結果 (DataTable)</returns>
Public Function Query(ByVal sql As String) As DataTable
    Dim conn As New OleDbConnection
    ' 拡張子が .xlsx ならプロバイダに ACE を使用する。
    Dim ace As Boolean = Me.fileName.LastIndexOf(".xlsx") > 0
    ' 接続文字列を使ってExcelブックに接続する。
    conn.ConnectionString = GetConnectionString(Me.fileName, ace)
    conn.Open()
    ' OleDbCommand オブジェクトを作成する。
    Dim command As New OleDbCommand
    command.Connection = conn
    command.CommandText = sql
    ' OleDbCommand と OleDbDataAdapter を使ってデータセットの内容を得る。
    Dim dataSet1 As New DataSet
    Dim adapter As New OleDbDataAdapter
    adapter.SelectCommand = command
    adapter.Fill(dataSet1)
    conn.Close()
    ' DataTable を返す。
    Return dataSet1.Tables(0)
End Function

''' <summary>
''' Excel への接続文字列を得る。
''' </summary>
''' <param name="fileName">Excel のファイル名</param>
''' <param name="ace">True なら .XLSX 、False なら .XLS を使う。デフォルトは False</param>
''' <returns>接続文字列</returns>
Public Function GetConnectionString(ByVal fileName As String, Optional ByVal ace As Boolean = False) As String
    Dim connStrBuilder As New OleDbConnectionStringBuilder()
    If ace Then
        connStrBuilder("Provider") = "Microsoft.ACE.OLEDB.12.0"
        connStrBuilder("Data Source") = fileName
        connStrBuilder("Extended Properties") = "Excel 12.0"
    Else
        connStrBuilder("Provider") = "Microsoft.JET.OLEDB.4.0"
        connStrBuilder("Data Source") = fileName
        connStrBuilder("Extended Properties") = "Excel 8.0"
    End If
    Return connStrBuilder.ToString()
End Function

完全なサンプルコード

Form1.vb


Public Class Form1
    Private fileName As String
    Private excel As ExcelClass
    
    ''' <summary>
    ''' フォームがロードしたとき
    ''' </summary>
    ''' <param name="sender"></param>
    ''' <param name="e"></param>
    Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        txtCol.Text = ColUpDown.Value.ToString()
        txtRow.Text = RowUpDown.Value.ToString()
        txtSheet.Text = NumericUpDown1.Value.ToString()
    End Sub
    
    ''' <summary>
    ''' フォームが閉じるとき
    ''' </summary>
    ''' <param name="sender"></param>
    ''' <param name="e"></param>
    Private Sub Form1_FormClosing(sender As Object, e As FormClosingEventArgs) Handles MyBase.FormClosing
           If excel IsNot Nothing Then
            excel.Close()
            excel.Quit()
    End If
    End Sub
    
    ''' <summary>
    ''' 「ファイル/終了」メニュー
    ''' </summary>
    ''' <param name="sender"></param>
    ''' <param name="e"></param>
    Private Sub mnuExit_Click(sender As Object, e As EventArgs) Handles mnuExit.Click
        Close()
    End Sub
    
    ''' <summary>
    ''' 「ファイル/開く」メニュー
    ''' </summary>
    ''' <param name="sender"></param>
    ''' <param name="e"></param>
    Private Sub mnuFileOpen_Click(sender As Object, e As EventArgs) Handles mnuFileOpen.Click
        If OpenFileDialog1.ShowDialog() = DialogResult.OK Then
            fileName = OpenFileDialog1.FileName
            If excel Is Nothing Then
                excel = New ExcelClass(chkVisible.Checked)
                btnReadCell.Enabled = excel IsNot Nothing
                btnWriteCell.Enabled = btnReadCell.Enabled
                btnQuery.Enabled = btnReadCell.Enabled
                NumericUpDown1.Enabled = excel IsNot Nothing
                ColUpDown.Enabled = NumericUpDown1.Enabled
                RowUpDown.Enabled = NumericUpDown1.Enabled
                btnReadRange.Enabled = True
            End If
            Try
                excel.Open(fileName)
                statusLabel1.Text = fileName & " を開きました。"
                NumericUpDown1.Maximum = excel.CountOfSheets
                NumericUpDown1.Minimum = 1
                txtSheet.Text = NumericUpDown1.Minimum.ToString()
            Catch ex As Exception
                MsgBox(ex.Message)
            End Try
        End If
    End Sub
    
    ''' <summary>
    ''' 「ファイル/保存」メニュー
    ''' </summary>
    ''' <param name="sender"></param>
    ''' <param name="e"></param>
    Private Sub mnuFileSave_Click(sender As Object, e As EventArgs) Handles mnuFileSaveAs.Click
        If SaveFileDialog1.ShowDialog() = DialogResult.OK Then
            Try
                excel.SaveAs(SaveFileDialog1.FileName)
                statusLabel1.Text = SaveFileDialog1.FileName & " に保存しました。"
            Catch ex As Exception
                MsgBox(ex.Message)
            End Try
        End If
    End Sub
    
    ''' <summary>
    ''' 「ファイル/新規」メニュー
    ''' </summary>
    ''' <param name="sender"></param>
    ''' <param name="e"></param>
    Private Sub mnuFileNew_Click(sender As Object, e As EventArgs) Handles mnuFileNew.Click
        If excel IsNot Nothing Then
            excel.Close()
            excel.Quit()
        End If
        excel = New ExcelClass()
        excel.CreateBook()
        btnReadCell.Enabled = True
        btnWriteCell.Enabled = True
        btnQuery.Enabled = True
        NumericUpDown1.Enabled = True
        NumericUpDown1.Maximum = excel.CountOfSheets
        NumericUpDown1.Minimum = 1
        btnReadRange.Enabled = True
        ColUpDown.Enabled = True
        RowUpDown.Enabled = True
        txtSheet.Text = "1"
        statusLabel1.Text = "新しい Excel オブジェクトを作成しました。"
    End Sub
    
    ''' <summary>
    ''' 「読む」ボタン
    ''' </summary>
    ''' <param name="sender"></param>
    ''' <param name="e"></param>
    Private Sub btnReadCell_Click(sender As Object, e As EventArgs) Handles btnReadCell.Click
        Dim col, row As Integer
        Try
            row = Int32.Parse(txtRow.Text)
            col = Int32.Parse(txtCol.Text)
            excel.CurrentSheet = Int32.Parse(txtSheet.Text)
            txtValue.Text = If(excel.GetCellValue(row, col) Is Nothing, "(Empty)", excel.GetCellValue(row, col).ToString())
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
    End Sub
    
    ''' <summary>
    ''' 「書く」ボタン
    ''' </summary>
    ''' <param name="sender"></param>
    ''' <param name="e"></param>
    Private Sub btnWriteCell_Click(sender As Object, e As EventArgs) Handles btnWriteCell.Click
        Dim col, row As Integer
        Try
            row = Int32.Parse(txtRow.Text)
            col = Int32.Parse(txtCol.Text)
            excel.CurrentSheet = Int32.Parse(txtSheet.Text)
            excel.SetCellValue(row, col, txtValue.Text)
            statusLabel1.Text = "Cells(" & txtRow.Text & ", " & txtCol.Text & ") = " & txtValue.Text
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
    End Sub
    
    ''' <summary>
    ''' アップダウンコントロールで値が変化したとき
    ''' </summary>
    ''' <param name="sender"></param>
    ''' <param name="e"></param>
    Private Sub NumericUpDown1_ValueChanged(sender As Object, e As EventArgs) Handles NumericUpDown1.ValueChanged
        txtSheet.Text = NumericUpDown1.Value.ToString()
    End Sub
    
    ''' <summary>
    ''' 「ファイル/上書き保存」メニュー
    ''' </summary>
    ''' <param name="sender"></param>
    ''' <param name="e"></param>
    Private Sub mnuFileSave_Click_1(sender As Object, e As EventArgs) Handles mnuFileSave.Click
        Try
            excel.Save()
            statusLabel1.Text = "上書き保存しました。"
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
    End Sub
    
    ''' <summary>
    ''' レンジを読む
    ''' </summary>
    ''' <param name="sender"></param>
    ''' <param name="e"></param>
    Private Sub btnReadRange_Click(sender As Object, e As EventArgs) Handles btnReadRange.Click
        Try
            Dim data = excel.GetRange(txtRange.Text)
            DataGridView1.DataSource = data
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
    End Sub
    
    ''' <summary>
    ''' クエリボタン
    ''' </summary>
    ''' <param name="sender"></param>
    ''' <param name="e"></param>
    Private Sub btnQuery_Click(sender As Object, e As EventArgs) Handles btnQuery.Click
        Try
            DataGridView1.DataSource = excel.Query(txtSelect.Text)
            statusLabel1.Text = txtSelect.Text
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
    End Sub
    
    ''' <summary>
    ''' 列番号のアップダウン
    ''' </summary>
    ''' <param name="sender"></param>
    ''' <param name="e"></param>
    Private Sub ColUpDown_ValueChanged(sender As Object, e As EventArgs) Handles ColUpDown.ValueChanged
        txtCol.Text = ColUpDown.Value.ToString()
    End Sub
    
    ''' <summary>
    ''' 行番号のアップダウン
    ''' </summary>
    ''' <param name="sender"></param>
    ''' <param name="e"></param>
    Private Sub RowUpDown_ValueChanged(sender As Object, e As EventArgs) Handles RowUpDown.ValueChanged
        txtRow.Text = RowUpDown.Value.ToString()
    End Sub
End Class

ExcelClass.vb

'Imports Microsoft.Office.Core
Imports Microsoft.Office.Interop
Imports System.Data.OleDb

''' <summary>
''' Excel 操作クラス
''' </summary>
Public Class ExcelClass
    Private sheetNumber As Integer  ' 現在のワークシート番号(最少は1)
    Private fileName As String  ' 現在のファイル
    
    ''' <summary>
    ''' Excel Application オブジェクト
    ''' </summary>
    ''' <returns></returns>
    Public Property ExcelObj As Excel.Application
    ''' <summary>
    ''' 現在のワークブック
    ''' </summary>
    ''' <remarks></remarks>
    Public Property Book As Excel.Workbook
    ''' <summary>
    ''' 現在のワークシート
    ''' </summary>
    ''' <remarks></remarks>
    Public Property Sheet As Excel.Worksheet
    
    ''' <summary>
    ''' 現在のシート番号
    ''' </summary>
    ''' <remarks>先頭のシートは1</remarks>
    Public Property CurrentSheet As Integer
        Get
            Return sheetNumber
        End Get
        Set(value As Integer)
            sheetNumber = value
            Sheet = Book.Worksheets(sheetNumber)
        End Set
    End Property
    
    ''' <summary>
    ''' シート数
    ''' </summary>
    ''' <returns></returns>
    Public ReadOnly Property CountOfSheets As Integer
        Get
            Return Book.Worksheets.Count
        End Get
    End Property
    
    ''' <summary>
    ''' ブック数
    ''' </summary>
    ''' <returns></returns>
    Public ReadOnly Property CountOfBooks As Integer
        Get
            Return ExcelObj.Workbooks.Count
        End Get
    End Property
    
    
    ''' <summary>
    ''' コンストラクタ
    ''' </summary>
    Public Sub New(Optional ByVal showExcel As Boolean = False)
        ExcelObj = New Excel.Application()
        ExcelObj.Visible = showExcel
        ExcelObj.DisplayAlerts = False  ' 保存時の確認メッセージボックスを表示しない。
        sheetNumber = 1
    End Sub
    
    ''' <summary>
    ''' ブックを開く
    ''' </summary>
    ''' <param name="fileName">Excelファイル</param>
    Public Sub Open(ByVal fileName As String)
        Book = ExcelObj.Workbooks.Open(fileName)
        CurrentSheet = 1
        Me.fileName = fileName
    End Sub
    
    ''' <summary>
    ''' ブックをファイル保存する
    ''' </summary>
    ''' <param name="fileName">Excelワークブックのファイル名</param>
    Public Sub SaveAs(ByVal fileName As String)
        Book.SaveAs(fileName)
    End Sub
    
    ''' <summary>
    ''' ブックを上書き保存する
    ''' </summary>
    Public Sub Save()
        Book.Save()
    End Sub
    
    ''' <summary>
    ''' ブックを閉じる
    ''' </summary>
    Public Sub Close()
        If Book IsNot Nothing Then
            Book.Close()
        End If
    End Sub
    
    ''' <summary>
    ''' 新しいブックを作成する
    ''' </summary>
    Public Sub CreateBook()
        Book = ExcelObj.Workbooks.Add()
        Sheet = Book.Worksheets(1)
    End Sub
    
    ''' <summary>
    ''' Excel を閉じる
    ''' </summary>
    Public Sub Quit()
        ExcelObj.Quit()
    End Sub
    
    ''' <summary>
    ''' セルの値を得る
    ''' </summary>
    ''' <param name="row">行番号(1から始まる)</param>
    ''' <param name="col">列番号(1から始まる)</param>
    ''' <returns>セルの値</returns>
    Public Function GetCellValue(ByVal row As Integer, ByVal col As Integer) As Object
        Return Sheet.Cells(row, col).Value
    End Function
    
    ''' <summary>
    ''' セルに値を書く
    ''' </summary>
    ''' <param name="row">行番号(1から始まる)</param>
    ''' <param name="col">列番号(1から始まる)</param>
    ''' <param name="val">セルの値</param>
    Public Sub SetCellValue(ByVal row As Integer, ByVal col As Integer, ByVal val As Object)
        Sheet.Cells(row, col).Value = val
    End Sub
    
    ''' <summary>
    ''' レンジを読む
    ''' </summary>
    ''' <param name="rng">レンジ</param>
    ''' <returns>データテーブル</returns>
    Public Function GetRange(ByVal rng As String) As DataTable
        Dim dr As DataRow
        Dim cells As String() = rng.Split(CChar(":"))
        ' Range オブジェクトを作成する。
        Dim r As Excel.Range
        If cells.Length = 1 Then
            r = Sheet.Range(cells(0))
        Else
            r = Sheet.Range(cells(0), cells(1))
        End If
        Dim dt As New DataTable
        Dim cols As Integer = 1
        Dim rows As Integer = 1
        ' レンジのサイズを得る。
        If cells.Length > 1 Then
            cols = Asc(cells(1).Substring(0, 1)) - Asc(cells(0).Substring(0, 1)) + 1
            rows = Asc(cells(1).Substring(1, 1)) - Asc(cells(0).Substring(1, 1)) + 1
        End If
        ' カラムを追加する。
        For i = 1 To cols
            dt.Columns.Add()
        Next
        ' DataTable を作成する。
        For j = 1 To rows
            dr = dt.NewRow()
            For i = 1 To cols
                dr(i - 1) = r.Cells(j, i).Value
            Next
            dt.Rows.Add(dr)
        Next
        Return dt
    End Function
    
    ''' <summary>
    ''' 名前を付けた領域に対してクエリーを行う。
    ''' </summary>
    ''' <param name="sql">SELECT 文</param>
    ''' <returns>クエリー結果 (DataTable)</returns>
    Public Function Query(ByVal sql As String) As DataTable
        Dim conn As New OleDbConnection
        ' 拡張子が .xlsx ならプロバイダに ACE を使用する。
        Dim ace As Boolean = Me.fileName.LastIndexOf(".xlsx") > 0
        ' 接続文字列を使ってExcelブックに接続する。
        conn.ConnectionString = GetConnectionString(Me.fileName, ace)
        conn.Open()
        ' OleDbCommand オブジェクトを作成する。
        Dim command As New OleDbCommand
        command.Connection = conn
        command.CommandText = sql
        ' OleDbCommand と OleDbDataAdapter を使ってデータセットの内容を得る。
        Dim dataSet1 As New DataSet
        Dim adapter As New OleDbDataAdapter
        adapter.SelectCommand = command
        adapter.Fill(dataSet1)
        conn.Close()
        ' DataTable を返す。
        Return dataSet1.Tables(0)
    End Function
    
    ''' <summary>
    ''' Excel への接続文字列を得る。
    ''' </summary>
    ''' <param name="fileName">Excel のファイル名</param>
    ''' <param name="ace">True なら .XLSX 、False なら .XLS を使う。デフォルトは False</param>
    ''' <returns>接続文字列</returns>
    Public Function GetConnectionString(ByVal fileName As String, Optional ByVal ace As Boolean = False) As String
        Dim connStrBuilder As New OleDbConnectionStringBuilder()
        If ace Then
            connStrBuilder("Provider") = "Microsoft.ACE.OLEDB.12.0"
            connStrBuilder("Data Source") = fileName
            connStrBuilder("Extended Properties") = "Excel 12.0"
        Else
            connStrBuilder("Provider") = "Microsoft.JET.OLEDB.4.0"
            connStrBuilder("Data Source") = fileName
            connStrBuilder("Extended Properties") = "Excel 8.0"
        End If
        Return connStrBuilder.ToString()
    End Function
End Class

 

 


 

このページの先頭へ