Exporting Data From Silverlight Datagrid To Excel

Categories: Silverlight & Excel Posted on 9/15/2009 12:55 PM by Ryan Shelby Feedback (16)

As I mentioned in my last post Creating an Excel Worksheet from Silverlight, I created a Silverlight Datagrid that exposes an "Export" method to extract the information from the Datagrid, and then exports it out to an Excel File. All the Developer then has to do is add a button control with a click event containing the call.

Below is an image of the Demo that I created you can click on and try out.

This control uses the same set of Excel Files I explained about previously in my last post. In order to get this control to work in your project, follow the steps listed below:

1.) Add the Excel folder contained in the Source Code below to your Silverlight project.

2.) Add the Excel Datagrid Control to your XAML. Below shows the minimal XAML required to setup and reference the Excel Datagrid in XAML. This example only uses DataGridTextColumns, but you can also use template columns containing TextBlocks, TextBoxes, HyperLinkButtons, and Grids or StackPanels containing child controls. Make sure you replace "ExcelSilverlightDemo" with whatever the name of your project is.

<UserControl x:Class="ExcelSilverlightDemo.MainPage"

    xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"

    xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"

    xmlns:data="clr-namespace:System.Windows.Controls;assembly=System.Windows.Controls.Data"

    xmlns:d="http://schemas.microsoft.com/expression/blend/2008"

    xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006"

    xmlns:Excel="clr-namespace:ExcelSilverlightDemo.Excel">

        <Grid x:Name="LayoutRoot" Background="Black" HorizontalAlignment="Stretch" VerticalAlignment="Stretch" Width="700">

            <Grid.RowDefinitions>

                <RowDefinition Height="30" />

                <RowDefinition Height="*" />

            </Grid.RowDefinitions>

            <Button x:Name="ExportButton" Width="150" Content="Export to Excel" HorizontalAlignment="Right" Grid.Row="1" Canvas.ZIndex="5" Height="25" Click="ExportToExcel_Click" />

            <Excel:DataGrid x:Name="dgDemo" AutoGenerateColumns="False" HorizontalAlignment="Stretch" VerticalAlignment="Stretch" Grid.Row="1">

                <data:DataGrid.Columns>

                 <data:DataGridTextColumn Header="ID" Binding="{Binding ID}" IsReadOnly="True" Width="40" />

                 <data:DataGridTextColumn Header="Part Number" Binding="{Binding PartNumber}" IsReadOnly="True" />

                 <data:DataGridTextColumn Header="Part Name" Binding="{Binding PartName}" IsReadOnly="True" Width="100" />

                 <data:DataGridTextColumn Header="Made" Binding="{Binding MadeDate}" IsReadOnly="True" Width="200" />

                 <data:DataGridTextColumn Header="Quantity" Binding="{Binding Quantity}" IsReadOnly="True" Width="50" />

                </data:DataGrid.Columns>

            </Excel:DataGrid>

    </Grid>

</UserControl>

3.) Add a button control to your XAML and then include the "Export" Method to the Button's Click event.

Private Sub ExportToExcel_Click(ByVal sender As System.Object, ByVal e As System.Windows.RoutedEventArgs)

        dgDemo.Export() 'One simple command called by the datagrid.

End Sub

That's pretty much it. Build and run the project. When you click the export button, the "Save As" Dialog Box appears shown below. All you have to do is enter the file name and click save, and the file will be created to that location client-side. Depending on your operating system or settings, the file may just appear as a regular XML file. However it should open up in Microsoft Excel 2007 when you click on it.

This Datagrid works similiar to the Print Friendly Datagrid Control I made a few months ago. So if you would like to see how everything works, you can check out the older post. However instead of HTML Elements, we're generating Excel Tables, Headers, Columns, Rows and Cells.

Below is the generated Open XML File that can be opened, viewed and even modified by Excel.

<?xml version="1.0" encoding="utf-8"?>

<?mso-application progid="Excel.Sheet"?>

<Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet">

  <DocumentProperties xmlns="urn:schemas-microsoft-com:office:office">

    <Author>Silverlight</Author>

    <LastAuthor>Silverlight</LastAuthor>

    <Created>9/15/2009 1:02:04 PM</Created>

    <LastSaved>9/15/2009 1:02:04 PM</LastSaved>

    <Company>Your Company Name</Company>

    <Version>12.00</Version>

  </DocumentProperties>

  <ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel">

    <WindowHeight>12015</WindowHeight>

    <WindowWidth>20055</WindowWidth>

    <WindowTopX>480</WindowTopX>

    <WindowTopY>150</WindowTopY>

    <ProtectStructure>False</ProtectStructure>

    <ProtectWindows>False</ProtectWindows>

  </ExcelWorkbook>

  <Styles>

    <Style ss:ID="Default" ss:Name="Normal" xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet">

      <Alignment ss:Horizontal="Left" ss:Vertical="Center" />

      <Borders />

      <Font ss:FontName="Arial" ss:Size="10" ss:Color="#000000" />

      <Interior ss:Color="#FFFFFF" ss:Pattern="Solid" />

      <NumberFormat />

      <Protection />

    </Style>

    <Style ss:ID="HeaderStyle" xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet">

      <Alignment ss:Horizontal="Center" ss:Vertical="Center" ss:WrapText="1" />

      <Borders>

        <Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1" />

        <Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1" />

        <Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1" />

        <Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1" />

      </Borders>

      <Font ss:FontName="Arial" ss:Size="10" ss:Color="#FFFFFF" ss:Bold="1" />

      <Interior ss:Color="#000000" ss:Pattern="Solid" />

      <NumberFormat />

      <Protection />

    </Style>

    <Style ss:ID="ItemStyle" xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet">

      <Alignment ss:Horizontal="Left" ss:Vertical="Center" ss:WrapText="1" />

      <Borders>

        <Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1" />

        <Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1" />

        <Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1" />

        <Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1" />

      </Borders>

      <Font ss:FontName="Arial" ss:Size="10" ss:Color="#000000" />

      <Interior ss:Color="#FFFFFF" ss:Pattern="Solid" />

      <NumberFormat />

      <Protection />

    </Style>

  </Styles>

  <Worksheet ss:Name="Data Export" xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet">

    <Table ss:ExpandedColumnCount="5" ss:ExpandedRowCount="51" ss:FullColumns="5" ss:FullRows="51" ss:DefaultRowHeight="15">

      <Column ss:AutoFitWidth="0" ss:Width="40" />

      <Column ss:AutoFitWidth="0" ss:Width="100" />

      <Column ss:AutoFitWidth="0" ss:Width="100" />

      <Column ss:AutoFitWidth="0" ss:Width="200" />

      <Column ss:AutoFitWidth="0" ss:Width="50" />

      <Row ss:Index="1" ss:AutoFitHeight="0" ss:Height="24">

        <Cell ss:Index="1" ss:StyleID="HeaderStyle" ss:MergeAcross="0">

          <Data ss:Type="String">ID</Data>

        </Cell>

        <Cell ss:Index="2" ss:StyleID="HeaderStyle" ss:MergeAcross="0">

          <Data ss:Type="String">Part Number</Data>

        </Cell>

        <Cell ss:Index="3" ss:StyleID="HeaderStyle" ss:MergeAcross="0">

          <Data ss:Type="String">Part Name</Data>

        </Cell>

        <Cell ss:Index="4" ss:StyleID="HeaderStyle" ss:MergeAcross="0">

          <Data ss:Type="String">Made</Data>

        </Cell>

        <Cell ss:Index="5" ss:StyleID="HeaderStyle" ss:MergeAcross="0">

          <Data ss:Type="String">Quantity</Data>

        </Cell>

      </Row>

      <Row ss:Index="2" ss:AutoFitHeight="0" ss:Height="24">

        <Cell ss:Index="1" ss:StyleID="ItemStyle" ss:MergeAcross="0">

          <Data ss:Type="Number">1</Data>

        </Cell>

        <Cell ss:Index="2" ss:StyleID="ItemStyle" ss:MergeAcross="0">

          <Data ss:Type="String">F9K-1</Data>

        </Cell>

        <Cell ss:Index="3" ss:StyleID="ItemStyle" ss:MergeAcross="0">

          <Data ss:Type="String">Part 1</Data>

        </Cell>

        <Cell ss:Index="4" ss:StyleID="ItemStyle" ss:MergeAcross="0">

          <Data ss:Type="String">9/16/2009 1:03:05 PM</Data>

        </Cell>

        <Cell ss:Index="5" ss:StyleID="ItemStyle" ss:MergeAcross="0">

          <Data ss:Type="Number">101</Data>

        </Cell>

      </Row>

      <Row ss:Index="3" ss:AutoFitHeight="0" ss:Height="24">

        <Cell ss:Index="1" ss:StyleID="ItemStyle" ss:MergeAcross="0">

          <Data ss:Type="Number">2</Data>

        </Cell>

        <Cell ss:Index="2" ss:StyleID="ItemStyle" ss:MergeAcross="0">

          <Data ss:Type="String">F9K-2</Data>

        </Cell>

        <Cell ss:Index="3" ss:StyleID="ItemStyle" ss:MergeAcross="0">

          <Data ss:Type="String">Part 2</Data>

        </Cell>

        <Cell ss:Index="4" ss:StyleID="ItemStyle" ss:MergeAcross="0">

          <Data ss:Type="String">9/17/2009 1:04:06 PM</Data>

        </Cell>

        <Cell ss:Index="5" ss:StyleID="ItemStyle" ss:MergeAcross="0">

          <Data ss:Type="Number">102</Data>

        </Cell>

      </Row>

      <Row ss:Index="4" ss:AutoFitHeight="0" ss:Height="24">

        <Cell ss:Index="1" ss:StyleID="ItemStyle" ss:MergeAcross="0">

          <Data ss:Type="Number">3</Data>

        </Cell>

        <Cell ss:Index="2" ss:StyleID="ItemStyle" ss:MergeAcross="0">

          <Data ss:Type="String">F9K-3</Data>

        </Cell>

        <Cell ss:Index="3" ss:StyleID="ItemStyle" ss:MergeAcross="0">

          <Data ss:Type="String">Part 3</Data>

        </Cell>

        <Cell ss:Index="4" ss:StyleID="ItemStyle" ss:MergeAcross="0">

          <Data ss:Type="String">9/18/2009 1:05:07 PM</Data>

        </Cell>

        <Cell ss:Index="5" ss:StyleID="ItemStyle" ss:MergeAcross="0">

          <Data ss:Type="Number">103</Data>

        </Cell>

      </Row>

      <Row ss:Index="5" ss:AutoFitHeight="0" ss:Height="24">

        <Cell ss:Index="1" ss:StyleID="ItemStyle" ss:MergeAcross="0">

          <Data ss:Type="Number">4</Data>

        </Cell>

        <Cell ss:Index="2" ss:StyleID="ItemStyle" ss:MergeAcross="0">

          <Data ss:Type="String">F9K-4</Data>

        </Cell>

        <Cell ss:Index="3" ss:StyleID="ItemStyle" ss:MergeAcross="0">

          <Data ss:Type="String">Part 4</Data>

        </Cell>

        <Cell ss:Index="4" ss:StyleID="ItemStyle" ss:MergeAcross="0">

          <Data ss:Type="String">9/19/2009 1:06:08 PM</Data>

        </Cell>

        <Cell ss:Index="5" ss:StyleID="ItemStyle" ss:MergeAcross="0">

          <Data ss:Type="Number">104</Data>

        </Cell>

      </Row>

    </Table>

  </Worksheet>

</Workbook>

In the future I plan to add additional features such as charts and graphs. In the meantime, I hope these ideas save other developers time.

Source Code: ExcelSilverlightDemo_10-14-2009.zip (1.77 mb)

Creating Excel Worksheet From SilverlightHow to export data from Silverlight to Excel without making trips back to the server.Printer Friendly Silverlight Datagrid ControlA printer friendly Silverlight Datagrid Control.Centering the Header Text in the Silverlight 2 DatagridHow to change the horizontal-alignment of the header text inside the Silverlight 2 Datagrid.

Comments

Pingback from topsy.com

Twitter Trackbacks for

        rshelby.com | Exporting Data From Silverilght Datagrid To Excel
        [rshelby.com]
        on Topsy.com

Very cool! I did something similar but I generated the Excel file server-side. Never thought of generating client-side.

Pingback from excel.panell.info

rshelby.com | Exporting Data From Silverilght Datagrid To Excel

Dear rshelby

I am trying to save the file in the form of Excel(.xls) file for which i am changing the extension in dialog box

But after saving as .xls files once i open it shows me XML data instead of columns and rows so what change should i do.

Please help me.

Hi Sanket,

Unfortunately I am not getting the same problem as you. When the dialog box appears, you just need to enter the name and it will save the file as an XML spreadsheet with the extension ".xml" at the end.

When you click on the file, it should open up automatically in Excel, however I've only tested this using Office 2007, so I am not sure if Office 2003 will automatically open it up as an Excel Spreadsheet.

Hope this helps.

Pingback from stevepietrek.com

Links (9/17/2009) « Steve Pietrek – Everything SharePoint

Pingback from tune-up-pc.com

Tune Up Your PC » Post Topic » Silverlight Postings

Pingback from jasper22.wordpress.com

Silverlight Postings « Jasper Blog

I really like this solution.

I am on a macbook. Excel crashes when I try to open the XML file.

great solution, works great in VB had any luck porting it to c#, all my attempts have failed to put the data from the grid into the exported file

Hi Chris. I will work on a C# version and hopefully make it available later this week. Thanks.

Hi Ryan,

Thank you very much for posting this and it works really well.

Just one thing you know you can drag the column headers and change their order, is that possible to generate the excel based on the order that the user selected?

Regards,
Justin

Hi Ryan,

Great post that will fill a big gap. I've run the demo app, but the file doesn't appear on the hard drive. Stepped through the code and it doesn't error out, it just doesn't save the file.

Any ideas where to look?

Thanks,
Steve