Asp.Net 中 动态生成 Excel 文件的心得

近段时间一直在维护修改一个package（Asp.net + C#+SQLServer），在动态生成Excel文件方面有些心得。

我现在只知道两种方法
1，Server 端存在 Excel 文件，也就是模板。 复制模板，取得要显示的数据，以操作表的形式，将数据写入Excel 。关于 操作方法   原来的Post上面有，在此不详细说明了。

2，Server端无模板，通过 xslt 和 xml 转化成 Excel 文件，xslt 中 是要求的Excel文件显示的式样(style)，xml 中当然是数据啦。
 下面是一段主要的代码
//动态生成 xslt 文件 strXSLTPath 是要保存的 xslt 文件 在 Server上的绝对路径
   System.IO.StreamWriter sw = new System.IO.StreamWriter(strXSLTPath);
   sw.WriteLine("<xsl:stylesheet version=\"1.0\" xmlns=\"urn:schemas-microsoft-com:office:spreadsheet\" xmlns:xsl=\"http://www.w3.org/1999/XSL/Transform\"");
   sw.WriteLine(" xmlns:msxsl=\"urn:schemas-microsoft-com:xslt\" xmlns:user=\"urn:my-scripts\" xmlns:o=\"urn:schemas-microsoft-com:office:office\"");
   sw.WriteLine(" xmlns:x=\"urn:schemas-microsoft-com:office:excel\" xmlns:ss=\"urn:schemas-microsoft-com:office:spreadsheet\">");
   sw.WriteLine(" <xsl:template match=\"NewDataSet\">");
   sw.WriteLine("  <Workbook xmlns=\"urn:schemas-microsoft-com:office:spreadsheet\" xmlns:o=\"urn:schemas-microsoft-com:office:office\"");
   sw.WriteLine("   xmlns:x=\"urn:schemas-microsoft-com:office:excel\" xmlns:ss=\"urn:schemas-microsoft-com:office:spreadsheet\"");
   sw.WriteLine("   xmlns:html=\"http://www.w3.org/TR/REC-html40\">");
   sw.WriteLine("   <Styles>");
   sw.WriteLine("    <Style ss:ID=\"Default\" ss:Name=\"Normal\">");
   sw.WriteLine("     <Alignment ss:Vertical=\"Bottom\" />");
   sw.WriteLine("     <Borders />");
   sw.WriteLine("     <Font ss:FontName=\"ＭＳ Ｐゴシック\" x:CharSet=\"128\" x:Family=\"Modern\" ss:Size=\"11\" />");
   sw.WriteLine("     <Interior />");
   sw.WriteLine("     <NumberFormat />");
   sw.WriteLine("     <Protection />");
   sw.WriteLine("    </Style>");
   sw.WriteLine("   </Styles>");
   sw.WriteLine("   <Worksheet ss:Name=\"DataSheet\">");
   sw.WriteLine("   <Table ss:ExpandedColumnCount=\"" + intColCount + "\" ss:ExpandedRowCount=\"" + (intRowCount + 1) + "\" x:FullColumns=\"1\" x:FullRows=\"1\"");
   sw.WriteLine("   ss:DefaultColumnWidth=\"54\" ss:DefaultRowHeight=\"13.5\">");
   sw.WriteLine("   <xsl:apply-templates select=\"Fields\" />");
   sw.WriteLine("   <xsl:apply-templates select=\"SearchRslts\" />");
   sw.WriteLine("   </Table>");
   sw.WriteLine("   </Worksheet>");
   sw.WriteLine("  </Workbook>");
   sw.WriteLine(" </xsl:template>");
   sw.WriteLine(" <xsl:template match=\"Fields\">");
   sw.WriteLine("  <Row>");
   for(i=0;i<intColCount;i++)
   {
    sw.WriteLine("   <Cell>");
    sw.WriteLine("    <Data ss:Type=\"String\">");
    sw.WriteLine("     <xsl:value-of select=\"field" + (i+1) +"\" />");
    sw.WriteLine("    </Data>");
    sw.WriteLine("   </Cell>");
   }
   sw.WriteLine("  </Row>");
   sw.WriteLine(" </xsl:template>");

   sw.WriteLine(" <xsl:template match=\"SearchRslts\">");
   sw.WriteLine("  <Row>");
   for(i=0;i<intColCount;i++)
   {
    sw.WriteLine("   <Cell>");
    sw.WriteLine("    <Data ss:Type=\"String\">");
    sw.WriteLine("     <xsl:value-of select=\"" + dsData.Tables[0].Columns[i].ColumnName + "\" />");
    sw.WriteLine("    </Data>");
    sw.WriteLine("   </Cell>");
   }
   sw.WriteLine("  </Row>");
   sw.WriteLine(" </xsl:template>");
   sw.WriteLine("</xsl:stylesheet>");
   sw.Close();

//从DataSet 生成 XML  dsData 是 Dataset 对象
   //First, save the XML representation of the DataSet in a file
   //and add a processing instruction to the XML so that it can be
   //transformed client-side.
   System.Xml.XmlTextWriter tw;
   System.Random randa = new System.Random();
   string strXMLPath = Server.MapPath(randa.Next() + ".xml");
   tw = new System.Xml.XmlTextWriter(strXMLPath, System.Text.Encoding.UTF8);
   tw.Formatting = System.Xml.Formatting.Indented;
   tw.Indentation = 3;
   tw.WriteStartDocument();
   tw.WriteProcessingInstruction("xml-stylesheet",
    "type='text/xsl' href='"+ strXSLTPath + "'");
   dsData.WriteXml(tw);
   tw.Close();

// 转换成 excel 文件  
   //Second, transform the DataSet XML and save it to a file.
   System.Xml.XmlDataDocument xmlDoc = new System.Xml.XmlDataDocument(dsData);
   System.Xml.XPath.XPathNavigator nav = xmlDoc.CreateNavigator();
   System.Xml.Xsl.XslTransform xslTran = new System.Xml.Xsl.XslTransform();
   xslTran.Load(strXSLTPath);
   tw = new System.Xml.XmlTextWriter(SaveAs, System.Text.Encoding.UTF8);
   tw.Formatting = System.Xml.Formatting.Indented;
   tw.Indentation = 3;
   tw.WriteStartDocument();
   xslTran.Transform(nav,null,tw,null);
   tw.Close();      
   //Delete the XML File
   System.IO.File.Delete(strXMLPath);

参考：http://support.microsoft.com/default.aspx?scid=kb;en-us;319180