Mvc controller output to excel

ExcelController.vb

Imports System.Web.Mvc
Imports DocumentFormat.OpenXml.Packaging
Imports DocumentFormat.OpenXml
Imports DocumentFormat.OpenXml.Spreadsheet
Imports System.IO
Imports d = Cpp.BudgetPreparation.Data

Namespace Areas.Downloads

    Public Class ExcelController
        Inherits Controller

        ' GET: Downloads/Excel
        Function Index() As FileResult

            Dim ms As New MemoryStream()

            Dim xl As SpreadsheetDocument = SpreadsheetDocument.Create(ms, SpreadsheetDocumentType.Workbook)

            Dim wbp As WorkbookPart = xl.AddWorkbookPart()
            Dim wsp As WorksheetPart = wbp.AddNewPart(Of WorksheetPart)()

            Dim wb As New Workbook()

            Dim fv As New FileVersion()

            fv.ApplicationName = "Excel"

            Dim ws As New Worksheet()

            Dim sd As New SheetData()

            Dim skippy As New UInt32Value()
            skippy.Value = 1UI

            Dim r1 As New Row() With {.RowIndex = skippy}

            Dim c1 As New Cell()
            c1.DataType = CellValues.String
            c1.CellValue = New CellValue("first")
            r1.Append(c1)

            Dim c2 As New Cell()
            c2.CellReference = "C1"
            c2.DataType = CellValues.String
            c2.CellValue = New CellValue("second")

            r1.Append(c2)
            sd.Append(r1)

            'Dim r2 As New Row() With {.RowIndex = DirectCast(1UI, UInt32Value)

            '            Dim r2 As New Row() With { _
            '	Key .RowIndex = DirectCast(1UI, UInt32Value) _
            '}

            Dim skippy2 As New UInt32Value()
            skippy2.Value = 2UI

            Dim r2 As New Row() With {.RowIndex = skippy2}

            Dim c3 As New Cell()
            c3.DataType = CellValues.String
            c3.CellValue = New CellValue("third")

            r2.Append(c3)
            sd.Append(r2)

            ws.Append(sd)
            wsp.Worksheet = ws
            wsp.Worksheet.Save()
            Dim sheets As New Sheets()
            Dim sheet As New Sheet()

            sheet.Name = "first test"
            sheet.SheetId = 1
            sheet.Id = wbp.GetIdOfPart(wsp)

            sheets.Append(sheet)
            wb.Append(fv)
            wb.Append(sheets)

            xl.WorkbookPart.Workbook = wb
            xl.WorkbookPart.Workbook.Save()
            xl.Close()

            Return Me.GetExcelFile("spreadsheet1", ms.ToArray())

        End Function

        Private Function GetExcelFile(fileName As String, data As Byte()) As FileResult

            Const ExcelContentType As String = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"
            Dim FileNameComplete As String = fileName & ".xlsx"
            Return File(data, ExcelContentType, FileNameComplete)

        End Function

        Function PositionChanges() As FileResult

            Dim Descriptor As New Cpp.Financials.Data.Repository.AccountDescriptionRepository
            Dim Repo As New d.Repository.Ado.Positions.ReconciliationHierarchy(Descriptor, Descriptor, Descriptor, Descriptor)

            Dim Model = Repo.GetPositionChanges

            Dim ms As New MemoryStream()

            Dim xl As SpreadsheetDocument = SpreadsheetDocument.Create(ms, SpreadsheetDocumentType.Workbook)

            Dim wbp As WorkbookPart = xl.AddWorkbookPart()
            Dim wsp As WorksheetPart = wbp.AddNewPart(Of WorksheetPart)()

            Dim wb As New Workbook()

            Dim fv As New FileVersion()

            fv.ApplicationName = "Excel"

            Dim ws As New Worksheet()

            Dim sd As New SheetData()

            Dim newRowIndex As New UInt32Value(System.Convert.ToUInt32(0))

            For Each item In Model.OrderBy(Function(t) t.FundLong)

                Dim r1 As New Row() With {.RowIndex = newRowIndex}

                Dim c As New Cell()
                c.DataType = CellValues.String
                c.CellValue = New CellValue(item.FundLong)
                r1.Append(c)

                sd.Append(r1)

                newRowIndex = System.Convert.ToUInt32(r1.RowIndex.Value + 1)
                r1.RowIndex = New UInt32Value(newRowIndex)

                For Each item2 In item.Changes.OrderBy(Function(r) r.Division).ThenBy(Function(q) q.Project)

                    Dim r2d2 As New Row() With {.RowIndex = newRowIndex}

                    Dim d1 As New Cell()
                    d1.DataType = CellValues.String
                    d1.CellValue = New CellValue(String.Empty)
                    r2d2.Append(d1)

                    Dim dd As New Cell()
                    dd.DataType = CellValues.String
                    dd.CellValue = New CellValue(item2.FriendlyDivisionProject)
                    r2d2.Append(dd)

                    sd.Append(r2d2)

                    newRowIndex = System.Convert.ToUInt32(r2d2.RowIndex.Value + 1)
                    r2d2.RowIndex = New UInt32Value(newRowIndex)

                    For Each item3 In item2.Positions.OrderBy(Function(u) u.PositionObject)

                        Dim c3po As New Row() With {.RowIndex = newRowIndex}

                        Dim ffNull As New Cell()
                        ffNull.DataType = CellValues.String
                        ffNull.CellValue = New CellValue(String.Empty)
                        c3po.Append(ffNull)

                        Dim ffNull2 As New Cell()
                        ffNull2.DataType = CellValues.String
                        ffNull2.CellValue = New CellValue(String.Empty)
                        c3po.Append(ffNull2)


                        Dim ff As New Cell()
                        ff.DataType = CellValues.String
                        ff.CellValue = New CellValue(item3.FriendlyPositionObject)
                        c3po.Append(ff)

                        Dim ft As New Cell()
                        ft.DataType = CellValues.Number
                        ft.CellValue = New CellValue(item3.FtCountChange)
                        c3po.Append(ft)

                        Dim pt As New Cell()
                        pt.DataType = CellValues.Number
                        pt.CellValue = New CellValue(item3.PtCountChange)
                        c3po.Append(pt)

                        sd.Append(c3po)

                        newRowIndex = System.Convert.ToUInt32(c3po.RowIndex.Value + 1)
                        c3po.RowIndex = New UInt32Value(newRowIndex)

                    Next



                Next


                'For Each cell As Cell In Row.Elements(Of Cell)()
                '    ' Update the references for reserved cells.
                '    Dim cellReference As String = cell.CellReference.Value
                '    cell.CellReference = New StringValue(cellReference.Replace(Row.RowIndex.Value.ToString(), newRowIndex.ToString()))
                'Next
                '' Update the row index.
                'Row.RowIndex = New UInt32Value(newRowIndex)


            Next

            ws.Append(sd)
            wsp.Worksheet = ws
            wsp.Worksheet.Save()
            Dim sheets As New Sheets()
            Dim sheet As New Sheet()

            sheet.Name = "position changes"
            sheet.SheetId = 1
            sheet.Id = wbp.GetIdOfPart(wsp)

            sheets.Append(sheet)
            wb.Append(fv)
            wb.Append(sheets)

            xl.WorkbookPart.Workbook = wb
            xl.WorkbookPart.Workbook.Save()
            xl.Close()

            Return Me.GetExcelFile("positionchanges", ms.ToArray())

        End Function


    End Class



End Namespace