sl3中的DataGrid中的数据导出到Excel中

把DataGrid中的数据导出到Excel

本例子只支持office2007以上版本

Page.xaml界面设计，Page.xaml代码如下:

Code
<UserControl xmlns:data="clr-namespace:System.Windows.Controls;assembly=System.Windows.Controls.Data"  x:Class="SilverlightAppExportToExcel.MainPage"
    xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation" 
    xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
   
    xmlns:d="http://schemas.microsoft.com/expression/blend/2008" xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006" 
    mc:Ignorable="d">
  <Grid x:Name="LayoutRoot" Loaded="LayoutRoot_Loaded">
        <Grid.RowDefinitions>
            <RowDefinition Height="Auto"></RowDefinition>
            <RowDefinition Height="Auto"></RowDefinition>
            <RowDefinition Height="Auto"></RowDefinition>
            <RowDefinition Height="Auto"></RowDefinition>
        </Grid.RowDefinitions>
     
        <data:DataGrid  x:Name="dg">
            <data:DataGrid.Columns>
                <!--Converter={StaticResource DoubleConverter},-->
                <data:DataGridTextColumn Header="Tax ID" Binding="{Binding TaxID, Mode=OneWay}" />
                <data:DataGridTextColumn Header="价格" Binding="{Binding Price,Converter={StaticResource DoubleConverter},Mode=OneWay}" />
                <data:DataGridTemplateColumn Header="Customer Since">
                    <data:DataGridTemplateColumn.CellTemplate>
                        <DataTemplate>
                            <TextBlock Text="{Binding Path=Price}" x:Name="txtPrice"
                        VerticalAlignment="Center" HorizontalAlignment="Right" Margin="0,0,4,0" />
                        </DataTemplate>
                    </data:DataGridTemplateColumn.CellTemplate>
                </data:DataGridTemplateColumn>
                <data:DataGridTemplateColumn Header="Customer Since">
                    <data:DataGridTemplateColumn.CellTemplate>
                        <DataTemplate>
                            <StackPanel>
                                <TextBlock Text="AAA"
                        VerticalAlignment="Center" HorizontalAlignment="Right" Margin="0,0,4,0" />
                                <TextBlock Text="BBB"
                        VerticalAlignment="Center" HorizontalAlignment="Right" Margin="0,0,4,0" />
                                <TextBlock Text="CCC"
                        VerticalAlignment="Center" HorizontalAlignment="Right" Margin="0,0,4,0" />
                                <TextBlock Text="{Binding Path=Name}" VerticalAlignment="Center" HorizontalAlignment="Right" Margin="0,0,4,0" />
                                <data:DataGrid x:Name="datagrid" HeadersVisibility="None">

                                </data:DataGrid>
                            </StackPanel>
                        </DataTemplate>
                    </data:DataGridTemplateColumn.CellTemplate>
                </data:DataGridTemplateColumn>
            </data:DataGrid.Columns>
            <!--<data:DataGrid.RowDetailsTemplate>
                <DataTemplate>
                    <StackPanel x:Name="SignerPanel"  Margin="48,0,0,0">
                        <StackPanel Orientation="Horizontal">
                            <TextBlock Text="{Binding BorrowerName, Mode=OneWay}" />
                            <TextBlock Text=": Signers" />
                        </StackPanel>
                    
                        <data:DataGrid x:Name="SignerSubGrid" IsReadOnly="False" AutoGenerateColumns="False"
                        Visibility="{Binding Path=Signer, Converter={StaticResource VisibilityConverter}}" 
                        RowEditEnded="SignerSubGrid_RowEditEnded">
                            <data:DataGrid.Columns>
                                <data:DataGridTemplateColumn Header="Signer Type">
                                    <data:DataGridTemplateColumn.CellTemplate>
                                        <DataTemplate>
                                            <ComboBox x:Name="SignerTypeCbo"
                                        SelectedItem="{Binding Path=SignerType, Mode=TwoWay}"
                                        ItemsSource="{Binding Source={StaticResource SignerTypes}}"
                                        Height="23" />
                                        </DataTemplate>
                                    </data:DataGridTemplateColumn.CellTemplate>
                                </data:DataGridTemplateColumn>
                                <data:DataGridTextColumn Header="Required Signer" Binding="{Binding RequiredSigner, Mode=TwoWay}" />
                                <data:DataGridTextColumn Header="Business Affiliation" Binding="{Binding BusinessAffiliation, Mode=TwoWay}" />
                                <data:DataGridTextColumn Header="Type of Business" Binding="{Binding TypeOfBusiness, Mode=TwoWay}" />
                            </data:DataGrid.Columns>
                        </data:DataGrid>

                        <StackPanel Orientation="Horizontal" HorizontalAlignment="Right" Margin="0,12,0,12">
                            <Button x:Name="addNewSigner" Content="Add Signer" 
                        Height="23" Margin="4,0,4,0" 
                        Click="addNewSigner_Click" />
                        </StackPanel>
                       
                    </StackPanel>
                </DataTemplate>
            </data:DataGrid.RowDetailsTemplate>-->
        </data:DataGrid>
        <Button x:Name="btnExport" Content="导出到Excel"  Width="100" Height="25" Grid.Row="3"  Click="btnExport_Click"></Button>

    </Grid>
</UserControl>

 

首先建立一个简单的类 :

  public class Book
    {
        public string ID { get; set; }
        public string Name { get; set; }
        public double Price { get; set; }
    }

 

LayoutRoot_Loaded事件代码如下:

Code
private void LayoutRoot_Loaded(object sender, RoutedEventArgs e)
        {
            List<Book> list = new List<Book>();
            for (int i = 0; i < 4; i++)
            {
                Book book = new Book();
                book.ID = i.ToString();
                book.Name = i.ToString() + "Silverlight开发  kafa";
                book.Price = 123456;
                list.Add(book);
            }
            DataGridTemplateColumn textColumnMax = new DataGridTemplateColumn();
            //textColumnMax.HeaderStyle = (Style)Application.Current.Resources["PacsDataGridColumnHeaderStyle"];
            //textColumnMax.CellStyle = (Style)Application.Current.Resources["PacsDataGridCellStyle"];
            //TextBlock tx = new TextBlock();
            //Binding bind = new Binding("Price");
            //bind.Converter = new DoubleConverter();
            //tx.SetBinding(TextBlock.TextProperty, bind);
            textColumnMax.Header = "最大值";
            //textColumnMax.CellTemplate = PACSSLAPP.AppCode.BuildDataTemplate.BuildDataTemple("MaxTech", 1);
            //PACSSLAPP.View.CommonControls.DoubleConverter conver = (PACSSLAPP.View.CommonControls.DoubleConverter)Application.Current.Resources["DoubleConverter"];
            textColumnMax.CellTemplate = BuildDataTemplate.BuildDataDoubleTemple("Price", 1);
            this.dg.Columns.Add(textColumnMax);
            this.dg.ItemsSource = list;

 

导出代码如下:

Code
  private void btnExport_Click(object sender, RoutedEventArgs e)
        {
            //var context = dds.DomainContext as SuperEmployeeDomainContext;
            var context = dg.ItemsSource as List<Book>;
            var s = Application.GetResourceStream(new Uri("excelTemplate.txt", UriKind.Relative));
            var dialog = new SaveFileDialog();

            dialog.DefaultExt = "*.xml";
            dialog.Filter = "Excel Xml (*.xml)|*.xml|All files (*.*)|*.*";

            if (dialog.ShowDialog() == false) return;

            using (var sw = new StreamWriter(dialog.OpenFile()))
            {
                var sr = new StreamReader(s.Stream);
                while (!sr.EndOfStream)
                {
                    var line = sr.ReadLine();
                    if (line == "***") break;
                    sw.WriteLine(line);
                }
                sw.WriteLine("<Row>");
                sw.WriteLine("<Cell ss:StyleID=\"s76\"><Data ss:Type=\"String\">{0}</Data></Cell>", "");
                sw.WriteLine("<Cell><Data ss:Type=\"String\">{0}</Data></Cell>", "出版社");
                //sw.WriteLine("<Cell><Data ss:Type=\"String\">{0}</Data></Cell>", "作者");
                //sw.WriteLine("<Cell><Data ss:Type=\"Number\">{0}</Data></Cell>", "出版日期");
                sw.WriteLine("</Row>");
                foreach (var emp in context)
                {
                    sw.WriteLine("<Row>");
                    sw.WriteLine("<Cell><Data ss:Type=\"String\">{0}</Data></Cell>", emp.ID);
                    sw.WriteLine("<Cell><Data ss:Type=\"String\">{0}</Data></Cell>", emp.Name);
                    //sw.WriteLine("<Cell><Data ss:Type=\"String\">{0}</Data></Cell>", emp.Publishers);
                    //sw.WriteLine("<Cell><Data ss:Type=\"Number\">{0}</Data></Cell>", emp.Issues);
                    sw.WriteLine("</Row>");
                }
                while (!sr.EndOfStream)
                {
                    sw.WriteLine(sr.ReadLine());
                }
            }

 

excel模板代码:

Code
<?xml version="1.0"?>
<?mso-application progid="Excel.Sheet"?>
<Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet"
 xmlns:o="urn:schemas-microsoft-com:office:office"
 xmlns:x="urn:schemas-microsoft-com:office:excel"
 xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"
 xmlns:html="http://www.w3.org/TR/REC-html40">
 <DocumentProperties xmlns="urn:schemas-microsoft-com:office:office">
  <Author>Brad Abrams</Author>
  <LastAuthor>Brad Abrams</LastAuthor>
  <Created>2009-03-15T06:13:53Z</Created>
  <LastSaved>2009-03-15T06:15:33Z</LastSaved>
  <Company>Microsoft</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">
   <Alignment ss:Vertical="Bottom"/>
   <Borders/>
   <Font ss:FontName="Calibri" x:Family="Swiss" ss:Size="11" ss:Color="#000000"/>
   <Interior/>
   <NumberFormat/>
   <Protection/>
  </Style>
  <Style ss:ID="s62">
   <Borders>
    <Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1"/>
   </Borders>
   <Font ss:FontName="Calibri" x:Family="Swiss" ss:Size="11" ss:Color="#000000"
    ss:Bold="1"/>
  </Style>
  <Style ss:ID="s64">
   <Alignment ss:Horizontal="Center" ss:Vertical="Bottom"/>
   <Borders>
    <Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1"/>
    <Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"/>
   </Borders>
   <Font ss:FontName="Calibri" x:Family="Swiss" ss:Size="16" ss:Color="#000000"
    ss:Bold="1"/>
  </Style>
  <Style ss:ID="s65">
   <Alignment ss:Horizontal="Center" ss:Vertical="Bottom"/>
   <Borders>
    <Border ss:Position="Bottom" 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="Calibri" x:Family="Swiss" ss:Size="16" ss:Color="#000000"
    ss:Bold="1"/>
  </Style>
  <Style ss:ID="s66">
   <Borders/>
   <Font ss:FontName="Calibri" x:Family="Swiss" ss:Size="11" ss:Color="#000000"
    ss:Bold="1"/>
  </Style>
  <Style ss:ID="s68">
   <Borders>
    <Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"/>
   </Borders>
   <Font ss:FontName="Calibri" x:Family="Swiss" ss:Size="12" ss:Color="#000000"/>
  </Style>
  <Style ss:ID="s69">
   <Borders>
    <Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1"/>
    <Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"/>
   </Borders>
   <Font ss:FontName="Calibri" x:Family="Swiss" ss:Size="12" ss:Color="#000000"/>
  </Style>
  <Style ss:ID="s71">
   <Borders/>
   <Font ss:FontName="Calibri" x:Family="Swiss" ss:Size="12" ss:Color="#000000"/>
  </Style>
  <Style ss:ID="s72">
   <Borders>
    <Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1"/>
   </Borders>
   <Font ss:FontName="Calibri" x:Family="Swiss" ss:Size="12" ss:Color="#000000"/>
  </Style>
  <Style ss:ID="s74">
   <Borders>
    <Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1"/>
   </Borders>
   <Font ss:FontName="Calibri" x:Family="Swiss" ss:Size="12" ss:Color="#000000"/>
  </Style>
  <Style ss:ID="s75">
   <Borders>
    <Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1"/>
    <Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1"/>
   </Borders>
   <Font ss:FontName="Calibri" x:Family="Swiss" ss:Size="12" ss:Color="#000000"/>
  </Style>
  <Style ss:ID="s76">
   <Alignment ss:Horizontal="Center" ss:Vertical="Bottom"/>
   <Borders>
    <Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1"/>
    <Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"/>
    <Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"/>
   </Borders>
   <Font ss:FontName="Calibri" x:Family="Swiss" ss:Size="16" ss:Color="#000000"
    ss:Bold="1"/>
  </Style>
  <Style ss:ID="s77">
   <Borders>
    <Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"/>
    <Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"/>
   </Borders>
   <Font ss:FontName="Calibri" x:Family="Swiss" ss:Size="12" ss:Color="#000000"/>
  </Style>
  <Style ss:ID="s78">
   <Borders>
    <Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"/>
   </Borders>
   <Font ss:FontName="Calibri" x:Family="Swiss" ss:Size="12" ss:Color="#000000"/>
  </Style>
  <Style ss:ID="s79">
   <Borders>
    <Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1"/>
    <Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"/>
   </Borders>
   <Font ss:FontName="Calibri" x:Family="Swiss" ss:Size="12" ss:Color="#000000"/>
  </Style>
 </Styles>
 <Worksheet ss:Name="Sheet1">
  <Table ss:ExpandedColumnCount="19" ss:ExpandedRowCount="27" x:FullColumns="1"
   x:FullRows="1" ss:DefaultRowHeight="15">
   <Column ss:AutoFitWidth="0" ss:Width="113.25"/>
   <Column ss:AutoFitWidth="0" ss:Width="65.25"/>
   <Column ss:AutoFitWidth="0" ss:Width="93"/>
  
***

  </Table>
  <WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
   <PageSetup>
    <Header x:Margin="0.3"/>
    <Footer x:Margin="0.3"/>
    <PageMargins x:Bottom="0.75" x:Left="0.7" x:Right="0.7" x:Top="0.75"/>
   </PageSetup>
   <Unsynced/>
   <Print>
    <ValidPrinterInfo/>
    <HorizontalResolution>300</HorizontalResolution>
    <VerticalResolution>300</VerticalResolution>
   </Print>
   <Selected/>
   <Panes>
    <Pane>
     <Number>3</Number>
     <ActiveRow>26</ActiveRow>
    </Pane>
   </Panes>
   <ProtectObjects>False</ProtectObjects>
   <ProtectScenarios>False</ProtectScenarios>
  </WorksheetOptions>
 </Worksheet>
 <Worksheet ss:Name="Sheet2">
  <Table ss:ExpandedColumnCount="1" ss:ExpandedRowCount="1" x:FullColumns="1"
   x:FullRows="1" ss:DefaultRowHeight="15">
   <Row ss:AutoFitHeight="0"/>
  </Table>
  <WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
   <PageSetup>
    <Header x:Margin="0.3"/>
    <Footer x:Margin="0.3"/>
    <PageMargins x:Bottom="0.75" x:Left="0.7" x:Right="0.7" x:Top="0.75"/>
   </PageSetup>
   <Unsynced/>
   <ProtectObjects>False</ProtectObjects>
   <ProtectScenarios>False</ProtectScenarios>
  </WorksheetOptions>
 </Worksheet>
 <Worksheet ss:Name="Sheet3">
  <Table ss:ExpandedColumnCount="1" ss:ExpandedRowCount="1" x:FullColumns="1"
   x:FullRows="1" ss:DefaultRowHeight="15">
   <Row ss:AutoFitHeight="0"/>
  </Table>
  <WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
   <PageSetup>
    <Header x:Margin="0.3"/>
    <Footer x:Margin="0.3"/>
    <PageMargins x:Bottom="0.75" x:Left="0.7" x:Right="0.7" x:Top="0.75"/>
   </PageSetup>
   <Unsynced/>
   <ProtectObjects>False</ProtectObjects>
   <ProtectScenarios>False</ProtectScenarios>
  </WorksheetOptions>
 </Worksheet>
</Workbook>