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?<\/p>\n
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…<\/p>\n
– 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)
\n– Right click on your button and click Edit Execute code and it will open code window.
\n– In my case screen name was SearchOrdersSearchQuery so place some initialize code such as Adding Event Handler
\n– Couple of things to notice in below code. First we register some event handlers, second we use
\nstream 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.<\/p>\n
Imports System.Text\r\n\r\nNamespace LightSwitchApplication\r\n Public Class SearchOrdersSearchQuery\r\n Private Sub SearchOrdersSearchQuery_Created()\r\n Dim CSVButton = Me.FindControl("ExportCSV")\r\n AddHandler CSVButton.ControlAvailable, AddressOf ExportCSV_ControlAvailable\r\n End Sub\r\n\r\n Private Sub ExportCSV_ControlAvailable(sender As Object, e As ControlAvailableEventArgs)\r\n RemoveHandler Me.FindControl("ExportCSV").ControlAvailable, AddressOf ExportCSV_ControlAvailable\r\n Dim Button = DirectCast(e.Control, Button)\r\n AddHandler Button.Click, AddressOf ExportCSV_Click\r\n End Sub\r\n\r\n Private Sub ExportCSV_Click(sender As Object, e As System.Windows.RoutedEventArgs)\r\n Dim collectionProperty As Microsoft.LightSwitch.Details.Client.IScreenCollectionProperty = Me.Details.Properties.OrdersSearchQuery\r\n Dim intPageSize = collectionProperty.PageSize\r\n 'Get the Current PageSize and store to variable\r\n collectionProperty.PageSize = 0\r\n\r\n Dim dialog = New SaveFileDialog()\r\n dialog.Filter = "Excel (*.xls)|*.xls"\r\n If dialog.ShowDialog() = True Then\r\n\r\n Using stream As New StreamWriter(dialog.OpenFile())\r\n Dim csv As String = GetCSV()\r\n stream.Write(csv)\r\n stream.Close()\r\n 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)\r\n End Using\r\n End If\r\n collectionProperty.PageSize = intPageSize\r\n 'Reset the Current PageSize\r\n End Sub\r\n\r\n '\/\/Selected columns\r\n 'Private Function GetCSV() As String\r\n ' Dim csv As New StringBuilder()\r\n ' csv.AppendFormat("Heading1 , Heading2, Heading3" & "," & System.Environment.NewLine)\r\n ' For Each c In Me.EvacuationRegistrations\r\n ' csv.AppendFormat(Convert.ToString(c.first_name) & "," & Convert.ToString(c.last_name) & "," & Convert.ToString(c.location) & "," & System.Environment.NewLine, c)\r\n ' Next\r\n ' Return csv.ToString(0, (csv.Length - 1))\r\n 'End Function\r\n\r\n Private Function GetCSV() As String\r\n Dim csv As New StringBuilder()\r\n\r\n Dim i As Integer = 0\r\n\r\n For Each orderRow In OrdersSearchQuery\r\n '\/\/HEADER\r\n If i = 0 Then\r\n Dim c As Integer = 0\r\n For Each prop In orderRow.Details.Properties.All().OfType(Of Microsoft.LightSwitch.Details.IEntityStorageProperty)()\r\n If c > 0 Then\r\n csv.Append(vbTab)\r\n End If\r\n c = c + 1\r\n csv.Append(prop.DisplayName)\r\n Next\r\n End If\r\n csv.AppendLine("")\r\n\r\n '\/\/DATA ROWS\r\n\r\n Dim c1 As Integer = 0\r\n For Each prop In orderRow.Details.Properties.All().OfType(Of Microsoft.LightSwitch.Details.IEntityStorageProperty)()\r\n If c1 > 0 Then\r\n csv.Append(vbTab)\r\n End If\r\n c1 = c1 + 1\r\n csv.Append(prop.Value)\r\n Next\r\n i = i + 1\r\n Next\r\n\r\n If csv.Length > 0 Then\r\n Return csv.ToString(0, csv.Length - 1)\r\n Else\r\n Return ""\r\n End If\r\n End Function\r\n\r\n '\/\/Selected columns\r\n Private Function GetCSV_old() As String\r\n Dim csv As New StringBuilder()\r\n Dim i As Integer = 0\r\n\r\n For Each a In OrdersSearchQuery\r\n If i = 0 Then\r\n csv.AppendFormat("OrderId" & "," & "OrderDate" & System.Environment.NewLine, a)\r\n End If\r\n csv.AppendFormat(a.OrderID & "," & a.OrderDate & System.Environment.NewLine, a)\r\n i = i + 1\r\n Next\r\n\r\n If csv.Length > 0 Then\r\n Return csv.ToString(0, csv.Length - 1)\r\n Else\r\n Return ""\r\n End If\r\n End Function\r\n\r\n End Class\r\n\r\nEnd Namespace<\/pre>\nOnce you run LightSwitch application you can click on button “ExportToCVS” and it will generate tab separated file which can be opened in Excel.<\/p>\n
Below post was very useful where I got this trick.<\/p>\n
How to export data to CSV using Microsoft Lightswitch Web Application.<\/a><\/p><\/blockquote>\n