//参数需要传入 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文件导出成功";
}
}