1
Help us understand the problem. What are the problem?

More than 1 year has passed since last update.

posted at

updated at

VB.NET MVCにてOpenXMLを使用して、Excelをサーバーサイドで生成しダウンロードする

はじめに

集計表などの帳票出力機能でExcelファイルをダウンロードさせる機能を作ることになったときに調べたことのメモ。
開発前に参考資料としてエクセル出力はこちらを参考にとこのURLを教えていただき、MemoryStreamに出力する方法はわかった。しかし、残念ながら今回参画したプロジェクトの使用言語はVBであったことと、罫線や値のセル内の配置位置、複数シート作成、というようなこともできないと困るので、他にいくつかサイトを検索しC#で書かれていたサンプルコードをVBでも動作するように改変した。

開発スケジュールの制約からあまりきれいにはまとまっていないが、とにかくExcelファイル作成ができるようになるための機能をOpenxmlUtility.vbというファイルにまとめ、レイアウトに関してはmodelで頑張って書くことで凌ぐことにした。

動作環境

  • サーバー側
    • ASP.NET(VB) MVC
    • OpenXML

改変したソースコード

C#で書かれた情報が載っているだけでもありがたい。下記は参考サイトをもとに罫線や値のセル内の配置位置指定のメソッドをまとめたファイルである。

OpenxmlUtility.vb
Imports System
Imports DocumentFormat.OpenXml
Imports DocumentFormat.OpenXml.Packaging
Imports DocumentFormat.OpenXml.Spreadsheet
Imports Color = DocumentFormat.OpenXml.Spreadsheet.Color
Imports System.ComponentModel
Imports System.ComponentModel.DataAnnotations


Namespace Models

    ''' <summary>
    ''' OpenXmlの操作に便利なメソッド
    ''' Models直下に任意の名前で作成した。好きなところに置けばよい。Importsは必要なものを後で整理する。
    ''' </summary>
    Public Class OpenXmlUtility

        Private _DefaultFileName As String = "dummy.xlsx"

        Public ReadOnly Property DefaultFileName() As String
            Get
                Return _DefaultFileName
            End Get
        End Property



        ''' <summary>
        ''' 指定したセルの値の配置を設定する
        ''' 
        ''' </summary>
        ''' <param name="bookPart"></param>
        ''' <param name="cell"></param>
        ''' <param name="hAlign"></param>
        ''' <param name="vAlign"></param>
        Public Sub SetCellAlignment(ByRef bookPart As WorkbookPart,ByRef cell As Cell, hAlign As HorizontalAlignmentValues, vAlign As VerticalAlignmentValues)
            If cell Is Nothing Or bookPart Is Nothing Then
                Return
            End If

            Dim cellFormats = bookPart.WorkbookStylesPart.Stylesheet.CellFormats.Elements(Of CellFormat)
            If cell.StyleIndex IsNot Nothing Then
                Dim currentXf = cellFormats.ElementAt(Convert.ToInt32(cell.StyleIndex.Value))

                '同じ属性をもち、かつ指定されたalignと同じ設定をもつCellFormatを探す
                Dim formatTo As CellFormat
                Try
                    formatTo = cellFormats.FirstOrDefault(Function(r) r.GetAttributes().SequenceEqual(currentXf.GetAttributes()) _
                                                                And r.Alignment IsNot Nothing _
                                                                And r.Alignment.Horizontal IsNot Nothing And r.Alignment.Horizontal.Value = hAlign _
                                                                And r.Alignment.Vertical IsNot Nothing And r.Alignment.Vertical.Value = vAlign)
                Catch ex As NullReferenceException

                End Try
                If formatTo Is Nothing Then
                    formatTo                            = currentXf.Clone()

                    If formatTo.Alignment Is Nothing Then
                        formatTo.Alignment            = New Alignment()
                    End If
                    If formatTo.Alignment.Horizontal Is Nothing Then
                        formatTo.Alignment.Horizontal = New EnumValue(Of HorizontalAlignmentValues)
                    End If
                    formatTo.Alignment.Horizontal.Value = hAlign

                    If formatTo.Alignment.Vertical Is Nothing Then
                        formatTo.Alignment.Vertical   = New EnumValue(Of VerticalAlignmentValues)
                    End If
                    formatTo.Alignment.Vertical.Value   = vAlign

                    bookPart.WorkbookStylesPart.Stylesheet.CellFormats.Append(formatTo)
                    bookPart.WorkbookStylesPart.Stylesheet.Save()
                    cell.StyleIndex = New UInt32Value(CType(cellFormats.LongCount(), UInteger) -1UI)
                Else
                    cell.StyleIndex = New UInt32Value(CType(cellFormats.ToList().IndexOf(formatTo), UInteger))
                End If
            Else
                Dim formatTo As CellFormat          = New CellFormat()
                formatTo.Alignment                  = New Alignment()

                formatTo.Alignment.Horizontal       = New EnumValue(Of HorizontalAlignmentValues)
                formatTo.Alignment.Horizontal.Value = hAlign

                formatTo.Alignment.Vertical         = New EnumValue(Of VerticalAlignmentValues) 
                formatTo.Alignment.Vertical.Value   = vAlign

                bookPart.WorkbookStylesPart.Stylesheet.CellFormats.Append(formatTo)
                bookPart.WorkbookStylesPart.Stylesheet.Save()
                cell.StyleIndex                     = New UInt32Value(CType(cellFormats.LongCount(), UInteger) -1UI)

            End If
        End Sub

        ''' <summary>
        ''' 
        ''' </summary>
        ''' <param name="targetWsPart"></param>
        ''' <param name="cell"></param>
        ''' <param name="cellAddress"></param>
        ''' <param name="border"></param>
        Public Sub AppendCellBorder(ByRef targetWsPart As WorksheetPart,ByRef cell As Cell, cellAddress As String, border As Border)
            'Dim cell As Cell = GetCell(targetWsPart, cellAddress)
            Dim bookPart As WorkbookPart = targetWsPart.GetParentParts().First()

            Dim cellFormat As CellFormat
            If cell.StyleIndex Is Nothing Then
                cellFormat = new CellFormat()
            Else
                cellFormat = bookPart.WorkbookStylesPart.Stylesheet.CellFormats.ElementAt(CType(cell.StyleIndex.Value, Integer))
            End If

            Dim currentBorder As Border
            Dim mergedBorder  As Border
            If cellFormat.BorderId Is Nothing Then
                mergedBorder = border
            Else
                currentBorder = bookPart.WorkbookStylesPart.Stylesheet.Borders.ElementAt(CType(cellFormat.BorderId.Value, Integer))
                mergedBorder  = MergeBorder(currentBorder, border)
            End If

            Dim sameBorder As Border = bookPart.WorkbookStylesPart.Stylesheet.Borders.FirstOrDefault(Function(b)  b.OuterXml = mergedBorder.OuterXml)
            Dim borderId As UInt32

            If sameBorder Is Nothing Then
                bookPart.WorkbookStylesPart.Stylesheet.Borders.Append(mergedBorder)
                bookPart.WorkbookStylesPart.Stylesheet.Borders.Count.Value += 1
                borderId   = bookPart.WorkbookStylesPart.Stylesheet.Borders.Count.Value - 1
                sameBorder = mergedBorder
            Else
                borderId = bookPart.WorkbookStylesPart.Stylesheet.Borders.ToList().IndexOf(sameBorder)
            End If

            'borderIdを持ち、かつその他の属性が「cellFormatのborder以外」と一致するようなcellFormatを探す
            Dim cellFormatEnumerable As IEnumerable(Of CellFormat) = bookPart.WorkbookStylesPart.Stylesheet.CellFormats.Elements(Of CellFormat)()
            Dim targetFormat As CellFormat
            Try

                targetFormat = cellFormatEnumerable.FirstOrDefault(Function(xf) xf.GetAttributes(). _
                                                                                        Where(Function(at) at.LocalName <> "borderId" ).SequenceEqual(cellFormat.GetAttributes(). _ 
                                                                                        Where(Function(at) at.LocalName <> "borderId")) _ 
                                                                                        And (xf.BorderId <> Nothing And xf.BorderId.Value = borderId) And xf.InnerXml = cellFormat.InnerXml)
            Catch ex As NullReferenceException

            End Try
            If targetFormat Is Nothing Then
                targetFormat = cellFormat.CloneNode(True)
                If targetFormat.BorderId Is Nothing Then
                    targetFormat.BorderId = New UInt32Value(CType(borderId, UInteger))
                Else
                    targetFormat.BorderId.Value = CType(borderId, UInteger)
                End If

                bookPart.WorkbookStylesPart.Stylesheet.CellFormats.Append(targetFormat)
                bookPart.WorkbookStylesPart.Stylesheet.CellFormats.Count.Value += 1

                cell.StyleIndex = new UInt32Value(CType(bookPart.WorkbookStylesPart.Stylesheet.CellFormats.Count.Value - 1, UInteger))

            Else
                cell.StyleIndex = new UInt32Value(CType(cellFormatEnumerable.ToList().IndexOf(targetFormat), UInteger))
            End If

        End Sub

        Public Function MergeBorder(borderModified As Border,borderAppend As  Border) As Border
            Dim mergedBorder As Border = borderModified.CloneNode(true)

            'TopBorder
            If borderAppend.TopBorder IsNot Nothing And borderAppend.TopBorder.HasAttributes Then
                mergedBorder.TopBorder = borderAppend.TopBorder.CloneNode(true)
            End If

            'LeftBorder
            If borderAppend.LeftBorder IsNot Nothing And borderAppend.LeftBorder.HasAttributes Then
                mergedBorder.LeftBorder = borderAppend.LeftBorder.CloneNode(true)
            End If

            'BottomBorder
            If borderAppend.BottomBorder IsNot Nothing And borderAppend.BottomBorder.HasAttributes Then
                mergedBorder.BottomBorder = borderAppend.BottomBorder.CloneNode(true)
            End If

            'RightBorder
            If borderAppend.RightBorder IsNot Nothing And borderAppend.RightBorder.HasAttributes Then
                mergedBorder.RightBorder = borderAppend.RightBorder.CloneNode(true)
            End If

            'DiagonalBorder
            If borderAppend.DiagonalBorder IsNot Nothing And borderAppend.DiagonalBorder.HasAttributes Then
                mergedBorder.DiagonalBorder = borderAppend.DiagonalBorder.CloneNode(true)
            End If

            'DiagonalAttribute
            If borderAppend.DiagonalDown IsNot Nothing  Then
                mergedBorder.DiagonalDown = borderAppend.DiagonalDown.Clone()
            End If

            If borderAppend.DiagonalUp IsNot Nothing  Then
                mergedBorder.DiagonalUp = borderAppend.DiagonalUp.Clone()
            End If

            Return mergedBorder

        End Function

        ''' <summary>
        ''' セルアドレスで指定されたセルのオブジェクトを取得する(はずだが「sheetPart.Worksheet.Descendants(Of Row)().FirstOrDefault(Function(r As Row) r.RowIndex.Value = rowIndex)」で落ちる)
        ''' </summary>
        ''' <param name="sheetPart"></param>
        ''' <param name="cellAddress"></param>
        ''' <returns></returns>
        Private Function GetCell(sheetPart As WorksheetPart, cellAddress As String) As Cell

            Dim rowIndex As UInt32 = GetRowIndex(cellAddress)
            'Dim shData As SheetData = sheetPart.Worksheet.Descendants(Of SheetData)().First()  <- これは動く
            '下記行のようにrowを取得するとNothingのときNullReferenceException発生で先の処理に進めなくなったので改変する
            'Dim row As Row         = sheetPart.Worksheet.Descendants(Of Row)().FirstOrDefault(Function(r As Row) r.RowIndex.Value = rowIndex)
            Dim row As Row
            If sheetPart.Worksheet.Descendants(Of Row)().FirstOrDefault(Function(r As Row) r.RowIndex.Value = rowIndex) Is Nothing Then
                row = Nothing
            End If
            'Dim row As Row = Nothing
            If row Is Nothing Then
                row = New Row() With { .RowIndex = new UInt32Value(rowIndex) }

                Dim shData As SheetData = sheetPart.Worksheet.Descendants(Of SheetData)().First()
                '場所を特定して行を挿入(行は行番号で昇順に並ばないとだめ)
                Dim rRow As Row = shData.Elements(Of Row).LastOrDefault(Function(r)  r.RowIndex.Value < rowIndex)
                If rRow Is Nothing Then
                    shData.InsertAt(row, 0)
                Else
                    shData.InsertAfter(row, rRow)
                End If
            End If

            Dim colIndex As UInt32 = GetColumnIndex(cellAddress)
            Dim cell As Cell       = row.Elements(Of Cell).FirstOrDefault(Function(c) c.CellReference.Value = cellAddress)
            If cell Is Nothing Then
                cell = new Cell() With { .CellReference = new StringValue(cellAddress) }

                'cellは昇順に書き込む必要がある
                Dim rCell As Cell = row.Elements(Of Cell).LastOrDefault(Function(c) GetColumnIndex(c.CellReference) < colIndex)
                if rCell Is Nothing Then
                    row.InsertAt(cell, 0)
                Else
                    row.InsertAfter(cell, rCell)
                End If
            End If
            Return cell

        End Function

        Public Function GetRowIndex(cellName As String) As UInt32
            Dim regex As Regex = New Regex("\d+")
            Dim match As Match = regex.Match(cellName)
            return UInt32.Parse(match.Value)
        End Function

        Public Function GetColumnIndex(cellName As String) As UInt32
            Dim columnName As String = GetColumnName(cellName).ToUpper()
            Const colChars As String = "ABCDEFGHIJKLMNOPQRSTUVWXYZ"
            Dim columnIndex As UInt32 = 0
            Dim i As Integer
            For i = columnName.Length To 0 Step -1
                columnIndex += CType((colChars.IndexOf(columnName.Substring(columnName.Length - i, 1)) + 1) * Math.Pow(26, i - 1), UInteger)
            Next 
            Return columnIndex
        End Function

        Public Function GetColumnName(cellName As String) As String
            Dim match As Match = Regex.Match(cellName, "[A-Za-z]+")
            Return match.Value
        End Function

        Public Function GetWorksheetIndex(bookPart As WorkbookPart, sheetName As String) As Integer
            Dim sheets As Sheets = bookPart.Workbook.GetFirstChild(Of Sheets)
            'シートのエレメントの順番
            Dim sheetIndex As Integer = 0
            For Each sh As Sheet In sheets
                If sh.Name = sheetName Then
                    Exit For
                End If
                sheetIndex += 1
            Next
            Return sheetIndex
        End Function

        Public Function GetWorksheetPart(bookPart As WorkbookPart, index As Integer) As WorksheetPart
            Dim sheets As Sheets = bookPart.Workbook.GetFirstChild(Of Sheets)
            If index >= sheets.Count() Then
                Return Nothing
            End If
            Dim targetRerationshipID = sheets.Descendants(Of Sheet).ElementAt(Index).Id
            Return CType(bookPart.GetPartById(targetRerationshipID), WorksheetPart)

        End Function

        Public Function CreateDefaultStyles() As Stylesheet
            Dim ss As Stylesheet = New Stylesheet()

            Dim fts As Fonts     = New Fonts()

            'ID:0のフォント シートのデフォルトとして使用
            Dim ft As DocumentFormat.OpenXml.Spreadsheet.Font = New DocumentFormat.OpenXml.Spreadsheet.Font()
            Dim ftn As FontName  = New FontName()
            ftn.Val              = StringValue.FromString("Arial")
            Dim ftsz As FontSize = New FontSize()
            ftsz.Val             = DoubleValue.FromDouble(11)
            ft.FontName          = ftn
            ft.FontSize          = ftsz
            fts.Append(ft)

            'ID:1のフォント StyleIndex=1で指定する
            ft                   = New DocumentFormat.OpenXml.Spreadsheet.Font()
            ftn                  = new FontName()
            ftn.Val              = StringValue.FromString("Times New Roman")
            ftsz                 = new FontSize()
            ftsz.Val             = DoubleValue.FromDouble(18)
            ft.FontName          = ftn
            ft.FontSize          = ftsz

            'Boldの指定を追加
            ft.Append(new Bold())
            fts.Append(ft)

            fts.Count = UInt32Value.FromUInt32(CType(fts.ChildElements.Count, UInteger))
            '塗りつぶしパターン 今回未使用だが、タグが必要なため定義
            Dim fills As Fills = New Fills()
            Dim fill  As Fill  = New Fill()
            fills.Append(fill)

            fills.Count = UInt32Value.FromUInt32(CType(fills.ChildElements.Count, UInteger))
            '罫線パターン 今回未使用だが、タグが必要なため定義
            Dim borders As Borders = New Borders()
            Dim border  As Border  = New Border()
            borders.Append(border)
            borders.Count          = UInt32Value.FromUInt32(CType(borders.ChildElements.Count, UInteger))

            Dim csfs As CellStyleFormats = New CellStyleFormats()
            Dim cf   As CellFormat       = New CellFormat()
            cf.FontId                    = 0
            cf.FillId                    = 0
            cf.BorderId                  = 0
            csfs.Append(cf)
            csfs.Count                   = UInt32Value.FromUInt32(CType(csfs.ChildElements.Count, UInteger))

            Dim nfs As DocumentFormat.OpenXml.Spreadsheet.NumberingFormats = New DocumentFormat.OpenXml.Spreadsheet.NumberingFormats()
            Dim cfs As CellFormats                                         = New CellFormats()

            cf = New CellFormat()
            cf.NumberFormatId = 0
            cf.FontId         = 0
            cf.FillId         = 0
            cf.BorderId       = 0
            cf.FormatId       = 0
            cfs.Append(cf)

            'index 1
            cf = New CellFormat()
            cf.FontId   = 1
            cf.FillId   = 0
            cf.BorderId = 0
            cf.FormatId = 0

            '文字の配置位置を指定する
            Dim alignment As Alignment  = New Alignment()
            alignment.Horizontal        = HorizontalAlignmentValues.Right
            alignment.Vertical          = VerticalAlignmentValues.Top

            '自動で折り返す場合
            'alignment.WrapText = true

            'CellFormatに配置情報を追加
            cf.Append(alignment)
            cfs.Append(cf)
            nfs.Count = UInt32Value.FromUInt32(CType(nfs.ChildElements.Count, UInteger))
            cfs.Count = UInt32Value.FromUInt32(CType(cfs.ChildElements.Count, UInteger))

            ss.Append(fts)
            ss.Append(fills)
            ss.Append(borders)
            ss.Append(csfs)
            ss.Append(cfs)

            Dim css As CellStyles = New CellStyles()
            Dim cs  As CellStyle  = New CellStyle()
            cs.Name               = StringValue.FromString("Normal")
            cs.FormatId           = 0
            cs.BuiltinId          = 0
            css.Append(cs)
            css.Count             = UInt32Value.FromUInt32(CType(css.ChildElements.Count, UInteger))
            ss.Append(css)

            Dim dfs As DifferentialFormats = New DifferentialFormats()
            dfs.Count                      = 0
            ss.Append(dfs)

            Dim tss As TableStyles = new TableStyles()
            tss.Count              = 0
            tss.DefaultTableStyle  = StringValue.FromString("TableStyleMedium9")
            tss.DefaultPivotStyle  = StringValue.FromString("PivotStyleLight16")
            ss.Append(tss)

            return ss
        End Function

    End Class

End Namespace

使用例

下記のようにOpenXMLUtility.vbをmodel.vb内で使用すれば、Cellに罫線や値の配置位置が設定できる。

作成したバイナリデータとファイル名はcontrollerからReturn File()に渡せば罫線や値の配置位置が設定されたエクセルファイルがダウンロードできる。
クラス名など名前は投稿用に編集したので、必要に応じて適宜書き換える。

model.vb
Imports System.ComponentModel
Imports System.ComponentModel.DataAnnotations
Imports System.ComponentModel.DataAnnotations.Schema
Imports System.Data.Entity
Imports Oracle.ManagedDataAccess.Client
Imports System.Linq
Imports System.IO

Imports DocumentFormat.OpenXml
Imports DocumentFormat.OpenXml.Packaging
Imports DocumentFormat.OpenXml.Spreadsheet
Imports Color = DocumentFormat.OpenXml.Spreadsheet.Color


Namespace Models

    'ControllerのReturn File()にバイナリデータとファイル名を渡す
    Public Class SpreadSheetData

        Public Property fileContents        As Byte()

        Public Property filename            As String

    End Class


    'エクセルファイルのレイアウトが確定したらここに定数化できるものは書く
    Public Class SpreadsheetsModel

        Public Property LabelsSpreadsheets                       As String = "ファイル名"

        Public Property LabelSpreadsheetSheetName                As String = "シート名"

        Public Property LabelSubTotal                            As String = "小計"


    End Class


    Public Class Model
        ''' <summary>
        ''' エクセル出力用OpenXmlのバイナリデータ作成
        ''' </summary>
        ''' <param name="spreadsheetsDatas "></param>
        ''' <param name="model"></param>
        ''' <returns></returns>
        Public Function createOpenXMLSpreadSheetData(spreadsheetsDatas As List(Of spreadsheetsDatas), model As model) As SpreadSheetData

            Dim SpreadSheetData As SpreadSheetData = New SpreadSheetData()

            Using stream As New MemoryStream()
                Dim spreadsheetsModel   As SpreadsheetsModel          = New SpreadsheetsModel
                Dim OpenXmlUtility      As New OpenXmlUtility
                SpreadSheetData.filename                              = OpenXmlUtility.DefaultFileName()
                '.xlsxファイル作成
                Dim spreadsheetDocument As SpreadsheetDocument = SpreadsheetDocument.Create(stream, SpreadsheetDocumentType.Workbook)

                'workbook.xmlファイル作成 中身は空っぽ
                Dim workbookpart        As WorkbookPart        = spreadsheetDocument.AddWorkbookPart
                'workbook.xmlの要素(中身)作成
                workbookpart.Workbook                          = New Workbook
                'worksheets/sheet.xmlファイル作成用変数宣言
                Dim worksheetPart       As WorksheetPart
                Dim sheetData           As SheetData


                'Excelのスタイル動作確認用
                Dim stylesPart          As WorkbookStylesPart  = workbookpart.AddNewPart(Of WorkbookStylesPart)()
                stylesPart.Stylesheet                          = OpenXmlUtility.CreateDefaultStyles()
                stylesPart.Stylesheet.Save()

                'Sheetsをワークブックに追加
                Dim sheets              As Sheets
                sheets                                         = spreadsheetDocument.WorkbookPart.Workbook.AppendChild(New Sheets())
                Dim sheet  As Sheet 

                'シートのヘッダー部分
                Dim row1   As Row
                Dim cellA1 As Cell
                Dim cellC1 As Cell
                Dim cellE1 As Cell
                Dim cellI1 As Cell
                Dim row2   As Row
                Dim cellA2 As Cell
                Dim row3   As Row
                Dim cellA3 As Cell
                Dim cellB3 As Cell 
                Dim cellC3 As Cell 
                Dim cellD3 As Cell 
                Dim cellE3 As Cell 
                Dim cellF3 As Cell 
                Dim cellG3 As Cell 
                Dim cellH3 As Cell 
                Dim cellI3 As Cell

                '罫線の設定
                Dim border As Dictionary(Of String, Border) = New Dictionary(Of String, Border)
                border.Add("GridThin", New Border() With{ _
                                                    .LeftBorder   = New LeftBorder() With{ _
                                                                        .Style = BorderStyleValues.Thin, _
                                                                        .Color = New Color() With{ _
                                                                                            .Rgb = New HexBinaryValue("00000000")
                                                                                            } _
                                                                        }, _
                                                    .TopBorder    = New TopBorder() With{ _
                                                                        .Style = BorderStyleValues.Thin, _
                                                                        .Color = New Color() With{ _
                                                                                            .Rgb = New HexBinaryValue("00000000")
                                                                                            } _
                                                                        }, _
                                                    .RightBorder  = New RightBorder() With{ _
                                                                        .Style = BorderStyleValues.Thin, _
                                                                        .Color = New Color() With{ _
                                                                                            .Rgb = New HexBinaryValue("00000000")
                                                                                            } _
                                                                        }, _
                                                    .BottomBorder = New BottomBorder() With{ _
                                                                        .Style = BorderStyleValues.Thin, _
                                                                        .Color = New Color() With{ _
                                                                                            .Rgb = New HexBinaryValue("00000000")
                                                                                            } _
                                                                        } _
                                                    } _
                            )
                border.Add("NShapeThin", New Border() With{ _
                                                    .LeftBorder   = New LeftBorder() With{ _
                                                                        .Style = BorderStyleValues.Thin, _
                                                                        .Color = New Color() With{ _
                                                                                            .Rgb = New HexBinaryValue("00000000")
                                                                                            } _
                                                                        }, _
                                                    .TopBorder    = New TopBorder() With{ _
                                                                        .Style = BorderStyleValues.Thin, _
                                                                        .Color = New Color() With{ _
                                                                                            .Rgb = New HexBinaryValue("00000000")
                                                                                            } _
                                                                        }, _
                                                    .RightBorder  = New RightBorder() With{ _
                                                                        .Style = BorderStyleValues.Thin, _
                                                                        .Color = New Color() With{ _
                                                                                            .Rgb = New HexBinaryValue("00000000")
                                                                                            } _
                                                                        } _
                                                    } _
                            )
                border.Add("UShapeThin", New Border() With{ _
                                                    .LeftBorder   = New LeftBorder() With{ _
                                                                        .Style = BorderStyleValues.Thin, _
                                                                        .Color = New Color() With{ _
                                                                                            .Rgb = New HexBinaryValue("00000000")
                                                                                            } _
                                                                        }, _
                                                    .RightBorder  = New RightBorder() With{ _
                                                                        .Style = BorderStyleValues.Thin, _
                                                                        .Color = New Color() With{ _
                                                                                            .Rgb = New HexBinaryValue("00000000")
                                                                                            } _
                                                                        }, _
                                                    .BottomBorder = New BottomBorder() With{ _
                                                                        .Style = BorderStyleValues.Thin, _
                                                                        .Color = New Color() With{ _
                                                                                            .Rgb = New HexBinaryValue("00000000")
                                                                                            } _
                                                                        } _
                                                    } _
                            )
                border.Add("CShapeThin", New Border() With{ _
                                                    .LeftBorder   = New LeftBorder() With{ _
                                                                        .Style = BorderStyleValues.Thin, _
                                                                        .Color = New Color() With{ _
                                                                                            .Rgb = New HexBinaryValue("00000000")
                                                                                            } _
                                                                        }, _
                                                    .TopBorder    = New TopBorder() With{ _
                                                                        .Style = BorderStyleValues.Thin, _
                                                                        .Color = New Color() With{ _
                                                                                            .Rgb = New HexBinaryValue("00000000")
                                                                                            } _
                                                                        }, _
                                                    .BottomBorder = New BottomBorder() With{ _
                                                                        .Style = BorderStyleValues.Thin, _
                                                                        .Color = New Color() With{ _
                                                                                            .Rgb = New HexBinaryValue("00000000")
                                                                                            } _
                                                                        } _
                                                    } _
                            )
                border.Add("BackwordsCShapeThin", New Border() With{ _
                                                    .TopBorder    = New TopBorder() With{ _
                                                                        .Style = BorderStyleValues.Thin, _
                                                                        .Color = New Color() With{ _
                                                                                            .Rgb = New HexBinaryValue("00000000")
                                                                                            } _
                                                                        }, _
                                                    .RightBorder  = New RightBorder() With{ _
                                                                        .Style = BorderStyleValues.Thin, _
                                                                        .Color = New Color() With{ _
                                                                                            .Rgb = New HexBinaryValue("00000000")
                                                                                            } _
                                                                        }, _
                                                    .BottomBorder = New BottomBorder() With{ _
                                                                        .Style = BorderStyleValues.Thin, _
                                                                        .Color = New Color() With{ _
                                                                                            .Rgb = New HexBinaryValue("00000000")
                                                                                            } _
                                                                        } _
                                                    } _
                            )
                border.Add("HorizonalParallelThin", New Border() With{ _
                                                    .TopBorder   = New TopBorder() With{ _
                                                                        .Style = BorderStyleValues.Thin, _
                                                                        .Color = New Color() With{ _
                                                                                            .Rgb = New HexBinaryValue("00000000")
                                                                                            } _
                                                                        }, _
                                                    .BottomBorder  = New BottomBorder() With{ _
                                                                        .Style = BorderStyleValues.Thin, _
                                                                        .Color = New Color() With{ _
                                                                                            .Rgb = New HexBinaryValue("00000000")
                                                                                            } _
                                                                        } _
                                                    } _
                            )

                Dim row        As Row
                Dim cellIdx    As Cell
                Dim rowIdx     As UInt32 = 4  'データ表示領域の行番号
                Dim sheetId    As UInt32 = 1  'シートを複数作成するときはsheetIdを加算しながら作成する
                Dim cellRef    As String
                Dim mergeCells As MergeCells = New MergeCells

                If SpreadSheetData.filename = "dummy.xlsx" Then
                        SpreadSheetData.filename = String.Concat(spreadsheetsModel.LabelsSpreadsheets ".xlsx")
                End If

                'worksheets/sheet.xmlファイル作成 中身は空っぽ
                worksheetPart           = workbookpart.AddNewPart(Of WorksheetPart)()

                'sheet.xmlにworksheet要素作成
                sheetData               = New SheetData()
                worksheetPart.Worksheet = New Worksheet()

                '列の幅設定
                '必要な列の数分幅を設定し、columnsオブジェクトにcolumnオブジェクトの幅情報をAppendし、
                '最後にcolumnsオブジェクトをworksheetオブジェクトにAppendすれば列の幅が設定できる。
                Dim columns  As Columns = New Columns()
                Dim column1  As Column  = New Column()
                column1.Min         = 1
                column1.Max         = 1
                column1.Width       = 9
                column1.CustomWidth = True

                columns.Append(column1)

                Dim column2  As Column  = New Column()
                column2.Min         = 2
                column2.Max         = 2
                column2.Width       = 11
                column2.CustomWidth = True

                columns.Append(column2)

                Dim column3  As Column  = New Column()
                column3.Min         = 3
                column3.Max         = 3
                column3.Width       = 21
                column3.CustomWidth = True

                columns.Append(column3)

                ~~~略~~~

                worksheetPart.Worksheet.Append(columns)

                worksheetPart.Worksheet.Append(sheetData)

                'ワークシートをワークブックに追加
                'sheets要素内にsheet要素が作成させる
                'シートが複数必要な帳票の場合は、「worksheetPart = workbookpart.AddNewPart(Of WorksheetPart)()」辺りから
                '各シートのヘッダー部分のデータ作成処理を含めて「sheets.Append(sheet)」すれば複数シートのエクセルファイルが作成される。
                sheet                = New Sheet
                sheet.Id             = spreadsheetDocument.WorkbookPart.GetIdOfPart(worksheetPart)
                sheet.SheetId        = sheetId
                sheet.Name           = String.Concat(spreadsheetsModel.LabelSpreadsheetSheetName , DateTime.Now.ToString("yyyyMMddHHmmss"))
                sheets.Append(sheet)

                'シートの列の幅のデフォルト設定
                worksheetPart.Worksheet.SheetFormatProperties                    = New SheetFormatProperties()
                worksheetPart.Worksheet.SheetFormatProperties.DefaultRowHeight   = 0
                worksheetPart.Worksheet.SheetFormatProperties.DefaultColumnWidth = 5

                'Rowオブジェクトにcellオブジェクトを1セルずつAppendし、1行の最後で
                'sheetDataオブジェクトにRowオブジェクトをAppendすれば1行追加できる。
                row1                 = New Row
                cellA1               = New Cell
                cellA1.DataType      = CellValues.String
                cellA1.CellReference = "A1" 
                cellA1.CellValue     = New CellValue("title")
                row1.Append(cellA1)

                cellC1               = New Cell
                cellC1.DataType      = CellValues.String
                cellC1.CellReference = "C1" 
                cellC1.CellValue     = New CellValue("yyyymmdd")
                row1.Append(cellC1)

                sheetData.Append(row1)

                '2行目以降のデータ追加は1行目と同様、Rowオブジェクトにcellオブジェクトを1セルずつAppendし、
                '1行の最後でsheetDataオブジェクトにRowオブジェクトをAppendする。
                '途中の行を飛ばすとエクセルが開けなかったので、空行は空行のオブジェクトをAppendする
                row2                 = New Row
                cellA2               = New Cell
                cellA2.DataType      = CellValues.String
                cellA2.CellReference = "A2" 
                cellA2.CellValue     = New CellValue("")

                row2.Append(cellA2)

                sheetData.Append(row2)

                row3                 = New Row
                cellA3               = New Cell
                cellA3.DataType      = CellValues.String
                cellA3.CellReference = "A3" 
                cellA3.CellValue     = New CellValue(LABEL_TITLEA3)
                row3.Append(cellA3)

                cellB3               = New Cell
                cellB3.DataType      = CellValues.String
                cellB3.CellReference = "B3" 
                cellB3.CellValue     = New CellValue(LABEL_TITLEB3)
                row3.Append(cellB3)

                cellC3               = New Cell
                cellC3.DataType      = CellValues.String
                cellC3.CellReference = "C3" 
                cellC3.CellValue     = New CellValue(LABEL_TITLEC3)
                row3.Append(cellC3)

                ~~~略~~~

                sheetData.Append(row3)

                '罫線および値の配置位置の設定はどこでしてももよさそう
                '罫線の設定
                OpenXmlUtility.AppendCellBorder(worksheetPart, cellA3, "A3", border("GridThin"))
                OpenXmlUtility.AppendCellBorder(worksheetPart, cellB3, "B3", border("GridThin"))
                OpenXmlUtility.AppendCellBorder(worksheetPart, cellC3, "C3", border("GridThin"))
                OpenXmlUtility.AppendCellBorder(worksheetPart, cellD3, "D3", border("GridThin"))
                OpenXmlUtility.AppendCellBorder(worksheetPart, cellE3, "E3", border("GridThin"))
                OpenXmlUtility.AppendCellBorder(worksheetPart, cellF3, "F3", border("GridThin"))
                OpenXmlUtility.AppendCellBorder(worksheetPart, cellG3, "G3", border("GridThin"))
                OpenXmlUtility.AppendCellBorder(worksheetPart, cellH3, "H3", border("GridThin"))
                OpenXmlUtility.AppendCellBorder(worksheetPart, cellI3, "I3", border("GridThin"))

                '値の配置位置
                OpenXmlUtility.SetCellAlignment(workbookpart, cellA3, HorizontalAlignmentValues.Center, VerticalAlignmentValues.Center)
                OpenXmlUtility.SetCellAlignment(workbookpart, cellB3, HorizontalAlignmentValues.Center, VerticalAlignmentValues.Center)
                OpenXmlUtility.SetCellAlignment(workbookpart, cellC3, HorizontalAlignmentValues.Center, VerticalAlignmentValues.Center)
                OpenXmlUtility.SetCellAlignment(workbookpart, cellD3, HorizontalAlignmentValues.Center, VerticalAlignmentValues.Center)
                OpenXmlUtility.SetCellAlignment(workbookpart, cellE3, HorizontalAlignmentValues.Center, VerticalAlignmentValues.Center)
                OpenXmlUtility.SetCellAlignment(workbookpart, cellF3, HorizontalAlignmentValues.Center, VerticalAlignmentValues.Center)
                OpenXmlUtility.SetCellAlignment(workbookpart, cellG3, HorizontalAlignmentValues.Center, VerticalAlignmentValues.Center)
                OpenXmlUtility.SetCellAlignment(workbookpart, cellH3, HorizontalAlignmentValues.Center, VerticalAlignmentValues.Center)
                OpenXmlUtility.SetCellAlignment(workbookpart, cellI3, HorizontalAlignmentValues.Center, VerticalAlignmentValues.Center)

                For Each spreadsheetsData In spreadsheetsDatas
                    'データベースより取得した数値をエクセルシートの各セルに入力
                    row                   = New Row

                    cellRef               = "A" & rowIdx.ToString("D")
                    cellIdx               = New Cell
                    cellIdx.DataType      = CellValues.String
                    cellIdx.CellReference = cellRef    
                    cellIdx.CellValue     = New CellValue(spreadsheetsData.VALUE_A)
                    OpenXmlUtility.AppendCellBorder(worksheetPart, cellIdx, cellRef , border("GridThin"))
                    row.Append(cellIdx)

                    cellRef               = "B" & rowIdx.ToString("D")
                    cellIdx               = New Cell
                    cellIdx.DataType      = CellValues.String
                    cellIdx.CellReference = cellRef    
                    cellIdx.CellValue     = New CellValue(spreadsheetsData.VALUE_B)
                    OpenXmlUtility.AppendCellBorder(worksheetPart, cellIdx, cellRef , border("GridThin"))
                    row.Append(cellIdx)

                    ~~~略~~~

                    '計算式はCellオブジェクトのCellFormulaに計算式を書けば設定できる
                    cellRef               = "F" & rowIdx.ToString("D")
                    cellIdx               = New Cell
                    cellIdx.CellReference = cellRef    
                    cellIdx.CellFormula   = New CellFormula("=[何か計算式]"))
                    OpenXmlUtility.AppendCellBorder(worksheetPart, cellIdx, cellRef , border("GridThin"))
                    row.Append(cellIdx)

                    ~~~略~~~

                    sheetData.Append(row)

                    rowIdx += 1
                Next

                'フッタ部分
                cellRef                 = "A" & rowIdx.ToString("D")
                row                     = New Row
                cellIdx                 = New Cell
                cellIdx.DataType        = CellValues.String
                cellIdx.CellReference   = cellRef    
                cellIdx.CellValue       = New CellValue(LABEL_FOOTER_A)
                OpenXmlUtility.SetCellAlignment(workbookpart, cellIdx, HorizontalAlignmentValues.Center, VerticalAlignmentValues.Center)
                OpenXmlUtility.AppendCellBorder(worksheetPart, cellIdx, cellRef   , border("CShapeThin"))
                row.Append(cellIdx)

                cellRef               = "B" & rowIdx.ToString("D")
                cellIdx               = New Cell
                cellIdx.CellReference = cellRef
                '下記のようにExcelの関数も入力できる
                cellIdx.CellFormula   = New CellFormula("SUM(B4:B" & rowIdx.ToString("D") - 1 & ")")
                OpenXmlUtility.AppendCellBorder(worksheetPart, cellIdx, cellRef   , border("HorizonalParallelThin"))
                row.Append(cellIdx)

                ~~~略~~~

                sheetData.Append(row)

                '次の行
                cellRef                 = "A" & rowIdx.ToString("D")
                row                     = New Row
                cellIdx                 = New Cell
                cellIdx.DataType        = CellValues.String
                cellIdx.CellReference   = cellRef    
                cellIdx.CellValue       = New CellValue("[何か値]")
                OpenXmlUtility.SetCellAlignment(workbookpart, cellIdx, HorizontalAlignmentValues.Center, VerticalAlignmentValues.Center)
                OpenXmlUtility.AppendCellBorder(worksheetPart, cellIdx, cellRef   , border("GridThin"))
                row.Append(cellIdx)

                'Cellのマージは下記のように書けばできる
                Dim mergeCell6 As MergeCell  = New MergeCell

                mergeCell6.Reference         = new StringValue("A"& rowIdx.ToString("D") & ":B"& rowIdx.ToString("D"))
                mergeCells.Append(mergeCell6)

                'セルを結合した場合、罫線は別途Cellに対して設定する必要がある
                cellRef                 = "B" & rowIdx.ToString("D")
                cellIdx                 = New Cell
                cellIdx.CellReference   = cellRef    
                OpenXmlUtility.AppendCellBorder(worksheetPart, cellIdx, cellRef   , border("GridThin"))
                row.Append(cellIdx)


                ~~~略~~~

                sheetData.Append(row)

                rowIdx += 1

                cellRef                 = "A" & rowIdx.ToString("D")
                row                     = New Row
                cellIdx                 = New Cell
                cellIdx.DataType        = CellValues.String
                cellIdx.CellReference   = cellRef    
                cellIdx.CellValue       = New CellValue(spreadsheetsModel.LabelSubTotal)
                OpenXmlUtility.SetCellAlignment(workbookpart, cellIdx, HorizontalAlignmentValues.Center, VerticalAlignmentValues.Center)
                OpenXmlUtility.AppendCellBorder(worksheetPart, cellIdx, cellRef   , border("GridThin"))
                row.Append(cellIdx)

                Dim mergeCell7 As MergeCell  = New MergeCell

                mergeCell7.Reference         = new StringValue("A"& rowIdx.ToString("D") & ":B"& rowIdx.ToString("D"))
                mergeCells.Append(mergeCell7)

                'セルの結合情報のAppendは、workbookをsaveする直前に1回実行すればExcelに反映されるので最後に1回実行すればよい。
                worksheetPart.Worksheet.Append(mergeCells)

                cellRef                 = "B" & rowIdx.ToString("D")
                cellIdx                 = New Cell
                cellIdx.CellReference   = cellRef    
                OpenXmlUtility.AppendCellBorder(worksheetPart, cellIdx, cellRef   , border("GridThin"))
                row.Append(cellIdx)

                cellRef                 = "C" & rowIdx.ToString("D")
                cellIdx                 = New Cell
                cellIdx.CellReference   = cellRef    
                cellIdx.CellFormula     = New CellFormula("=[合計の計算式]")
                OpenXmlUtility.SetCellAlignment(workbookpart, cellIdx, HorizontalAlignmentValues.Center, VerticalAlignmentValues.Center)
                OpenXmlUtility.AppendCellBorder(worksheetPart, cellIdx, cellRef   , border("GridThin"))
                row.Append(cellIdx)

                ~~~略~~~

                sheetData.Append(row)

                workbookpart.Workbook.Save()

                spreadsheetDocument.Close()

                SpreadSheetData.fileContents = stream.ToArray()

            End Using

            Return SpreadSheetData

        End Function

    End Class


End Namespace

参考サイト

Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
1
Help us understand the problem. What are the problem?