How to export grid data as excel in LightSwitch Web Application

Someone recently asked me how to export LightSwitch grid to excel format. Well sounds easy in Desktop Mode coz there is inbuilt option when you publish application in Desktop mode but what about when application is published as web app?

I am going to show two different techniques First one is export as true Excel file and second technique is export as csv format but save as *.xls so it can open comma separated file in excel…

– To use below code create a button on your search screen and name it ExportCSV (or something else but I am using that name in this example)
– Right click on your button and click Edit Execute code and it will open code window.
– In my case screen name was SearchOrdersSearchQuery so place some initialize code such as Adding Event Handler
– Couple of things to notice in below code. First we register some event handlers, second we use
stream As New StreamWriter(dialog.OpenFile()) rather than using file paths because in web mode you don’t have access to host machine file system direct way for security reason so LightSwitch Provides you access to Stream where you can write data.

Technique-1: Export as CSV – comma separated file and Save as Excel

Imports System.Text

Namespace LightSwitchApplication
    Public Class SearchOrdersSearchQuery
        Private Sub SearchOrdersSearchQuery_Created()
            Dim CSVButton = Me.FindControl("ExportCSV")
            AddHandler CSVButton.ControlAvailable, AddressOf ExportCSV_ControlAvailable
        End Sub

        Private Sub ExportCSV_ControlAvailable(sender As Object, e As ControlAvailableEventArgs)
            RemoveHandler Me.FindControl("ExportCSV").ControlAvailable, AddressOf ExportCSV_ControlAvailable
            Dim Button = DirectCast(e.Control, Button)
            AddHandler Button.Click, AddressOf ExportCSV_Click
        End Sub

        Private Sub ExportCSV_Click(sender As Object, e As System.Windows.RoutedEventArgs)
            Dim collectionProperty As Microsoft.LightSwitch.Details.Client.IScreenCollectionProperty = Me.Details.Properties.OrdersSearchQuery
            Dim intPageSize = collectionProperty.PageSize
            'Get the Current PageSize and store to variable
            collectionProperty.PageSize = 0

            Dim dialog = New SaveFileDialog()
            dialog.Filter = "Excel (*.xls)|*.xls"
            If dialog.ShowDialog() = True Then

                Using stream As New StreamWriter(dialog.OpenFile())
                    Dim csv As String = GetCSV()
                    stream.Write(csv)
                    stream.Close()
                    Me.ShowMessageBox("Excel File Created Successfully. " & vbCrLf & "NOTE: When you open excel file and if you receive prompt about invalid format then just click yes to continue.", "Excel Export", MessageBoxOption.Ok)
                End Using
            End If
            collectionProperty.PageSize = intPageSize
            'Reset the Current PageSize
        End Sub

        '//Selected columns
        'Private Function GetCSV() As String
        '    Dim csv As New StringBuilder()
        '    csv.AppendFormat("Heading1 , Heading2, Heading3" & "," & System.Environment.NewLine)
        '    For Each c In Me.EvacuationRegistrations
        '        csv.AppendFormat(Convert.ToString(c.first_name) & "," & Convert.ToString(c.last_name) & "," & Convert.ToString(c.location) & "," & System.Environment.NewLine, c)
        '    Next
        '    Return csv.ToString(0, (csv.Length - 1))
        'End Function

        Private Function GetCSV() As String
            Dim csv As New StringBuilder()

            Dim i As Integer = 0

            For Each orderRow In OrdersSearchQuery
                '//HEADER
                If i = 0 Then
                    Dim c As Integer = 0
                    For Each prop In orderRow.Details.Properties.All().OfType(Of Microsoft.LightSwitch.Details.IEntityStorageProperty)()
                        If c > 0 Then
                            csv.Append(vbTab)
                        End If
                        c = c + 1
                        csv.Append(prop.DisplayName)
                    Next
                End If
                csv.AppendLine("")

                '//DATA ROWS

                Dim c1 As Integer = 0
                For Each prop In orderRow.Details.Properties.All().OfType(Of Microsoft.LightSwitch.Details.IEntityStorageProperty)()
                    If c1 > 0 Then
                        csv.Append(vbTab)
                    End If
                    c1 = c1 + 1
                    csv.Append(prop.Value)
                Next
                i = i + 1
            Next

            If csv.Length > 0 Then
                Return csv.ToString(0, csv.Length - 1)
            Else
                Return ""
            End If
        End Function

        '//Selected columns
        Private Function GetCSV_old() As String
            Dim csv As New StringBuilder()
            Dim i As Integer = 0

            For Each a In OrdersSearchQuery
                If i = 0 Then
                    csv.AppendFormat("OrderId" & "," & "OrderDate" & System.Environment.NewLine, a)
                End If
                csv.AppendFormat(a.OrderID & "," & a.OrderDate & System.Environment.NewLine, a)
                i = i + 1
            Next

            If csv.Length > 0 Then
                Return csv.ToString(0, csv.Length - 1)
            Else
                Return ""
            End If
        End Function

    End Class

End Namespace

Once you run LightSwitch application you can click on button “ExportToCVS” and it will generate tab separated file which can be opened in Excel.

Below post was very useful where I got this trick.

How to export data to CSV using Microsoft Lightswitch Web Application.

Technique-2: Export as true Excel file (Use ExcelWriter Class)

Here is full code which shows how to export data as true excel file (Binary format) which cant be viewed in text editor.

Imports System.Text

Namespace LightSwitchApplication
    Public Class SearchOrdersSearchQuery

        Private Sub SearchOrdersSearchQuery_Created()
            Dim CSVButton = Me.FindControl("ExportToExcel")
            AddHandler CSVButton.ControlAvailable, AddressOf ExportCSV_ControlAvailable
        End Sub

        Private Sub SearchClick_Execute()
            OrdersSearchQuery.Refresh()
        End Sub

        Private Sub ExportCSV_ControlAvailable(sender As Object, e As ControlAvailableEventArgs)
            RemoveHandler Me.FindControl("ExportToExcel").ControlAvailable, AddressOf ExportCSV_ControlAvailable
            Dim Button = DirectCast(e.Control, Button)
            AddHandler Button.Click, AddressOf ExportCSV_Click
        End Sub

        Dim EXPORT_FORMAT As String = "EXCEL" '// or CSV
        Private Sub ExportCSV_Click(sender As Object, e As System.Windows.RoutedEventArgs)
            If EXPORT_FORMAT = "CSV" Then
                ExportAsCSV()
            Else
                ExportAsExcel()
            End If
        End Sub

        Sub ExportAsExcel()
            Dim collectionProperty As Microsoft.LightSwitch.Details.Client.IScreenCollectionProperty = Me.Details.Properties.OrdersSearchQuery
            Dim intPageSize = collectionProperty.PageSize
            'Get the Current PageSize and store to variable
            collectionProperty.PageSize = 0


            'Dim stream As New FileStream("demo.xls", FileMode.OpenOrCreate)
            'Dim writer As New ExcelWriter(stream)
            'writer.BeginWrite()
            'writer.WriteCell(0, 0, "ExcelWriter Demo")
            'writer.WriteCell(1, 0, "int")
            'writer.WriteCell(2, 0, "double")
            'writer.WriteCell(1, 1, 10)
            'writer.WriteCell(2, 1, 1.5)
            'writer.WriteCell(3, 0, "empty")
            'writer.WriteCell(3, 1)
            'writer.EndWrite()
            'stream.Close()


            Dim dialog = New SaveFileDialog()
            dialog.Filter = "Excel (*.xls)|*.xls"
            If dialog.ShowDialog() = True Then
                Using fileStream As Stream = dialog.OpenFile()
                    Using writer1 As New ExcelWriter(fileStream)
                        Dim csv As String = GetCSV()
                        writer1.BeginWrite()
                        Dim rowsWritten As Integer = WriteToExcel(writer1)
                        writer1.EndWrite()
                        Me.ShowMessageBox("Excel File Created Successfully. Total " & rowsWritten & " written.", "Excel Export", MessageBoxOption.Ok)
                    End Using
                End Using
            End If
            collectionProperty.PageSize = intPageSize
            'Reset the Current PageSize
        End Sub
        Sub ExportAsCSV()
            Dim collectionProperty As Microsoft.LightSwitch.Details.Client.IScreenCollectionProperty = Me.Details.Properties.OrdersSearchQuery
            Dim intPageSize = collectionProperty.PageSize
            'Get the Current PageSize and store to variable
            collectionProperty.PageSize = 0

            Dim dialog = New SaveFileDialog()
            dialog.Filter = "Text Files (*.csv)|*.csv"
            If dialog.ShowDialog() = True Then

                Using stream As New StreamWriter(dialog.OpenFile())
                    Dim csv As String = GetCSV()
                    stream.Write(csv)
                    stream.Close()
                    Me.ShowMessageBox("CSV File Created Successfully. " & vbCrLf & "NOTE: When you open excel file and if you receive prompt about invalid format then just click yes to continue.", "Excel Export", MessageBoxOption.Ok)
                End Using
            End If
            collectionProperty.PageSize = intPageSize
            'Reset the Current PageSize
        End Sub
        '//Selected columns
        'Private Function GetCSV() As String
        '    Dim csv As New StringBuilder()
        '    csv.AppendFormat("Heading1 , Heading2, Heading3" & "," & System.Environment.NewLine)
        '    For Each c In Me.EvacuationRegistrations
        '        csv.AppendFormat(Convert.ToString(c.first_name) & "," & Convert.ToString(c.last_name) & "," & Convert.ToString(c.location) & "," & System.Environment.NewLine, c)
        '    Next
        '    Return csv.ToString(0, (csv.Length - 1))
        'End Function

        Private Function GetCSV() As String
            Dim csv As New StringBuilder()

            Dim i As Integer = 0

            For Each orderRow In OrdersSearchQuery
                '//HEADER
                If i = 0 Then
                    Dim c As Integer = 0
                    For Each prop In orderRow.Details.Properties.All().OfType(Of Microsoft.LightSwitch.Details.IEntityStorageProperty)()
                        If c > 0 Then
                            csv.Append(vbTab)
                        End If
                        c = c + 1
                        csv.Append(prop.DisplayName)
                    Next
                End If
                csv.AppendLine("")

                '//DATA ROWS

                Dim c1 As Integer = 0
                For Each prop In orderRow.Details.Properties.All().OfType(Of Microsoft.LightSwitch.Details.IEntityStorageProperty)()
                    If c1 > 0 Then
                        csv.Append(vbTab)
                    End If
                    c1 = c1 + 1
                    csv.Append(prop.Value)
                Next
                i = i + 1
            Next

            If csv.Length > 0 Then
                Return csv.ToString(0, csv.Length - 1)
            Else
                Return ""
            End If
        End Function

        '//Selected columns
        Private Function GetCSV_old() As String
            Dim csv As New StringBuilder()
            Dim i As Integer = 0

            For Each a In OrdersSearchQuery
                If i = 0 Then
                    csv.AppendFormat("OrderId" & "," & "OrderDate" & System.Environment.NewLine, a)
                End If
                csv.AppendFormat(a.OrderID & "," & a.OrderDate & System.Environment.NewLine, a)
                i = i + 1
            Next

            If csv.Length > 0 Then
                Return csv.ToString(0, csv.Length - 1)
            Else
                Return ""
            End If
        End Function

        Private Function WriteToExcel(writer As ExcelWriter) As Integer
            'writer.WriteCell(0, 0, "ExcelWriter Demo")
            'writer.WriteCell(1, 0, "int")
            'writer.WriteCell(1, 1, 10)
            'writer.WriteCell(2, 0, "double")
            'writer.WriteCell(2, 1, 1.5)
            'writer.WriteCell(3, 0, "empty")
            'writer.WriteCell(3, 1)

            Dim rowNum As Integer = 0

            For Each orderRow In OrdersSearchQuery
                '//HEADER
                If rowNum = 0 Then
                    WriteExcelHeaderRow(orderRow, writer)
                End If

                '//DATA ROWS
                WriteExcelDataRow(rowNum, orderRow, writer)

                rowNum = rowNum + 1
            Next

            Return rowNum
        End Function

        Private Sub WriteExcelHeaderRow(orderRow As Order, writer As ExcelWriter)
            Dim colNum1 As Integer = 0
            For Each prop In orderRow.Details.Properties.All().OfType(Of Microsoft.LightSwitch.Details.IEntityStorageProperty)()
                writer.WriteCell(0, colNum1, prop.DisplayName)
                colNum1 = colNum1 + 1
            Next
        End Sub

        Private Sub WriteExcelDataRow(rowNum As Integer, orderRow As Order, writer As ExcelWriter)
            Dim colNum2 As Integer = 0
            For Each prop In orderRow.Details.Properties.All().OfType(Of Microsoft.LightSwitch.Details.IEntityStorageProperty)()
                Select Case prop.PropertyType.Name
                    Case "Int32", "Int64"
                        writer.WriteCell(rowNum, colNum2, Convert.ToInt64(prop.Value))
                    Case "Double", "Decimal"
                        writer.WriteCell(rowNum, colNum2, Convert.ToDouble(prop.Value))
                    Case Else
                        writer.WriteCell(rowNum, colNum2, Convert.ToString(prop.Value))
                End Select


                colNum2 = colNum2 + 1
            Next
        End Sub
    End Class

    ''' <summary>
    ''' Produces Excel file without using Excel
    ''' </summary>
    Public Class ExcelWriter
        Implements IDisposable

        Private flagStarted As Boolean

        Private stream As Stream
        Private writer As BinaryWriter

        Private clBegin As UShort() = {&H809, 8, 0, &H10, 0, 0}
        Private clEnd As UShort() = {&HA, 0}


        ''' <summary>
        ''' Initializes a new instance of the <see cref="ExcelWriter"/> class.
        ''' </summary>
        ''' <param name="stream">The stream.</param>
        Public Sub New(stream As Stream)
            Me.stream = stream
            writer = New BinaryWriter(stream)
        End Sub

        Private Sub WriteUshortArray(value As UShort())
            For i As Integer = 0 To value.Length - 1
                writer.Write(value(i))
            Next
        End Sub

        ''' <summary>
        ''' Writes the text cell value.
        ''' </summary>
        ''' <param name="row">The row.</param>
        ''' <param name="col">The col.</param>
        ''' <param name="value">The string value.</param>
        Public Sub WriteCell(row As Integer, col As Integer, value As String)
            Dim clData As UShort() = {&H204, 0, 0, 0, 0, 0}
            Dim iLen As Integer = value.Length
            Dim plainText As Byte() = StringToAscii(value) '//Encoding.ASCII.GetBytes(value) //Becuase SilverLight doesnt have ASCII Encoding I wrote little function
            clData(1) = CUShort(8 + iLen)
            clData(2) = CUShort(row)
            clData(3) = CUShort(col)
            clData(5) = CUShort(iLen)
            WriteUshortArray(clData)
            writer.Write(plainText)
        End Sub

        Private Function StringToAscii(s As String) As Byte()
            Dim retval As Byte() = New Byte(s.Length - 1) {}
            For ix As Integer = 0 To s.Length - 1
                Dim ch As Char = s(ix)
                If AscW(ch) <= &H7F Then
                    retval(ix) = CByte(AscW(ch))
                Else
                    retval(ix) = CByte(AscW("?"c))
                End If
            Next
            Return retval
        End Function

        ''' <summary>
        ''' Writes the integer cell value.
        ''' </summary>
        ''' <param name="row">The row number.</param>
        ''' <param name="col">The column number.</param>
        ''' <param name="value">The value.</param>
        Public Sub WriteCell(row As Integer, col As Integer, value As Integer)
            Dim clData As UShort() = {&H27E, 10, 0, 0, 0}
            clData(2) = CUShort(row)
            clData(3) = CUShort(col)
            WriteUshortArray(clData)
            Dim iValue As Integer = (value << 2) Or 2
            writer.Write(iValue)
        End Sub

        ''' <summary>
        ''' Writes the double cell value.
        ''' </summary>
        ''' <param name="row">The row number.</param>
        ''' <param name="col">The column number.</param>
        ''' <param name="value">The value.</param>
        Public Sub WriteCell(row As Integer, col As Integer, value As Double)
            Dim clData As UShort() = {&H203, 14, 0, 0, 0}
            clData(2) = CUShort(row)
            clData(3) = CUShort(col)
            WriteUshortArray(clData)
            writer.Write(value)
        End Sub

        ''' <summary>
        ''' Writes the empty cell.
        ''' </summary>
        ''' <param name="row">The row number.</param>
        ''' <param name="col">The column number.</param>
        Public Sub WriteCell(row As Integer, col As Integer)
            Dim clData As UShort() = {&H201, 6, 0, 0, &H17}
            clData(2) = CUShort(row)
            clData(3) = CUShort(col)
            WriteUshortArray(clData)
        End Sub

        ''' <summary>
        ''' Must be called once for creating XLS file header
        ''' </summary>
        Public Sub BeginWrite()
            flagStarted = True
            WriteUshortArray(clBegin)
        End Sub

        ''' <summary>
        ''' Ends the writing operation, but do not close the stream
        ''' </summary>
        Public Sub EndWrite()
            WriteUshortArray(clEnd)
            writer.Flush()
            flagStarted = False
        End Sub

#Region "IDisposable Support"
        Private disposedValue As Boolean ' To detect redundant calls

        ' IDisposable
        Protected Overridable Sub Dispose(disposing As Boolean)
            If Not Me.disposedValue Then
                If disposing Then
                    ' TODO: dispose managed state (managed objects).
                End If

                If writer IsNot Nothing Then
                    writer.Dispose()
                End If

                ' TODO: free unmanaged resources (unmanaged objects) and override Finalize() below.
                ' TODO: set large fields to null.
            End If
            Me.disposedValue = True
        End Sub

        ' TODO: override Finalize() only if Dispose(ByVal disposing As Boolean) above has code to free unmanaged resources.
        'Protected Overrides Sub Finalize()
        '    ' Do not change this code.  Put cleanup code in Dispose(ByVal disposing As Boolean) above.
        '    Dispose(False)
        '    MyBase.Finalize()
        'End Sub

        ' This code added by Visual Basic to correctly implement the disposable pattern.
        Public Sub Dispose() Implements IDisposable.Dispose
            ' Do not change this code.  Put cleanup code in Dispose(disposing As Boolean) above.
            Dispose(True)
            GC.SuppressFinalize(Me)
        End Sub
#End Region

    End Class

End Namespace

Hope you find this useful !!!