Export CSV File with Lightswitch WebClient

Introduction

Lightswitch Desktop client offers you the possibility to export a datagrid into an Excel file. But this feature has not been incorporated (security reason) in the Web version of the Desktop Client. I would like to thank the Lightswitch community for all the hard work.

Background

It is recommended to know how to use Lightswitch and where to find the different components. Here are some references that will help you to understand.

• http://blogs.msdn.com/b/rmattsampson/archive/2011/05/10/how-do-i-import-data-from-a-web-application.aspx

Using the Code

To export a DataGrid, we will need:

• An Export Button
• An XAML Interface to download the file
• A new class managing the Import/Export

Export Button

Create a new button, "ExportExcel" for example. Click on Edit Execute Code. The following will code a dispatcher in charge of opening a dialog window for you to download the file.

partial void ExtractExcel_Execute()
{
    Dispatchers.Main.BeginInvoke(() =>
    {
        SelectSaveFileWindow selectSaveFileWindow = new SelectSaveFileWindow();
        selectSaveFileWindow.Closed += new EventHandler(selectSaveFileWindow_Closed);
        selectSaveFileWindow.Show();
    });
}

Once the button created, and the dispatcher ready, let's create a SaveDialogWindow.

SaveDialogWindow

We are going to create a Silverlight based window that will help us to export the grid into a CSV file. To do so, right click on UserCode, Add New Item.

Select Silverlight, SilverLight ChildWindow, name it "SaveFileWindow.xaml". My window looks like this, but feel free to change it. :)

You can notice that the window is calling a class "SelectSaveFileWindow" from the user code folder.

<controls:ChildWindow x:Class="LightSwitchApplication.UserCode.SelectSaveFileWindow"
           xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
           xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
           xmlns:controls="clr-namespace:System.Windows.Controls;assembly=System.Windows.Controls"
           Width="394" Height="305"
           Title="Save File" >
    <Grid x:Name="LayoutRoot" Margin="2">
        <Grid.RowDefinitions>
            <RowDefinition />
            <RowDefinition Height="Auto" />
        </Grid.RowDefinitions>
        <Button x:Name="CancelButton" Content="Cancel" 
        Click="CancelButton_Click" Width="75" Height="23" 
        HorizontalAlignment="Right" Margin="0,12,0,0" Grid.Row="1" />
        <Button x:Name="OKButton" Content="OK" 
        Click="OKButton_Click" Width="75" Height="23" 
        HorizontalAlignment="Right" Margin="0,12,79,0" Grid.Row="1" />
        <Button Content="Browse" Height="23" 
        HorizontalAlignment="Left" Margin="291,92,0,0" 
        Name="BrowseButton" VerticalAlignment="Top" 
        Width="75" Click="BrowseButton_Click" />
        <TextBox Height="23" HorizontalAlignment="Left" 
        Margin="66,92,0,0" Name="FileTextBox" 
        VerticalAlignment="Top" Width="219" IsEnabled="True"/>
    </Grid>
</controls:ChildWindow>

The window:

Below the code you need to find in this class.

public partial class SelectSaveFileWindow : ChildWindow
    {
        public SelectSaveFileWindow()
        {
            InitializeComponent();
        }
        private Stream documentStream;
        public Stream DocumentStream
        {
            get { return documentStream; }
            set { documentStream = value; }
        }

        private void OKButton_Click(object sender, RoutedEventArgs e)
        {
            this.DialogResult = true;
        }
        private void CancelButton_Click(object sender, RoutedEventArgs e)
        {
            this.DialogResult = false;
        }
        private void BrowseButton_Click(object sender, RoutedEventArgs e)
        {
            
            SaveFileDialog SaveFileWindow = new SaveFileDialog();
            SaveFileWindow.Filter = "csv files (*.csv)|*.csv";
            
          //  SaveFileWindow.FilterIndex = 1;
            if (SaveFileWindow.ShowDialog() == true)
            {
                this.FileTextBox.Text = SaveFileWindow.SafeFileName;
                System.IO.Stream myStream = SaveFileWindow.OpenFile();
                documentStream = myStream;
            }
        }
    }

ExportClass

This class will be the one writing the data from your entity, into the CSV file you created with the help of the previous window.

public class CSVExport
    {
        public void ExportCSV(object sender,IEnumerable entitiesToExport, string[] properties)
        {
            //System.IO.Stream stream = null;
            SelectSaveFileWindow SelectSaveFileWindow = (SelectSaveFileWindow)sender;
            if (SelectSaveFileWindow.DialogResult == true 
                 && (SelectSaveFileWindow.DocumentStream != null))
            {
               PerformExport(SelectSaveFileWindow.DocumentStream, entitiesToExport, properties);
               SelectSaveFileWindow.DocumentStream.Close();
            }
        }
        private static void PerformExport
        (System.IO.Stream file, IEnumerable entitiesToExport, string[] properties)
        {
            // Initialize a writer
            System.IO.StreamWriter writer = new System.IO.StreamWriter(file);
            writer.AutoFlush = true;

            // Write the header
            writer.WriteLine(String.Join(",", properties));

            // Export each entity separately
            foreach (IEntityObject entity in entitiesToExport)
            {
                ExportSingle(writer, entity, properties);
            }
        }  
        private static void ExportSingle
        (System.IO.StreamWriter writer, IEntityObject entity, string[] properties)
        {
            List<string> stringArray = new List<string>();
            Microsoft.LightSwitch.Details.IEntityProperty currentProperty;
            // Write each property to the string array
            foreach (string prop in properties)
            {
                try
                {
                    // Get the property from the entity by name
                    currentProperty = entity.Details.Properties[prop];
                }
                catch (Exception)
                {
                    throw new InvalidOperationException
                    (String.Format("A property named {0} 
                    does not exist on the entity named {1}.", prop, entity.Details.Name));
                }
                stringArray.Add(currentProperty.Value.ToString());
            }
            // Write the string array
            writer.WriteLine(String.Join(",", stringArray.ToArray()));
        }
        
        
    }

The Final Step !

Almost there! So here is the generic part, now to export the data we are going to need to provide a list of the entity to export. Remember the Dispatcher we created first? We have an Event Handler on it. This is the one that will trigger the export.

Let's take a look at the code.

void selectSaveFileWindow_Closed(object sender, EventArgs e)
{
    ExcelExport G = new ExcelExport();
    List<string> List = new List<string>();

    List.Add("FirstName");
    List.Add("LastName");
    List.Add("Address");

    G.ExportCSV(sender, this.EmployeeList, List.ToArray());
}

Let's review what is inside. We are creating a list of the file to export from our DataGrid. We are passing this to the function "ExportCSV" along with the event, the datagrid and the list.

Points of Interest

I am fairly new developing on Lightswitch, and love how easy it is. There is a big community out there trying to make this work. This is still a lot of improvement we can do to this code, so feel free to help to improve this. I would like to thank all the persons working on LS and all the tutorials on the internet, without them I will still try to figure out to export on Excel. :)

History

• 11/26/2014: Original draft.
• 12/01/2014: Corrected Spelling and added pictures.