siverlight 原生datagrid 导出excel格式

最新推荐文章于 2023-12-27 13:30:54 发布

转载最新推荐文章于 2023-12-27 13:30:54 发布 · 991 阅读

siverlight 专栏收录该内容

18 篇文章

订阅专栏

本文介绍了一种通过拼接XML字符串的方式实现复杂表头的Excel文件导出方法。该方法利用StringBuilder来构建XML格式的Excel文件内容，并通过设置不同的样式和属性来实现复杂的表格布局。此外，还提供了导出过程中的注意事项及示例代码。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

用拼xml的方式导出excel,拼复杂表头时可以新建一张excel把表格画好然后另存为xml格式

考在代码里就可以了

//复杂表头
private static void HeaderStrSucce(int length,StringBuilder NewSB)
{
//ss:MergeAcross='3'表示占3行的内容
//ss:Index='2'从第二个格子开始这些可以建一个excel另存为xml看
string teableheader = "<Row><Cell ss:MergeDown='1'><Data ss:Type='String'>车辆自编号</Data></Cell>";
NewSB.AppendLine(teableheader); teableheader = "";
for (int i = 0; i < length; i++)
{
teableheader += "<Cell ss:MergeAcross='3'><Data ss:Type='Number'>" + (i + 1) + "</Data></Cell>";
NewSB.AppendLine(teableheader); teableheader = "";
}
NewSB.AppendLine("</Row>");

NewSB.AppendLine("<Row><Cell ss:Index='2'><Data ss:Type='String'>美</Data></Cell>");
for (int i = 0; i < length; i++)
{
if (i == 0)//第二列的第一行才要合并ss:Index='2'所以到站要单独提出来
{

NewSB.AppendLine("<Cell><Data ss:Type='String'>发车</Data></Cell>");
NewSB.AppendLine("<Cell><Data ss:Type='String'>到站</Data></Cell>");
NewSB.AppendLine("<Cell><Data ss:Type='String'>准点</Data></Cell>");
//teableheader += @"<Cell><Data ss:Type='String'>发车</Data></Cell>" +
// "<Cell><Data ss:Type='String'>到站</Data></Cell>" +
// "<Cell><Data ss:Type='String'>准点</Data></Cell>";

}
else
{
NewSB.AppendLine("<Cell><Data ss:Type='String'>江</Data></Cell>");
NewSB.AppendLine("<Cell><Data ss:Type='String'>发车</Data></Cell>");
NewSB.AppendLine("<Cell><Data ss:Type='String'>到站</Data></Cell>");
NewSB.AppendLine("<Cell><Data ss:Type='String'>准点</Data></Cell>");
// teableheader += @"<Cell><Data ss:Type='String'>江</Data></Cell>
// <Cell><Data ss:Type='String'>发车</Data></Cell>" +
// "<Cell><Data ss:Type='String'>到站</Data></Cell>" +
// "<Cell><Data ss:Type='String'>准点</Data></Cell>";
}
}
//teableheader += @"<Cell><Data ss:Type='String'>班次</Data></Cell>" +
// "<Cell><Data ss:Type='String'>里程</Data></Cell>";
NewSB.AppendLine("</Row>");
//teableheader += "</Row>";
}

public static string ExportDataToExcelXml(DataGrid _dg, string WorksheetName, string WorkTitle)
{
try
{
var Dg_Is = _dg.ItemsSource;
if (Dg_Is == null) return "";

//WorksheetNameWorksheetName = WorksheetName.Trim();
//WorkTitleWorkTitle = WorkTitle.Trim();
string SaveFileName = WorksheetName + "(" + WorkTitle + ").xls";
SaveFileDialog saveFileDialog = new SaveFileDialog();
saveFileDialog.DefaultExt = ".xls";
saveFileDialog.Filter = "Excel文件 (*.xls)|*.xls";
if (saveFileDialog.ShowDialog().Value)
{
SaveFileName = saveFileDialog.SafeFileName;
}
else
{
return "";
}
if (SaveFileName == "") return "请输入要保存的文件名";

using (var sw = saveFileDialog.OpenFile())
{
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;
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(" </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\">");
for (int i = 0; i < _dg.Columns.Count; i++)
{
if (_dg.Columns[i].Visibility == Visibility.Collapsed) continue;
NewSB.AppendLine(" <Column ss:AutoFitWidth=\"1\" ss:Width=\"" + _dg.Columns[i].ActualWidth + "\"/>");
}

//使用复杂表头时需要把最上边的这行注释掉
//NewSB.AppendLine(" <Row ss:AutoFitHeight=\"0\" ss:Height=\"30\">");
//NewSB.AppendLine(" <Cell ss:MergeAcross=\"" + (NewflexVisibleColumnsCount - 1) + "\" ss:StyleID=\"s22\">");
//NewSB.Append("<Data ss:Type=\"String\">" + WorksheetName + "(" + WorkTitle + ")");

//NewSB.Append("</Data></Cell>");
//NewSB.AppendLine(" </Row>");
//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>");

//NewSB.AppendLine(HeaderStr(11));
HeaderStrSucce(11,NewSB);//复杂表头

foreach (object data in _dg.ItemsSource)//行
{
NewSB.AppendLine(" <Row ss:AutoFitHeight=\"0\" ss:Height=\"18.75\">");
foreach (DataGridColumn col in _dg.Columns)//列
{
string strValue = "";
Binding objBinding = null;
if (col is DataGridBoundColumn)
objBinding = (col as DataGridBoundColumn).Binding;
if (col is DataGridTemplateColumn)
{
//This is a template column... let us see the underlying dependency object
DependencyObject objDO = (col as DataGridTemplateColumn).CellTemplate.LoadContent();
FrameworkElement oFE = (FrameworkElement)objDO;
FieldInfo oFI = oFE.GetType().GetField("TextProperty");
if (oFI != null)
{
if (oFI.GetValue(null) != null)
{
if (oFE.GetBindingExpression((DependencyProperty)oFI.GetValue(null)) != null)
objBinding = oFE.GetBindingExpression((DependencyProperty)oFI.GetValue(null)).ParentBinding;
}
}
}
if (objBinding != null)
{
if (objBinding.Path.Path != "")
{
PropertyInfo pi = data.GetType().GetProperty(objBinding.Path.Path);
if (pi != null)
{
if (pi.GetValue(data, null) != null)
{
strValue = pi.GetValue(data, null).ToString();
}
else { strValue = ""; }
}
}
if (objBinding.Converter != null)
{
if (strValue != "")
strValue = objBinding.Converter.Convert(strValue, typeof(string), objBinding.ConverterParameter, objBinding.ConverterCulture).ToString();
else
strValue = ""; //strValue = objBinding.Converter.Convert(data, typeof(string), objBinding.ConverterParameter, objBinding.ConverterCulture).ToString();
}
}

NewSB.AppendLine(" <Cell ss:StyleID=\"s25\"><Data ss:Type=\"String\">" + strValue + "</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());
//write data of byte[] to stream
sw.Write(strbyte, 0, strbyte.Length);
}

}
catch (Exception e)
{
return "Excel文件导出失败,请稍候再试" + e.ToString();
}
return "Excel文件导出成功";
}