ぷろぐらむノート

Excelファイル（xlsx形式）を作成する事が出来る ClosedXML の説明をします。

ClosedXMLというライブラリは、このOpenXML SDKをラップして、簡単に操作できるライブラリです。

ClosedXMLは、 NuGet を使ってパッケージをインストール出来ます。Nugetで「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 wksheet As IXLWorksheet = book.Worksheets.Add("MySheet")

        wksheet.Cell("B2").Value = "Hello World!!"

        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 wksheet As IXLWorksheet = book.Worksheets.Add("MySheet")

        Dim cell As IXLCell = wksheet.Cell("B2")
        With cell
            .Value = "Hello World!"
            .Style.Font.FontSize = 24
            .Style.Font.FontColor = XLColor.Red
            .Style.Border.OutsideBorder = XLBorderStyleValues.DashDotDot
        End With

        Dim cell2 As IXLCell = wksheet.Cell(5, 3)
        With cell2
            .Value = "Hello World!"
            .Style.Font.FontSize = 24
            .Style.Font.FontColor = XLColor.Red
            .Style.Border.OutsideBorder = XLBorderStyleValues.DashDotDot
        End With

        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("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