Complete Open XML Spreadsheet in VB

• Download demo (no EXE) - 31 KB
• Download demo - 70.9 KB

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 sheets object and append a sheet object for each spreadsheet you are creating.
• Create Styles object and append a style object for each combination of style. A style object designates the number format, font, cell fill, borders.
• Add the data to the sheet object as cell objects and if necessary, associate the style object with the cell.
• Append the Sheets, Styles and 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