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

Exporting data from a Microsoft Lightswitch application to local drive using browser is a little bit tricky.  At first I thought I can just use a normal thread dispatcher to open a dialog box but it turns out it did not work on web applications.

Fortunately there is a workaround to the problem.  Below are the steps on how to export a data from a Microsoft Ligthswitch web application to local drive using CSV format.

1. Know the fields and table you want to export.

2. Create a method/button in your screen. e.g., “ExportAccountToCSV”

3. Create an CSV string create Sub.  See sample below:

Private Function GetAccountCSV() As String
    Dim csv As New StringBuilder()
    Dim i As Integer = 0

    For Each a In mstAccounts
        If i = 0 Then
        csv.AppendFormat("AccountNumber" & "," & "AccountName" & "," & "AccountType" & System.Environment.NewLine, a)
        End If
        csv.AppendFormat(a.AccountNumber & "," & a.AccountName & "," & a.AccountType & 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

4. Put an execute code in the export button “ExportAccountToCSV”

Private Sub ExportAccountToCSV_Execute()
    Dim csv As String = GetAccountCSV()
    AddHandler Me.FindControl("ExportAccountToCSV").ControlAvailable, (Function(object1, eventargs1)
               Dim btnExport As Button = DirectCast(eventargs1.Control, Button)
               AddHandler btnExport.Click,
               (Function(object2, eventargs2)
                       Dim dialog = New SaveFileDialog()
                       dialog.Filter = "CSV (*.csv)|*.csv"
                       If dialog.ShowDialog() = True Then
                           Using stream As New StreamWriter(dialog.OpenFile())
                           stream.Write(csv)
                           stream.Close()
                           End Using
                       End If
               End Function)
   End Function)
End Sub

That’s it.  Below is my example screen that exports and imports a CSV file.

Update (July 24, 2012):  Since there were so many inquiries and questions regarding this blog I’ve created and uploaded a working project: http://www.mediafire.com/?6ga3lyl54purwgf

Update (August 16, 2012): Otis Ranger, made it so that we could eliminate the annoying Silverlight message “Dialog not yet initialized” every time we re-click the export button.  Below is the equivalent VB.NET code of Otis Ranger,  I made some minor adjustments to make it simpler so that also we could re-use our CSV function.

        Private Sub EditableTable1ItemsGrid_Created()
            AddHandler Me.FindControl("Export").ControlAvailable, AddressOf exportAvailable
        End Sub

        Private Sub exportAvailable(sender As Object, e As ControlAvailableEventArgs)
            Dim Button = DirectCast(e.Control, Button)

            RemoveHandler Me.FindControl("Export").ControlAvailable, AddressOf exportAvailable

            AddHandler Button.Click, AddressOf exportClicked
        End Sub

        Private Sub exportClicked(sender As Object, e As System.Windows.RoutedEventArgs)
            Dim csv As String = GetTextCSV()
            Dim dialog = New SaveFileDialog()
            dialog.Filter = "CSV (*.csv)|*.csv"
            If dialog.ShowDialog() = True Then
                Using stream As New StreamWriter(dialog.OpenFile())
                    stream.Write(csv)
                    stream.Close()
                End Using
            End If
        End Sub

If you find this blog helpful, you can make the donation by clicking the Paypal button below.

Advertisements

30 thoughts on “How to export data to CSV using Microsoft Lightswitch Web Application.

  1. I am new at this and was wondering if you could go into a little more detail as to where you created the CSV sub. What did you click on? I understand the code button but not sure where to stick the other code.

    Thanks,

    Heather

    1. Just create a button. Then write a code for that button in its execute method. In my example its ExportAccountToCSV_Execute, just copy this code. Then copy the CSV creator function, in my example its, GetAccountCSV, put this function anywhere in your screen codes so that the ExportAccountToCSV_Execute can call this function.

    2. Anyway we can get a copy of the updated code in VB? The old way is working ok but it sure would be nice to get the new solution.

      Thanks for everyones help on this!!!

      Heather

  2. I have successfully implemented saving the files from the local drive to the database.
    Now I am trying to retrieve the files from the database and save them to the user machine.
    I tried to implement the code that you have provided for saving the file but it is not displaying the savedialog and doing nothing when I click the button in which I have placed your code. My application is running in browser.
    It would be very helpful if you could upload a working sample project.

  3. Can I just ask how much testing you have done on your “working project”? I have seen many posts similar to this and they all seem to have the same problem. The first time you click the button it works beautifully. ANY subsequent click on the button throws an error including your example.

    1. Yes, I admit there is a message notifying the user that silverlight must initialize the dialog. There are so many hush hush about it, but the solution, actually just a work around, to the message is just to refresh the screen every time you close the dialog. There no solution actually to this, as far as I know, because its not an error it is just security feature of silverlight, but its an annoying message though.

      1. Then why not mention this? THe whole point in this workaround is because of the security issues that have been put in place. If I am understanding the code correctly you are setting the Click event every time the button is pressed but not removing it again afterwards. Surely if the click event is removed once it has done it’s thing it would be ok?

      2. Apparently I don’t know how to remove the handler and my users are just contended pressing the refresh button every time they got the message. But If you have the code and would like to share to all of us, I’ll be happy to include it in my example.

    1. Here is the solution! Firstly do not put all the code in the button event and set the controlavailiable event in the screen created event. then separate the rest of the code like so:-
      partial void MultiLoadContracts_Created()
      {
      // Write your code here.
      var CSVButton = this.FindControl(“ExportToExcel”);
      CSVButton.ControlAvailable += DownloadButton_ControlAvailable;
      }
      private string GetCSV()
      {
      StringBuilder csv = new StringBuilder();
      csv.AppendFormat(“Heading1 , Heading2, Heading3” + “,” +
      System.Environment.NewLine);
      foreach (var c in this.DateQuery)
      {
      csv.AppendFormat(c.FirstField + “,” +
      c.SecondField + “,” +
      c.ThirdField + “,” +
      System.Environment.NewLine
      , c);
      }
      return csv.ToString(0, csv.Length – 1);
      }
      void DownloadButton_ControlAvailable(object sender, ControlAvailableEventArgs e)
      {
      this.FindControl(“ExportToExcel”).ControlAvailable -= DownloadButton_ControlAvailable;
      var Button = (Button)e.Control;
      Button.Click += DownloadButton_Click;
      }
      void DownloadButton_Click(object sender, System.Windows.RoutedEventArgs e)
      {
      string csv = GetCSV();
      var dialog = new SaveFileDialog();
      dialog.Filter = “CSV (*.csv)|*.csv”;
      if (dialog.ShowDialog() == true)
      {
      using (StreamWriter stream = new StreamWriter(dialog.OpenFile()))
      {
      stream.Write(csv);
      stream.Close();
      this.ShowMessageBox(“CSV File Created Successfully”, “TPS CSV Export”, MessageBoxOption.Ok);
      }
      }
      }

      It will be easier to follow the link in my earlier post to the MSDN site & if you do navigate to there please vote the post as helpful as I am sure it will be a lot better than hitting the refresh after each export.

      1. I have no problem at all with it as long as you give me credit and +1 me on the MSDN site. As I said before I am more than happy to share.

      2. Realised that there is a flaw in the code. If your grid supports paging it only exports what is on the screen. This updated code disables the paging, exports and then re-enables it again.

        partial void MultiLoadContracts_Created()
        {
        // Write your code here.
        var CSVButton = this.FindControl(“ExportToExcel”);
        CSVButton.ControlAvailable += DownloadButton_ControlAvailable;
        }
        private string GetCSV()
        {
        StringBuilder csv = new StringBuilder();
        csv.AppendFormat(“Heading1 , Heading2, Heading3” + “,” +
        System.Environment.NewLine);
        foreach (var c in this.DateQuery)
        {
        csv.AppendFormat(c.FirstField + “,” +
        c.SecondField + “,” +
        c.ThirdField + “,” +
        System.Environment.NewLine
        , c);
        }
        return csv.ToString(0, csv.Length – 1);
        }
        void DownloadButton_ControlAvailable(object sender, ControlAvailableEventArgs e)
        {
        this.FindControl(“ExportToExcel”).ControlAvailable -= DownloadButton_ControlAvailable;
        var Button = (Button)e.Control;
        Button.Click += DownloadButton_Click;
        }
        void DownloadButton_Click(object sender, System.Windows.RoutedEventArgs e)
        {
        Microsoft.LightSwitch.Details.Client.IScreenCollectionProperty collectionProperty = this.Details.Properties.DateQuery;
        var intPageSize = collectionProperty.PageSize; //Get the Current PageSize and store to variable
        collectionProperty.PageSize = 0;

        var dialog = new SaveFileDialog();
        dialog.Filter = “CSV (*.csv)|*.csv”;
        if (dialog.ShowDialog() == true)
        {
        using (StreamWriter stream = new StreamWriter(dialog.OpenFile()))
        {
        string csv = GetCSV();
        stream.Write(csv);
        stream.Close();
        this.ShowMessageBox(“CSV File Created Successfully”, “TPS CSV Export”, MessageBoxOption.Ok);
        }
        }
        collectionProperty.PageSize = intPageSize; //Reset the Current PageSize
        }

        Please +1 this link http://social.msdn.microsoft.com/Forums/en-US/lightswitch/thread/e5f0d29d-5133-4297-8d59-33a208bda903

      3. I think disabling grid paging should be in another blog. Going directly to the data source is what I normally do but using the grid data and just disabling the paging is witty and easy to implement. Thanks, Voted.

  4. Hi,
    I need to be able to create the csv from a data source and not a grid. I changed the code to:

    Private Sub ExportData()
    Dim Completed = False
    Dim csv As New StringBuilder()
    Me.Details.Dispatcher.BeginInvoke(
    Sub()
    Dim i As Integer = 0
    Dim DT = DoseTypes.SelectedItem.DoseTypeIdent

    Dim Recs = DataWorkspace.ShotEntryData.StudentsInDangerByDoseTypeAndFacilityIdent(DT, ppvFacility.FacilityIdent)
    For Each a As StudentsInDanger In Recs
    If i = 0 Then
    csv.AppendFormat(“AccountNumber” & “,” & “AccountName” & “,” & “AccountType” & System.Environment.NewLine, a)
    End If
    csv.AppendFormat(a.LastName & “,” & a.FirstName & “,” & a.AssessmentText & System.Environment.NewLine, a)
    i = i + 1
    Next
    Completed = True
    End Sub)

    ‘If csv.Length > 0 Then
    Dim dialog = New SaveFileDialog()
    dialog.Filter = “CSV (*.csv)|*.csv”
    If dialog.ShowDialog() = True Then
    Using stream As New StreamWriter(dialog.OpenFile())
    stream.Write(csv.ToString(0, csv.Length – 1))
    stream.Close()
    End Using
    End If
    ‘Else
    ‘ShowMessageBox(“No data was available to be exported.”)
    ‘End If

    End Sub

    The problem is the code within the BeginInvoke is not blocked and completed the execution before it gets to the dialog box comes up. I have to do the BeginInvoke or I will get the “It is not valid to call Execute() on a different Dispatcher than the ExecutableObject’s Logic Dispatcher” error.

    Any Ideas on how to resolve this?
    Thanks,
    Dave

    1. I don’t have the time to test the solution, but why are you gathering your data inside another function? The way the examples show is that the data is returned to it so it *should* wait until it is completed before continuing…….in theory. If I get a chance this weekend then I will have a look at a solution 🙂

      P.S. Have you posted this on the MSDN Lightswitch forum? There are some brilliant minds there that may help quicker

      1. Hi Otis,

        I should have replied sooner. I could not read directly from my database on the UI thread that is why I had to use BeginInvoke.

        Justin responded to my question on MSDN but his solution did not work for me completely but it did help.

        I was able to resolve it by first showing the dialog then perform the BeginInvoke. The sub within the BeginInvoke set a screen property (which implements INotifyChange) when it completed the writes to the StringBuilder variable. The property’s changed routine then writes out the file.

        Thanks,
        Dave

  5. Hi there! Quick question that’s completely off topic. Do you know how to make your site mobile friendly? My site looks weird when browsing from my iphone. I’m trying to find
    a theme or plugin that might be able to correct this issue.

    If you have any suggestions, please share. Thank you!

    1. Yep, silverlight seems not friendly on mobile devices, especially phone size device, but for surface and ipad it seems ok. Still waiting for the HTML 5 client in LS though.

    1. You cant, hence the “fun” with this exporting to CSV. I am talking in regards to LS V1 but I dont think anyuthing has changed in regards to Web app in any of the versions.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s