ClosedXML シートを取得・追加・削除・コピーする

ブックにシートを取得・追加・削除・コピーする方法を記載します。

シートをコピーする

Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
    Dim FilePath As String = "C:\work\HelloWorld.xlsx"

    Using book As XLWorkbook = New XLWorkbook()
        Dim wksheet1 As IXLWorksheet = book.Worksheets.Add("MySheet")

        'シートをコピーする
        wksheet1.CopyTo("SheetCopy1")
        wksheet1.CopyTo("SheetCopy2")

        book.SaveAs(FilePath)
    End Using
End Sub

 

シートを削除する

Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
    Dim FilePath As String = "C:\work\HelloWorld.xlsx"

    Using book As XLWorkbook = New XLWorkbook()
        Dim wksheet1 As IXLWorksheet = book.Worksheets.Add("MySheet")

        wksheet1.CopyTo("SheetCopy2")

        'シートを削除する
        book.Worksheet(1).Delete()
        'または
        book.Worksheet("SheetCopy2").Delete()

        book.SaveAs(FilePath)
    End Using
End Sub

 

シートの取得する

Dim FilePath As String = "C:\work\HelloWorld.xlsx"

Using book As XLWorkbook = New XLWorkbook(FilePath)

    '最初のシートを取得
    Dim wksheet1 As IXLWorksheet = book.Worksheet(1)

    'MySheetを取得
    Dim wksheet As IXLWorksheet = Nothing
    If (book.TryGetWorksheet("MySheet", wksheet)) = False Then
        Console.WriteLine("取得できません")
    End If

End Using

 

シートの追加する

Dim FilePath As String = "C:\work\HelloWorld.xlsx"

Using book As XLWorkbook = New XLWorkbook(FilePath)

    Dim wksheet2 As IXLWorksheet = book.AddWorksheet("シート2")
    'または
    Dim wksheet3 As IXLWorksheet = book.Worksheets.Add("シート3")

    Dim wksheet1 As IXLWorksheet = book.AddWorksheet("シート1", 1) '最初のシートとして作成
End Using