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
About

Software developer living in the Philippines.

Posted in Lightswitch
30 comments on “How to export data to CSV using Microsoft Lightswitch Web Application.
  1. Heather Mounts says:

    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

    • hgminerva says:

      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.

    • Heather Mounts says:

      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. Mike says:

    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. hgminerva says:

    The findcontrol must correspond to your button name. Anyways I uploaded a working project: http://www.mediafire.com/?6ga3lyl54purwgf

  4. Otis Ranger says:

    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.

    • hgminerva says:

      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.

      • Otis Ranger says:

        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?

      • hgminerva says:

        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.

  5. Otis Ranger says:

    If I get the solution I will be more than happy to share with everyone and I have asked the VS Community for help also (http://social.msdn.microsoft.com/Forums/en-US/lightswitch/thread/e5f0d29d-5133-4297-8d59-33a208bda903)

    • Otis Ranger says:

      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.

      • hgminerva says:

        Superb! This is what I am looking for (How to add and remove handlers). I converted to VB and re-post this to my blog if you don’t mined.

      • Otis Ranger says:

        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.

      • hgminerva says:

        The credit is all yours, can you provide us a link or so.

      • Otis Ranger says:

        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

      • hgminerva says:

        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.

      • hgminerva says:

        By the way, you don’t need to declare a Microsoft.LightSwitch.Details.Client.IScreenCollectionProperty variable just invoke directly Me.Details.Properties..PageSize. https://hgminerva.wordpress.com/2012/08/18/how-to-remove-programmatically-the-paging-in-the-grid-of-microsoft-ligthswitch/

    • hgminerva says:

      Voted! Superb solution. Just modified this blog and uploaded the updated working project base on your comment .

  6. Dave Vorgang says:

    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

    • Otis Ranger says:

      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

      • Dave Vorgang says:

        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

      • Otis Ranger says:

        Cool,

        Just glad you got it sorted 🙂

  7. […] this example we will be using the dialog box similar to what we did in exporting a CSV File.  The first step is to add a handler to a button. To add a handler you need to modify the created […]

  8. Sherlene says:

    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!

    • hgminerva says:

      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.

  9. How can i export to excel with the default button in Lightswitch in LS Web App?

    • Otis Ranger says:

      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

Author

Harold Glenn P. Minerva
Software Developer / Tech Enthusiast
Living in the Philippines

View Harold Glenn Minerva's profile on LinkedIn

Instagram

Software Engineer - Seasonal and Range Trading Software. Magenta Trader is a powerful stock market visualization software that increases your probability of trading success.

Software Architect and Founder - Easyfis.com is a multi-tenant cloud-based Software-as-a-Service (SaaS) business app that caters to micro, small and medium trading businesses.

CTO and Co-Founder - We give your company the leverage by providing innovative software solutions products such as Point-of-Sales (POS), Financial Information System (FMIS), Payroll and DTR (HRIS), and many more.

%d bloggers like this: