c#导出到XLS问题
本文关键字：XLS 问题 | 更新日期: 2023-09-27 18:05:17
我试图将数据导出到格式化的XLS，但我似乎有某种限制问题。它可以导出一些数据集，也可以不导出。
我遍历了结果XML并注释了大多数行，然后逐个取消注释，直到文档成功打开。我知道行和列的限制，但结果文档无法在Excel中打开远远之前。我说的是失败时超过34 excel行(约500格式化的XML行)。
这是我完整的导出代码:
private void GenerateConfigReport(HttpContext context)
{
GIS34PortalService client = new GIS34PortalService();
DataTable features = client.GetFeatures(objectId, "id");
string filename = "ConfigRapport";
#region header
StringBuilder header = new StringBuilder();
header.AppendLine("<?xml version='"1.0'" encoding='"utf-8'"?>");
header.AppendLine("<?mso-application progid='"Excel.Sheet'"?>");
header.AppendLine("<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'">");
header.AppendLine(" <DocumentProperties xmlns='"urn:schemas-microsoft-com:office:office'">");
header.AppendLine(" <Author>Amit Bracha</Author>");
header.AppendLine(" <LastAuthor>Amit Bracha</LastAuthor>");
header.AppendLine(" <Created>" + DateTime.Now + "</Created>");
header.AppendLine(" <LastSaved>" + DateTime.Now + "</LastSaved>");
header.AppendLine(" <Company>Landinspektørfirmaet LE34 A/S</Company>");
header.AppendLine(" <Version>12.00</Version>");
header.AppendLine(" </DocumentProperties>");
header.AppendLine(" <OfficeDocumentSettings xmlns='"urn:schemas-microsoft-com:office:office'">");
header.AppendLine(" <RemovePersonalInformation/>");
header.AppendLine(" </OfficeDocumentSettings>");
header.AppendLine(" <ExcelWorkbook xmlns='"urn:schemas-microsoft-com:office:excel'">");
header.AppendLine(" <WindowHeight>7875</WindowHeight>");
header.AppendLine(" <WindowWidth>14895</WindowWidth>");
header.AppendLine(" <WindowTopX>360</WindowTopX>");
header.AppendLine(" <WindowTopY>300</WindowTopY>");
header.AppendLine(" <ProtectStructure>False</ProtectStructure>");
header.AppendLine(" <ProtectWindows>False</ProtectWindows>");
header.AppendLine(" </ExcelWorkbook>");
#endregion
#region styles
StringBuilder styles = new StringBuilder();
styles.AppendLine(" <Styles>");
...
styles.AppendLine(" </Styles>");
region
#region title
StringBuilder title = new StringBuilder();
title.AppendLine(" <Worksheet ss:Name='"Ark1'">");
title.AppendLine(" <Names>");
title.AppendLine(" <NamedRange ss:Name='"GIS34_Skade_V1_alle_koder'" ss:RefersTo='"=Ark1!R5C2:R28C2'"/>");
title.AppendLine(" </Names>");
title.AppendLine(" <Table ss:ExpandedColumnCount='"16'" ss:ExpandedRowCount='"34'" x:FullColumns='"1'" x:FullRows='"1'" ss:StyleID='"s16'" ss:DefaultRowHeight='"15'">");
title.AppendLine(" <Column ss:StyleID='"s16'" ss:AutoFitWidth='"0'" ss:Width='"30'"/>");
title.AppendLine(" <Column ss:StyleID='"s16'" ss:AutoFitWidth='"0'" ss:Width='"135'" ss:Span='"2'"/>");
title.AppendLine(" <Column ss:Index='"5'" ss:StyleID='"s19'" ss:AutoFitWidth='"0'" ss:Width='"30'"/>");
title.AppendLine(" <Column ss:StyleID='"s16'" ss:AutoFitWidth='"0'" ss:Width='"135'"/>");
title.AppendLine(" <Column ss:StyleID='"s19'" ss:AutoFitWidth='"0'" ss:Width='"30'"/>");
title.AppendLine(" <Column ss:StyleID='"s16'" ss:AutoFitWidth='"0'" ss:Width='"135'"/>");
title.AppendLine(" <Column ss:StyleID='"s19'" ss:AutoFitWidth='"0'" ss:Span='"3'"/>");
title.AppendLine(" <Row ss:AutoFitHeight='"0'" ss:Height='"30'" ss:StyleID='"s18'">");
title.AppendLine(" <Cell ss:StyleID='"s33'"/>");
title.AppendLine(" <Cell ss:StyleID='"s126'"><Data ss:Type='"String'">LE34</Data></Cell>");
title.AppendLine(" <Cell ss:StyleID='"s87'"><Data ss:Type='"String'">Kunde:</Data></Cell>");
title.AppendLine(" <Cell ss:StyleID='"s32'"><Data ss:Type='"String'">Assens Kommune</Data></Cell>");
title.AppendLine(" </Row>");
title.AppendLine(" <Row ss:AutoFitHeight='"0'" ss:Height='"30'" ss:StyleID='"s18'">");
title.AppendLine(" <Cell ss:StyleID='"s33'"/>");
title.AppendLine(" <Cell ss:StyleID='"s128'"><Data ss:Type='"String'">GIS34</Data></Cell>");
title.AppendLine(" <Cell ss:StyleID='"s87'"><Data ss:Type='"String'">Kodeliste:</Data></Cell>");
title.AppendLine(" <Cell ss:StyleID='"s32'"><Data ss:Type='"String'">" + objectName + "</Data></Cell>");
title.AppendLine(" </Row>");
title.AppendLine(" <Row ss:AutoFitHeight='"0'">");
title.AppendLine(" <Cell ss:StyleID='"s33'"/>");
title.AppendLine(" <Cell ss:MergeAcross='"6'" ss:StyleID='"s37'"><Data ss:Type='"String'">Udformning af lagkontrol: </Data></Cell>");
title.AppendLine(" <Cell ss:StyleID='"s16'"/>");
title.AppendLine(" <Cell ss:StyleID='"s16'"/>");
title.AppendLine(" <Cell ss:StyleID='"s16'"/>");
title.AppendLine(" <Cell ss:StyleID='"s16'"/>");
title.AppendLine(" </Row>");
title.AppendLine(" <Row ss:AutoFitHeight='"0'">");
title.AppendLine(" <Cell ss:StyleID='"s33'"/>");
title.AppendLine(" <Cell ss:MergeAcross='"6'" ss:StyleID='"s37'"><Data ss:Type='"String'">Lagkontrollen kan udformes så den give en meningsfuld oversigt over opgaverne.</Data></Cell>");
title.AppendLine(" <Cell ss:StyleID='"s16'"/>");
title.AppendLine(" <Cell ss:StyleID='"s16'"/>");
title.AppendLine(" <Cell ss:StyleID='"s16'"/>");
title.AppendLine(" <Cell ss:StyleID='"s16'"/>");
title.AppendLine(" </Row>");
title.AppendLine(" <Row ss:AutoFitHeight='"0'">");
title.AppendLine(" <Cell ss:StyleID='"s33'"/>");
title.AppendLine(" <Cell ss:MergeAcross='"6'" ss:StyleID='"s37'"><Data ss:Type='"String'">Der defineres de lag som matcher de teams som skal løse opgaverne.</Data><NamedCell ss:Name='"GIS34_Skade_V1_alle_koder'"/></Cell>");
title.AppendLine(" <Cell ss:StyleID='"s16'"/>");
title.AppendLine(" <Cell ss:StyleID='"s16'"/>");
title.AppendLine(" <Cell ss:StyleID='"s16'"/>");
title.AppendLine(" <Cell ss:StyleID='"s16'"/>");
title.AppendLine(" </Row>");
title.AppendLine(" <Row ss:AutoFitHeight='"0'">");
title.AppendLine(" <Cell ss:StyleID='"s33'"/>");
title.AppendLine(" <Cell ss:Index='"5'" ss:StyleID='"s16'"/>");
title.AppendLine(" <Cell ss:Index='"7'" ss:StyleID='"s16'"/>");
title.AppendLine(" <Cell ss:Index='"9'" ss:StyleID='"s16'"/>");
title.AppendLine(" <Cell ss:StyleID='"s16'"/>");
title.AppendLine(" <Cell ss:StyleID='"s16'"/>");
title.AppendLine(" <Cell ss:StyleID='"s16'"/>");
title.AppendLine(" </Row>");
title.AppendLine(" <Row ss:AutoFitHeight='"0'" ss:Height='"20.0625'" ss:StyleID='"s17'">");
title.AppendLine(" <Cell ss:StyleID='"s33'"/>");
title.AppendLine(" <Cell ss:StyleID='"s24'"><Data ss:Type='"String'">Features</Data><NamedCell ss:Name='"GIS34_Skade_V1_alle_koder'"/></Cell>");
title.AppendLine(" <Cell ss:StyleID='"s25'"><Data ss:Type='"String'">Attributter</Data></Cell>");
title.AppendLine(" <Cell ss:StyleID='"s26'"><Data ss:Type='"String'">Menu items</Data></Cell>");
title.AppendLine(" <Cell ss:Index='"6'" ss:StyleID='"s27'"><Data ss:Type='"String'">Lagkontrol</Data></Cell>");
title.AppendLine(" <Cell ss:Index='"8'" ss:StyleID='"s28'"><Data ss:Type='"String'">WebGIS</Data></Cell>");
title.AppendLine(" </Row>");
title.AppendLine(" <Row ss:AutoFitHeight='"0'" ss:StyleID='"s17'"/>");
#endregion
#region content
StringBuilder content = new StringBuilder();
foreach (DataRow feature in features.Rows)
{
content.AppendLine(" <Row ss:AutoFitHeight='"0'">");
content.AppendLine(" <Cell ss:Index='"2'" ss:MergeAcross='"2'" ss:StyleID='"s36'"><Data ss:Type='"String'">" + (feature["name"].ToString() + " (" + feature["geometry_type"].ToString().ToLower() + ")") + "</Data></Cell>");
content.AppendLine(" <Cell ss:StyleID='"s16'"/>");
content.AppendLine(" <Cell ss:StyleID='"s21'"><Data ss:Type='"String'"></Data></Cell>");
content.AppendLine(" <Cell ss:StyleID='"s16'"/>");
content.AppendLine(" <Cell ss:StyleID='"s22'"><Data ss:Type='"String'"></Data></Cell>");
content.AppendLine(" </Row>");
DataTable attributtes = client.GetAttributes(feature["id"].ToString(), "id");
foreach (DataRow attributte in attributtes.Rows)
{
content.AppendLine(" <Row ss:AutoFitHeight='"0'">");
content.AppendLine(" <Cell ss:Index='"2'" ss:StyleID='"s29'"></Cell>");
content.AppendLine(" <Cell ss:StyleID='"s30'"><Data ss:Type='"String'">" + (attributte["name"].ToString() + " (" + attributte["type"].ToString().ToLower() + ")") + "</Data></Cell>");
content.AppendLine(" <Cell ss:StyleID='"s30'"/>");
content.AppendLine(" <Cell ss:StyleID='"s16'"/>");
content.AppendLine(" <Cell ss:StyleID='"s20'"/>");
content.AppendLine(" <Cell ss:StyleID='"s16'"/>");
content.AppendLine(" <Cell ss:StyleID='"s20'"/>");
content.AppendLine(" </Row>");
if (attributte["type"].ToString().ToLower() == "menu")
{
DataTable menuItems = client.GetAttributeInfo(attributte["id"].ToString(), attributte["type"].ToString(), "id");
foreach (DataRow menuItem in menuItems.Rows)
{
content.AppendLine(" <Row ss:AutoFitHeight='"0'">");
content.AppendLine(" <Cell ss:Index='"2'" ss:StyleID='"s29'"></Cell>");
content.AppendLine(" <Cell ss:StyleID='"s30'"/>");
content.AppendLine(" <Cell ss:StyleID='"s30'"><Data ss:Type='"String'">" + (menuItem["name"].ToString() + (menuItem["value"].ToString().ToLower().Contains("true") ? " (default)" : "")) + "</Data></Cell>");
content.AppendLine(" <Cell ss:StyleID='"s16'"/>");
content.AppendLine(" <Cell ss:StyleID='"s20'"/>");
content.AppendLine(" <Cell ss:StyleID='"s16'"/>");
content.AppendLine(" <Cell ss:StyleID='"s20'"/>");
content.AppendLine(" </Row>");
}
}
}
}
content.AppendLine(" </Table>");
#endregion
#region footer
StringBuilder footer = new StringBuilder();
footer.AppendLine(" <WorksheetOptions xmlns='"urn:schemas-microsoft-com:office:excel'">");
footer.AppendLine(" <PageSetup>");
footer.AppendLine(" <Header x:Margin='"0.3'"/>");
footer.AppendLine(" <Footer x:Margin='"0.3'"/>");
footer.AppendLine(" </PageSetup>");
footer.AppendLine(" <Selected/>");
footer.AppendLine(" <FreezePanes/>");
footer.AppendLine(" <FrozenNoSplit/>");
footer.AppendLine(" <SplitHorizontal>7</SplitHorizontal>");
footer.AppendLine(" <TopRowBottomPane>7</TopRowBottomPane>");
footer.AppendLine(" <ProtectObjects>False</ProtectObjects>");
footer.AppendLine(" <ProtectScenarios>False</ProtectScenarios>");
footer.AppendLine(" </WorksheetOptions>");
footer.AppendLine(" </Worksheet>");
footer.AppendLine("</Workbook>");
#endregion
context.Response.ContentType = "application/excel";
context.Response.AddHeader("Content-disposition", "attachment; filename='"report.xls'"");
string excel = header.ToString() + styles.ToString() + title.ToString() + content.ToString() + footer.ToString();
HttpContext.Current.Response.BinaryWrite(UTF8Encoding.UTF8.GetBytes(excel));
}
下面是结果XML: http://www.gis34.dk/docs/report.xls
有人知道为什么Excel打不开这个文档吗?
ExpandedRowCount是你的问题。显然，它与实际的行数不匹配。
我把它取下来，它就打开了。
更好的选择是输出实际的行数。
我不认为这是一个限制，因为我们知道Excel并不局限于这么少的行数。这几乎肯定是一个数据/转义特殊字符的问题…你用AppendLine添加行的方式是不安全的…数据中没有'<'或其他无效的XML字符吗?
我建议你使用合适的XLS组件，这样所有这些问题都会得到解决。有很多免费的、开源的和商业的服务。
看看这个:http://nugetmusthaves.com/Tag/Excel