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

シートをコピーする

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

 

セル内の文字の属性、セルの属性、セルのコピー、などセルの操作方法を記載します。

文字属性を設定する

Using book As XLWorkbook = New XLWorkbook(FilePath)
    Dim wksheet As IXLWorksheet = book.Worksheet(1)

    Dim cell As IXLCell = wksheet.Cell("A1")

    cell.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Left
    cell.Value = "左寄せ"

    'セルを１つ下に移す
    cell = cell.CellBelow()
    'セルを１つ右に移す
    cell = cell.CellRight()

    cell.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center
    cell.Value = "中央寄せ"

    '太字
    cell.Style.Font.Bold = True
    '文字色
    cell.Style.Font.FontColor = XLColor.Red
    'フォントの設定
    cell.Style.Font.FontSize = 16
    cell.Style.Font.FontName = "ＭＳ Ｐ明朝"

    book.SaveAs(FilePath)
End Using

 

セル属性を設定する

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

Using book As XLWorkbook = New XLWorkbook(FilePath)
    Dim wksheet As IXLWorksheet = book.Worksheet(1)

    'セルに罫線を引く
    Dim cell1 As IXLCell = wksheet.Cell(1, 1)
    cell.Value = "罫線の設定"
    cell.Style.Border.BottomBorder = XLBorderStyleValues.Thin
    cell.Style.Border.BottomBorderColor = XLColor.Red
    '塗りつぶしの設定
    cell.Style.Fill.BackgroundColor = XLColor.BabyBlue

    'セル範囲に罫線を引く
    Dim range2 As IXLRange = wksheet.Range("B4:D8")
    range2.Style.Border.OutsideBorder = XLBorderStyleValues.DashDotDot
    range2.Style.Border.BottomBorderColor = XLColor.Red
    range2.Style.Fill.PatternType = XLFillPatternValues.DarkDown
    range2.Style.Fill.PatternColor = XLColor.Gray
    range2.Style.Fill.PatternBackgroundColor = XLColor.Blue

    book.SaveAs(FilePath)
End Using

 

セルをコピーする

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

Using book As XLWorkbook = New XLWorkbook(FilePath)
    Dim wksheet As IXLWorksheet = book.Worksheet(1)

    'コピー範囲を取得
    Dim range1 As IXLRange = wksheet.Range("B4:D8")

    'コピー位置を取得
    Dim cell1 As IXLCell = wksheet.Cell("E10")

    'コピー
    range1.CopyTo(cell1)

    book.SaveAs(FilePath)
End Using

 

セルサイズ自動調整する

'ワークシート中の全ての列幅を自動調整
wksheet.Columns().AdjustToContents()

'指定列のカラムを、自動調整
wksheet.Columns(3, 10).AdjustToContents()

'ワークシート中の全ての行の高さを自動調整
wksheet.Rows().AdjustToContents()

'指定行目を自動調整
wksheet.Rows(3, 10).AdjustToContents()

 

コレクションで設定する

'文字列のリストを作成
Dim slist As New List(Of String)
slist.Add("Hello")
slist.Add("World")

'コレクション全てセルに設定
Dim cell1 As IXLCell = wksheet.Cell(1, 2)
cell1.Value = slist