Silverlight DataGrid导出Excel（原生表头与复杂表头）

 //参数需要传入 DataGrid,Excel里的小标题,小标题

public static string GridDataToExcel(DataGrid _dg, string WorksheetName, string WorkTitle)
        {

         // 这是原生表头的写法，如果是复杂表头 可以在Excel中编辑好表头 然后保存为xml格式  放入网页中 就可看到复杂表头的代码  然后...
            try
            {
                //得到datagrid的值，没有这return null;
                var Dg_Is = _dg.ItemsSource;
                if (Dg_Is == null) return "";
                string SaveFileName = WorksheetName + "(" + WorkTitle + ").xls";//文件名称
                //实例化文件保存位置
                SaveFileDialog saveFileDialog = new SaveFileDialog();

                //以文件“*.xls”导出
                saveFileDialog.DefaultExt = ".xls";//默认保存文件的扩展名称
                saveFileDialog.Filter = "Excel文件 (*.xls)|*.xls"; //文件的多种格式设置
                if (saveFileDialog.ShowDialog().Value) //方法只能从用户启动的代码，如果是用户启动，则返回true
                    SaveFileName = saveFileDialog.SafeFileName;//添加保存文件的文件名
                else
                    return "";

                if (SaveFileName == "") return "请输入要保存的文件名";//文件名无输入；返回提示

                //using 定义一个范围，将在此范围之外释放一个或多个对象。
                using (var sw = saveFileDialog.OpenFile()) //sw :参数指定的文件的读写流
                {
                    int NewflexVisibleColumnsCount = 0;
                    for (int i = 0; i < _dg.Columns.Count; i++)  //循环列
                    {
                        if (_dg.Columns[i].Visibility == Visibility.Collapsed) continue;
                        NewflexVisibleColumnsCount++;
                    }
                    StringBuilder NewSB = new StringBuilder();//初始化/构建/分配单元
                    string NewSaveFileName = SaveFileName;//构建单元的名称
                    int PageSizeNum = (Dg_Is as System.Collections.IList).Count;//datagrid数据总数
                    string FirstHeaderText = "(uuzo)";

                    NewSB = new StringBuilder();
                    NewSB.AppendLine("<?xml version=\"1.0\"?>");
                    NewSB.AppendLine("<?mso-application progid=\"Excel.Sheet\"?>");
                    NewSB.AppendLine("<Workbook xmlns=\"urn:schemas-microsoft-com:office:spreadsheet\"");
                    NewSB.AppendLine(" xmlns:o=\"urn:schemas-microsoft-com:office:office\"");
                    NewSB.AppendLine(" xmlns:x=\"urn:schemas-microsoft-com:office:excel\"");
                    NewSB.AppendLine(" xmlns:ss=\"urn:schemas-microsoft-com:office:spreadsheet\"");
                    NewSB.AppendLine(" xmlns:html=\"http://www.w3.org/TR/REC-html40\">");
                    NewSB.AppendLine(" <DocumentProperties xmlns=\"urn:schemas-microsoft-com:office:office\">");
                    NewSB.AppendLine("  <Author>" + "me" + "</Author>");
                    NewSB.AppendLine("  <LastAuthor>" + "me" + "</LastAuthor>");
                    NewSB.AppendLine("  <Created>" + DateTime.Now.ToString("yyyy-MM-ddTHH:mm:ss") + "</Created>");
                    NewSB.AppendLine("  <LastSaved>" + DateTime.Now.ToString("yyyy-MM-ddTHH:mm:ss") + "</LastSaved>");
                    NewSB.AppendLine("  <Company>Microsoft</Company>");
                    NewSB.AppendLine("  <Version>11.9999</Version>");
                    NewSB.AppendLine(" </DocumentProperties>");
                    NewSB.AppendLine(" <ExcelWorkbook xmlns=\"urn:schemas-microsoft-com:office:excel\">");
                    NewSB.AppendLine("  <WindowHeight>9675</WindowHeight>");
                    NewSB.AppendLine("  <WindowWidth>16020</WindowWidth>");
                    NewSB.AppendLine("  <WindowTopX>0</WindowTopX>");
                    NewSB.AppendLine("  <WindowTopY>120</WindowTopY>");
                    NewSB.AppendLine("  <ProtectStructure>False</ProtectStructure>");
                    NewSB.AppendLine("  <ProtectWindows>False</ProtectWindows>");
                    NewSB.AppendLine(" </ExcelWorkbook>");

                    NewSB.AppendLine("<Styles>");
                    NewSB.AppendLine("  <Style ss:ID=\"Default\" ss:Name=\"Normal\">");
                    NewSB.AppendLine("   <Alignment ss:Vertical=\"Center\"/>");
                    NewSB.AppendLine("   <Borders/>");
                    NewSB.AppendLine("   <Font ss:FontName=\"宋体\" x:CharSet=\"134\" ss:Size=\"12\"/>");
                    NewSB.AppendLine("   <Interior/>");
                    NewSB.AppendLine("   <NumberFormat/>");
                    NewSB.AppendLine("   <Protection/>");
                    NewSB.AppendLine("  </Style>");
                    NewSB.AppendLine("  <Style ss:ID=\"s22\">");
                    NewSB.AppendLine("   <Alignment ss:Horizontal=\"Center\" ss:Vertical=\"Center\"/>");
                    NewSB.AppendLine("   <Borders>");
                    NewSB.AppendLine("    <Border ss:Position=\"Bottom\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>");
                    NewSB.AppendLine("   </Borders>");
                    NewSB.AppendLine("   <Font ss:FontName=\"宋体\" x:CharSet=\"134\" ss:Size=\"12\" ss:Bold=\"1\"/>");
                    NewSB.AppendLine("  </Style>");
                    NewSB.AppendLine("  <Style ss:ID=\"s24\">");
                    NewSB.AppendLine("   <Alignment ss:Horizontal=\"Left\" ss:Vertical=\"Center\"/>");
                    NewSB.AppendLine("   <Borders>");
                    NewSB.AppendLine("    <Border ss:Position=\"Bottom\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>");
                    NewSB.AppendLine("    <Border ss:Position=\"Left\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>");
                    NewSB.AppendLine("    <Border ss:Position=\"Right\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>");
                    NewSB.AppendLine("    <Border ss:Position=\"Top\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>");
                    NewSB.AppendLine("   </Borders>");
                    NewSB.AppendLine("   <Interior ss:Color=\"#C0C0C0\" ss:Pattern=\"Solid\"/>");
                    NewSB.AppendLine("  </Style>");
                    NewSB.AppendLine("  <Style ss:ID=\"s25\">");
                    NewSB.AppendLine("   <Alignment ss:Horizontal=\"Left\" ss:Vertical=\"Center\"/>");
                    NewSB.AppendLine("   <Borders>");
                    NewSB.AppendLine("    <Border ss:Position=\"Bottom\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>");
                    NewSB.AppendLine("    <Border ss:Position=\"Left\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>");
                    NewSB.AppendLine("    <Border ss:Position=\"Right\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>");
                    NewSB.AppendLine("    <Border ss:Position=\"Top\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>");
                    NewSB.AppendLine("   </Borders>");
                    NewSB.AppendLine("   <Font ss:FontName=\"宋体\" x:CharSet=\"134\" ss:Size=\"9\"/>");
                    NewSB.AppendLine("  </Style>");
                    NewSB.AppendLine(@"<Style ss:ID='s63'>
                            <Alignment ss:Vertical='Center'/>
                        </Style>
                        <Style ss:ID='s64'>
                        <Alignment ss:Vertical='Center'/>
                        <Font ss:FontName='宋体' x:CharSet='134' ss:Size='12'/>
                        </Style>
                        <Style ss:ID='s65'>
                        <Alignment ss:Vertical='Center' ss:VerticalText='1'/>
                        <Font ss:FontName='宋体' x:CharSet='134' ss:Size='12'/>
                        </Style>
                        <Style ss:ID='s67'>
                        <Alignment ss:Horizontal='Center' ss:Vertical='Center'/>
                        <Font ss:FontName='宋体' x:CharSet='134' ss:Size='12'/>
                        </Style>
                            <Style ss:ID='s70'>
                        <Alignment ss:Vertical='Center' ss:VerticalText='1'/>
                        <Font ss:FontName='宋体' x:CharSet='134' ss:Size='12'/>
                        </Style>
                        <Style ss:ID='s71'>
                        <Alignment ss:Vertical='Center' ss:VerticalText='1'/>
                        <Borders/>
                        <Font ss:FontName='宋体' x:CharSet='134' ss:Size='12'/>
                        <Interior/>
                        </Style>
                        <Style ss:ID='s78'>
                        <Alignment ss:Vertical='Center' ss:WrapText='1'/>
                        <Font ss:FontName='宋体' x:CharSet='134' ss:Size='12'/>
                        </Style>");
                    NewSB.AppendLine(" </Styles>");

                    NewSB.AppendLine("<Worksheet ss:Name=\"" + WorksheetName + "\">");
                    NewSB.AppendLine("  <Names>");
                    NewSB.AppendLine("   <NamedRange ss:Name=\"_FilterDatabase\"");
                    NewSB.AppendLine("    ss:RefersTo=\"='" + WorksheetName + "'!R2C1:R" + (PageSizeNum + 2) + "C" + NewflexVisibleColumnsCount + "\" ss:Hidden=\"1\"/>");
                    NewSB.AppendLine("  </Names>");
                    NewSB.AppendLine("  <Table ss:ExpandedColumnCount=\"" + NewflexVisibleColumnsCount + "\" ss:ExpandedRowCount=\"" + (PageSizeNum + 2) +
 "\" x:FullColumns=\"1\"");
                    NewSB.AppendLine("   x:FullRows=\"1\" ss:DefaultColumnWidth=\"60\" ss:DefaultRowHeight=\"14.25\">");

                    NewSB.AppendLine("   <Row ss:AutoFitHeight=\"0\" ss:Height=\"22.5\">");
                    for (int i = 0; i < _dg.Columns.Count; i++) // 表头
                    {
                        if (_dg.Columns[i].Visibility == Visibility.Collapsed) continue;
                        if (FirstHeaderText == "(uuzo)") FirstHeaderText = _dg.Columns[i].Header.ToString();
                        NewSB.AppendLine("    <Cell ss:StyleID=\"s24\"><Data ss:Type=\"String\">" + _dg.Columns[i].Header.ToString() + "</Data><NamedCell"); //表头
                        NewSB.AppendLine("      ss:Name=\"_FilterDatabase\"/></Cell>");
                    }
                    NewSB.AppendLine("   </Row>");

                      
                    foreach (object data in _dg.ItemsSource)//行
                    {
                        NewSB.AppendLine("   <Row ss:AutoFitHeight=\"0\" ss:Height=\"18.75\">");

                        foreach (PropertyInfo pi in data.GetType().GetProperties())//col
                        {
                            string pval = (string)pi.GetValue(data, null);
                            NewSB.AppendLine("    <Cell ss:StyleID=\"s25\"><Data ss:Type=\"String\"><![CDATA[" + pval + "]]></Data><NamedCell");
                            NewSB.AppendLine("      ss:Name=\"_FilterDatabase\"/></Cell>");
                        }
                        NewSB.AppendLine("   </Row>");
                    }
                    NewSB.AppendLine("  </Table>");
                    NewSB.AppendLine("  <WorksheetOptions xmlns=\"urn:schemas-microsoft-com:office:excel\">");
                    NewSB.AppendLine("   <Unsynced/>");
                    NewSB.AppendLine("   <Print>");
                    NewSB.AppendLine("    <ValidPrinterInfo/>");
                    NewSB.AppendLine("    <PaperSizeIndex>9</PaperSizeIndex>");
                    NewSB.AppendLine("    <HorizontalResolution>200</HorizontalResolution>");
                    NewSB.AppendLine("    <VerticalResolution>200</VerticalResolution>");
                    NewSB.AppendLine("   </Print>");
                    NewSB.AppendLine("   <Selected/>");
                    NewSB.AppendLine("   <ProtectObjects>False</ProtectObjects>");
                    NewSB.AppendLine("   <ProtectScenarios>False</ProtectScenarios>");
                    NewSB.AppendLine("  </WorksheetOptions>");
                    //注释不用的为排序
                    //NewSB.AppendLine("  <AutoFilter x:Range=\"R2C1:R" + (PageSizeNum + 2) + "C" + NewflexVisibleColumnsCount + "\" xmlns=\"urn:schemas-microsoft-com:office:excel\">");
                    NewSB.AppendLine("  </AutoFilter>");
                    NewSB.AppendLine("  <Sorting xmlns=\"urn:schemas-microsoft-com:office:excel\">");
                    NewSB.AppendLine("   <Sort>" + FirstHeaderText + "</Sort>");
                    NewSB.AppendLine("  </Sorting>");
                    NewSB.AppendLine(" </Worksheet>");
                    NewSB.AppendLine("</Workbook>");

                    byte[] strbyte = Encoding.UTF8.GetBytes(NewSB.ToString());
                    sw.Write(strbyte, 0, strbyte.Length);
                }
            }
            catch (Exception e)
            {
                return "Excel文件导出失败,请稍候再试" + e.ToString();
            }
            MessageBox.Show("Excel文件导出成功");
            return "Excel文件导出成功";
        }
    }