Complete Open XML Spreadsheet in VB
Class that allows you to programmatically create an Open XML Excel spreadsheet
Introduction
In writing web based programs, there has always been a high demand for reports. I find it very helpful to create the output of a report as a spreadsheet. A spreadsheet allows you to format the report in a readymade grid and allows the user to manipulate the output to suit their personal needs, including formulas and copy and pasting that would not be available in a PDF file. The obstacle with using spreadsheets before the XML format came about is that the web server has to act like a work station and open an instance of Excel.exe. This lead to using the Disk IO on the server. The Disk should be isolated from the Internet server functions as much as possible. There are several more reasons why running an executable on a server from the Internet Services thread is bad. OpenXML fixed that by creating the Excel file in the memory stream as a simple set of XML files.
Background
The Open XML standards call for a series of XML files in a fixed hierarchy that are complex and intricate. The Open XML SDK have greatly decreased the complication and allows the program to create spreadsheets without even having a copy of Microsoft Office installed on the server. For this project, I use Open XML SDK 2.5 and Visual Studio 2012. I used Visual Basic to keep things simple but this project will easily translate to C#. However, if you compile this as a library, you can use it from any other VS language.
Using the Code
I created a class, openXMLExcelCreator that can be called to do all the heavy lifting. In the sample, I included how to call this class. The class is however the most interesting and I wanted to share what I learned in creating this class. The openXML object classes behave slightly different than normal VS objects in that scope is not restricted to the VS container but is restricted in the XML container. For example, a reference to an object's parent may allow the object to be referenced again. Also, children of the base class Workbook are called "Parts".
I will list the steps to create a spreadsheet, although there are a few more steps for additional options such a "themes". Here is a list of the overall steps:
- Create a Memory Stream to hold the files and attach the workbook base.
- Create the
Workbook Part. - Create the
sheetsobject and append asheetobject for each spreadsheet you are creating. - Create
Stylesobject and append astyleobject for each combination of style. Astyleobject designates the number format, font, cell fill, borders. - Add the data to the
sheetobject ascellobjects and if necessary, associate thestyleobject with the cell. - Append the
Sheets,Stylesand File Info to the workbook. - Save the workbook and close it so the memory stream will transmit the finished parts.
The class has some helper classes that hold the various styles that make it easier to call this class so the programmer will only have to initialize the class, add the spreadsheets, add cell formatting, command the class to assemble the spreadsheet and add the output to the response memory stream. The part I want to discuss is how the styles are made. It is easiest to create a default style for each of the style parts. The Parts are font, fill, border and number format, although alignment can be a part of the style. The Styles Part contains children objects that are a collection of style objects. The names make them obvious as to what they contain. Therefore the objects Fonts contain font styles, Fills contain cell fills, Borders, and NumberingFormats contain what their name implies. In my code below, I create the parents Fills, Fonts, Borders, etc. and fill them with an ordered list of children. You have to keep track of the childrens' index so that when the parent CellFormats is filled with its children, you can point to the style indexes. All the parents are then added the Styles Part which becomes Style.xml file. The code below also performs one more job. It goes through each cell in the sheet and tracks which style index combination it will need. This can be observed in the example where cell A1 is size 14 and is blue, if any other cell is also size 14 and blue, there is no need to create a second CellStyle object since both cells can use the same one. However if A1 is blue and size 14 and another cell, let's say D12 is blue and size 14 but also has a red background, then you will need 2 separate cell styles. The subs addStyles() and plotStyles() will do this while also keeping track of the style indexes.
Private Sub addStyles()
Dim thisWorkbookStyles As WorkbookStylesPart = thisWorkbookpart.AddNewPart(Of WorkbookStylesPart)("sheetStyle1")
Dim thisStyleSheet As New Stylesheet
'Font Area
Dim fontFormatCount As Integer = 1 'fontcount starts with default font, fill and border so it is 1
Dim fillFormatCount As Integer = 1
Dim numberFormatCount As Integer = 0
Dim borderFormatCount As Integer = 1
Dim alignmentFormatCount As Integer = 0
'default font
Dim defaultFontStyle As New MyFontStyle
defaultFontStyle.fontColor = System.Drawing.Color.Black
defaultFontStyle.fontName = "Calibri"
defaultFontStyle.fontSize = 11D
defaultFontStyle.formatIndex = 0
defaultFontStyle.isBold = False
listFonts.Add(defaultFontStyle)
Dim allFonts As New Fonts
'always add default font first, index will be 0, subsequent indexes will be 1 or greater
allFonts.Count = CType(1, UInt32Value)
Dim defaultFont As Font = defaultFontStyle.FontObj
Dim defaultFontScheme As New FontScheme
defaultFontScheme.Val = FontSchemeValues.Minor
defaultFont.Append(defaultFontScheme)
allFonts.Append(defaultFont)
'fills and add default
Dim thisFills As New Fills
thisFills.Count = CType(fillFormatCount, UInt32Value)
Dim defaultFill As New MyFillStyle
defaultFill.Pattern = PatternValues.None
thisFills.Append(defaultFill.FillObj)
fillFormatCount += 1
defaultFill = New MyFillStyle
defaultFill.Pattern = PatternValues.Gray125
thisFills.Append(defaultFill.FillObj)
Dim thisNumberFormats As NumberingFormats = Nothing
'borders and add default border
Dim thisBorders As New Borders
thisBorders.Count = CType(borderFormatCount, UInt32Value)
Dim defaultBorder = New MyborderStyle
thisBorders.Append(defaultBorder.BorderObj)
Dim listAllAlignments As New List(Of Alignment)
For i As Integer = 0 To allSheets.Count - 1
Dim thisOneSheet = allSheets(i)
If thisOneSheet.allFontStyles.Count > 0 Then
For n As Integer = 0 To thisOneSheet.allFontStyles.Count - 1
fontFormatCount += 1
thisOneSheet.allFontStyles(n).formatIndex = fontFormatCount - 1
Dim thisFontStyle As MyFontStyle = thisOneSheet.allFontStyles(n)
listFonts.Add(thisFontStyle)
plotStyles(thisFontStyle, i)
allFonts.Count = CType(fontFormatCount, UInt32Value)
allFonts.Append(thisFontStyle.FontObj)
Next
End If
If thisOneSheet.allFillStyles.Count > 0 Then
For n As Integer = 0 To thisOneSheet.allFillStyles.Count - 1
fillFormatCount += 1
thisOneSheet.allFillStyles(n).formatIndex = fillFormatCount - 1
Dim thisFillStyle As MyFillStyle = thisOneSheet.allFillStyles(n)
plotStyles(thisFillStyle, i)
thisFills.Count = CType(fillFormatCount, UInt32Value)
thisFills.Append(thisFillStyle.FillObj)
Next
End If
If thisOneSheet.allNumberStyles.Count > 0 Then
For n As Integer = 0 To thisOneSheet.allNumberStyles.Count - 1
Dim thisNumberStyle As MyNumberStyle = thisOneSheet.allNumberStyles(n)
If (thisOneSheet.allNumberStyles(n).styleId >= 165) Then
numberFormatCount += 1
thisOneSheet.allNumberStyles(n).FormatIndex = 165 + numberFormatCount - 1
If thisNumberFormats Is Nothing Then thisNumberFormats = New NumberingFormats()
thisNumberFormats.Count = CType(numberFormatCount, UInt32Value)
Dim thisOneNumberFormat As New NumberingFormat
thisOneNumberFormat.NumberFormatId = CType(165 + numberFormatCount - 1, UInt32Value)
thisOneNumberFormat.FormatCode = thisNumberStyle.customStyle
thisNumberFormats.Append(thisOneNumberFormat)
Else
thisOneSheet.allNumberStyles(n).FormatIndex = thisOneSheet.allNumberStyles(n).styleId
End If
plotStyles(thisNumberStyle, i)
Next
End If
If thisOneSheet.allBorderStyles.Count > 0 Then
For n As Integer = 0 To thisOneSheet.allBorderStyles.Count - 1
borderFormatCount += 1
thisOneSheet.allBorderStyles(n).formatIndex = borderFormatCount - 1
Dim thisBorderStyle As MyborderStyle = thisOneSheet.allBorderStyles(n)
plotStyles(thisBorderStyle, i)
thisBorders.Count = CType(borderFormatCount, UInt32Value)
thisBorders.Append(thisBorderStyle.BorderObj)
Next
End If
If thisOneSheet.AlignmentStyleCount > 0 Then
For n As Integer = 0 To thisOneSheet.AlignmentStyleCount - 1
alignmentFormatCount += 1
thisOneSheet.setAlignmentStyleFormatIndex(n, alignmentFormatCount - 1)
Dim thisAligmentStyle As myAlignmentStyle = thisOneSheet.getAlignmentStyle(n)
plotStyles(thisAligmentStyle, i)
listAllAlignments.Add(thisAligmentStyle.AlignmentObj)
Next
End If
Next
Dim thisCellStyleFormats As New CellStyleFormats
thisCellStyleFormats.Count = CType(1, UInt32Value)
Dim thisCellFormat As New CellFormat
With thisCellFormat
.NumberFormatId = CType(0, UInt32Value)
.FontId = CType(0, UInt32Value)
.FillId = CType(0, UInt32Value)
.BorderId = CType(0, UInt32Value)
End With
thisCellStyleFormats.Append(thisCellFormat)
Dim thisCellFormats As New CellFormats
thisCellFormats.Count = CType(1, UInt32Value)
thisCellFormat = New CellFormat
With thisCellFormat
.NumberFormatId = CType(0, UInt32Value)
.FontId = CType(0, UInt32Value)
.FillId = CType(0, UInt32Value)
.BorderId = CType(0, UInt32Value)
.FormatId = CType(0, UInt32Value)
End With
thisCellFormats.Append(thisCellFormat)
If Not stylePlot Is Nothing AndAlso stylePlot.Count > 0 Then
'consolidate stylecombos and update styleplot with indexes
Dim stylecomboCount As Integer = 1 'the 1 is for the default cellFormat is always first so the first index is 1
Dim consolStyles As New Dictionary(Of Long, Integer)
Dim orderedConsolStyles As New List(Of StyleCombo)
Dim keys As New List(Of PlotLocation)
For Each dp As PlotLocation In stylePlot.Keys
keys.Add(dp)
Next
For i As Integer = 0 To keys.Count - 1
Dim thisStyleCombo As StyleCombo = stylePlot(keys(i))
'Dim consolVal As Integer
If consolStyles.ContainsKey(thisStyleCombo.styleCode) Then
thisStyleCombo.styleIndex = consolStyles(thisStyleCombo.styleCode)
Else
thisStyleCombo.styleIndex = stylecomboCount
orderedConsolStyles.Add(thisStyleCombo)
consolStyles.Add(thisStyleCombo.styleCode, stylecomboCount)
stylecomboCount += 1
End If
stylePlot(keys(i)) = thisStyleCombo
Next
thisCellFormats.Count = orderedConsolStyles.Count + 1 ' the + 1 is for the default cellFormat is always first
For Each thisStylecombo As StyleCombo In orderedConsolStyles
thisCellFormat = New CellFormat
With thisCellFormat
.NumberFormatId = thisStylecombo.numberFormatIndex
.FontId = thisStylecombo.fontFormatIndex
.FillId = thisStylecombo.fillFormatIndex
.BorderId = thisStylecombo.borderFormatIndex
.FormatId = CType(0, UInt32Value)
.ApplyNumberFormat = (thisStylecombo.numberFormatIndex > 0)
.ApplyBorder = (thisStylecombo.borderFormatIndex > 0)
.ApplyFont = (thisStylecombo.fontFormatIndex > 0)
.ApplyFill = (thisStylecombo.fillFormatIndex > 0)
.ApplyAlignment = (thisStylecombo.alignmentFormatIndex > -1)
If (thisStylecombo.alignmentFormatIndex > -1) Then
Dim thisAlignment As Alignment = listAllAlignments(thisStylecombo.alignmentFormatIndex)
Dim newAlignment As New Alignment
newAlignment.Horizontal = thisAlignment.Horizontal
newAlignment.WrapText = thisAlignment.WrapText
.Append(newAlignment)
End If
End With
thisCellFormats.Append(thisCellFormat)
Next
End If
Dim thisCellStyles As New CellStyles
thisCellStyles.Count = CType(1, UInt32Value)
Dim thisCellStyle As New CellStyle
With thisCellStyle
.Name = "Normal"
.FormatId = CType(0, UInt32Value)
.BuiltinId = CType(0, UInt32Value)
End With
thisCellStyles.Append(thisCellStyle)
Dim thisDifferentialFormats As New DifferentialFormats
thisDifferentialFormats.Count = CType(0, UInt32Value)
Dim thisTableStyles As New TableStyles
With thisTableStyles
.Count = CType(0, UInt32Value)
.DefaultTableStyle = "TableStyleMedium9"
.DefaultPivotStyle = "PivotStyleLight16"
End With
If numberFormatCount > 0 Then thisStyleSheet.Append(thisNumberFormats)
thisStyleSheet.Append(allFonts) 'does not need if since default font is always needed
If fillFormatCount > 0 Then thisStyleSheet.Append(thisFills)
If borderFormatCount > 0 Then thisStyleSheet.Append(thisBorders)
thisStyleSheet.Append(thisCellStyleFormats)
thisStyleSheet.Append(thisCellFormats)
thisStyleSheet.Append(thisCellStyles)
thisStyleSheet.Append(thisDifferentialFormats)
thisStyleSheet.Append(thisTableStyles)
thisWorkbookStyles.Stylesheet = thisStyleSheet
End Sub
Private Sub plotStyles(styleObj As Object, worksheetId As Integer)
Dim myArea As System.Drawing.Rectangle
Dim styleIndex As Integer
Dim whichStyle As Integer
Dim styleCode As Long
If TypeOf styleObj Is MyFontStyle Then
Dim thisObj As MyFontStyle = CType(styleObj, MyFontStyle)
myArea = thisObj.location
styleIndex = thisObj.formatIndex
styleCode = thisObj.stylecode
whichStyle = 1
End If
If TypeOf styleObj Is MyFillStyle Then
Dim thisObj As MyFillStyle = CType(styleObj, MyFillStyle)
myArea = thisObj.location
styleIndex = thisObj.formatIndex
styleCode = thisObj.stylecode
whichStyle = 2
End If
If TypeOf styleObj Is MyNumberStyle Then
Dim thisObj As MyNumberStyle = CType(styleObj, MyNumberStyle)
myArea = thisObj.location
styleIndex = thisObj.FormatIndex
styleCode = thisObj.stylecode
whichStyle = 3
End If
If TypeOf styleObj Is MyborderStyle Then
Dim thisObj As MyborderStyle = CType(styleObj, MyborderStyle)
myArea = thisObj.location
styleIndex = thisObj.formatIndex
styleCode = thisObj.stylecode
whichStyle = 4
End If
If TypeOf styleObj Is myAlignmentStyle Then
Dim thisObj As myAlignmentStyle = CType(styleObj, myAlignmentStyle)
myArea = thisObj.location
styleIndex = thisObj.formatIndex
styleCode = thisObj.stylecode
whichStyle = 5
End If
If stylePlot Is Nothing Then stylePlot = New Dictionary_
(Of PlotLocation, StyleCombo)(New PlotLocationEqualityComparer())
For i As Integer = myArea.Y To (myArea.Y + myArea.Height - 1)
For n As Integer = myArea.X To (myArea.X + myArea.Width - 1)
Dim thisPoint = New PlotLocation(n, i, worksheetId)
Dim thisStyleCombo As StyleCombo
If stylePlot.ContainsKey(thisPoint) Then
thisStyleCombo = stylePlot(thisPoint)
Else
thisStyleCombo = New StyleCombo
End If
thisStyleCombo.styleCode += styleCode
Select Case whichStyle
Case 1
thisStyleCombo.fontFormatIndex = styleIndex
Case 2
thisStyleCombo.fillFormatIndex = styleIndex
Case 3
thisStyleCombo.numberFormatIndex = styleIndex
Case 4
thisStyleCombo.borderFormatIndex = styleIndex
Case 5
thisStyleCombo.alignmentFormatIndex = styleIndex
End Select
stylePlot(thisPoint) = thisStyleCombo
Next
Next
End Sub
Valling the class can open up some insights as well. The example below shows off what I learned in creating this class. First, for input of the data, I decided to use a List collection of String arrays. This way, you can add lines as you need and not worry about how many columns you are using in one row versus another. To designate a formula, just like in Excel, simply add the "=" character as the first character in a cell. I did run into trouble when I was placing the output of a database query into a spreadsheet when a use used the equals character because it was pretty when starting the name of an important customer so now I filter all database query with a routing that removes the equal sign from the beginning of text. My pattern for calling the class is to add the data to the worksheets, format, assemble and add output to the Response.
Private Sub doStuff()
'Create a list collection to hold the data to be displayed
Dim SpreadsheetGrid As New List(Of String())
Dim oneLine As String = ""
oneLine = "Cell A1|25.00|4|=B1*C1"
SpreadsheetGrid.Add(oneLine.Split("|"c))
oneLine = "Something Here"
SpreadsheetGrid.Add(oneLine.Split("|"c))
'Create the spreadsheet object
Dim rpt As New openXMLExcelCreator()
'add a worksheet to the workbook this is worksheet index 0
rpt.addWorksheet("This is Sheet 1", New System.Drawing.Point(0, 0), SpreadsheetGrid, True)
'add a second worksheet (index 1) but this time we will not do auto fit and designate custom column widths
rpt.addWorksheet("this is sheet 2", New System.Drawing.Point(0, 0), SpreadsheetGrid)
'format the text of the first sheet, we are making the first row cells A1 and B1 to be 14 point fonts
rpt.addFontStyle(0, 0, 0, 2, 1, "Times New Roman", 14, System.Drawing.Color.Aqua, True)
'merge cell A2 with B2
rpt.addMergeCell(0, 0, 1, 1, 2)
'make cell D1 into finance format (#,###.00)
rpt.addNumberFormatStyle(0, 3, 0, 1, 1, openXMLExcelCreator.MyNumberStyle.numberStyles.financeComma)
'make cell B1 on sheet 2 have a custom format
rpt.addNumberFormatStyle(1, 1, 0, 1, 1, openXMLExcelCreator.MyNumberStyle.numberStyles.custom, "RR 0.00")
'center cell A2
rpt.addAlignmentStyles(0, 0, 1, 1, 1, False, openXMLExcelCreator.HAlignment.center)
'paint the background of cell A2 Yellow
rpt.addCellFillStyle(0, 0, 1, 1, 1, Drawing.Color.Yellow)
'put a border around cell A1 on sheet 2
Dim styleIndex As Integer = rpt.CreateBorderStyles(1, 0, 0, 1, 1)
rpt.setBorder(1, styleIndex, openXMLExcelCreator.BorderSides.Right, _
openXMLExcelCreator.BorderLineStyles.DoubleLine, Drawing.Color.Black)
rpt.setBorder(1, styleIndex, openXMLExcelCreator.BorderSides.Bottom, _
openXMLExcelCreator.BorderLineStyles.Medium, Drawing.Color.Orange)
'set custom column widths
rpt.addColWidth(1, 0, 1, 25D)
'assemble the spreadsheet
Dim allBytes As Byte() = rpt.assemble()
'make the file available for download
Response.BufferOutput = True
Response.ContentType = "application/vnd.openxml.formats-officedocument.spreadsheetml.sheet"
Response.AddHeader("Content-Disposition", "attachment; filename=testfile.xlsx")
Response.OutputStream.Write(allBytes, 0, allBytes.Length)
Response.End()
End Sub
Points of Interest
I hope you enjoy your journey through Open XML as much as I did. This is my first article, so I look forward to feedback from the audience. Thank you!
History
- 4/24/2014: Initial version