はじめに
集計表などの帳票出力機能で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