VB.NET覚書

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

2019-05-11

clsExcel

'「COM」の Microsoft Excel x.x Object Library の参照設定の追加必要
Imports Microsoft.Office.Interop
Imports System.Runtime.InteropServices

Public Class clsExcel

    ''' <summary>
    ''' ファイル名
    ''' </summary>
    Public Property fileName As String

    ''' <summary>
    ''' ファイルパス
    ''' </summary>
    Public Property filePath As String

    ''' <summary>
    ''' シート名
    ''' </summary>
    Public Property sheetName As String

    Public Property app As Excel.Application = Nothing
    Public Property book As Excel.Workbook = Nothing
    Public Property sheet As Excel.Worksheet = Nothing

    ''' <summary>
    ''' セル
    ''' </summary>
    ''' <param name="y">行</param>
    ''' <param name="x">列</param>
    Public Property cells(y As Integer, x As Integer) As String
        Get
            Return sheet.Cells(y, x)
        End Get
        Set(value As String)
            sheet.Cells(y, x) = value
        End Set
    End Property

    ''' <summary>
    ''' セル書式(文字列 "@")
    ''' </summary>
    ''' <param name="y">行</param>
    ''' <param name="x">列</param>
    Public Property cellFormat(y As Integer, x As Integer) As String
        Get
            Return sheet.Range(sheet.Cells(y, x), sheet.Cells(y, x)).NumberFormatLocal
        End Get
        Set(value As String)
            sheet.Range(sheet.Cells(y, x), sheet.Cells(y, x)).NumberFormatLocal = value
        End Set
    End Property

    ''' <summary>
    ''' 列幅設定
    ''' </summary>
    ''' <param name="s">列 例("B:C")</param>
    Public Property columnWidth(s As String) As Integer
        Get
            Return sheet.Range(s).ColumnWidth
        End Get
        Set(value As Integer)
            sheet.Range(s).ColumnWidth = value
        End Set
    End Property

    ''' <summary>
    ''' 行の高さ設定
    ''' </summary>
    ''' <param name="s">行 例("2:5")</param>
    Public Property RowHeight(s As Integer) As Integer
        Get
            Return sheet.Range(s).RowHeight
        End Get
        Set(value As Integer)
            sheet.Range(s).RowHeight = value
        End Set
    End Property

    ''' <summary>
    ''' 新規作成
    ''' </summary>
    Public Function create() As Boolean

        Dim Jug As Boolean = True

        'シートの作成
        app = New Excel.Application()
        app.Workbooks.Add()
        book = app.Workbooks(1)
        sheet = CType(book.Worksheets(1), Excel.Worksheet)
        'シート名を設定する
        sheet.Name = sheetName

        Return Jug

    End Function


    ''' <summary>
    ''' ファイル保存
    ''' </summary>
    Public Function save() As Boolean

        Dim Jug As Boolean = True

        Try

            '保存する
            book.SaveAs(filePath & fileName, FileFormat:=Excel.XlFileFormat.xlExcel8)

        Catch ex As Exception
            Throw ex
            Jug = False

            'オブジェクト解放
            app.Quit()
            Marshal.ReleaseComObject(sheet)
            Marshal.ReleaseComObject(book)
            Marshal.ReleaseComObject(app)

        Finally

        End Try

        Return Jug

    End Function

    ''' <summary>
    ''' ファイル解放
    ''' </summary>
    Public Function fileClose() As Boolean

        Dim Jug As Boolean = True

        'オブジェクト解放
        app.Quit()
        Marshal.ReleaseComObject(sheet)
        Marshal.ReleaseComObject(book)
        Marshal.ReleaseComObject(app)

        Return Jug

    End Function


    'Public Sub ExcelCreate()

    '    '定義
    '    Dim app As Excel.Application = Nothing
    '    Dim book As Excel.Workbook = Nothing
    '    Dim sheet As Excel.Worksheet = Nothing

    '    'Try
    '    'シートの作成
    '    app = New Excel.Application()
    '    app.Workbooks.Add()
    '    book = app.Workbooks(1)
    '    sheet = CType(book.Worksheets(1), Excel.Worksheet)

    '    'シート名を設定する
    '    sheet.Name = sheetName

    '    'セルに値をセットする(セル名で指定する場合)
    '    sheet.Range("A1").Value = "ABC"

    '    'セルに値をセットする(番号で指定する場合)
    '    sheet.Cells(2, 1) = "DEF"   '2行・1列目(A2セル)

    '    '保存する
    '    book.SaveAs(filePath & "\" & fileName, FileFormat:=Excel.XlFileFormat.xlExcel8)

    '    'Catch ex As Exception
    '    '    Throw ex

    '    'Finally
    '    'オブジェクト解放
    '    app.Quit()
    '    Marshal.ReleaseComObject(sheet)
    '    Marshal.ReleaseComObject(book)
    '    Marshal.ReleaseComObject(app)

    '    'End Try
    'End Sub

End Class



clsTxtFile

Public Class clsTextFile
    ''' <summary>
    ''' 読み込んだデータ
    ''' </summary>
    Private m_line As New ArrayList
    ''' <summary>
    ''' ファイル名
    ''' </summary>
    Private m_filename As String

    ''' <summary>
    ''' ファイル名
    ''' </summary>
    Public Property filename As String
        Get
            Return m_filename
        End Get
        Set(value As String)
            m_filename = value
        End Set
    End Property

    ''' <summary>
    ''' 読み込んだデータ
    ''' </summary>
    Public Property line As ArrayList
        Get
            Return m_line
        End Get
        Set(value As ArrayList)
            m_line = value
        End Set
    End Property

    ''' <summary>
    ''' テキストファイル読込
    ''' </summary>
    Public Function readFile() As Boolean

        m_line.Clear()

        Return readFile_append()

    End Function

    ''' <summary>
    ''' テキストファイル書込
    ''' </summary>
    Public Function saveFile() As Boolean

        Dim F As Integer
        Dim Ret As String

        Ret = True

        F = FreeFile()

        FileOpen(F, m_filename, OpenMode.Output)

        For Each Item As String In m_line
            'System.Windows.Forms.Application.DoEvents()
            PrintLine(F, Item)
        Next

        FileClose(F)

        Return Ret

    End Function

    ''' <summary>
    ''' 追加読込
    ''' </summary>
    Public Function readFile_append() As Boolean

        Dim F As Integer
        Dim S As String
        Dim Ret As String

        Ret = True

        F = FreeFile()

        FileOpen(F, m_filename, OpenMode.Input)

        Do While Not EOF(F)
            'System.Windows.Forms.Application.DoEvents()
            S = LineInput(F)
            m_line.Add(S)
        Loop

        FileClose(F)

        Return Ret

    End Function

    ''' <summary>
    ''' 追加書込
    ''' </summary>
    Public Function saveFile_append() As Boolean

        Dim F As Integer
        Dim Ret As String

        Ret = True

        F = FreeFile()

        FileOpen(F, m_filename, OpenMode.Append)

        For Each Item As String In m_line
            'System.Windows.Forms.Application.DoEvents()
            PrintLine(F, Item)
        Next

        FileClose(F)

        Return Ret

    End Function

    ''' <summary>
    ''' CSV形式の1行から指定した列を取り出す(列番号は0から)
    ''' </summary>
    ''' <param name="str">文字列</param>
    ''' <param name="n">列</param>
    Public Function csvRead(str As String, n As Integer) As String

        Dim rdline As String()
        Dim ret As String

        rdline = str.Split(",")
        ret = rdline(n)
        Return ret

    End Function

    ''' <summary>
    ''' データ読出し
    ''' </summary>
    ''' <param name="x">列</param>
    ''' <param name="y">行</param>
    Public Function getData(x As Integer, y As Integer) As String

        Return csvRead(m_line.Item(y), x)

    End Function

    ''' <summary>
    ''' 列数を返す
    ''' </summary>
    ''' <param name="y">行</param>
    Public Function getDataColumn(y As String) As Integer

        Dim rdline As String()
        Dim ret As String

        rdline = y.Split(",")
        ret = rdline.Length
        Return ret

    End Function

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