VB.NET覚書

VB.NETの覚書
[個人メモ] [仕事メモ] [アイデア] [Ruby]
[TIPS] [対応表] [コメント書き方] [.NET Tips] [VB中学校] [VB.NET の入門サイト] [VB.NET コーディング標準 ] [VBレスキュー] [NonSoft]

2019-02-12

clsBackgroundworker

Imports System.ComponentModel
'http://multithreadingapp.blogspot.com/

Enum ST_NO
    ''' <summary>
    ''' アイドル状態
    ''' </summary>
    E_IDLE
    ''' <summary>
    ''' 動作中
    ''' </summary>
    E_BUSY
    ''' <summary>
    ''' キャンセル
    ''' </summary>
    E_CANCEL
    ''' <summary>
    ''' エラー
    ''' </summary>
    E_ERR
    ''' <summary>
    ''' 終了
    ''' </summary>
    E_DONE
    ''' <summary>
    ''' 総数
    ''' </summary>
    E_ALL_NUM
End Enum
''' <summary>
''' 処理率(100で終了)
''' </summary>
''' <param name="Para">パラメータ</param>
''' <param name="Result">応答</param>
Public Delegate Function job(Para As String, ByRef Result As String) As Integer

''' <summary>
''' マルチスレッド(Backgrowndworker)クラス
''' </summary>
''' <remarks>
''' 使用例
''' 
''' Public Class Form1
''' 
'''    Dim WithEvents bgw As New clsBackGroundWorker
''' 
'''    Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click
''' 
'''        bgw.m_job = Function(Para As String, ByRef Result As String) As Integer
'''                        MsgBox(Para)
''' 
'''                        Result = "終了"
''' 
'''                        Return clsBackGroundWorker.MAX_PROGRESS
'''                    End Function
''' 
'''        bgw.start("開始")
''' 
'''    End Sub
''' 
'''    Private Sub bgw_completed(ByVal sender As Object, ByVal e As System.EventArgs) Handles bgw.completed
''' 
'''        MsgBox(bgw.getResult())
''' 
'''    End Sub
''' 
''' End Class
''' </remarks>
Public Class clsBackGroundWorker

    Public Const MAX_PROGRESS = 100

    Private bw As BackgroundWorker = New BackgroundWorker
    Private m_progress As Integer = 0
    Private m_status_msg As String = ""
    Private m_status_no As Integer = 0
    Private m_result As String = ""

    ''' <summary>
    ''' 実行する処理を記載 
    ''' </summary>
    Public m_job As job

    ''' <summary>
    ''' 終了処理
    ''' </summary>
    Public Event completed(ByVal sender As Object, ByVal e As EventArgs)

    Sub New()
        bw.WorkerSupportsCancellation = True
        bw.WorkerReportsProgress = True

        AddHandler bw.DoWork, AddressOf bw_DoWork
        AddHandler bw.ProgressChanged, AddressOf bw_ProgressChanged
        AddHandler bw.RunWorkerCompleted, AddressOf bw_RunWorkerCompleted

        m_status_msg = "IDLE"
        m_status_no = ST_NO.E_IDLE

    End Sub

    ''' <summary>
    ''' ステータスメッセージ
    ''' </summary>
    Function getStatusMessage() As String
        Return m_status_msg
    End Function

    ''' <summary>
    ''' ステータス変数
    ''' </summary>
    Function getStatusNo() As Integer
        Return m_status_no
    End Function

    Private Sub bw_DoWork(ByVal sender As Object, ByVal e As DoWorkEventArgs)

        Dim worker As BackgroundWorker = CType(sender, BackgroundWorker)
        Dim Ret As Integer = 0
        Dim Result As String = ""
        m_status_msg = "BUSY"
        m_status_no = ST_NO.E_BUSY

        Do While True

            Ret = m_job(CType(e.Argument, String), Result)

            If bw.CancellationPending = True Then
                e.Cancel = True
                Exit Do
            Else
                ' Perform a time consuming operation and report progress.
                bw.ReportProgress(Ret)
                If Ret = MAX_PROGRESS Then
                    Exit Do
                End If
            End If
        Loop

        e.Result = Result

    End Sub

    Private Sub bw_ProgressChanged(ByVal sender As Object, ByVal e As ProgressChangedEventArgs)
        m_progress = e.ProgressPercentage
    End Sub

    Private Sub bw_RunWorkerCompleted(ByVal sender As Object, ByVal e As RunWorkerCompletedEventArgs)

        If e.Cancelled = True Then
            m_status_msg = "Canceled!"
            m_status_no = ST_NO.E_CANCEL
        ElseIf e.Error IsNot Nothing Then
            m_status_msg = "Error: " & e.Error.Message
            m_status_no = ST_NO.E_ERR
        Else
            m_status_msg = "Done!"
            m_status_no = ST_NO.E_DONE
            'MsgBox("Done")
            m_result = e.Result.ToString()
            RaiseEvent completed(Me, New EventArgs)
        End If

    End Sub

    ''' <summary>
    ''' スレッドを開始
    ''' </summary>
    Public Sub start(x As String)
        If Not bw.IsBusy = True Then
            bw.RunWorkerAsync(x)
        End If
    End Sub

    ''' <summary>
    ''' キャンセル処理
    ''' </summary>
    Public Sub cancel()
        If bw.WorkerSupportsCancellation = True Then
            bw.CancelAsync()
        End If
    End Sub

    ''' <summary>
    ''' 処理状況を返す(0-100)
    ''' </summary>
    Public Function getProgress() As Integer
        Return m_progress
    End Function

    ''' <summary>
    ''' 結果
    ''' </summary>
    Public Function getResult() As String

        Return m_result

    End Function

End Class

使用

Public Class Form1

    Dim WithEvents bgw As New clsBackGroundWorker

    Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click

        bgw.m_job = Function(Para As String, ByRef Result As String) As Integer
                        MsgBox(Para)

                        Result = "終了"

                        Return clsBackGroundWorker.MAX_PROGRESS
                    End Function

        bgw.start("開始")

    End Sub

    Private Sub bgw_completed(ByVal sender As Object, ByVal e As System.EventArgs) Handles bgw.completed

        MsgBox(bgw.getResult())

    End Sub

    Private Sub Form1_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load

    End Sub
End Class



backgroundworker 引数戻り値

http://www.atmarkit.co.jp/fdotnet/dotnettips/436bgworker/bgworker.html

Imports System.ComponentModel

Public Class Form1

  ' [スタート]ボタンのイベント・ハンドラ
  Private Sub buttonStart_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles buttonStart.Click
    buttonStart.Enabled = False

    ' 時間のかかる処理を別スレッドで開始
    bgWorker.RunWorkerAsync(100)
    ' DoWorkイベント発生
  End Sub

  ' 時間のかかる処理を行うメソッド
  Private Sub bgWorker_DoWork(ByVal sender As System.Object, ByVal e As System.ComponentModel.DoWorkEventArgs) Handles bgWorker.DoWork
    ' 別スレッドで実行されるため、このメソッドでは
    ' UI(コントロール)を操作してはいけない

    ' このメソッドへのパラメータ
    Dim bgWorkerArg As Integer = CType(e.Argument, Integer)

    ' senderの値はbgWorkerの値と同じ
    Dim worker As BackgroundWorker = CType(sender, BackgroundWorker)

    ' 時間のかかる処理
    For i As Integer = 1 To bgWorkerArg

      System.Threading.Thread.Sleep(100)

      Dim percentage As Integer = i * 100 / bgWorkerArg ' 進ちょく率
      worker.ReportProgress(percentage)
      ' ProgressChangedイベント発生
    Next

    ' このメソッドからの戻り値
    e.Result = "すべて完了"

    ' この後、RunWorkerCompletedイベントが発生
  End Sub

  Private Sub bgWorker_ProgressChanged(ByVal sender As Object, ByVal e As System.ComponentModel.ProgressChangedEventArgs) Handles bgWorker.ProgressChanged
    ' 進ちょく状況の表示
    Me.Text = e.ProgressPercentage & "%完了"
    progressBar.Value = e.ProgressPercentage
  End Sub

  Private Sub bgWorker_RunWorkerCompleted(ByVal sender As Object, ByVal e As System.ComponentModel.RunWorkerCompletedEventArgs) Handles bgWorker.RunWorkerCompleted
    ' 処理結果の表示
    Me.Text = e.Result.ToString()
    MessageBox.Show("正常に完了")

    buttonStart.Enabled = True
  End Sub
End Class
トラックバック - http://sub.g.hatena.ne.jp/garyo/20190212

2019-02-08

トラックバック - http://sub.g.hatena.ne.jp/garyo/20190208

2019-02-06

デリゲートラムダ式

Public Delegate Function Func1(ByVal msg As String) As Boolean

デリゲート関数の戻りの型と引数の型を定義した関数ポインタ型みたいなもので、その型で定義した変数に、同じ型のラムダ式を代入できる。

デリゲートインスタンス化でき、その時にはnewと既存関数へのAddressOfを使用する

Public Delegate Sub Sub1()

Dim s = New Sub1(AddressOf test)

Public Class Form1

    Public Delegate Function Func1(x As Integer) As Integer
    Public Delegate Sub Sub1()
    Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click

        Dim y As Func1

        Dim inc As Func1 = Function(x) x + 1

        Dim inc2 As Func1 = Function(x)
                                Return x + 2
                            End Function

        y = inc
        MsgBox(y(1))
        y = inc2
        MsgBox(y(1))

           Dim s As Sub1

        s = New Sub1(AddressOf test)

        s()

    End Sub

    Sub test()
        MsgBox("test")
    End Sub

End Class

マルチスレッド

Public Class Form1

    Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click

        Me.BackgroundWorker1.RunWorkerAsync()

    End Sub

    Private Sub BackgroundWorker1_DoWork(sender As Object, e As System.ComponentModel.DoWorkEventArgs) Handles BackgroundWorker1.DoWork
        Do
            Label1.Text = Now.ToLongTimeString()
            Threading.Thread.Sleep(1000)
        Loop
    End Sub

    Private Sub Form1_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
        Control.CheckForIllegalCrossThreadCalls = False
    End Sub
End Class


プログレスバーマルチスレッド

Public Class Form1

    Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click
        BackgroundWorker1.WorkerReportsProgress = True
        BackgroundWorker1.WorkerSupportsCancellation = True
        BackgroundWorker1.RunWorkerAsync()

    End Sub

    Private Sub BackgroundWorker1_DoWork(sender As Object, e As System.ComponentModel.DoWorkEventArgs) Handles BackgroundWorker1.DoWork

        For i As Integer = 1 To 100
            Threading.Thread.Sleep(1000)

            If BackgroundWorker1.CancellationPending = True Then
                e.Cancel = True
                Exit For
            End If
            BackgroundWorker1.ReportProgress(i)
        Next
    End Sub

    Private Sub BackgroundWorker1_ProgressChanged(sender As Object, e As System.ComponentModel.ProgressChangedEventArgs) Handles BackgroundWorker1.ProgressChanged
        ProgressBar1.Value = e.ProgressPercentage
    End Sub

    Private Sub BackgroundWorker1_RunWorkerCompleted(sender As Object, e As System.ComponentModel.RunWorkerCompletedEventArgs) Handles BackgroundWorker1.RunWorkerCompleted
        MsgBox("終了")
        ProgressBar1.Value = 0
    End Sub

    Private Sub Button2_Click(sender As System.Object, e As System.EventArgs) Handles Button2.Click
        BackgroundWorker1.CancelAsync()
    End Sub

    Private Sub Form1_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load

    End Sub
End Class

マルチスレッドクラス

Imports System.ComponentModel
'http://multithreadingapp.blogspot.com/

Enum ST_NO
    E_IDLE
    E_BUSY
    E_CANCEL
    E_ERR
    E_DONE
    E_ALL_NUM
End Enum
Public Delegate Function Job() As Integer

Public Class clsMultiThread

    Dim bw As BackgroundWorker = New BackgroundWorker
    Dim m_progress As Integer = 0
    Dim m_status_msg As String = ""
    Dim m_status_no As Integer = 0

    Public m_job As Job

    Sub New()
        bw.WorkerSupportsCancellation = True
        bw.WorkerReportsProgress = True

        AddHandler bw.DoWork, AddressOf bw_DoWork
        AddHandler bw.ProgressChanged, AddressOf bw_ProgressChanged
        AddHandler bw.RunWorkerCompleted, AddressOf bw_RunWorkerCompleted

        m_status_msg = "IDLE"
        m_status_no = ST_NO.E_IDLE

    End Sub

    Function getStatusMessage() As String
        Return m_status_msg
    End Function

    Function getStatusNo() As Integer
        Return m_status_no
    End Function

    Private Sub bw_DoWork(ByVal sender As Object, ByVal e As DoWorkEventArgs)

        Dim worker As BackgroundWorker = CType(sender, BackgroundWorker)
        m_status_msg = "BUSY"
        m_status_no = ST_NO.E_BUSY

        Dim Ret As Integer

        Do While True

            Ret = m_job()

            If bw.CancellationPending = True Then
                e.Cancel = True
                Exit Do
            Else
                ' Perform a time consuming operation and report progress.
                System.Threading.Thread.Sleep(500)
                bw.ReportProgress(Ret)
            End If

        Loop



        For i = 1 To 10
            If bw.CancellationPending = True Then
                e.Cancel = True
                Exit For
            Else
                ' Perform a time consuming operation and report progress.
                System.Threading.Thread.Sleep(500)
                bw.ReportProgress(i * 10)
            End If
        Next
    End Sub

    Private Sub bw_ProgressChanged(ByVal sender As Object, ByVal e As ProgressChangedEventArgs)
        m_progress = e.ProgressPercentage
        'Me.tbProgress.Text = e.ProgressPercentage.ToString() & "%"
    End Sub

    Private Sub bw_RunWorkerCompleted(ByVal sender As Object, ByVal e As RunWorkerCompletedEventArgs)
        If e.Cancelled = True Then
            m_status_msg = "Canceled!"
            m_status_no = ST_NO.E_CANCEL
            'Me.tbProgress.Text = "Canceled!"
        ElseIf e.Error IsNot Nothing Then
            m_status_msg = "Error: " & e.Error.Message
            m_status_no = ST_NO.E_ERR
            'Me.tbProgress.Text = "Error: " & e.Error.Message
        Else
            m_status_msg = "Done!"
            m_status_no = ST_NO.E_DONE
            'Me.tbProgress.Text = "Done!"
            MsgBox("Done")
        End If
    End Sub

    Public Sub start()
        If Not bw.IsBusy = True Then
            bw.RunWorkerAsync()
        End If
    End Sub

    Public Sub cancel()
        If bw.WorkerSupportsCancellation = True Then
            bw.CancelAsync()
        End If
    End Sub

End Class

トラックバック - http://sub.g.hatena.ne.jp/garyo/20190206

2018-03-28

スクロールバーを一番下へスクロール

https://dobon.net/vb/dotnet/control/tbscrolltolast.html

'カレット位置を末尾に移動
TextBox1.SelectionStart = TextBox1.Text.Length
'テキストボックスにフォーカスを移動
TextBox1.Focus()
'カレット位置までスクロール
TextBox1.ScrollToCaret()
トラックバック - http://sub.g.hatena.ne.jp/garyo/20180328

2017-05-31

ExcelグラフVB.netで表示

http://www.officepro.jp/excelvba/chart/index4.html

'[参照設定の追加]-[COM]タブから

'Microsoft Excel xx.0 Object Libraryを選択する必要あり。

Imports Microsoft.Office.Core
Imports Microsoft.Office.Interop
Imports Microsoft.Office.Interop.Excel

Public Class ClsExcel

    Dim CurrentSheet As Integer

    Public Property ExcelObj As Excel.Application

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

    Public Sub New()
        ExcelObj = New Excel.Application()
        ExcelObj.Visible = True  ' Excel を表示する場合のみ
        ExcelObj.DisplayAlerts = False  ' 保存時の確認メッセージボックスを表示しない場合のみ
    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>
    Public Sub CreateBook()
        Book = ExcelObj.Workbooks.Add()
        Sheet = Book.Worksheets(1)
    End Sub
    ''' <summary>
    ''' 新しいワークシートを追加する。
    ''' </summary>
    ''' <remarks></remarks>
    Public Sub AddSheet()
        Sheet = Book.Worksheets.Add()
    End Sub
    ''' <summary>
    ''' ブックを選択する
    ''' </summary>
    Public Sub setBook(bookname As String)
        Sheet = Book.Worksheets(bookname)
    End Sub
    ''' <summary>
    ''' ブックを選択する
    ''' </summary>
    Public Sub setBookByNo(bookno As Integer)
        Sheet = Book.Worksheets(bookno)
    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 System.Data.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 System.Data.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>
    Public Sub Close()
        If Book IsNot Nothing Then
            Book.Close()
        End If
    End Sub

    ''' <summary>
    ''' Excel を閉じる
    ''' </summary>
    Public Sub Quit()
        ExcelObj.Quit()
    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

    Public Sub test(f As Form1)
        Dim chartObj As ChartObject
        Dim chart1 As Chart

        chartObj = Sheet.ChartObjects(1)
        chart1 = chartObj.Chart
        chart1.SetSourceData(Book.Worksheets("Sheet1").Range("B2:E6"))

        chart1.Export(Filename:=My.Application.Info.DirectoryPath & "\test001.GIF", FilterName:="GIF")

        Using fs As System.IO.FileStream = New System.IO.FileStream(My.Application.Info.DirectoryPath & _
       "\test001.GIF", System.IO.FileMode.Open, System.IO.FileAccess.Read)
            f.PictureBox1.Image = System.Drawing.Image.FromStream(fs)
        End Using
    End Sub

End Class

Excel操作クラス

'[参照設定の追加]-[COM]タブから

'Microsoft Excel xx.0 Object Libraryを選択する必要あり。

Imports Microsoft.Office.Core
Imports Microsoft.Office.Interop

Public Class ClsExcel

    Dim CurrentSheet As Integer

    Public Property ExcelObj As Excel.Application

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

    Public Sub New()
        ExcelObj = New Excel.Application()
        ExcelObj.Visible = True  ' Excel を表示する場合のみ
        ExcelObj.DisplayAlerts = False  ' 保存時の確認メッセージボックスを表示しない場合のみ
    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>
    Public Sub CreateBook()
        Book = ExcelObj.Workbooks.Add()
        Sheet = Book.Worksheets(1)
    End Sub
    ''' <summary>
    ''' 新しいワークシートを追加する。
    ''' </summary>
    ''' <remarks></remarks>
    Public Sub AddSheet()
        Sheet = Book.Worksheets.Add()
    End Sub
    ''' <summary>
    ''' ブックを選択する
    ''' </summary>
    Public Sub setBook(bookname As String)
        Sheet = Book.Worksheets(bookname)
    End Sub
    ''' <summary>
    ''' ブックを選択する
    ''' </summary>
    Public Sub setBookByNo(bookno As Integer)
        Sheet = Book.Worksheets(bookno)
    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>
    Public Sub Close()
        If Book IsNot Nothing Then
            Book.Close()
        End If
    End Sub

    ''' <summary>
    ''' Excel を閉じる
    ''' </summary>
    Public Sub Quit()
        ExcelObj.Quit()
    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

End Class

Public Class Form1

    Public Property m_CE As New ClsExcel

    Private Sub QuitQToolStripMenuItem_Click(sender As System.Object, e As System.EventArgs) Handles QuitQToolStripMenuItem.Click
        End
    End Sub

    Private Sub BtnRead1_Click(sender As System.Object, e As System.EventArgs) Handles BtnRead1.Click

    End Sub

    Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click
        m_CE.Open("C:\temp\book1.xlsx")
        m_CE.setBook("Sheet1")
        MsgBox(m_CE.GetCellValue(1, 1))
        m_CE.SetCellValue(2, 1, "ABC")
        MsgBox(m_CE.GetCellValue(2, 1))
        m_CE.Save()
        m_CE.Close()
        m_CE.Quit()
    End Sub

    Private Sub Form1_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load


    End Sub
End Class
トラックバック - http://sub.g.hatena.ne.jp/garyo/20170531