如何从被剥去出口从一个DataTable到Excel时停止0领先的？ [英] How to stop the leading 0's from being stripped off when exporting to excel from a datatable?
问题描述
我遇到了同样的问题，在这个问题：
<一个href=\"http://stackoverflow.com/questions/22879/how-do-you-$p$pvent-leading-zeros-from-being-stripped-when-importing-an-excel-doc\">How你prevent使用导入Excel文档时，被剥离的前导零的C＃
但我不知道这是我的方案的最佳解决方案。这里是code，我用做出口。有谁知道我可以从被剥去前导0更改为prevent？
私有静态无效Export_with_XSLT_Web（DataSet中dsExport，
字符串[] sHeaders，
字符串[] sFileds，
ExportFormat FormatType，
字符串的文件名）
{ HttpContext.Current.Response.Clear（）;
HttpContext.Current.Response.Buffer = TRUE; HttpContext.Current.Response.ContentType =应用程序/ vnd.ms-EXCEL;
HttpContext.Current.Response.AppendHeader（内容处置，
附件;
文件名=+文件名）;
} // XSLT用于把这种数据集。
MemoryStream的流=新的MemoryStream（）;
XmlTextWriter的作家=新的XmlTextWriter（流Encoding.UTF8）; CreateStylesheet（作家，sHeaders，sFileds，FormatType）;
writer.Flush（）;
stream.Seek（0，SeekOrigin.Begin）; XmlDataDocument的xmlDoc中=新的XmlDataDocument（dsExport）;
的XslTransform xslTran =新的XslTransform（）;
xslTran.Load（新XmlTextReader的（流），NULL，NULL）; 使用（StringWriter的SW =新的StringWriter（））
{
xslTran.Transform（xmlDoc中，空，申银万国，NULL）; HttpContext.Current.Response.Write（sw.ToString（））;
writer.Close（）;
stream.Close（）;
HttpContext.Current.Response.End（）;
}
}
}
下面是创建样式表，有什么在这里，我可以改变一些或所有领域带来文本的方法。
私有静态无效CreateStylesheet（XmlTextWriter的作家，
字符串[] sHeaders，
字符串[] sFileds，
ExportFormat FormatType）
{
尝试
{
// XSL：样式表
字符串NS =http://www.w3.org/1999/XSL/Transform;
writer.Formatting = Formatting.Indented;
writer.WriteStartDocument（）;
writer.WriteStartElement（XSL，样式，NS）;
writer.WriteAttributeString（版本，1.0）;
writer.WriteStartElement（XSL：输出）;
writer.WriteAttributeString（法，文字）;
writer.WriteAttributeString（版本，4.0）;
writer.WriteEndElement（）; // XSL模板
writer.WriteStartElement（XSL：模板）;
writer.WriteAttributeString（匹配，/）; // XSL：价值为头
的for（int i = 0; I＆LT; sHeaders.Length;我++）
{
writer.WriteString（\\）;
writer.WriteStartElement（XSL： - 值）;
writer.WriteAttributeString（选择，'+ sHeaders [I] +'）;
writer.WriteEndElement（）; // XSL：value-of的
writer.WriteString（\\）;
} //的xsl：for-每个
writer.WriteStartElement（的xsl：for-每个）;
writer.WriteAttributeString（选择，导出/值）;
writer.WriteString（\\ r \\ n）; // XSL：value-of的数据字段
的for（int i = 0; I＆LT; sFileds.Length;我++）
{
writer.WriteString（\\）;
writer.WriteStartElement（XSL： - 值）;
writer.WriteAttributeString（选择，sFileds [I]）;
writer.WriteEndElement（）; // XSL：value-of的
writer.WriteString（\\）;
} writer.WriteEndElement（）; //的xsl：for-每个
writer.WriteEndElement（）; // XSL模板
writer.WriteEndElement（）; // XSL：样式表
writer.WriteEndDocument（）;
}
赶上（异常前）
{
扔防爆;
}
}
我不知道你的XSL转换的输出：我会假设它为Excel的XML格式。
试图扭转我一个Excel工作表中写道三个数字（007）的过程：一次作为数字，一次作为文本，一次作为数字，但格式化显示3位补零。然后，我保存为XML，看着它。这里是片段：
＆LT;行＆GT;
＆所述;电池＆gt;＆lt;数据分类：种类=编号大于7＆下; /数据与GT;＆下; /电池＆gt;
＆LT; /行＆GT;
＆LT;行＆GT;
＆所述;电池＆gt;＆lt;数据分类：种类=字符串×：打勾=1＆GT; 007＆下; /数据与GT;＆下; /电池＆gt;
＆LT; /行＆GT;
＆LT;行＆GT;
＆LT;细胞SS：StyleID =S22＆GT;＆lt;数据SS：TYPE =数大于7＆LT; /数据＆GT;＆LT; /电池＆gt;
＆LT; /行＆GT;
我不复制的风格，但你可以很容易地做到这一点。
编辑：一如既往谷歌是你的朋友（和我一样的;-)）：的 http://www.creativyst.com/Doc/Articles/CSV/CSV01.htm#CSVAndExcel 。
编辑（2）：我想链接就够了。该文章说，（如果您确定目标为仅 Excel）中您可以使用特定的Excel，CSV语法。所以你的情况，看着你的code我觉得你应该的插入缺少的逗号的改变和开放
writer.WriteString（\\）;
到
writer.WriteString（= \\）;
要注意的是我没有尝试。
只有一个问题出于好奇：是不是很简单，只是输出您需要什么样的工作对数据集，而不是
- 在XML转换它
- 生成一个特设的XSL
- 执行XSL转换
- 的结果复制到响应流
I am running into the same problem as in this question:
How do you prevent leading zeros from being stripped when importing an excel doc using c#
But I am not sure if that is the best solution for my scenario. Here is the code I am using to do the export. Does anyone know what I can change to prevent the leading 0's from being stripped off?
private static void Export_with_XSLT_Web(DataSet dsExport,
string[] sHeaders,
string[] sFileds,
ExportFormat FormatType,
string FileName)
{
HttpContext.Current.Response.Clear();
HttpContext.Current.Response.Buffer = true;
HttpContext.Current.Response.ContentType = "application/vnd.ms-excel";
HttpContext.Current.Response.AppendHeader("content-disposition",
"attachment;
filename=" + FileName);
}
// XSLT to use for transforming this dataset.
MemoryStream stream = new MemoryStream();
XmlTextWriter writer = new XmlTextWriter(stream, Encoding.UTF8);
CreateStylesheet(writer, sHeaders, sFileds, FormatType);
writer.Flush();
stream.Seek(0, SeekOrigin.Begin);
XmlDataDocument xmlDoc = new XmlDataDocument(dsExport);
XslTransform xslTran = new XslTransform();
xslTran.Load(new XmlTextReader(stream), null, null);
using(StringWriter sw = new StringWriter())
{
xslTran.Transform(xmlDoc, null, sw, null);
HttpContext.Current.Response.Write(sw.ToString());
writer.Close();
stream.Close();
HttpContext.Current.Response.End();
}
}
}
Here is the method that creates the stylesheet, is there anything in here that I can change to bring in some or all fields as text.
private static void CreateStylesheet(XmlTextWriter writer,
string[] sHeaders,
string[] sFileds,
ExportFormat FormatType)
{
try
{
// xsl:stylesheet
string ns = "http://www.w3.org/1999/XSL/Transform";
writer.Formatting = Formatting.Indented;
writer.WriteStartDocument();
writer.WriteStartElement("xsl", "stylesheet", ns);
writer.WriteAttributeString("version", "1.0");
writer.WriteStartElement("xsl:output");
writer.WriteAttributeString("method", "text");
writer.WriteAttributeString("version", "4.0");
writer.WriteEndElement();
// xsl-template
writer.WriteStartElement("xsl:template");
writer.WriteAttributeString("match", "/");
// xsl:value-of for headers
for(int i = 0; i < sHeaders.Length; i++)
{
writer.WriteString("\"");
writer.WriteStartElement("xsl:value-of");
writer.WriteAttributeString("select", "'" + sHeaders[i] + "'");
writer.WriteEndElement(); // xsl:value-of
writer.WriteString("\"");
}
// xsl:for-each
writer.WriteStartElement("xsl:for-each");
writer.WriteAttributeString("select", "Export/Values");
writer.WriteString("\r\n");
// xsl:value-of for data fields
for(int i = 0; i < sFileds.Length; i++)
{
writer.WriteString("\"");
writer.WriteStartElement("xsl:value-of");
writer.WriteAttributeString("select", sFileds[i]);
writer.WriteEndElement(); // xsl:value-of
writer.WriteString("\"");
}
writer.WriteEndElement(); // xsl:for-each
writer.WriteEndElement(); // xsl-template
writer.WriteEndElement(); // xsl:stylesheet
writer.WriteEndDocument();
}
catch(Exception Ex)
{
throw Ex;
}
}
I don't know the output of your XSL transformation: I will assume it's the xml format for Excel. Trying to reverse the process I wrote three numbers (007) in an Excel sheet: once as number, once as text and once as number but formatted to show 3 digits padded with zeros. Then I saved it as xml and looked at it. Here is the fragment:
<Row>
<Cell><Data ss:Type="Number">7</Data></Cell>
</Row>
<Row>
<Cell><Data ss:Type="String" x:Ticked="1">007</Data></Cell>
</Row>
<Row>
<Cell ss:StyleID="s22"><Data ss:Type="Number">7</Data></Cell>
</Row>
I'm not copying the style but you can easily do it.
Edit: as always Google Is Your Friend (and mine, too ;-) ): http://www.creativyst.com/Doc/Articles/CSV/CSV01.htm#CSVAndExcel.
Edit (2): I thought the link was enough. The article is saying that (if you are sure the target is only Excel) you can use an Excel-specific CSV syntax. So in your case and looking at your code I think you should insert the missing commas and change the opening
writer.WriteString("\"");
into
writer.WriteString("=\"");
Beware that I didn't try.
Just one question out of curiosity: wouldn't it be simpler to just output what you need working on the DataSet instead of
- transforming it in XML
- generating an ad-hoc XSL
- performing the XSL transformation
- copying the result to the Response stream
?
这篇关于如何从被剥去出口从一个DataTable到Excel时停止0领先的？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
